目錄

一、前言

二、基礎知識回顧

三、索引設計優化

1.遵循最左匹配原則，合理設計聯合索引順序

2.利用覆蓋索引避免回表查詢

3.針對字符串列使用前綴索引

4.合理使用復合索引替代多個單列索引

5.使用前綴索引優化模糊查詢的左匹配

四、索引使用優化

1.避免在WHERE子句中對字段進行函數運算

2.避免隱式類型轉換導致索引失效

3.小心使用NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等否定操作符

4.合理使用LIMIT優化分頁查詢

5.避免使用SELECT *，只查詢需要的列

6.使用EXPLAIN分析查詢執行計劃

五、特殊場景索引優化

1.使用索引排序優化ORDER BY操作

2.在大表上創建索引的最佳實踐

3.使用虛擬列為計算結果創建索引

六、索引維護優化

1.定期優化和重建索引

2.控制單表上的索引數量

3.使用降序索引優化排序

4.使用部分索引優化高選擇性數據

七、索引監控與進階技巧

1.利用索引統計信息進行調優

2.使用索引提示(Index Hints)解決優化器選擇問題

八、結語

一、前言

MySQL索引優化是提升數據庫性能的關鍵手段，一個合理的索引設計和使用策略，往往能將查詢速度提升幾十倍甚至上百倍。

然而，索引優化并不簡單，既需要扎實的理論基礎，也需要豐富的實戰經驗。

本文總結了21個MySQL索引優化的實戰技巧，從索引選擇、設計到維護、監控的全生命周期，幫助你解決日常開發中的索引性能問題。

二、基礎知識回顧

在具體介紹前，讓我們先簡單回顧索引的基礎知識：

MySQL常用的索引類型包括：主鍵索引、唯一索引、普通索引、聯合/組合/復合索引、覆蓋索引、全文索引等。

其中最常用的B+樹索引，具有以下特點：

? 非葉子節點只存儲鍵值信息

? 所有葉子節點包含了完整的數據記錄

? 葉子節點通過指針連接，方便范圍查詢

? 所有節點按鍵值大小排序

理解這些基礎對于后續優化至關重要。接下來，讓我們進入正題。

三、索引設計優化

1.遵循最左匹配原則，合理設計聯合索引順序

聯合索引的順序直接影響其使用效率。MySQL會從左到右依次使用索引列，如果中間某列沒有使用，則后面的列也無法使用索引。

錯誤示例：

-- 創建索引(name, age, city)
CREATE INDEX idx_user_name_age_city ON user(name, age, city);-- 以下查詢無法充分利用索引
SELECT * FROM user WHERE age = 25 AND city = 'Beijing'; ?-- name列缺失，只能全表掃描
SELECT * FROM user WHERE name = 'Tom' AND city = 'Beijing'; ?-- 中間age列缺失，city無法使用索引

優化方法：

1. 將選擇性高的列放在前面（選擇性 = 不重復值 / 總記錄數）

2. 將常用于條件查詢的列放在前面

3. 考慮范圍查詢的列放在最后

-- 假設選擇性：city < name < age
CREATE INDEX idx_user_name_age_city ON user(name, age, city);-- 充分利用索引的查詢
SELECT * FROM user WHERE name = 'Tom' AND age = 25;
SELECT * FROM user WHERE name = 'Tom' AND age = 25 AND city = 'Beijing';

(注：最左前綴匹配原則的核心要點：

1.必須包含最左列：?查詢條件中必須包含組合索引定義中的最左邊第一列 (name)，否則索引對該查詢無效。

2.連續前綴匹配：?索引可以被用于匹配查詢條件中索引列的連續前綴?((name),?(name,age),?(name, age, city)）。

3.不能跳過中間列：?如果查詢條件沒有包含索引定義中某個連續前綴的中間列（例如，只有 name和 city，跳過了 age），那么只有跳過的列之前的列（name）能用于索引查找，跳過的列之后的列（city）不能用于查找，只能作為掃描后的過濾條件。

-- 創建組合索引索引
CREATE INDEX idx_user_name_age_city ON user(name, age, city);-- 充分利用索引的查詢
SELECT * FROM user WHERE name = 'Tom';
SELECT * FROM user WHERE name = 'Tom' AND age = 25 ;
SELECT * FROM user WHERE name = 'Tom' AND age = 25 AND city = 'Beijing';
-- 跳過了索引中定義的age列，只有name是利用了索引，city這里只是作為查詢條件
SELECT * FROM user WHERE name = 'Tom' AND city = 'Beijing';

4.范圍查詢會中斷后續列:如果查詢條件中對某一列使用了范圍查詢 (>,?<,?BETWEEN,?LIKE 'prefix%')，那么索引中位于這個范圍查詢列之后的列無法再用于索引查找，只能作為掃描后的過濾條件。

-- 創建組合索引索引
CREATE INDEX idx_user_name_age_city ON user(name, age, city);-- name列使用范圍查詢，導致age和city列不走這個組合索引（這里name列是走索引的）
SELECT * FROM user WHERE name like 'Tom%' AND age = 25 AND city = 'Beijing';

)

2.利用覆蓋索引避免回表查詢

回表操作是指通過索引找到對應的行記錄指針，再通過指針去查詢完整記錄的過程。

如果查詢只需要返回索引包含的列，則可以避免回表，這稱為覆蓋索引。

優化前：

-- 創建普通索引
CREATE INDEX idx_user_name ON user(name);-- 需要回表查詢
SELECT id, name, age, city FROM user WHERE name = 'Tom';

優化后：

-- 創建包含所需字段的索引
CREATE INDEX idx_user_name_age_city ON user(name, age, city);-- 使用覆蓋索引，無需回表
SELECT name, age, city FROM user WHERE name = 'Tom';

3.針對字符串列使用前綴索引

對于CHAR和VARCHAR類型的列，如果整列長度較大，可以只索引開頭的部分字符，這樣可以大幅減少索引占用空間，提高索引效率。
優化方法：

-- 假設product_desc是較長的產品描述文本
CREATE INDEX idx_product_desc ON product(product_desc(50));

如何確定前綴長度？可以通過計算選擇性來確定：
?

-- 計算不同前綴長度的選擇性
SELECT?COUNT(DISTINCT LEFT(product_desc, 10)) / COUNT(*) AS sel_10,COUNT(DISTINCT LEFT(product_desc, 20)) / COUNT(*) AS sel_20,COUNT(DISTINCT LEFT(product_desc, 30)) / COUNT(*) AS sel_30,COUNT(DISTINCT LEFT(product_desc, 40)) / COUNT(*) AS sel_40,COUNT(DISTINCT LEFT(product_desc, 50)) / COUNT(*) AS sel_50,COUNT(DISTINCT product_desc) / COUNT(*) AS sel_full
FROM product;

創建索引時，選擇一個接近完整列選擇性的前綴長度即可。
注意事項：?使用前綴索引后，無法使用該索引做ORDER BY或GROUP BY，也無法使用覆蓋索引。

4.合理使用復合索引替代多個單列索引

多個單列索引在多條件查詢時，MySQL只會選擇一個索引。而復合索引可以同時滿足多個條件的查詢需求。

優化前：

-- 單獨創建兩個索引
CREATE INDEX idx_user_age ON user(age);
CREATE INDEX idx_user_city ON user(city);-- MySQL通常只會選擇一個索引
SELECT * FROM user WHERE age = 25 AND city = 'Beijing';

優化后：

-- 創建一個復合索引
CREATE INDEX idx_user_age_city ON user(age, city);-- 可以同時使用age和city條件
SELECT * FROM user WHERE age = 25 AND city = 'Beijing';

5.使用前綴索引優化模糊查詢的左匹配

LIKE語句使用通配符前綴（如'%abc'）會導致索引失效。但對于右匹配模式（如'abc%'），索引仍然有效。

?可以使用索引的查詢：

-- 可以使用索引
SELECT * FROM products WHERE product_name LIKE 'iphone%';

無法使用索引的查詢：

-- 無法使用索引
SELECT * FROM products WHERE product_name LIKE '%iphone%';

優化方法：

對于需要搜索包含某個關鍵詞的記錄，可以考慮全文索引或搜索引擎。對于簡單場景，也可以通過字段冗余解決：

-- 添加一個反轉字段
ALTER TABLE products ADD product_name_reversed VARCHAR(255);-- 創建反轉字段的索引
CREATE INDEX idx_product_name_rev ON products(product_name_reversed);-- 搜索以'phone'結尾的產品
SELECT * FROM products?
WHERE product_name_reversed LIKE CONCAT(REVERSE('phone'), '%');

或者如果你的?MySQL 版本 >= 8.0也可以使用函數索引；

-- 創建字段的函數索引
CREATE INDEX idx_product_name_fun ON products(REVERSE(product_name));-- 搜索以'phone'結尾的產品（這里直接用REVERSE函數反轉字段）
SELECT * FROM products?
WHERE REVERSE(product_name) LIKE CONCAT(REVERSE('phone'), '%');

或者使用全文索引（關鍵字FULLTEXT）：

-- 1. 在需要搜索的列上創建全文索引 (以 products 表的 description 列為例)
ALTER TABLE products ADD FULLTEXT INDEX ft_idx_description (description);-- 2. 基礎搜索：查找包含 'phone' 或 'camera' 的記錄 (自然語言模式)
SELECT *, MATCH(description) AGAINST('phone camera') AS relevance
FROM products
WHERE MATCH(description) AGAINST('phone camera')
ORDER BY relevance DESC;-- 3. 布爾搜索：查找必須包含 'fast' 且包含 'charger' 但不包含 'wireless' 的記錄
SELECT *
FROM products
WHERE MATCH(description) AGAINST('+fast +charger -wireless' IN BOOLEAN MODE);-- 4. 短語搜索：查找精確包含 'long battery life' 短語的記錄
SELECT *
FROM products
WHERE MATCH(description) AGAINST('"long battery life"' IN BOOLEAN MODE);

強烈建議：?除非是微不足道的中文搜索需求，否則不要依賴 MySQL 全文索引解決核心中文搜索問題。投入專業搜索引擎 (如 ES/MeiliSearch + 中文分詞) 的回報遠高于在 MySQL 內勉強湊合。

（搜索引擎這里不做贅述，有想了解的同學請關注我后續的文章。）

四、索引使用優化

1.避免在WHERE子句中對字段進行函數運算

?在字段上使用函數會導致索引失效，應該把運算轉移到值上。

錯誤用法：

-- 索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2023;

優化方法：

-- 可以使用索引
SELECT * FROM orders?
WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

2.避免隱式類型轉換導致索引失效

MySQL在進行查詢時，如果字段類型與條件值類型不匹配，會進行隱式類型轉換，可能導致索引失效。

錯誤用法：

-- user_id是varchar類型，但使用了整數條件
CREATE INDEX idx_user_id ON users(user_id);
SELECT * FROM users WHERE user_id = 12345; ?-- 索引可能失效

優化方法：

-- 確保條件值類型與字段類型一致
SELECT * FROM users WHERE user_id = '12345'; ?-- 使用字符串類型

3.小心使用NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等否定操作符

否定條件通常會導致索引失效，因為數據庫需要檢查所有不滿足條件的記錄。
優化方法：

盡量用肯定表達式替代否定表達式：

-- 優化前：無法充分利用索引
SELECT * FROM products WHERE category_id != 5;-- 優化后：可以使用索引
SELECT * FROM products WHERE category_id < 5 OR category_id > 5;

4.合理使用LIMIT優化分頁查詢

大偏移量的LIMIT分頁查詢效率較低，因為MySQL需要檢索前N條記錄然后丟棄。

優化前：

-- 性能較差的分頁查詢
SELECT * FROM products ORDER BY id LIMIT 100000, 10;

優化方法1 - 使用索引覆蓋掃描：

-- 先獲取ID，再關聯查詢完整數據
SELECT p.* FROM products p
JOIN (SELECT id FROM products ORDER BY id LIMIT 100000, 10
) tmp ON p.id = tmp.id;

優化方法2 - 使用上次查詢的最大ID（不適合前端跳頁）：

-- 假設已知上一頁的最大ID是100233
SELECT * FROM products WHERE id > 100233 ORDER BY id LIMIT 10;

5.避免使用SELECT *，只查詢需要的列

使用SELECT *會返回所有列，可能破壞覆蓋索引的效果，并增加網絡和內存開銷。

優化前：

-- 可能導致不必要的開銷
SELECT * FROM users WHERE name = 'Tom';

優化后：

-- 只返回需要的列，可能利用覆蓋索引
SELECT id, name, email FROM users WHERE name = 'Tom';

6.使用EXPLAIN分析查詢執行計劃

在優化前，先使用EXPLAIN分析SQL語句的執行計劃，了解索引使用情況。

EXPLAIN SELECT * FROM users WHERE name = 'Tom' AND age > 20;

重點關注以下字段：

??type: 從好到差依次是：system > const > eq_ref > ref > range > index > ALL（性能排序）

??key: 實際使用的索引

??rows: 預計需要掃描的行數

??Extra: 額外信息，如"Using index"表示使用了覆蓋索引

五、特殊場景索引優化

1.使用索引排序優化ORDER BY操作

如果ORDER BY的列與WHERE使用的列不一致，排序無法使用索引，會導致文件排序（如果滿足`name='Tom'`的行數很多，那么排序可能會很慢，因為排序操作可能需要在內存或者磁盤上完成（即所謂的文件排序，filesort）。

優化前：

-- WHERE和ORDER BY使用不同的列，可能導致文件排序
CREATE INDEX idx_user_name ON users(name);
SELECT * FROM users WHERE name = 'Tom' ORDER BY age;

優化后：

-- 創建聯合索引同時包含WHERE和ORDER BY的列
CREATE INDEX idx_user_name_age ON users(name, age);
SELECT * FROM users WHERE name = 'Tom' ORDER BY age;

注意事項：?ORDER BY的多個字段需要與索引順序一致，且排序方向需一致(全ASC或全DESC)。

2.在大表上創建索引的最佳實踐

在大表上直接創建索引可能會導致長時間鎖表。可以使用以下方法優化：

方法1 - 使用低峰期操作：

-- 在低峰期執行索引創建
CREATE INDEX idx_order_status ON orders(status);

方法2 - 使用在線DDL(MySQL 8.0+首選方案)：

-- 使用ALGORITHM和LOCK選項
CREATE INDEX idx_order_status ON orders(status)
ALGORITHM=INPLACE, LOCK=NONE;

技術原理：非阻塞索引構建

1. ALGORITHM=INPLACE

   增量構建：僅掃描原表一次，中間數據存于臨時文件（非新建整表）?
   空間復用：無需額外占用等表大小的磁盤空間（對比ALGORITHM=COPY）

2. LOCK=NONE

   零鎖表：允許并發的SELECT和DML（INSERT/UPDATE/DELETE）
   底層通過行版本控制（如InnoDB的MVCC）實現讀寫分離

方法3 - 使用pt-online-schema-change工具：

pt-online-schema-change --alter "ADD INDEX idx_order_status (status)" \
--host=localhost --user=root --ask-pass --database=mydb --table=orders \
--execute

3.使用虛擬列為計算結果創建索引

對于經常需要計算后過濾的場景，可以使用虛擬列并在其上創建索引（關鍵字VIRTUAL）。

-- 添加虛擬列存儲計算結果
ALTER TABLE products?
ADD total_value DECIMAL(10,2) AS (price * quantity) VIRTUAL;-- 在虛擬列上創建索引
CREATE INDEX idx_total_value ON products(total_value);-- 使用計算列進行查詢
SELECT * FROM products WHERE total_value > 10000;

或者使用MySQL 8.0+的函數索引：

-- 添加虛擬列存儲計算結果
CREATE INDEX tatal_value ON products((price * quantity))-- 使用計算列進行查詢
SELECT * FROM products WHERE (price * quantity) > 10000;

虛擬列索引和函數索引是同一索引優化機制（預計算表達式+建索引）的兩種語法糖：

• 虛擬列索引:?通過創建"計算列"提供?顯式接口

• 函數索引:?允許直接索引表達式?無需顯式列?

選擇誰 ≈ 選擇語法風格，而非性能差異，因為兩者在相同條件下性能命中索引時查詢性能一致。

六、索引維護優化

1.定期優化和重建索引

隨著數據變化，索引可能變得碎片化，影響性能。定期優化表和重建索引可以改善性能。

-- 分析表
ANALYZE TABLE orders;-- 優化表
OPTIMIZE TABLE orders;-- 或者重建索引
ALTER TABLE orders DROP INDEX idx_status, ADD INDEX idx_status(status);

總結：

1. 日常用?ANALYZE?維持優化器準確性

2. 定期監控碎片率，>30% 時用?OPTIMIZE

3. 關鍵索引性能下降時單獨重建索引

4. 永遠在業務低峰期操作！

注：下面提供一個碎片率的計算方式（參考）：

-- 檢查碎片率
SELECT TABLE_NAME,ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS "Size(MB)",ROUND(DATA_FREE / 1024 / 1024, 2) AS "Free(MB)",ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH)) * 100, 2) AS "Frag %"
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';

2.控制單表上的索引數量

索引數量過多會影響寫性能，建議每個表的索引數量控制在5個以內。

優化方法：

1. 刪除重復和未使用的索引

2. 合并功能類似的索引

2. 合并功能類似的索引-- 查找未使用的索引
SELECT * FROM schema_unused_indexes; ?-- Performance Schema-- 查找重復的索引
SELECT * FROM sys.schema_redundant_indexes; ?-- Sys Schema

3.使用降序索引優化排序

MySQL 8.0+支持降序索引，可以優化混合排序方向的查詢。

-- 創建混合排序方向的索引(MySQL 8.0+)
CREATE INDEX idx_user_age_score ON users(age ASC, score DESC);-- 可以高效執行的查詢
SELECT * FROM users ORDER BY age ASC, score DESC;

4.使用部分索引優化高選擇性數據

MySQL 8.0+支持在WHERE條件滿足時才為行創建索引記錄，減少索引大小。

-- 只為活躍用戶創建索引(MySQL 8.0+)
CREATE INDEX idx_active_users ON users(name, email)?
WHERE status = 'active';

七、索引監控與進階技巧

1.利用索引統計信息進行調優

MySQL維護了索引統計信息，可以幫助優化器選擇合適的索引。有時統計信息不準確會導致次優的執行計劃。

-- 查看表的統計信息
SHOW TABLE STATUS LIKE 'users';-- 查看索引的基數
SHOW INDEX FROM users;-- 刷新統計信息
ANALYZE TABLE users;

2.使用索引提示(Index Hints)解決優化器選擇問題

有時MySQL優化器的選擇不是最優的，可以使用索引提示強制使用特定索引。

-- 強制使用特定索引
SELECT * FROM users FORCE INDEX(idx_name_age)?
WHERE name = 'Tom' AND age > 20;-- 忽略特定索引
SELECT * FROM users IGNORE INDEX(idx_status)?
WHERE status = 'active' AND age > 20;

建議：?索引提示應該是最后的手段，通常先嘗試優化表結構和索引設計。

八、結語

索引優化是一個持續的過程，需要結合業務特點、數據分布和查詢模式來綜合考慮。

優秀的索引設計需要理論知識和實踐經驗的結合。