MySQL索引优化（二）
MySQL索引优化（二）
一、演示索引失效问题
（1）先建立一个staffs表，往其中插入三条数据，其中id为自增的主键
（2）在name，age，pos字段上建立复合索引：
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name,age,pos);
（3）现在先列出一些常见的引起索引失效的原因：
1.全值匹配我最爱：
EXPLAIN SELECT * FROM staffs WHERE name='July';
结果如下：
EXPLAIN SELECT * FROM staffs WHERE name='July' AND age=25;
EXPLAIN SELECT * FROM staffs WHERE name='July' AND age=25 AND pos='dev';
以上三个都是ok的
然而，以下语句却变成了全表扫描，没有用到索引
EXPLAIN SELECT * FROM staffs WHERE age=25 AND pos='dev';
EXPLAIN SELECT * FROM staffs WHERE pos='dev';
根据这几个分析，可以引申出下面的结论：
2.最佳左前缀法则
如果索引了多列，要遵守最左前缀法则，指的是查询从索引最左列开始并且不跳过索引中的列
EXPLAIN SELECT * FROM staffs WHERE name='July' AND age=25 AND pos='dev';
上述SQL语句索引未失效，因此可以得出个口诀“排头大哥不能死，中间兄弟不能断”，即索引的第一个列必须要用到，才能确保索引不失效；中间的列不要断开，否则用不到全部的索引。
3.不在索引列上做任何操作（计算、函数、自动或手动类型转换），否则会导致索引失效而转向全表扫描。
如：
EXPLAIN SELECT * FROM staffs WHERE LEFT(name,4)='July';
显然，在索引列上添加了操作后，性能下降了。
4.存储引擎不能使用索引中范围条件右边的列
EXPLAIN SELECT * FROM staffs WHERE name='July' AND age>25 AND pos='dev';
age条件变成了范围，其之后的索引全部失效了。
**5.尽量使用覆盖索引(只访问索引的查询（查询列和索引列一致）)，减少select ***
EXPLAIN SELECT name,age,pos FROM staffs WHERE name='July' AND age>25 AND pos='dev';
可见，性能提升。
6.MySQL在使用不等于(!=或< >)的时候无法使用索引会导致全表扫描
7.is null.is not null也无法使用索引
8.like以通配符开头(’%abc…’)，索引会失效，变成全表扫描的操作
EXPLAIN SELECT * FROM staffs WHERE name LIKE '%July%';
EXPLAIN SELECT * FROM staffs WHERE name LIKE '%July';
EXPLAIN SELECT * FROM staffs WHERE name LIKE 'July%';
可见，前两条语句索引会失效。于是得出又一个口诀，like %写右边。但是，如果工作中需要用到两边都有%的语句，应该怎么样解决索引不被使用呢？
下面建立一个tbl_user表来进行演示
现在在name和age自动上建立索引
CREATE INDEX idx_user_nameAge ON tbl_user(name,age);
分析以下的SQL语句
EXPLAIN SELECT name,age FROM tbl_user WHERE NAME like '%aa%';
结果如下：
显然使用到了索引idx_user_nameAge ；
下面分析以下SQL语句
EXPLAIN SELECT id FROM tbl_user WHERE NAME like '%aa%';
结果如下：
显然使用了主键索引id；
下面分析以下SQL语句：
EXPLAIN SELECT name FROM tbl_user WHERE NAME like '%aa%';
结果如下：
显然用到了索引idx_user_nameAge
下面分析以下SQL语句：
EXPLAIN SELECT age FROM tbl_user WHERE NAME like '%aa%';
结果如下：
显然使用了索引idx_user_nameAge
如果查询id,name或id,name,age或name，age，索引都是不失效的。
但是如果是以下的SQL语句
EXPLAIN SELECT * FROM tbl_user WHERE NAME like '%aa%';
此时，索引失效。
再分析以下语句：
EXPLAIN SELECT id,name,age,email FROM tbl_user WHERE NAME like '%aa%';
结果如下：
显然，索引失效。
因此，结论就是如果要使用%abc%来做模糊查询，要建立覆盖索引，查询的字段范围不能超过索引所在的字段范围。
9.字符串不加单引号，索引失效
来看看staffs表：
以下两条SQL语句
SELECT * FROM staffs WHERE name=2000;
SELECT * FROM staffs WHERE name='2000';
查询结果相同，如下：
但是根据3中所提到的不要在索引字段上进行任何操作的规则，SELECT * FROM staffs WHERE name=2000; 语句在name字段上进行了类型转换，将2000转成了字符串类型来进行查询，因此索引会失效。
10.少用or，用or来连接时会索引失效
一些小练习：