免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 7877 | 回复: 4

【已解决】Mysql复合索引字段顺序的问题 [复制链接]

论坛徽章:
0
发表于 2013-01-08 16:26 |显示全部楼层
本帖最后由 cenalulu 于 2013-01-09 11:06 编辑

表test如下:
| Field       | Type                  | Null | Key | Default | Extra          |
+-------------+-----------------------+------+-----+---------+----------------+
| pid         | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| tid         | mediumint( unsigned | NO   | MUL | 0       |                |
| showtime    | int(11)               | YES  |     | 0       |                |

数据量200w+

原来有个复合索引建立在tid和showtime上的。
现有个sql
SELECT * FROM test  WHERE AND tid='47992' AND pid >=1660250 and showtime>0  ORDER 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行数据,即使加上排序的时间也很快。

不知道真实情况是否这样,求大神给出正确解释。

论坛徽章:
9
每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00数据库技术版块每周发帖之星
日期:2016-03-07 16:30:25
发表于 2013-01-08 17:34 |显示全部楼层
楼主理解是正确的。
情况1的结果是 tid过滤完以后按照showtime,pid顺序一个个检查 pid的值是不是> 1660250 (从执行结果来看需要扫描12万行左右才能得到结果
情况2 的结果是 tid过滤完以后,继续用pid索引过滤 > 1660250, 最后再进行排序。(从执行计划来看只需对2000行进行排序)

论坛徽章:
0
发表于 2013-01-08 17:52 |显示全部楼层
回复 2# cenalulu
感谢解答!
看来加索引这事还真的经过实际的测试,如果tid+pid过滤后的数据大于123444,效率应该比不上第一条索引了。。

论坛徽章:
8
综合交流区版块每周发帖之星
日期:2015-12-02 15:03:53数据库技术版块每日发帖之星
日期:2015-10-02 06:20:00IT运维版块每日发帖之星
日期:2015-10-02 06:20:00IT运维版块每日发帖之星
日期:2015-09-14 06:20:00金牛座
日期:2014-10-10 11:23:34CU十二周年纪念徽章
日期:2013-10-24 15:41:34酉鸡
日期:2013-10-19 10:17:1315-16赛季CBA联赛之北京
日期:2017-03-06 15:12:44
发表于 2013-01-09 18:55 |显示全部楼层
索引就是个平衡的艺术

论坛徽章:
0
发表于 2016-06-22 14:01 |显示全部楼层
为什么会赶不上第一条索引 搞不懂~回复 3# wangjj20


   
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP