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