mysql创建多列索引查询百万表数据的性能优化经验分享

最近发现最代码网站中的收到的评论，提到我的，心情被赞的查询异常缓慢，通过nginx日志发现响应时间快的在5s，慢的有13s，终于忍无可忍花时间来解决了。

执行explain之后的截图如下：

可以看到possible_keys中有很多是之前无用的index，并没有按预想的多列索引status,source_user_id,type来查询，于是果断去掉了多余的索引，执行explain后截图：

可以看到虽然用到了status_sourceuserid_type索引，但rows反而更大了，可以想象查询时间还肯定会更慢吧。

于是修改了索引的列顺序为：

mysql> alter table javaniu_event drop index status_sourceuserid_type;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table javaniu_event add index sourceuserid_status_type(source_user_id, status, type);
Query OK, 0 rows affected (14.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行explain后截图

比上一次的优化降低了一个数量级。

之后研究了下mysql索引的机制，发现多列索引是按建立索引的列的顺序来顺序过滤数据的，所以按event的业务规则来说，肯定是先用户来区分动态，之后再按动态类型，最后再按动态状态来查询，于是最终调整索引顺序为：

mysql> alter table javaniu_event drop index sourceuserid_status_type;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table javaniu_event add index sourceuserid_type_status(source_user_id,type,status);
Query OK, 0 rows affected (16.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行explain后截图：

可以看到rows比上一次优化降低了一个数量级。

网页点击我收到的评论列表时明显响应要快很多。

平时多总结分享不只是对自己学到的知识的巩固，也是可以和其他技术交流学习的机会，牛牛们可以多静心分享下相关的经验和代码。