一、MySQL架構(gòu)
和其它數(shù)據(jù)庫(kù)相比，MySQL有點(diǎn)與眾不同图焰，它的架構(gòu)可以在多種不同場(chǎng)景中應(yīng)用并發(fā)揮良好作用启盛。主要體現(xiàn)在存儲(chǔ)引擎的架構(gòu)上，插件式的存儲(chǔ)引擎架構(gòu)將查詢處理和其它的系統(tǒng)任務(wù)以及數(shù)據(jù)的存儲(chǔ)提取相分離技羔。這種架構(gòu)可以根據(jù)業(yè)務(wù)的需求和實(shí)際需要選擇合適的存儲(chǔ)引擎僵闯。
- 連接層：最上層是一些客戶端和連接服務(wù)。主要完成一些類(lèi)似于連接處理藤滥、授權(quán)認(rèn)證鳖粟、及相關(guān)的安全方案。在該層上引入了線程池的概念拙绊，為通過(guò)認(rèn)證安全接入的客戶端提供線程向图。同樣在該層上可以實(shí)現(xiàn)基于SSL的安全鏈接泳秀。服務(wù)器也會(huì)為安全接入的每個(gè)客戶端驗(yàn)證它所具有的操作權(quán)限。
- 服務(wù)層：第二層服務(wù)層榄攀，主要完成大部分的核心服務(wù)功能嗜傅， 包括查詢解析、分析檩赢、優(yōu)化吕嘀、緩存、以及所有的內(nèi)置函數(shù)贞瞒，所有跨存儲(chǔ)引擎的功能也都在這一層實(shí)現(xiàn)偶房，包括觸發(fā)器、存儲(chǔ)過(guò)程军浆、視圖等
- 引擎層：第三層存儲(chǔ)引擎層棕洋，存儲(chǔ)引擎真正的負(fù)責(zé)了MySQL中數(shù)據(jù)的存儲(chǔ)和提取，服務(wù)器通過(guò)API與存儲(chǔ)引擎進(jìn)行通信瘾敢。不同的存儲(chǔ)引擎具有的功能不同拍冠，這樣我們可以根據(jù)自己的實(shí)際需要進(jìn)行選取
- 存儲(chǔ)層：第四層為數(shù)據(jù)存儲(chǔ)層，主要是將數(shù)據(jù)存儲(chǔ)在運(yùn)行于該設(shè)備的文件系統(tǒng)之上簇抵，并完成與存儲(chǔ)引擎的交互
?
畫(huà)出 MySQL 架構(gòu)圖庆杜，這種變態(tài)問(wèn)題都能問(wèn)的出來(lái)
MySQL 的查詢流程具體是？or 一條SQL語(yǔ)句在MySQL中如何執(zhí)行的碟摆？
客戶端請(qǐng)求 ---> 連接器（驗(yàn)證用戶身份晃财，給予權(quán)限） ---> 查詢緩存（存在緩存則直接返回，不存在則執(zhí)行后續(xù)操作） ---> 分析器（對(duì)SQL進(jìn)行詞法分析和語(yǔ)法分析操作） ---> 優(yōu)化器（主要對(duì)執(zhí)行的sql優(yōu)化選擇最優(yōu)的執(zhí)行方案方法） ---> 執(zhí)行器（執(zhí)行時(shí)會(huì)先看用戶是否有執(zhí)行權(quán)限典蜕，有才去使用這個(gè)引擎提供的接口） ---> 去引擎層獲取數(shù)據(jù)返回（如果開(kāi)啟查詢緩存則會(huì)緩存查詢結(jié)果）圖：極客時(shí)間
?
說(shuō)說(shuō)MySQL有哪些存儲(chǔ)引擎断盛？都有哪些區(qū)別？
二愉舔、存儲(chǔ)引擎
存儲(chǔ)引擎是MySQL的組件钢猛，用于處理不同表類(lèi)型的SQL操作。不同的存儲(chǔ)引擎提供不同的存儲(chǔ)機(jī)制轩缤、索引技巧命迈、鎖定水平等功能，使用不同的存儲(chǔ)引擎火的，還可以獲得特定的功能壶愤。
使用哪一種引擎可以靈活選擇，一個(gè)數(shù)據(jù)庫(kù)中多個(gè)表可以使用不同引擎以滿足各種性能和實(shí)際需求馏鹤，使用合適的存儲(chǔ)引擎征椒，將會(huì)提高整個(gè)數(shù)據(jù)庫(kù)的性能 。
MySQL服務(wù)器使用可插拔的存儲(chǔ)引擎體系結(jié)構(gòu)湃累，可以從運(yùn)行中的 MySQL 服務(wù)器加載或卸載存儲(chǔ)引擎 勃救。
查看存儲(chǔ)引擎
-- 查看支持的存儲(chǔ)引擎
SHOW ENGINES
-- 查看默認(rèn)存儲(chǔ)引擎
SHOW VARIABLES LIKE 'storage_engine'
--查看具體某一個(gè)表所使用的存儲(chǔ)引擎碍讨，這個(gè)默認(rèn)存儲(chǔ)引擎被修改了！
show create table tablename
--準(zhǔn)確查看某個(gè)數(shù)據(jù)庫(kù)中的某一表所使用的存儲(chǔ)引擎
show table status like 'tablename'
show table status from database where name="tablename"
設(shè)置存儲(chǔ)引擎
-- 建表時(shí)指定存儲(chǔ)引擎剪芥。默認(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;
-- 修改存儲(chǔ)引擎
ALTER TABLE t ENGINE = InnoDB;
-- 修改默認(rèn)存儲(chǔ)引擎，也可以在配置文件my.cnf中修改默認(rèn)引擎
SET default_storage_engine=NDBCLUSTER;
默認(rèn)情況下税肪，每當(dāng) CREATE TABLE 或 ALTER TABLE 不能使用默認(rèn)存儲(chǔ)引擎時(shí)溉躲，都會(huì)生成一個(gè)警告。為了防止在所需的引擎不可用時(shí)出現(xiàn)令人困惑的意外行為益兄，可以啟用 NO_ENGINE_SUBSTITUTION SQL 模式锻梳。如果所需的引擎不可用，則此設(shè)置將產(chǎn)生錯(cuò)誤而不是警告净捅，并且不會(huì)創(chuàng)建或更改表
存儲(chǔ)引擎對(duì)比
常見(jiàn)的存儲(chǔ)引擎就 InnoDB疑枯、MyISAM、Memory蛔六、NDB荆永。
InnoDB 現(xiàn)在是 MySQL 默認(rèn)的存儲(chǔ)引擎，支持事務(wù)国章、行級(jí)鎖定和外鍵
文件存儲(chǔ)結(jié)構(gòu)對(duì)比
在 MySQL中建立任何一張數(shù)據(jù)表具钥，在其數(shù)據(jù)目錄對(duì)應(yīng)的數(shù)據(jù)庫(kù)目錄下都有對(duì)應(yīng)表的 .frm 文件，.frm 文件是用來(lái)保存每個(gè)數(shù)據(jù)表的元數(shù)據(jù)(meta)信息液兽，包括表結(jié)構(gòu)的定義等骂删，與數(shù)據(jù)庫(kù)存儲(chǔ)引擎無(wú)關(guān)，也就是任何存儲(chǔ)引擎的數(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 存儲(chǔ)引擎專用欧瘪，用于存儲(chǔ)MyISAM 表的數(shù)據(jù)
- .MYI (MYIndex)文件：MyISAM 存儲(chǔ)引擎專用，用于存儲(chǔ)MyISAM 表的索引相關(guān)信息
InnoDB 物理文件結(jié)構(gòu)為：
- .frm 文件：與表相關(guān)的元數(shù)據(jù)信息都存放在frm文件匙赞，包括表結(jié)構(gòu)的定義信息等
- .ibd 文件或 .ibdata 文件：這兩種文件都是存放 InnoDB 數(shù)據(jù)的文件恋追，之所以有兩種文件形式存放 InnoDB 的數(shù)據(jù)，是因?yàn)?InnoDB 的數(shù)據(jù)存儲(chǔ)方式能夠通過(guò)配置來(lái)決定是使用共享表空間存放存儲(chǔ)數(shù)據(jù)罚屋，還是用獨(dú)享表空間存放存儲(chǔ)數(shù)據(jù)。獨(dú)享表空間存儲(chǔ)方式使用.ibd文件嗅绸，并且每個(gè)表一個(gè).ibd文件 共享表空間存儲(chǔ)方式使用.ibdata文件脾猛，所有表共同使用一個(gè).ibdata文件（或多個(gè)，可自己配置）
?
ps：正經(jīng)公司鱼鸠，這些都有專業(yè)運(yùn)維去做猛拴，數(shù)據(jù)備份羹铅、恢復(fù)啥的，讓我一個(gè) Javaer 搞這的話愉昆，加錢(qián)不职员？
面試這么回答
- InnoDB 支持事務(wù)，MyISAM 不支持事務(wù)跛溉。這是 MySQL 將默認(rèn)存儲(chǔ)引擎從 MyISAM 變成 InnoDB 的重要原因之一焊切；
- InnoDB 支持外鍵，而 MyISAM 不支持芳室。對(duì)一個(gè)包含外鍵的 InnoDB 表轉(zhuǎn)為 MYISAM 會(huì)失斪ǚ尽；
- InnoDB 是聚簇索引堪侯，MyISAM 是非聚簇索引嚎尤。聚簇索引的文件存放在主鍵索引的葉子節(jié)點(diǎn)上，因此 InnoDB 必須要有主鍵伍宦，通過(guò)主鍵索引效率很高芽死。但是輔助索引需要兩次查詢以故，先查詢到主鍵草穆，然后再通過(guò)主鍵查詢到數(shù)據(jù)。因此凡人，主鍵不應(yīng)該過(guò)大滓玖，因?yàn)橹麈I太大坪哄，其他索引也都會(huì)很大。而 MyISAM 是非聚集索引势篡，數(shù)據(jù)文件是分離的翩肌，索引保存的是數(shù)據(jù)文件的指針。主鍵索引和輔助索引是獨(dú)立的禁悠。
- InnoDB 不保存表的具體行數(shù)念祭，執(zhí)行select count(*) from table 時(shí)需要全表掃描。而 MyISAM 用一個(gè)變量保存了整個(gè)表的行數(shù)碍侦，執(zhí)行上述語(yǔ)句時(shí)只需要讀出該變量即可粱坤，速度很快；
- InnoDB 最小的鎖粒度是行鎖瓷产，MyISAM 最小的鎖粒度是表鎖站玄。一個(gè)更新語(yǔ)句會(huì)鎖住整張表，導(dǎo)致其他查詢和更新都會(huì)被阻塞濒旦，因此并發(fā)訪問(wèn)受限株旷。這也是 MySQL 將默認(rèn)存儲(chǔ)引擎從 MyISAM 變成 InnoDB 的重要原因之一；
對(duì)比項(xiàng)MyISAMInnoDB主外鍵不支持支持事務(wù)不支持支持行表鎖表鎖，即使操作一條記錄也會(huì)鎖住整個(gè)表晾剖，不適合高并發(fā)的操作行鎖,操作時(shí)只鎖某一行锉矢，不對(duì)其它行有影響，適合高并發(fā)的操作緩存只緩存索引齿尽，不緩存真實(shí)數(shù)據(jù)不僅緩存索引還要緩存真實(shí)數(shù)據(jù)沽损，對(duì)內(nèi)存要求較高，而且內(nèi)存大小對(duì)性能有決定性的影響表空間小大關(guān)注點(diǎn)性能事務(wù)默認(rèn)安裝是是
?
一張表循头，里面有ID自增主鍵绵估，當(dāng)insert了17條記錄之后，刪除了第15,16,17條記錄贷岸，再把Mysql重啟壹士，再insert一條記錄，這條記錄的ID是18還是15 偿警？
如果表的類(lèi)型是MyISAM躏救，那么是18。因?yàn)镸yISAM表會(huì)把自增主鍵的最大ID 記錄到數(shù)據(jù)文件中螟蒸，重啟MySQL自增主鍵的最大ID也不會(huì)丟失盒使；
如果表的類(lèi)型是InnoDB，那么是15七嫌。因?yàn)镮nnoDB 表只是把自增主鍵的最大ID記錄到內(nèi)存中少办，所以重啟數(shù)據(jù)庫(kù)或?qū)Ρ磉M(jìn)行OPTION操作，都會(huì)導(dǎo)致最大ID丟失诵原。
?
哪個(gè)存儲(chǔ)引擎執(zhí)行 select count(*) 更快英妓，為什么?
MyISAM更快，因?yàn)镸yISAM內(nèi)部維護(hù)了一個(gè)計(jì)數(shù)器绍赛，可以直接調(diào)取蔓纠。
- 在 MyISAM 存儲(chǔ)引擎中，把表的總行數(shù)存儲(chǔ)在磁盤(pán)上吗蚌，當(dāng)執(zhí)行 select count(*) from t 時(shí)腿倚，直接返回總數(shù)據(jù)。
- 在 InnoDB 存儲(chǔ)引擎中蚯妇，跟 MyISAM 不一樣敷燎，沒(méi)有將總行數(shù)存儲(chǔ)在磁盤(pán)上，當(dāng)執(zhí)行 select count(*) from t 時(shí)箩言，會(huì)先把數(shù)據(jù)讀出來(lái)硬贯，一行一行的累加，最后返回總數(shù)量陨收。
InnoDB 中 count(*) 語(yǔ)句是在執(zhí)行的時(shí)候饭豹，全表掃描統(tǒng)計(jì)總數(shù)量，所以當(dāng)數(shù)據(jù)越來(lái)越大時(shí)，語(yǔ)句就越來(lái)越耗時(shí)了墨状，為什么 InnoDB 引擎不像 MyISAM 引擎一樣，將總行數(shù)存儲(chǔ)到磁盤(pán)上菲饼？這跟 InnoDB 的事務(wù)特性有關(guān)肾砂，由于多版本并發(fā)控制（MVCC）的原因，InnoDB 表“應(yīng)該返回多少行”也是不確定的宏悦。
三镐确、數(shù)據(jù)類(lèi)型
主要包括以下五大類(lèi)：
- 整數(shù)類(lèi)型：BIT、BOOL饼煞、TINY INT源葫、SMALL INT、MEDIUM INT砖瞧、 INT息堂、 BIG INT
- 浮點(diǎn)數(shù)類(lèi)型：FLOAT、DOUBLE块促、DECIMAL
- 字符串類(lèi)型：CHAR荣堰、VARCHAR、TINY TEXT竭翠、TEXT振坚、MEDIUM TEXT、LONGTEXT斋扰、TINY BLOB渡八、BLOB、MEDIUM BLOB传货、LONG BLOB
- 日期類(lèi)型：Date屎鳍、DateTime、TimeStamp损离、Time哥艇、Year
- 其他數(shù)據(jù)類(lèi)型：BINARY、VARBINARY僻澎、ENUM貌踏、SET、Geometry窟勃、Point祖乳、MultiPoint、LineString秉氧、MultiLineString眷昆、Polygon、GeometryCollection等
?
CHAR 和 VARCHAR 的區(qū)別？
char是固定長(zhǎng)度亚斋，varchar長(zhǎng)度可變：
char(n) 和 varchar(n) 中括號(hào)中 n 代表字符的個(gè)數(shù)作媚，并不代表字節(jié)個(gè)數(shù)，比如 CHAR(30) 就可以存儲(chǔ) 30 個(gè)字符帅刊。
存儲(chǔ)時(shí)纸泡，前者不管實(shí)際存儲(chǔ)數(shù)據(jù)的長(zhǎng)度，直接按 char 規(guī)定的長(zhǎng)度分配存儲(chǔ)空間赖瞒；而后者會(huì)根據(jù)實(shí)際存儲(chǔ)的數(shù)據(jù)分配最終的存儲(chǔ)空間
相同點(diǎn)：
- char(n)女揭，varchar(n)中的n都代表字符的個(gè)數(shù)
- 超過(guò)char，varchar最大長(zhǎng)度n的限制后栏饮，字符串會(huì)被截?cái)唷?/li>
不同點(diǎn)：
- char不論實(shí)際存儲(chǔ)的字符數(shù)都會(huì)占用n個(gè)字符的空間吧兔，而varchar只會(huì)占用實(shí)際字符應(yīng)該占用的字節(jié)空間加1（實(shí)際長(zhǎng)度length，0<=length<255）或加2（length>255）袍嬉。因?yàn)関archar保存數(shù)據(jù)時(shí)除了要保存字符串之外還會(huì)加一個(gè)字節(jié)來(lái)記錄長(zhǎng)度（如果列聲明長(zhǎng)度大于255則使用兩個(gè)字節(jié)來(lái)保存長(zhǎng)度）境蔼。
- 能存儲(chǔ)的最大空間限制不一樣：char的存儲(chǔ)上限為255字節(jié)。
- char在存儲(chǔ)時(shí)會(huì)截?cái)辔膊康目崭穸梗鴙archar不會(huì)欧穴。
char是適合存儲(chǔ)很短的、一般固定長(zhǎng)度的字符串泵殴。例如涮帘，char非常適合存儲(chǔ)密碼的MD5值，因?yàn)檫@是一個(gè)定長(zhǎng)的值笑诅。對(duì)于非常短的列调缨，char比varchar在存儲(chǔ)空間上也更有效率。
?
列的字符串類(lèi)型可以是什么吆你？
字符串類(lèi)型是：SET弦叶、BLOB、ENUM妇多、CHAR伤哺、CHAR、TEXT者祖、VARCHAR
?
BLOB和TEXT有什么區(qū)別立莉？
BLOB是一個(gè)二進(jìn)制對(duì)象，可以容納可變數(shù)量的數(shù)據(jù)七问。有四種類(lèi)型的BLOB：TINYBLOB蜓耻、BLOB、MEDIUMBLO和 LONGBLOB
TEXT是一個(gè)不區(qū)分大小寫(xiě)的BLOB械巡。四種TEXT類(lèi)型：TINYTEXT刹淌、TEXT饶氏、MEDIUMTEXT 和 LONGTEXT。
BLOB 保存二進(jìn)制數(shù)據(jù)有勾，TEXT 保存字符數(shù)據(jù)疹启。
四、索引
?
說(shuō)說(shuō)你對(duì) MySQL 索引的理解蔼卡？
數(shù)據(jù)庫(kù)索引的原理皮仁，為什么要用 B+樹(shù)，為什么不用二叉樹(shù)菲宴？
聚集索引與非聚集索引的區(qū)別？
InnoDB引擎中的索引策略趋急，了解過(guò)嗎喝峦？
創(chuàng)建索引的方式有哪些？
聚簇索引/非聚簇索引呜达，mysql索引底層實(shí)現(xiàn)谣蠢，為什么不用B-tree，為什么不用hash查近，葉子結(jié)點(diǎn)存放的是數(shù)據(jù)還是指向數(shù)據(jù)的內(nèi)存地址眉踱，使用索引需要注意的幾個(gè)地方？
- MYSQL官方對(duì)索引的定義為：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)霜威，所以說(shuō)索引的本質(zhì)是：數(shù)據(jù)結(jié)構(gòu)
- 索引的目的在于提高查詢效率谈喳，可以類(lèi)比字典、 火車(chē)站的車(chē)次表戈泼、圖書(shū)的目錄等 婿禽。
- 可以簡(jiǎn)單的理解為“排好序的快速查找數(shù)據(jù)結(jié)構(gòu)”，數(shù)據(jù)本身之外大猛，數(shù)據(jù)庫(kù)還維護(hù)者一個(gè)滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)扭倾，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法挽绩。這種數(shù)據(jù)結(jié)構(gòu)膛壹，就是索引。下圖是一種可能的索引方式示例唉堪。左邊的數(shù)據(jù)表模聋，一共有兩列七條記錄，最左邊的是數(shù)據(jù)記錄的物理地址巨坊。為了加快Col2的查找撬槽，可以維護(hù)一個(gè)右邊所示的二叉查找樹(shù)，每個(gè)節(jié)點(diǎn)分別包含索引鍵值趾撵，和一個(gè)指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針侄柔，這樣就可以運(yùn)用二叉查找在一定的復(fù)雜度內(nèi)獲取到對(duì)應(yīng)的數(shù)據(jù)共啃，從而快速檢索出符合條件的記錄。
- 索引本身也很大暂题，不可能全部存儲(chǔ)在內(nèi)存中移剪，一般以索引文件的形式存儲(chǔ)在磁盤(pán)上
- 平常說(shuō)的索引，沒(méi)有特別指明的話薪者，就是B+樹(shù)（多路搜索樹(shù)纵苛，不一定是二叉樹(shù)）結(jié)構(gòu)組織的索引。其中聚集索引言津，次要索引攻人，覆蓋索引，符合索引悬槽，前綴索引怀吻，唯一索引默認(rèn)都是使用B+樹(shù)索引，統(tǒng)稱索引初婆。此外還有哈希索引等蓬坡。
基本語(yǔ)法：
創(chuàng)建：
創(chuàng)建索引：CREATE [UNIQUE] INDEX indexName ON mytable(username(length));如果是CHAR，VARCHAR類(lèi)型磅叛，length可以小于字段實(shí)際長(zhǎng)度屑咳；如果是BLOB和TEXT類(lèi)型，必須指定 length弊琴。
修改表結(jié)構(gòu)(添加索引)：ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)
刪除：DROP INDEX [indexName] ON mytable;
查看：SHOW INDEX FROM table_name\G --可以通過(guò)添加 \G 來(lái)格式化輸出信息兆龙。
使用ALERT命令
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 該語(yǔ)句添加一個(gè)主鍵，這意味著索引值必須是唯一的敲董，且不能為NULL详瑞。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list 這條語(yǔ)句創(chuàng)建索引的值必須是唯一的（除了NULL外，NULL可能會(huì)出現(xiàn)多次）臣缀。
ALTER TABLE tbl_name ADD INDEX index_name (column_list) 添加普通索引坝橡，索引值可出現(xiàn)多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)該語(yǔ)句指定了索引為 FULLTEXT 精置，用于全文索引计寇。
優(yōu)勢(shì)
- 提高數(shù)據(jù)檢索效率，降低數(shù)據(jù)庫(kù)IO成本
- 降低數(shù)據(jù)排序的成本脂倦，降低CPU的消耗
劣勢(shì)
- 索引也是一張表番宁，保存了主鍵和索引字段，并指向?qū)嶓w表的記錄赖阻，所以也需要占用內(nèi)存
- 雖然索引大大提高了查詢速度蝶押，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT火欧、UPDATE和DELETE棋电。因?yàn)楦卤頃r(shí)茎截，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段赶盔， 都會(huì)調(diào)整因?yàn)楦滤鶐?lái)的鍵值變化后的索引信息
MySQL索引分類(lèi)
數(shù)據(jù)結(jié)構(gòu)角度
- B+樹(shù)索引
- Hash索引
- Full-Text全文索引
- R-Tree索引
從物理存儲(chǔ)角度
- 聚集索引（clustered index）
- 非聚集索引（non-clustered index）企锌，也叫輔助索引（secondary index）聚集索引和非聚集索引都是B+樹(shù)結(jié)構(gòu)
從邏輯角度
- 主鍵索引：主鍵索引是一種特殊的唯一索引，不允許有空值
- 普通索引或者單列索引：每個(gè)索引只包含單個(gè)列于未，一個(gè)表可以有多個(gè)單列索引
- 多列索引（復(fù)合索引撕攒、聯(lián)合索引）：復(fù)合索引指多個(gè)字段上創(chuàng)建的索引黑忱，只有在查詢條件中使用了創(chuàng)建索引時(shí)的第一個(gè)字段遭庶，索引才會(huì)被使用镊绪。使用復(fù)合索引時(shí)遵循最左前綴集合
- 唯一索引或者非唯一索引
- 空間索引：空間索引是對(duì)空間數(shù)據(jù)類(lèi)型的字段建立的索引树灶，MYSQL中的空間數(shù)據(jù)類(lèi)型有4種，分別是GEOMETRY硼莽、POINT橄杨、LINESTRING莺葫、POLYGON片习。MYSQL使用SPATIAL關(guān)鍵字進(jìn)行擴(kuò)展，使得能夠用于創(chuàng)建正規(guī)索引類(lèi)型的語(yǔ)法創(chuàng)建空間索引蹬叭。創(chuàng)建空間索引的列藕咏，必須將其聲明為NOT NULL，空間索引只能在存儲(chǔ)引擎為MYISAM的表中創(chuàng)建
?
為什么MySQL 索引中用B+tree秽五，不用B-tree 或者其他樹(shù)孽查，為什么不用 Hash 索引
聚簇索引/非聚簇索引，MySQL 索引底層實(shí)現(xiàn)坦喘，葉子結(jié)點(diǎn)存放的是數(shù)據(jù)還是指向數(shù)據(jù)的內(nèi)存地址盲再，使用索引需要注意的幾個(gè)地方？
使用索引查詢一定能提高查詢的性能嗎瓣铣？為什么?
MySQL索引結(jié)構(gòu)
首先要明白索引（index）是在存儲(chǔ)引擎（storage engine）層面實(shí)現(xiàn)的答朋，而不是server層面。不是所有的存儲(chǔ)引擎都支持所有的索引類(lèi)型棠笑。即使多個(gè)存儲(chǔ)引擎支持某一索引類(lèi)型梦碗，它們的實(shí)現(xiàn)和行為也可能有所差別。
B+Tree索引
MyISAM 和 InnoDB 存儲(chǔ)引擎蓖救，都使用 B+Tree的數(shù)據(jù)結(jié)構(gòu)洪规，它相對(duì)與 B-Tree結(jié)構(gòu)，所有的數(shù)據(jù)都存放在葉子節(jié)點(diǎn)上循捺，且把葉子節(jié)點(diǎn)通過(guò)指針連接到一起斩例，形成了一條數(shù)據(jù)鏈表，以加快相鄰數(shù)據(jù)的檢索效率从橘。
先了解下 B-Tree 和 B+Tree 的區(qū)別
B-Tree
B-Tree是為磁盤(pán)等外存儲(chǔ)設(shè)備設(shè)計(jì)的一種平衡查找樹(shù)念赶。
系統(tǒng)從磁盤(pán)讀取數(shù)據(jù)到內(nèi)存時(shí)是以磁盤(pán)塊（block）為基本單位的础钠，位于同一個(gè)磁盤(pán)塊中的數(shù)據(jù)會(huì)被一次性讀取出來(lái)，而不是需要什么取什么晶乔。
InnoDB 存儲(chǔ)引擎中有頁(yè)（Page）的概念珍坊，頁(yè)是其磁盤(pán)管理的最小單位。InnoDB 存儲(chǔ)引擎中默認(rèn)每個(gè)頁(yè)的大小為16KB正罢，可通過(guò)參數(shù) innodb_page_size 將頁(yè)的大小設(shè)置為 4K阵漏、8K、16K翻具，在 MySQL 中可通過(guò)如下命令查看頁(yè)的大新那印：show variables like 'innodb_page_size';
而系統(tǒng)一個(gè)磁盤(pán)塊的存儲(chǔ)空間往往沒(méi)有這么大，因此 InnoDB 每次申請(qǐng)磁盤(pán)空間時(shí)都會(huì)是若干地址連續(xù)磁盤(pán)塊來(lái)達(dá)到頁(yè)的大小 16KB裆泳。InnoDB 在把磁盤(pán)數(shù)據(jù)讀入到磁盤(pán)時(shí)會(huì)以頁(yè)為基本單位叹洲，在查詢數(shù)據(jù)時(shí)如果一個(gè)頁(yè)中的每條數(shù)據(jù)都能有助于定位數(shù)據(jù)記錄的位置，這將會(huì)減少磁盤(pán)I/O次數(shù)工禾，提高查詢效率运提。
B-Tree 結(jié)構(gòu)的數(shù)據(jù)可以讓系統(tǒng)高效的找到數(shù)據(jù)所在的磁盤(pán)塊。為了描述 B-Tree闻葵，首先定義一條記錄為一個(gè)二元組[key, data] 民泵，key為記錄的鍵值，對(duì)應(yīng)表中的主鍵值槽畔，data 為一行記錄中除主鍵外的數(shù)據(jù)栈妆。對(duì)于不同的記錄，key值互不相同鳞尔。
一棵m階的B-Tree有如下特性：
- 每個(gè)節(jié)點(diǎn)最多有m個(gè)孩子
- 除了根節(jié)點(diǎn)和葉子節(jié)點(diǎn)外兔沃，其它每個(gè)節(jié)點(diǎn)至少有Ceil(m/2)個(gè)孩子。
- 若根節(jié)點(diǎn)不是葉子節(jié)點(diǎn)，則至少有2個(gè)孩子
- 所有葉子節(jié)點(diǎn)都在同一層奇颠，且不包含其它關(guān)鍵字信息
- 每個(gè)非終端節(jié)點(diǎn)包含n個(gè)關(guān)鍵字信息（P0,P1,…Pn, k1,…kn）
- 關(guān)鍵字的個(gè)數(shù)n滿足：ceil(m/2)-1 <= n <= m-1
- ki(i=1,…n)為關(guān)鍵字，且關(guān)鍵字升序排序
- Pi(i=1,…n)為指向子樹(shù)根節(jié)點(diǎn)的指針松却。P(i-1)指向的子樹(shù)的所有節(jié)點(diǎn)關(guān)鍵字均小于ki程腹，但都大于k(i-1)
B-Tree 中的每個(gè)節(jié)點(diǎn)根據(jù)實(shí)際情況可以包含大量的關(guān)鍵字信息和分支淀歇，如下圖所示為一個(gè) 3 階的 B-Tree：
圖片：DobbinSoong
每個(gè)節(jié)點(diǎn)占用一個(gè)盤(pán)塊的磁盤(pán)空間，一個(gè)節(jié)點(diǎn)上有兩個(gè)升序排序的關(guān)鍵字和三個(gè)指向子樹(shù)根節(jié)點(diǎn)的指針投剥，指針存儲(chǔ)的是子節(jié)點(diǎn)所在磁盤(pán)塊的地址盛龄。兩個(gè)關(guān)鍵詞劃分成的三個(gè)范圍域?qū)?yīng)三個(gè)指針指向的子樹(shù)的數(shù)據(jù)的范圍域。以根節(jié)點(diǎn)為例政恍，關(guān)鍵字為17和35汪拥，P1指針指向的子樹(shù)的數(shù)據(jù)范圍為小于17，P2指針指向的子樹(shù)的數(shù)據(jù)范圍為17~35篙耗，P3指針指向的子樹(shù)的數(shù)據(jù)范圍為大于35迫筑。
模擬查找關(guān)鍵字29個(gè)過(guò)程：
- 根據(jù)根節(jié)點(diǎn)找到磁盤(pán)塊1，讀入內(nèi)存宗弯「迹【磁盤(pán)I/O操作第1次】
- 比較關(guān)鍵字29在區(qū)間（17,35），找到磁盤(pán)塊1的指針P2蒙保。
- 根據(jù)P2指針找到磁盤(pán)塊3辕棚，讀入內(nèi)存∽匪【磁盤(pán)I/O操作第2次】
- 比較關(guān)鍵字29在區(qū)間（26,30）坟募，找到磁盤(pán)塊3的指針P2。
- 根據(jù)P2指針找到磁盤(pán)塊8邑狸，讀入內(nèi)存懈糯。【磁盤(pán)I/O操作第3次】
- 在磁盤(pán)塊8中的關(guān)鍵字列表中找到關(guān)鍵字29单雾。
分析上面過(guò)程赚哗，發(fā)現(xiàn)需要3次磁盤(pán)I/O操作，和3次內(nèi)存查找操作硅堆。由于內(nèi)存中的關(guān)鍵字是一個(gè)有序表結(jié)構(gòu)屿储，可以利用二分法查找提高效率。而3次磁盤(pán)I/O操作是影響整個(gè)B-Tree查找效率的決定因素渐逃。B-Tree相對(duì)于AVLTree縮減了節(jié)點(diǎn)個(gè)數(shù)够掠，使每次磁盤(pán)I/O取到內(nèi)存的數(shù)據(jù)都發(fā)揮了作用，從而提高了查詢效率茄菊。
B+Tree
B+Tree 是在 B-Tree 基礎(chǔ)上的一種優(yōu)化疯潭，使其更適合實(shí)現(xiàn)外存儲(chǔ)索引結(jié)構(gòu)，InnoDB 存儲(chǔ)引擎就是用 B+Tree 實(shí)現(xiàn)其索引結(jié)構(gòu)面殖。
從上一節(jié)中的B-Tree結(jié)構(gòu)圖中可以看到每個(gè)節(jié)點(diǎn)中不僅包含數(shù)據(jù)的key值竖哩，還有data值。而每一個(gè)頁(yè)的存儲(chǔ)空間是有限的脊僚，如果data數(shù)據(jù)較大時(shí)將會(huì)導(dǎo)致每個(gè)節(jié)點(diǎn)（即一個(gè)頁(yè)）能存儲(chǔ)的key的數(shù)量很小相叁，當(dāng)存儲(chǔ)的數(shù)據(jù)量很大時(shí)同樣會(huì)導(dǎo)致B-Tree的深度較大，增大查詢時(shí)的磁盤(pán)I/O次數(shù)，進(jìn)而影響查詢效率增淹。在B+Tree中椿访，所有數(shù)據(jù)記錄節(jié)點(diǎn)都是按照鍵值大小順序存放在同一層的葉子節(jié)點(diǎn)上，而非葉子節(jié)點(diǎn)上只存儲(chǔ)key值信息虑润，這樣可以大大加大每個(gè)節(jié)點(diǎn)存儲(chǔ)的key值數(shù)量赎离，降低B+Tree的高度。
B+Tree相對(duì)于B-Tree有幾點(diǎn)不同：
- 非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值信息端辱；
- 所有葉子節(jié)點(diǎn)之間都有一個(gè)鏈指針梁剔；
- 數(shù)據(jù)記錄都存放在葉子節(jié)點(diǎn)中
將上一節(jié)中的B-Tree優(yōu)化，由于B+Tree的非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值信息舞蔽，假設(shè)每個(gè)磁盤(pán)塊能存儲(chǔ)4個(gè)鍵值及指針信息荣病，則變成B+Tree后其結(jié)構(gòu)如下圖所示：
通常在B+Tree上有兩個(gè)頭指針，一個(gè)指向根節(jié)點(diǎn)渗柿，另一個(gè)指向關(guān)鍵字最小的葉子節(jié)點(diǎn)个盆，而且所有葉子節(jié)點(diǎn)（即數(shù)據(jù)節(jié)點(diǎn)）之間是一種鏈?zhǔn)江h(huán)結(jié)構(gòu)。因此可以對(duì)B+Tree進(jìn)行兩種查找運(yùn)算：一種是對(duì)于主鍵的范圍查找和分頁(yè)查找朵栖，另一種是從根節(jié)點(diǎn)開(kāi)始颊亮，進(jìn)行隨機(jī)查找。
可能上面例子中只有22條數(shù)據(jù)記錄陨溅，看不出B+Tree的優(yōu)點(diǎn)终惑，下面做一個(gè)推算：
InnoDB存儲(chǔ)引擎中頁(yè)的大小為16KB，一般表的主鍵類(lèi)型為INT（占用4個(gè)字節(jié)）或BIGINT（占用8個(gè)字節(jié)）门扇，指針類(lèi)型也一般為4或8個(gè)字節(jié)雹有，也就是說(shuō)一個(gè)頁(yè)（B+Tree中的一個(gè)節(jié)點(diǎn)）中大概存儲(chǔ)16KB/(8B+8B)=1K個(gè)鍵值（因?yàn)槭枪乐担瑸榉奖阌?jì)算臼寄，這里的K取值為103）霸奕。也就是說(shuō)一個(gè)深度為3的B+Tree索引可以維護(hù)103 * 10^3 * 10^3 = 10億 條記錄。
實(shí)際情況中每個(gè)節(jié)點(diǎn)可能不能填充滿吉拳，因此在數(shù)據(jù)庫(kù)中质帅，B+Tree的高度一般都在2-4層。MySQL的InnoDB存儲(chǔ)引擎在設(shè)計(jì)時(shí)是將根節(jié)點(diǎn)常駐內(nèi)存的留攒，也就是說(shuō)查找某一鍵值的行記錄時(shí)最多只需要1~3次磁盤(pán)I/O操作煤惩。
B+Tree性質(zhì)
- 通過(guò)上面的分析，我們知道IO次數(shù)取決于b+數(shù)的高度h稼跳，假設(shè)當(dāng)前數(shù)據(jù)表的數(shù)據(jù)為N盟庞，每個(gè)磁盤(pán)塊的數(shù)據(jù)項(xiàng)的數(shù)量是m吃沪，則有h=㏒(m+1)N汤善，當(dāng)數(shù)據(jù)量N一定的情況下，m越大，h越泻斓不狮；而m = 磁盤(pán)塊的大小 / 數(shù)據(jù)項(xiàng)的大小，磁盤(pán)塊的大小也就是一個(gè)數(shù)據(jù)頁(yè)的大小在旱，是固定的摇零，如果數(shù)據(jù)項(xiàng)占的空間越小，數(shù)據(jù)項(xiàng)的數(shù)量越多桶蝎，樹(shù)的高度越低驻仅。這就是為什么每個(gè)數(shù)據(jù)項(xiàng)，即索引字段要盡量的小登渣，比如int占4字節(jié)噪服，要比bigint8字節(jié)少一半。這也是為什么b+樹(shù)要求把真實(shí)的數(shù)據(jù)放到葉子節(jié)點(diǎn)而不是內(nèi)層節(jié)點(diǎn)胜茧，一旦放到內(nèi)層節(jié)點(diǎn)粘优，磁盤(pán)塊的數(shù)據(jù)項(xiàng)會(huì)大幅度下降，導(dǎo)致樹(shù)增高呻顽。當(dāng)數(shù)據(jù)項(xiàng)等于1時(shí)將會(huì)退化成線性表雹顺。
- 當(dāng)b+樹(shù)的數(shù)據(jù)項(xiàng)是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，比如(name,age,sex)的時(shí)候廊遍，b+數(shù)是按照從左到右的順序來(lái)建立搜索樹(shù)的嬉愧，比如當(dāng)(張三,20,F)這樣的數(shù)據(jù)來(lái)檢索的時(shí)候，b+樹(shù)會(huì)優(yōu)先比較name來(lái)確定下一步的所搜方向喉前，如果name相同再依次比較age和sex英染，最后得到檢索的數(shù)據(jù)；但當(dāng)(20,F)這樣的沒(méi)有name的數(shù)據(jù)來(lái)的時(shí)候被饿，b+樹(shù)就不知道下一步該查哪個(gè)節(jié)點(diǎn)四康，因?yàn)榻⑺阉鳂?shù)的時(shí)候name就是第一個(gè)比較因子，必須要先根據(jù)name來(lái)搜索才能知道下一步去哪里查詢狭握。比如當(dāng)(張三,F)這樣的數(shù)據(jù)來(lái)檢索時(shí)闪金，b+樹(shù)可以用name來(lái)指定搜索方向，但下一個(gè)字段age的缺失论颅，所以只能把名字等于張三的數(shù)據(jù)都找到哎垦，然后再匹配性別是F的數(shù)據(jù)了， 這個(gè)是非常重要的性質(zhì)恃疯，即索引的最左匹配特性漏设。
MyISAM主鍵索引與輔助索引的結(jié)構(gòu)
MyISAM引擎的索引文件和數(shù)據(jù)文件是分離的。MyISAM引擎索引結(jié)構(gòu)的葉子節(jié)點(diǎn)的數(shù)據(jù)域今妄，存放的并不是實(shí)際的數(shù)據(jù)記錄郑口，而是數(shù)據(jù)記錄的地址鸳碧。索引文件與數(shù)據(jù)文件分離，這樣的索引稱為"非聚簇索引"犬性。MyISAM的主索引與輔助索引區(qū)別并不大瞻离，只是主鍵索引不能有重復(fù)的關(guān)鍵字。
在MyISAM中乒裆，索引（含葉子節(jié)點(diǎn)）存放在單獨(dú)的.myi文件中套利，葉子節(jié)點(diǎn)存放的是數(shù)據(jù)的物理地址偏移量（通過(guò)偏移量訪問(wèn)就是隨機(jī)訪問(wèn)，速度很快）鹤耍。
主索引是指主鍵索引肉迫，鍵值不可能重復(fù)；輔助索引則是普通索引稿黄，鍵值可能重復(fù)昂拂。
通過(guò)索引查找數(shù)據(jù)的流程：先從索引文件中查找到索引節(jié)點(diǎn)，從中拿到數(shù)據(jù)的文件指針抛猖，再到數(shù)據(jù)文件中通過(guò)文件指針定位了具體的數(shù)據(jù)格侯。輔助索引類(lèi)似。
InnoDB主鍵索引與輔助索引的結(jié)構(gòu)
InnoDB引擎索引結(jié)構(gòu)的葉子節(jié)點(diǎn)的數(shù)據(jù)域财著，存放的就是實(shí)際的數(shù)據(jù)記錄（對(duì)于主索引联四，此處會(huì)存放表中所有的數(shù)據(jù)記錄；對(duì)于輔助索引此處會(huì)引用主鍵撑教，檢索的時(shí)候通過(guò)主鍵到主鍵索引中找到對(duì)應(yīng)數(shù)據(jù)行）朝墩，或者說(shuō)，InnoDB的數(shù)據(jù)文件本身就是主鍵索引文件伟姐，這樣的索引被稱為“聚簇索引”收苏，一個(gè)表只能有一個(gè)聚簇索引。
主鍵索引：
我們知道InnoDB索引是聚集索引愤兵，它的索引和數(shù)據(jù)是存入同一個(gè).idb文件中的鹿霸，因此它的索引結(jié)構(gòu)是在同一個(gè)樹(shù)節(jié)點(diǎn)中同時(shí)存放索引和數(shù)據(jù)，如下圖中最底層的葉子節(jié)點(diǎn)有三行數(shù)據(jù)秆乳，對(duì)應(yīng)于數(shù)據(jù)表中的id懦鼠、stu_id、name數(shù)據(jù)項(xiàng)屹堰。
在Innodb中肛冶，索引分葉子節(jié)點(diǎn)和非葉子節(jié)點(diǎn)，非葉子節(jié)點(diǎn)就像新華字典的目錄扯键，單獨(dú)存放在索引段中睦袖，葉子節(jié)點(diǎn)則是順序排列的，在數(shù)據(jù)段中荣刑。Innodb的數(shù)據(jù)文件可以按照表來(lái)切分（只需要開(kāi)啟innodb_file_per_table)馅笙，切分后存放在xxx.ibd中伦乔，默認(rèn)不切分，存放在xxx.ibdata中延蟹。
輔助（非主鍵）索引：
這次我們以示例中學(xué)生表中的name列建立輔助索引，它的索引結(jié)構(gòu)跟主鍵索引的結(jié)構(gòu)有很大差別叶堆，在最底層的葉子結(jié)點(diǎn)有兩行數(shù)據(jù)阱飘，第一行的字符串是輔助索引，按照ASCII碼進(jìn)行排序虱颗，第二行的整數(shù)是主鍵的值沥匈。
這就意味著，對(duì)name列進(jìn)行條件搜索忘渔，需要兩個(gè)步驟：
① 在輔助索引上檢索name高帖，到達(dá)其葉子節(jié)點(diǎn)獲取對(duì)應(yīng)的主鍵；
② 使用主鍵在主索引上再進(jìn)行對(duì)應(yīng)的檢索操作
這也就是所謂的“回表查詢”
InnoDB 索引結(jié)構(gòu)需要注意的點(diǎn)
- 數(shù)據(jù)文件本身就是索引文件
- 表數(shù)據(jù)文件本身就是按 B+Tree 組織的一個(gè)索引結(jié)構(gòu)文件
- 聚集索引中葉節(jié)點(diǎn)包含了完整的數(shù)據(jù)記錄
- InnoDB 表必須要有主鍵畦粮，并且推薦使用整型自增主鍵
正如我們上面介紹 InnoDB 存儲(chǔ)結(jié)構(gòu)，索引與數(shù)據(jù)是共同存儲(chǔ)的，不管是主鍵索引還是輔助索引产园，在查找時(shí)都是通過(guò)先查找到索引節(jié)點(diǎn)才能拿到相對(duì)應(yīng)的數(shù)據(jù)侣姆，如果我們?cè)谠O(shè)計(jì)表結(jié)構(gòu)時(shí)沒(méi)有顯式指定索引列的話，MySQL 會(huì)從表中選擇數(shù)據(jù)不重復(fù)的列建立索引儒将，如果沒(méi)有符合的列吏祸，則 MySQL 自動(dòng)為 InnoDB 表生成一個(gè)隱含字段作為主鍵，并且這個(gè)字段長(zhǎng)度為6個(gè)字節(jié)钩蚊，類(lèi)型為整型贡翘。
?
那為什么推薦使用整型自增主鍵而不是選擇UUID？
- UUID是字符串砰逻，比整型消耗更多的存儲(chǔ)空間鸣驱；
- 在B+樹(shù)中進(jìn)行查找時(shí)需要跟經(jīng)過(guò)的節(jié)點(diǎn)值比較大小，整型數(shù)據(jù)的比較運(yùn)算比字符串更快速蝠咆；
- 自增的整型索引在磁盤(pán)中會(huì)連續(xù)存儲(chǔ)丐巫，在讀取一頁(yè)數(shù)據(jù)時(shí)也是連續(xù)；UUID是隨機(jī)產(chǎn)生的勺美，讀取的上下兩行數(shù)據(jù)存儲(chǔ)是分散的递胧，不適合執(zhí)行where id > 5 && id < 20的條件查詢語(yǔ)句。
- 在插入或刪除數(shù)據(jù)時(shí)赡茸，整型自增主鍵會(huì)在葉子結(jié)點(diǎn)的末尾建立新的葉子節(jié)點(diǎn)缎脾，不會(huì)破壞左側(cè)子樹(shù)的結(jié)構(gòu)；UUID主鍵很容易出現(xiàn)這樣的情況占卧，B+樹(shù)為了維持自身的特性遗菠，有可能會(huì)進(jìn)行結(jié)構(gòu)的重構(gòu)联喘，消耗更多的時(shí)間。
?
為什么非主鍵索引結(jié)構(gòu)葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵值辙纬？
保證數(shù)據(jù)一致性和節(jié)省存儲(chǔ)空間豁遭，可以這么理解：商城系統(tǒng)訂單表會(huì)存儲(chǔ)一個(gè)用戶ID作為關(guān)聯(lián)外鍵，而不推薦存儲(chǔ)完整的用戶信息贺拣，因?yàn)楫?dāng)我們用戶表中的信息（真實(shí)名稱蓖谢、手機(jī)號(hào)、收貨地址···）修改后譬涡，不需要再次維護(hù)訂單表的用戶數(shù)據(jù)闪幽，同時(shí)也節(jié)省了存儲(chǔ)空間。
Hash索引
- 主要就是通過(guò)Hash算法（常見(jiàn)的Hash算法有直接定址法涡匀、平方取中法盯腌、折疊法、除數(shù)取余法陨瘩、隨機(jī)數(shù)法）腕够，將數(shù)據(jù)庫(kù)字段數(shù)據(jù)轉(zhuǎn)換成定長(zhǎng)的Hash值，與這條數(shù)據(jù)的行指針一并存入Hash表的對(duì)應(yīng)位置舌劳；如果發(fā)生Hash碰撞（兩個(gè)不同關(guān)鍵字的Hash值相同）燕少，則在對(duì)應(yīng)Hash鍵下以鏈表形式存儲(chǔ)。檢索算法：在檢索查詢時(shí)蒿囤，就再次對(duì)待查關(guān)鍵字再次執(zhí)行相同的Hash算法客们，得到Hash值，到對(duì)應(yīng)Hash表對(duì)應(yīng)位置取出數(shù)據(jù)即可材诽，如果發(fā)生Hash碰撞底挫，則需要在取值時(shí)進(jìn)行篩選。目前使用Hash索引的數(shù)據(jù)庫(kù)并不多脸侥，主要有Memory等建邓。MySQL目前有Memory引擎和NDB引擎支持Hash索引。
full-text全文索引
- 全文索引也是MyISAM的一種特殊索引類(lèi)型睁枕，主要用于全文索引官边，InnoDB從MYSQL5.6版本提供對(duì)全文索引的支持。
- 它用于替代效率較低的LIKE模糊匹配操作外遇，而且可以通過(guò)多字段組合的全文索引一次性全模糊匹配多個(gè)字段注簿。
- 同樣使用B-Tree存放索引數(shù)據(jù)，但使用的是特定的算法跳仿，將字段數(shù)據(jù)分割后再進(jìn)行索引（一般每4個(gè)字節(jié)一次分割）诡渴，索引文件存儲(chǔ)的是分割前的索引字符串集合，與分割后的索引信息菲语，對(duì)應(yīng)Btree結(jié)構(gòu)的節(jié)點(diǎn)存儲(chǔ)的是分割后的詞信息以及它在分割前的索引字符串集合中的位置妄辩。
R-Tree空間索引
空間索引是MyISAM的一種特殊索引類(lèi)型惑灵，主要用于地理空間數(shù)據(jù)類(lèi)型
?
為什么Mysql索引要用B+樹(shù)不是B樹(shù)？
用B+樹(shù)不用B樹(shù)考慮的是IO對(duì)性能的影響眼耀，B樹(shù)的每個(gè)節(jié)點(diǎn)都存儲(chǔ)數(shù)據(jù)英支，而B(niǎo)+樹(shù)只有葉子節(jié)點(diǎn)才存儲(chǔ)數(shù)據(jù)，所以查找相同數(shù)據(jù)量的情況下哮伟，B樹(shù)的高度更高干花，IO更頻繁。數(shù)據(jù)庫(kù)索引是存儲(chǔ)在磁盤(pán)上的澈吨，當(dāng)數(shù)據(jù)量大時(shí)把敢，就不能把整個(gè)索引全部加載到內(nèi)存了寄摆，只能逐一加載每一個(gè)磁盤(pán)頁(yè)（對(duì)應(yīng)索引樹(shù)的節(jié)點(diǎn)）谅辣。其中在MySQL底層對(duì)B+樹(shù)進(jìn)行進(jìn)一步優(yōu)化：在葉子節(jié)點(diǎn)中是雙向鏈表，且在鏈表的頭結(jié)點(diǎn)和尾節(jié)點(diǎn)也是循環(huán)指向的婶恼。
?
面試官：為何不采用Hash方式桑阶？
因?yàn)镠ash索引底層是哈希表，哈希表是一種以key-value存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)勾邦，所以多個(gè)數(shù)據(jù)在存儲(chǔ)關(guān)系上是完全沒(méi)有任何順序關(guān)系的蚣录，所以，對(duì)于區(qū)間查詢是無(wú)法直接通過(guò)索引查詢的眷篇，就需要全表掃描萎河。所以，哈希索引只適用于等值查詢的場(chǎng)景蕉饼。而B(niǎo)+ Tree是一種多路平衡查詢樹(shù)虐杯，所以他的節(jié)點(diǎn)是天然有序的（左子節(jié)點(diǎn)小于父節(jié)點(diǎn)、父節(jié)點(diǎn)小于右子節(jié)點(diǎn)）昧港，所以對(duì)于范圍查詢的時(shí)候不需要做全表掃描擎椰。
哈希索引不支持多列聯(lián)合索引的最左匹配規(guī)則，如果有大量重復(fù)鍵值得情況下创肥，哈希索引的效率會(huì)很低达舒，因?yàn)榇嬖诠Ｅ鲎矄?wèn)題。
哪些情況需要?jiǎng)?chuàng)建索引
- 主鍵自動(dòng)建立唯一索引
- 頻繁作為查詢條件的字段
- 查詢中與其他表關(guān)聯(lián)的字段叹侄，外鍵關(guān)系建立索引
- 單鍵/組合索引的選擇問(wèn)題巩搏，高并發(fā)下傾向創(chuàng)建組合索引
- 查詢中排序的字段，排序字段通過(guò)索引訪問(wèn)大幅提高排序速度
- 查詢中統(tǒng)計(jì)或分組字段
哪些情況不要?jiǎng)?chuàng)建索引
- 表記錄太少
- 經(jīng)常增刪改的表
- 數(shù)據(jù)重復(fù)且分布均勻的表字段趾代，只應(yīng)該為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引（如果某個(gè)數(shù)據(jù)類(lèi)包含太多的重復(fù)數(shù)據(jù)塔猾，建立索引沒(méi)有太大意義）
- 頻繁更新的字段不適合創(chuàng)建索引（會(huì)加重IO負(fù)擔(dān)）
- where條件里用不到的字段不創(chuàng)建索引
MySQL高效索引
覆蓋索引（Covering Index）,或者叫索引覆蓋， 也就是平時(shí)所說(shuō)的不需要回表操作
- 就是select的數(shù)據(jù)列只用從索引中就能夠取得稽坤，不必讀取數(shù)據(jù)行丈甸，MySQL可以利用索引返回select列表中的字段糯俗，而不必根據(jù)索引再次讀取數(shù)據(jù)文件，換句話說(shuō)查詢列要被所建的索引覆蓋睦擂。
- 索引是高效找到行的一個(gè)方法得湘，但是一般數(shù)據(jù)庫(kù)也能使用索引找到一個(gè)列的數(shù)據(jù)，因此它不必讀取整個(gè)行顿仇。畢竟索引葉子節(jié)點(diǎn)存儲(chǔ)了它們索引的數(shù)據(jù)淘正，當(dāng)能通過(guò)讀取索引就可以得到想要的數(shù)據(jù)，那就不需要讀取行了臼闻。一個(gè)索引包含（覆蓋）滿足查詢結(jié)果的數(shù)據(jù)就叫做覆蓋索引鸿吆。
- 判斷標(biāo)準(zhǔn)使用explain，可以通過(guò)輸出的extra列來(lái)判斷述呐，對(duì)于一個(gè)索引覆蓋查詢惩淳，顯示為using index，MySQL查詢優(yōu)化器在執(zhí)行查詢前會(huì)決定是否有索引覆蓋查詢
五乓搬、MySQL查詢
?
count(*) 和 count(1)和count(列名)區(qū)別 ps：這道題說(shuō)法有點(diǎn)多
執(zhí)行效果上：
- count(*)包括了所有的列思犁，相當(dāng)于行數(shù)，在統(tǒng)計(jì)結(jié)果的時(shí)候进肯，不會(huì)忽略列值為NULL
- count(1)包括了所有列激蹲，用1代表代碼行，在統(tǒng)計(jì)結(jié)果的時(shí)候江掩，不會(huì)忽略列值為NULL
- count(列名)只包括列名那一列学辱，在統(tǒng)計(jì)結(jié)果的時(shí)候，會(huì)忽略列值為空（這里的空不是只空字符串或者0环形，而是表示null）的計(jì)數(shù)策泣，即某個(gè)字段值為NULL時(shí)，不統(tǒng)計(jì)斟赚。
執(zhí)行效率上：
- 列名為主鍵着降，count(列名)會(huì)比count(1)快
- 列名不為主鍵，count(1)會(huì)比count(列名)快
- 如果表多個(gè)列并且沒(méi)有主鍵拗军，則 count(1) 的執(zhí)行效率優(yōu)于 count(*)
- 如果有主鍵任洞，則 select count（主鍵）的執(zhí)行效率是最優(yōu)的
- 如果表只有一個(gè)字段，則 select count(*) 最優(yōu)发侵。
?
MySQL中 in和 exists 的區(qū)別交掏？
- exists：exists對(duì)外表用loop逐條查詢，每次查詢都會(huì)查看exists的條件語(yǔ)句刃鳄，當(dāng)exists里的條件語(yǔ)句能夠返回記錄行時(shí)（無(wú)論記錄行是的多少盅弛，只要能返回），條件就為真，返回當(dāng)前l(fā)oop到的這條記錄挪鹏；反之见秽，如果exists里的條件語(yǔ)句不能返回記錄行，則當(dāng)前l(fā)oop到的這條記錄被丟棄讨盒，exists的條件就像一個(gè)bool條件解取，當(dāng)能返回結(jié)果集則為true，不能返回結(jié)果集則為false
- in：in查詢相當(dāng)于多個(gè)or條件的疊加
SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);
如果查詢的兩個(gè)表大小相當(dāng)返顺，那么用in和exists差別不大禀苦。
如果兩個(gè)表中一個(gè)較小，一個(gè)是大表遂鹊，則子查詢表大的用exists振乏，子查詢表小的用in：
?
UNION和UNION ALL的區(qū)別?
UNION和UNION ALL都是將兩個(gè)結(jié)果集合并為一個(gè)，兩個(gè)要聯(lián)合的SQL語(yǔ)句 字段個(gè)數(shù)必須一樣秉扑，而且字段類(lèi)型要“相容”（一致）慧邮；
- UNION在進(jìn)行表連接后會(huì)篩選掉重復(fù)的數(shù)據(jù)記錄（效率較低），而UNION ALL則不會(huì)去掉重復(fù)的數(shù)據(jù)記錄邻储；
- UNION會(huì)按照字段的順序進(jìn)行排序赋咽，而UNION ALL只是簡(jiǎn)單的將兩個(gè)結(jié)果合并就返回旧噪；
SQL執(zhí)行順序
- 手寫(xiě)SELECT DISTINCT <select_list>
FROM <left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number> - 機(jī)讀FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number> - 總結(jié)
?
mysql 的內(nèi)連接吨娜、左連接、右連接有什么區(qū)別淘钟？
什么是內(nèi)連接宦赠、外連接、交叉連接米母、笛卡爾積呢勾扭？
Join圖
六、MySQL 事務(wù)
?
事務(wù)的隔離級(jí)別有哪些铁瞒？MySQL的默認(rèn)隔離級(jí)別是什么妙色？
什么是幻讀，臟讀慧耍，不可重復(fù)讀呢身辨？
MySQL事務(wù)的四大特性以及實(shí)現(xiàn)原理
MVCC熟悉嗎，它的底層原理芍碧？
MySQL 事務(wù)主要用于處理操作量大煌珊，復(fù)雜度高的數(shù)據(jù)。比如說(shuō)泌豆，在人員管理系統(tǒng)中定庵，你刪除一個(gè)人員，你即需要?jiǎng)h除人員的基本資料，也要?jiǎng)h除和該人員相關(guān)的信息蔬浙，如信箱猪落，文章等等，這樣畴博，這些數(shù)據(jù)庫(kù)操作語(yǔ)句就構(gòu)成一個(gè)事務(wù)许布！
ACID — 事務(wù)基本要素
事務(wù)是由一組SQL語(yǔ)句組成的邏輯處理單元，具有4個(gè)屬性绎晃，通常簡(jiǎn)稱為事務(wù)的ACID屬性蜜唾。
- A (Atomicity) 原子性：整個(gè)事務(wù)中的所有操作，要么全部完成庶艾，要么全部不完成袁余，不可能停滯在中間某個(gè)環(huán)節(jié)。事務(wù)在執(zhí)行過(guò)程中發(fā)生錯(cuò)誤咱揍，會(huì)被回滾（Rollback）到事務(wù)開(kāi)始前的狀態(tài)颖榜，就像這個(gè)事務(wù)從來(lái)沒(méi)有執(zhí)行過(guò)一樣
- C (Consistency) 一致性：在事務(wù)開(kāi)始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫(kù)的完整性約束沒(méi)有被破壞
- I (Isolation)隔離性：一個(gè)事務(wù)的執(zhí)行不能其它事務(wù)干擾煤裙。即一個(gè)事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對(duì)其它并發(fā)事務(wù)是隔離的掩完，并發(fā)執(zhí)行的各個(gè)事務(wù)之間不能互相干擾
- D (Durability) 持久性：在事務(wù)完成以后，該事務(wù)所對(duì)數(shù)據(jù)庫(kù)所作的更改便持久的保存在數(shù)據(jù)庫(kù)之中硼砰，并不會(huì)被回滾
并發(fā)事務(wù)處理帶來(lái)的問(wèn)題
- 更新丟失（Lost Update)：事務(wù)A和事務(wù)B選擇同一行且蓬，然后基于最初選定的值更新該行時(shí)，由于兩個(gè)事務(wù)都不知道彼此的存在题翰，就會(huì)發(fā)生丟失更新問(wèn)題
- 臟讀(Dirty Reads)：事務(wù)A讀取了事務(wù)B更新的數(shù)據(jù)恶阴，然后B回滾操作，那么A讀取到的數(shù)據(jù)是臟數(shù)據(jù)
- 不可重復(fù)讀（Non-Repeatable Reads)：事務(wù) A 多次讀取同一數(shù)據(jù)豹障，事務(wù)B在事務(wù)A多次讀取的過(guò)程中冯事，對(duì)數(shù)據(jù)作了更新并提交，導(dǎo)致事務(wù)A多次讀取同一數(shù)據(jù)時(shí)血公，結(jié)果不一致昵仅。
- 幻讀（Phantom Reads)：幻讀與不可重復(fù)讀類(lèi)似。它發(fā)生在一個(gè)事務(wù)A讀取了幾行數(shù)據(jù)累魔，接著另一個(gè)并發(fā)事務(wù)B插入了一些數(shù)據(jù)時(shí)摔笤。在隨后的查詢中，事務(wù)A就會(huì)發(fā)現(xiàn)多了一些原本不存在的記錄薛夜，就好像發(fā)生了幻覺(jué)一樣籍茧，所以稱為幻讀。
幻讀和不可重復(fù)讀的區(qū)別：
- 不可重復(fù)讀的重點(diǎn)是修改：在同一事務(wù)中梯澜，同樣的條件寞冯，第一次讀的數(shù)據(jù)和第二次讀的數(shù)據(jù)不一樣渴析。（因?yàn)橹虚g有其他事務(wù)提交了修改）
- 幻讀的重點(diǎn)在于新增或者刪除：在同一事務(wù)中，同樣的條件,吮龄，第一次和第二次讀出來(lái)的記錄數(shù)不一樣俭茧。（因?yàn)橹虚g有其他事務(wù)提交了插入/刪除）
并發(fā)事務(wù)處理帶來(lái)的問(wèn)題的解決辦法：
“更新丟失”通常是應(yīng)該完全避免的。但防止更新丟失漓帚，并不能單靠數(shù)據(jù)庫(kù)事務(wù)控制器來(lái)解決母债，需要應(yīng)用程序?qū)σ碌臄?shù)據(jù)加必要的鎖來(lái)解決，因此尝抖，防止更新丟失應(yīng)該是應(yīng)用的責(zé)任毡们。
“臟讀” 、 “不可重復(fù)讀”和“幻讀” 昧辽，其實(shí)都是數(shù)據(jù)庫(kù)讀一致性問(wèn)題衙熔，必須由數(shù)據(jù)庫(kù)提供一定的事務(wù)隔離機(jī)制來(lái)解決：
一種是加鎖：在讀取數(shù)據(jù)前，對(duì)其加鎖搅荞，阻止其他事務(wù)對(duì)數(shù)據(jù)進(jìn)行修改红氯。
另一種是數(shù)據(jù)多版本并發(fā)控制（MultiVersion Concurrency Control，簡(jiǎn)稱 MVCC 或 MCC）咕痛，也稱為多版本數(shù)據(jù)庫(kù)：不用加任何鎖痢甘， 通過(guò)一定機(jī)制生成一個(gè)數(shù)據(jù)請(qǐng)求時(shí)間點(diǎn)的一致性數(shù)據(jù)快照 （Snapshot)， 并用這個(gè)快照來(lái)提供一定級(jí)別 （語(yǔ)句級(jí)或事務(wù)級(jí)） 的一致性讀取茉贡。從用戶的角度來(lái)看塞栅，好象是數(shù)據(jù)庫(kù)可以提供同一數(shù)據(jù)的多個(gè)版本。
事務(wù)隔離級(jí)別
數(shù)據(jù)庫(kù)事務(wù)的隔離級(jí)別有4種块仆，由低到高分別為
- READ-UNCOMMITTED(讀未提交)： 最低的隔離級(jí)別构蹬，允許讀取尚未提交的數(shù)據(jù)變更王暗，可能會(huì)導(dǎo)致臟讀悔据、幻讀或不可重復(fù)讀。
- READ-COMMITTED(讀已提交)： 允許讀取并發(fā)事務(wù)已經(jīng)提交的數(shù)據(jù)俗壹，可以阻止臟讀科汗，但是幻讀或不可重復(fù)讀仍有可能發(fā)生。
- REPEATABLE-READ(可重復(fù)讀)： 對(duì)同一字段的多次讀取結(jié)果都是一致的绷雏，除非數(shù)據(jù)是被本身事務(wù)自己所修改头滔，可以阻止臟讀和不可重復(fù)讀，但幻讀仍有可能發(fā)生难咕。
- SERIALIZABLE(可串行化)： 最高的隔離級(jí)別五督，完全服從ACID的隔離級(jí)別稀蟋。所有的事務(wù)依次逐個(gè)執(zhí)行，這樣事務(wù)之間就完全不可能產(chǎn)生干擾早歇，也就是說(shuō)，該級(jí)別可以防止臟讀、不可重復(fù)讀以及幻讀箭跳。
查看當(dāng)前數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別：
show variables like 'tx_isolation'
下面通過(guò)事例一一闡述在事務(wù)的并發(fā)操作中可能會(huì)出現(xiàn)臟讀晨另，不可重復(fù)讀，幻讀和事務(wù)隔離級(jí)別的聯(lián)系谱姓。
數(shù)據(jù)庫(kù)的事務(wù)隔離越嚴(yán)格借尿，并發(fā)副作用越小，但付出的代價(jià)就越大屉来，因?yàn)槭聞?wù)隔離實(shí)質(zhì)上就是使事務(wù)在一定程度上“串行化”進(jìn)行路翻，這顯然與“并發(fā)”是矛盾的。同時(shí)茄靠，不同的應(yīng)用對(duì)讀一致性和事務(wù)隔離程度的要求也是不同的帚桩，比如許多應(yīng)用對(duì)“不可重復(fù)讀”和“幻讀”并不敏感，可能更關(guān)心數(shù)據(jù)并發(fā)訪問(wèn)的能力嘹黔。
Read uncommitted
讀未提交账嚎，就是一個(gè)事務(wù)可以讀取另一個(gè)未提交事務(wù)的數(shù)據(jù)。
事例：老板要給程序員發(fā)工資儡蔓，程序員的工資是3.6萬(wàn)/月郭蕉。但是發(fā)工資時(shí)老板不小心按錯(cuò)了數(shù)字，按成3.9萬(wàn)/月喂江，該錢(qián)已經(jīng)打到程序員的戶口召锈，但是事務(wù)還沒(méi)有提交，就在這時(shí)获询，程序員去查看自己這個(gè)月的工資涨岁，發(fā)現(xiàn)比往常多了3千元，以為漲工資了非常高興吉嚣。但是老板及時(shí)發(fā)現(xiàn)了不對(duì)梢薪，馬上回滾差點(diǎn)就提交了的事務(wù)，將數(shù)字改成3.6萬(wàn)再提交尝哆。
分析：實(shí)際程序員這個(gè)月的工資還是3.6萬(wàn)秉撇，但是程序員看到的是3.9萬(wàn)。他看到的是老板還沒(méi)提交事務(wù)時(shí)的數(shù)據(jù)秋泄。這就是臟讀琐馆。
那怎么解決臟讀呢？Read committed恒序！讀提交瘦麸，能解決臟讀問(wèn)題。
Read committed
讀提交歧胁，顧名思義滋饲，就是一個(gè)事務(wù)要等另一個(gè)事務(wù)提交后才能讀取數(shù)據(jù)彤敛。
事例：程序員拿著信用卡去享受生活（卡里當(dāng)然是只有3.6萬(wàn)），當(dāng)他埋單時(shí)（程序員事務(wù)開(kāi)啟）了赌，收費(fèi)系統(tǒng)事先檢測(cè)到他的卡里有3.6萬(wàn)墨榄，就在這個(gè)時(shí)候！勿她！程序員的妻子要把錢(qián)全部轉(zhuǎn)出充當(dāng)家用袄秩，并提交。當(dāng)收費(fèi)系統(tǒng)準(zhǔn)備扣款時(shí)逢并，再檢測(cè)卡里的金額之剧，發(fā)現(xiàn)已經(jīng)沒(méi)錢(qián)了（第二次檢測(cè)金額當(dāng)然要等待妻子轉(zhuǎn)出金額事務(wù)提交完）。程序員就會(huì)很郁悶砍聊，明明卡里是有錢(qián)的…
分析：這就是讀提交背稼，若有事務(wù)對(duì)數(shù)據(jù)進(jìn)行更新（UPDATE）操作時(shí)，讀操作事務(wù)要等待這個(gè)更新操作事務(wù)提交后才能讀取數(shù)據(jù)玻蝌，可以解決臟讀問(wèn)題蟹肘。但在這個(gè)事例中，出現(xiàn)了一個(gè)事務(wù)范圍內(nèi)兩個(gè)相同的查詢卻返回了不同數(shù)據(jù)俯树，這就是不可重復(fù)讀帘腹。
那怎么解決可能的不可重復(fù)讀問(wèn)題？Repeatable read 许饿！
Repeatable read
重復(fù)讀阳欲，就是在開(kāi)始讀取數(shù)據(jù)（事務(wù)開(kāi)啟）時(shí)，不再允許修改操作陋率。MySQL的默認(rèn)事務(wù)隔離級(jí)別
事例：程序員拿著信用卡去享受生活（卡里當(dāng)然是只有3.6萬(wàn)）球化，當(dāng)他埋單時(shí)（事務(wù)開(kāi)啟，不允許其他事務(wù)的UPDATE修改操作）瓦糟，收費(fèi)系統(tǒng)事先檢測(cè)到他的卡里有3.6萬(wàn)筒愚。這個(gè)時(shí)候他的妻子不能轉(zhuǎn)出金額了。接下來(lái)收費(fèi)系統(tǒng)就可以扣款了狸页。
分析：重復(fù)讀可以解決不可重復(fù)讀問(wèn)題锨能。寫(xiě)到這里，應(yīng)該明白的一點(diǎn)就是芍耘，不可重復(fù)讀對(duì)應(yīng)的是修改，即UPDATE操作熄阻。但是可能還會(huì)有幻讀問(wèn)題斋竞。因?yàn)榛米x問(wèn)題對(duì)應(yīng)的是插入INSERT操作，而不是UPDATE操作秃殉。
什么時(shí)候會(huì)出現(xiàn)幻讀坝初？
事例：程序員某一天去消費(fèi)浸剩，花了2千元，然后他的妻子去查看他今天的消費(fèi)記錄（全表掃描FTS鳄袍，妻子事務(wù)開(kāi)啟）绢要，看到確實(shí)是花了2千元，就在這個(gè)時(shí)候拗小，程序員花了1萬(wàn)買(mǎi)了一部電腦重罪，即新增INSERT了一條消費(fèi)記錄，并提交哀九。當(dāng)妻子打印程序員的消費(fèi)記錄清單時(shí)（妻子事務(wù)提交）剿配，發(fā)現(xiàn)花了1.2萬(wàn)元，似乎出現(xiàn)了幻覺(jué)阅束，這就是幻讀呼胚。
那怎么解決幻讀問(wèn)題？Serializable息裸！
Serializable 序列化
Serializable 是最高的事務(wù)隔離級(jí)別蝇更，在該級(jí)別下，事務(wù)串行化順序執(zhí)行呼盆，可以避免臟讀簿寂、不可重復(fù)讀與幻讀。簡(jiǎn)單來(lái)說(shuō)宿亡，Serializable會(huì)在讀取的每一行數(shù)據(jù)上都加鎖常遂，所以可能導(dǎo)致大量的超時(shí)和鎖爭(zhēng)用問(wèn)題。這種事務(wù)隔離級(jí)別效率低下挽荠，比較耗數(shù)據(jù)庫(kù)性能克胳，一般不使用。
比較
事務(wù)隔離級(jí)別讀數(shù)據(jù)一致性臟讀不可重復(fù)讀幻讀讀未提交（read-uncommitted）最低級(jí)被圈匆，只能保證不讀取物理上損壞的數(shù)據(jù)是是是讀已提交（read-committed）語(yǔ)句級(jí)否是是可重復(fù)讀（repeatable-read）事務(wù)級(jí)否否是串行化（serializable）最高級(jí)別漠另，事務(wù)級(jí)否否否
需要說(shuō)明的是，事務(wù)隔離級(jí)別和數(shù)據(jù)訪問(wèn)的并發(fā)性是對(duì)立的跃赚，事務(wù)隔離級(jí)別越高并發(fā)性就越差笆搓。所以要根據(jù)具體的應(yīng)用來(lái)確定合適的事務(wù)隔離級(jí)別，這個(gè)地方?jīng)]有萬(wàn)能的原則纬傲。
MySQL InnoDB 存儲(chǔ)引擎的默認(rèn)支持的隔離級(jí)別是 REPEATABLE-READ（可重讀）满败。我們可以通過(guò)SELECT @@tx_isolation;命令來(lái)查看，MySQL 8.0 該命令改為SELECT @@transaction_isolation;
這里需要注意的是：與 SQL 標(biāo)準(zhǔn)不同的地方在于InnoDB 存儲(chǔ)引擎在 REPEATABLE-READ（可重讀）事務(wù)隔離級(jí)別下使用的是Next-Key Lock 算法叹括，因此可以避免幻讀的產(chǎn)生算墨，這與其他數(shù)據(jù)庫(kù)系統(tǒng)(如 SQL Server)是不同的。所以說(shuō)InnoDB 存儲(chǔ)引擎的默認(rèn)支持的隔離級(jí)別是 REPEATABLE-READ（可重讀）已經(jīng)可以完全保證事務(wù)的隔離性要求汁雷，即達(dá)到了 SQL標(biāo)準(zhǔn)的 SERIALIZABLE(可串行化)隔離級(jí)別净嘀，而且保留了比較好的并發(fā)性能报咳。
因?yàn)楦綦x級(jí)別越低，事務(wù)請(qǐng)求的鎖越少挖藏，所以大部分?jǐn)?shù)據(jù)庫(kù)系統(tǒng)的隔離級(jí)別都是READ-COMMITTED(讀已提交):暑刃，但是你要知道的是InnoDB 存儲(chǔ)引擎默認(rèn)使用 REPEATABLE-READ（可重讀）并不會(huì)有任何性能損失。
MVCC 多版本并發(fā)控制
MySQL的大多數(shù)事務(wù)型存儲(chǔ)引擎實(shí)現(xiàn)都不是簡(jiǎn)單的行級(jí)鎖膜眠⊙页迹基于提升并發(fā)性考慮，一般都同時(shí)實(shí)現(xiàn)了多版本并發(fā)控制（MVCC）柴底，包括Oracle婿脸、PostgreSQL。只是實(shí)現(xiàn)機(jī)制各不相同柄驻。
可以認(rèn)為 MVCC 是行級(jí)鎖的一個(gè)變種狐树，但它在很多情況下避免了加鎖操作，因此開(kāi)銷(xiāo)更低鸿脓。雖然實(shí)現(xiàn)機(jī)制有所不同抑钟，但大都實(shí)現(xiàn)了非阻塞的讀操作，寫(xiě)操作也只是鎖定必要的行野哭。
MVCC 的實(shí)現(xiàn)是通過(guò)保存數(shù)據(jù)在某個(gè)時(shí)間點(diǎn)的快照來(lái)實(shí)現(xiàn)的在塔。也就是說(shuō)不管需要執(zhí)行多長(zhǎng)時(shí)間，每個(gè)事物看到的數(shù)據(jù)都是一致的拨黔。
典型的MVCC實(shí)現(xiàn)方式蛔溃，分為樂(lè)觀（optimistic）并發(fā)控制和悲觀（pressimistic）并發(fā)控制。下邊通過(guò) InnoDB的簡(jiǎn)化版行為來(lái)說(shuō)明 MVCC 是如何工作的篱蝇。
InnoDB 的 MVCC贺待，是通過(guò)在每行記錄后面保存兩個(gè)隱藏的列來(lái)實(shí)現(xiàn)。這兩個(gè)列零截，一個(gè)保存了行的創(chuàng)建時(shí)間麸塞，一個(gè)保存行的過(guò)期時(shí)間（刪除時(shí)間）。當(dāng)然存儲(chǔ)的并不是真實(shí)的時(shí)間涧衙，而是系統(tǒng)版本號(hào)（system version number）哪工。每開(kāi)始一個(gè)新的事務(wù)，系統(tǒng)版本號(hào)都會(huì)自動(dòng)遞增弧哎。事務(wù)開(kāi)始時(shí)刻的系統(tǒng)版本號(hào)會(huì)作為事務(wù)的版本號(hào)雁比，用來(lái)和查詢到的每行記錄的版本號(hào)進(jìn)行比較。
REPEATABLE READ（可重讀）隔離級(jí)別下MVCC如何工作：
SELECTInnoDB會(huì)根據(jù)以下兩個(gè)條件檢查每行記錄：只有符合上述兩個(gè)條件的才會(huì)被查詢出來(lái)
InnoDB只查找版本早于當(dāng)前事務(wù)版本的數(shù)據(jù)行傻铣，這樣可以確保事務(wù)讀取的行章贞，要么是在開(kāi)始事務(wù)之前已經(jīng)存在要么是事務(wù)自身插入或者修改過(guò)的
行的刪除版本號(hào)要么未定義，要么大于當(dāng)前事務(wù)版本號(hào)非洲，這樣可以確保事務(wù)讀取到的行在事務(wù)開(kāi)始之前未被刪除
INSERT：InnoDB為新插入的每一行保存當(dāng)前系統(tǒng)版本號(hào)作為行版本號(hào)
DELETE：InnoDB為刪除的每一行保存當(dāng)前系統(tǒng)版本號(hào)作為行刪除標(biāo)識(shí)
UPDATE：InnoDB為插入的一行新紀(jì)錄保存當(dāng)前系統(tǒng)版本號(hào)作為行版本號(hào)鸭限，同時(shí)保存當(dāng)前系統(tǒng)版本號(hào)到原來(lái)的行作為刪除標(biāo)識(shí)
保存這兩個(gè)額外系統(tǒng)版本號(hào)，使大多數(shù)操作都不用加鎖两踏。使數(shù)據(jù)操作簡(jiǎn)單败京，性能很好，并且也能保證只會(huì)讀取到符合要求的行梦染。不足之處是每行記錄都需要額外的存儲(chǔ)空間赡麦，需要做更多的行檢查工作和一些額外的維護(hù)工作。
MVCC 只在 COMMITTED READ（讀提交）和REPEATABLE READ（可重復(fù)讀）兩種隔離級(jí)別下工作帕识。
事務(wù)日志
InnoDB 使用日志來(lái)減少提交事務(wù)時(shí)的開(kāi)銷(xiāo)泛粹。因?yàn)槿罩局幸呀?jīng)記錄了事務(wù)，就無(wú)須在每個(gè)事務(wù)提交時(shí)把緩沖池的臟塊刷新(flush)到磁盤(pán)中肮疗。
事務(wù)修改的數(shù)據(jù)和索引通常會(huì)映射到表空間的隨機(jī)位置晶姊，所以刷新這些變更到磁盤(pán)需要很多隨機(jī) IO。
InnoDB 假設(shè)使用常規(guī)磁盤(pán)伪货，隨機(jī)IO比順序IO昂貴得多们衙，因?yàn)橐粋€(gè)IO請(qǐng)求需要時(shí)間把磁頭移到正確的位置，然后等待磁盤(pán)上讀出需要的部分碱呼，再轉(zhuǎn)到開(kāi)始位置蒙挑。
InnoDB 用日志把隨機(jī)IO變成順序IO。一旦日志安全寫(xiě)到磁盤(pán)愚臀，事務(wù)就持久化了忆蚀，即使斷電了，InnoDB可以重放日志并且恢復(fù)已經(jīng)提交的事務(wù)姑裂。
InnoDB 使用一個(gè)后臺(tái)線程智能地刷新這些變更到數(shù)據(jù)文件馋袜。這個(gè)線程可以批量組合寫(xiě)入，使得數(shù)據(jù)寫(xiě)入更順序炭分，以提高效率桃焕。
事務(wù)日志可以幫助提高事務(wù)效率：
- 使用事務(wù)日志，存儲(chǔ)引擎在修改表的數(shù)據(jù)時(shí)只需要修改其內(nèi)存拷貝捧毛，再把該修改行為記錄到持久在硬盤(pán)上的事務(wù)日志中观堂，而不用每次都將修改的數(shù)據(jù)本身持久到磁盤(pán)。
- 事務(wù)日志采用的是追加的方式呀忧，因此寫(xiě)日志的操作是磁盤(pán)上一小塊區(qū)域內(nèi)的順序I/O师痕，而不像隨機(jī)I/O需要在磁盤(pán)的多個(gè)地方移動(dòng)磁頭，所以采用事務(wù)日志的方式相對(duì)來(lái)說(shuō)要快得多而账。
- 事務(wù)日志持久以后胰坟，內(nèi)存中被修改的數(shù)據(jù)在后臺(tái)可以慢慢刷回到磁盤(pán)。
- 如果數(shù)據(jù)的修改已經(jīng)記錄到事務(wù)日志并持久化泞辐，但數(shù)據(jù)本身沒(méi)有寫(xiě)回到磁盤(pán)笔横，此時(shí)系統(tǒng)崩潰竞滓，存儲(chǔ)引擎在重啟時(shí)能夠自動(dòng)恢復(fù)這一部分修改的數(shù)據(jù)。
目前來(lái)說(shuō)吹缔，大多數(shù)存儲(chǔ)引擎都是這樣實(shí)現(xiàn)的商佑，我們通常稱之為預(yù)寫(xiě)式日志（Write-Ahead Logging），修改數(shù)據(jù)需要寫(xiě)兩次磁盤(pán)厢塘。
事務(wù)的實(shí)現(xiàn)
事務(wù)的實(shí)現(xiàn)是基于數(shù)據(jù)庫(kù)的存儲(chǔ)引擎茶没。不同的存儲(chǔ)引擎對(duì)事務(wù)的支持程度不一樣。MySQL 中支持事務(wù)的存儲(chǔ)引擎有 InnoDB 和 NDB晚碾。
事務(wù)的實(shí)現(xiàn)就是如何實(shí)現(xiàn)ACID特性抓半。
事務(wù)的隔離性是通過(guò)鎖實(shí)現(xiàn)，而事務(wù)的原子性格嘁、一致性和持久性則是通過(guò)事務(wù)日志實(shí)現(xiàn) 笛求。
?
事務(wù)是如何通過(guò)日志來(lái)實(shí)現(xiàn)的，說(shuō)得越深入越好讥蔽。
事務(wù)日志包括：重做日志redo和回滾日志undo
- redo log（重做日志） 實(shí)現(xiàn)持久化和原子性在innoDB的存儲(chǔ)引擎中涣易，事務(wù)日志通過(guò)重做(redo)日志和innoDB存儲(chǔ)引擎的日志緩沖(InnoDB Log Buffer)實(shí)現(xiàn)。事務(wù)開(kāi)啟時(shí)冶伞，事務(wù)中的操作新症，都會(huì)先寫(xiě)入存儲(chǔ)引擎的日志緩沖中，在事務(wù)提交之前响禽，這些緩沖的日志都需要提前刷新到磁盤(pán)上持久化徒爹，這就是DBA們口中常說(shuō)的“日志先行”(Write-Ahead Logging)。當(dāng)事務(wù)提交之后芋类，在Buffer Pool中映射的數(shù)據(jù)文件才會(huì)慢慢刷新到磁盤(pán)隆嗅。此時(shí)如果數(shù)據(jù)庫(kù)崩潰或者宕機(jī)，那么當(dāng)系統(tǒng)重啟進(jìn)行恢復(fù)時(shí)侯繁，就可以根據(jù)redo log中記錄的日志胖喳，把數(shù)據(jù)庫(kù)恢復(fù)到崩潰前的一個(gè)狀態(tài)。未完成的事務(wù)贮竟，可以繼續(xù)提交丽焊，也可以選擇回滾，這基于恢復(fù)的策略而定咕别。在系統(tǒng)啟動(dòng)的時(shí)候技健，就已經(jīng)為redo log分配了一塊連續(xù)的存儲(chǔ)空間，以順序追加的方式記錄Redo Log惰拱，通過(guò)順序IO來(lái)改善性能雌贱。所有的事務(wù)共享redo log的存儲(chǔ)空間，它們的Redo Log按語(yǔ)句的執(zhí)行順序，依次交替的記錄在一起欣孤。
- undo log（回滾日志） 實(shí)現(xiàn)一致性u(píng)ndo log 主要為事務(wù)的回滾服務(wù)馋没。在事務(wù)執(zhí)行的過(guò)程中，除了記錄redo log导街，還會(huì)記錄一定量的undo log披泪。undo log記錄了數(shù)據(jù)在每個(gè)操作前的狀態(tài)纤子，如果事務(wù)執(zhí)行過(guò)程中需要回滾搬瑰，就可以根據(jù)undo log進(jìn)行回滾操作。單個(gè)事務(wù)的回滾控硼，只會(huì)回滾當(dāng)前事務(wù)做的操作泽论，并不會(huì)影響到其他的事務(wù)做的操作。Undo記錄的是已部分完成并且寫(xiě)入硬盤(pán)的未完成的事務(wù)卡乾，默認(rèn)情況下回滾日志是記錄下表空間中的（共享表空間或者獨(dú)享表空間）
二種日志均可以視為一種恢復(fù)操作翼悴，redo_log是恢復(fù)提交事務(wù)修改的頁(yè)操作，而undo_log是回滾行記錄到特定版本幔妨。二者記錄的內(nèi)容也不同鹦赎，redo_log是物理日志，記錄頁(yè)的物理修改操作误堡，而undo_log是邏輯日志古话，根據(jù)每行記錄進(jìn)行記錄。
?
又引出個(gè)問(wèn)題：你知道MySQL 有多少種日志嗎锁施？
- 錯(cuò)誤日志：記錄出錯(cuò)信息陪踩，也記錄一些警告信息或者正確的信息。
- 查詢?nèi)罩?/strong>：記錄所有對(duì)數(shù)據(jù)庫(kù)請(qǐng)求的信息悉抵，不論這些請(qǐng)求是否得到了正確的執(zhí)行肩狂。
- 慢查詢?nèi)罩?/strong>：設(shè)置一個(gè)閾值，將運(yùn)行時(shí)間超過(guò)該值的所有SQL語(yǔ)句都記錄到慢查詢的日志文件中姥饰。
- 二進(jìn)制日志：記錄對(duì)數(shù)據(jù)庫(kù)執(zhí)行更改的所有操作傻谁。
- 中繼日志：中繼日志也是二進(jìn)制日志，用來(lái)給slave 庫(kù)恢復(fù)
- 事務(wù)日志：重做日志redo和回滾日志undo
?
分布式事務(wù)相關(guān)問(wèn)題列粪，可能還會(huì)問(wèn)到 2PC肤京、3PC规辱，，，
MySQL對(duì)分布式事務(wù)的支持
分布式事務(wù)的實(shí)現(xiàn)方式有很多送火，既可以采用 InnoDB 提供的原生的事務(wù)支持，也可以采用消息隊(duì)列來(lái)實(shí)現(xiàn)分布式事務(wù)的最終一致性坑赡。這里我們主要聊一下 InnoDB 對(duì)分布式事務(wù)的支持衡奥。
MySQL 從 5.0.3 InnoDB 存儲(chǔ)引擎開(kāi)始支持XA協(xié)議的分布式事務(wù)。一個(gè)分布式事務(wù)會(huì)涉及多個(gè)行動(dòng)，這些行動(dòng)本身是事務(wù)性的赘那。所有行動(dòng)都必須一起成功完成刑桑，或者一起被回滾。
在MySQL中募舟，使用分布式事務(wù)涉及一個(gè)或多個(gè)資源管理器和一個(gè)事務(wù)管理器祠斧。
如圖，MySQL 的分布式事務(wù)模型拱礁。模型中分三塊：應(yīng)用程序（AP）琢锋、資源管理器（RM）、事務(wù)管理器（TM）:
- 應(yīng)用程序：定義了事務(wù)的邊界呢灶，指定需要做哪些事務(wù)吴超；
- 資源管理器：提供了訪問(wèn)事務(wù)的方法，通常一個(gè)數(shù)據(jù)庫(kù)就是一個(gè)資源管理器鸯乃；
- 事務(wù)管理器：協(xié)調(diào)參與了全局事務(wù)中的各個(gè)事務(wù)鲸阻。
分布式事務(wù)采用兩段式提交（two-phase commit）的方式：
- 第一階段所有的事務(wù)節(jié)點(diǎn)開(kāi)始準(zhǔn)備，告訴事務(wù)管理器ready缨睡。
- 第二階段事務(wù)管理器告訴每個(gè)節(jié)點(diǎn)是commit還是rollback鸟悴。如果有一個(gè)節(jié)點(diǎn)失敗，就需要全局的節(jié)點(diǎn)全部rollback奖年，以此保障事務(wù)的原子性细诸。
七、MySQL鎖機(jī)制
?
數(shù)據(jù)庫(kù)的樂(lè)觀鎖和悲觀鎖拾并？
MySQL 中有哪幾種鎖揍堰，列舉一下？
MySQL中InnoDB引擎的行鎖是怎么實(shí)現(xiàn)的嗅义？
MySQL 間隙鎖有沒(méi)有了解屏歹，死鎖有沒(méi)有了解，寫(xiě)一段會(huì)造成死鎖的 sql 語(yǔ)句之碗，死鎖發(fā)生了如何解決蝙眶，MySQL 有沒(méi)有提供什么機(jī)制去解決死鎖
鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問(wèn)某一資源的機(jī)制。
在數(shù)據(jù)庫(kù)中褪那，除傳統(tǒng)的計(jì)算資源（如CPU幽纷、RAM、I/O等）的爭(zhēng)用以外博敬，數(shù)據(jù)也是一種供許多用戶共享的資源友浸。數(shù)據(jù)庫(kù)鎖定機(jī)制簡(jiǎn)單來(lái)說(shuō)，就是數(shù)據(jù)庫(kù)為了保證數(shù)據(jù)的一致性偏窝，而使各種共享資源在被并發(fā)訪問(wèn)變得有序所設(shè)計(jì)的一種規(guī)則收恢。
打個(gè)比方武学，我們到淘寶上買(mǎi)一件商品，商品只有一件庫(kù)存伦意，這個(gè)時(shí)候如果還有另一個(gè)人買(mǎi)火窒，那么如何解決是你買(mǎi)到還是另一個(gè)人買(mǎi)到的問(wèn)題？這里肯定要用到事物驮肉，我們先從庫(kù)存表中取出物品數(shù)量熏矿，然后插入訂單，付款后插入付款表信息离钝，然后更新商品數(shù)量票编。在這個(gè)過(guò)程中，使用鎖可以對(duì)有限的資源進(jìn)行保護(hù)奈辰，解決隔離和并發(fā)的矛盾栏妖。
鎖的分類(lèi)
從對(duì)數(shù)據(jù)操作的類(lèi)型分類(lèi)：
- 讀鎖（共享鎖）：針對(duì)同一份數(shù)據(jù)，多個(gè)讀操作可以同時(shí)進(jìn)行奖恰，不會(huì)互相影響
- 寫(xiě)鎖（排他鎖）：當(dāng)前寫(xiě)操作沒(méi)有完成前，它會(huì)阻斷其他寫(xiě)鎖和讀鎖
從對(duì)數(shù)據(jù)操作的粒度分類(lèi)：
為了盡可能提高數(shù)據(jù)庫(kù)的并發(fā)度宛裕，每次鎖定的數(shù)據(jù)范圍越小越好瑟啃，理論上每次只鎖定當(dāng)前操作的數(shù)據(jù)的方案會(huì)得到最大的并發(fā)度，但是管理鎖是很耗資源的事情（涉及獲取揩尸，檢查蛹屿，釋放鎖等動(dòng)作），因此數(shù)據(jù)庫(kù)系統(tǒng)需要在高并發(fā)響應(yīng)和系統(tǒng)性能兩方面進(jìn)行平衡岩榆，這樣就產(chǎn)生了“鎖粒度（Lock granularity）”的概念错负。
- 表級(jí)鎖：開(kāi)銷(xiāo)小，加鎖快勇边；不會(huì)出現(xiàn)死鎖犹撒；鎖定粒度大，發(fā)生鎖沖突的概率最高粒褒，并發(fā)度最低（MyISAM 和 MEMORY 存儲(chǔ)引擎采用的是表級(jí)鎖）识颊；
- 行級(jí)鎖：開(kāi)銷(xiāo)大，加鎖慢奕坟；會(huì)出現(xiàn)死鎖祥款；鎖定粒度最小，發(fā)生鎖沖突的概率最低月杉，并發(fā)度也最高（InnoDB 存儲(chǔ)引擎既支持行級(jí)鎖也支持表級(jí)鎖刃跛，但默認(rèn)情況下是采用行級(jí)鎖）；
- 頁(yè)面鎖：開(kāi)銷(xiāo)和加鎖時(shí)間界于表鎖和行鎖之間苛萎；會(huì)出現(xiàn)死鎖桨昙；鎖定粒度界于表鎖和行鎖之間跌帐，并發(fā)度一般。
適用：從鎖的角度來(lái)說(shuō)绊率，表級(jí)鎖更適合于以查詢?yōu)橹鹘髁玻挥猩倭堪此饕龡l件更新數(shù)據(jù)的應(yīng)用，如Web應(yīng)用滤否；而行級(jí)鎖則更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)脸狸，同時(shí)又有并發(fā)查詢的應(yīng)用，如一些在線事務(wù)處理（OLTP）系統(tǒng)藐俺。
行鎖表鎖頁(yè)鎖MyISAM
√
BDB
√√InnoDB√√
Memory
√
MyISAM 表鎖
MyISAM 的表鎖有兩種模式：
- 表共享讀鎖 （Table Read Lock）：不會(huì)阻塞其他用戶對(duì)同一表的讀請(qǐng)求炊甲，但會(huì)阻塞對(duì)同一表的寫(xiě)請(qǐng)求；
- 表獨(dú)占寫(xiě)鎖 （Table Write Lock）：會(huì)阻塞其他用戶對(duì)同一表的讀和寫(xiě)操作欲芹；
MyISAM 表的讀操作與寫(xiě)操作之間卿啡，以及寫(xiě)操作之間是串行的。當(dāng)一個(gè)線程獲得對(duì)一個(gè)表的寫(xiě)鎖后菱父， 只有持有鎖的線程可以對(duì)表進(jìn)行更新操作颈娜。其他線程的讀、 寫(xiě)操作都會(huì)等待浙宜，直到鎖被釋放為止官辽。
默認(rèn)情況下，寫(xiě)鎖比讀鎖具有更高的優(yōu)先級(jí)：當(dāng)一個(gè)鎖釋放時(shí)粟瞬，這個(gè)鎖會(huì)優(yōu)先給寫(xiě)鎖隊(duì)列中等候的獲取鎖請(qǐng)求同仆，然后再給讀鎖隊(duì)列中等候的獲取鎖請(qǐng)求。
InnoDB 行鎖
InnoDB 實(shí)現(xiàn)了以下兩種類(lèi)型的行鎖：
- 共享鎖（S）：允許一個(gè)事務(wù)去讀一行裙品，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖俗批。
- 排他鎖（X）：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫(xiě)鎖市怎。
為了允許行鎖和表鎖共存岁忘，實(shí)現(xiàn)多粒度鎖機(jī)制，InnoDB 還有兩種內(nèi)部使用的意向鎖（Intention Locks）焰轻，這兩種意向鎖都是表鎖：
- 意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行加行共享鎖臭觉，事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的 IS 鎖。
- 意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加行排他鎖辱志，事務(wù)在給一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的 IX 鎖蝠筑。
索引失效會(huì)導(dǎo)致行鎖變表鎖。比如 vchar 查詢不寫(xiě)單引號(hào)的情況揩懒。
加鎖機(jī)制
樂(lè)觀鎖與悲觀鎖是兩種并發(fā)控制的思想什乙，可用于解決丟失更新問(wèn)題
樂(lè)觀鎖會(huì)“樂(lè)觀地”假定大概率不會(huì)發(fā)生并發(fā)更新沖突，訪問(wèn)已球、處理數(shù)據(jù)過(guò)程中不加鎖臣镣，只在更新數(shù)據(jù)時(shí)再根據(jù)版本號(hào)或時(shí)間戳判斷是否有沖突辅愿，有則處理，無(wú)則提交事務(wù)忆某。用數(shù)據(jù)版本（Version）記錄機(jī)制實(shí)現(xiàn)点待，這是樂(lè)觀鎖最常用的一種實(shí)現(xiàn)方式
悲觀鎖會(huì)“悲觀地”假定大概率會(huì)發(fā)生并發(fā)更新沖突，訪問(wèn)弃舒、處理數(shù)據(jù)前就加排他鎖癞埠，在整個(gè)數(shù)據(jù)處理過(guò)程中鎖定數(shù)據(jù)，事務(wù)提交或回滾后才釋放鎖聋呢。另外與樂(lè)觀鎖相對(duì)應(yīng)的苗踪，悲觀鎖是由數(shù)據(jù)庫(kù)自己實(shí)現(xiàn)了的，要用的時(shí)候削锰，我們直接調(diào)用數(shù)據(jù)庫(kù)的相關(guān)語(yǔ)句就可以了通铲。
鎖模式(InnoDB有三種行鎖的算法)
-
記錄鎖(Record Locks)：?jiǎn)蝹€(gè)行記錄上的鎖。對(duì)索引項(xiàng)加鎖器贩，鎖定符合條件的行颅夺。其他事務(wù)不能修改和刪除加鎖項(xiàng)；SELECT * FROM table WHERE id = 1 FOR UPDATE;它會(huì)在 id=1 的記錄上加上記錄鎖磨澡，以阻止其他事務(wù)插入碗啄，更新，刪除 id=1 這一行在通過(guò) 主鍵索引 與 唯一索引 對(duì)數(shù)據(jù)行進(jìn)行 UPDATE 操作時(shí)稳摄，也會(huì)對(duì)該行數(shù)據(jù)加記錄鎖：-- id 列為主鍵列或唯一索引列
UPDATE SET age = 50 WHERE id = 1; - 間隙鎖（Gap Locks）：當(dāng)我們使用范圍條件而不是相等條件檢索數(shù)據(jù)，并請(qǐng)求共享或排他鎖時(shí)饲宿，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖厦酬。對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙”瘫想。InnoDB 也會(huì)對(duì)這個(gè)“間隙”加鎖仗阅，這種鎖機(jī)制就是所謂的間隙鎖。對(duì)索引項(xiàng)之間的“間隙”加鎖国夜，鎖定記錄的范圍（對(duì)第一條記錄前的間隙或最后一條將記錄后的間隙加鎖）减噪，不包含索引項(xiàng)本身。其他事務(wù)不能在鎖范圍內(nèi)插入數(shù)據(jù)车吹，這樣就防止了別的事務(wù)新增幻影行筹裕。間隙鎖基于非唯一索引，它鎖定一段范圍內(nèi)的索引記錄窄驹。間隙鎖基于下面將會(huì)提到的Next-Key Locking 算法朝卒，請(qǐng)務(wù)必牢記：使用間隙鎖鎖住的是一個(gè)區(qū)間，而不僅僅是這個(gè)區(qū)間中的每一條數(shù)據(jù)乐埠。SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;即所有在（1抗斤，10）區(qū)間內(nèi)的記錄行都會(huì)被鎖住囚企，所有id 為 2、3瑞眼、4龙宏、5、6伤疙、7银酗、8、9 的數(shù)據(jù)行的插入會(huì)被阻塞掩浙，但是 1 和 10 兩條記錄行并不會(huì)被鎖住花吟。GAP鎖的目的，是為了防止同一事務(wù)的兩次當(dāng)前讀厨姚，出現(xiàn)幻讀的情況
- 臨鍵鎖(Next-key Locks)：臨鍵鎖衅澈，是記錄鎖與間隙鎖的組合，它的封鎖范圍谬墙，既包含索引記錄今布，又包含索引區(qū)間。(臨鍵鎖的主要目的拭抬，也是為了避免幻讀(Phantom Read)部默。如果把事務(wù)的隔離級(jí)別降級(jí)為RC，臨鍵鎖則也會(huì)失效造虎。)Next-Key 可以理解為一種特殊的間隙鎖傅蹂，也可以理解為一種特殊的算法。通過(guò)臨建鎖可以解決幻讀的問(wèn)題算凿。每個(gè)數(shù)據(jù)行上的非唯一索引列上都會(huì)存在一把臨鍵鎖份蝴，當(dāng)某個(gè)事務(wù)持有該數(shù)據(jù)行的臨鍵鎖時(shí)，會(huì)鎖住一段左開(kāi)右閉區(qū)間的數(shù)據(jù)氓轰。需要強(qiáng)調(diào)的一點(diǎn)是婚夫，InnoDB 中行級(jí)鎖是基于索引實(shí)現(xiàn)的，臨鍵鎖只與非唯一索引列有關(guān)署鸡，在唯一索引列（包括主鍵列）上不存在臨鍵鎖案糙。對(duì)于行的查詢，都是采用該方法靴庆，主要目的是解決幻讀的問(wèn)題时捌。
?
select for update有什么含義，會(huì)鎖表還是鎖行還是其他
for update 僅適用于InnoDB撒穷，且必須在事務(wù)塊(BEGIN/COMMIT)中才能生效匣椰。在進(jìn)行事務(wù)操作時(shí)，通過(guò)“for update”語(yǔ)句端礼，MySQL會(huì)對(duì)查詢結(jié)果集中每行數(shù)據(jù)都添加排他鎖禽笑，其他線程對(duì)該記錄的更新與刪除操作都會(huì)阻塞入录。排他鎖包含行鎖、表鎖佳镜。
InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：只有通過(guò)索引條件檢索數(shù)據(jù)僚稿，InnoDB才使用行級(jí)鎖，否則蟀伸，InnoDB將使用表鎖蚀同！假設(shè)有個(gè)表單 products ，里面有id跟name二個(gè)欄位啊掏，id是主鍵蠢络。
- 明確指定主鍵，并且有此筆資料迟蜜，row lock
SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;
- 明確指定主鍵刹孔，若查無(wú)此筆資料，無(wú)lock
SELECT * FROM products WHERE id='-1' FOR UPDATE;
- 無(wú)主鍵娜睛，table lock
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
- 主鍵不明確髓霞，table lock
SELECT * FROM products WHERE id<>'3' FOR UPDATE;
- 主鍵不明確，table lock
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;
注1: FOR UPDATE僅適用于InnoDB畦戒，且必須在交易區(qū)塊(BEGIN/COMMIT)中才能生效方库。注2: 要測(cè)試鎖定的狀況，可以利用MySQL的Command Mode 障斋，開(kāi)二個(gè)視窗來(lái)做測(cè)試纵潦。
?
MySQL 遇到過(guò)死鎖問(wèn)題嗎，你是如何解決的垃环？
死鎖
死鎖產(chǎn)生：
- 死鎖是指兩個(gè)或多個(gè)事務(wù)在同一資源上相互占用酪穿，并請(qǐng)求鎖定對(duì)方占用的資源，從而導(dǎo)致惡性循環(huán)
- 當(dāng)事務(wù)試圖以不同的順序鎖定資源時(shí)晴裹，就可能產(chǎn)生死鎖。多個(gè)事務(wù)同時(shí)鎖定同一個(gè)資源時(shí)也可能會(huì)產(chǎn)生死鎖
- 鎖的行為和順序和存儲(chǔ)引擎相關(guān)救赐。以同樣的順序執(zhí)行語(yǔ)句涧团，有些存儲(chǔ)引擎會(huì)產(chǎn)生死鎖有些不會(huì)——死鎖有雙重原因：真正的數(shù)據(jù)沖突；存儲(chǔ)引擎的實(shí)現(xiàn)方式经磅。
檢測(cè)死鎖：數(shù)據(jù)庫(kù)系統(tǒng)實(shí)現(xiàn)了各種死鎖檢測(cè)和死鎖超時(shí)的機(jī)制泌绣。InnoDB存儲(chǔ)引擎能檢測(cè)到死鎖的循環(huán)依賴并立即返回一個(gè)錯(cuò)誤。
死鎖恢復(fù)：死鎖發(fā)生以后预厌，只有部分或完全回滾其中一個(gè)事務(wù)阿迈，才能打破死鎖，InnoDB目前處理死鎖的方法是轧叽，將持有最少行級(jí)排他鎖的事務(wù)進(jìn)行回滾苗沧。所以事務(wù)型應(yīng)用程序在設(shè)計(jì)時(shí)必須考慮如何處理死鎖刊棕，多數(shù)情況下只需要重新執(zhí)行因死鎖回滾的事務(wù)即可。
外部鎖的死鎖檢測(cè)：發(fā)生死鎖后待逞，InnoDB 一般都能自動(dòng)檢測(cè)到甥角，并使一個(gè)事務(wù)釋放鎖并回退，另一個(gè)事務(wù)獲得鎖识樱，繼續(xù)完成事務(wù)嗤无。但在涉及外部鎖，或涉及表鎖的情況下怜庸，InnoDB 并不能完全自動(dòng)檢測(cè)到死鎖当犯， 這需要通過(guò)設(shè)置鎖等待超時(shí)參數(shù) innodb_lock_wait_timeout 來(lái)解決
死鎖影響性能：死鎖會(huì)影響性能而不是會(huì)產(chǎn)生嚴(yán)重錯(cuò)誤，因?yàn)镮nnoDB會(huì)自動(dòng)檢測(cè)死鎖狀況并回滾其中一個(gè)受影響的事務(wù)割疾。在高并發(fā)系統(tǒng)上嚎卫，當(dāng)許多線程等待同一個(gè)鎖時(shí)，死鎖檢測(cè)可能導(dǎo)致速度變慢杈曲。有時(shí)當(dāng)發(fā)生死鎖時(shí)驰凛，禁用死鎖檢測(cè)（使用innodb_deadlock_detect配置選項(xiàng)）可能會(huì)更有效，這時(shí)可以依賴innodb_lock_wait_timeout設(shè)置進(jìn)行事務(wù)回滾担扑。
MyISAM避免死鎖：
- 在自動(dòng)加鎖的情況下恰响，MyISAM 總是一次獲得 SQL 語(yǔ)句所需要的全部鎖，所以 MyISAM 表不會(huì)出現(xiàn)死鎖涌献。
InnoDB避免死鎖：
- 為了在單個(gè)InnoDB表上執(zhí)行多個(gè)并發(fā)寫(xiě)入操作時(shí)避免死鎖胚宦，可以在事務(wù)開(kāi)始時(shí)通過(guò)為預(yù)期要修改的每個(gè)元祖（行）使用SELECT ... FOR UPDATE語(yǔ)句來(lái)獲取必要的鎖，即使這些行的更改語(yǔ)句是在之后才執(zhí)行的燕垃。
- 在事務(wù)中枢劝，如果要更新記錄，應(yīng)該直接申請(qǐng)足夠級(jí)別的鎖卜壕，即排他鎖您旁，而不應(yīng)先申請(qǐng)共享鎖、更新時(shí)再申請(qǐng)排他鎖轴捎，因?yàn)檫@時(shí)候當(dāng)用戶再申請(qǐng)排他鎖時(shí)鹤盒，其他事務(wù)可能又已經(jīng)獲得了相同記錄的共享鎖，從而造成鎖沖突侦副，甚至死鎖
- 如果事務(wù)需要修改或鎖定多個(gè)表侦锯，則應(yīng)在每個(gè)事務(wù)中以相同的順序使用加鎖語(yǔ)句。在應(yīng)用中秦驯，如果不同的程序會(huì)并發(fā)存取多個(gè)表尺碰，應(yīng)盡量約定以相同的順序來(lái)訪問(wèn)表，這樣可以大大降低產(chǎn)生死鎖的機(jī)會(huì)
- 通過(guò)SELECT ... LOCK IN SHARE MODE獲取行的讀鎖后，如果當(dāng)前事務(wù)再需要對(duì)該記錄進(jìn)行更新操作亲桥，則很有可能造成死鎖洛心。
- 改變事務(wù)隔離級(jí)別
如果出現(xiàn)死鎖，可以用 show engine innodb status;命令來(lái)確定最后一個(gè)死鎖產(chǎn)生的原因两曼。返回結(jié)果中包括死鎖相關(guān)事務(wù)的詳細(xì)信息皂甘，如引發(fā)死鎖的 SQL 語(yǔ)句，事務(wù)已經(jīng)獲得的鎖悼凑，正在等待什么鎖偿枕，以及被回滾的事務(wù)等。據(jù)此可以分析死鎖產(chǎn)生的原因和改進(jìn)措施户辫。
八爱葵、MySQL調(diào)優(yōu)
?
日常工作中你是怎么優(yōu)化SQL的剥懒？
SQL優(yōu)化的一般步驟是什么榆苞，怎么看執(zhí)行計(jì)劃（explain）色迂，如何理解其中各個(gè)字段的含義？
如何寫(xiě)sql能夠有效的使用到復(fù)合索引奥额？
一條sql執(zhí)行過(guò)長(zhǎng)的時(shí)間苫幢，你如何優(yōu)化，從哪些方面入手垫挨？
什么是最左前綴原則韩肝？什么是最左匹配原則？
影響mysql的性能因素
業(yè)務(wù)需求對(duì)MySQL的影響(合適合度)
存儲(chǔ)定位對(duì)MySQL的影響
系統(tǒng)各種配置及規(guī)則數(shù)據(jù)
活躍用戶的基本信息數(shù)據(jù)
活躍用戶的個(gè)性化定制信息數(shù)據(jù)
準(zhǔn)實(shí)時(shí)的統(tǒng)計(jì)信息數(shù)據(jù)
其他一些訪問(wèn)頻繁但變更較少的數(shù)據(jù)
二進(jìn)制多媒體數(shù)據(jù)
流水隊(duì)列數(shù)據(jù)
超大文本數(shù)據(jù)
不適合放進(jìn)MySQL的數(shù)據(jù)
需要放進(jìn)緩存的數(shù)據(jù)
Schema設(shè)計(jì)對(duì)系統(tǒng)的性能影響
盡量減少對(duì)數(shù)據(jù)庫(kù)訪問(wèn)的請(qǐng)求
盡量減少無(wú)用數(shù)據(jù)的查詢請(qǐng)求
硬件環(huán)境對(duì)系統(tǒng)性能的影響
性能分析
MySQL Query Optimizer
- MySQL 中有專門(mén)負(fù)責(zé)優(yōu)化 SELECT 語(yǔ)句的優(yōu)化器模塊九榔，主要功能：通過(guò)計(jì)算分析系統(tǒng)中收集到的統(tǒng)計(jì)信息哀峻，為客戶端請(qǐng)求的 Query 提供他認(rèn)為最優(yōu)的執(zhí)行計(jì)劃（他認(rèn)為最優(yōu)的數(shù)據(jù)檢索方式，但不見(jiàn)得是 DBA 認(rèn)為是最優(yōu)的哲泊，這部分最耗費(fèi)時(shí)間）
- 當(dāng)客戶端向 MySQL 請(qǐng)求一條 Query剩蟀，命令解析器模塊完成請(qǐng)求分類(lèi)，區(qū)別出是 SELECT 并轉(zhuǎn)發(fā)給 MySQL Query Optimize r時(shí)切威，MySQL Query Optimizer 首先會(huì)對(duì)整條 Query 進(jìn)行優(yōu)化育特，處理掉一些常量表達(dá)式的預(yù)算，直接換算成常量值先朦。并對(duì) Query 中的查詢條件進(jìn)行簡(jiǎn)化和轉(zhuǎn)換且预，如去掉一些無(wú)用或顯而易見(jiàn)的條件、結(jié)構(gòu)調(diào)整等烙无。然后分析 Query 中的 Hint 信息（如果有），看顯示 Hint 信息是否可以完全確定該 Query 的執(zhí)行計(jì)劃遍尺。如果沒(méi)有 Hint 或 Hint 信息還不足以完全確定執(zhí)行計(jì)劃截酷，則會(huì)讀取所涉及對(duì)象的統(tǒng)計(jì)信息，根據(jù) Query 進(jìn)行寫(xiě)相應(yīng)的計(jì)算分析乾戏，然后再得出最后的執(zhí)行計(jì)劃迂苛。
MySQL常見(jiàn)瓶頸
- CPU：CPU在飽和的時(shí)候一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或從磁盤(pán)上讀取數(shù)據(jù)時(shí)候
- IO：磁盤(pán)I/O瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量的時(shí)候
- 服務(wù)器硬件的性能瓶頸：top三热，free，iostat 和 vmstat來(lái)查看系統(tǒng)的性能狀態(tài)
性能下降SQL慢 執(zhí)行時(shí)間長(zhǎng) 等待時(shí)間長(zhǎng) 原因分析
- 查詢語(yǔ)句寫(xiě)的爛
- 索引失效（單值三幻、復(fù)合）
- 關(guān)聯(lián)查詢太多join（設(shè)計(jì)缺陷或不得已的需求）
- 服務(wù)器調(diào)優(yōu)及各個(gè)參數(shù)設(shè)置（緩沖就漾、線程數(shù)等）
MySQL常見(jiàn)性能分析手段
在優(yōu)化MySQL時(shí)，通常需要對(duì)數(shù)據(jù)庫(kù)進(jìn)行分析念搬，常見(jiàn)的分析手段有慢查詢?nèi)罩?/strong>抑堡，EXPLAIN 分析查詢，profiling分析以及show命令查詢系統(tǒng)狀態(tài)及系統(tǒng)變量朗徊，通過(guò)定位分析性能的瓶頸首妖，才能更好的優(yōu)化數(shù)據(jù)庫(kù)系統(tǒng)的性能。
性能瓶頸定位
我們可以通過(guò) show 命令查看 MySQL 狀態(tài)及變量爷恳，找到系統(tǒng)的瓶頸：
Mysql> show status ——顯示狀態(tài)信息（擴(kuò)展show status like ‘XXX’）
Mysql> show variables ——顯示系統(tǒng)變量（擴(kuò)展show variables like ‘XXX’）
Mysql> show innodb status ——顯示InnoDB存儲(chǔ)引擎的狀態(tài)
Mysql> show processlist ——查看當(dāng)前SQL執(zhí)行有缆，包括執(zhí)行狀態(tài)、是否鎖表等
Shell> mysqladmin variables -u username -p password——顯示系統(tǒng)變量
Shell> mysqladmin extended-status -u username -p password——顯示狀態(tài)信息
Explain(執(zhí)行計(jì)劃)
是什么：使用 Explain 關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語(yǔ)句温亲，從而知道 MySQL 是如何處理你的 SQL 語(yǔ)句的棚壁。分析你的查詢語(yǔ)句或是表結(jié)構(gòu)的性能瓶頸
能干嗎：
- 表的讀取順序
- 數(shù)據(jù)讀取操作的操作類(lèi)型
- 哪些索引可以使用
- 哪些索引被實(shí)際使用
- 表之間的引用
- 每張表有多少行被優(yōu)化器查詢
怎么玩：
- Explain + SQL語(yǔ)句
- 執(zhí)行計(jì)劃包含的信息（如果有分區(qū)表的話還會(huì)有partitions）
expalin
各字段解釋
id（select 查詢的序列號(hào)，包含一組數(shù)字栈虚，表示查詢中執(zhí)行select子句或操作表的順序）
id相同袖外，執(zhí)行順序從上往下
id全不同，如果是子查詢节芥，id的序號(hào)會(huì)遞增在刺，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行
id部分相同头镊，執(zhí)行順序是先按照數(shù)字大的先執(zhí)行蚣驼，然后數(shù)字相同的按照從上往下的順序執(zhí)行
select_type（查詢類(lèi)型，用于區(qū)別普通查詢相艇、聯(lián)合查詢颖杏、子查詢等復(fù)雜查詢）
SIMPLE ：簡(jiǎn)單的select查詢，查詢中不包含子查詢或UNION
PRIMARY：查詢中若包含任何復(fù)雜的子部分坛芽，最外層查詢被標(biāo)記為PRIMARY
SUBQUERY：在select或where列表中包含了子查詢
DERIVED：在from列表中包含的子查詢被標(biāo)記為DERIVED留储，MySQL會(huì)遞歸執(zhí)行這些子查詢，把結(jié)果放在臨時(shí)表里
UNION：若第二個(gè)select出現(xiàn)在UNION之后咙轩，則被標(biāo)記為UNION获讳，若UNION包含在from子句的子查詢中，外層select將被標(biāo)記為DERIVED
UNION RESULT：從UNION表獲取結(jié)果的select
table（顯示這一行的數(shù)據(jù)是關(guān)于哪張表的）
type（顯示查詢使用了那種類(lèi)型活喊，從最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）tip: 一般來(lái)說(shuō)丐膝，得保證查詢至少達(dá)到range級(jí)別，最好到達(dá)ref
system：表只有一行記錄（等于系統(tǒng)表），是 const 類(lèi)型的特例帅矗，平時(shí)不會(huì)出現(xiàn)
const：表示通過(guò)索引一次就找到了偎肃，const 用于比較 primary key 或 unique 索引，因?yàn)橹灰ヅ湟恍袛?shù)據(jù)浑此，所以很快累颂，如將主鍵置于 where 列表中，mysql 就能將該查詢轉(zhuǎn)換為一個(gè)常量
eq_ref：唯一性索引掃描凛俱，對(duì)于每個(gè)索引鍵紊馏，表中只有一條記錄與之匹配，常見(jiàn)于主鍵或唯一索引掃描
ref：非唯一性索引掃描最冰，范圍匹配某個(gè)單獨(dú)值得所有行瘦棋。本質(zhì)上也是一種索引訪問(wèn)，他返回所有匹配某個(gè)單獨(dú)值的行暖哨，然而赌朋，它可能也會(huì)找到多個(gè)符合條件的行，多以他應(yīng)該屬于查找和掃描的混合體
range：只檢索給定范圍的行篇裁，使用一個(gè)索引來(lái)選擇行沛慢。key列顯示使用了哪個(gè)索引，一般就是在你的where語(yǔ)句中出現(xiàn)了between达布、<团甲、>、in等的查詢黍聂，這種范圍掃描索引比全表掃描要好躺苦，因?yàn)樗恍栝_(kāi)始于索引的某一點(diǎn)，而結(jié)束于另一點(diǎn)产还，不用掃描全部索引
index：Full Index Scan匹厘，index于ALL區(qū)別為index類(lèi)型只遍歷索引樹(shù)。通常比ALL快脐区，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小愈诚。（也就是說(shuō)雖然all和index都是讀全表，但index是從索引中讀取的牛隅，而all是從硬盤(pán)中讀的）
ALL：Full Table Scan炕柔，將遍歷全表找到匹配的行
possible_keys（顯示可能應(yīng)用在這張表中的索引，一個(gè)或多個(gè)媒佣，查詢涉及到的字段若存在索引匕累，則該索引將被列出，但不一定被查詢實(shí)際使用）
key
實(shí)際使用的索引默伍，如果為NULL哩罪，則沒(méi)有使用索引
查詢中若使用了覆蓋索引授霸，則該索引和查詢的 select 字段重疊，僅出現(xiàn)在key列表中
explain-key
key_len
表示索引中使用的字節(jié)數(shù)际插，可通過(guò)該列計(jì)算查詢中使用的索引的長(zhǎng)度。在不損失精確性的情況下显设，長(zhǎng)度越短越好
key_len顯示的值為索引字段的最大可能長(zhǎng)度框弛，并非實(shí)際使用長(zhǎng)度，即key_len是根據(jù)表定義計(jì)算而得捕捂，不是通過(guò)表內(nèi)檢索出的
ref（顯示索引的哪一列被使用了瑟枫，如果可能的話，是一個(gè)常數(shù)指攒。哪些列或常量被用于查找索引列上的值）
rows（根據(jù)表統(tǒng)計(jì)信息及索引選用情況慷妙，大致估算找到所需的記錄所需要讀取的行數(shù)）
Extra（包含不適合在其他列中顯示但十分重要的額外信息）
using filesort: 說(shuō)明mysql會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，不是按照表內(nèi)的索引順序進(jìn)行讀取允悦。mysql中無(wú)法利用索引完成的排序操作稱為“文件排序”膝擂。常見(jiàn)于order by和group by語(yǔ)句中
Using temporary：使用了臨時(shí)表保存中間結(jié)果，mysql在對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表隙弛。常見(jiàn)于排序order by和分組查詢group by架馋。
using index：表示相應(yīng)的select操作中使用了覆蓋索引，避免訪問(wèn)了表的數(shù)據(jù)行全闷，效率不錯(cuò)叉寂，如果同時(shí)出現(xiàn)using where，表明索引被用來(lái)執(zhí)行索引鍵值的查找总珠；否則索引被用來(lái)讀取數(shù)據(jù)而非執(zhí)行查找操作
using where：使用了where過(guò)濾
using join buffer：使用了連接緩存
impossible where：where子句的值總是false屏鳍，不能用來(lái)獲取任何元祖
select tables optimized away：在沒(méi)有g(shù)roup by子句的情況下，基于索引優(yōu)化操作或?qū)τ贛yISAM存儲(chǔ)引擎優(yōu)化COUNT(*)操作局服，不必等到執(zhí)行階段再進(jìn)行計(jì)算钓瞭，查詢執(zhí)行計(jì)劃生成的階段即完成優(yōu)化
distinct：優(yōu)化distinct操作，在找到第一匹配的元祖后即停止找同樣值的動(dòng)作
case:
explain-demo
- 第一行（執(zhí)行順序4）：id列為1腌逢，表示是union里的第一個(gè)select降淮，select_type列的primary表示該查詢?yōu)橥鈱硬樵儯瑃able列被標(biāo)記為搏讶，表示查詢結(jié)果來(lái)自一個(gè)衍生表佳鳖，其中derived3中3代表該查詢衍生自第三個(gè)select查詢，即id為3的select媒惕∠捣裕【select d1.name......】
- 第二行（執(zhí)行順序2）：id為3，是整個(gè)查詢中第三個(gè)select的一部分妒蔚。因查詢包含在from中穿挨，所以為derived月弛。【select id,name from t1 where other_column=''】
- 第三行（執(zhí)行順序3）：select列表中的子查詢select_type為subquery科盛，為整個(gè)查詢中的第二個(gè)select帽衙。【select id from t3】
- 第四行（執(zhí)行順序1）：select_type為union贞绵，說(shuō)明第四個(gè)select是union里的第二個(gè)select厉萝，最先執(zhí)行【select name,id from t2】
- 第五行（執(zhí)行順序5）：代表從union的臨時(shí)表中讀取行的階段，table列的<union1,4>表示用第一個(gè)和第四個(gè)select的結(jié)果進(jìn)行union操作榨崩∏吹妫【兩個(gè)結(jié)果union操作】
慢查詢?nèi)罩?/h5>
MySQL 的慢查詢?nèi)罩臼?MySQL 提供的一種日志記錄，它用來(lái)記錄在 MySQL 中響應(yīng)時(shí)間超過(guò)閾值的語(yǔ)句母蛛，具體指運(yùn)行時(shí)間超過(guò) long_query_time 值的 SQL翩剪，則會(huì)被記錄到慢查詢?nèi)罩局小?/p>
- long_query_time 的默認(rèn)值為10，意思是運(yùn)行10秒以上的語(yǔ)句
- 默認(rèn)情況下彩郊，MySQL數(shù)據(jù)庫(kù)沒(méi)有開(kāi)啟慢查詢?nèi)罩厩巴洌枰謩?dòng)設(shè)置參數(shù)開(kāi)啟
查看開(kāi)啟狀態(tài)
SHOW VARIABLES LIKE '%slow_query_log%'
開(kāi)啟慢查詢?nèi)罩?/strong>
- 臨時(shí)配置：
mysql> set global slow_query_log='ON';
mysql> set global slow_query_log_file='/var/lib/mysql/hostname-slow.log';
mysql> set global long_query_time=2;
也可set文件位置，系統(tǒng)會(huì)默認(rèn)給一個(gè)缺省文件host_name-slow.log
使用set操作開(kāi)啟慢查詢?nèi)罩局粚?duì)當(dāng)前數(shù)據(jù)庫(kù)生效焦辅，如果MySQL重啟則會(huì)失效博杖。
- 永久配置修改配置文件my.cnf或my.ini，在[mysqld]一行下面加入兩個(gè)配置參數(shù)
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/hostname-slow.log
long_query_time = 3
注：log-slow-queries 參數(shù)為慢查詢?nèi)罩敬娣诺奈恢每甑牵话氵@個(gè)目錄要有 MySQL 的運(yùn)行帳號(hào)的可寫(xiě)權(quán)限剃根，一般都將這個(gè)目錄設(shè)置為 MySQL 的數(shù)據(jù)存放目錄；long_query_time=2 中的 2 表示查詢超過(guò)兩秒才記錄前方；在my.cnf或者 my.ini 中添加 log-queries-not-using-indexes 參數(shù)狈醉，表示記錄下沒(méi)有使用索引的查詢。
可以用 select sleep(4) 驗(yàn)證是否成功開(kāi)啟惠险。
在生產(chǎn)環(huán)境中苗傅，如果手工分析日志，查找班巩、分析SQL渣慕，還是比較費(fèi)勁的，所以MySQL提供了日志分析工具mysqldumpslow抱慌。
通過(guò) mysqldumpslow --help 查看操作幫助信息
- 得到返回記錄集最多的10個(gè)SQLmysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log
- 得到訪問(wèn)次數(shù)最多的10個(gè)SQLmysqldumpslow -s c -t 10 /var/lib/mysql/hostname-slow.log
- 得到按照時(shí)間排序的前10條里面含有左連接的查詢語(yǔ)句mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hostname-slow.log
- 也可以和管道配合使用mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log | more
也可使用 pt-query-digest 分析 RDS MySQL 慢查詢?nèi)罩?/strong>
Show Profile 分析查詢
通過(guò)慢日志查詢可以知道哪些 SQL 語(yǔ)句執(zhí)行效率低下逊桦，通過(guò) explain 我們可以得知 SQL 語(yǔ)句的具體執(zhí)行情況，索引使用等抑进，還可以結(jié)合Show Profile命令查看執(zhí)行狀態(tài)强经。
Show Profile 是 MySQL 提供可以用來(lái)分析當(dāng)前會(huì)話中語(yǔ)句執(zhí)行的資源消耗情況∷律可以用于SQL的調(diào)優(yōu)的測(cè)量
默認(rèn)情況下匿情，參數(shù)處于關(guān)閉狀態(tài)兰迫，并保存最近15次的運(yùn)行結(jié)果
分析步驟mysql> show profiles; +----------+------------+---------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------+ | 1 | 0.00385450 | show variables like "profiling" | | 2 | 0.00170050 | show variables like "profiling" | | 3 | 0.00038025 | select * from t_base_user | +----------+------------+---------------------------------+
converting HEAP to MyISAM 查詢結(jié)果太大，內(nèi)存都不夠用了往磁盤(pán)上搬了炬称。
create tmp table 創(chuàng)建臨時(shí)表汁果，這個(gè)要注意
Copying to tmp table on disk 把內(nèi)存臨時(shí)表復(fù)制到磁盤(pán)
locked
診斷SQL，show profile cpu,block io for query id(上一步前面的問(wèn)題SQL數(shù)字號(hào)碼)
日常開(kāi)發(fā)需要注意的結(jié)論
是否支持玲躯，看看當(dāng)前的mysql版本是否支持mysql>Show variables like 'profiling'; --默認(rèn)是關(guān)閉须鼎，使用前需要開(kāi)啟
開(kāi)啟功能，默認(rèn)是關(guān)閉府蔗，使用前需要開(kāi)啟mysql>set profiling=1;
運(yùn)行SQL
查看結(jié)果
?
查詢中哪些情況不會(huì)使用索引？
性能優(yōu)化
索引優(yōu)化
- 全值匹配我最?lèi)?ài)
- 最佳左前綴法則汞窗，比如建立了一個(gè)聯(lián)合索引(a,b,c)姓赤，那么其實(shí)我們可利用的索引就有(a), (a,b), (a,b,c)
- 不在索引列上做任何操作（計(jì)算、函數(shù)仲吏、(自動(dòng)or手動(dòng))類(lèi)型轉(zhuǎn)換）不铆，會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描
- 存儲(chǔ)引擎不能使用索引中范圍條件右邊的列
- 盡量使用覆蓋索引(只訪問(wèn)索引的查詢(索引列和查詢列一致))，減少select
- is null ,is not null 也無(wú)法使用索引
- like "xxxx%" 是可以用到索引的裹唆，like "%xxxx" 則不行(like "%xxx%" 同理)誓斥。like以通配符開(kāi)頭('%abc...')索引失效會(huì)變成全表掃描的操作，
- 字符串不加單引號(hào)索引失效
- 少用or许帐，用它來(lái)連接時(shí)會(huì)索引失效
- <劳坑，<=，=成畦，>距芬，>=，BETWEEN循帐，IN 可用到索引框仔，<>，not in 拄养，!= 則不行离斩，會(huì)導(dǎo)致全表掃描
一般性建議
- 對(duì)于單鍵索引，盡量選擇針對(duì)當(dāng)前query過(guò)濾性更好的索引
- 在選擇組合索引的時(shí)候瘪匿，當(dāng)前Query中過(guò)濾性最好的字段在索引字段順序中跛梗，位置越靠前越好。
- 在選擇組合索引的時(shí)候柿顶，盡量選擇可以能夠包含當(dāng)前query中的where字句中更多字段的索引
- 盡可能通過(guò)分析統(tǒng)計(jì)信息和調(diào)整query的寫(xiě)法來(lái)達(dá)到選擇合適索引的目的
- 少用Hint強(qiáng)制索引
查詢優(yōu)化
永遠(yuǎn)小標(biāo)驅(qū)動(dòng)大表（小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集）
slect * from A where id in (select id from B)`等價(jià)于
#等價(jià)于
select id from B
select * from A where A.id=B.id
當(dāng) B 表的數(shù)據(jù)集必須小于 A 表的數(shù)據(jù)集時(shí)茄袖，用 in 優(yōu)于 exists
select * from A where exists (select 1 from B where B.id=A.id)
#等價(jià)于
select * from A
select * from B where B.id = A.id`
當(dāng) A 表的數(shù)據(jù)集小于B表的數(shù)據(jù)集時(shí)，用 exists優(yōu)于用 in
注意：A表與B表的ID字段應(yīng)建立索引嘁锯。
order by關(guān)鍵字優(yōu)化
order by子句宪祥，盡量使用 Index 方式排序聂薪，避免使用 FileSort 方式排序
MySQL 支持兩種方式的排序，F(xiàn)ileSort 和 Index蝗羊，Index效率高藏澳，它指 MySQL 掃描索引本身完成排序，F(xiàn)ileSort 效率較低耀找；
ORDER BY 滿足兩種情況翔悠，會(huì)使用Index方式排序；①ORDER BY語(yǔ)句使用索引最左前列 ②使用where子句與ORDER BY子句條件列組合滿足索引最左前列
盡可能在索引列上完成排序操作野芒，遵照索引建的最佳最前綴
如果不在索引列上蓄愁，filesort 有兩種算法，mysql就要啟動(dòng)雙路排序和單路排序
雙路排序：MySQL 4.1之前是使用雙路排序,字面意思就是兩次掃描磁盤(pán)狞悲，最終得到數(shù)據(jù)
單路排序：從磁盤(pán)讀取查詢需要的所有列撮抓，按照order by 列在 buffer對(duì)它們進(jìn)行排序，然后掃描排序后的列表進(jìn)行輸出摇锋，效率高于雙路排序
優(yōu)化策略
增大sort_buffer_size參數(shù)的設(shè)置
增大max_lencth_for_sort_data參數(shù)的設(shè)置
GROUP BY關(guān)鍵字優(yōu)化
- group by實(shí)質(zhì)是先排序后進(jìn)行分組丹拯，遵照索引建的最佳左前綴
- 當(dāng)無(wú)法使用索引列，增大 max_length_for_sort_data 參數(shù)的設(shè)置荸恕，增大sort_buffer_size參數(shù)的設(shè)置
- where高于having乖酬，能寫(xiě)在where限定的條件就不要去having限定了
數(shù)據(jù)類(lèi)型優(yōu)化
MySQL 支持的數(shù)據(jù)類(lèi)型非常多，選擇正確的數(shù)據(jù)類(lèi)型對(duì)于獲取高性能至關(guān)重要融求。不管存儲(chǔ)哪種類(lèi)型的數(shù)據(jù)咬像，下面幾個(gè)簡(jiǎn)單的原則都有助于做出更好的選擇。
- 更小的通常更好：一般情況下双肤，應(yīng)該盡量使用可以正確存儲(chǔ)數(shù)據(jù)的最小數(shù)據(jù)類(lèi)型施掏。簡(jiǎn)單就好：簡(jiǎn)單的數(shù)據(jù)類(lèi)型通常需要更少的CPU周期。例如茅糜，整數(shù)比字符操作代價(jià)更低七芭，因?yàn)樽址托?duì)規(guī)則（排序規(guī)則）使字符比較比整型比較復(fù)雜。
- 盡量避免NULL：通常情況下最好指定列為NOT NULL
九蔑赘、分區(qū)狸驳、分表、分庫(kù)
MySQL分區(qū)
一般情況下我們創(chuàng)建的表對(duì)應(yīng)一組存儲(chǔ)文件缩赛，使用MyISAM存儲(chǔ)引擎時(shí)是一個(gè).MYI和.MYD文件耙箍，使用Innodb存儲(chǔ)引擎時(shí)是一個(gè).ibd和.frm（表結(jié)構(gòu)）文件。
當(dāng)數(shù)據(jù)量較大時(shí)（一般千萬(wàn)條記錄級(jí)別以上）酥馍，MySQL的性能就會(huì)開(kāi)始下降辩昆，這時(shí)我們就需要將數(shù)據(jù)分散到多組存儲(chǔ)文件，保證其單個(gè)文件的執(zhí)行效率
能干嘛
- 邏輯數(shù)據(jù)分割
- 提高單一的寫(xiě)和讀應(yīng)用速度
- 提高分區(qū)范圍讀查詢的速度
- 分割數(shù)據(jù)能夠有多個(gè)不同的物理文件路徑
- 高效的保存歷史數(shù)據(jù)
怎么玩
首先查看當(dāng)前數(shù)據(jù)庫(kù)是否支持分區(qū)
- MySQL5.6以及之前版本：SHOW VARIABLES LIKE '%partition%';
- MySQL5.6：show plugins;
分區(qū)類(lèi)型及操作
- RANGE分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值旨袒，把多行分配給分區(qū)汁针。mysql將會(huì)根據(jù)指定的拆分策略术辐，,把數(shù)據(jù)放在不同的表文件上。相當(dāng)于在文件上,被拆成了小塊.但是,對(duì)外給客戶的感覺(jué)還是一張表施无，透明的辉词。按照 range 來(lái)分，就是每個(gè)庫(kù)一段連續(xù)的數(shù)據(jù)猾骡，這個(gè)一般是按比如時(shí)間范圍來(lái)的瑞躺，比如交易表啊，銷(xiāo)售表啊等兴想，可以根據(jù)年月來(lái)存放數(shù)據(jù)幢哨。可能會(huì)產(chǎn)生熱點(diǎn)問(wèn)題嫂便，大量的流量都打在最新的數(shù)據(jù)上了嘱么。range 來(lái)分，好處在于說(shuō)顽悼，擴(kuò)容的時(shí)候很簡(jiǎn)單。
- LIST分區(qū)：類(lèi)似于按RANGE分區(qū)几迄，每個(gè)分區(qū)必須明確定義蔚龙。它們的主要區(qū)別在于，LIST分區(qū)中每個(gè)分區(qū)的定義和選擇是基于某列的值從屬于一個(gè)值列表集中的一個(gè)值映胁，而RANGE分區(qū)是從屬于一個(gè)連續(xù)區(qū)間值的集合木羹。
- HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來(lái)進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算解孙。這個(gè)函數(shù)可以包含MySQL 中有效的坑填、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。hash 分發(fā)弛姜，好處在于說(shuō)脐瑰，可以平均分配每個(gè)庫(kù)的數(shù)據(jù)量和請(qǐng)求壓力；壞處在于說(shuō)擴(kuò)容起來(lái)比較麻煩廷臼，會(huì)有一個(gè)數(shù)據(jù)遷移的過(guò)程苍在，之前的數(shù)據(jù)需要重新計(jì)算 hash 值重新分配到不同的庫(kù)或表
- KEY分區(qū)：類(lèi)似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列荠商，且MySQL服務(wù)器提供其自身的哈希函數(shù)寂恬。必須有一列或多列包含整數(shù)值。
看上去分區(qū)表很帥氣莱没，為什么大部分互聯(lián)網(wǎng)還是更多的選擇自己分庫(kù)分表來(lái)水平擴(kuò)展咧初肉？
- 分區(qū)表，分區(qū)鍵設(shè)計(jì)不太靈活饰躲，如果不走分區(qū)鍵牙咏，很容易出現(xiàn)全表鎖
- 一旦數(shù)據(jù)并發(fā)量上來(lái)臼隔，如果在分區(qū)表實(shí)施關(guān)聯(lián)，就是一個(gè)災(zāi)難
- 自己分庫(kù)分表眠寿，自己掌控業(yè)務(wù)場(chǎng)景與訪問(wèn)模式躬翁，可控。分區(qū)表盯拱，研發(fā)寫(xiě)了一個(gè)sql盒发，都不確定mysql是怎么玩的，不太可控
?
隨著業(yè)務(wù)的發(fā)展狡逢，業(yè)務(wù)越來(lái)越復(fù)雜宁舰，應(yīng)用的模塊越來(lái)越多，總的數(shù)據(jù)量很大奢浑，高并發(fā)讀寫(xiě)操作均超過(guò)單個(gè)數(shù)據(jù)庫(kù)服務(wù)器的處理能力怎么辦蛮艰？
這個(gè)時(shí)候就出現(xiàn)了數(shù)據(jù)分片，數(shù)據(jù)分片指按照某個(gè)維度將存放在單一數(shù)據(jù)庫(kù)中的數(shù)據(jù)分散地存放至多個(gè)數(shù)據(jù)庫(kù)或表中雀彼。數(shù)據(jù)分片的有效手段就是對(duì)關(guān)系型數(shù)據(jù)庫(kù)進(jìn)行分庫(kù)和分表壤蚜。
區(qū)別于分區(qū)的是，分區(qū)一般都是放在單機(jī)里的徊哑，用的比較多的是時(shí)間范圍分區(qū)告喊，方便歸檔社牲。只不過(guò)分庫(kù)分表需要代碼實(shí)現(xiàn)埋市，分區(qū)則是mysql內(nèi)部實(shí)現(xiàn)结榄。分庫(kù)分表和分區(qū)并不沖突，可以結(jié)合使用梢莽。
