一、索引與約束

1、索引是什么

索引是一種有序的存儲(chǔ)結(jié)構(gòu)，它按照單個(gè)或者多個(gè)列的值進(jìn)行排序。

并且它分為：主鍵索引、唯一索引、普通索引、組合索引、以及全文索引。

我們使用索引的目的就是為了提升搜索的效率。

2、索引的分類

列的屬性-索引約束

• 主鍵索引：非空唯一索引，一個(gè)表只有一個(gè)主鍵索引；在 innodb 中，主鍵索引的 B+ 樹(shù)包含表數(shù)據(jù)信息；
• 唯一索引：不可以出現(xiàn)相同的值，可以有 NULL 值；
• 普通索引：允許出現(xiàn)相同的索引內(nèi)容；
• 組合索引：對(duì)表上的多個(gè)列進(jìn)行索引；
• 全文索引：將存儲(chǔ)在數(shù)據(jù)庫(kù)當(dāng)中的整本書和整篇文章中的任意內(nèi)容信息查找出來(lái)的技術(shù)；關(guān)鍵詞 FULLTEXT; 在短字符串中用 LIKE % ；在全文索引中用 match 和 against ；

-- 主鍵索引
PRIMARY KEY(key1, key2)

--唯一索引
UNIQUE(key)

--普通索引
INDEX(key)
-- OR
KEY(key[,...])

--組合索引
INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);

約束：為了實(shí)現(xiàn)數(shù)據(jù)的完整性，對(duì)于 innodb，提供了以下幾種約束，primary key，unique key，foreign key，default，not null；

其中外鍵約束：用來(lái)關(guān)聯(lián)兩個(gè)表，來(lái)保證參照完整性；MyISAM 存儲(chǔ)引擎本身并不支持外鍵，只起到注釋作 用；而 innodb 完整支持外鍵，并具備事務(wù)性；

創(chuàng)建主鍵索引或者唯一索引的時(shí)候同時(shí)創(chuàng)建了相應(yīng)的約束；但是約束時(shí)邏輯上的概念；索引是一個(gè)數(shù)據(jù)結(jié)構(gòu)既包含邏輯的概念也包含物理的存儲(chǔ)方式；

數(shù)據(jù)結(jié)構(gòu)

索引包括多種數(shù)據(jù)結(jié)構(gòu)，其中最常用的就是B+數(shù)索引，hash索引，全文索引。我們本文主要討論的是在InnoDB引擎中所使用的B+數(shù)索引。那么為什么我們不使用紅黑樹(shù)呢？

首先B+樹(shù)全稱：多路平衡搜索樹(shù)。對(duì)于瘦高的紅黑樹(shù)來(lái)說(shuō)B+樹(shù)是胖矮的。我們把所有的數(shù)據(jù)存放在葉子節(jié)點(diǎn)中，而且葉子節(jié)點(diǎn)還串聯(lián)在一起，一個(gè)頁(yè)中可以存放幾個(gè)葉子節(jié)點(diǎn)，而非葉子節(jié)點(diǎn)存放索引內(nèi)容，并且也放在頁(yè)中，我們可以看下圖。

當(dāng)我們查找一個(gè)數(shù)據(jù)的時(shí)候，可以使用更少的磁盤IO就可以獲得想要的數(shù)據(jù)。比如我們想要查找25這個(gè)節(jié)點(diǎn)的數(shù)據(jù)，先查找第一個(gè)頁(yè)，找到25的位置，在看第二個(gè)頁(yè)，，在找到第一個(gè)葉子節(jié)點(diǎn)，然后平移過(guò)去找到25這個(gè)節(jié)點(diǎn)，一共有4次磁盤IO（每次查找頁(yè)就是一次IO）。

但是使用的是紅黑樹(shù)的話，那么就不止4次的磁盤IO了。當(dāng)然除了更少的磁盤IO后，也是為了方便范圍查找。對(duì)于B+樹(shù)來(lái)說(shuō)，他的葉子節(jié)點(diǎn)都串聯(lián)在一起，當(dāng)找到第一個(gè)節(jié)點(diǎn)之后，就可以相繼找出其他節(jié)點(diǎn)。但是紅黑樹(shù)來(lái)說(shuō)的話，每次都要重新查找葉子節(jié)點(diǎn)。

總結(jié)：可以減少磁盤訪問(wèn)次數(shù)；用來(lái)組織磁盤數(shù)據(jù)，以頁(yè)為單位，物理磁盤頁(yè)一般為 4K，innodb 默認(rèn)頁(yè)大小為 16K；對(duì)頁(yè)的訪問(wèn)是一次磁盤 IO，緩存中會(huì)緩存常訪問(wèn)的頁(yè)； 平衡二叉樹(shù)（紅黑樹(shù)、AVL 樹(shù)） 特征：非葉子節(jié)點(diǎn)只存儲(chǔ)索引信息，葉子節(jié)點(diǎn)存儲(chǔ)具體數(shù)據(jù)信息；葉子節(jié)點(diǎn)之間互相連接，方便范圍查詢； 每個(gè)索引對(duì)應(yīng)著一個(gè) B+ 樹(shù)；

索引實(shí)現(xiàn)-物理存儲(chǔ)

innodb 由段、區(qū)、頁(yè)組成；段分為數(shù)據(jù)段、索引段、回滾段等；區(qū)大小為 1 MB（一個(gè)區(qū)由 64 個(gè) 連續(xù)頁(yè)構(gòu)成）；頁(yè)的默認(rèn)值為 16k；頁(yè)為邏輯頁(yè)，磁盤物理頁(yè)大小一般為 4K 或者 8K；為了保證區(qū)中的頁(yè)連續(xù)，存儲(chǔ)引擎一般一次從磁盤中申請(qǐng) 4~5 個(gè)區(qū)；

3、使用索引的場(chǎng)景

我們每次搜索數(shù)據(jù)都是通過(guò)索引來(lái)實(shí)現(xiàn)的，其中在哪里可以使用到索引呢？是在where，group by，order by后面使用索引的。那么哪些場(chǎng)景不適合使用索引呢？首先就是沒(méi)有where，group by，order by的地方，還有區(qū)分度不高的列，需要經(jīng)常修改的列，表數(shù)據(jù)量少。

我們創(chuàng)建B+樹(shù)類型的索引就是為了通過(guò)比較來(lái)找到我們所需要的數(shù)據(jù)，但是當(dāng)區(qū)分度不高的時(shí)候，反而會(huì)降低速度，如果經(jīng)常修改這個(gè)列，那么我們的B+的結(jié)構(gòu)就要經(jīng)常變化，更加影響速率，表的數(shù)據(jù)較少的時(shí)候，沒(méi)有必要去創(chuàng)建索引，創(chuàng)建索引反而會(huì)浪費(fèi)空間。

學(xué)習(xí)了上面所講述的B+樹(shù)和索引之后來(lái)想一下下面幾個(gè)經(jīng)典的面試題吧：

• 為什么采用多路的樹(shù)結(jié)構(gòu)？一個(gè)節(jié)點(diǎn)有多條鏈路，相較于平衡二叉搜索樹(shù)是一個(gè)更加矮胖的結(jié)構(gòu)，樹(shù)的高度更低，可以較少的磁盤io次數(shù)來(lái)索引數(shù)據(jù)。
• 為什么非葉子節(jié)點(diǎn)只存儲(chǔ)索引信息？B+樹(shù)節(jié)點(diǎn)映射固定的大小磁盤數(shù)據(jù)，可以包含更多的索引信息。能快速鎖定數(shù)據(jù)所在葉子節(jié)點(diǎn)的位置。
• 為什么葉子節(jié)點(diǎn)依次相連？便于范圍查詢，避免中序遍歷回溯回去查找下一個(gè)節(jié)點(diǎn)。

二、索引方式

1、聚集索引

按照主鍵構(gòu)造的 B+ 樹(shù)，葉子節(jié)點(diǎn)中存放數(shù)據(jù)頁(yè)中，數(shù)據(jù)也是索引的一部分。

一般來(lái)說(shuō)主鍵索引就可以作為聚集索引，當(dāng)沒(méi)有主鍵的時(shí)候，如果有唯一索引，那唯一索引也可以作為聚集索引。18

-- user表中 有id主鍵
select * from user where id >= 18 and id < 40;

我們通過(guò)上面的SQL語(yǔ)句，進(jìn)行主鍵索引（聚集索引），從結(jié)構(gòu)中查找18的位置，然后一層一層找，最后在葉子節(jié)點(diǎn)中找到，然后18到40的位置是連續(xù)的，我們節(jié)點(diǎn)的查找也是順序的。并且這里的葉子節(jié)點(diǎn)，全部都是保存的數(shù)據(jù)。

2、輔助索引（二級(jí)索引）

葉子節(jié)點(diǎn)不包含行記錄的全部數(shù)據(jù)，輔助索引的葉子節(jié)點(diǎn)中，除了用來(lái)排序的 key 還包含一個(gè) bookmark ，該書簽存儲(chǔ)了聚集索引的 key；

-- user表 包含 id name lockyNum; id是主鍵，lockyNum 輔助索引;
select * from user where lockyNum = 33;

由于這里使用的是輔助索引，在輔助索引中，葉子節(jié)點(diǎn)中存儲(chǔ)的并不是數(shù)據(jù)，而是主鍵的id，當(dāng)我們通過(guò)輔助索引找到相應(yīng)的位置之后，根據(jù)查找到的主鍵id，再進(jìn)入聚集索引中，然后操作就是上面聚集索引的過(guò)程了。后面簡(jiǎn)稱回表查詢。

3、覆蓋索引

從輔助索引中就能找到數(shù)據(jù)，而不需通過(guò)聚集索引查找；利用輔助索引樹(shù)高度一般低于聚集索引 樹(shù)；較少磁盤 IO；在實(shí)際中我們select后一定不要*，而是具體的寫出想要查找什么字段。

4、最左匹配規(guī)則

對(duì)于組合索引，從左到右依次匹配，遇到 > < between like 就停止匹配；在下面的索引中，是組合索引，當(dāng)我們使用id，name，age；id，name；id；這三種方式去索引的話，就可以走索引結(jié)構(gòu)，但是一旦前面沒(méi)有id之后，那么就不會(huì)走索引結(jié)構(gòu)。也就是說(shuō)，最左匹配規(guī)則，必須要按著從左往右的順序來(lái)。

KEY（id，name，age）

5、索引下推

為了減少回表次數(shù)，提升查詢效率；在 MySQL 5.6 的版本開(kāi)始推出； MySQL 架構(gòu)分為 server 層和存儲(chǔ)引擎層； 沒(méi)有索引下推機(jī)制之前，server 層向存儲(chǔ)引擎層請(qǐng)求數(shù)據(jù)，在 server 層根據(jù)索引條件判斷進(jìn)行數(shù)據(jù) 過(guò)濾； 有索引下推機(jī)制之后，將部分索引條件判斷下推到存儲(chǔ)引擎中過(guò)濾數(shù)據(jù)；最終由存儲(chǔ)引擎將數(shù)據(jù)匯 總返回給 server 層；

三、索引的失效和原則

1、索引失效

• 1、select ... where A and B 若 A 和 B 中有一個(gè)不包含索引，則索引失效；
• 2、索引字段參與運(yùn)算，則索引失效；例如： from_unixtime(idx) = '2021-04-30'; 改成 idx = unix_timestamp("2021-04-30")
• 3、索引字段發(fā)生隱式轉(zhuǎn)換，則索引失效；例如：將列隱式轉(zhuǎn)換為某個(gè)類型，實(shí)際等價(jià)于在索引列上作 用了隱式轉(zhuǎn)換函數(shù)；
• 4、LIKE 模糊查詢，通配符 % 開(kāi)頭，則索引失效；例如： select * from user where name like '%Mark';
• 5、在索引字段上使用 NOT <> != 索引失效；如果判斷 id <> 0 則修改為 idx > 0 or idx < 0 ；
• 6、組合索引中，沒(méi)使用第一列索引，索引失效；

2、索引原則

• 1、查詢頻次較高且數(shù)據(jù)量大的表建立索引，索引選擇使用頻次較高，過(guò)濾效果好的列或者組合；
• 2、使用短索引；節(jié)點(diǎn)包含的信息多，較少磁盤 IO 操作；比如： smallint ， tinyint ；
• 3、對(duì)于很長(zhǎng)的動(dòng)態(tài)字符串，考慮使用前綴索引；
• 4、對(duì)于組合索引，考慮最左側(cè)匹配原則、覆蓋索引；
• 5、盡量選擇區(qū)分度高的列作為索引；該列的值相同的越少越好；
• 6、盡量擴(kuò)展索引，在現(xiàn)有索引的基礎(chǔ)上，添加復(fù)合索引；最多 6 個(gè)索引；
• 7、不要 select * ； 盡量只列出需要的列字段；方便使用覆蓋索引；
• 8、索引列，列盡量設(shè)置為非空；
• 9、可以開(kāi)啟自適應(yīng) hash 索引或者調(diào)整 change buffer；

四、怎么解決慢的問(wèn)題

我們通過(guò)使用 EXPLAIN 來(lái)查看 SQL 語(yǔ)句的具體執(zhí)行過(guò)程。 原理：模擬優(yōu)化器執(zhí)行 SQL 查詢語(yǔ)句，從而知道 MySQL 是如何處理 SQL 語(yǔ)句的。

首先我們需要找到SQL這個(gè)語(yǔ)句在哪里，通過(guò) show processlist 列出較慢的連接通道來(lái) 以及使用慢查詢?nèi)罩緛?lái)找到具體的SQL語(yǔ)句。再分析SQL中我們要先查看在where、group by、order by中是否使用索引，如果沒(méi)有使用，那么就可以考慮是否添加索引，然后繼續(xù)優(yōu)化SQL語(yǔ)句中in和not in 變成聯(lián)合查詢，并且減少整體的聯(lián)合查詢。以及一個(gè)隱形的問(wèn)題：age問(wèn)題，應(yīng)該存儲(chǔ)出生年月，讓客戶端進(jìn)行計(jì)算年紀(jì)。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論