筆記很無味，點贊請準備。

image

索引定義

數(shù)據(jù)庫索引，是數(shù)據(jù)庫管理系統(tǒng)中一個排序的數(shù)據(jù)結構，以協(xié)助快速查詢，更新數(shù)據(jù)庫表中的數(shù)據(jù)。

從定義中可以看出，索引其實就是一種數(shù)據(jù)結構。數(shù)據(jù)都是以文件的形式存儲在磁盤上的，每一行數(shù)據(jù)都有它的磁盤地址，如果沒有索引，要從幾百萬行數(shù)據(jù)中檢索一條數(shù)據(jù)，只能遍歷整張表才能找過結果。有了索引之后，只需要在索引里面去檢索這條數(shù)據(jù)就可以了，因為索引是一種特殊的專門用來快速檢索的數(shù)據(jù)結構，當我們找到數(shù)據(jù)磁盤地址后，就可以拿到想要的數(shù)據(jù)。

索引類型

以InnoDB存儲引擎為例，索引類型有：

1. Normal（普通索引）

   也叫非唯一索引，是最普通的索引，沒有任何限制條件。

2. Unique（唯一索引）

   唯一索引要求鍵值不能重復。

   主鍵索引是一種特殊的唯一索引，它多了一個限制條件，要求鍵值不能為空。

3. Fulltext（全文索引）

   全文索引主要是針對比較大的數(shù)據(jù)，比如我們存放的是消息內(nèi)容，有幾kb的數(shù)據(jù)，如果要解決like查詢效率低的問題，可以創(chuàng)建全文索引。只有文本類型的字段才可以創(chuàng)建全文索引，比如char、varchar、text。

   全文索引的使用：

   select * from table where match(content) against ('xxxx' IN NATURAL LANGUAGE MODE);
   

4. 聚集索引（聚簇索引）

   聚集索引就是索引鍵值的邏輯順序跟表數(shù)據(jù)行的物理存儲順序是一致的。在InnoDB中，主鍵索引就是聚集索引，非主鍵索引是非聚集索引。

索引存儲模型

索引的存儲模型有二分查找、二叉查找樹（BST Binary Search Tree）、平衡二叉樹（AVL Tree）、多路平衡查找樹（B Tree）、加強版多路平衡查找樹（B+ Tree）。

存儲模型是一步一步演進過來的。

InnoDB邏輯存儲結構

image-20200402125920970

mysql的存儲結構分為5級，表空間、段、簇、頁、行。

• 表空間

表空間可以看做是InnoDB存儲引擎邏輯結構的最高層，所有的數(shù)據(jù)都存放在表空間中。表空間又分為：系統(tǒng)表空間、獨占表空間、通用表空間、臨時表空間、Undo表空間。

• 段（Segment）

表空間是由各個段組成，段又分為：數(shù)據(jù)段、索引段、回滾段。

段是一個邏輯概念，一個ibd文件（獨立的表空間文件）里面會有很多個段組成。

創(chuàng)建一個索引會創(chuàng)建兩個段，一個是索引段（leaf node segment），一個是數(shù)據(jù)段（non-leaf node segment）。索引段管理非葉子節(jié)點的數(shù)據(jù)，數(shù)據(jù)段管理葉子節(jié)點數(shù)據(jù)。

• 簇（Extent）

一個段又是由多個簇（也可以叫區(qū)）組成，每個簇的大小是1MB（64個連續(xù)的頁）。

每一個段至少有個一個簇，一個段所管理的空間大小是無限的，可以一直擴展下去，擴展的最小單位就是簇。

• 頁（Page）

簇是由連續(xù)的頁組成的空間，一個簇中有64個連續(xù)的頁。

一個表空間最多擁有2^32個頁，默認情況下一個頁的大小為16KB，也就是說一個表空間最多存儲64TB的數(shù)據(jù)。

• 行（Row）

InnoDB存儲引擎是面向行（Row-oriented）存儲的。

索引使用原則

1. 列的離散度

   列的離散度計算公式：count(distinct(column_name))/count(*)，列的全部不同值和所有數(shù)據(jù)行的比例。數(shù)據(jù)行數(shù)相同的情況下，分子越大，列的離散度就越高。

   簡單來說，如果列的重復值越多，離散度就越低，重復值越少，離散度就越高。

2. 聯(lián)合索引最左匹配

   聯(lián)合索引在B+Tree中是復合的數(shù)據(jù)結構，它是按照從左到右的順序來建立搜索樹的。

我們在建立聯(lián)合索引的時候，一定要把最常用的列放到最左邊。

例如：給表table中建立聯(lián)合索引a和b，

select * from table where a='xxx' and b='xxx' /*是可以用到聯(lián)合索引的。*/

select * from table where a='xxx' /*可以用到聯(lián)合索引*/

select * from table where b='xxx' /*無法使用聯(lián)合索引*/

給a和b建立聯(lián)合索引其實就是相當于（a）和（a，b）建立了兩個索引。

在給a、b、c建立聯(lián)合索引的時候其實就是建立了（a）索引，（a,b）索引，（a,b,c）索引，這時，如果條件where b=‘xxx‘和where b=‘xxx' and c='xxx'，還有where a='xxx' and c='xxx'都是用不到聯(lián)合索引的。

說明聯(lián)合索引的使用條件時不能不使用第一個字段，也不能中斷。

什么時候用不到索引

1. 在索引列上使用函數(shù)（replace、substr、concat、sum、count、avg）、表達式、計算（+、-、、/*）。

2. 字符串不加引號，出現(xiàn)隱式轉(zhuǎn)換。

3. like條件前面帶%。

4. 負向查詢

   not like 不能使用索引。

   !=、<>、not in在某些情況下可能用到索引。

   注意：一個sql語句是否使用到索引，是跟數(shù)據(jù)庫版本，數(shù)據(jù)量、數(shù)據(jù)選擇度都有關系的。

其實用不用到索引，最終都是由優(yōu)化器說了算。

優(yōu)化器是基于cost開銷來決定的，怎么樣開銷小就怎么來。它不基于規(guī)則，也不基于語義。

覆蓋索引

在輔助索引里面，不管是單列索引還是聯(lián)合索引，如果select后的數(shù)據(jù)列只要從索引中就能夠得到，不用在從數(shù)據(jù)區(qū)中讀取，這時候使用的索引就叫覆蓋索引，這樣也避免了回表。

回表

非主鍵索引，是先通過索引找到主鍵索引的鍵值，在通過主鍵值查出索引里面沒有的數(shù)據(jù)，它比基于主鍵索引查詢的時候多了一次查詢，這個過程就是回表。

索引下推

例如：給a和b建立索引。

1. 先根據(jù)a列從存儲引擎中把符合規(guī)則的數(shù)據(jù)拉取到mysql的server層。
2. 在server層按照b進行數(shù)據(jù)過濾。

這個過程就叫索引下推

B+ Tree

image-20200402130100082

特點

1. 它的關鍵字的數(shù)量是跟路數(shù)相等的。
2. B+Tree的根據(jù)節(jié)點和枝節(jié)點都不會存儲數(shù)據(jù)，只有葉子節(jié)點才存儲數(shù)據(jù)。搜索到關鍵字不會直接返回，會到最后一層葉子節(jié)點。
3. B+Tree的每個葉子節(jié)點增加了一個指向相鄰葉子節(jié)點的指針，它的最后一個數(shù)據(jù)會指向下一個葉子節(jié)點的第一個數(shù)據(jù)，這樣就形成了一個有序鏈表的結構。
4. 它是根據(jù)左閉右開的區(qū)間來檢索數(shù)據(jù)的。

[每天進步一點點]mysql筆記整理（一）

[每天進步一點點]mysql筆記整理（二）：事務與鎖

• 整理不易，轉(zhuǎn)載請注明出處，喜歡的小伙伴可以關注公眾號查看更多喜歡的文章。
• 微信:ffj2000