1.Mysql基本架構(gòu)圖
一.Innodb和MylSAM的區(qū)別
? ? 1.Innodb 支持事務(wù)弄唧，MylSam 不支持事務(wù)
? ? 2.Innodb支持外鍵现柠，Mylsam不支持
? ? 3.Innodb支持行鎖
? ? 4.Innodb刪除數(shù)據(jù)表，是一行一行刪除，Mylsam會(huì)重建
? ? 5.Mylsam支持全文索引采记，Innodb不支持
二.索引
索引的三大優(yōu)點(diǎn)：索引大大減少了服務(wù)器需要掃描的行數(shù)，索引可以幫助服務(wù)器避免排序和臨時(shí)表政勃，索引可以將隨機(jī)IO變成順序IO
????1.聚集索引
? ? ? ? ?? 定義：數(shù)據(jù)行的物理順序和列（主鍵）的邏輯順序相同唧龄，一個(gè)表中只能有一個(gè)聚集索引。葉子節(jié)點(diǎn)就是對(duì)應(yīng)的數(shù)據(jù)節(jié)點(diǎn)奸远。
?????????聚集索引就是按照每一張表的主鍵構(gòu)造的一棵B+樹(shù)既棺，同時(shí)葉子節(jié)點(diǎn)中存放的即是整張表的行記錄數(shù)據(jù)
? ? ? ?
2.非聚集索引（輔助索引）
? ? ? ? ? ? 定義：索引中索引的邏輯順序與磁盤(pán)上行的物理存儲(chǔ)順序不同，一個(gè)表中可以擁有多個(gè)非聚集索引
? ? ? ? ? ? 非聚集索引懒叛，葉子節(jié)點(diǎn)并不包含行記錄的全部數(shù)據(jù)丸冕，葉子節(jié)點(diǎn)除鍵值以外，每個(gè)葉子節(jié)點(diǎn)中的索引行還包含了一個(gè)書(shū)簽薛窥。指向了主鍵索引的主鍵胖烛，然后再通過(guò)主鍵索引來(lái)找到一個(gè)完整的行記錄
? ? ? ? ? 非聚集索引的二次查詢問(wèn)題
????????非聚集索引葉節(jié)點(diǎn)仍然是索引節(jié)點(diǎn)，只是有一個(gè)指針指向?qū)?yīng)的數(shù)據(jù)塊诅迷，此如果使用非聚集索引查詢佩番，而查詢列中包含了其他該索引沒(méi)有覆蓋的列，那么他還要進(jìn)行第二次的查詢罢杉，查詢節(jié)點(diǎn)上對(duì)應(yīng)的數(shù)據(jù)行的數(shù)據(jù)
舉個(gè)例子：
使用以下語(yǔ)句進(jìn)行查詢趟畏，不需要進(jìn)行二次查詢，直接就可以從非聚集索引的節(jié)點(diǎn)里面就可以獲取到查詢列的數(shù)據(jù)滩租。
????????????????????select id, username from t1 where username = '小明'?
????????????????????select username from t1 where username = '小明'
但是使用以下語(yǔ)句進(jìn)行查詢赋秀，就需要二次的查詢?nèi)カ@取原數(shù)據(jù)行的score：
????????????????????select username, score from t1 where username = '小明'
- 通過(guò)cardinality關(guān)鍵字來(lái)決定，建索引的必要性律想，如果關(guān)鍵字越小猎莲，則需考慮是否有必要建立這個(gè)索引
參考資料：https://blog.csdn.net/weigeshikebi/article/details/80214965
3.聯(lián)合索引
?? 1. 聯(lián)合索引指的是對(duì)表上的多個(gè)列進(jìn)行索引。
2.能夠使用聯(lián)合索引的情況
①全匹配
select * from t where a=xxx and b=xxx
②最左前綴匹配
對(duì)于單個(gè)的a列技即，也可以用到(a,b,c)聯(lián)合索引
select * from t where a=xxx
select * from t where a=xxx and b=xxx （可以)
select * from t where a=xxx and b=xxx and c=xxx（可以）
select * from t where b=xxx and c=xxx (則不行)
select * from t where b=xxx（不行）我們可以看到益眉，葉子節(jié)點(diǎn)上的b為1，2，1郭脂，4年碘，1，2不是有序的
③不能使用聯(lián)合索引
葉子節(jié)點(diǎn)上b的值為1,2,1,4,1,2展鸡，顯然不是排序的屿衅。
select * from t where b=xxx
同理，如果建立(a,b,c)索引莹弊，則下面的查詢都能用到索引涤久。
select * from t where a=xxx and b=xxx and c=xxx
select * from t where a=xxx and b=xxx
select * from t where a=xxx
select * from t where b=xxx and c=xxx
3.聯(lián)合索引可對(duì)第二個(gè)列進(jìn)行排序處理，減少一次filesort忍弛。
在聯(lián)合索引(a,b)中响迂，由于a相同的情況下b本來(lái)就是排序的，所以下面的查詢能夠用到(a,b)索引细疚，且不需要額外再進(jìn)行排序蔗彤。
select * from t where a=xxx order by b
同理，如果建立(a,b,c)索引疯兼，下面的查詢也能少一次fileSort然遏。
select * from t where a=xxx and b=xxx order by c
select * from t where b=xxx order by c
select * from t where a=xxx order by b
但是對(duì)于
select * from t where a=xxx order by c
則不行，c還需要再進(jìn)行一次排序
4.覆蓋索引
如果一個(gè)索引包含(或覆蓋)所有需要查詢的字段的值吧彪，稱為‘覆蓋索引
150多萬(wàn)的數(shù)據(jù)待侵，這么一個(gè)簡(jiǎn)單的語(yǔ)句：
慢查詢?nèi)罩纠锞尤缓芏嘤昧?秒的，Explain的結(jié)果是：
從Explain的結(jié)果可以看出姨裸，查詢已經(jīng)使用了索引秧倾，但為什么還這么慢？
分析：首先傀缩，該語(yǔ)句ORDER BY 使用了Using filesort文件排序中狂，查詢效率低；其次扑毡，查詢字段不在索引上，沒(méi)有使用覆蓋索引盛险，需要通過(guò)索引回表查詢瞄摊；也有數(shù)據(jù)分布的原因。
知道了原因苦掘，那么問(wèn)題就好解決了换帜。
解決方案：由于只需查詢uid字段，添加一個(gè)聯(lián)合索引便可以避免回表和文件排序鹤啡，利用覆蓋索引提升查詢速度惯驼，同時(shí)利用索引完成排序。
覆蓋索引：SQL只需要通過(guò)索引就可以返回查詢所需要的數(shù)據(jù)，而不必通過(guò)二級(jí)索引查到主鍵之后再去查詢數(shù)據(jù)祟牲。
我們?cè)貳xplain看一次：
Extra信息已經(jīng)有'Using Index'隙畜，表示已經(jīng)使用了覆蓋索引。經(jīng)過(guò)索引優(yōu)化之后说贝，線上的查詢基本不超過(guò)0.001秒议惰。
5.索引下推優(yōu)化（ICP優(yōu)化）-減少回表次數(shù)
而 MySQL 5.6 引入的索引下推優(yōu)化（index condition pushdown)， 可以在索引遍歷過(guò)程中乡恕，對(duì)索引中包含的字段先做判斷言询，直接過(guò)濾掉不滿足條件的記錄，減少回表次數(shù)傲宜。
圖 3 和圖 4运杭，是這兩個(gè)過(guò)程的執(zhí)行流程圖。
在圖 3 和 4 這兩個(gè)圖里面函卒，每一個(gè)虛線箭頭表示回表一次辆憔。
圖 3 中，在 (name,age) 索引里面我特意去掉了 age 的值谆趾，這個(gè)過(guò)程 InnoDB 并不會(huì)去看 age 的值躁愿，只是按順序把“name 第一個(gè)字是’張’”的記錄一條條取出來(lái)回表。因此沪蓬，需要回表 4 次彤钟。
圖 4 跟圖 3 的區(qū)別是，InnoDB 在 (name,age) 索引內(nèi)部就判斷了 age 是否等于 10跷叉，對(duì)于不等于 10 的記錄逸雹，直接判斷并跳過(guò)。在我們的這個(gè)例子中云挟，只需要對(duì) ID4梆砸、ID5 這兩條記錄回表取數(shù)據(jù)判斷，就只需要回表 2 次园欣。
6.MRR優(yōu)化--將隨機(jī)IO轉(zhuǎn)變?yōu)轫樞騃O提高效率
? ? 1.將查詢得到的輔助索引鍵值放入一個(gè)緩存當(dāng)中帖世，這時(shí)緩存中的數(shù)據(jù)是根據(jù)輔助索引鍵值排序的
? ? 2.將緩存中的鍵值，根據(jù)RowID（主鍵）進(jìn)行排序
? ? 3.根據(jù)RowID的排序順序來(lái)訪問(wèn)實(shí)際的數(shù)據(jù)文件沸枯。
沒(méi)有MRR之前：
在沒(méi)有MRR之前,或者沒(méi)有開(kāi)啟MRR特性時(shí)日矫，MySQL 針對(duì)基于輔助索引的查詢策略是這樣的：
selectnon_key_colfromtb where key_col=x;
MySQL 執(zhí)行查詢的偽代碼
第一步 先根據(jù)where條件中的輔助索引獲取輔助索引與主鍵的集合，結(jié)果集為rest绑榴。
select key_col,pk_col from tb where key_col=x order by key_col
第二步 通過(guò)第一步獲取的主鍵來(lái)獲取對(duì)應(yīng)的值哪轿。
? ? ??foreach pk_column valueinrest do:
select non_key_column from tb where pk_column=val
由于MySQL存儲(chǔ)數(shù)據(jù)的方式： 輔助索引的存儲(chǔ)順序并非與主鍵的順序一致，從圖中可以看出,根據(jù)輔助索引獲取的主鍵來(lái)訪問(wèn)表中的數(shù)據(jù)會(huì)導(dǎo)致隨機(jī)的IO .?不同主鍵不在同一個(gè)page 里面時(shí)必然導(dǎo)致多次IO 和隨機(jī)讀翔怎。
在使用MRR優(yōu)化特性的情況下窃诉，
MySQL 針對(duì)基于輔助索引的查詢策略是這樣的：
第一步 先根據(jù)where條件中的輔助索引獲取輔助索引與主鍵的集合rest
select key_col,pk_col from tb where key_col=x order by key_col;
第二步 將結(jié)果集rest放在buffer里面(read_rnd_buffer_size 大小直到buffer滿了)杨耙，然后對(duì)結(jié)果集rest按照pk_col排序，得到結(jié)果集是rest_sort
第三步 利用已經(jīng)排序過(guò)的結(jié)果集飘痛，訪問(wèn)表中的數(shù)據(jù)珊膜，此時(shí)是順序IO.
select non_key_col fromtb where pk_colin(rest_sort)
從圖示所看，MySQL 將根據(jù)輔助索引獲取的結(jié)果集根據(jù)主鍵進(jìn)行排序敦冬，將亂序化為有序辅搬，可以用主鍵順序訪問(wèn)基表，將隨機(jī)讀轉(zhuǎn)化為順序讀脖旱，多頁(yè)數(shù)據(jù)記錄可一次性讀入或根據(jù)此次的主鍵范圍分次讀入堪遂，以減少I(mǎi)O操作，提高查詢效率萌庆。
7.普通索引和唯一索引
MySQL中普通索引和唯一索引的區(qū)別詳解
一溶褪、查詢和更新上的區(qū)別
這兩類(lèi)索引在查詢能力上是沒(méi)差別的，主要考慮的是對(duì)更新性能的影響践险。建議盡量選擇普通索引猿妈。
查詢操作：
????????■ 普通索引
????????查找到第一個(gè)滿足條件的記錄后，繼續(xù)向后遍歷巍虫，直到第一個(gè)不滿足條件的記錄彭则。
????????■ 唯一索引
????????由于索引定義了唯一性，查找到第一個(gè)滿足條件的記錄后占遥，直接停止繼續(xù)檢索俯抖。
????????普通索引會(huì)多檢索一次，幾乎沒(méi)有影響瓦胎。因?yàn)?InnoDB 的數(shù)據(jù)是按照數(shù)據(jù)頁(yè)為單位進(jìn)行讀寫(xiě)的芬萍，需要讀取數(shù)據(jù)時(shí)，并不是直接從磁盤(pán)讀取記錄搔啊，而是先把數(shù)據(jù)頁(yè)讀到內(nèi)存柬祠，再去數(shù)據(jù)頁(yè)中檢索。
????????一個(gè)數(shù)據(jù)頁(yè)默認(rèn) 16 KB负芋，對(duì)于整型字段漫蛔，一個(gè)數(shù)據(jù)頁(yè)可以放近千個(gè) key，除非要讀取的數(shù)據(jù)在數(shù)據(jù)頁(yè)的最后一條記錄旧蛾，就需要再讀一個(gè)數(shù)據(jù)頁(yè)莽龟，這種情況很少，對(duì)CPU的消耗基本可以忽略了蚜点。
????????因此說(shuō)，在查詢數(shù)據(jù)方面拌阴，普通索引和唯一索引沒(méi)差別绍绘。
更新操作：
????????更新操作并不是直接對(duì)磁盤(pán)中的數(shù)據(jù)進(jìn)行更新，是先把數(shù)據(jù)頁(yè)從磁盤(pán)讀入內(nèi)存，再更新數(shù)據(jù)頁(yè)陪拘。
????????■ 普通索引
????????將數(shù)據(jù)頁(yè)從磁盤(pán)讀入內(nèi)存厂镇，更新數(shù)據(jù)頁(yè)。
????????■ 唯一索引
????????將數(shù)據(jù)頁(yè)從磁盤(pán)讀入內(nèi)存左刽，判斷是否唯一捺信，再更新數(shù)據(jù)頁(yè)。
????????由于 MySQL 中有個(gè) change buffer 的機(jī)制欠痴，會(huì)導(dǎo)致普通索引和唯一索引在更新上有一定的區(qū)別迄靠。
????????change buffer的作用是為了降低IO 操作，避免系統(tǒng)負(fù)載過(guò)高喇辽。change buffer將數(shù)據(jù)寫(xiě)入數(shù)據(jù)頁(yè)的過(guò)程掌挚，叫做merge。
????????如果需要更新的數(shù)據(jù)頁(yè)在內(nèi)存中時(shí)菩咨，會(huì)直接更新數(shù)據(jù)頁(yè)吠式；如果數(shù)據(jù)不在內(nèi)存中，會(huì)先將更新操作記入change buffer抽米，當(dāng)下一次讀取數(shù)據(jù)頁(yè)時(shí)特占，順帶merge到數(shù)據(jù)頁(yè)中，change buffer也有定期merge策略云茸。數(shù)據(jù)庫(kù)正常關(guān)閉的過(guò)程中是目，也會(huì)觸發(fā)merge。
????????對(duì)于唯一索引查辩，更新前需要判斷數(shù)據(jù)是否唯一（不能和表中數(shù)據(jù)重復(fù)）胖笛，如果數(shù)據(jù)頁(yè)在內(nèi)存中，就可以直接判斷并且更新宜岛，如果不在內(nèi)存中长踊，就需要去磁盤(pán)中讀出來(lái)，判斷一下是否唯一萍倡，是的話就更新身弊。change buffer是用不到的。即使數(shù)據(jù)頁(yè)不在內(nèi)存中列敲，還是要讀出來(lái)阱佛。
????????change buffer 用的是 buffer pool 里的內(nèi)存，因此不能無(wú)限增大戴而。change buffer 的大小凑术，可以通過(guò)參數(shù) innodb_change_buffer_max_size 來(lái)動(dòng)態(tài)設(shè)置。這個(gè)參數(shù)設(shè)置為 50 的時(shí)候所意，表示 change buffer 的大小最多只能占用 buffer pool 的 50%淮逊。
結(jié)論：唯一索引用不了change buffer催首，只有普通索引可以用。
八.如何給字符串加索引呢泄鹏？
針對(duì)字符串的索引郎任，一般會(huì)采用的是前綴索引，比如舉個(gè)例子备籽，有郵箱號(hào)列如QQ郵箱 975289275@qq.com為了舶治，那郵箱號(hào)重復(fù)的地方比如@qq.com我們就可以忽略，只對(duì)前面的@qq.com得前面項(xiàng)加前綴索引车猬，那么如何決定前綴索引的長(zhǎng)度呢霉猛？可以通過(guò)以下命令計(jì)算出這個(gè)列有多少不同的值，然后取區(qū)分度最大的長(zhǎng)度加索引诈唬。
????????select count(dinstinct left(email,length)) as l from table
其中l(wèi)ength 可以是1韩脏，2，3铸磅，4赡矢，5，6......
一般遵循索引選擇性原則阅仔，索引選擇性原則指的是 不重復(fù)的索引值 和 據(jù)表記錄總數(shù)的比值吹散，比值越高，性能越好
前綴索引八酒，遵循完整列的選擇性 count(distinc left(emial,length))/count(*) as from table 這個(gè)比值越高空民，則性能越好
2.剛剛看到一個(gè)很不錯(cuò)的方法，使用偽hahs索引羞迷，就是將該字符串維護(hù)一個(gè)用hash函數(shù)處理的列界轩，然后在該hash列上加索引即可，但是缺陷是要維護(hù)hash值
8.Hash索引的缺點(diǎn)
1衔瓮、不能避免讀取行
哈希索引只包含哈希值和行指針浊猾，而不存儲(chǔ)字段值，所以不能使用索引中的值來(lái)避免讀取行热鞍。不過(guò)葫慎，訪問(wèn)內(nèi)存中的行的速度很快，所以大部分情況下這一點(diǎn)對(duì)性能的影響并不明顯薇宠。
2偷办、無(wú)法用于排序
哈希索引數(shù)據(jù)并不是按照索引值順序存儲(chǔ)的，所以也就無(wú)法用于排序澄港。
3椒涯、無(wú)法使用部分索引列匹配查找
哈希索引也不支持部分索引列匹配查找，因?yàn)楣Ｋ饕冀K是使用索引列的全部?jī)?nèi)容來(lái)計(jì)算哈希值的回梧。例如废岂，在數(shù)據(jù)列（A,B）上建立哈希索引铡溪，如果查詢只有數(shù)據(jù)列A，則無(wú)法使用該索引泪喊。
4、只支持等值查找
哈希索引只支持等值比較查詢髓涯，包括=袒啼、IN()、<=>（注意<>和<=>是不同的操作）纬纪。也不支持任何范圍查詢蚓再，例如WHERE price>100。
5包各、存在Hash沖突
訪問(wèn)哈希索引的數(shù)據(jù)非痴觯快，除非有很多哈希沖突（不同的索引列值卻有相同的哈希值）问畅。當(dāng)出現(xiàn)哈希沖突的時(shí)候娃属，存儲(chǔ)引擎必須遍歷鏈表中所有的行指針，逐行進(jìn)行比較护姆，直到找到所有符合條件的行矾端。
同時(shí)，當(dāng)哈希沖突很多的時(shí)候卵皂，一些索引維護(hù)操作的代價(jià)也會(huì)很高秩铆。例如，如果在某個(gè)選擇性很低（哈希沖突很多）的列上建立哈希索引灯变，那么當(dāng)從表中刪除一行時(shí)殴玛，存儲(chǔ)引擎需要遍歷對(duì)應(yīng)哈希值的鏈表中的每一行，找到并刪除對(duì)應(yīng)行的引用添祸，沖突越多滚粟，代價(jià)越大。
9.B樹(shù)的缺點(diǎn)
1.每個(gè)節(jié)點(diǎn)中既要存索引信息膝捞，又要存其對(duì)應(yīng)的數(shù)據(jù)坦刀，如果數(shù)據(jù)很大，那么當(dāng)樹(shù)的體量很大時(shí)蔬咬，每次讀到內(nèi)存中的樹(shù)的信息就會(huì)不太夠鲤遥。
2.B樹(shù)遍歷整個(gè)樹(shù)的過(guò)程和二叉樹(shù)本質(zhì)上是一樣的，B樹(shù)相對(duì)二叉樹(shù)雖然提高了磁盤(pán)IO性能林艘，但并沒(méi)有解決遍歷元素效率低下的問(wèn)題盖奈。
3.范圍查詢的效率太低，沒(méi)有B+樹(shù)的高
