??MySQL 一直是 RDBMS 型數(shù)據(jù)庫(kù)中用戶最多炼邀，使用最廣的數(shù)據(jù)庫(kù)。主要是因?yàn)樗_(kāi)源剪侮、免費(fèi)拭宁、文檔豐富等。在 MySQL 查詢優(yōu)化中瓣俯，提得最多杰标、用得最廣的就是 Index, 那我們就來(lái)慢慢的揭開(kāi)它神秘的面紗吧。

Structure

InnoDB-physical-structure-overview.png

??MySQL 是基于磁盤(pán)的數(shù)據(jù)庫(kù)彩匕，所以 MySQL 的所有信息皆是以文件的形式存儲(chǔ)于磁盤(pán)腔剂，我想這是借鑒了 Linux 的設(shè)計(jì)哲學(xué) "Everything is a file" 吧。進(jìn)入 MySQL 使用如下命令查看數(shù)據(jù)目錄驼仪。

mysql> show variables where Variable_Name LIKE "%dir";
+---------------------------+----------------------------+
| Variable_name             | Value                      |
+---------------------------+----------------------------+
| basedir                   | /usr/                      |
| character_sets_dir        | /usr/share/mysql/charsets/ |
| datadir                   | /var/lib/mysql/            |
| innodb_data_home_dir      |                            |
| innodb_log_group_home_dir | ./                         |
| innodb_tmpdir             |                            |
| lc_messages_dir           | /usr/share/mysql/          |
| plugin_dir                | /usr/lib/mysql/plugin/     |
| slave_load_tmpdir         | /tmp                       |
| tmpdir                    | /tmp                       |
+---------------------------+----------------------------+
10 rows in set (0.00 sec)

Spaces

??進(jìn)入 datadir/database 目錄, 文件分為 .frm 與 .ibd 兩類(lèi)文件掸犬，分別是表的結(jié)構(gòu)文件與數(shù)據(jù)文件。以 var/lib/mysql 為數(shù)據(jù)目錄谅畅，repldb 為 db_name, repl_tab1 作為表名登渣，展示如下:

root@mysql-master:/var/lib/mysql/repldb# ls -l
-rw-r----- 1 mysql mysql    61 Apr 28  2019 db.opt
-rw-r----- 1 mysql mysql  8676 Apr 28  2019 repl_tb1.frm
-rw-r----- 1 mysql mysql 98304 Apr 28  2019 repl_tb1.ibd

??以上是我們可以簡(jiǎn)單看到的物理文件，那在 MySQL 的邏輯當(dāng)中毡泻，它又是如何的呢? InnoDB 的數(shù)據(jù)存儲(chǔ)使用 "spaces" 進(jìn)行存儲(chǔ)胜茧，通常我們叫它 "tablespaces", 有時(shí)候 InnoDB 內(nèi)部也叫它 "filespaces"。一個(gè) space 可由 1 個(gè)或 n 個(gè)實(shí)際的文件組成(如: ibdata1,ibdata2 等, 多個(gè)文件時(shí)仇味，它們帶有特定的順序和序號(hào))呻顽，但不管它由多少個(gè)實(shí)際的文件進(jìn)行存儲(chǔ)，它只組成一個(gè)邏輯文件, 每個(gè) space 文件在 InnoDB 中被賦于一個(gè) 32-bit Int 的 space_id 作為標(biāo)識(shí)符丹墨。.idb 可以包含多個(gè)表文件廊遍，但是在 MySQL 的實(shí)現(xiàn)中，一個(gè) .ibd 文件只包含一個(gè)表贩挣，所以把一個(gè) .ibd 文件等價(jià)于一個(gè)表文件喉前。

Pages

??前面已經(jīng)知道 MySQL 把每個(gè)表的數(shù)據(jù)存儲(chǔ)在一個(gè) data file 也就是 .ibd 文件中没酣，又因?yàn)?MySQL 是基于磁盤(pán)的數(shù)據(jù)庫(kù)系統(tǒng)，那是不是查詢的時(shí)候把物理文件一次性加載到內(nèi)存當(dāng)中卵迂，然后再進(jìn)行過(guò)濾查詢呢裕便？或者進(jìn)一步的也文件流的形式載入內(nèi)存，然后再過(guò)濾呢? 是的见咒，MySQL 的查詢確實(shí)需要把物理文件加載到內(nèi)存才能進(jìn)行處理偿衰，但是 MySQL 的操作都不是前面提到的兩種方式。MySQL 采用 "分而治之" 的辦法改览，把 .ibd 邏輯分割成小塊下翎，然后每次加載一個(gè)或幾個(gè)塊到內(nèi)存再進(jìn)行處理，這就是 MySQL 的查詢處理方式宝当，這些小塊就是 MySQL 的數(shù)據(jù)處理最小單元视事。這些塊在 MySQL 中稱為一個(gè)Page, 有時(shí)也叫一個(gè)block, 每個(gè)Page同Space一樣有一個(gè)32-bitInt 的標(biāo)識(shí)符page_no, 每個(gè)page默認(rèn)固定大小為16KB, 可通過(guò)參數(shù) innodb_page_size 進(jìn)行調(diào)整，如: 4K庆揩、8K郑口、32K、64K 等盾鳞。Page 由以下幾部分組成 詳細(xì):

• Fil Header
• Page Header
• Infimun + Supermum Records
• User Records
• Free Space
• Page Directory
• Fil Trailer

??Page 與 Page 之間是否是互不相關(guān)呢? 各 Page 之間的關(guān)系在 Page 的 Fil Header 中進(jìn)行定義， Page 之間以 Doubly-Linked的方式進(jìn)行關(guān)聯(lián)瞻离，Page 的結(jié)構(gòu)詳細(xì)如下:

FIL_PAGE_SPACE //就是所謂的 `page_no`
FIL_PAGE_OFFSET
FIL_PAGE_PREV //指向前一個(gè) Page
FIL_PAGE_NEXT //指向后一個(gè) Page
FIL_PAGE_LSN  //LSN: log serial number
FIL_PAGE_TYPE
FIL_PAGE_FILE_FLUSH_LSN
FIL_PAGE_ARCH_LOG_NO

Records

??經(jīng)過(guò)前面的了解腾仅，我們已經(jīng)知道真實(shí)的數(shù)據(jù)是分布在 space 空間的每個(gè) page 內(nèi)，那如何才能得得到每行數(shù)據(jù)記錄呢套利？Page 的組成部分中有一項(xiàng) User Records, 那就是真實(shí)數(shù)據(jù)存儲(chǔ)位置推励。當(dāng)然了，Record 有自己的 結(jié)構(gòu)肉迫，主要分為 Compact 和 Redundant 兩種格式验辞，但不管何種格式，它里一個(gè)指向下一條記錄的數(shù)據(jù) next_record, 通過(guò)這樣的方式喊衫，各 Record 就以 Singly-Linked 的形式進(jìn)行連接跌造。

Index

??通過(guò)前面的介紹，我們已經(jīng)知道了 MySQL InnoDB 引擎對(duì)于數(shù)據(jù)的處理方式和存儲(chǔ)結(jié)構(gòu)族购，當(dāng)我們查詢數(shù)據(jù)時(shí)壳贪，先定位 Table Space、再定位 Page寝杖、最后定位 Record违施，這是一個(gè)完整的數(shù)據(jù)查詢過(guò)程。那 Index 能夠幫助我們做什么呢瑟幕？ Index 其實(shí)就是幫助我們快速定位記錄磕蒲，縮短 space --> page --> record 這個(gè)過(guò)程留潦。

??MySQL 是基于磁盤(pán)的數(shù)據(jù)庫(kù)系統(tǒng)，有說(shuō)過(guò)它的設(shè)計(jì)哲學(xué)和 Linux 的 "Everything is a file" 的設(shè)計(jì)哲學(xué)相符辣往，那么 MySQL 的 Index 的設(shè)計(jì)哲學(xué)又是什么呢?

"Everything is an index in InnoDB".

??Index 分為 Clustered Index 和 Secondary Indexes 兩類(lèi), MySQL 中的索引的存儲(chǔ)根據(jù)不同的數(shù)據(jù)表和引擎又采用不同的形式兔院，一般情況通常采用 B-Tree 索引, 空間數(shù)據(jù)采用 R-Tree索引, 內(nèi)存數(shù)據(jù)采用 Hash 索引，文檔數(shù)據(jù)采用 Full Text 索引等排吴。

Clustered Index

??MySQL 中以 InnoDB 作為引擎時(shí)秆乳，每張表有且僅有一個(gè) Clustered Index，而且每張表必須有一個(gè) Clustered Index钻哩，下面詳細(xì)了解一下 Clustered Index 的生成規(guī)則屹堰。

• 有主鍵時(shí)，InnoDB 使用它作為 Clustered Index;
• 無(wú)主鍵
  • 有 unique 列且 not null 列, MySQL 用第一個(gè)符合該條件的列作為 Clustered Index;
  • 且無(wú)合適的 unique 列, InnoDB 會(huì)內(nèi)部生成一個(gè)名稱為 GEN_CLUST_INDEX 的隱藏索引街氢，該索引以隱藏的 6-byte 的 row_id 列作為索引列扯键，該列以數(shù)據(jù)行插入的順序進(jìn)行排序;

Q: 那為什么一定要有一個(gè)Clustered Index呢?

A: 因?yàn)?MySQL 需要快速定位 Record, 所以必須要有一個(gè)索引，就像書(shū)的目錄一樣珊肃，通過(guò)索引就可以快速定位到記錄對(duì)應(yīng)的 Page荣刑。

Q: 那索引里面存儲(chǔ)的是什么呢?

A: Clustered Index 里面包含了 索引列 和 page 的映射關(guān)系，而且直接指向了 page 中的行記錄, 而這也是Clustered Index 之所以快的原因伦乔。

Secondary Indexes

??MySQL 中有僅僅有一個(gè) Clustered Index厉亏，但是對(duì)于 Secondary Indexes 則可以存在多個(gè)(這也是兩個(gè) Index 的寫(xiě)法分單數(shù)復(fù)數(shù)的原因)。 在 InnoDB 中所有的 Secondary Indexes 都是基于 Clustered Index 的烈和，每一個(gè) Secondary Index 都包含 Primary Keys, 也就是 Clustered Index 中的索引列爱只。

Q: Secondary Index 也是直接指向 page 中的記錄嗎？

A: Secondary Index 并不直接指向 page, 它先指向 Clustered Index 然后再指向 page 內(nèi)數(shù)據(jù) (這種情況也稱為 回表)招刹。

Index-selective

• Leftmost Prefix

  當(dāng)有多列索引(multiple-column恬试，也叫聯(lián)合索引) 時(shí)，索引形式如(c1, c2, c3), 查詢條件如 (c1), (c1, c2) 以及 (c1, c2, c3) 均可利用到該索引疯暑。

• Index-Extensions

  InnoDB 自動(dòng)把 Primary Key 附加到 Secondary Index 后面训柴，如 PK(c1, c2), 同時(shí)也定義了 Secondary Index 其包含 (c3), InnoDB 內(nèi)部?jī)?yōu)化器 (optimizer) 會(huì)把其擴(kuò)展為 (c3, c1, c2) 的形式, 從而提高查詢效率。該功能需要通過(guò)指令 SET optimizer_switch = 'use_index_extensions=off' 進(jìn)行啟動(dòng)和停用妇拯，InnoDB 默認(rèn)屬于開(kāi)啟狀態(tài)幻馁。

• Index-Merge

  所謂索引融合，就是對(duì)多個(gè)索引進(jìn)行條件掃描乖阵，然后再把它們的結(jié)果合并宣赔。

• Index-Cover

  所謂索引覆蓋，就是查詢的列通過(guò)索引就能夠取得瞪浸，這種情況下不需要再定位到page去取數(shù)據(jù)儒将。避免了磁盤(pán)讀寫(xiě)、回表查詢等操作对蒲，所以這種情況比一般的索引查詢效率更高钩蚊。

pros and cons

??當(dāng)了解了索引的功效之后贡翘，我們是不是為表建立越多的索引越好呢? 并不如此，索引也有它的適用范圍砰逻，和代價(jià)鸣驱，所以并不是索引越多越好。

Pros

• 減少數(shù)據(jù)量的掃描蝠咆；
• 當(dāng)索引列有排序操作時(shí)踊东，可以避免服務(wù)端的排序與臨時(shí)表的產(chǎn)生；
• 可以把隨機(jī)的磁盤(pán) I/O 變?yōu)轫樞虻?I/O;

Cons

??當(dāng)數(shù)據(jù)量少的時(shí)候建立索引其實(shí)并沒(méi)有什么用刚操，如果數(shù)據(jù)量還不足一個(gè) page, 而 page 又是數(shù)據(jù)處理的最小單位闸翅，所以這個(gè)時(shí)候有沒(méi)有索引都一樣；那當(dāng)數(shù)據(jù)量很大的時(shí)候呢菊霜，這個(gè)時(shí)候如果數(shù)據(jù)量非常大坚冀，那么索引文件相應(yīng)的也變得很大，如果 MySQL 需要多次才能掃描完索引的內(nèi)容鉴逞，那么這時(shí)候索引也沒(méi)有用了记某，因?yàn)樗饕淖x取也得造成磁盤(pán)的 I/O, 且也不能短時(shí)間定位到對(duì)應(yīng)的數(shù)據(jù)。 而且索引的建立也是有一定的代價(jià)的构捡，如每次插入數(shù)據(jù)時(shí)液南，相比于無(wú)索引情況需要另外的磁盤(pán)寫(xiě)操作，對(duì)應(yīng)的也占用更多的磁盤(pán)空間勾徽，讀取的時(shí)候相應(yīng)的也占用更多的內(nèi)存容量贺拣，數(shù)據(jù)更新隨之而來(lái)的也是索引更新成本。

Summary

??索引并不適用于所有的情況捂蕴，只有當(dāng)索引的成本小于它的代價(jià)時(shí)，才是有效的索引闪幽。所以建立索引時(shí)需要綜合考慮實(shí)際的業(yè)務(wù)場(chǎng)景啥辨，再結(jié)合 MySQL 查詢優(yōu)化器以及索引選擇原理才能夠建立適合自己業(yè)務(wù)的索引。

Appendix

• B+tree 為什么快 ?
• 查詢到底什么了什么索引 ?
• Page 詳細(xì)結(jié)構(gòu) ?
• Record 詳細(xì)結(jié)構(gòu) ?

微信公眾號(hào)