一、前言
在日常開發(fā)中，我們往往會給表加各種索引，來提高 MySQL 的檢索效率。
但我們有時會遇到明明給字段加了索引，并沒有走索引的Case。 進而導致 MySQL 產(chǎn)生慢查詢。
嚴重場景下，甚至出現(xiàn)主從延遲、數(shù)據(jù)庫拖垮的極端事故。
本文梳理出索引失效的幾種常見場景給大家參考。
二、技術(shù)基礎(chǔ)
Explain 命令使用
只要我們在 SQL 前加上 explain，就可以分析出，當前環(huán)境下 MySQL 的“查詢方式”以及“索引選擇”。
首先大致看下每個字段的含義：
| 列名 | 含義 |
|---|---|
| id | 每個select操作的唯一標識 |
| select_type | 查詢的類型，我們可以根據(jù)該字段判斷查詢的性質(zhì)，包括查詢是簡單/復雜查詢類型 |
| table | 查詢訪問表的別名 |
| type | 關(guān)聯(lián)的類型，mysql把查詢過程都視為關(guān)聯(lián)，不管是單表/多表。這個字段也是衡量查詢性能的關(guān)鍵字段之一 |
| possible_keys | 查詢可能會使用哪些索引，這列是基于查詢訪問的列來判斷的 |
| key | mysql最終決定使用哪個索引（這個索引不一定出現(xiàn)在possible_keys中） |
| key_len | mysql在索引里使用的字節(jié)數(shù)，我們可以根據(jù)它推斷具體使用了索引中的哪些字段 |
| ref | 查找所用的列/常量 |
| rows | mysql估算的預計掃描行數(shù)，這個數(shù)字和實際掃描的行數(shù)可能相差甚遠，包括limit語句對于這個估算值也是不起作用的 |
| filtered | 表里符合條件的記錄數(shù)的百分比的估計，我們可以用這個字段大致估計表關(guān)聯(lián)時關(guān)聯(lián)的記錄數(shù) |
| extra | 包含一些額外信息，也是我們優(yōu)化時需要重點關(guān)注的字段 |
Type（重點看）
type 列表示了 MySQL 關(guān)聯(lián)的類型，它代表了mysql是如何在表里找數(shù)據(jù)的。
下面按性能從高到低的順序介紹type類型： 以下四種類型，說明 “性能很好，一般無需優(yōu)化” ：
-
system：表里就一條數(shù)據(jù) -
const：一般是針對主鍵/唯一鍵的等值查詢，mysql可以把這類查詢優(yōu)化為一個常量表達式 -
eq_ref：一般出現(xiàn)在多表join時，針對主鍵/唯一鍵的等值查詢，mysql知道只需要返回一條記錄 -
ref：多表 join 時，針對索引字段的查詢
以下幾種類型，需要 “看具體情況，決定是否要優(yōu)化” ：
-
fulltext：關(guān)聯(lián)使用了全文索引 -
ref_or_null：查詢走了索引，但是除此之外還要判斷字段是不是null，如果出現(xiàn)這種類型，可以考慮這個字段是否有為空的必要 -
index_merge：使用了索引合并優(yōu)化，如果高頻出現(xiàn)，可以考慮是不是索引設(shè)計有問題。 -
unique_subquery：in 子句中的子查詢，如果只訪問主鍵/唯一鍵可能會出現(xiàn)這種 type，并不常見 -
index_subquery：同樣是 in 里的子查詢，訪問了索引列，并不常見 -
range：對索引字段的范圍掃描，一般出現(xiàn)在帶有比較的查詢語句中，一些in和or的查詢也會導致這種類型的掃描
以下兩種類型，需要 “優(yōu)化 & 避免出現(xiàn)” ：
-
index：按索引進行全表掃描，如果查詢不是覆蓋索引的，可能會產(chǎn)生很大量的隨機IO -
all：全表掃描
三、準備工作
- 建一張
user表
CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`user_id` bigint(20) NOT NULL COMMENT '用戶uuid',
`user_name` varchar(64) DEFAULT '' COMMENT '用戶昵稱',
`email` varchar(64) DEFAULT '' COMMENT '郵箱',
`age` tinyint(4) DEFAULT '1' COMMENT '年齡',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建日期',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日期',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_userid` (`user_id`),
KEY `idx_username_email_age` (`user_name`,`email`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';
復制代碼- 初始化一些數(shù)據(jù)
-- 創(chuàng)建存儲過程
delimiter $
CREATE PROCEDURE insert_user(IN limit_num int)
BEGIN
DECLARE i INT DEFAULT 10;
DECLARE user_id bigint(20) ;
DECLARE username varchar(64) ;
DECLARE email varchar(64) ;
DECLARE age TINYINT(4) DEFAULT 1;
WHILE i < limit_num DO
SET user_id = FLOOR(RAND() * 100000000);
SET username = CONCAT("647-",i);
SET email = CONCAT(username,"@163.com");
SET age = FLOOR(RAND() * 100);
INSERT INTO `user` VALUES (NULL, user_id, username, email, age, NOW(), NOW());
SET i = i + 1;
END WHILE;
END $
-- 調(diào)用存儲過程
call insert_user(100);
復制代碼四、幾種常見的索引失效場景
1. 聯(lián)合索引不滿足最左匹配原則
- 錯誤示例：
explain select * from user where age = 20 and email = "647@163.com";
復制代碼- 分析結(jié)果：
- 優(yōu)化思路：
根據(jù)業(yè)務(wù)場景，合理的建立相應的聯(lián)合索引。
2. 范圍查詢，數(shù)量級過大，默認走全表掃描
一般來說，MySQL 判斷數(shù)量級返回超過全數(shù)的 10% ~ 30%（或者達到某個閾值），默認會走全表掃描。
- 錯誤示例：
explain select * from user where user_id > 10;
復制代碼- 分析結(jié)果：
-
產(chǎn)生原因：MySQL 優(yōu)化器判斷走索引&回表帶來的消耗，比走全表還要多。因此，會走全表掃描。
-
優(yōu)化思路：
根據(jù)業(yè)務(wù)場景，預估返回數(shù)量級。如果數(shù)量級過大，可以分批拉取。
反之，可以加limit或者force index走索引。
3. 索引列參與運算
- 錯誤示例：
explain select * from user where id + 1 = 2;
復制代碼- 分析結(jié)果：
- 優(yōu)化思路：
不要用數(shù)據(jù)庫做運算，不浪費寶貴的數(shù)據(jù)庫資源。
4. 索引列使用了函數(shù)
- 錯誤示例：
explain select * from user where SUBSTR(user_id,1,3) = '100';
復制代碼- 分析結(jié)果：
- 優(yōu)化思路：
不要用數(shù)據(jù)庫做函數(shù)運算，不浪費寶貴的數(shù)據(jù)庫資源。
5. 錯誤的 like 使用
- 錯誤示例：
explain select * from user where user_name like '%00%';
復制代碼- 分析結(jié)果：
- 優(yōu)化思路：
嚴禁使用左%匹配，要用只能用右%匹配。
如果實在有業(yè)務(wù)場景，可以使用ES做。
6. 隱式類型轉(zhuǎn)換
- 錯誤示例：
explain select * from user where user_name = 647;
復制代碼- 分析結(jié)果：
user_name 是 varchar 類型，傳入 INT 比較，會產(chǎn)生 INT -> varchar 的隱式類型轉(zhuǎn)換導致索引失效。
- 特殊 Case：
explain select * from user where user_id = "647";
復制代碼- 分析結(jié)果：
user_id 是 bigint 類型，如果傳入字符串比較。雖然產(chǎn)生隱式轉(zhuǎn)換，但不會導致索引失效。
- 優(yōu)化思路：
注意字段類型，避免隱式轉(zhuǎn)換。
7. OR 使用不當
- 錯誤示例：
explain select * from user where user_name = "647" or email = "647@163.com";
復制代碼- 分析結(jié)果：
- 優(yōu)化思路：
確保
or的兩邊都要有索引。
8. 兩個索引列做比較
- 錯誤示例：
explain select * from user where user_id > id;
復制代碼- 分析結(jié)果：
- 優(yōu)化思路：
不要對兩個列做比較。
9. 非主鍵列，加上 not，索引失效
in 會走索引，not in 不會走索引 exists 會走索引，not exists 不會走索引 is null 會走索引，is not null 不會走索引
- 錯誤示例：
explain select * from user where user_id not in (647)
復制代碼- 分析結(jié)果：
- 優(yōu)化思路：
不要用 not。
10. 非主鍵列，order by 可能導致索引失效
具體是否失效，和使用的 MySQL 版本也有一定關(guān)系。 具體需要根據(jù) explain 分析。
如果 MySQL 版本支持，需要注意滿足“最左原則”。
- 錯誤示例：
explain select * from user order by user_id;
復制代碼- 分析結(jié)果：
文章來源：http://www.zghlxwxcb.cn/news/detail-451730.html
- 優(yōu)化思路：
非主鍵列，盡量不要用 order by。實在要用，需要先用 explain 分析是否可以走索引。
如果條件允許，可以用 ES 代替。文章來源地址http://www.zghlxwxcb.cn/news/detail-451730.html
到了這里，關(guān)于MySQL 如何優(yōu)化慢查詢？的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
