[mysql优化]子查询与连接查询

• 1.玩家新建表部分字段

  data_account_creates | CREATE TABLE `data_account_creates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `log_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `world_id` int(11) NOT NULL,
  `channel_id` int(11) NOT NULL,
  `account` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `data_account_creates_world_id_log_time_index` (`world_id`,`log_time`),
  KEY `create_time_world_channel` (`log_time`,`world_id`,`channel_id`),
  ) ENGINE=InnoDB AUTO_INCREMENT=41423 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

• 2.玩家登录表部分字段

  data_account_logins | CREATE TABLE `data_account_logins` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `log_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `world_id` int(11) NOT NULL,
  `channel_id` int(11) NOT NULL,
  `account` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `account` (`account`,`log_time`),
  KEY `data_account_logins_world_id_log_time_index` (`world_id`,`log_time`),
  KEY `login_time_world_channel` (`log_time`,`world_id`,`channel_id`),
  ) ENGINE=InnoDB AUTO_INCREMENT=148269 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

  查询结果需求：[有效新增]>所选时期内，每日新增用户中在注册起7日内（包含当日）有2日及以上登陆过游戏的用户数量。

  a.使用子查询,explain分析

查询结果如下：

b.使用连接查询，explain分析

查询结果如下：

我们首先从子查询看起：

i.子查询使用了三次临时表[using temporary]，两次外部排序[using filesort]。最多扫描行数：57494。 ii.连接查询使用了两次临时表，两次外部排序。最多扫描行数7845。 使用show processlist可以看到使用子查询时，最长的时间消耗是在创建临时表数据[copying to tmp table]这个阶段：

总结：在使用查询语句时，应当注意临时表的情况，尽可能减少临时表的数量以及扫描行数，提高命中率。

转载请注明原文地址：https://blog.keepchen.com/a/mysql-query-optimization.html