【已解决】Mysql复合索引字段顺序的问题
本帖最后由 cenalulu 于 2013-01-09 11:06 编辑表test如下：
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------+------+-----+---------+----------------+
| pid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| tid | mediumint(8) unsigned | NO | MUL | 0 | |
| showtime | int(11) | YES| | 0 | |
数据量200w+
原来有个复合索引建立在tid和showtime上的。
现有个sql
SELECT * FROM testWHERE AND tid='47992' AND pid >=1660250 and showtime>0ORDER BY p.showtime,p.pid LIMIT 0, 40;
于是将索引修改如下（tid为最左前缀不能改，有别的sql用到tid+showtime）：
ALTER TABLE test ADD INDEX idx_tid_showtime_pid(tid,showtime,pid);
执行sql效率很低，请看explain
+----+-------------+----------------+-------+----------------------+----------------------+---------+------+--------+-------------+
| id | select_type | table | type| possible_keys | key | key_len | ref| rows | Extra |
+----+-------------+----------------+-------+----------------------+----------------------+---------+------+--------+-------------+
|1 | SIMPLE | test | range | idx_tid_showtime_pid | idx_tid_showtime_pid | 8 | NULL | 123444 | Using where |
+----+-------------+----------------+-------+----------------------+----------------------+---------+------+--------+-------------+
感觉效率依然很低，通过slow-log记录可以看到执行时间在0.7秒以上。
测试了下去掉showtime条件后只使用tid和pid的复合索引效率是很高的，于是再次修改索引：
ALTER TABLE test ADD INDEX idx_tid_pid_showtime(tid,pid,showtime);
+----+-------------+-------+-------+---------------------------------------------------------------------------------------+----------------------+---------+------+------+-----------------------------+
| id | select_type | table | type| possible_keys | key | key_len | ref| rows | Extra |
+----+-------------+-------+-------+---------------------------------------------------------------------------------------+----------------------+---------+------+------+-----------------------------+
|1 | SIMPLE | test | range | idx_tid_showtime_pid,idx_tid_pid_showtime | idx_tid_pid_showtime | 12 | NULL | 3290 | Using where; Using filesort |
+----+-------------+-------+-------+---------------------------------------------------------------------------------------+----------------------+---------+------+------+------
这样效率大大提高。
以上问题，从我个人理解上是这样的，idx_tid_showtime_pid(tid,showtime,pid)这条索引满足了使用索引order by的条件，所以需要扫描123444行数据。
而 idx_tid_pid_showtime(tid,pid,showtime)这条索引的列顺序和order by的顺序不一样，排序无法使用索引，而where条件使用索引效率很好，只扫描了3290行数据，即使加上排序的时间也很快。
不知道真实情况是否这样，求大神给出正确解释。
楼主理解是正确的。
情况1的结果是 tid过滤完以后按照showtime,pid顺序一个个检查 pid的值是不是> 1660250 （从执行结果来看需要扫描12万行左右才能得到结果
情况2 的结果是 tid过滤完以后，继续用pid索引过滤 > 1660250， 最后再进行排序。（从执行计划来看只需对2000行进行排序） 回复 2# cenalulu
感谢解答！
看来加索引这事还真的经过实际的测试，如果tid+pid过滤后的数据大于123444，效率应该比不上第一条索引了。。 索引就是个平衡的艺术 为什么会赶不上第一条索引 搞不懂~回复 3# wangjj20
页:
[1]