MySql – 索引优化实例

川长思鸟来 2023-03-04 08:08 16阅读 0赞

**MySql – 索引优化实例**

**1. 索引优化实例**

**---------------------------------------------------------------------------------------------------------------------------**

## **1. 索引优化实例** ##

### 1.1 数据表结构 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NzU2Njgy_size_16_color_FFFFFF_t_70][]

![20200802124954121.png][]

### **1.2 前导模糊查询不能命中索引** ###

name列存在普通索引，当执行

EXPLAIN SELECT * FROM test WHERE name LIKE '%s%';

返回的结果为：

![2020080212530353.png][]

如果将SQL改为

EXPLAIN SELECT * FROM test WHERE name LIKE 's%';

执行计划结果为

![20200802125410924.png][]

### **1.3 数据类型出现隐式转换的时候不会命中索引** ###

name列的类型为varchar类型，执行以下SQL

EXPLAIN SELECT * FROM test WHERE name=1;

![20200802125830955.png][]

如果将SQL改为以下SQL

EXPLAIN SELECT * FROM test WHERE name='1';

![20200802125934200.png][]

### **1.4 复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左匹配原则），不会命中符合索引** ###

为gender、age、status三列创建复合索引：ALTER TABLE test ADD INDEX ind\_gender\_age\_status (gender,age,status);

EXPLAIN SELECT * FROM test WHERE gender = 0 AND age = 23;

![20200802142307809.png][]

可以看到是走了索引的，如果改成如下SQL

EXPLAIN SELECT * FROM test WHERE age = 23 AND gender = 0;

![20200802142354297.png][]

结果和上图一致，说明最左匹配原则和查询条件的顺序无关，但如果换成了如下SQL

EXPLAIN SELECT * FROM test WHERE status = 4;

![20200802142747438.png][]

因为查询条件缺失gender字段数据，就和最左匹配原则矛盾，造成索引失效

### **1.5 用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到** ###

EXPLAIN SELECT * FROM test WHERE status = 4 OR remark = 'DSA'

![20200802145118946.png][]

因为or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加IO访问。

### **1.6 负向条件查询不能使用索引，可以优化为in查询** ###

负向条件有：!=、<>、not in、not exists、not like等。

EXPLAIN SELECT * FROM test WHERE status !=1 AND status != 2;

![20200802151101870.png][]

负向条件不能命中缓存，可以优化为in查询，但是前提是返回的数据量占比要少，否则有可能走全表扫描

### **1.7 利用覆盖索引进行查询，避免回表** ###

被查询的列，数据能从索引中取得，即“被查询列要被所建的索引覆盖”，这样能加速查询速度。

EXPLAIN SELECT status FROM test where status=1;

![2020080216192292.png][]

但如果查询结果集变成select  \*

EXPLAIN SELECT * FROM test where status=1;

![20200802161955539.png][]

当查询其他列时，就需要回表查询，这也是为什么要避免select  \* 的原因

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NzU2Njgy_size_16_color_FFFFFF_t_70]: /images/20230208/c100113024314686aa408a762ffbbb64.png
[20200802124954121.png]: /images/20230208/af005221ad97470fa4d27fe3c2d3e57a.png
[2020080212530353.png]: /images/20230208/7f7e2388969345559bb69c36d64cb77e.png
[20200802125410924.png]: /images/20230208/13439c0d3aa64378bcf8edbb915321d4.png
[20200802125830955.png]: /images/20230208/68509421b5cd482b85b7e1b1b50fed01.png
[20200802125934200.png]: /images/20230208/c216a77c0b5e47838539dc65bec34c6d.png
[20200802142307809.png]: /images/20230208/bcf306aafe9d4fbdb64e5408cf5b62db.png
[20200802142354297.png]: /images/20230208/addc0f1d821b4bf3b1261b4d4c78a0fe.png
[20200802142747438.png]: /images/20230208/1b230a2f83534cd284d8559301f66d99.png
[20200802145118946.png]: /images/20230208/0e96fd4bdaf1427fb08da5e7de331997.png
[20200802151101870.png]: /images/20230208/aa48bb3938144b96ad6bdb26352f78a1.png
[2020080216192292.png]: /images/20230208/f4b7ae1bca124372b958a547fc2c252a.png
[20200802161955539.png]: /images/20230208/8d2dcb3662174576aa757abeac4ebc59.png