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来连接时会索引失效

一些小练习：