[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分析

使用子查询,explain分析1 使用子查询,explain分析2

查询结果如下:

使用子查询,explain查询结果

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

使用连接查询,explain分析 使用连接查询,explain分析

查询结果如下:

使用连接查询,explain分析查询结果

我们首先从子查询看起:

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

show processlist

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

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