一个优化MySQL查询操作的具体案例分析
问题描述
一个用户反映先线一个SQL语句执行时间慢得无法接受。SQL语句看上去很简单（本文描述中修改了表名和字段名):
SELECTcount(*)FROMaJOINbONa.`S`=b.`S`WHEREa.`L`>'2014-03-3000:55:00'ANDa.`L`<'2014-03-3001:00:00';
且查询需要的字段都建了索引，表结构如下：
CREATETABLE`a`( `L`timestampNOTNULLDEFAULT'2000-01-0100:00:00', `I`varchar(32)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL, `A`varchar(32)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL, `S`varchar(64)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL, `F`tinyint(4)DEFAULTNULL, `V`varchar(256)CHARACTERSETutf8COLLATEutf8_binDEFAULT'', `N`varchar(64)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL, KEY`IX_L`(`L`), KEY`IX_I`(`I`), KEY`IX_S`(`S`) )ENGINE=InnoDBDEFAULTCHARSET=utf8; CREATETABLE`b`( `R`timestampNOTNULLDEFAULT'2000-01-0100:00:00', `V`varchar(32)DEFAULTNULL, `U`varchar(32)DEFAULTNULL, `C`varchar(16)DEFAULTNULL, `S`varchar(64)DEFAULTNULL, `I`varchar(64)DEFAULTNULL, `E`bigint(32)DEFAULTNULL, `ES`varchar(128)DEFAULTNULL, KEY`IX_R`(`R`), KEY`IX_C`(`C`), KEY`IX_S`(`S`) )ENGINE=InnoDBDEFAULTCHARSET=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|Usingindex| |1|SIMPLE|a|ref|IX_L,IX_S|IX_S|195|test.b.S|1|Usingwhere| +----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+
分析
从explain的结果看，查询用了b作为驱动表。
上一篇文章我们介绍到，MySQL选择jion顺序是分别分析各种join顺序的代价后，选择最小代价的方法。
这个join只涉及到两个表，自然也与optimizer_search_depth无关。于是我们的问题就是，我们预期的那个join顺序的为什么没有被选中？
MySQLTips:MySQL提供straight_join语法，强制设定连接顺序。
explainSELECTcount(*)FROMastraight_joinbONa.`S`=b.`S`WHEREa.`L`>'2014-03-3000:55:00'ANDa.`L`<'2014-03-3001: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|Usingwhere| |1|SIMPLE|b|index|IX_S|IX_S|195|NULL|1038165|Usingwhere;Usingindex;Usingjoinbuffer| +----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+
MySQLTips:explain结果中，join的查询代价可以用依次连乘rows估算。
?join顺序对了，简单的分析查询代价：普通join是1038165*1,straight_join是63*1038165.貌似MySQL没有错。但一定哪里不对！
发现异常
回到我们最初的设想。我们预计表a作为驱动表，是因为认为表b能够用上IX_S索引，而实际上staight_join的时候确实用上了，但这个结果与我们预期的又不同。
我们知道，索引的过滤性是决定了一个索引在查询中是否会被选中的重要因素，那么是不是b.S的过滤性不好呢？
MySQLTips:showindexfromtbname返回结果中Cardinality的值可以表明一个索引的过滤性。
showindex的结果太多，也可以从information_schema表中取。
mysql>select*frominformation_schema.STATISTICSwheretable_name='b'andindex_name='IX_S'\G ***************************1.row*************************** TABLE_CATALOG:def TABLE_SCHEMA:test TABLE_NAME:b NON_UNIQUE:1 INDEX_SCHEMA:test INDEX_NAME:IX_S SEQ_IN_INDEX:1 COLUMN_NAME:S COLLATION:A CARDINALITY:1038165 SUB_PART:NULL PACKED:NULL NULLABLE:YES INDEX_TYPE:BTREE COMMENT: INDEX_COMMENT:
可以这个索引的CARDINALITY:1038165，已经很大了。那这个表的估算行是多少呢。
showtablestatuslike'b'\G ***************************1.row*************************** Name:b Engine:InnoDB Version:10 Row_format:Compact Rows:1038165 Avg_row_length:114 Data_length:119160832 Max_data_length:0 Index_length:109953024 Data_free:5242880 Auto_increment:NULL Create_time:2014-05-2300:24:25 Update_time:NULL Check_time:NULL Collation:utf8_general_ci Checksum:NULL Create_options: Comment: 1rowinset(0.00sec)
从Rows:1038165看出，IX_S这个索引的区分度被认为非常好，已经近似于唯一索引。
MySQLTips:在showtablestatus结果中看到的Rows用于表示表的当前行数。对于MyISAM表这是一个精确值，但对InnoDB这是个估算值。
虽然是估算值，但优化器是以此为指导的，也就是说，上面的某个explain里面的数据完全不符合期望：staight_join结果中第二行的rows。
阶段结论
我们发现整个错误的逻辑是这样的：以a为驱动表的执行计划，由于索引b.S的rows估计为1038165导致优化器认为代价大于以b为驱动表。而实际上这个索引的区分度为1.（当然对explan结果比较熟悉的同学会发现，第二行的type字段和Extra字段一起诡异了)
也就是说，straight_join得到的每一行去b中查询的时候，都走了全表扫描。在MySQL里面出现这种情况的最常见的是类型转换。比如一个字符串字段，虽然包含的是全数字，但查询的时候传入的不是字符串格式。
在这个case里面，两个都是字符串。因此，就是字符集相关了。
回到两个表结构，发现S字段的声明差别在于COLLATEutf8_bin--这个就是本case的根本原因了：a表得到的S值是utf8_bin，优化器认为类型不同，无法直接用上索引b.IX_S过滤。
至于为什么还会用上索引，这个是因为覆盖索引带来“误解”。
MySQLTips：若查询的所有结果能够从某个索引完全得到，则会优先用遍历索引替代遍历数据。
作为验证，
mysql>explainSELECT*FROMastraight_JOINbONbinarya.`S`=b.`S`WHEREa.`L`>'2014-03-3000:55:00'ANDa.`L`<'2014-03-3001:00:00'; +—-+————-+——-+——-+—————+——+———+——+———+————————————————+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +—-+————-+——-+——-+—————+——+———+——+———+————————————————+ |1|SIMPLE|a|range|IX_L|IX_L|4|NULL|63|Usingwhere| |1|SIMPLE|b|ALL|IX_S|NULL|NULL|NULL|1038165|Rangecheckedforeachrecord(indexmap:0x4)| +—-+————-+——-+——-+—————+——+———+——+———+————————————————+
由于结果是select*，无法使用覆盖索引，因此第二行的key就显示为NULL.(笔者泪：要是早出这个结果查起来可方便多了）
优化
当然最直接的想法就是修改两个表的S字段的定义，改成相同即可。这个方法可以避免修改业务代码，但DDL代价略大。这里提供两种在SQL语句方面的优化。
1、selectcount(*)frombjoin(selectsfromaWHEREa.`L`>'2014-03-3000:55:00'ANDa.`L`<'2014-03-3001:00:00')taonb.S=ta.s;
这个写法比较直观，需要注意最后b.S和ta.S的顺序
2、SELECTcount(*)FROMaJOINbONbinarya.`S`=b.`S`WHEREa.`L`>'2014-03-3000:55:00'ANDa.`L`<'2014-03-3001:00:00';
从前面的分析知道是由于b.S定义为utf8_bin.
MySQLTips:MySQL中字符集命名规则中,XXX_bin与XXX的区别为大小写是否敏感。
这里我们将A.s全部增加binary限定，先转为小写，就是将临时结果集转成utf8_bin，之后使用b.S匹配时就能够直接利用索引。
其实两个改写方法的本质相同，区别是写法1是隐式转换。理论上说写法2速度更快些。
小结
做join的字段尽量设计为类型完全相同。