《Mysql实例MySQL前缀索引导致的慢查询分析总结》要点：
本文介绍了Mysql实例MySQL前缀索引导致的慢查询分析总结，希望对您有用。如果有疑问，可以联系我们。
前端时间跟一个DB相关的项目,alanc反馈有一个查询,使用索引比不使用索引慢很多倍,有点毁三观.所以跟进了一下,用explain,看了看2个查询不同的结果.
不用索引的查询的时候结果如下,实际查询中速度比较块.
代码如下:
mysql> explain select * from rosterusers limit 10000,3 ;
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
| 1 | SIMPLE | rosterusers | ALL | NULL | NULL | NULL | NULL | 2010066 | |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
而使用索引order by的查询结果如下,速度反而慢的惊人.
mysql> explain select * from rosterusers order by username limit 10000,3 ;
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
| 1 | SIMPLE | rosterusers | ALL | NULL | NULL | NULL | NULL | 2010087 | Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
区别在于,使用索引查询的Extra变成了,Using filesort.居然用了使用外部文件进行排序.这个当然慢了.
但数据表上在username,的确是有索引的.怎么会反而要Using filesort?
看了一下数据表定义.是一个开源聊天服务器ejabberd的一张表.初看以为主键i_rosteru_user_jid是username,和jid的联合索引,那么使用order by username时应该是可以使用到索引才对呀?
代码如下:
CREATE TABLE `rosterusers` (
`username` varchar(250) NOT NULL,
`jid` varchar(250) NOT NULL,
UNIQUE KEY `i_rosteru_user_jid` (`username`(75),`jid`(75)),
KEY `i_rosteru_jid` (`jid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
仔细检查突然发现其主键定义,不是定义的完整的主键名称,而跟了一个75的长度描述,稍稍一愣,原来用的是前缀索引,而不是整个字段都是索引.(我的记忆里面InnoDB还不支持这玩意,估计是4.0后什么版本加入的),前缀索引就是将数据字段中前面N个字节作为索引的一种方式.
发现了这个问题后,我们开始怀疑慢查询和这个索引有关,前缀索引的主要用途在于有时字段过程,而MySQL支持的很多索引长度是有限制的.
首先不带order by 的limit 这种查询,本质可能还是和主键相关的,因为MySQL 的INNODB的操作实际都是依靠主键的(即使你没有建立,系统也会有一个默认的),而limit这种查询,使用主键是可以加快速度,(explain返回的rows 应该是一个参考值),虽然我没有看见什么文档明确的说明过这个问题,但从不带order by 的limit 查询的返回结果基本可以证明这点.
但当我们使用order by username的时候,由于希望使用的是username的排序,而不是username(75)的排序,但实际索引是前缀索引,不是完整字段的索引.所以反而导致了order by的时候完全无法利用索引了.(我在SQL语句里面增加强制使用索引i_rosteru_user_jid也不起作用).而其实使用中,表中的字段username 连75个都用不到,何况定义的250的长度.完全是自己折腾导致的麻烦.由于这是其他产品的表格,我们无法更改,暂时只能先将就用不不带排序的查询讲究.
总结：
•前缀索引,并不是一个万能药,他的确可以赞助我们对一个写过长的字段上建立索引.但也会导致排序(order by ,group by)查询上都是无法使用前缀索引的.
•任何时候,对于DB Schema定义,合理的规划自己的字段长度,字段类型都是首要的事情.
维易PHP培训学院每天发布《Mysql实例MySQL前缀索引导致的慢查询分析总结》等实战技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。
转载请注明本页网址：
http://www.vephp.com/jiaocheng/12301.html
