人妖在线一区,国产日韩欧美一区二区综合在线,国产啪精品视频网站免费,欧美内射深插日本少妇

新聞動態(tài)

Mysql索引選擇以及優(yōu)化詳解

發(fā)布日期:2022-02-14 17:48 | 文章來源:腳本之家

索引模型

哈希表

  • 適用于只有等值查詢的場景,Memory引擎默認(rèn)索引
  • InnoDB支持自適應(yīng)哈希索引,不可干預(yù),由引擎自行決定是否創(chuàng)建

有序數(shù)組:在等值查詢和范圍查詢場景中的性能都非常優(yōu)秀,但插入和刪除數(shù)據(jù)需要進(jìn)行數(shù)據(jù)移動,成本太高。因此,只適用于靜態(tài)存儲引擎

二叉平衡樹:每個節(jié)點的左兒子小于父節(jié)點,父節(jié)點又小于右兒子,時間復(fù)雜度是 O(log(N))

多叉平衡樹:索引不止存在內(nèi)存中,還要寫到磁盤上。為了讓一個查詢盡量少地讀磁盤,就必須讓查詢過程訪問盡量少的數(shù)據(jù)塊。因此,要使用“N 叉”樹。

B+Tree

B-Tree 與 B+Tree

B-Tree

B+Tree

InnoDB 使用了 B+ 樹索引模型。假設(shè),我們有一個主鍵列為 ID 的表,表中有字段 k,并且在 k 上有索引,如下所示:

  • 主鍵索引:也被稱為聚簇索引,葉子節(jié)點存的是整行數(shù)據(jù)
  • 非主鍵索引:也被稱為二級索引,葉子節(jié)點內(nèi)容是主鍵的值

注意事項

  • 索引基于數(shù)據(jù)頁有序存儲,可能發(fā)生數(shù)據(jù)頁的分裂(頁存儲空間不足)和合并(數(shù)據(jù)刪除造成頁利用率低)
  • 數(shù)據(jù)的無序插入會造成數(shù)據(jù)的移動,甚至數(shù)據(jù)頁的分裂
  • 主鍵長度越小,普通索引的葉子節(jié)點就越小,普通索引占用的空間也就越小
  • 索引字段越小,單層可存儲數(shù)據(jù)量越多,可減少磁盤IO
// 假設(shè)一個數(shù)據(jù)頁16K、一行數(shù)據(jù)1K、索引間指針6字節(jié)、索引字段bigint類型(8字節(jié))
// 索引個數(shù)
K = 16*1024/(8+6) =1170
// 單個葉子節(jié)點記錄數(shù)
N = 16/1 = 16
// 三層B+記錄數(shù)
V = K*K*N = 21902400

MyISAM也是使用B+Tree索引,區(qū)別在于不區(qū)分主鍵和非主鍵索引,均是非聚簇索引,葉子節(jié)點保存的是數(shù)據(jù)文件的指針

索引選擇

優(yōu)化器選擇索引的目的,是找到一個最優(yōu)的執(zhí)行方案,并用最小的代價去執(zhí)行語句。在數(shù)據(jù)庫里面,掃描行數(shù)是影響執(zhí)行代價的因素之一。掃描的行數(shù)越少,意味著訪問磁盤數(shù)據(jù)的次數(shù)越少,消耗的 CPU 資源越少。

當(dāng)然,掃描行數(shù)并不是唯一的判斷標(biāo)準(zhǔn),優(yōu)化器還會結(jié)合是否使用臨時表、是否排序等因素進(jìn)行綜合判斷。

掃描行數(shù)如何計算

一個索引上不同的值越多,這個索引的區(qū)分度就越好。而一個索引上不同的值的個數(shù),稱之為“基數(shù)”(cardinality)。

-- 查看當(dāng)前索引基數(shù)
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 使用采樣統(tǒng)計,默認(rèn)會選擇 N 個數(shù)據(jù)頁,統(tǒng)計這些頁面上的不同值,得到一個平均值,然后乘以這個索引的頁面數(shù),就得到了這個索引的基數(shù)。因此,上述兩個索引顯示的基數(shù)并不相同。

而數(shù)據(jù)表是會持續(xù)更新的,索引統(tǒng)計信息也不會固定不變。所以,當(dāng)變更的數(shù)據(jù)行數(shù)超過 1/M 的時候(innodb_stats_persistent=on時默認(rèn)10,反之16),會自動觸發(fā)重新做一次索引統(tǒng)計。

mysql> show variables like '%innodb_stats_persistent%';
+--------------------------------------+-------------+
| Variable_name      | Value  |
+--------------------------------------+-------------+
-- 是否自動觸發(fā)更新統(tǒng)計信息,當(dāng)被修改的數(shù)據(jù)超過10%時就會觸發(fā)統(tǒng)計信息重新統(tǒng)計計算
| innodb_stats_auto_recalc    | ON   |
-- 控制在重新計算統(tǒng)計信息時是否會考慮刪除標(biāo)記的記錄
| innodb_stats_include_delete_marked | OFF   |
-- 對null值的統(tǒng)計方法,當(dāng)變量設(shè)置為nulls_equal時,所有NULL值都被視為相同
| innodb_stats_method     | nulls_equal | 
-- 操作元數(shù)據(jù)時是否觸發(fā)更新統(tǒng)計信息
| innodb_stats_on_metadata    | OFF   |
-- 統(tǒng)計信息是否持久化存儲
| innodb_stats_persistent    | ON   |
-- innodb_stats_persistent=on,持久化統(tǒng)計信息采樣的抽樣頁數(shù)
| innodb_stats_persistent_sample_pages | 20   |
-- 不推薦使用,已經(jīng)被innodb_stats_transient_sample_pages替換
| innodb_stats_sample_pages   | 8   |
-- 瞬時抽樣page數(shù)
| innodb_stats_transient_sample_pages | 8   |
+--------------------------------------+-------------+
  • 除了因為抽樣導(dǎo)致統(tǒng)計基數(shù)不準(zhǔn)外,MVCC也會導(dǎo)致基數(shù)統(tǒng)計不準(zhǔn)確。例如:事務(wù)A先事務(wù)B開啟且未提交,事務(wù)B刪除部分?jǐn)?shù)據(jù),在可重復(fù)讀中事務(wù)A還可以查詢到刪除的數(shù)據(jù),此部分?jǐn)?shù)據(jù)目前至少有兩個版本,有一個標(biāo)識為deleted的數(shù)據(jù)。
  • 主鍵是直接按照表的行數(shù)來估計的,表的行數(shù),優(yōu)化器直接使用show table status like 't'的值
  • 手動觸發(fā)索引統(tǒng)計:
-- 重新統(tǒng)計索引信息
mysql> analyze table t;

排序?qū)λ饕x擇的影響

-- 創(chuàng)建表
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;
-- 定義測試數(shù)據(jù)存儲過程
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 ;
-- 執(zhí)行存儲過程,插入測試數(shù)據(jù)
mysql> CALL idata ();
-- 查看執(zhí)行計劃,使用了字段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 |
+----+-------------+-------+-------+---------------+-----+---------+------+-------+-----------------------+
-- 由于需要進(jìn)行字段b排序,雖然索引b需要掃描更多的行數(shù),但本身是有序的,綜合掃描行數(shù)和排序,優(yōu)化器選擇了索引b,認(rèn)為代價更小
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強(qiáng)制走索引a,糾正優(yōu)化器錯誤的選擇,不建議使用(不通用,且索引名稱更變語句也需要變)
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:引導(dǎo) MySQL 使用我們期望的索引,按b,a排序,優(yōu)化器需要考慮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:有些場景下,我們可以新建一個更合適的索引,來提供給優(yōu)化器做選擇,或刪掉誤用的索引
ALTER TABLE `t`
DROP INDEX `a`,
DROP INDEX `b`,
ADD INDEX `ab` (`a`,`b`) ;

索引優(yōu)化

索引選擇性

索引選擇性 = 基數(shù) / 總行數(shù)

-- 表t中字段xxx的索引選擇性
select count(distinct xxx)/count(id) from t;

索引的選擇性,指的是不重復(fù)的索引值(基數(shù))和表記錄數(shù)的比值。選擇性是索引篩選能力的一個指標(biāo),索引的取值范圍是 0~1 ,當(dāng)選擇性越大,索引價值也就越大。

在使用普通索引查詢時,會先加載普通索引,通過普通索引查詢到實際行的主鍵,再使用主鍵通過聚集索引查詢相應(yīng)的行,以此循環(huán)查詢所有的行。若直接全量搜索聚集索引,則不需要在普通索引和聚集索引中來回切換,相比兩種操作的總開銷可能掃描全表效率更高。

實際工作中,還是要看業(yè)務(wù)情況,如果數(shù)據(jù)分布不均衡,實際查詢條件總是查詢數(shù)據(jù)較少的部分,在索引選擇較低的列上加索引,效果可能也很不錯。

覆蓋索引

覆蓋索引可以減少樹的搜索次數(shù),顯著提升查詢性能,所以使用覆蓋索引是一個常用的性能優(yōu)化手段

-- 只需要查 ID 的值,而 ID 的值已經(jīng)在 k 索引樹上了,因此可以直接提供查詢結(jié)果,不需要回表
select ID from T where k between 3 and 5
-- 增加字段V,每次查詢需要返回V,可考慮把k、v做成聯(lián)合索引
select ID,V from T where k between 3 and 5

最左前綴原則+索引下推

-- id、name、age三列,name、age上創(chuàng)建聯(lián)合索引
-- 滿足最左前綴原則,name、age均走索引
select * from T where name='xxx' and age=12
-- Mysql自動優(yōu)化,調(diào)整name、age順序,,name、age均走索引
select * from T where age=12 and name='xxx'
-- name滿足最左前綴原則走索引,MySQL5.6引入索引下推優(yōu)化(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

聯(lián)合索引建立原則:

  • 如果通過調(diào)整順序,可以少維護(hù)一個索引,那么這個順序往往就是需要優(yōu)先考慮采用的
  • 空間:優(yōu)先小字段單獨建立索引,例如:name、age,可建立(name,age)聯(lián)合索引和(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:全文本索引,回表次數(shù)由符合條件的數(shù)據(jù)量決定
mysql> alter table SUser add index index1(email);
-- 方案2:前綴索引,回表次數(shù)由前綴匹配結(jié)果決定
mysql> alter table SUser add index index2(email(6));

前綴索引可以節(jié)省空間,但需要注意前綴長度的定義,在節(jié)省空間的同時,不能增加太多查詢成本,即減少回表驗證次數(shù)

如何設(shè)置合適的前綴長度?

-- 預(yù)設(shè)一個可以接受的區(qū)分度損失比,選擇滿足條件中最小的前綴長度
select count(distinct left(email,n))/count(distinct email) from SUser;

如果合適的前綴長度較長?

比如身份證號,如果滿足區(qū)分度要求,可能需要12位以上的前綴索引,節(jié)約的空間有限,又增加了查詢成本,就沒有必要使用前綴索引。此時,我們可以考慮使用以下方式:

倒序存儲

-- 查詢時字符串反轉(zhuǎn)查詢
mysql> select field_list from t where id_card = reverse('input_id_card_string');

使用hash字段

-- 創(chuàng)建一個整數(shù)字段,來保存身份證的校驗碼,同時在這個字段上創(chuàng)建索引
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等

前綴索引對覆蓋索引的影響?

-- 使用前綴索引就用不上覆蓋索引對查詢性能的優(yōu)化
select id,email from SUser where email='xxx';

唯一索引

建議使用普通索引,唯一索引無法使用change buffer,內(nèi)存命中率低

索引失效

  • 不做列運算,包括函數(shù)的使用,可能破壞索引值的有序性
  • 避免 %xxx 式查詢使索引失效
  • or語句前后沒有同時使用索引,當(dāng)or左右查詢字段只有一個是索引,該索引失效
  • 組合索引ABC問題,最左前綴原則
  • 隱式類型轉(zhuǎn)化
  • 隱式字符編碼轉(zhuǎn)換
  • 優(yōu)化器放棄索引,回表、排序成本等因素影響,改走其它索引或者全部掃描

總結(jié)

到此這篇關(guān)于Mysql索引選擇以及優(yōu)化的文章就介紹到這了,更多相關(guān)Mysql索引選擇優(yōu)化內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站!

美國服務(wù)器租用

版權(quán)聲明:本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有,歡迎引用、轉(zhuǎn)載,請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像,否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來,僅供學(xué)習(xí)參考,不代表本站立場,如有內(nèi)容涉嫌侵權(quán),請聯(lián)系alex-e#qq.com處理。

實時開通

自選配置、實時開通

免備案

全球線路精選!

全天候客戶服務(wù)

7x24全年不間斷在線

專屬顧問服務(wù)

1對1客戶咨詢顧問

在線
客服

在線客服:7*24小時在線

客服
熱線

400-630-3752
7*24小時客服服務(wù)熱線

關(guān)注
微信

關(guān)注官方微信
頂部