MySQL 選錯索引

廖子博發表於2024-10-15
MySql索引

檢視執行計劃選擇的索引

explain select * from t where a between 10000 and 10000;

透過慢日誌（slow log）檢視語句執行情況

-- 啟動慢日誌
SHOW VARIABLES LIKE 'slow_query_log';

-- 慢日誌儲存位置
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 慢日誌閾值
SHOW VARIABLES LIKE 'long_query_time';

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

強制選擇索引

select  * from t force index(a) where a between 10000 and 20000;

選錯索引原因：統計資訊不準確

基數：索引上不同值的個數，基數越大，索引區分度越好。

檢視索引基數

show index from t;

MySQL 計算索引基數使用取樣統計法：

  • 選擇N個頁，統計每頁不同值，計算平均數，再乘以頁面數，得到索引基數
  • 當變更行數超過 1/M 時，自動觸發索引統計

索引統計方式，innodb_stats_persistent

  • on，統計資訊持久持久化，N=20，M=10
  • off，統計資訊只儲存在記憶體，N=8，M=16

透過執行計劃檢視預告掃描行數

explain select * from t where a between 10000 and 20000;

最佳化器選擇索引判斷：基數、掃描行數、回表次數

可以透過執行計劃和慢日誌判斷預估掃描行數是否符合實際掃描行數

修復統計資訊不準確

analyze table t

選錯索引原因：需要使用臨時表或排序

因為需要排序，InnoDB 可能會選擇 b 索引

explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

選錯索引處理

使用 force index 強行選擇索引

select * from t force index(1) where a between 10000 and 20000;

在保證邏輯相同的情況下，修改語句，引導 MySQL 使用期望的索引

explain select * from t where a between 1 and 10000 and b between 50000 and 100000 order by b, a limit 1;

select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;

新建更合適的索引或刪除錯誤索引

相關文章