MySQL使用LIKE索引是否失效的驗證的示例
更新時間：2024年08月16日 09:46:51 作者：zxrhhm
LIKE查詢可以通過一些方法來使得LIKE查詢能夠使用索引,本文主要介紹了MySQL使用LIKE索引是否失效的驗證的示例,具有一定的參考價值,感興趣的可以了解一下
1、簡單的示例展示
在MySQL中，LIKE查詢可以通過一些方法來使得LIKE查詢能夠使用索引。以下是一些可以使用的方法：
使用前導通配符（%），但確保它緊跟著一個固定的字符。
避免使用后置通配符（%），只在查詢的末尾使用。
使用COLLATE來控制字符串比較的行為，使得查詢能夠使用索引。
下面是一個簡單的例子，演示如何使用LIKE查詢并且使索引有效
-- 假設我們有一個表 users，有一個索引在 name 字段上 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(255) ); -- 創(chuàng)建索引 CREATE INDEX idx_name ON users(name); -- 使用 LIKE 查詢，并且利用索引進行查詢的例子 -- 使用前導通配符，確保它緊跟著一個固定的字符 SELECT * FROM users WHERE name LIKE 'A%'; -- 使用索引 -- 避免使用后置通配符 SELECT * FROM users WHERE name LIKE '%A'; -- 不使用索引 -- 使用 COLLATE 來確保比較符合特定的語言或字符集規(guī)則 SELECT * FROM users WHERE name COLLATE utf8mb4_unicode_ci LIKE '%A%'; -- 使用索引
在實際應用中，你需要根據你的數據庫表結構、查詢模式和數據分布來決定是否可以使用LIKE查詢并且使索引有效。如果LIKE查詢不能使用索引，可以考慮使用全文搜索功能或者其他查詢優(yōu)化技巧。
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、 執(zhí)行 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)
執(zhí)行計劃查看，發(fā)現選擇掃描二級索引index_name,表t_departments有多個字段列的行計劃中的 Extra=Using index condition 使用了索引下推功能。MySQL5.6 之后，增加一個索引下推功能，可以在索引遍歷過程中，對索引中包含的字段先做判斷，在存儲引擎層直接過濾掉不滿足條件的記錄后再返回給 MySQL Server 層，減少回表次數，從而提升了性能。
2.3、 執(zhí)行 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)
執(zhí)行計劃查看，發(fā)現選擇掃描二級索引index_name
2.4、 執(zhí)行 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有多個字段列的執(zhí)行計劃的結果 type= ALL，代表了全表掃描。
表t_deptlist 有兩個字段列的執(zhí)行計劃的結果中，可以看到 key=idx_t_deptlist_department_name ，也就是說用上了二級索引，而且從 Extra 里的 Using index 說明用上了覆蓋索引。
2.5、 執(zhí)行 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有多個字段列的執(zhí)行計劃的結果 type= ALL，代表了全表掃描。
表t_deptlist 有兩個字段列的執(zhí)行計劃的結果中，可以看到 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+ 樹的葉子節(jié)點包含「索引值+主鍵值」，所以查二級索引的 B+ 樹就能查到全部結果了，這個就是覆蓋索引。
從執(zhí)行計劃里的 type 是 index，這代表著是通過全掃描二級索引的 B+ 樹的方式查詢到數據的，也就是遍歷了整顆索引樹。
而 LIKE 'Sales’和LIKE 'Sa%'查詢語句的執(zhí)行計劃中 type 是 range，表示對索引列DEPARTMENT_NAME進行范圍查詢，也就是利用了索引樹的有序性的特點，通過查詢比較的方式，快速定位到了數據行。
所以，type=range 的查詢效率會比 type=index 的高一些。
4、為什么選擇全掃描二級索引樹，而不掃描聚簇索引樹呢？
因為表t_deptlist 二級索引idx_t_deptlist_department_name 的記錄是「索引列+主鍵值」，而聚簇索引記錄的東西會更多，比如聚簇索引中的葉子節(jié)點則記錄了主鍵值、事務 id、用于事務和 MVCC 的回滾指針以及所有的非索引列。
再加上表t_deptlist 只有兩個字段列，DEPARTMENT_ID是主鍵,DEPARTMENT_NAME是索引字段，因此 SELECT * 相當于 SELECT DEPARTMENT_ID，DEPARTMENT_NAME 不用執(zhí)行回表操作。
所以， MySQL 優(yōu)化器認為直接遍歷二級索引樹要比遍歷聚簇索引樹的成本要小的多，因此 MySQL 優(yōu)化器選擇了「全掃描二級索引樹」的方式查詢數據。
5、數據表t_departments 多了非索引字段，執(zhí)行同樣的查詢語句，為什么是全表掃描呢？
多了其他非索引字段后，select * from t_departments where DEPARTMENT_NAME LIKE ‘%ale’ OR DEPARTMENT_NAME LIKE ‘%ale%’ ; 要查詢的數據就不能只在二級索引樹里找了，得需要回表操作找到主鍵值才能完成查詢的工作，再加上是左模糊匹配，無法利用索引樹的有序性來快速定位數據，所以得在二級索引樹逐一遍歷，獲取主鍵值后，再到聚簇索引樹檢索到對應的數據行，這樣執(zhí)行成本就會高了。
所以，優(yōu)化器認為上面這樣的查詢過程的成本實在太高了，所以直接選擇全表掃描的方式來查詢數據。
如果數據庫表中的字段只有主鍵+二級索引，那么即使使用了左模糊匹配或左右模糊匹配，也不會走全表掃描（type=all），而是走全掃描二級索引樹(type=index)。
到此這篇關于MySQL使用LIKE索引是否失效的驗證的示例的文章就介紹到這了,更多相關MySQL LIKE索引內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
mysql中全連接full join...on...的用法說明
這篇文章主要介紹了mysql中全連接full join...on...的用法說明，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-03-03
這篇文章主要介紹了MySQL提升大量數據查詢效率的優(yōu)化神器，文章圍繞主題展開詳細的內容介紹，具有一定的參考價值，需要的小伙伴可以參考一下2022-07-07
這篇文章主要介紹了MySQL的子查詢及相關優(yōu)化學習教程,使用子查詢時需要注意其對數據庫性能的影響,需要的朋友可以參考下2015-11-11
本文主要介紹了阿里云服務器MySQL與nacos配置,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2026-03-03
這篇文章主要為大家詳細介紹了windows下mysql?8.0.27?安裝配置圖文教程，文中安裝步驟介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-06-06
我們需要獲取數據庫表數據的特定子集時，可以使用where子句指定搜索條件進行過濾。本文主要介紹了MySQL之where使用，具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-11-11
這篇文章主要介紹了mysql慢查詢mysqldumpslow的使用詳解,本文給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友參考下吧2025-06-06
在OpenSUSE下裝上MySQL后，發(fā)現無法選擇添加事務支持數據引擎InnoDB。2009-11-11
這篇文章主要介紹了MySQL的FreeList機制的相關資料，幫助大家更好的理解和使用MySQL 數據庫，感興趣的朋友可以了解下2020-11-11
Mysql?5.7?新特性之?json?類型的增刪改查操作和用法
這篇文章主要介紹了Mysql?5.7?新特性之json?類型的增刪改查,主要通過代碼介紹mysql?json類型的增刪改查等基本操作的用法，需要的朋友可以參考下2022-09-09