目錄
- 索引模型
- B+Tree
- 索引選擇
- 索引優化
- 索引選擇性
- 覆蓋索引
- 最左前綴原則+索引下推
- 前綴索引
- 唯一索引
- 索引失效
- 總結
索引模型
哈希表
- 適用于只有等值查詢的場景，Memory引擎默認索引
- InnoDB支持自適應哈希索引，不可干預，由引擎自行決定是否創建
有序數組：在等值查詢和范圍查詢場景中的性能都非常優秀，但插入和刪除數據需要進行數據移動，成本太高。因此，只適用于靜態存儲引擎
二叉平衡樹：每個節點的左兒子小于父節點，父節點又小于右兒子，時間復雜度是 O(log(N))
多叉平衡樹：索引不止存在內存中，還要寫到磁盤上。為了讓一個查詢盡量少地讀磁盤，就必須讓查詢過程訪問盡量少的數據塊。因此，要使用“N 叉”樹。
B+Tree
B-Tree 與 B+Tree
B-Tree
B+Tree
InnoDB 使用了 B+ 樹索引模型。假設，我們有一個主鍵列為 ID 的表，表中有字段 k，并且在 k 上有索引，如下所示：
- 主鍵索引：也被稱為聚簇索引，葉子節點存的是整行數據
- 非主鍵索引：也被稱為二級索引，葉子節點內容是主鍵的值
注意事項
- 索引基于數據頁有序存儲，可能發生數據頁的分裂(頁存儲空間不足)和合并(數據刪除造成頁利用率低)
- 數據的無序插入會造成數據的移動，甚至數據頁的分裂
- 主鍵長度越小，普通索引的葉子節點就越小，普通索引占用的空間也就越小
- 索引字段越小，單層可存儲數據量越多，可減少磁盤IO
// 假設一個數據頁16K、一行數據1K、索引間指針6字節、索引字段bigint類型(8字節)
// 索引個數
K = 16*1024/(8+6) =1170
// 單個葉子節點記錄數
N = 16/1 = 16
// 三層B+記錄數
V = K*K*N = 21902400
MyISAM也是使用B+Tree索引，區別在于不區分主鍵和非主鍵索引，均是非聚簇索引，葉子節點保存的是數據文件的指針
索引選擇
優化器選擇索引的目的，是找到一個最優的執行方案，并用最小的代價去執行語句。在數據庫里面，掃描行數是影響執行代價的因素之一。掃描的行數越少，意味著訪問磁盤數據的次數越少，消耗的 CPU 資源越少。
當然，掃描行數并不是唯一的判斷標準，優化器還會結合是否使用臨時表、是否排序等因素進行綜合判斷。
掃描行數如何計算
一個索引上不同的值越多，這個索引的區分度就越好。而一個索引上不同的值的個數，稱之為“基數”（cardinality）。
-- 查看當前索引基數
mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 0 | PRIMARY | 1 | id | A | 100256 | NULL | NULL | | BTREE | | |
| test | 1 | index_a | 1 | a | A | 98199 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
從性能的角度考慮，InnoDB 使用采樣統計，默認會選擇 N 個數據頁，統計這些頁面上的不同值，得到一個平均值，然后乘以這個索引的頁面數，就得到了這個索引的基數。因此，上述兩個索引顯示的基數并不相同。
而數據表是會持續更新的，索引統計信息也不會固定不變。所以，當變更的數據行數超過 1/M 的時候(innodb_stats_persistent=on時默認10，反之16)，會自動觸發重新做一次索引統計。
mysql> show variables like '%innodb_stats_persistent%';
+--------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------+-------------+
-- 是否自動觸發更新統計信息，當被修改的數據超過10%時就會觸發統計信息重新統計計算
| innodb_stats_auto_recalc | ON |
-- 控制在重新計算統計信息時是否會考慮刪除標記的記錄
| innodb_stats_include_delete_marked | OFF |
-- 對null值的統計方法，當變量設置為nulls_equal時，所有NULL值都被視為相同
| innodb_stats_method | nulls_equal |
-- 操作元數據時是否觸發更新統計信息
| innodb_stats_on_metadata | OFF |
-- 統計信息是否持久化存儲
| innodb_stats_persistent | ON |
-- innodb_stats_persistent=on，持久化統計信息采樣的抽樣頁數
| innodb_stats_persistent_sample_pages | 20 |
-- 不推薦使用，已經被innodb_stats_transient_sample_pages替換
| innodb_stats_sample_pages | 8 |
-- 瞬時抽樣page數
| innodb_stats_transient_sample_pages | 8 |
+--------------------------------------+-------------+
- 除了因為抽樣導致統計基數不準外，MVCC也會導致基數統計不準確。例如：事務A先事務B開啟且未提交，事務B刪除部分數據，在可重復讀中事務A還可以查詢到刪除的數據，此部分數據目前至少有兩個版本，有一個標識為deleted的數據。
- 主鍵是直接按照表的行數來估計的，表的行數，優化器直接使用show table status like 't'的值
- 手動觸發索引統計：
-- 重新統計索引信息
mysql> analyze table t;
排序對索引選擇的影響
-- 創建表
mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;
-- 定義測試數據存儲過程
mysql> delimiter ;
CREATE PROCEDURE idata ()
BEGIN
DECLARE i INT ;
SET i = 1 ;
WHILE (i = 100000) DO
INSERT INTO t
VALUES
(i, i, i) ;
SET i = i + 1 ;
END
WHILE ;
END;
delimiter ;
-- 執行存儲過程，插入測試數據
mysql> CALL idata ();
-- 查看執行計劃，使用了字段a上的索引
mysql> explain select * from t where a between 10000 and 20000;
+----+-------------+-------+-------+---------------+-----+---------+------+-------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----+---------+------+-------+-----------------------+
| 1 | SIMPLE | t | range | a | a | 5 | NULL | 10000 | Using index condition |
+----+-------------+-------+-------+---------------+-----+---------+------+-------+-----------------------+
-- 由于需要進行字段b排序，雖然索引b需要掃描更多的行數，但本身是有序的，綜合掃描行數和排序，優化器選擇了索引b，認為代價更小
mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+-------+---------------+-----+---------+------+-------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----+---------+------+-------+------------------------------------+
| 1 | SIMPLE | t | range | a,b | b | 5 | NULL | 50128 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+-----+---------+------+-------+------------------------------------+
-- 方案1：通過force index強制走索引a，糾正優化器錯誤的選擇，不建議使用（不通用，且索引名稱更變語句也需要變）
mysql> explain select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | t | range | a | a | 5 | NULL | 999 | Using index condition; Using where; Using filesort |
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+
-- 方案2：引導 MySQL 使用我們期望的索引，按b,a排序，優化器需要考慮a排序的代價
mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | t | range | a,b | a | 5 | NULL | 999 | Using index condition; Using where; Using filesort |
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+
-- 方案3：有些場景下，我們可以新建一個更合適的索引，來提供給優化器做選擇，或刪掉誤用的索引
ALTER TABLE `t`
DROP INDEX `a`,
DROP INDEX `b`,
ADD INDEX `ab` (`a`,`b`) ;
索引優化
索引選擇性
索引選擇性 = 基數 / 總行數
-- 表t中字段xxx的索引選擇性
select count(distinct xxx)/count(id) from t;
索引的選擇性，指的是不重復的索引值（基數）和表記錄數的比值。選擇性是索引篩選能力的一個指標，索引的取值范圍是 0~1 ，當選擇性越大，索引價值也就越大。
在使用普通索引查詢時，會先加載普通索引，通過普通索引查詢到實際行的主鍵，再使用主鍵通過聚集索引查詢相應的行，以此循環查詢所有的行。若直接全量搜索聚集索引，則不需要在普通索引和聚集索引中來回切換，相比兩種操作的總開銷可能掃描全表效率更高。
實際工作中，還是要看業務情況，如果數據分布不均衡，實際查詢條件總是查詢數據較少的部分，在索引選擇較低的列上加索引，效果可能也很不錯。
覆蓋索引
覆蓋索引可以減少樹的搜索次數，顯著提升查詢性能，所以使用覆蓋索引是一個常用的性能優化手段
-- 只需要查 ID 的值，而 ID 的值已經在 k 索引樹上了，因此可以直接提供查詢結果，不需要回表
select ID from T where k between 3 and 5
-- 增加字段V，每次查詢需要返回V，可考慮把k、v做成聯合索引
select ID,V from T where k between 3 and 5
最左前綴原則+索引下推
-- id、name、age三列，name、age上創建聯合索引
-- 滿足最左前綴原則，name、age均走索引
select * from T where name='xxx' and age=12
-- Mysql自動優化，調整name、age順序，，name、age均走索引
select * from T where age=12 and name='xxx'
-- name滿足最左前綴原則走索引，MySQL5.6引入索引下推優化（index condition pushdown)，即索引中先過濾掉不滿足age=12的記錄再回表
select * from T where name like 'xxx%' and age=12
-- 不滿足最左前綴原則，均不走索引
select * from T where name like '%xxx%' and age=12
-- 滿足最左前綴原則，name走索引
select * from T where name='xxx'
-- 不滿足最左前綴原則，不走索引
select * from T where age=12
聯合索引建立原則：
- 如果通過調整順序，可以少維護一個索引，那么這個順序往往就是需要優先考慮采用的
- 空間：優先小字段單獨建立索引，例如：name、age，可建立(name,age)聯合索引和(age)單字段索引
前綴索引
mysql> create table SUser(
ID bigint unsigned primary key,
name varchar(64),
email varchar(64),
...
)engine=innodb;
-- 以下查詢場景
mysql> select name from SUser where email='xxx';
-- 方案1：全文本索引，回表次數由符合條件的數據量決定
mysql> alter table SUser add index index1(email);
-- 方案2：前綴索引，回表次數由前綴匹配結果決定
mysql> alter table SUser add index index2(email(6));
前綴索引可以節省空間，但需要注意前綴長度的定義，在節省空間的同時，不能增加太多查詢成本，即減少回表驗證次數
如何設置合適的前綴長度？
-- 預設一個可以接受的區分度損失比，選擇滿足條件中最小的前綴長度
select count(distinct left(email,n))/count(distinct email) from SUser;
如果合適的前綴長度較長？
比如身份證號，如果滿足區分度要求，可能需要12位以上的前綴索引，節約的空間有限，又增加了查詢成本，就沒有必要使用前綴索引。此時，我們可以考慮使用以下方式：
倒序存儲
-- 查詢時字符串反轉查詢
mysql> select field_list from t where id_card = reverse('input_id_card_string');
使用hash字段
-- 創建一個整數字段，來保存身份證的校驗碼，同時在這個字段上創建索引
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
-- 查詢時使用hash字段走索引查詢，再使用原字段精度過濾
mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
以上兩種方式的缺點：
- 不支持范圍查詢
- 使用hash字段需要額外占用空間，新增了一個字段
- 讀寫時需要額外的處理，reverse或者crc32等
前綴索引對覆蓋索引的影響？
-- 使用前綴索引就用不上覆蓋索引對查詢性能的優化
select id,email from SUser where email='xxx';
唯一索引
建議使用普通索引，唯一索引無法使用change buffer，內存命中率低
索引失效
- 不做列運算，包括函數的使用，可能破壞索引值的有序性
- 避免 %xxx 式查詢使索引失效
- or語句前后沒有同時使用索引，當or左右查詢字段只有一個是索引，該索引失效
- 組合索引ABC問題，最左前綴原則
- 隱式類型轉化
- 隱式字符編碼轉換
- 優化器放棄索引，回表、排序成本等因素影響，改走其它索引或者全部掃描
總結
到此這篇關于Mysql索引選擇以及優化的文章就介紹到這了,更多相關Mysql索引選擇優化內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- mysql性能優化之索引優化
- MySQL 索引分析和優化
- MySQL中索引優化distinct語句及distinct的多字段操作
- Mysql使用索引實現查詢優化
- Mysql limit 優化，百萬至千萬級快速分頁 復合索引的引用并應用于輕量級框架
- MySQL索引背后的之使用策略及優化(高性能索引策略)
- MySQL 聯合索引與Where子句的優化 提高數據庫運行效率
- MySQL Order By索引優化方法
- 探究MySQL優化器對索引和JOIN順序的選擇
- mysql優化之路----hash索引優化
