MySQL使用LIKE索引是否失效的验证

1、简单的示例展示

在MySQL中，LIKE查询可以通过一些方法来使得LIKE查询能够使用索引。以下是一些可以使用的方法：

• 使用前导通配符（%），但确保它紧跟着一个固定的字符。

• 避免使用后置通配符（%），只在查询的末尾使用。

• 使用COLLATE来控制字符串比较的行为，使得查询能够使用索引。

下面是一个简单的例子，演示如何使用LIKE查询并且使索引有效

-- 假设我们有一个表 users，有一个索引在 name 字段上
CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(255)
);-- 创建索引
CREATE INDEX idx_name ON users(name);-- 使用 LIKE 查询，并且利用索引进行查询的例子
-- 使用前导通配符，确保它紧跟着一个固定的字符
SELECT * FROM users WHERE name LIKE 'A%'; -- 使用索引-- 避免使用后置通配符
SELECT * FROM users WHERE name LIKE '%A'; -- 不使用索引-- 使用 COLLATE 来确保比较符合特定的语言或字符集规则
SELECT * FROM users WHERE name COLLATE utf8mb4_unicode_ci LIKE '%A%'; -- 使用索引

在实际应用中，你需要根据你的数据库表结构、查询模式和数据分布来决定是否可以使用LIKE查询并且使索引有效。如果LIKE查询不能使用索引，可以考虑使用全文搜索功能或者其他查询优化技巧。

2、实验演示是否能正确使用索引

2.1、表及数据准备

准备两张表 t_departments 和 t_deptlist

(root@192.168.80.85)[superdb]> desc t_departments;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| DEPARTMENT_ID   | int         | NO   | PRI | NULL    |       |
| DEPARTMENT_NAME | varchar(30) | YES  |     | NULL    |       |
| MANAGER_ID      | int         | YES  |     | NULL    |       |
| LOCATION_ID     | int         | YES  | MUL | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)(root@192.168.80.85)[superdb]> create table t_deptlist as select DEPARTMENT_ID,DEPARTMENT_NAME from t_departments;
Query OK, 29 rows affected (0.09 sec)
Records: 29  Duplicates: 0  Warnings: 0(root@192.168.80.85)[superdb]> desc t_deptlist;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| DEPARTMENT_ID   | int         | NO   |     | NULL    |       |
| DEPARTMENT_NAME | varchar(30) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)(root@192.168.80.85)[superdb]> alter table t_deptlist add constraint pk_t_deptlist_id primary key(DEPARTMENT_ID);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0(root@192.168.80.85)[superdb]> create index idx_t_deptlist_department_name on t_deptlist(department_name);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0(root@192.168.80.85)[superdb]> show index from t_departments;
+---------------+------------+-----------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table         | Non_unique | Key_name              | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------------+------------+-----------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_departments |          0 | PRIMARY               |            1 | DEPARTMENT_ID   | A         |          29 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| t_departments |          1 | idx_t_department_name |            1 | DEPARTMENT_NAME | A         |          29 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------------+------------+-----------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)(root@192.168.80.85)[superdb]> show index from t_deptlist;
+------------+------------+--------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name                       | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+--------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_deptlist |          0 | PRIMARY                        |            1 | DEPARTMENT_ID   | A         |          29 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| t_deptlist |          1 | idx_t_deptlist_department_name |            1 | DEPARTMENT_NAME | A         |          29 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+------------+------------+--------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

表t_departments有多个字段列，其中DEPARTMENT_ID是主键,DEPARTMENT_NAME是索引字段，其它是非索引字段列

表t_deptlist有两个字段，其中DEPARTMENT_ID是主键,DEPARTMENT_NAME是索引字段

2.2、 执行 where DEPARTMENT_NAME LIKE ‘Sales’

(root@192.168.80.85)[superdb]> explain select * from t_departments where DEPARTMENT_NAME LIKE 'Sales';
+----+-------------+---------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table         | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_departments | NULL       | range | idx_t_department_name | idx_t_department_name | 123     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)(root@192.168.80.85)[superdb]> explain select * from t_deptlist where DEPARTMENT_NAME LIKE 'Sales';
+----+-------------+------------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys                  | key                            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_deptlist | NULL       | range | idx_t_deptlist_department_name | idx_t_deptlist_department_name | 123     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

执行计划查看，发现选择扫描二级索引index_name,表t_departments有多个字段列的行计划中的 Extra=Using index condition 使用了索引下推功能。MySQL5.6 之后，增加一个索引下推功能，可以在索引遍历过程中，对索引中包含的字段先做判断，在存储引擎层直接过滤掉不满足条件的记录后再返回给 MySQL Server 层，减少回表次数，从而提升了性能。

2.3、 执行 where DEPARTMENT_NAME LIKE ‘Sa%’

(root@192.168.80.85)[superdb]> explain select * from t_departments where DEPARTMENT_NAME LIKE 'Sa%';
+----+-------------+---------------+------------+-------+-----------------------+-----------------------+-------------+------+------+----------+-----------------------+
| id | select_type | table         | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------------+------------+-------+-----------------------+-----------------------+-------------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_departments | NULL       | range | idx_t_department_name | idx_t_department_name | 123     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)(root@192.168.80.85)[superdb]> explain select * from t_deptlist where DEPARTMENT_NAME LIKE 'Sa%';
+----+-------------+------------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys                  | key                            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_deptlist | NULL       | range | idx_t_deptlist_department_name | idx_t_deptlist_department_name | 123     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

执行计划查看，发现选择扫描二级索引index_name

2.4、 执行 where DEPARTMENT_NAME LIKE ‘%ale%’

(root@192.168.80.85)[superdb]> explain select * from t_departments where DEPARTMENT_NAME LIKE '%ale%';
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_departments | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   29 |    11.11 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)(root@192.168.80.85)[superdb]> explain select * from t_deptlist where DEPARTMENT_NAME LIKE '%ale%';
+----+-------------+------------+------------+-------+---------------+--------------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key                            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+--------------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_deptlist | NULL       | index | NULL          | idx_t_deptlist_department_name | 123     | NULL |   29 |    11.11 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+--------------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

表t_departments有多个字段列的执行计划的结果 type= ALL，代表了全表扫描。
表t_deptlist 有两个字段列的执行计划的结果中，可以看到 key=idx_t_deptlist_department_name ，也就是说用上了二级索引，而且从 Extra 里的 Using index 说明用上了覆盖索引。

2.5、 执行 where DEPARTMENT_NAME LIKE ‘%ale’

(root@192.168.80.85)[superdb]> explain select * from t_departments where DEPARTMENT_NAME LIKE '%ale';
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_departments | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   29 |    11.11 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)(root@192.168.80.85)[superdb]> explain select * from t_deptlist where DEPARTMENT_NAME LIKE '%ale';
+----+-------------+------------+------------+-------+---------------+--------------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key                            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+--------------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_deptlist | NULL       | index | NULL          | idx_t_deptlist_department_name | 123     | NULL |   29 |    11.11 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+--------------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

表t_departments有多个字段列的执行计划的结果 type= ALL，代表了全表扫描。
表t_deptlist 有两个字段列的执行计划的结果中，可以看到 key=idx_t_deptlist_department_name ，也就是说用上了二级索引，而且从 Extra 里的 Using index 说明用上了覆盖索引。
和上一个LIKE ‘%ale%’ 一样的结果。

3、为什么表t_deptlist where department_name LIKE ‘%ale’ 和 LIKE '%ale%'用上了二级索引

首先，这张表的字段没有「非索引」字段，所以 SELECT * 相当于 SELECT DEPARTMENT_ID，DEPARTMENT_NAME，这个查询的数据都在二级索引的 B+ 树，因为二级索引idx_t_deptlist_department_name 的 B+ 树的叶子节点包含「索引值+主键值」，所以查二级索引的 B+ 树就能查到全部结果了，这个就是覆盖索引。

从执行计划里的 type 是 index，这代表着是通过全扫描二级索引的 B+ 树的方式查询到数据的，也就是遍历了整颗索引树。

而 LIKE 'Sales’和LIKE 'Sa%'查询语句的执行计划中 type 是 range，表示对索引列DEPARTMENT_NAME进行范围查询，也就是利用了索引树的有序性的特点，通过查询比较的方式，快速定位到了数据行。

所以，type=range 的查询效率会比 type=index 的高一些。

4、为什么选择全扫描二级索引树，而不扫描聚簇索引树呢？

因为表t_deptlist 二级索引idx_t_deptlist_department_name 的记录是「索引列+主键值」，而聚簇索引记录的东西会更多，比如聚簇索引中的叶子节点则记录了主键值、事务 id、用于事务和 MVCC 的回滚指针以及所有的非索引列。

再加上表t_deptlist 只有两个字段列，DEPARTMENT_ID是主键,DEPARTMENT_NAME是索引字段，因此 SELECT * 相当于 SELECT DEPARTMENT_ID，DEPARTMENT_NAME 不用执行回表操作。

所以， MySQL 优化器认为直接遍历二级索引树要比遍历聚簇索引树的成本要小的多，因此 MySQL 优化器选择了「全扫描二级索引树」的方式查询数据。

5、数据表t_departments 多了非索引字段，执行同样的查询语句，为什么是全表扫描呢？

多了其他非索引字段后，select * from t_departments where DEPARTMENT_NAME LIKE ‘%ale’ OR DEPARTMENT_NAME LIKE ‘%ale%’ ; 要查询的数据就不能只在二级索引树里找了，得需要回表操作找到主键值才能完成查询的工作，再加上是左模糊匹配，无法利用索引树的有序性来快速定位数据，所以得在二级索引树逐一遍历，获取主键值后，再到聚簇索引树检索到对应的数据行，这样执行成本就会高了。

所以，优化器认为上面这样的查询过程的成本实在太高了，所以直接选择全表扫描的方式来查询数据。

如果数据库表中的字段只有主键+二级索引，那么即使使用了左模糊匹配或左右模糊匹配，也不会走全表扫描（type=all），而是走全扫描二级索引树(type=index)。