在高并發(fā)和大數(shù)據(jù)量的生產(chǎn)環(huán)境中，MySQL的查詢性能至關(guān)重要。本文圍繞“索引失效”這一常見問題展開，結(jié)合真實(shí)業(yè)務(wù)場(chǎng)景，從問題現(xiàn)象、定位過程、根因分析、優(yōu)化改進(jìn)到預(yù)防監(jiān)控，帶你深入排查并徹底解決索引失效引發(fā)的性能瓶頸。

一、問題現(xiàn)象描述

• 響應(yīng)時(shí)間突增：某關(guān)鍵查詢的平均響應(yīng)時(shí)間由 < 50ms 突然飆升至 500ms~2s。
• 連接數(shù)激增：慢查詢堆積導(dǎo)致數(shù)據(jù)庫連接數(shù)持續(xù)上升，甚至出現(xiàn)連接超時(shí)。
• CPU/IO突然飆高：結(jié)合監(jiān)控，發(fā)現(xiàn) MySQL 進(jìn)程的 CPU 利用率或 IO 等待明顯提升。
• 業(yè)務(wù)鏈路阻塞：依賴該查詢的請(qǐng)求出現(xiàn)排隊(duì)，業(yè)務(wù)整體吞吐下降。

這些都是典型的索引失效引起的性能下降現(xiàn)象。

二、問題定位過程

1. 開啟慢查詢?nèi)罩?/h3>

在 my.cnf 中配置：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1    # 記錄超過1秒的查詢
log_queries_not_using_indexes = 1   # 記錄未使用索引的查詢

重啟后，復(fù)現(xiàn)業(yè)務(wù)，收集慢查詢?nèi)罩尽?/p>

2. 使用EXPLAIN分析執(zhí)行計(jì)劃

EXPLAIN FORMAT=JSON
SELECT *
FROM orders
WHERE user_id = 123 AND status = 'PENDING';

通過輸出，重點(diǎn)關(guān)注：

• "type" 字段：ALL/NOSCAN 表示全表掃描或索引失效。
• "key" 字段：顯示實(shí)際使用的索引；NULL 表示未使用索引。
• "rows"：掃描行數(shù)巨大時(shí)往往意味著全表掃描。

3. 監(jiān)控視圖查詢

-- 當(dāng)前正在執(zhí)行的查詢及其狀態(tài)
SELECT * FROM information_schema.PROCESSLIST
WHERE COMMAND = 'Query';

-- 索引統(tǒng)計(jì)信息
SHOW INDEX FROM orders;

通過上述步驟，可以快速定位哪些 SQL 未走索引或全表掃描。

三、根因分析與解決

場(chǎng)景1：范圍查詢導(dǎo)致索引失效

SELECT * FROM orders
WHERE user_id = 123
  AND created_at > '2023-01-01';

如果在 (user_id, created_at) 的聯(lián)合索引上，MySQL 可以使用前綴索引；但

WHERE created_at > '2023-01-01'
  AND user_id = 123;

順序顛倒可能導(dǎo)致只命中 created_at 單列索引，或在某些版本下索引失效。

解決：保證 WHERE 中字段順序與索引列順序一致；必要時(shí)拆分查詢。

場(chǎng)景2：前綴模糊匹配

WHERE username LIKE '%john%'

以上寫法無法利用 B-tree 索引。

解決：使用倒排索引（如 Elasticsearch），或避免前綴通配符，改為 john%。

場(chǎng)景3：函數(shù)/隱式類型轉(zhuǎn)換

WHERE DATE(created_at) = '2023-07-10'

DATE() 會(huì)對(duì) created_at 列做全表函數(shù)掃描。

解決：使用范圍查詢：

WHERE created_at >= '2023-07-10 00:00:00'
  AND created_at < '2023-07-11 00:00:00'

或?yàn)?DATE(created_at) 創(chuàng)建函數(shù)索引（MySQL 8.0+）。

場(chǎng)景4：列順序與索引不匹配

對(duì)于復(fù)合索引 (a,b,c)，查詢只使用了 (c,b) 的順序，會(huì)導(dǎo)致索引失效。

解決：根據(jù)實(shí)際查詢場(chǎng)景拆分或重建索引，保證常用查詢字段順序一致。

場(chǎng)景5：數(shù)據(jù)傾斜與索引選擇不當(dāng)

當(dāng) status 取值極度不均衡（如 99% 為 'DONE'），WHERE status='DONE' 雖有索引，但效果不顯著。執(zhí)行計(jì)劃可能選擇全表掃描。

解決：考慮字段基數(shù)，避免為高度傾斜字段單獨(dú)建立索引，或使用覆蓋索引（覆蓋查詢所需字段）。

四、優(yōu)化改進(jìn)措施

1.合理拆分索引與覆蓋索引

對(duì)于頻繁查詢字段，創(chuàng)建覆蓋索引，例如：

CREATE INDEX idx_user_status ON orders(user_id, status, created_at);

EXPLAIN 時(shí)看到 Using index condition 則說明走了覆蓋索引，無需回表。

2.建立監(jiān)控告警

• 結(jié)合 pt-query-digest 定期分析慢查詢?nèi)罩尽?/li>
• 利用 PMM（Percona Monitoring and Management）監(jiān)控索引使用率和查詢吞吐。

3.定期整理/重建索引

大表可使用在線 DDL：

ALTER TABLE orders
  DROP INDEX idx_old,
  ADD INDEX idx_new(user_id, status, created_at)
  LOCK=NONE;

避免索引碎片。

4.查詢參數(shù)化和預(yù)編譯

使用 PreparedStatement 避免 SQL 拼接導(dǎo)致執(zhí)行計(jì)劃不命中緩存。

5.歸檔與分表分庫

• 對(duì)歷史冷數(shù)據(jù)做歸檔操作，減小單表大小。
• 對(duì)業(yè)務(wù)熱點(diǎn)分庫分表，進(jìn)一步提升查詢性能。

五、預(yù)防措施與監(jiān)控

1.建立 SQL 規(guī)范審查機(jī)制

新增或改動(dòng) SQL 前進(jìn)行 EXPLAIN 審核。

2.自動(dòng)化測(cè)試

在 CI/CD 流程中加入慢查詢聯(lián)調(diào)檢測(cè)，對(duì)索引失效提前報(bào)警。

3.定期培訓(xùn)與分享

建立經(jīng)驗(yàn)分享白皮書，宣貫索引原理與查詢優(yōu)化。

4.健康檢查腳本

周期執(zhí)行腳本，統(tǒng)計(jì)未使用的索引、低效索引和高瓶頸 SQL。

通過以上系統(tǒng)化的索引失效排查與優(yōu)化方案，能夠幫助后端開發(fā)者在生產(chǎn)環(huán)境中快速發(fā)現(xiàn)性能瓶頸，精準(zhǔn)定位根因并實(shí)施改進(jìn)，最終保障 MySQL 查詢的高效可靠。

到此這篇關(guān)于MySQL查詢性能慢時(shí)索引失效的排查與優(yōu)化實(shí)踐的文章就介紹到這了,更多相關(guān)MySQL索引失效內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論