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)

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)。