MySQL使用LIKE索引是否失效的驗證的示例
更新時間：2024年08月16日 09:46:51 作者：zxrhhm
LIKE查詢可以通過一些方法來使得LIKE查詢能夠使用索引,本文主要介紹了MySQL使用LIKE索引是否失效的驗證的示例,具有一定的參考價值,感興趣的可以了解一下
1、簡單的示例展示
在MySQL中，LIKE查詢可以通過一些方法來使得LIKE查詢能夠使用索引。以下是一些可以使用的方法：
使用前導(dǎo)通配符（%），但確保它緊跟著一個固定的字符。
避免使用后置通配符（%），只在查詢的末尾使用。
使用COLLATE來控制字符串比較的行為，使得查詢能夠使用索引。
下面是一個簡單的例子，演示如何使用LIKE查詢并且使索引有效
-- 假設(shè)我們有一個表 users，有一個索引在 name 字段上 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(255) ); -- 創(chuàng)建索引 CREATE INDEX idx_name ON users(name); -- 使用 LIKE 查詢，并且利用索引進行查詢的例子 -- 使用前導(dǎo)通配符，確保它緊跟著一個固定的字符 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%'; -- 使用索引
在實際應(yīng)用中，你需要根據(jù)你的數(shù)據(jù)庫表結(jié)構(gòu)、查詢模式和數(shù)據(jù)分布來決定是否可以使用LIKE查詢并且使索引有效。如果LIKE查詢不能使用索引，可以考慮使用全文搜索功能或者其他查詢優(yōu)化技巧。
2、實驗演示是否能正確使用索引
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有多個字段列，其中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ā)現(xiàn)選擇掃描二級索引index_name,表t_departments有多個字段列的行計劃中的 Extra=Using index condition 使用了索引下推功能。MySQL5.6 之后，增加一個索引下推功能，可以在索引遍歷過程中，對索引中包含的字段先做判斷，在存儲引擎層直接過濾掉不滿足條件的記錄后再返回給 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í)行計劃查看，發(fā)現(xiàn)選擇掃描二級索引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í)行計劃的結(jié)果 type= ALL，代表了全表掃描。
表t_deptlist 有兩個字段列的執(zhí)行計劃的結(jié)果中，可以看到 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í)行計劃的結(jié)果 type= ALL，代表了全表掃描。
表t_deptlist 有兩個字段列的執(zhí)行計劃的結(jié)果中，可以看到 key=idx_t_deptlist_department_name ，也就是說用上了二級索引，而且從 Extra 里的 Using index 說明用上了覆蓋索引。
和上一個LIKE ‘%ale%’ 一樣的結(jié)果。
3、為什么表t_deptlist where department_name LIKE ‘%ale’ 和 LIKE '%ale%'用上了二級索引
首先，這張表的字段沒有「非索引」字段，所以 SELECT * 相當(dāng)于 SELECT DEPARTMENT_ID，DEPARTMENT_NAME，這個查詢的數(shù)據(jù)都在二級索引的 B+ 樹，因為二級索引idx_t_deptlist_department_name 的 B+ 樹的葉子節(jié)點包含「索引值+主鍵值」，所以查二級索引的 B+ 樹就能查到全部結(jié)果了，這個就是覆蓋索引。
從執(zhí)行計劃里的 type 是 index，這代表著是通過全掃描二級索引的 B+ 樹的方式查詢到數(shù)據(jù)的，也就是遍歷了整顆索引樹。
而 LIKE 'Sales’和LIKE 'Sa%'查詢語句的執(zhí)行計劃中 type 是 range，表示對索引列DEPARTMENT_NAME進行范圍查詢，也就是利用了索引樹的有序性的特點，通過查詢比較的方式，快速定位到了數(shù)據(jù)行。
所以，type=range 的查詢效率會比 type=index 的高一些。
4、為什么選擇全掃描二級索引樹，而不掃描聚簇索引樹呢？
因為表t_deptlist 二級索引idx_t_deptlist_department_name 的記錄是「索引列+主鍵值」，而聚簇索引記錄的東西會更多，比如聚簇索引中的葉子節(jié)點則記錄了主鍵值、事務(wù) id、用于事務(wù)和 MVCC 的回滾指針以及所有的非索引列。
再加上表t_deptlist 只有兩個字段列，DEPARTMENT_ID是主鍵,DEPARTMENT_NAME是索引字段，因此 SELECT * 相當(dāng)于 SELECT DEPARTMENT_ID，DEPARTMENT_NAME 不用執(zhí)行回表操作。
所以， MySQL 優(yōu)化器認(rèn)為直接遍歷二級索引樹要比遍歷聚簇索引樹的成本要小的多，因此 MySQL 優(yōu)化器選擇了「全掃描二級索引樹」的方式查詢數(shù)據(jù)。
5、數(shù)據(jù)表t_departments 多了非索引字段，執(zhí)行同樣的查詢語句，為什么是全表掃描呢？
多了其他非索引字段后，select * from t_departments where DEPARTMENT_NAME LIKE ‘%ale’ OR DEPARTMENT_NAME LIKE ‘%ale%’ ; 要查詢的數(shù)據(jù)就不能只在二級索引樹里找了，得需要回表操作找到主鍵值才能完成查詢的工作，再加上是左模糊匹配，無法利用索引樹的有序性來快速定位數(shù)據(jù)，所以得在二級索引樹逐一遍歷，獲取主鍵值后，再到聚簇索引樹檢索到對應(yīng)的數(shù)據(jù)行，這樣執(zhí)行成本就會高了。
所以，優(yōu)化器認(rèn)為上面這樣的查詢過程的成本實在太高了，所以直接選擇全表掃描的方式來查詢數(shù)據(jù)。
如果數(shù)據(jù)庫表中的字段只有主鍵+二級索引，那么即使使用了左模糊匹配或左右模糊匹配，也不會走全表掃描（type=all），而是走全掃描二級索引樹(type=index)。
到此這篇關(guān)于MySQL使用LIKE索引是否失效的驗證的示例的文章就介紹到這了,更多相關(guān)MySQL LIKE索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要為大家介紹了慢查詢?nèi)罩局蠰ock_time的由來解析，以及Lock_time?包含哪些鎖等待時間、以及是怎么計算得到的，有需要的朋友可以借鑒參考下，希望能夠有所幫助2023-06-06
實例解析MySQL中的存儲過程及存儲過程的調(diào)用方法
存儲例程是存儲在數(shù)據(jù)庫服務(wù)器中的一組sql語句,通過在查詢中調(diào)用一個指定的名稱來執(zhí)行這些sql語句命令,下面就以實例解析MySQL中的存儲過程及存儲過程的調(diào)用方法:2016-05-05
本文主要介紹了Shell腳本來自動備份MySQL數(shù)據(jù)庫,腳本會備份指定數(shù)據(jù)庫或所有數(shù)據(jù)庫,按日期命名備份文件以防止覆蓋,并自動刪除N天前的舊備份以節(jié)省空間,具有一定的參考價值,感興趣的可以了解一下2025-03-03
Navicat for Mysql 字段注釋中文亂碼問題及解決
這篇文章主要介紹了Navicat for Mysql 字段注釋中文亂碼問題及解決方案,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-09-09
這篇文章主要介紹了MySQL 外鍵約束的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)MySQL，感興趣的朋友可以了解下2020-08-08
這篇文章主要介紹了MySQL查詢排序與查詢聚合函數(shù)用法,結(jié)合實例形式分析了MySQL查詢結(jié)果排序以及查詢聚合函數(shù)相關(guān)使用技巧,需要的朋友可以參考下2019-11-11
MySQL無服務(wù)及服務(wù)無法啟動的終極解決方案分享
又是MySQL的問題,之前已經(jīng)遇見過一次本地MySQL服務(wù)無法啟動的情況,現(xiàn)在又出現(xiàn)了,下面這篇文章主要給大家介紹了關(guān)于MySQL無服務(wù)及服務(wù)無法啟動的終極解決方案,需要的朋友可以參考下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)問題，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01