MySQL-索引失效

1、like

1.1、like ‘%xxx’

匹配以任意字符开头并以 xxx 结尾的所有记录。
当使用%xxx，B-tree没办法从有序索引中找到起始位置。一般情况下引擎开始使用全表扫描，当然，也有不一般的情况，假设有表结构如下：

| departments | CREATE TABLE `departments` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |

当执行查询

select * from departments where dept_name like '%s';

索引dept_name刚好可以覆盖select *，触发了覆盖索引。相比于全表扫，全索引扫描此处的消耗会更少。

+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table       | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | departments | NULL       | index | NULL          | dept_name | 122     | NULL |    9 |    11.11 | Using where; Using index |
+----+-------------+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+