柚子快報激活碼778899分享：MySQL —— 索引
柚子快報激活碼778899分享：MySQL —— 索引
索引的概念
MySQL的索引是?種數(shù)據(jù)結(jié)構(gòu)，它可以幫助數(shù)據(jù)庫高效地查詢、更新數(shù)據(jù)表中的數(shù)據(jù)。索引通過 ?定的規(guī)則排列數(shù)據(jù)表中的記錄，使得對表的查詢可以通過對索引的搜索來加快速度。
MySQL索引類似于書籍的目錄，通過指向數(shù)據(jù)行的位置，可以快速定位和訪問表中的數(shù)據(jù)，比如 漢語字典的目錄（索引）頁，我們可以按筆畫、偏旁部首、拼音等排序的?錄（索引）快速查找到需 要的字。
探討索引的數(shù)據(jù)結(jié)構(gòu)
我們來一起探討那種數(shù)據(jù)結(jié)構(gòu)用來當(dāng)作索引更適合數(shù)據(jù)庫
首先是哈希表，對于哈希表來說，查找的時間復(fù)雜度為 O(1)，十分優(yōu)秀，但是卻不適合數(shù)據(jù)庫，因為哈希表不支持范圍查找，而我們數(shù)據(jù)庫是需要支持范圍查找的，例如：我們有張成績表，要查詢成績大于等于60以上的學(xué)生時，這時就要求我們數(shù)據(jù)庫能支持**范圍查找，**所以哈希表并不適合作索引
接下來登場的是二叉搜索樹，查找的時間復(fù)雜度為O(logN)，但是如果出現(xiàn)極端情況，二叉搜索樹是可能會退化成一顆單分支的樹，這時候時間復(fù)雜度就變成O(N)，還是不理想，這時候大家可能會想到AVL樹或者紅黑樹，AVL樹就不說了，旋轉(zhuǎn)次數(shù)太多了，在數(shù)據(jù)庫面前，數(shù)據(jù)量十分龐大，如果旋轉(zhuǎn)，呵呵… 我們來看一下紅黑樹，雖然保持相對平衡，但是在數(shù)據(jù)一多的情況下，我們無法保證樹到底有多高
為什么要討論樹高呢？ 因為數(shù)據(jù)庫的數(shù)據(jù)是存儲在磁盤上的，所以當(dāng)你需要讀取數(shù)據(jù)的時候，是需要進行磁盤的IO的，磁盤的IO速度是十分慢的，如果IO 次數(shù)一高，效率就會低下哎，所以我們應(yīng)該減少磁盤IO次數(shù)，也就是減小樹高，那么紅黑樹就不能滿足了
磁盤IO 是制約數(shù)據(jù)庫性能的主要因素
既然紅黑樹不行，那我們可以考慮B樹，這時一顆多路平衡樹，由于是多路，所以可以降低書高，但是我們的MySQL還是不滿意，覺得效率還不是不夠高，于是MySQL 就使用B樹的變形也就是B+樹，我們在前面就知道B+樹有一些特點：真實的數(shù)據(jù)都是保存在葉子節(jié)點上的，非葉子結(jié)點只是起到一個導(dǎo)航的作用，并且葉子結(jié)點是使用雙向鏈表進行連接的，所以在數(shù)據(jù)庫進行范圍查找的時候十分方便。
B樹示例：
B+樹示例： B+樹的時間復(fù)雜度是O(logN)，并且可以有效的控制樹高
B+樹與B樹的對比： 1.B+樹的葉子結(jié)點之間有相互連接的引用，可以通過這個連接找到與其相鄰的兄弟節(jié)點，mysql 在組織葉子結(jié)點時使用的時雙向鏈表 2.非葉子結(jié)點的值包涵在葉子結(jié)點中，MySQL 非葉子結(jié)點只保存了對子結(jié)點的引用，沒有保存真實的數(shù)據(jù)，所有真實的數(shù)據(jù)都是在葉子結(jié)點中 3.對于B+樹而言，在相同書高的情況下，查找任意元素的時間復(fù)雜度都是一樣的，性能均衡。
MySQL的頁
在 .ibd 文件中最重要的結(jié)構(gòu)體是 Page(頁)，頁是內(nèi)存與磁盤交互最小單元，默認大小是 16KB
每一個.ibd 文件由頁組成
每次內(nèi)存與磁盤的交互至少讀取一頁，所以在磁盤中每個頁內(nèi)部的地址都是連續(xù)的，之所以這么做，是因為在使用數(shù)據(jù)的過程中，根據(jù)局部性原理，將來要使用的數(shù)據(jù)大概率與當(dāng)前訪問的數(shù)據(jù)在空間上是臨近的所以一次從磁盤中讀取一頁的數(shù)據(jù)放入內(nèi)存中，當(dāng)下次查詢的數(shù)據(jù)還在這個頁中的時候，就可以直接從內(nèi)存中讀取，從而減少磁盤IO，以此來提高性能。
局部性原理： 是指程序在執(zhí)行時呈現(xiàn)出局部性規(guī)律，在一段時間內(nèi)，整個程序的執(zhí)行僅限于程序中的某?部 分。相應(yīng)地，執(zhí)行所訪問的存儲空間也局限于某個內(nèi)存區(qū)域，局部性通常有兩種形式：時間局部 性和空間局部性。 時間局部性（Temporal Locality）：如果?個信息項正在被訪問，那么在近期它很可能還會被再 次訪問。 空間局部性（Spatial Locality）：將來要用到的信息大概率與正在使用的信息在空間地址上是臨 近的。
每一個頁中即使沒有數(shù)據(jù)也會使用 16KB 的存儲空間，同時與索引的B+樹中的節(jié)點對應(yīng)
我們可以查詢MySQL中頁的大?。?/p>
16384 / 1024 = 16 KB
數(shù)據(jù)頁
數(shù)據(jù)庫由很多種的頁，索引頁、數(shù)據(jù)頁等等，這里我們討論數(shù)據(jù)頁：
頁頭頁尾
數(shù)據(jù)頁由頁頭和頁尾：
這里我們關(guān)注的是頁頭里包含上一頁頁號和下一頁頁號，通過這兩個屬性可以把頁與與之間鏈接起來，形成一個雙向鏈表。
頁主體
頁主體部分是保存真實數(shù)據(jù)的主要區(qū)域，每當(dāng)創(chuàng)建一個新頁時，都會自動分配兩個行，一個是頁內(nèi)最小行Infinum，另一個是頁內(nèi)最大行Supremun，這兩行并不存儲任何真實數(shù)據(jù)，而是最為數(shù)據(jù)行鏈表的頭和尾【你可以理解為鏈表的兩個空節(jié)點，一個是空的頭節(jié)點，另一個是空的尾節(jié)點】
第一個數(shù)據(jù)行有一個記錄下一行的地址偏移量的區(qū)域next_record將頁內(nèi)所有的數(shù)據(jù)行組成了一個單向鏈表
當(dāng)向一個新頁插入數(shù)據(jù)時，將Infimun 連接第一個數(shù)據(jù)行，最后一行真實數(shù)據(jù)連接Supremun，這樣數(shù)據(jù)行就構(gòu)成了一個單向鏈表，當(dāng)更多的數(shù)據(jù)行插入之后，會按照主鍵從小到大的順序進行連接：
頁目錄
看到這里，大家是不是又疑惑，單向鏈表的查找效率不是很慢嗎？時間復(fù)雜度是O(N)，當(dāng)然開發(fā)數(shù)據(jù)庫的人也想到了，為了提高查找效率，InnoDB采用二分查找來解決查詢效率問題，于是他們開發(fā)了頁目錄：
具體的實現(xiàn)方式是，在每一個頁中加入一個叫做頁目錄的結(jié)構(gòu)，將頁內(nèi)包括最大行和最小行在內(nèi)的數(shù)據(jù)行進行分組，這里單獨約定最小行獨自為一組，其他分組最多8條數(shù)據(jù)，同時將最大行放在最后一個分組中，按照主鍵從小到大的順序記錄在頁目錄中，頁目錄每一個位置稱為槽，每一個槽對應(yīng)一個分組，一旦分組中數(shù)據(jù)行超過8個的時候，就會分裂出一個新的分組
在后續(xù)查找某條數(shù)據(jù)行時，通過二分查找，就可以找到對應(yīng)的槽，然后在槽對應(yīng)的分組中進行最多8條數(shù)據(jù)的便即可獲得目標(biāo)數(shù)據(jù)
數(shù)據(jù)頁頭
數(shù)據(jù)頁頭記錄了當(dāng)前頁保存數(shù)據(jù)相關(guān)的信息
B+樹在數(shù)據(jù)庫中的應(yīng)用
非葉子節(jié)點保存索引數(shù)據(jù)，葉子節(jié)點保存真實數(shù)據(jù)
舉個例子：查找 id 為7 的數(shù)據(jù)行，首先通過索引頁1，發(fā)現(xiàn) 7 = 7 ，進入到索引頁2，然后 7 < 9，進入到數(shù)據(jù)頁4，最后找到id 為 7 的數(shù)據(jù)行，一共進行了三次磁盤IO。
以上的IO過程，加載索引頁1–>加載索引頁2–>加載數(shù)據(jù)頁3
我們現(xiàn)在來感受B+樹的強大：
假設(shè)一條數(shù)據(jù)大小為 1KB，忽略數(shù)據(jù)頁中頁頭頁尾等等非真實數(shù)據(jù)的內(nèi)容，也就是說一個數(shù)據(jù)頁可以保存16條數(shù)據(jù)。
索引頁保存的是索引，這里我們以主鍵索引為例，索引頁保存主鍵值和下一頁的地址，主鍵用bigint 來保存，也就是8字節(jié)，地址為6字節(jié)，一共就是14字節(jié)，頁的大小為16KB，那么一個索引頁可以保存 16 * 1024 / 14 = 1170 條索引記錄
如果B+樹樹高為三層的話，一條索引記錄對應(yīng)一個數(shù)據(jù)頁，那么三層B+樹就可以保存 1170 * 1170 * 16 = 21,902,400 條數(shù)據(jù)（第一層一個結(jié)點，有1170 條索引，那么第二層就有1170個結(jié)點，第二層每個結(jié)點都有1170條索引），也就是說如果是在兩千多萬條數(shù)據(jù)的表中，我們通過三次IO 就可以完成數(shù)據(jù)的檢索了。
索引的分類
主鍵索引
當(dāng)在表中定義了一個主鍵primary key 的時候，MySQL會將其作為索引，這個索引我們稱之為主鍵索引。
我們推薦在每一張表都去創(chuàng)建一個主鍵，如果沒有的話，可以添加一個自增列。
唯一索引
當(dāng)在表中定義一個唯一鍵unique 時，MySQL會自動創(chuàng)建索引，這個索引被稱之為唯一索引
普通索引
最基本的索引類型，沒有唯一性的限制，也就是相比于唯一索引來說，可以存在重復(fù)的列
普通索引可以由多列組合組成，這時候可以稱為復(fù)合索引。
普通索引是由用戶自己手動創(chuàng)建的，如果用戶覺得某個列查詢比較頻繁，可以考慮為這一列創(chuàng)建索引。
全文索引
基于文本列（char、varchar）上創(chuàng)建的索引，叫做全文索引。
全文索引可以加快對這列文本列包含的數(shù)據(jù)查詢和DML操作，用于全文搜索，僅有MyISAM 和 InnoDB 引擎支持該索引。
聚集索引
與主鍵索引是同義詞，也就是說主鍵索引就是聚集索引，也可以稱為聚簇索引。
如果表中沒有定義主鍵primary key ，InnoDB 會使用第一個 unique 和 not null 的列作為聚集索引。
如果表中實在沒有primary key 或者合適的 unique 索引，InnoDB 會為新生成的行生成一個行號為 6 字節(jié)的 ROW_ID 字段記錄，ROW_ID 是單調(diào)遞增的，并且使用 ROW_ID 作為索引。
注意ROW_ID 是MySQL內(nèi)部的設(shè)置，用戶是無法獲取的，MySQL使用這個索引是為了更好地創(chuàng)建索引樹來管理數(shù)據(jù)。
非聚集索引
非聚集索引（也可以稱為二級索引），就是除了聚集索引以外的索引就是非聚集索引。
二級索引中的每條記錄都包含改行的主鍵列，以及二級索引指定的列。
InnoDB 會使用這個主鍵值來搜索聚集索引中的行，這個過程稱為回表查詢
索引覆蓋
當(dāng)一個 select 語句使用了普通索引且查詢列表中的列剛好是創(chuàng)建普通索引時的所有或部分列，這時候就可以直接返回數(shù)據(jù)，就不用進行回表查詢，這樣的現(xiàn)象被稱為索引覆蓋
簡單解釋一下：聚集索引創(chuàng)建的索引樹是包含完整的真實數(shù)據(jù)的，非聚集索引創(chuàng)建的索引樹是沒有包含完整的真實數(shù)據(jù)但是包含對應(yīng)的主鍵值，如果通過非聚集索引查詢到的數(shù)據(jù)不滿足用戶要求會通過這個主鍵值來到主鍵索引樹獲取完整的數(shù)據(jù)，這個現(xiàn)象就是回表查詢，也就是查了兩個索引樹
如果通過非聚集索引的索引樹能獲取到目標(biāo)數(shù)據(jù)，就不需要進行回表查詢，直接返回數(shù)據(jù)即可，這就是索引覆蓋
舉個例子：我們有一張學(xué)生表，包含學(xué)生的 id ，姓名，班級，其中定義學(xué)生的 id 為主鍵值，姓名字段定義為普通索引，現(xiàn)在我要查詢張三的所有信息：
由于姓名就是索引，通過張三到姓名的索引樹中查找，隨后只能獲得主鍵值id + 姓名為張三的數(shù)據(jù)，但是我們要的是張三所有的數(shù)據(jù)，所以數(shù)據(jù)庫就會通過主鍵值到主鍵索引樹去查找張三的完整的數(shù)據(jù)，這就是回表查詢
如果我們至少想查張三的姓名：select name from student where name = '張三'; 這時廢話sql ，至少作為演示，數(shù)據(jù)庫通過姓名這個索引樹就可以得到張三這個名字，直接返回數(shù)據(jù)，不需要進行回表查詢，這就是索引覆蓋
索引的使用
自動創(chuàng)建
當(dāng)表中定義了主鍵、外鍵、唯一鍵，MySQL都會為其創(chuàng)建對應(yīng)的索引以及索引樹。
如果表中沒有任何約束，MySQL回自動為每一列生成一個索引并且使用ROW_ID 進行標(biāo)識
手動創(chuàng)建
主鍵索引
在創(chuàng)建表時，直接在字段后面定義主鍵：
create table test0 (
id bigint primary key auto_increment,
name varchar(50)
);
在創(chuàng)建表時，先定義好字段，最后定義主鍵
create table test0 (
id bigint auto_increment,
name varchar(50),
primary key(id)
);
創(chuàng)建表之后，我們可以通過修改表來定義主鍵
create table test0 (
id bigint,
name varchar(50)
);
alter table test0 add primary key(id);
如果你還想讓主鍵自增，還可以在寫下面的語句：
alter table test0 modify id bigint auto_increment;
modify 后面要跟完整的字段定義語句。
唯一索引
在創(chuàng)建表時在定義字段的同時，直接定義唯一鍵unique
create table test1 (
id bigint,
name varchar(50) unique
);
創(chuàng)建表時，在定義完字段后，單獨定義唯一鍵unique
create table test1 (
id bigint,
name varchar(50),
unique(name)
);
在創(chuàng)建表后可以通過修改表的字段來定義唯一鍵
create table test1 (
id bigint,
name varchar(50)
);
alter table test1 add unqiue(name);
普通索引
創(chuàng)建表時，再創(chuàng)建完字段后，定義普通索引index
create table test2 (
id bigint,
name varchar(50),
index(name)
);
通過查看表結(jié)構(gòu)，我們得知普通索引的標(biāo)記為MUL
創(chuàng)建完表后，可以通過修改表的方式添加普通索引index
create table test2 (
id bigint,
name varchar(50)
);
alter table test2 add index(name);
再創(chuàng)建表后，可以通過 create 語句手動創(chuàng)建普通索引
語法形式：create index index_name on table_name(字段名);
index_name 表示索引名，也就是說你可以自己命名索引。
推薦索引命名為 ind_表名_字段名，這樣我們在查詢索引的時候，可以得知這是在哪張表的普通索引，并且是哪個字段。
create table test2 (
id bigint,
name varchar(50)
);
create index ind_test2_name on test2(name);
復(fù)合索引
和創(chuàng)建普通主鍵一樣，只是多加一些字段名。這里直接上sql 語句
create table test3(
id bigint primary key auto_increment,
sn bigint unique,
name varchar(50) not null,
class_name varchar(20) not null,
index(sn,name)
);
create table test3(
id bigint primary key auto_increment,
sn bigint unique,
name varchar(50) not null,
class_name varchar(20) not null
);
alter table test3 add index (sn,name);
create table test3(
id bigint primary key auto_increment,
sn bigint unique,
name varchar(50) not null,
class_name varchar(20) not null
);
create index ind_test3_sn_name on test3(sn,name);
注意事項
索引的創(chuàng)建應(yīng)該建立在高頻的查詢列上，并且數(shù)據(jù)量比較大，因為數(shù)據(jù)量小盡管這個是高頻查詢的列，但是全表掃描會比索引樹要更快一些。
索引需要占用額外的空間，每創(chuàng)建一個索引都會創(chuàng)建對應(yīng)的索引樹。
對于表進行插入、刪除、更新操作的時候，由于同時會修改索引樹，可能會影響到性能。
創(chuàng)建過多或者不合理的索引會導(dǎo)致性能的下降，所以我們要謹慎創(chuàng)建索引。
查看索引
有三種方式查看索引：方式一：show keys from table_name;
我們來看一下上面的表格，Table 表示表名， Non_unique 表示是否不唯一 ：0 表示否（唯一）1表示是（不唯一） Seq_in_index 表示在索引中的標(biāo)號，從 1 開始排號，test3 有一個復(fù)合索引（sn, name），所以 name 標(biāo)號為 2 Column_name 表示對應(yīng)的字段名 Key_name 表示索引名，主鍵索引默認索引名為 PRIMARY Index_type 表示索引的數(shù)據(jù)結(jié)構(gòu)，這里顯示BTREE，說明是B樹，其實就是B+樹，B+樹是B樹的變形。
方式二：show index from table_name;
獲得的結(jié)果集和上面是一樣的：
方式三：desc table_name; 這是簡單查詢索引：
刪除索引
刪除主鍵索引：alter table table_name drop primary key; 如果發(fā)生上面的報錯信息，是因為這個主鍵值帶有自增屬性，我們要先刪除其自增的屬性，然后才能進行刪除主鍵索引的操作：
alter table test3 modify id bigint;
刪除了自增屬性后，可以進行主鍵索引的刪除：alter table test3 drop primary key;
刪除其他索引：alter table table_name drop index 索引名;
查看查詢語句有沒有走索引
以學(xué)生表為例： 查看索引： 進行全表數(shù)據(jù)獲?。簊elect * from student;
查看查詢語句有沒有走索引的語法格式為：explain 查詢語句
我們現(xiàn)在來看一下全表數(shù)據(jù)獲取有沒有走索引：
介紹一下：select_type 表示查詢方式，type 表示訪問類型，key 表示索引，possible_keys 表示可能走的索引
由上圖可知：查詢?yōu)楹唵尾樵?，訪問類型為ALL(全表掃描)，key 索引為 NULL，說明沒有走索引。
我們來看一下 type:
ALLindexrangerefeq_refconst,systemNULL
從左到右，性能越來越好
ALL：掃描全表 index : 掃描全部索引樹 range: 掃描部分索引，在索引范圍掃描，對索引的掃描開始于某一點，返回匹配值域的行，常見于 between、<、>等查詢 ref : 使用非唯一索引或非唯一索引前綴進行的查找，不是主鍵或者不是唯一索引 eq_ref：唯一性索引掃描，遂于每個索引鍵，表中只要一條記錄與之匹配，常見于主鍵或唯一索引掃描 const，system : 單表中最多有一個匹配行，查詢起來非常迅速，例如根據(jù)主鍵或唯一索引查詢。system 是const類型的特例，當(dāng)查詢的表只有一行的情況下，使用system NULL:不用訪問表或者索引，直接就能得到結(jié)果
我們使用主鍵來進行查詢： 可以看出走的是主鍵索引，并且進行的是范圍查找。
最后我們來看一下Extra: Using index: 表示使用索引，如果只有 Using index ，說明他沒有查詢到數(shù)據(jù)表，只用索引表就完成了這次查詢，這個也叫索引覆蓋 Using where: 表示條件查詢，如果不讀取表中的所有數(shù)據(jù)，或者不是僅僅通過索引樹就能獲得所許需要的數(shù)據(jù)的時候，則會出現(xiàn) Using where
小結(jié)
創(chuàng)建索引：主鍵索引(primary key) 唯一索引(unique) 普通索引(index)
創(chuàng)建索引可以在創(chuàng)建表中定義，也可以在表外創(chuàng)建
表外創(chuàng)建索引：alter table table_name add primary key / unique / index 字段名; 還有 普通索引的專屬創(chuàng)建形式create index 索引名 on table_name(字段名,字段名...);
修改表的字段：alter table table_name modify 字段定義語句; 通過字段定義語句就可以修改對應(yīng)的字段屬性了。
查看索引的方法：show keys/index from table_name 或者簡單查看索引desc table_name; 簡單查看索引的結(jié)果集的Key: PRI 表示主鍵索引，UNI表示唯一索引，MUL 表示普通索引
刪除索引：alter table table_name (primary key) / (index 索引名);
柚子快報激活碼778899分享：MySQL —— 索引
參考閱讀
本文內(nèi)容根據(jù)網(wǎng)絡(luò)資料整理，出于傳遞更多信息之目的，不代表金鑰匙跨境贊同其觀點和立場。
轉(zhuǎn)載請注明，如有侵權(quán)，聯(lián)系刪除。
Shoprite超市出海棧
