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慢查詢?nèi)罩局械腖ock_time由來(lái)解析
這篇文章主要為大家介紹了慢查詢?nèi)罩局蠰ock_time的由來(lái)解析，以及Lock_time?包含哪些鎖等待時(shí)間、以及是怎么計(jì)算得到的，有需要的朋友可以借鑒參考下，希望能夠有所幫助2023-06-06
實(shí)例解析MySQL中的存儲(chǔ)過(guò)程及存儲(chǔ)過(guò)程的調(diào)用方法
存儲(chǔ)例程是存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中的一組sql語(yǔ)句,通過(guò)在查詢中調(diào)用一個(gè)指定的名稱來(lái)執(zhí)行這些sql語(yǔ)句命令,下面就以實(shí)例解析MySQL中的存儲(chǔ)過(guò)程及存儲(chǔ)過(guò)程的調(diào)用方法:2016-05-05
本文主要介紹了Shell腳本來(lái)自動(dòng)備份MySQL數(shù)據(jù)庫(kù),腳本會(huì)備份指定數(shù)據(jù)庫(kù)或所有數(shù)據(jù)庫(kù),按日期命名備份文件以防止覆蓋,并自動(dòng)刪除N天前的舊備份以節(jié)省空間,具有一定的參考價(jià)值,感興趣的可以了解一下2025-03-03
Navicat for Mysql 字段注釋中文亂碼問(wèn)題及解決
這篇文章主要介紹了Navicat for Mysql 字段注釋中文亂碼問(wèn)題及解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-09-09
這篇文章主要介紹了MySQL 外鍵約束的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)MySQL，感興趣的朋友可以了解下2020-08-08
這篇文章主要介紹了MySQL查詢排序與查詢聚合函數(shù)用法,結(jié)合實(shí)例形式分析了MySQL查詢結(jié)果排序以及查詢聚合函數(shù)相關(guān)使用技巧,需要的朋友可以參考下2019-11-11
MySQL無(wú)服務(wù)及服務(wù)無(wú)法啟動(dòng)的終極解決方案分享
又是MySQL的問(wèn)題,之前已經(jīng)遇見(jiàn)過(guò)一次本地MySQL服務(wù)無(wú)法啟動(dòng)的情況,現(xiàn)在又出現(xiàn)了,下面這篇文章主要給大家介紹了關(guān)于MySQL無(wú)服務(wù)及服務(wù)無(wú)法啟動(dòng)的終極解決方案,需要的朋友可以參考下2022-06-06
MySQL中l(wèi)ike模糊查詢的優(yōu)化方法小結(jié)
本文介紹了五種優(yōu)化MySQL中l(wèi)ike模糊查詢的方法,主要包含后綴匹配走索引、反向索引、縮小搜索范圍、使用緩存和借助全文搜索引擎這幾種,感興趣的可以了解一下2024-11-11
這篇文章主要介紹了Linux/Windows系統(tǒng)下mysql 常用的命令,需要的朋友可以參考下2014-07-07
本文主要介紹了淺談MySQL timestamp(3)問(wèn)題，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01