一、索引與約束

1、索引是什么

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

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

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

2、索引的分類

列的屬性-索引約束

• 主鍵索引：非空唯一索引，一個表只有一個主鍵索引；在 innodb 中，主鍵索引的 B+ 樹包含表數(shù)據(jù)信息；
• 唯一索引：不可以出現(xiàn)相同的值，可以有 NULL 值；
• 普通索引：允許出現(xiàn)相同的索引內(nèi)容；
• 組合索引：對表上的多個列進行索引；
• 全文索引：將存儲在數(shù)據(jù)庫當中的整本書和整篇文章中的任意內(nè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[,...]);

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

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

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

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

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

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

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

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

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

索引實現(xiàn)-物理存儲

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

3、使用索引的場景

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

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

學習了上面所講述的B+樹和索引之后來想一下下面幾個經(jīng)典的面試題吧：

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

二、索引方式

1、聚集索引

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

一般來說主鍵索引就可以作為聚集索引，當沒有主鍵的時候，如果有唯一索引，那唯一索引也可以作為聚集索引。18

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

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

2、輔助索引（二級索引）

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

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

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

3、覆蓋索引

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

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

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

KEY（id，name，age）

5、索引下推

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

三、索引的失效和原則

1、索引失效

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

2、索引原則

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

四、怎么解決慢的問題

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

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

總結(jié)

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

最新評論