索引 - 多列索引与最左原则

  • 作者:KK

  • 发表日期:2017.3.27


一次查询只会使用1个索引

那么问题来了,WHERE type_id = 3 AND user_id = 99的时候,这里有两个字段条件,会用哪个?

如本文章标题所述,会用最左边的type_id,先在索引里找出type_id为3的记录,查出这些数据后放到一个临时表里

再在这个临时表中遍历里面的数据,看看谁的user_id = 99,而且这一步没得用user_id索引,这就是最左原则的基本执行逻辑

基于《普通索引》这篇文章你也可以自己explain试试id和user_id两个条件同时存在,但顺序不同的情况下会使用哪个索引


把最常用的条件放在最左边吧

你可能觉得给type_id和user_id两个字段都建立索引不就行了吗?……其实索引建得太多不是好事,后面再说

要按需求来,不经常查的字段你可以酌情不建立索引的,如果很多查询都只用一个字段做条件那都好说

可是实际上我们往往并不只用一个字段做条件,此时真要好好考虑啊

那么每次构建多字段条件的时候,要控制好最左边的字段,把最常用的固定放在最左边就是了


多列索引

其实创建索引时是可以对多个字段创建索引的,只是此前我一直是主要使用单列索引来做示例,现在开始讲述的内容都会考虑多列索引了

如下这条创建索引的语句:

ALTER TABLE article ADD INDEX type_user_tag(type_id,user_id,tag_id);

INDEX关键字后面的type_user_tag是索引的名称嘛,这和创建单列索引的时候一样,而括号里有多个被用于创建索引的字段名,这时候就是创建了多列索引了(语法跟单列索引的创建方式一样,就是加多几个字段逗号隔开;顺便提一下,通常用一些MySql管理工具来创建单列索引的时候,索引名称和字段名称都是相同的)

以上创建多列索引的时候,实际上相当于创建了(type_id)(type_id,user_id)(type_id,user_id,tag_id)三种索引,当WHERE type_id = 123的时候就会用上(type_id),而WHERE type_id = 123 AND user_id = 999的时候就用了(type_id,user_id)WHERE 三个字段的时候你懂的……我就不废话了


多列索引下的最左原则

  1. 继续基于上面的内容,注意type_id是在最左边的,在最左原则的机制下,WHERE子句里必须存在type_id字段的条件才能使用索引,如果只存在user_idtag_id是无法将索引用起来的

  2. 当WHERE子句里的条件顺序与索引不同时,MySql优化器会自动优化成顺序相同,比如tag_id = 3 AND type_id = 2 AND user_id = 999会自动优化成type_id、user_id、tag_id这样的顺序(因为它看到你有这样顺序的索引,MySql的优化器足够智能)