MySQL索引优化（二）

缺乏、安全感 2023-07-05 13:17 85阅读 0赞

## MySQL索引优化（二） ##

### 一、演示索引失效问题 ###

（1）先建立一个staffs表，往其中插入三条数据，其中id为自增的主键  
![在这里插入图片描述][2020021511404819.png]

（2）在name，age，pos字段上建立复合索引：

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name,age,pos);

（3）现在先列出一些常见的引起索引失效的原因：

**1.全值匹配我最爱：**

EXPLAIN SELECT * FROM staffs WHERE name='July';

结果如下：  
![在这里插入图片描述][20200215133733729.png]

EXPLAIN SELECT * FROM staffs WHERE name='July' AND age=25;

![在这里插入图片描述][20200215142918623.png]

EXPLAIN SELECT * FROM staffs WHERE name='July' AND age=25 AND pos='dev';

![在这里插入图片描述][20200215144653814.png]  
以上三个都是ok的

然而，以下语句却变成了全表扫描，没有用到索引

EXPLAIN SELECT * FROM staffs WHERE age=25 AND pos='dev';

![在这里插入图片描述][20200215145029157.png]

EXPLAIN SELECT * FROM staffs WHERE pos='dev';

![在这里插入图片描述][2020021514514040.png]

根据这几个分析，可以引申出下面的结论：  
**2.最佳左前缀法则**  
如果索引了多列，要遵守最左前缀法则，指的是查询从索引最左列开始并且不跳过索引中的列

EXPLAIN SELECT * FROM staffs WHERE name='July' AND age=25 AND pos='dev';

![在这里插入图片描述][20200215152418542.png]  
上述SQL语句索引未失效，因此可以得出个口诀“排头大哥不能死，中间兄弟不能断”，即索引的第一个列必须要用到，才能确保索引不失效；中间的列不要断开，否则用不到全部的索引。  
**3.不在索引列上做任何操作（计算、函数、自动或手动类型转换），否则会导致索引失效而转向全表扫描。**  
如：

EXPLAIN SELECT * FROM staffs WHERE LEFT(name,4)='July';

![在这里插入图片描述][20200215153404713.png]  
显然，在索引列上添加了操作后，性能下降了。

**4.存储引擎不能使用索引中范围条件右边的列**

EXPLAIN SELECT * FROM staffs WHERE name='July' AND age>25 AND pos='dev';

![在这里插入图片描述][20200215153731688.png]  
age条件变成了范围，其之后的索引全部失效了。  
\*\*5.尽量使用覆盖索引(只访问索引的查询（查询列和索引列一致）)，减少select \*\*\*

EXPLAIN SELECT name,age,pos FROM staffs WHERE name='July' AND age>25 AND pos='dev';

![在这里插入图片描述][20200215154333494.png]  
可见，性能提升。

**6.MySQL在使用不等于(!=或< >)的时候无法使用索引会导致全表扫描**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70]  
**7.is null.is not null也无法使用索引**  
![在这里插入图片描述][20200215155025173.png]  
**8.like以通配符开头(’%abc…’)，索引会失效，变成全表扫描的操作**

EXPLAIN SELECT * FROM staffs WHERE name LIKE '%July%';

![在这里插入图片描述][20200215155355475.png]

EXPLAIN SELECT * FROM staffs WHERE name LIKE '%July';

![在这里插入图片描述][20200215155433787.png]

EXPLAIN SELECT * FROM staffs WHERE name LIKE 'July%';

![在这里插入图片描述][20200215155511604.png]  
可见，前两条语句索引会失效。于是得出又一个口诀，like %写右边。但是，如果工作中需要用到两边都有%的语句，应该怎么样解决索引不被使用呢？  
下面建立一个tbl\_user表来进行演示  
![在这里插入图片描述][20200215163925359.png]  
现在在name和age自动上建立索引

CREATE INDEX idx_user_nameAge ON tbl_user(name,age);

分析以下的SQL语句

EXPLAIN SELECT name,age FROM tbl_user WHERE NAME like '%aa%';

结果如下：  
![在这里插入图片描述][20200215164407667.png]  
显然使用到了索引idx\_user\_nameAge ；

下面分析以下SQL语句

EXPLAIN SELECT id FROM tbl_user WHERE NAME like '%aa%';

结果如下：  
![在这里插入图片描述][20200215164558242.png]  
显然使用了主键索引id；  
下面分析以下SQL语句：

EXPLAIN SELECT name FROM tbl_user WHERE NAME like '%aa%';

结果如下：  
![在这里插入图片描述][20200215164722886.png]  
显然用到了索引idx\_user\_nameAge  
下面分析以下SQL语句：

EXPLAIN SELECT age FROM tbl_user WHERE NAME like '%aa%';

结果如下：  
![在这里插入图片描述][20200215164837936.png]  
显然使用了索引idx\_user\_nameAge

如果查询id,name或id,name,age或name，age，索引都是不失效的。  
但是如果是以下的SQL语句

EXPLAIN SELECT * FROM tbl_user WHERE NAME like '%aa%';

![在这里插入图片描述][20200215165122473.png]  
此时，索引失效。  
再分析以下语句：

EXPLAIN SELECT id,name,age,email FROM tbl_user WHERE NAME like '%aa%';

结果如下：  
![在这里插入图片描述][20200215165301993.png]  
显然，索引失效。  
因此，结论就是如果要使用%abc%来做模糊查询，要建立覆盖索引，查询的字段范围不能超过索引所在的字段范围。  
**9.字符串不加单引号，索引失效**  
来看看staffs表：  
![在这里插入图片描述][20200215171633124.png]  
以下两条SQL语句

SELECT * FROM staffs WHERE name=2000;
    SELECT * FROM staffs WHERE name='2000';

查询结果相同，如下：  
![在这里插入图片描述][20200215171901388.png]  
但是根据3中所提到的不要在索引字段上进行任何操作的规则，`SELECT * FROM staffs WHERE name=2000;` 语句在name字段上进行了类型转换，将2000转成了字符串类型来进行查询，因此索引会失效。  
**10.少用or，用or来连接时会索引失效**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70 1]  
一些小练习：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70 2]

[2020021511404819.png]: https://img-blog.csdnimg.cn/2020021511404819.png
[20200215133733729.png]: https://img-blog.csdnimg.cn/20200215133733729.png
[20200215142918623.png]: https://img-blog.csdnimg.cn/20200215142918623.png
[20200215144653814.png]: https://img-blog.csdnimg.cn/20200215144653814.png
[20200215145029157.png]: https://img-blog.csdnimg.cn/20200215145029157.png
[2020021514514040.png]: https://img-blog.csdnimg.cn/2020021514514040.png
[20200215152418542.png]: https://img-blog.csdnimg.cn/20200215152418542.png
[20200215153404713.png]: https://img-blog.csdnimg.cn/20200215153404713.png
[20200215153731688.png]: https://img-blog.csdnimg.cn/20200215153731688.png
[20200215154333494.png]: https://img-blog.csdnimg.cn/20200215154333494.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200215154806309.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ==,size_16,color_FFFFFF,t_70
[20200215155025173.png]: https://img-blog.csdnimg.cn/20200215155025173.png
[20200215155355475.png]: https://img-blog.csdnimg.cn/20200215155355475.png
[20200215155433787.png]: https://img-blog.csdnimg.cn/20200215155433787.png
[20200215155511604.png]: https://img-blog.csdnimg.cn/20200215155511604.png
[20200215163925359.png]: https://img-blog.csdnimg.cn/20200215163925359.png
[20200215164407667.png]: https://img-blog.csdnimg.cn/20200215164407667.png
[20200215164558242.png]: https://img-blog.csdnimg.cn/20200215164558242.png
[20200215164722886.png]: https://img-blog.csdnimg.cn/20200215164722886.png
[20200215164837936.png]: https://img-blog.csdnimg.cn/20200215164837936.png
[20200215165122473.png]: https://img-blog.csdnimg.cn/20200215165122473.png
[20200215165301993.png]: https://img-blog.csdnimg.cn/20200215165301993.png
[20200215171633124.png]: https://img-blog.csdnimg.cn/20200215171633124.png
[20200215171901388.png]: https://img-blog.csdnimg.cn/20200215171901388.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200215172626244.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200215173152185.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ==,size_16,color_FFFFFF,t_70