开发者社区> 问答> 正文

MySQL中复合索引中列的顺序对性能的影响,数据库报错

select * from dlog_user order by online_status, username

先看上面这个内联的SQL语句,username是dlog_user表的主键,dlog_friend有一个由 username和 friend_username组合而成的复合主键。

测试条件一


dlog_user 和 dlog_friend 两个表除了主键外没有建任何索引,对这条SQL语句EXPLAIN的结果是 dlog_user 做了全表查询(type=ALL),Extra信息是use filesort

测试条件二

dlog_user 增加复合索引

create index idx_online_status on dlog_user( username, online_status);

再次EXPLAIN SQL语句,还是全表查询以及 use filesort

测试条件三

修改复合索引,将 username 和 online_status 顺序调换一下,这回得到的结果是:type=index, Extra=空

索引起作用了!

测试条件四

更改SQL语句如下:

select a.* from dlog_user a inner join dlog_friend b on a.username=b.friend_username where b.username='ld' order by a.online_status desc,a.username

也就是ORDER BY的时候,两个字段的排序方式相反,这时不管怎么调整索引,执行此SQL语句都要进行全表查询以及 user filesort。

结论

1. ORDER BY 中的字段必须按照SQL语句中的顺序来建索引;
2. ORDER BY 中的字段的排序顺序必须一直,否则索引无效。
3. 建了索引不一定就有效,用实际的SQL检查一下。

附录(MySQL文档对use filesort的解释)

MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause. The keys then are sorted and the rows are retrieved in sorted order.

其他数据库不知道如何!

补充

SQL SERVER同样存在此问题,而且像这种情况如果在SQL SERVER分开对两个字段建索引是无效的。

![image.png](https://ucc.alicdn.com/pic/developer-ecology/f68de3adb8124da0b2abd548cf576bac.png)

展开
收起
python小菜菜 2020-06-01 19:30:20 882 0
1 条回答
写回答
取消 提交回答
  • "

    哇,很久的文章了,被我给翻出来了

    非常有用,谢谢。

    ######哇!百度搜到这个帖子,帖子中的内容还有效吗?" ![image.png](https://ucc.alicdn.com/pic/developer-ecology/38cd34e980f445e5a3cfcd44f435e573.png)
    2020-06-01 19:30:31
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
搭建电商项目架构连接MySQL 立即下载
搭建4层电商项目架构,实战连接MySQL 立即下载
PolarDB MySQL引擎重磅功能及产品能力盛大发布 立即下载

相关镜像