【MySQL】第七篇：索引失效常见场景

案例

对 city 表创建联合索引：

mysql> desc city;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint       | NO   | PRI | NULL    | auto_increment |
| center      | varchar(255) | YES  |     | NULL    |                |
| city_id     | varchar(255) | YES  | MUL | NULL    |                |
| name        | varchar(255) | YES  |     | NULL    |                |
| province_id | varchar(255) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> create index idx_cid_name_pid on city(city_id, name, province_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from city;
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| city  |          0 | PRIMARY          |            1 | id          | A         |         394 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| city  |          1 | idx_cid_name_pid |            1 | city_id     | A         |         394 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| city  |          1 | idx_cid_name_pid |            2 | name        | A         |         394 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| city  |          1 | idx_cid_name_pid |            3 | province_id | A         |         394 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)

一、全值匹配要常用

全值匹配指的是，查询的字段在索引中都可以匹配到，效率很高；
口诀：全值匹配要常用
SQL 中查询字段的顺序，跟使用索引中字段的顺序没有关系。优化器会在不影响 SQL 执行结果的前提下，给进行自动优化。

二、最佳左前缀法则

口诀：空中楼阁不要有

使用索引字段顺序的不同会导致，索引无法充分使用，甚至索引失效！因此，不要对组合索引尽量不要跨索引列查询(即空中楼阁)；

原因：使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

三、不要在索引列上做任何计算

在索引列上做任何操作(计算、函数、(自动 or 手动)类型转换)，会导致索引失效而转向全表扫描。因此，不要在索引列上做任何函数计算

3.1、在索引列上使用了函数导致索引失效

口诀：等号左边无函数计算

3.2、在索引列上使用了类型转换导致索引失效

口诀：等号右边无类型转换

四、索引列上不能有范围查询

索引列上不能有范围查询，将可能做范围查询的索引字段尽量放最后

五、尽量使用覆盖索引

不要写 select *，尽量使用覆盖索引(即查询列和索引列字段一致)

六、尽量不使用 (!= 、<、>)

使用 (!= 、<、>) 的时候，会导致索引失效

七、字段的 is not null 和 is null

当字段允许为 Null 的条件下：is not null 用不到索引，is null 可以用到索引。

八、like 的前后模糊匹配

前缀不能出现模糊匹配

九、减少使用 or

减少使用or，使用 union all 或者 union 来替代

口诀

全值匹配我最爱，最左前缀要遵守;
带头大哥不能死，中间兄弟不能断;
索引列上少计算，范围之后全失效;
LIKE 百分写最右，覆盖索引不写*;
不等空值还有 OR，索引影响要注意;
VAR 引号不可丢，SQL 优化有诀窍。

注意：MySQL各个版本都不尽相同，因此需要多实践