一个用户SQL慢查询分析，原因及优化_MySQL

问题描述

<code style="font-family: inherit;font-size: 14px;padding: 0px;color: inherit;background-color: transparent;border: 0px">一个用户反映先上一个SQL语句执行时间慢得无法接受。SQL语句看上去很简单（本文描述中修改了表名和字段名):SELECT count(*)FROMaJOINb ONa.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00' ; 且查询需要的字段都建了索引，表结构如下：CREATE TABLE `a` (`L` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',`I` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,`A` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,`S` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,`F` tinyint(4) DEFAULT NULL,`V` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '',`N` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,KEY `IX_L` (`L`),KEY `IX_I` (`I`),KEY `IX_S` (`S`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `b` (`R` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',`V` varchar(32) DEFAULT NULL,`U` varchar(32) DEFAULT NULL,`C` varchar(16) DEFAULT NULL,`S` varchar(64) DEFAULT NULL,`I` varchar(64) DEFAULT NULL,`E` bigint(32) DEFAULT NULL,`ES` varchar(128) DEFAULT NULL,KEY `IX_R` (`R`),KEY `IX_C` (`C`),KEY `IX_S` (`S`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;从语句看，这个查询计划很自然的，就应该是先用a作为驱动表，先后使用 a.L和b.S这两个索引。而实际上explain的结果却是：+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+| id | select_type | table | type| possible_keys | key| key_len | ref| rows| Extra |+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+|1 | SIMPLE| b | index | IX_S| IX_S | 195 | NULL | 1038165 | Using index ||1 | SIMPLE| a | ref | IX_L,IX_S | IX_S | 195 | test.b.S | 1 | Using where |+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+</code>

分析

<code style="font-family: inherit;font-size: 14px;padding: 0px;color: inherit;background-color: transparent;border: 0px">从explain的结果看，查询用了b作为驱动表。上一篇文章我们介绍到，MySQL选择jion顺序是分别分析各种join顺序的代价后，选择最小代价的方法。这个join只涉及到两个表，自然也与optimizer_search_depth无关。于是我们的问题就是，我们预期的那个join顺序的为什么没有被选中？MySQL Tips: MySQL提供straight_join语法，强制设定连接顺序。explain SELECT count(*)FROMastraight_joinb ONa.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00' ;+----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+| id | select_type | table | type| possible_keys | key| key_len | ref| rows| Extra |+----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+|1 | SIMPLE| a | range | IX_L,IX_S | IX_L | 4 | NULL |63 | Using where ||1 | SIMPLE| b | index | IX_S| IX_S | 195 | NULL | 1038165 | Using where; Using index; Using join buffer |+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------------<mark style="color:transparent">来4源gaodaimacom搞#代%码*网</mark>-------------+</code>

<code style="font-family: inherit;font-size: 14px;padding: 0px;color: inherit;background-color: transparent;border: 0px">MySQL Tips: explain结果中，join的查询代价可以用依次连乘rows估算。</code>

<code style="font-family: inherit;font-size: 14px;padding: 0px;color: inherit;background-color: transparent;border: 0px">join顺序对了，简单的分析查询代价：普通join是1038165*1, straight_join是 63*1038165. 貌似MySQL没有错。但一定哪里不对！</code>

喜欢 (0)赏

[搞代码]

分享 (0)