本文原始碼：GitHub·點這裡 || GitEE·點這裡
一、高效能索引
1、查詢效能問題
在MySQL使用的過程中，所謂的效能問題，在大部分的場景下都是指查詢的效能，導致查詢緩慢的根本原因是資料量的不斷變大，解決查詢效能的最常見手段是：針對查詢的業務場景，設計合理的索引結構。
2、索引使用原則
索引的使用並不是越多越好，而是針對業務下的查詢場景，不斷的改進和優化，例如電商系統中使用者訂單的場景，假設存在如下表結構：
CREATE TABLE `ds_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
`user_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='使用者表';
CREATE TABLE `ds_order` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
`user_id` int(11) NOT NULL COMMENT '使用者ID',
`order_no` varchar(60) NOT NULL COMMENT '訂單號',
`product_name` varchar(50) DEFAULT NULL COMMENT '產品名稱',
`number` int(11) DEFAULT '1' COMMENT '個數',
`unit_price` decimal(10,2) DEFAULT '0.00' COMMENT '單價',
`total_price` decimal(10,2) DEFAULT '0.00' COMMENT '總價',
`order_state` int(2) DEFAULT '1' COMMENT '1待支付，2已支付,3已發貨,4已簽收',
`order_remark` varchar(50) DEFAULT NULL COMMENT '訂單備註',
`create_time` datetime DEFAULT NULL COMMENT '建立時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='訂單表';
使用者和訂單管理表，在電商的業務中很常見，可以通過對該業務分析，看看常用的索引結構：
使用者方：
- 基於使用者的查詢，多數是基於使用者ID(user_id)；
- 基於訂單號(order_no)，檢視物流的資訊；
運營方：
- 基於時間段的流水明細(create_time)或排序;
- 基於訂單狀態的篩選(order_state)和統計；
- 基於產品(product_name)的資料統計分析；
這樣一個流程分析走下來，即可以在開發初期，確定哪些結構是查詢必須用到的，預先做好索引結構，避免資料量龐大到影響效能時再去考慮使用索引。
有些時候會考慮放棄一些查詢條件，例如基於產品名稱的資料統計，走定時任務的方式，用來緩解表的查詢壓力，處理的方式是多樣的。
優秀的索引設計，都是建立在對業務資料的理解上，考慮業務資料的查詢方式，提高查詢效率。
二、索引建立
1、單列索引
單列索引，即索引建立在表的一個欄位上，一個表可以有多個單列索引，使用起來相對比較簡單：
CREATE INDEX user_id_index ON ds_order(user_id) USING BTREE;
主鍵索引，或者上述的user_id_index都是單列索引。
業務場景：基於使用者自己對訂單查詢，和管理系統，訂單和使用者的關聯查詢，所以訂單表的user_id需要一個索引。
2、組合索引
組合索引包含兩個或兩個以上的列，組合索引相比單列索引複雜很多，如何建立組合索引，和業務關聯度非常高，在使用組合索引時，還需要考慮查詢條件的順序。
CREATE INDEX state_create_time_index ON `ds_order`(`create_time`,`order_state`);
如上就是組合索引，實際包含的是2個索引 (create_time) (create_time,order_state)，這樣查詢就涉及到最左字首的原則，必須按照順序來查詢，這裡下面詳說。
業務場景：首先單說這裡組合索引，在業務開發中，常見訂單狀態的統計，基於統計結果做運營分析，另外就是在運營系統中，基於建立時間段的篩選條件是預設存在的，避免全部資料實時掃描；一些其他的常見查詢也都是條件加時間段的查詢模式。
3、字首索引
如果需要加索引的列是很長的字串，那麼索引會變的龐大臃腫，起到的效果可能並不是很明顯。這時候可以擷取列的前面一部分，建立索引，節省空間，這樣可能會出現索引的選擇性下降，即基於字首索引查詢出的相似資料可能很多：
ALTER TABLE ds_order ADD KEY (order_no(30)) ;
這裡由於訂單號太長，所以選擇前面30位作為字首索引，用作訂單號的查詢，當然這裡涉及到一個非常經典的業務場景，訂單號機制。
業務場景：字首索引一個典型的應用場景就是處理訂單號，一個看似很長的訂單號，其實包含的資訊非常多：
- 時間點：就是訂單生成的時間，年月日時分秒；
- 標識位：即一個唯一的UID，保證訂全單號唯一；
- 埋點一：在很多業務中，在訂單號記錄產品類目；
- 埋點二：通常會標識產品屬性，例如顏色，口味等；
- 錯位符：防止訂單號被分析，會隨機一段錯位符號；
如此一段分析下來，實際訂單號是非常長的，所以需要引入字首索引機制，字首索引期望使用的索引長度可以篩選整個列的基數，例如上面的訂單號：
- 大部分業務基於時間節點篩選足夠，即索引長度14位；
- 如果是併發業務，很多時間節點相同，則索引長度是時間點+標識位；
注意：如果業務允許的情況下，一般要求字首索引的長度有唯一性，例如上面的時間和標示位。
4、其他索引
例如全文索引等，這些用到的場景不多，如果資料龐大，又需要檢索等，通常會選擇強大的搜尋中介軟體來處理。顯式唯一索引，這種也會在程式上做規避，避免不友好的異常被丟擲。
三、索引查詢
如何建立最優的索引，是一件不容易的事情，同樣在查詢的時候，是否使用索引也是一件難度極大的事情，經驗之談：多數是效能問題暴露的時候，才會回頭審視查詢的SQL語句，針對效能問題，做相應的查詢優化。
1、單列查詢
這裡直接查詢主鍵索引，MySQL的主鍵一般選擇自增，所以速度非常快。
EXPLAIN SELECT * FROM ds_order WHERE id=2;
EXPLAIN SELECT * FROM ds_order WHERE id=1+1;
EXPLAIN SELECT * FROM ds_order WHERE id+1=1;
這裡，id=2,id=1+1，MySQL都可以自動解析，但是id+1是在索引列上執行運算，直接導致主鍵索引失效。這裡有一個基本策略，如果非要在單列索引上做操作，可以將該邏輯放在程式中，到MySQL層面，SQL語句越乾淨利落越好。
2、字首索引查詢
字首索引的查詢，可以基於Like對特定長度篩選，或者全訂單號查詢。
EXPLAIN SELECT * FROM ds_order WHERE order_no LIKE '202008011314158723628732871625%';
EXPLAIN SELECT * FROM ds_order WHERE order_no='20200801131415872362873287162572367';
3、組合索引查詢
查詢最麻煩的就是組合索引，或者說查詢條件組合起來，都使用了索引：
EXPLAIN SELECT * FROM ds_order
WHERE create_time>'2020-08-01 00:00:00' AND order_state='1';
上述基於組合索引中列的順序，使用了組合索引：state_create_time_index。
EXPLAIN SELECT * FROM ds_order WHERE create_time>'2020-08-01 00:00:00';
上述只使用create_time列，也同樣使用了索引結構。
EXPLAIN SELECT * FROM ds_order WHERE order_state='1';
上述如果只使用order_state條件，則結果顯示全表掃描。
EXPLAIN SELECT * FROM ds_order
WHERE create_time>'2020-08-01 00:00:00' AND order_no LIKE '20200801%';
上述則基於組合索引的create_time列和單列索引order_no保證查詢條件都使用了索引。
通過上面幾個查詢案例，索引組合索引使用的注意事項如下：
- 組合索引必須按索引最左列開始查詢；
- 不能跳過組合欄位查詢,這樣無法使用索引；
四、索引其他說明
1、索引的優點
- 基於註解或唯一索引保證資料庫表中資料的唯一性；
- 索引通過減少掃描表的行數提高查詢的效率；
2、索引的缺點
- 建立索引和維護索引，會耗費空間和實際；
- 查詢以外的操作增刪改等，都需要動態維護索引；
3、索引使用總結
索引機制在MySQL中真的非常複雜，非專業的DBA(就是指開發人員)，基本要熟練常見的索引結構，待過兩年所謂的大廠，每個版本開發涉及的核心表SQL都是有專業DBA驗收，複雜的查詢都是提交需求，DBA直接輸出查詢SQL，當然在一般公司是沒有DBA，需要開發在開發的過程中不斷的思考，逐步優化，這需要對業務資料有一定的敏感度，對核心介面有執行監控，當發現稍微出現耗時情況，就可以不斷優化，這個積累是個枯燥和進步的過程。
五、原始碼地址
GitHub·地址
https://github.com/cicadasmile/mysql-data-base
GitEE·地址
https://gitee.com/cicadasmile/mysql-data-base
推薦閱讀：MySQL資料庫系列