受簡書字?jǐn)?shù)限制跺株，不在此處放了。
詳細(xì)內(nèi)容見：Mysql全面總結(jié)-阿里云開發(fā)者社區(qū) (aliyun.com)

來源：Javakeeper

作者：派大新

本文內(nèi)容大多是轉(zhuǎn)載，如有侵權(quán)，請聯(lián)系刪除杀糯。

一、Mysql 思維導(dǎo)圖

Mysql思維導(dǎo)圖.png

二桂躏、Mysql架構(gòu)

插件式的存儲引擎架構(gòu)將查詢處理和其它的系統(tǒng)任務(wù)以及數(shù)據(jù)的存儲提取相分離抢埋。這種架構(gòu)可以根據(jù)業(yè)務(wù)的需求和實際需要選擇合適的存儲引擎。（注：原文作者提出該種架構(gòu)與其他數(shù)據(jù)庫不同核心是插件式的存儲引擎州疾，一辜限、并未列舉出其他數(shù)據(jù)庫，二严蓖、受本人眼界所限薄嫡，目前接觸到的業(yè)界常見數(shù)據(jù)庫都是采用插件式的存儲引擎，如：TiDB颗胡、Clickhouse等毫深，三、插件式的存儲引擎本人是支持者）

Mysql架構(gòu)圖.png

• 連接層：最上層是一些客戶端和連接服務(wù)毒姨。主要完成一些類似于連接處理费什、授權(quán)認(rèn)證、及相關(guān)的安全方案。在該層上引入了線程池的概念鸳址，為通過認(rèn)證安全接入的客戶端提供線程瘩蚪。同樣在該層上可以實現(xiàn)基于SSL的安全鏈接。服務(wù)器也會為安全接入的每個客戶端驗證它所具有的操作權(quán)限稿黍。（注：一疹瘦、Mysql服務(wù)器本身不提供不同語言的連接驅(qū)動，Mysql官方通過實現(xiàn)Mysql提供的各種接口封裝成各語言的可用驅(qū)動巡球，提供給用戶提高開發(fā)效率言沐。二、關(guān)于mysql-connector-java中URL的解析參數(shù)酣栈，可見 mysql jdbc url具體參數(shù)全解_mysql jdbc url參數(shù)-CSDN博客 险胰，常見問題可能出在時區(qū)，中文編碼等配置上）
• 服務(wù)層：第二層服務(wù)層矿筝，主要完成大部分的核心服務(wù)功能起便， 包括查詢解析、分析窖维、優(yōu)化榆综、緩存、以及所有的內(nèi)置函數(shù)铸史，所有跨存儲引擎的功能也都在這一層實現(xiàn)鼻疮，包括觸發(fā)器、存儲過程琳轿、視圖等判沟。
• 引擎層：第三層存儲引擎層，存儲引擎真正的負(fù)責(zé)了MySQL中數(shù)據(jù)的存儲和提取崭篡，服務(wù)器通過API與存儲引擎進(jìn)行通信水评。不同的存儲引擎具有的功能不同，這樣我們可以根據(jù)自己的實際需要進(jìn)行選取媚送。
• 存儲層：第四層為數(shù)據(jù)存儲層，主要是將數(shù)據(jù)存儲在運行于該設(shè)備的文件系統(tǒng)之上寇甸，并完成與存儲引擎的交互塘偎。

著重考察問題：
MySQL 的查詢流程具體是？or 一條SQL語句在MySQL中如何執(zhí)行的拿霉？
客戶端請求 ---> 連接器（驗證用戶身份吟秩，給予權(quán)限） ---> 查詢緩存（存在緩存則直接返回，不存在則執(zhí)行后續(xù)操作） ---> 分析器（對SQL進(jìn)行詞法分析和語法分析操作） ---> 優(yōu)化器（主要對執(zhí)行的sql優(yōu)化選擇最優(yōu)的執(zhí)行方案方法） ---> 執(zhí)行器（執(zhí)行時會先看用戶是否有執(zhí)行權(quán)限绽淘，有才去使用這個引擎提供的接口） ---> 去引擎層獲取數(shù)據(jù)返回（如果開啟查詢緩存則會緩存查詢結(jié)果）

一次數(shù)據(jù)庫查詢過程.jpeg

三涵防、存儲引擎

存儲引擎是MySQL的組件，用于處理不同表類型的SQL操作沪铭。不同的存儲引擎提供不同的存儲機制壮池、索引技巧偏瓤、鎖定水平等功能，使用不同的存儲引擎椰憋，還可以獲得特定的功能厅克。
使用哪一種引擎可以靈活選擇，一個數(shù)據(jù)庫中多個表可以使用不同引擎以滿足各種性能和實際需求橙依，使用合適的存儲引擎证舟，將會提高整個數(shù)據(jù)庫的性能 。
MySQL服務(wù)器使用可插拔的存儲引擎體系結(jié)構(gòu)窗骑，可以從運行中的 MySQL 服務(wù)器加載或卸載存儲引擎 女责。

查看存儲引擎

-- 查看支持的存儲引擎
SHOW ENGINES

-- 查看默認(rèn)存儲引擎
SHOW VARIABLES LIKE 'storage_engine'

--查看具體某一個表所使用的存儲引擎，這個默認(rèn)存儲引擎被修改了创译！
show create table tablename

--準(zhǔn)確查看某個數(shù)據(jù)庫中的某一表所使用的存儲引擎
show table status like 'tablename'
show table status from database where name="tablename"

設(shè)置存儲引擎

-- 建表時指定存儲引擎抵知。默認(rèn)的就是INNODB，不需要設(shè)置
CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;

-- 修改存儲引擎
ALTER TABLE t ENGINE = InnoDB;

-- 修改默認(rèn)存儲引擎昔榴，也可以在配置文件my.cnf中修改默認(rèn)引擎
SET default_storage_engine=NDBCLUSTER;

默認(rèn)情況下辛藻，每當(dāng) CREATE TABLE 或 ALTER TABLE 不能使用默認(rèn)存儲引擎時，都會生成一個警告互订。為了防止在所需的引擎不可用時出現(xiàn)令人困惑的意外行為吱肌，可以啟用 NO_ENGINE_SUBSTITUTION SQL 模式。如果所需的引擎不可用仰禽，則此設(shè)置將產(chǎn)生錯誤而不是警告氮墨，并且不會創(chuàng)建或更改表。

存儲引擎對比

常見的存儲引擎就 InnoDB吐葵、MyISAM规揪、Memory、NDB（注：Mysql分布式存儲引擎温峭，MySQL Cluste（入門篇）—分布式數(shù)據(jù)庫集群搭建_mysql分布式數(shù)據(jù)庫-CSDN博客）猛铅。
InnoDB 現(xiàn)在是 MySQL 默認(rèn)的存儲引擎，支持事務(wù)凤藏、行級鎖定和外鍵

文件存儲結(jié)構(gòu)對比

在 MySQL中建立任何一張數(shù)據(jù)表奸忽，在其數(shù)據(jù)目錄對應(yīng)的數(shù)據(jù)庫目錄下都有對應(yīng)表的 .frm 文件，.frm 文件是用來保存每個數(shù)據(jù)表的元數(shù)據(jù)(meta)信息揖庄，包括表結(jié)構(gòu)的定義等栗菜，與數(shù)據(jù)庫存儲引擎無關(guān)，也就是任何存儲引擎的數(shù)據(jù)表都必須有.frm文件蹄梢，命名方式為 數(shù)據(jù)表名.frm疙筹，如user.frm。
查看MySQL 數(shù)據(jù)保存在哪里：show variables like 'data%'
MyISAM 物理文件結(jié)構(gòu)為：

• .frm文件：與表相關(guān)的元數(shù)據(jù)信息都存放在frm文件，包括表結(jié)構(gòu)的定義信息等
• .MYD (MYData) 文件：MyISAM 存儲引擎專用而咆，用于存儲MyISAM 表的數(shù)據(jù)
• .MYI (MYIndex)文件：MyISAM 存儲引擎專用霍比，用于存儲MyISAM 表的索引相關(guān)信息
  InnoDB 物理文件結(jié)構(gòu)為：
• .frm 文件：與表相關(guān)的元數(shù)據(jù)信息都存放在frm文件，包括表結(jié)構(gòu)的定義信息等
• .ibd 文件或 .ibdata 文件：這兩種文件都是存放 InnoDB 數(shù)據(jù)的文件翘盖，之所以有兩種文件形式存放 InnoDB 的數(shù)據(jù)桂塞，是因為 InnoDB 的數(shù)據(jù)存儲方式能夠通過配置來決定是使用共享表空間存放存儲數(shù)據(jù)，還是用獨享表空間存放存儲數(shù)據(jù)馍驯。 獨享表空間存儲方式使用.ibd文件阁危，并且每個表一個.ibd文件 共享表空間存儲方式使用.ibdata文件，所有表共同使用一個.ibdata文件（或多個汰瘫，可自己配置）
  ps：正經(jīng)公司狂打，這些都有專業(yè)運維去做，數(shù)據(jù)備份混弥、恢復(fù)啥的趴乡，讓我一個 Javaer 搞這的話，加錢不蝗拿？

面試這么回答

1. InnoDB 支持事務(wù)晾捏，MyISAM 不支持事務(wù)。這是 MySQL 將默認(rèn)存儲引擎從 MyISAM 變成 InnoDB 的重要原因之一哀托；
2. InnoDB 支持外鍵惦辛，而 MyISAM 不支持。對一個包含外鍵的 InnoDB 表轉(zhuǎn)為 MYISAM 會失敳质帧胖齐；
3. InnoDB 是聚簇索引，MyISAM 是非聚簇索引嗽冒。聚簇索引的文件存放在主鍵索引的葉子節(jié)點上呀伙，因此 InnoDB 必須要有主鍵，通過主鍵索引效率很高添坊。但是輔助索引需要兩次查詢剿另，先查詢到主鍵，然后再通過主鍵查詢到數(shù)據(jù)贬蛙。因此雨女，主鍵不應(yīng)該過大，因為主鍵太大速客，其他索引也都會很大。而 MyISAM 是非聚集索引五鲫，數(shù)據(jù)文件是分離的溺职，索引保存的是數(shù)據(jù)文件的指針。主鍵索引和輔助索引是獨立的。
4. InnoDB 不保存表的具體行數(shù)浪耘，執(zhí)行select count(*) from table 時需要全表掃描乱灵。而 MyISAM 用一個變量保存了整個表的行數(shù)，執(zhí)行上述語句時只需要讀出該變量即可七冲，速度很快痛倚；
5. InnoDB 最小的鎖粒度是行鎖，MyISAM 最小的鎖粒度是表鎖澜躺。一個更新語句會鎖住整張表蝉稳，導(dǎo)致其他查詢和更新都會被阻塞，因此并發(fā)訪問受限掘鄙。這也是 MySQL 將默認(rèn)存儲引擎從 MyISAM 變成 InnoDB 的重要原因之一耘戚；

面試問題：

一張表嚣鄙，里面有ID自增主鍵吻贿，當(dāng)insert了17條記錄之后，刪除了第15,16,17條記錄拗慨，再把Mysql重啟廓八，再insert一條記錄，這條記錄的ID是18還是15 赵抢？（MySQL面試：mysql中剧蹂，一張表里面有 ID 自增主鍵，當(dāng) insert 了 17 條記錄之后烦却，刪除了第 15,16,17 條記錄宠叼，再把 Mysql 重啟，再 insert 一條記錄其爵，這條記錄的-CSDN博客）

• MySQL8.0以下版本：
  • 表類型為InnoDB引擎冒冬，這條記錄的ID是15。因為InnoDB只把自增主鍵的最大ID記錄到內(nèi)存中摩渺，所以重啟MySQL或者對表進(jìn)行OPTIMIZE操作简烤，都會使得最大ID丟失
  • 表類型為MylSAM引擎，這條記錄的ID是18摇幻。因為MyISAM會把自增主鍵的最大ID記錄到數(shù)據(jù)文件里面横侦，重啟MySQL之后挥萌，自增主鍵的最大ID也不會丟失
• MySQL8.0以及以上版本
  • 這條記錄的ID是18，因為這個版本保證ID的值是在redo日志中的枉侧，重啟之后是可以恢復(fù)的
    哪個存儲引擎執(zhí)行 select count() 更快引瀑，為什么?*
    MyISAM更快，因為MyISAM內(nèi)部維護(hù)了一個計數(shù)器榨馁，可以直接調(diào)取憨栽。
• 在 MyISAM 存儲引擎中，把表的總行數(shù)存儲在磁盤上翼虫，當(dāng)執(zhí)行 select count(*) from t 時屑柔，直接返回總數(shù)據(jù)。
• 在 InnoDB 存儲引擎中蛙讥，跟 MyISAM 不一樣锯蛀，沒有將總行數(shù)存儲在磁盤上，當(dāng)執(zhí)行 select count(*) from t 時次慢，會先把數(shù)據(jù)讀出來旁涤，一行一行的累加，最后返回總數(shù)量迫像。

InnoDB 中 count(*) 語句是在執(zhí)行的時候劈愚，全表掃描統(tǒng)計總數(shù)量，所以當(dāng)數(shù)據(jù)越來越大時闻妓，語句就越來越耗時了菌羽，為什么 InnoDB 引擎不像 MyISAM 引擎一樣，將總行數(shù)存儲到磁盤上由缆？這跟 InnoDB 的事務(wù)特性有關(guān)注祖，由于多版本并發(fā)控制（MVCC）的原因，InnoDB 表“應(yīng)該返回多少行”也是不確定的均唉。

四是晨、數(shù)據(jù)類型

主要包括以下五大類：

• 整數(shù)類型：BIT、BOOL舔箭、TINY INT罩缴、SMALL INT、MEDIUM INT层扶、 INT箫章、 BIG INT
• 浮點數(shù)類型：FLOAT、DOUBLE镜会、DECIMAL
• 字符串類型：CHAR檬寂、VARCHAR、TINY TEXT戳表、TEXT桶至、MEDIUM TEXT拿诸、LONGTEXT、TINY BLOB塞茅、BLOB、MEDIUM BLOB季率、LONG BLOB
• 日期類型：Date野瘦、DateTime、TimeStamp飒泻、Time鞭光、Year

• 其他數(shù)據(jù)類型：BINARY、VARBINARY泞遗、ENUM惰许、SET、Geometry史辙、Point汹买、MultiPoint、LineString聊倔、MultiLineString晦毙、Polygon、GeometryCollection等

  
  
  Mysql數(shù)據(jù)類型.jpeg

Mysql數(shù)據(jù)類型1.jpeg

Mysql數(shù)據(jù)類型2.jpeg

面試問題：

CHAR 和 VARCHAR 的區(qū)別耙蔑？
char是固定長度见妒，varchar長度可變：
char(n) 和 varchar(n) 中括號中 n 代表字符的個數(shù)，并不代表字節(jié)個數(shù)甸陌，比如 CHAR(30) 就可以存儲 30 個字符须揣。
存儲時，前者不管實際存儲數(shù)據(jù)的長度钱豁，直接按 char 規(guī)定的長度分配存儲空間耻卡；而后者會根據(jù)實際存儲的數(shù)據(jù)分配最終的存儲空間
相同點：

1. char(n)，varchar(n)中的n都代表字符的個數(shù)
2. 超過char寥院，varchar最大長度n的限制后劲赠，字符串會被截斷。（注：默認(rèn)是不嚴(yán)格模式秸谢，大概率生產(chǎn)上是嚴(yán)格模式凛澎，會報錯，注意）
   不同點：
3. char不論實際存儲的字符數(shù)都會占用n個字符的空間估蹄，而varchar只會占用實際字符應(yīng)該占用的字節(jié)空間加1（實際長度length塑煎，0<=length<255）或加2（length>255）。因為varchar保存數(shù)據(jù)時除了要保存字符串之外還會加一個字節(jié)來記錄長度（如果列聲明長度大于255則使用兩個字節(jié)來保存長度）臭蚁。
4. 能存儲的最大空間限制不一樣：char的存儲上限為255字節(jié)最铁。
5. char在存儲時會截斷尾部的空格讯赏，而varchar不會。
   char是適合存儲很短的冷尉、一般固定長度的字符串漱挎。例如，char非常適合存儲密碼的MD5值雀哨，因為這是一個定長的值磕谅。對于非常短的列，char比varchar在存儲空間上也更有效率雾棺。

列的字符串類型可以是什么膊夹？
字符串類型是：SET、BLOB捌浩、ENUM放刨、CHAR、TEXT尸饺、VARCHAR

BLOB和TEXT有什么區(qū)別进统？
BLOB是一個二進(jìn)制對象，可以容納可變數(shù)量的數(shù)據(jù)浪听。有四種類型的BLOB：TINYBLOB麻昼、BLOB、MEDIUMBLO和 LONGBLOB
TEXT是一個不區(qū)分大小寫的BLOB馋辈。四種TEXT類型：TINYTEXT抚芦、TEXT、MEDIUMTEXT 和 LONGTEXT迈螟。
BLOB 保存二進(jìn)制數(shù)據(jù)叉抡，TEXT 保存字符數(shù)據(jù)。

五答毫、索引

• MYSQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)褥民，所以說索引的本質(zhì)是：數(shù)據(jù)結(jié)構(gòu)

• 索引的目的在于提高查詢效率，可以類比字典洗搂、 火車站的車次表消返、圖書的目錄等 。

• 可以簡單的理解為“排好序的快速查找數(shù)據(jù)結(jié)構(gòu)”耘拇，數(shù)據(jù)本身之外撵颊，數(shù)據(jù)庫還維護(hù)者一個滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)惫叛，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)高級查找算法倡勇。這種數(shù)據(jù)結(jié)構(gòu)，就是索引嘉涌。下圖是一種可能的索引方式示例妻熊。
  

  索引示例.jpeg
  
  左邊的數(shù)據(jù)表夸浅，一共有兩列七條記錄，最左邊的是數(shù)據(jù)記錄的物理地址扔役。為了加快Col2的查找帆喇，可以維護(hù)一個右邊所示的二叉查找樹，每個節(jié)點分別包含索引鍵值亿胸，和一個指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針番枚，這樣就可以運用二叉查找在一定的復(fù)雜度內(nèi)獲取到對應(yīng)的數(shù)據(jù)，從而快速檢索出符合條件的記錄损敷。

• 索引本身也很大，不可能全部存儲在內(nèi)存中深啤，一般以索引文件的形式存儲在磁盤上

• 平常說的索引拗馒，沒有特別指明的話，就是B+樹（多路搜索樹溯街，不一定是二叉樹）結(jié)構(gòu)組織的索引诱桂。其中聚集索引，次要索引呈昔，覆蓋索引挥等，符合索引，前綴索引堤尾，唯一索引默認(rèn)都是使用B+樹索引肝劲，統(tǒng)稱索引。此外還有哈希索引等郭宝。

基本語法：

• 創(chuàng)建：
  • 創(chuàng)建索引：CREATE [UNIQUE] INDEX indexName ON mytable(username(length)); 如果是CHAR辞槐，VARCHAR類型，length可以小于字段實際長度粘室；如果是BLOB和TEXT類型榄檬，必須指定 length。
  • 修改表結(jié)構(gòu)(添加索引)：ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)
• 刪除：DROP INDEX [indexName] ON mytable;
• 查看：SHOW INDEX FROM table_name\G --可以通過添加 \G 來格式化輸出信息衔统。
• 使用ALERT命令
  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 該語句添加一個主鍵鹿榜，這意味著索引值必須是唯一的，且不能為NULL锦爵。
  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list 這條語句創(chuàng)建索引的值必須是唯一的（除了NULL外舱殿，NULL可能會出現(xiàn)多次）。
  • ALTER TABLE tbl_name ADD INDEX index_name (column_list) 添加普通索引险掀，索引值可出現(xiàn)多次怀薛。
  • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)該語句指定了索引為 FULLTEXT ，用于全文索引迷郑。

優(yōu)勢

• 提高數(shù)據(jù)檢索效率枝恋，降低數(shù)據(jù)庫IO成本
• 降低數(shù)據(jù)排序的成本创倔，降低CPU的消耗

劣勢

• 索引也是一張表，保存了主鍵和索引字段焚碌，并指向?qū)嶓w表的記錄畦攘，所以也需要占用內(nèi)存
• 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度十电，如對表進(jìn)行INSERT知押、UPDATE和DELETE。因為更新表時鹃骂，MySQL不僅要保存數(shù)據(jù)台盯，還要保存一下索引文件每次更新添加了索引列的字段， 都會調(diào)整因為更新所帶來的鍵值變化后的索引信息

MySQL索引分類

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

• B+樹索引
• Hash索引
• Full-Text全文索引
• R-Tree索引

從物理存儲角度

• 聚集索引（clustered index）
• 非聚集索引（non-clustered index）畏线，也叫輔助索引（secondary index） 聚集索引和非聚集索引都是B+樹結(jié)構(gòu)

從邏輯角度

• 主鍵索引：主鍵索引是一種特殊的唯一索引静盅，不允許有空值
• 普通索引或者單列索引：每個索引只包含單個列，一個表可以有多個單列索引
• 多列索引（復(fù)合索引寝殴、聯(lián)合索引）：復(fù)合索引指多個字段上創(chuàng)建的索引蒿叠，只有在查詢條件中使用了創(chuàng)建索引時的第一個字段，索引才會被使用蚣常。使用復(fù)合索引時遵循最左前綴集合
• 唯一索引或者非唯一索引
• 空間索引：空間索引是對空間數(shù)據(jù)類型的字段建立的索引市咽，MYSQL中的空間數(shù)據(jù)類型有4種，分別是GEOMETRY抵蚊、POINT施绎、LINESTRING、POLYGON贞绳。MYSQL使用SPATIAL關(guān)鍵字進(jìn)行擴展粘姜，使得能夠用于創(chuàng)建正規(guī)索引類型的語法創(chuàng)建空間索引。創(chuàng)建空間索引的列熔酷，必須將其聲明為NOT NULL孤紧，空間索引只能在存儲引擎為MYISAM的表中創(chuàng)建

為什么MySQL 索引中用B+tree，不用B-tree 或者其他樹拒秘，為什么不用 Hash 索引 聚簇索引/非聚簇索引号显，MySQL 索引底層實現(xiàn)，葉子結(jié)點存放的是數(shù)據(jù)還是指向數(shù)據(jù)的內(nèi)存地址躺酒，使用索引需要注意的幾個地方押蚤？ 使用索引查詢一定能提高查詢的性能嗎？為什么?

MySQL索引結(jié)構(gòu)

首先要明白索引（index）是在存儲引擎（storage engine）層面實現(xiàn)的羹应，而不是server層面揽碘。不是所有的存儲引擎都支持所有的索引類型。即使多個存儲引擎支持某一索引類型，它們的實現(xiàn)和行為也可能有所差別雳刺。

B+Tree索引

MyISAM 和 InnoDB 存儲引擎劫灶，都使用 B+Tree的數(shù)據(jù)結(jié)構(gòu)，它相對與 B-Tree結(jié)構(gòu)掖桦，所有的數(shù)據(jù)都存放在葉子節(jié)點上本昏，且把葉子節(jié)點通過指針連接到一起，形成了一條數(shù)據(jù)鏈表枪汪，以加快相鄰數(shù)據(jù)的檢索效率涌穆。

先了解下 B-Tree 和 B+Tree 的區(qū)別

B-Tree

B-Tree是為磁盤等外存儲設(shè)備設(shè)計的一種平衡查找樹。

系統(tǒng)從磁盤讀取數(shù)據(jù)到內(nèi)存時是以磁盤塊（block）為基本單位的雀久，位于同一個磁盤塊中的數(shù)據(jù)會被一次性讀取出來宿稀，而不是需要什么取什么。

InnoDB 存儲引擎中有頁（Page）的概念赖捌，頁是其磁盤管理的最小單位祝沸。InnoDB 存儲引擎中默認(rèn)每個頁的大小為16KB，可通過參數(shù) innodb_page_size 將頁的大小設(shè)置為 4K巡蘸、8K、16K擂送，在 MySQL 中可通過如下命令查看頁的大性没摹：show variables like 'innodb_page_size';

而系統(tǒng)一個磁盤塊的存儲空間往往沒有這么大，因此 InnoDB 每次申請磁盤空間時都會是若干地址連續(xù)磁盤塊來達(dá)到頁的大小 16KB嘹吨。InnoDB 在把磁盤數(shù)據(jù)讀入到磁盤時會以頁為基本單位搬味，在查詢數(shù)據(jù)時如果一個頁中的每條數(shù)據(jù)都能有助于定位數(shù)據(jù)記錄的位置，這將會減少磁盤I/O次數(shù)蟀拷，提高查詢效率碰纬。

B-Tree 結(jié)構(gòu)的數(shù)據(jù)可以讓系統(tǒng)高效的找到數(shù)據(jù)所在的磁盤塊。為了描述 B-Tree问芬，首先定義一條記錄為一個二元組[key, data] 悦析，key為記錄的鍵值，對應(yīng)表中的主鍵值此衅，data 為一行記錄中除主鍵外的數(shù)據(jù)强戴。對于不同的記錄毁枯，key值互不相同量蕊。

一棵m階的B-Tree有如下特性：

1. 每個節(jié)點最多有m個孩子
2. 除了根節(jié)點和葉子節(jié)點外，其它每個節(jié)點至少有Ceil(m/2)個孩子坞淮。
3. 若根節(jié)點不是葉子節(jié)點墨微，則至少有2個孩子
4. 所有葉子節(jié)點都在同一層道媚，且不包含其它關(guān)鍵字信息
5. 每個非終端節(jié)點包含n個關(guān)鍵字信息（P0,P1,…Pn, k1,…kn）
6. 關(guān)鍵字的個數(shù)n滿足：ceil(m/2)-1 <= n <= m-1
7. ki(i=1,…n)為關(guān)鍵字，且關(guān)鍵字升序排序
8. Pi(i=1,…n)為指向子樹根節(jié)點的指針。P(i-1)指向的子樹的所有節(jié)點關(guān)鍵字均小于ki最域，但都大于k(i-1)

B-Tree 中的每個節(jié)點根據(jù)實際情況可以包含大量的關(guān)鍵字信息和分支谴分，如下圖所示為一個 3 階的 B-Tree：

B-Tree示例.png

每個節(jié)點占用一個盤塊的磁盤空間，一個節(jié)點上有兩個升序排序的關(guān)鍵字和三個指向子樹根節(jié)點的指針羡宙，指針存儲的是子節(jié)點所在磁盤塊的地址狸剃。兩個關(guān)鍵詞劃分成的三個范圍域?qū)?yīng)三個指針指向的子樹的數(shù)據(jù)的范圍域。以根節(jié)點為例狗热，關(guān)鍵字為17和35钞馁，P1指針指向的子樹的數(shù)據(jù)范圍為小于17，P2指針指向的子樹的數(shù)據(jù)范圍為17~35匿刮，P3指針指向的子樹的數(shù)據(jù)范圍為大于35僧凰。

模擬查找關(guān)鍵字29的過程：

1. 根據(jù)根節(jié)點找到磁盤塊1，讀入內(nèi)存熟丸⊙荡耄【磁盤I/O操作第1次】
2. 比較關(guān)鍵字29在區(qū)間（17,35），找到磁盤塊1的指針P2光羞。
3. 根據(jù)P2指針找到磁盤塊3绩鸣，讀入內(nèi)存∩炊遥【磁盤I/O操作第2次】
4. 比較關(guān)鍵字29在區(qū)間（26,30）呀闻，找到磁盤塊3的指針P2。
5. 根據(jù)P2指針找到磁盤塊8潜慎，讀入內(nèi)存捡多。【磁盤I/O操作第3次】
6. 在磁盤塊8中的關(guān)鍵字列表中找到關(guān)鍵字29铐炫。

分析上面過程垒手，發(fā)現(xiàn)需要3次磁盤I/O操作，和3次內(nèi)存查找操作倒信。由于內(nèi)存中的關(guān)鍵字是一個有序表結(jié)構(gòu)科贬，可以利用二分法查找提高效率。而3次磁盤I/O操作是影響整個B-Tree查找效率的決定因素鳖悠。B-Tree相對于AVLTree縮減了節(jié)點個數(shù)唆迁，使每次磁盤I/O取到內(nèi)存的數(shù)據(jù)都發(fā)揮了作用，從而提高了查詢效率竞穷。

B+Tree

B+Tree 是在 B-Tree 基礎(chǔ)上的一種優(yōu)化唐责，使其更適合實現(xiàn)外存儲索引結(jié)構(gòu)，InnoDB 存儲引擎就是用 B+Tree 實現(xiàn)其索引結(jié)構(gòu)瘾带。

從上一節(jié)中的B-Tree結(jié)構(gòu)圖中可以看到每個節(jié)點中不僅包含數(shù)據(jù)的key值鼠哥，還有data值。而每一個頁的存儲空間是有限的，如果data數(shù)據(jù)較大時將會導(dǎo)致每個節(jié)點（即一個頁）能存儲的key的數(shù)量很小朴恳，當(dāng)存儲的數(shù)據(jù)量很大時同樣會導(dǎo)致B-Tree的深度較大抄罕，增大查詢時的磁盤I/O次數(shù)，進(jìn)而影響查詢效率于颖。在B+Tree中呆贿，所有數(shù)據(jù)記錄節(jié)點都是按照鍵值大小順序存放在同一層的葉子節(jié)點上，而非葉子節(jié)點上只存儲key值信息森渐，這樣可以大大加大每個節(jié)點存儲的key值數(shù)量做入，降低B+Tree的高度。

B+Tree相對于B-Tree有幾點不同：

1. 非葉子節(jié)點只存儲鍵值信息同衣；
2. 所有葉子節(jié)點之間都有一個鏈指針竟块；
3. 數(shù)據(jù)記錄都存放在葉子節(jié)點中

將上一節(jié)中的B-Tree優(yōu)化，由于B+Tree的非葉子節(jié)點只存儲鍵值信息耐齐，假設(shè)每個磁盤塊能存儲4個鍵值及指針信息浪秘，則變成B+Tree后其結(jié)構(gòu)如下圖所示：

B+Tree示例.jpeg

通常在B+Tree上有兩個頭指針，一個指向根節(jié)點埠况，另一個指向關(guān)鍵字最小的葉子節(jié)點耸携，而且所有葉子節(jié)點（即數(shù)據(jù)節(jié)點）之間是一種鏈?zhǔn)江h(huán)結(jié)構(gòu)。因此可以對B+Tree進(jìn)行兩種查找運算：一種是對于主鍵的范圍查找和分頁查找辕翰，另一種是從根節(jié)點開始夺衍，進(jìn)行隨機查找。

可能上面例子中只有22條數(shù)據(jù)記錄金蜀，看不出B+Tree的優(yōu)點刷后，下面做一個推算：

InnoDB存儲引擎中頁的大小為16KB的畴，一般表的主鍵類型為INT（占用4個字節(jié)）或BIGINT（占用8個字節(jié)）渊抄，指針類型也一般為4或8個字節(jié)，也就是說一個頁（B+Tree中的一個節(jié)點）中大概存儲16KB/(8B+8B)=1K個鍵值（因為是估值丧裁，為方便計算护桦，這里的K取值為10^{3）。也就是說一個深度為3的B+Tree索引可以維護(hù)10}3 * 10^3 * 10^3 = 10億 條記錄煎娇。

實際情況中每個節(jié)點可能不能填充滿二庵，因此在數(shù)據(jù)庫中，B+Tree的高度一般都在2-4層缓呛。MySQL的InnoDB存儲引擎在設(shè)計時是將根節(jié)點常駐內(nèi)存的催享，也就是說查找某一鍵值的行記錄時最多只需要1~3次磁盤I/O操作。

B+Tree性質(zhì)

1. 通過上面的分析哟绊，我們知道IO次數(shù)取決于b+數(shù)的高度h因妙，假設(shè)當(dāng)前數(shù)據(jù)表的數(shù)據(jù)為N，每個磁盤塊的數(shù)據(jù)項的數(shù)量是m，則有h=㏒(m+1)N攀涵，當(dāng)數(shù)據(jù)量N一定的情況下铣耘，m越大，h越幸怨省蜗细；而m = 磁盤塊的大小 / 數(shù)據(jù)項的大小，磁盤塊的大小也就是一個數(shù)據(jù)頁的大小怒详，是固定的炉媒，如果數(shù)據(jù)項占的空間越小，數(shù)據(jù)項的數(shù)量越多棘利，樹的高度越低橱野。這就是為什么每個數(shù)據(jù)項，即索引字段要盡量的小善玫，比如int占4字節(jié)水援，要比bigint8字節(jié)少一半。這也是為什么b+樹要求把真實的數(shù)據(jù)放到葉子節(jié)點而不是內(nèi)層節(jié)點茅郎，一旦放到內(nèi)層節(jié)點蜗元，磁盤塊的數(shù)據(jù)項會大幅度下降，導(dǎo)致樹增高系冗。當(dāng)數(shù)據(jù)項等于1時將會退化成線性表奕扣。

2. 當(dāng)b+樹的數(shù)據(jù)項是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，比如(name,age,sex)的時候掌敬，b+數(shù)是按照從左到右的順序來建立搜索樹的惯豆，比如當(dāng)(張三,20,F)這樣的數(shù)據(jù)來檢索的時候，b+樹會優(yōu)先比較name來確定下一步的所搜方向奔害，如果name相同再依次比較age和sex楷兽，最后得到檢索的數(shù)據(jù)；但當(dāng)(20,F)這樣的沒有name的數(shù)據(jù)來的時候华临，b+樹就不知道下一步該查哪個節(jié)點芯杀，因為建立搜索樹的時候name就是第一個比較因子，必須要先根據(jù)name來搜索才能知道下一步去哪里查詢雅潭。比如當(dāng)(張三,F)這樣的數(shù)據(jù)來檢索時揭厚，b+樹可以用name來指定搜索方向，但下一個字段age的缺失扶供，所以只能把名字等于張三的數(shù)據(jù)都找到筛圆，然后再匹配性別是F的數(shù)據(jù)了， 這個是非常重要的性質(zhì)椿浓，即索引的最左匹配特性太援。

MyISAM主鍵索引與輔助索引的結(jié)構(gòu)

MyISAM引擎的索引文件和數(shù)據(jù)文件是分離的漾岳。MyISAM引擎索引結(jié)構(gòu)的葉子節(jié)點的數(shù)據(jù)域，存放的并不是實際的數(shù)據(jù)記錄粉寞，而是數(shù)據(jù)記錄的地址尼荆。索引文件與數(shù)據(jù)文件分離，這樣的索引稱為"非聚簇索引"唧垦。MyISAM的主索引與輔助索引區(qū)別并不大捅儒，只是主鍵索引不能有重復(fù)的關(guān)鍵字。

MyISAM索引結(jié)構(gòu)圖.jpeg

在MyISAM中振亮，索引（含葉子節(jié)點）存放在單獨的.myi文件中巧还，葉子節(jié)點存放的是數(shù)據(jù)的物理地址偏移量（通過偏移量訪問就是隨機訪問，速度很快）坊秸。

主索引是指主鍵索引麸祷，鍵值不可能重復(fù)；輔助索引則是普通索引褒搔，鍵值可能重復(fù)阶牍。

通過索引查找數(shù)據(jù)的流程：先從索引文件中查找到索引節(jié)點，從中拿到數(shù)據(jù)的文件指針星瘾，再到數(shù)據(jù)文件中通過文件指針定位了具體的數(shù)據(jù)走孽。輔助索引類似。

InnoDB主鍵索引與輔助索引的結(jié)構(gòu)

InnoDB引擎索引結(jié)構(gòu)的葉子節(jié)點的數(shù)據(jù)域琳状，存放的就是實際的數(shù)據(jù)記錄（對于主索引磕瓷，此處會存放表中所有的數(shù)據(jù)記錄；對于輔助索引此處會引用主鍵念逞，檢索的時候通過主鍵到主鍵索引中找到對應(yīng)數(shù)據(jù)行）困食，或者說，InnoDB的數(shù)據(jù)文件本身就是主鍵索引文件翎承，這樣的索引被稱為“聚簇索引”硕盹，一個表只能有一個聚簇索引。

主鍵索引：

我們知道InnoDB索引是聚集索引审洞，它的索引和數(shù)據(jù)是存入同一個.idb文件中的莱睁，因此它的索引結(jié)構(gòu)是在同一個樹節(jié)點中同時存放索引和數(shù)據(jù)待讳，如下圖中最底層的葉子節(jié)點有三行數(shù)據(jù)芒澜，對應(yīng)于數(shù)據(jù)表中的id、stu_id创淡、name數(shù)據(jù)項痴晦。

InnoDB主鍵索引結(jié)構(gòu)圖.jpeg

在Innodb中，索引分葉子節(jié)點和非葉子節(jié)點琳彩，非葉子節(jié)點就像新華字典的目錄誊酌，單獨存放在索引段中部凑，葉子節(jié)點則是順序排列的，在數(shù)據(jù)段中碧浊。Innodb的數(shù)據(jù)文件可以按照表來切分（只需要開啟innodb_file_per_table)涂邀，切分后存放在xxx.ibd中，默認(rèn)不切分箱锐，存放在xxx.ibdata中比勉。

輔助（非主鍵）索引：

這次我們以示例中學(xué)生表中的name列建立輔助索引，它的索引結(jié)構(gòu)跟主鍵索引的結(jié)構(gòu)有很大差別驹止，在最底層的葉子結(jié)點有兩行數(shù)據(jù)浩聋，第一行的字符串是輔助索引，按照ASCII碼進(jìn)行排序臊恋，第二行的整數(shù)是主鍵的值衣洁。

這就意味著，對name列進(jìn)行條件搜索抖仅，需要兩個步驟：

① 在輔助索引上檢索name坊夫，到達(dá)其葉子節(jié)點獲取對應(yīng)的主鍵；

② 使用主鍵在主索引上再進(jìn)行對應(yīng)的檢索操作

這也就是所謂的“回表查詢”

InnoDB輔助索引結(jié)構(gòu)圖.jpeg

InnoDB 索引結(jié)構(gòu)需要注意的點

1. 數(shù)據(jù)文件本身就是索引文件
2. 表數(shù)據(jù)文件本身就是按 B+Tree 組織的一個索引結(jié)構(gòu)文件
3. 聚集索引中葉節(jié)點包含了完整的數(shù)據(jù)記錄
4. InnoDB 表必須要有主鍵撤卢，并且推薦使用整型自增主鍵

正如我們上面介紹 InnoDB 存儲結(jié)構(gòu)践樱，索引與數(shù)據(jù)是共同存儲的，不管是主鍵索引還是輔助索引凸丸，在查找時都是通過先查找到索引節(jié)點才能拿到相對應(yīng)的數(shù)據(jù)拷邢，如果我們在設(shè)計表結(jié)構(gòu)時沒有顯式指定索引列的話，MySQL 會從表中選擇數(shù)據(jù)不重復(fù)的列建立索引屎慢，如果沒有符合的列瞭稼，則 MySQL 自動為 InnoDB 表生成一個隱含字段作為主鍵，并且這個字段長度為6個字節(jié)腻惠，類型為整型环肘。

那為什么推薦使用整型自增主鍵而不是選擇UUID？

• UUID是字符串集灌，比整型消耗更多的存儲空間悔雹；
• 在B+樹中進(jìn)行查找時需要跟經(jīng)過的節(jié)點值比較大小，整型數(shù)據(jù)的比較運算比字符串更快速欣喧；
• 自增的整型索引在磁盤中會連續(xù)存儲腌零，在讀取一頁數(shù)據(jù)時也是連續(xù)；UUID是隨機產(chǎn)生的唆阿，讀取的上下兩行數(shù)據(jù)存儲是分散的益涧，不適合執(zhí)行where id > 5 && id < 20的條件查詢語句。
• 在插入或刪除數(shù)據(jù)時驯鳖，整型自增主鍵會在葉子結(jié)點的末尾建立新的葉子節(jié)點闲询，不會破壞左側(cè)子樹的結(jié)構(gòu)久免；UUID主鍵很容易出現(xiàn)這樣的情況，B+樹為了維持自身的特性扭弧，有可能會進(jìn)行結(jié)構(gòu)的重構(gòu)阎姥，消耗更多的時間。

為什么非主鍵索引結(jié)構(gòu)葉子節(jié)點存儲的是主鍵值鸽捻？

保證數(shù)據(jù)一致性和節(jié)省存儲空間丁寄，可以這么理解：商城系統(tǒng)訂單表會存儲一個用戶ID作為關(guān)聯(lián)外鍵，而不推薦存儲完整的用戶信息泊愧，因為當(dāng)我們用戶表中的信息（真實名稱伊磺、手機號、收貨地址···）修改后删咱，不需要再次維護(hù)訂單表的用戶數(shù)據(jù)屑埋，同時也節(jié)省了存儲空間。

Hash索引

• 主要就是通過Hash算法（常見的Hash算法有直接定址法痰滋、平方取中法摘能、折疊法、除數(shù)取余法敲街、隨機數(shù)法）团搞，將數(shù)據(jù)庫字段數(shù)據(jù)轉(zhuǎn)換成定長的Hash值，與這條數(shù)據(jù)的行指針一并存入Hash表的對應(yīng)位置多艇；如果發(fā)生Hash碰撞（兩個不同關(guān)鍵字的Hash值相同）逻恐，則在對應(yīng)Hash鍵下以鏈表形式存儲。 檢索算法：在檢索查詢時峻黍，就再次對待查關(guān)鍵字再次執(zhí)行相同的Hash算法复隆，得到Hash值，到對應(yīng)Hash表對應(yīng)位置取出數(shù)據(jù)即可姆涩，如果發(fā)生Hash碰撞挽拂，則需要在取值時進(jìn)行篩選。目前使用Hash索引的數(shù)據(jù)庫并不多骨饿，主要有Memory等亏栈。 MySQL目前有Memory引擎和NDB引擎支持Hash索引。

full-text全文索引

• 全文索引也是MyISAM的一種特殊索引類型宏赘，主要用于全文索引绒北，InnoDB從MYSQL5.6版本提供對全文索引的支持。
• 它用于替代效率較低的LIKE模糊匹配操作置鼻，而且可以通過多字段組合的全文索引一次性全模糊匹配多個字段镇饮。
• 同樣使用B-Tree存放索引數(shù)據(jù)蜓竹，但使用的是特定的算法箕母，將字段數(shù)據(jù)分割后再進(jìn)行索引（一般每4個字節(jié)一次分割）储藐，索引文件存儲的是分割前的索引字符串集合，與分割后的索引信息嘶是，對應(yīng)Btree結(jié)構(gòu)的節(jié)點存儲的是分割后的詞信息以及它在分割前的索引字符串集合中的位置钙勃。

R-Tree空間索引

空間索引是MyISAM的一種特殊索引類型，主要用于地理空間數(shù)據(jù)類型

面試問題

為什么Mysql索引要用B+樹不是B樹聂喇？

用B+樹不用B樹考慮的是IO對性能的影響辖源，B樹的每個節(jié)點都存儲數(shù)據(jù)，而B+樹只有葉子節(jié)點才存儲數(shù)據(jù)希太，所以查找相同數(shù)據(jù)量的情況下克饶，B樹的高度更高誊辉，IO更頻繁矾湃。數(shù)據(jù)庫索引是存儲在磁盤上的，當(dāng)數(shù)據(jù)量大時堕澄，就不能把整個索引全部加載到內(nèi)存了邀跃，只能逐一加載每一個磁盤頁（對應(yīng)索引樹的節(jié)點）。其中在MySQL底層對B+樹進(jìn)行進(jìn)一步優(yōu)化：在葉子節(jié)點中是雙向鏈表蛙紫，且在鏈表的頭結(jié)點和尾節(jié)點也是循環(huán)指向的拍屑。

面試官：為何不采用Hash方式？

因為Hash索引底層是哈希表坑傅，哈希表是一種以key-value存儲數(shù)據(jù)的結(jié)構(gòu)僵驰，所以多個數(shù)據(jù)在存儲關(guān)系上是完全沒有任何順序關(guān)系的，所以唁毒，對于區(qū)間查詢是無法直接通過索引查詢的矢渊，就需要全表掃描。所以枉证，哈希索引只適用于等值查詢的場景矮男。而B+ Tree是一種多路平衡查詢樹，所以他的節(jié)點是天然有序的（左子節(jié)點小于父節(jié)點室谚、父節(jié)點小于右子節(jié)點）毡鉴，所以對于范圍查詢的時候不需要做全表掃描。

哈希索引不支持多列聯(lián)合索引的最左匹配規(guī)則秒赤，如果有大量重復(fù)鍵值得情況下猪瞬，哈希索引的效率會很低，因為存在哈希碰撞問題入篮。

哪些情況需要創(chuàng)建索引

1. 主鍵自動建立唯一索引
2. 頻繁作為查詢條件的字段
3. 查詢中與其他表關(guān)聯(lián)的字段陈瘦，外鍵關(guān)系建立索引
4. 單鍵/組合索引的選擇問題，高并發(fā)下傾向創(chuàng)建組合索引
5. 查詢中排序的字段潮售，排序字段通過索引訪問大幅提高排序速度
6. 查詢中統(tǒng)計或分組字段

哪些情況不要創(chuàng)建索引

1. 表記錄太少
2. 經(jīng)常增刪改的表
3. 數(shù)據(jù)重復(fù)且分布均勻的表字段痊项，只應(yīng)該為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引（如果某個數(shù)據(jù)類包含太多的重復(fù)數(shù)據(jù)锅风，建立索引沒有太大意義）
4. 頻繁更新的字段不適合創(chuàng)建索引（會加重IO負(fù)擔(dān)）
5. where條件里用不到的字段不創(chuàng)建索引

MySQL高效索引

覆蓋索引（Covering Index）,或者叫索引覆蓋， 也就是平時所說的不需要回表操作

• 就是select的數(shù)據(jù)列只用從索引中就能夠取得鞍泉，不必讀取數(shù)據(jù)行皱埠，MySQL可以利用索引返回select列表中的字段，而不必根據(jù)索引再次讀取數(shù)據(jù)文件咖驮，換句話說查詢列要被所建的索引覆蓋边器。
• 索引是高效找到行的一個方法，但是一般數(shù)據(jù)庫也能使用索引找到一個列的數(shù)據(jù)托修，因此它不必讀取整個行忘巧。畢竟索引葉子節(jié)點存儲了它們索引的數(shù)據(jù)，當(dāng)能通過讀取索引就可以得到想要的數(shù)據(jù)睦刃，那就不需要讀取行了袋坑。一個索引包含（覆蓋）滿足查詢結(jié)果的數(shù)據(jù)就叫做覆蓋索引。
• 判斷標(biāo)準(zhǔn) 使用explain眯勾，可以通過輸出的extra列來判斷枣宫，對于一個索引覆蓋查詢，顯示為using index吃环，MySQL查詢優(yōu)化器在執(zhí)行查詢前會決定是否有索引覆蓋查詢