MySQL前缀索引导致的慢查询分析总结

2024-07-24 13:00:56

字体：大中小

来源：转载

供稿：网友

前端时间跟一个DB相关的项目，alanc反馈有一个查询，使用索引比不使用索引慢很多倍，有点毁三观。所以跟进了一下，用explain，看了看2个查询不同的结果。

不用索引的查询的时候结果如下，实际查询中速度比较块。

复制代码代码如下:

mysql> explain select * from rosterusers limit 10000,3 ;

复制代码代码如下:

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定义，合理的规划自己的字段长度，字段类型都是首要的事情。

上一篇：解析SQLite中的常见问题与总结详解

下一篇：MYSQL索引无效和索引有效的详细介绍