mysql 索引底層詳解

mysql三層架構

+--------------+ | 客戶端 | +--------------+ ↓ +--------------+ | 服務端 | +--------------+ ↓ +--------------+ | 存儲引擎 | +--------------+

1

2

3

4

5

6

7

8

9

10

11

mysql server層結(jié)構

當客戶端需要查詢一條sql時，在server端內(nèi)部走了以下4步

+--------------+ | 連接器 | +--------------+ ↓ +--------------+ | 分析器 | +--------------+ ↓ +--------------+ | 優(yōu)化器 | +--------------+ ↓ +--------------+ | 執(zhí)行器 | +--------------+

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

索引的作用

加快數(shù)據(jù)的訪問

將隨機io變成順序io

大大減少了服務器需要掃描的數(shù)據(jù)量

幫助服務器避免排序和臨時表

減少io次數(shù)，提高磁盤壽命

索引缺點

加了索引之后查詢會更快，但是當數(shù)據(jù)量大的時候，增刪改就會變慢；因為每次修改數(shù)據(jù)時除了數(shù)據(jù)本身，數(shù)據(jù)庫還需要維護索引的那顆B+樹；

一張表最多能建多少個列和索引

innoDB: 最多創(chuàng)建1017列, 最多64個二級索引,加上主鍵有65個， 單個索引最多包含16列, 索引最大長度767字節(jié)(其實行格式為REDUNDANT,COMPACT最高為767字節(jié),但行格式為DYNAMIC,COMPRESSED最高可達為3072字節(jié)), 行大小最大65536字節(jié)

mysiam: 最多4096列, 最多64個二級索引, 單個索引最多包含16列, 索引最大長度1000字節(jié), 行大小最大65536字節(jié)

一級索引和二級索引

一級索引

：索引和數(shù)據(jù)存儲在一起，都存儲在同一個B+tree中的葉子節(jié)點。一般主鍵索引都是一級索引。主鍵就是聚簇索引，一個表就一個主鍵，一個表也就一個聚簇索引，所以綜上所述，

主鍵 = 一級索引 = 聚簇索引

二級索引

：二級索引樹的葉子節(jié)點存儲的是主鍵而不是數(shù)據(jù)。也就是說，在找到索引后，得到對應的主鍵，再回到一級索引中找主鍵對應的數(shù)據(jù)記錄。在innodb中，所有的二級索引B+樹都指向一級索引的key值，所以查詢二級索引時需要回表才能查到一整行數(shù)據(jù)；

索引是存儲在內(nèi)存還是磁盤的

索引都是存儲在磁盤里面的，因為需要持久化存儲，內(nèi)存里也有，但都是每次查詢時從磁盤加載到內(nèi)存里的

有或者無索引的情況下是怎么查找數(shù)據(jù)的？

無索引：沒有索引的情況下，都會全表掃描，就是一條條地找，這無疑效率不高，又費io；

有索引：有索引的情況下，會為這個索引生成一顆B+樹，有了這顆樹的幫助，查詢數(shù)據(jù)的效率會提升幾千倍；

查詢比較慢，一般卡在哪？

卡在io上，就是input和output，

解決方案是提高io效率，

減少io的次數(shù)，

減少io的量：盡可能地查詢減少查詢的數(shù)據(jù)，盡量避免用select * from xxx

去磁盤讀取數(shù)據(jù)的時候，是用多少讀取多少嗎？

肯定不是啦，mysql和磁盤交互的時候是以頁為單位進行傳輸?shù)模J情況下，每頁大小為16K，，就像我們在電腦上新建一個txt文件，里面什么內(nèi)容都沒有，但它還是占用了4KB，mysql也一樣，有自己的最小頁大小，可通過 innodb_page_size 參數(shù)觀看數(shù)據(jù)頁大小

mysql> show variables like 'innodb_page_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_page_size | 16384 | +------------------+-------+

1

2

3

4

5

6

很重要的概念：局部性原理

數(shù)據(jù)和程序都有狙擊成群的傾向，同時之前被訪問過的數(shù)據(jù)很可能再次被查詢，空間局部性、時間局部性

磁盤預讀

內(nèi)存跟磁盤交互時，一般情況下有一個最小的邏輯單元，稱之為頁（datapage），頁的大小由系統(tǒng)決定，一般是4k或8k，并且一定是整數(shù)倍的，4、8、16、32、64、128… 數(shù)據(jù)交互時，可以去頁的整數(shù)倍來進行讀取，innodb存儲引擎，每次讀取數(shù)據(jù)都是16k

索引為什么能加快查詢

要解決這個問題，我們就得先知道索引是怎么存儲的

索引是怎么存儲的？

一個索引對應一個B+樹，如果一張表建了10個索引，那就會有10個B+樹，

OLAP聯(lián)機分析處理—數(shù)據(jù)倉庫–hive

對海量歷史數(shù)據(jù)進行分析，產(chǎn)生決策性的影響

OLTP聯(lián)機事務處理–關系型數(shù)據(jù)庫

要求在很短的時效內(nèi)返回對用的數(shù)據(jù)

為什么用B+樹來存儲索引？

hash索引不適合用來做數(shù)據(jù)庫的結(jié)構，

如果是單個等值值查詢（通過key查找value），那么就會非常快，

不支持范圍查詢，進行范圍查詢時，必須要挨個遍歷

對內(nèi)存的要求比較高，

哈希沖突會造成數(shù)據(jù)散列不均勻，會產(chǎn)生大量的線性查詢，很浪費時間

在mysql中有沒有hash索引

答：有

memory存儲引擎使用的是hash索引

innodb支持自適應hash，就是由mysql來決定使用hash還是樹來存儲，人工無法干預

存儲引擎的分類

innodb ：持久化 + 內(nèi)存

memory ：只存儲在內(nèi)存，不支持持久化，斷電就沒了，結(jié)構是hash表

myisam ：持久化

可以在建表是自己指定存儲引擎，就像這樣

create table (id bigint(20) primary key ,name varchar(10)) engine='innodb';

1

MySQL5.5版本之前，默認內(nèi)置存儲引擎是Myisam，

MySQL5.5版本之后，MySQL的默認內(nèi)置存儲引擎已經(jīng)是InnoDB，

樹的分類

二叉樹

BST樹（binary search tree）必須保證順序

AVL樹 平衡二叉樹，有序

紅黑樹

B樹

B+樹

在很早很早很早很早以前，索引是用用二叉樹實現(xiàn)的，大概是2點幾版本的

二叉樹本身是無序的，所以發(fā)展出了有序的BST樹，BST在插入數(shù)據(jù)的時候必須保證有序，左子樹必須小于根節(jié)點，右子樹必須大于根節(jié)點；

bst樹插入時如果是連續(xù)遞增或遞減順序的話，就會退化成鏈表，所以衍生出了會旋轉(zhuǎn)的平衡二叉樹avl，

avl樹插入慢，查詢快，因為插入的時候為了保證平衡，需要進行旋轉(zhuǎn)操作，平衡二叉樹有一個條件，為了保證平衡，最短子樹和最長子樹的長度差不能超過1，所以會經(jīng)常要旋轉(zhuǎn)，旋轉(zhuǎn)也是需要性能開銷的。所以平衡二叉樹只能用于插入少、查詢多的數(shù)據(jù)，當我們的查詢和插入一樣多 情況下，使用平衡二叉樹就不合適了，所以這時候又衍生出了一種新的數(shù)據(jù)結(jié)構：紅黑樹

紅黑樹最長子樹只要不超過最短子樹的2倍即可，但是隨著數(shù)據(jù)的插入，發(fā)現(xiàn)樹的深度會變深，樹的深度越深，意味著io次數(shù)越多，就會影響數(shù)據(jù)讀取的效率；所以為了解決這個問題，就需要把有序的二叉樹，變成有序的多叉樹，這就是B樹

B樹中每層都存儲數(shù)據(jù)，但是每個磁盤塊能存儲的內(nèi)容是有限的，除了索引之外，還要存儲數(shù)據(jù)，而數(shù)據(jù)占用的空間更多，這就使得能存儲的索引變少了，如果想要插入更多的數(shù)據(jù)，就得在加一層，變成四層，但是這樣會增加io量，所以為了解決這個問題，衍生出了B+樹；

B+樹只在葉子節(jié)點儲存數(shù)據(jù)，非葉子層只存儲索引，并且B+樹不但可以從上往下查找，還可以從下往上查找數(shù)據(jù)；

聚簇索引和 非聚簇索引 的區(qū)別

innodb只能有一個聚簇索引，但是有很多的非聚簇索引

聚簇索引： 數(shù)據(jù)和索引是放在一起的，就是聚簇索引，就像這樣

+----------+ | 索引值 | +----------+ | 數(shù)據(jù) | +----------+

1

2

3

4

5

非聚簇索引：數(shù)據(jù)和索引是分開存放的，在B+樹中索引值對應的是文件地址，就是非聚簇索引，可以肯定的是，所有的非聚簇索引都指向了聚簇索引，就像這樣

+------------+ | 索引值 | +------------+ |數(shù)據(jù)文件地址| +------------+

1

2

3

4

5

innodb中，如果id是主鍵，后面我把name字段添加為索引，這棵樹是怎么樣存儲的？

一開始我的主鍵是id字段，那么在B+樹中葉子節(jié)點的結(jié)構是這樣的

+----------+ |主鍵索引值| +----------+ | 數(shù)據(jù) | +----------+

1

2

3

4

5

這時候我又把name字段設置為索引了，這時候mysql為這個name索引也創(chuàng)建一顆B+樹，這顆樹的葉子節(jié)點存的就不是數(shù)據(jù)了，如果存數(shù)據(jù)的話會造成冗余，所以這個name索引樹存的是主鍵id，就像這樣

+----------+ | ye | +----------+ / \ +----------+ +----------+ | xin | | dong | +----------+ +----------+ | 主鍵id | | 主鍵id | +----------+ +----------+

1

2

3

4

5

6

7

8

9

innodb插入數(shù)據(jù)時必須要包含一個索引的key值

向innodb插入數(shù)據(jù)的時候，必須要包含一個索引的key值，這個索引的key值，可以是主鍵，如果沒有主鍵，就是唯一鍵，如果沒有唯一鍵，那么就是一個自生成的6字節(jié)的rowid；

myisam用的都是非聚簇索引；

innodb只有一個聚簇索引，有多個非聚簇索引；

什么是存儲引擎

索引的創(chuàng)建跟存儲引擎是掛鉤的，存儲引擎表示不同的數(shù)據(jù)在磁盤存儲的文件格式也是不同的。

mysql常用的存儲引擎有三個

memory ：內(nèi)存級別的存儲引擎，不支持持久化，斷電丟失數(shù)據(jù)，hash索引

myisam ： mysql 5.5之前默認的存儲引擎，每次修改數(shù)據(jù)都會鎖表，不支持事務

innodb ： mysql 5.5之后默認的存儲引擎，支持事務、行鎖；

mysql會自動創(chuàng)建索引嘛

innodb只能有一個聚簇索引，但是有很多的非聚簇索引，向innodb插入數(shù)據(jù)的時候，必須要包含一個索引的key值，這個索引的key值，可以使主見，如果沒有主鍵，就是唯一鍵，如果沒有唯一鍵，那么就是一個自生成的6字節(jié)的rowid；

為什么只能有一個聚簇索引

因為主鍵只有一個，聚簇索引對應的就是主鍵字段，只有主鍵的索引B+樹才會存儲數(shù)據(jù)，其他的二級索引存儲的都是主鍵的值；

如果每個二級索引樹都存儲數(shù)據(jù)的話，就會造成數(shù)據(jù)的冗余；

myisam和innodb區(qū)別

myisam支持表鎖，innodb支持表鎖和行鎖

myisam不支持外鍵，innodb支持外鍵

myisam不支持事務，innodb支持事務

在計算機內(nèi)存足夠的情況下，innodb效率比myisam高，因為innodb是優(yōu)先讀緩存， myisam是直接從磁盤讀取數(shù)據(jù)

數(shù)據(jù)存儲在哪個目錄

數(shù)據(jù)存儲在： 你的mysql目錄/data/db1/ 目錄下，

其中，

后綴為.opt的文件是一個配置文件，指定該數(shù)據(jù)庫的字符集編碼

后綴為.frm的文件就是表結(jié)構

后綴為.idb表示當前表用的存儲引擎是innodb

后綴為.myd是數(shù)據(jù)文件。使用的存儲引擎是myisam

后綴為.myi是索引文件，使用的存儲引擎是myisam

索引監(jiān)控

查詢語句為：show status like ‘Handler_read%’;

結(jié)果說明：

Handler_read_first 代表讀取索引頭的次數(shù)，如果這個值很高，說明全索引掃描很多。

Handler_read_key：代表一個索引被使用的次數(shù)，如果我們新增加一個索引，然后通過這個索引來查詢數(shù)據(jù)，，可以查看Handler_read_key是否有增加，如果有增加，說明sql用到索引。這個數(shù)量越大越好，大表示索引查詢使用的比較多；

Handler_read_next：代表讀取索引的下列，按(主)鍵順序依次讀取之后的N行

Handler_read_last : 從(主)鍵的最后位置開始讀取

Handler_read_prev： 代表讀取索引的上列，一般發(fā)生在ORDER BY … DESC。

Handler_read_rnd： 代表在固定位置讀取行，如果這個值很高，說明對大量結(jié)果集進行了排序、進行了全表掃描、關聯(lián)查詢沒有用到合適的KEY。

Handler_read_rnd_next： 代表進行了很多表掃描，查詢性能低下。

關于mysql 的mysql 回表、索引覆蓋、最左匹配、索引下推，請看我的另一篇文章 ：添加鏈接描述

MySQL 數(shù)據(jù)庫

版權聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權內(nèi)容。

版權聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權內(nèi)容。