这里重点讨论的是复合索引。（这里的索引都是指BTREE索引）
准备工作
首先，我们需要建立一张表，表非常简单，没有任何意义
create table t(
c1 char(20),
c2 char(20),
c3 char(20),
c4 char(20),
key idx_t_c1234(c1, c2, c3, c4)
);
其次，你需要对explain关键字有一定的了解（可以查看我另一篇文章explain详解）
下面进入重点
左前缀法则
如果索引存在多列，查询要从索引的最左前列开始，并且不能跳过索引中的列
索引中的四个字段都使用：
图片.png
key表示用到了索引idx_t_c1234,ref列是四个const表示4个条件都是常量，key_len表示使用索引大概的字节数，extra中Using where 表示使用了where条件，Using index表示覆盖索引，说明select查询的字段都在索引中可以获取，不需要去磁盘中找。
中间差个条件呢,如：
select * from t where c1='a' and c2='b' and c4='d';
图片.png
此时key_len的长度是122，而上面是244；ref中是两个const，可以得到此时只是使用了索引中的c1和c2两个字段。c4失效了。因为差了c3，中间断了，导致索引失效
如果是这样的呢
select * from t where c1='a' and c2 > 'b' and c3 = 'c';
图片.png
可以看到key_len是61，ref只有一个const，表示只用到了c1；范围之后索引全失效，这个也类似于中间断了。（如果是5.7以前的版本的话，这里type会range，而key_len会是122，用到了c1和c2两个字段）
下面的SQL，有兴趣的可以试下
explain select * from t where c2='b' and c3='c' and c4='d';
跟上面类似，从源头就断了，索引肯定会失效；type变为index（为啥不是all？可以思考下）
如果修改表结构：
alter table add column c5 char(20);
再执行上面的sql看看（好好理解两者的不同）
左前缀法则非常有用，在order by和group by还会用到
覆盖索引
覆盖索引这个概念其实非常简单，但确非常有用；覆盖索引是指select查询的字段都来自index（索引），不需要去磁盘中查找，从而提高效率。
在explain关键字中的extra列中如果出现了Using index，表示用到了覆盖索引。
图片.png
图片.png
尽量少用select *,用select *一般都会导致无法用覆盖索引（除非所有字段都建索引，这很明显不是明智的做法）
索引列少操作
一般来说，索引列上做任何操作（计算、函数、类型转换（自动或手动）），都可能会导致索引类失效。
图片.png
少用不等
在索引列上使用!= 或 <> 都可能导致索引失效
图片.png
慎用null
使用is null 或者is not null也可能会导致索引失效
自mysql5.6之后，增加了Using index condition，使用is null 或者is not null会使用Using index condition进行
优化，但是还是慎用，虽然优化了，依然会影响效率。
图片.png
like操作
like操作很可能会导致索引失效
图片.png
用like尽量不要在开头加匹配符
图片.png
如果这样写呢
explain select * from t where c1 like 'a%' and c2 > 'b' and c3 = 'c';
有兴趣的可以自己去尝试下。
如果非要首位写匹配符呢，建议用覆盖索引。
字符串一定要加单引号
字符串不加单引号会导致索引失效（自动类型转换），这个错误非常难发现，一定要养成好的习惯
图片.png
少用or
用or来连接会导致索引失效
图片.png
最后说一个新手常犯的错误，在where条件的列上都加上索引。
对于btree索引而言，独立的索引只能用一个，也就是说，where条件的列上都加上索引，只能用上一个索引，正确的做法是用复合索引。
网友评论