索引 - 多列索引与最左原则 ¶
作者: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 三个字段
的时候你懂的……我就不废话了
多列索引下的最左原则 ¶
继续基于上面的内容,注意
type_id
是在最左边的,在最左原则的机制下,WHERE子句里必须存在type_id
字段的条件才能使用索引,如果只存在user_id
或tag_id
是无法将索引用起来的当WHERE子句里的条件顺序与索引不同时,MySql优化器会自动优化成顺序相同,比如
tag_id = 3 AND type_id = 2 AND user_id = 999
会自动优化成type_id、user_id、tag_id这样的顺序(因为它看到你有这样顺序的索引,MySql的优化器足够智能)