0.前言

假設你經營一家電商平臺，某天用戶突然投訴商品搜索加載時間超過10秒。技術團隊緊急排查，發現一條原本執行0.1秒的查詢語句，在百萬級數據量下竟變成了全表掃描。這時，數據庫索引猶如深夜急診室里的救命儀器——它的存在與否，直接決定系統是起死回生還是徹底崩潰。

索引設計的底層邏輯如同城市交通規劃。想象早高峰時的十字路口，無序的車流必然引發堵塞。B+樹結構通過分層導航，讓數據查詢像ETC通道般快速通行。某金融平臺曾將交易記錄的查詢響應時間從8秒縮短至0.2秒，秘密就在于將單列索引改造為組合索引，如同在十字路口增設定向車道。

1.索引設計原則

• 代碼先行，索引后上：給數據庫表添加索引，一般應該等到主體業務功能開發完畢，把涉及到該表相關sql都要拿出來分析之后再建立索引。

• 高頻查詢列：優先為 WHERE、JOIN、ORDER BY、GROUP BY 等子句中頻繁使用的列創建索引。

• 避免低基數列：低基數列（如性別、狀態等重復值多的列）不適合創建索引，因為它們的區分度低，索引效果不明顯。盡量使用那些基數比較大的字段，就是值比較多的字段，那么才能發揮出B+樹快速二分查找的優勢來。列的唯一性越高，索引效果越好。

• 聯合索引盡量覆蓋條件：對于多列查詢，優先使用組合索引（多列索引），但需遵循最左前綴原則。同時將查詢中使用頻率最高的列放在前面，同時考慮查詢的過濾性，將過濾性更強的列放在前面。

• 長字符串可以采用前綴索引：盡量對字段類型較小的列設計索引，比如說什么tinyint之類的，因為字段類型較小的話，占用磁盤空間也會比較小，在搜索的時候性能也會比較好一點。對于長字符串列（如 VARCHAR），可以使用前綴索引（如 CREATE INDEX idx_name ON users(name(10))），以節省空間。

• 選擇合適的索引類型：優先使用自增整數作為主鍵，避免使用 UUID 等無序主鍵。

• 控制索引數量：索引會增加寫操作（INSERT/UPDATE/DELETE）的開銷。建議單表索引不超過?5個，避免冗余索引。優先建聯合索引。查詢時調整SQL條件順序，使其與索引列的順序一致。

2.索引優化

分頁查詢優化

很多時候我們業務系統實現分頁功能可能會用如下sql實現

SELECT * FROM orders ORDER BY id LIMIT 10000, 10;

表示從表 orders 中取出從 10001 行開始的 10 行記錄。看似只查詢了 10 條記錄，實際這條 SQL 是先讀取 10010條記錄，然后拋棄前 10000 條記錄，然后讀到后面 10 條想要的數據。因此要查詢一張大表比較靠后的數據，執行效率是非常低的。

優化1：根據自增且連續的主鍵排序的分頁查詢

SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

優化2：根據非主鍵字段排序的分頁查詢

SELECT * FROM orders ORDER BY name LIMIT 10000, 10;

關鍵是讓排序時返回的字段盡可能少，所以可以讓排序和分頁操作先查出主鍵，然后根據主鍵查到對應的記錄，SQL改寫如下:

select * from orders e inner join (select id from orders order by name limit 90000,5) ed
on e.id = ed.id;

Join關聯查詢優化

優化1：關聯字段加索引;

優化2：優先選擇小表做驅動表。

一次一行循環地從第一張表（稱為驅動表）中讀取行，在這行數據中取到關聯字段，根據關聯字段在另一張表（被驅動表）里取出滿足條件的行，然后取出兩張表的結果合集。

當使用left join時，左表是驅動表，右表是被驅動表，當使用right join時，右表時驅動表，左表是被驅動表，
當使用join時，mysql會選擇數據量比較小的表作為驅動表，大表作為被驅動表。

in和exsits優化

原則：小表驅動大表，即小的數據集驅動大的數據集

in：當B表的數據集小于A表的數據集時，in優于exists

select * from A where id in (select id from B)

exists：當A表的數據集小于B表的數據集時，exists優于in

將主查詢A的數據，放到子查詢B中做條件驗證，根據驗證結果（true或false）來決定主查詢的數據是否保留

select * from A where exists (select 1 from B where B.id = A.id)

3.最后

通過合理設計索引，可以顯著提升 MySQL 的查詢性能，同時減少系統資源的消耗。在實際應用中，建議根據具體場景靈活調整索引設計和查詢語句，以達到最佳的性能表現。