B-Tree結(jié)構(gòu)
B-Tree的數(shù)據(jù)結(jié)構(gòu)：
1.有一個(gè)大于1的正整數(shù)d 是B-Tree的度
2.有一個(gè)正整數(shù)h代表樹(shù)高
3..每個(gè)非葉子節(jié)點(diǎn)由n-1個(gè)key和n個(gè)指針組成，其中d<=n<=2d
4.每個(gè)葉子節(jié)點(diǎn)最少包含一個(gè)key和兩個(gè)指針遏暴，最多包含2d-1個(gè)key和2d個(gè)指針，葉節(jié)點(diǎn)的指針均為null
5.所有葉節(jié)點(diǎn)具有相同的深度猪叙，等于樹(shù)高h(yuǎn)橘沥。
6.key和指針互相間隔，節(jié)點(diǎn)兩端是指針
7.一個(gè)節(jié)點(diǎn)中的key從左到右非遞減排列桩撮。
8.每個(gè)指針要么為null炮沐，要么指向另外一個(gè)節(jié)點(diǎn)争群。
B+Tree結(jié)構(gòu)
通過(guò)圖我們可以看到 B+Tree的非葉子節(jié)點(diǎn)不存儲(chǔ)data，只存儲(chǔ)key大年。葉子節(jié)點(diǎn)存儲(chǔ)data查蓉，并且葉子節(jié)點(diǎn)沒(méi)有指針踢京。
B-/+Tree索引的性能分析
B-Tree中一次檢索最多需要h-1次I/O（根節(jié)點(diǎn)常駐內(nèi)存）帆疟，漸進(jìn)復(fù)雜度為O(h)=O(logdN)O(h)=O(logdN)蚁吝。一般實(shí)際應(yīng)用中，出度d是非常大的數(shù)字垦缅，通常超過(guò)100冲泥，因此h非常小（通常不超過(guò)3）
MYSQL 中MyISAM索引引擎特點(diǎn)
我們可以看到MyISAM使用B+Tree作為索引結(jié)構(gòu)壁涎，同時(shí)葉子節(jié)點(diǎn)存儲(chǔ)的是數(shù)據(jù)存放的地址凡恍。
在MyISAM中，主索引和輔助索引（Secondary key）在結(jié)構(gòu)上沒(méi)有任何區(qū)別怔球，只是主索引要求key是唯一的嚼酝，而輔助索引的key可以重復(fù)。如果我們?cè)贑ol2上建立一個(gè)輔助索引竟坛，則此索引的結(jié)構(gòu)如下圖所示
MyISAM的索引方式也叫做“非聚集”的闽巩，之所以這么稱呼是為了與InnoDB的聚集索引區(qū)分。（不過(guò)我猜測(cè)估計(jì)也是因?yàn)樗臄?shù)據(jù)沒(méi)有直接聚集在索引文件上 我自己亂說(shuō)的）
MYSQL 中InnoDB索引引擎特點(diǎn)
InnoDB的最大區(qū)別就是担汤，葉子節(jié)點(diǎn)直接存儲(chǔ)了數(shù)據(jù)內(nèi)容涎跨。也就是索引文件和數(shù)據(jù)文件在一起。這種索引叫做聚集索引
因?yàn)镮nnoDB的數(shù)據(jù)文件本身要按主鍵聚集崭歧，所以InnoDB要求表必須有主鍵（MyISAM可以沒(méi)有）隅很，如果沒(méi)有顯式指定，則MySQL系統(tǒng)會(huì)自動(dòng)選擇一個(gè)可以唯一標(biāo)識(shí)數(shù)據(jù)記錄的列作為主鍵驾荣，如果不存在這種列外构，則MySQL自動(dòng)為InnoDB表生成一個(gè)隱含字段作為主鍵，這個(gè)字段長(zhǎng)度為6個(gè)字節(jié)播掷，類型為長(zhǎng)整形
第二個(gè)與MyISAM索引的不同是InnoDB的輔助索引data域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址审编；輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄歧匈。
選用過(guò)長(zhǎng)的字段作為主鍵好還是不好垒酬？為什么？
不好件炉，因?yàn)槿绻x用過(guò)長(zhǎng)的字段作為主鍵勘究，那么輔助索引的葉子節(jié)點(diǎn)都是引用主鍵的，使得輔助索引過(guò)大斟冕。
InnoDB上采用自增字段做主鍵好不好口糕？為什么？
好 因?yàn)橥粋€(gè)葉子節(jié)點(diǎn)內(nèi)的記錄是按主鍵的順序存放的磕蛇。如果采用自增的主鍵景描，那么有新紀(jì)錄插入就可以按照節(jié)點(diǎn)的順序插入。這樣就會(huì)形成一個(gè)緊湊的索引結(jié)構(gòu)秀撇，近似順序填滿超棺。由于每次插入時(shí)也不需要移動(dòng)已有數(shù)據(jù)，因此效率很高呵燕，也不會(huì)增加很多開(kāi)銷在維護(hù)索引上棠绘。
如果使用非自增主鍵（如果身份證號(hào)或?qū)W號(hào)等），由于每次插入主鍵的值近似于隨機(jī)再扭，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁(yè)得中間某個(gè)位置：
此時(shí)MySQL不得不為了將新記錄插到合適位置而移動(dòng)數(shù)據(jù)氧苍，甚至目標(biāo)頁(yè)面可能已經(jīng)被回寫到磁盤上而從緩存中清掉，此時(shí)又要從磁盤上讀回來(lái)泛范，這增加了很多開(kāi)銷候引，同時(shí)頻繁的移動(dòng)、分頁(yè)操作造成了大量的碎片敦跌，得到了不夠緊湊的索引結(jié)構(gòu)澄干，后續(xù)不得不通過(guò)OPTIMIZE TABLE來(lái)重建表并優(yōu)化填充頁(yè)面。
聯(lián)合索引生效規(guī)則
組合索引的生效原則是 從前往后依次使用生效柠傍，如果中間某個(gè)索引沒(méi)有使用麸俘，那么斷點(diǎn)前面的索引部分起作用，斷點(diǎn)后面的索引沒(méi)有起作用惧笛；
比如
where a=3 and b=45 and c=5 .... 這種三個(gè)索引順序使用中間沒(méi)有斷點(diǎn)从媚，全部發(fā)揮作用；
where a=3 and c=5... 這種情況下b就是斷點(diǎn)患整，a發(fā)揮了效果拜效，c沒(méi)有效果
where b=3 and c=4... 這種情況下a就是斷點(diǎn)喷众，在a后面的索引都沒(méi)有發(fā)揮作用，這種寫法聯(lián)合索引沒(méi)有發(fā)揮任何效果紧憾；
where b=45 and a=3 and c=5 .... 這個(gè)跟第一個(gè)一樣到千，全部發(fā)揮作用，abc只要用上了就行赴穗，跟寫的順序無(wú)關(guān)
(0) select * from mytable where a=3 and b=5 and c=4;
abc三個(gè)索引都在where條件里面用到了憔四，而且都發(fā)揮了作用
(1) select * from mytable where c=4 and b=6 and a=3;
這條語(yǔ)句列出來(lái)只想說(shuō)明 mysql沒(méi)有那么笨，where里面的條件順序在查詢之前會(huì)被mysql自動(dòng)優(yōu)化般眉，效果跟上一句一樣
(2) select * from mytable where a=3 and c=7;
a用到索引了赵，b沒(méi)有用，所以c是沒(méi)有用到索引效果的
(3) select * from mytable where a=3 and b>7 and c=3;
a用到了甸赃，b也用到了柿汛，c沒(méi)有用到，這個(gè)地方b是范圍值埠对，也算斷點(diǎn)苛茂，只不過(guò)自身用到了索引
(4) select * from mytable where b=3 and c=4;
因?yàn)閍索引沒(méi)有使用，所以這里 bc都沒(méi)有用上索引效果
(5) select * from mytable where a>4 and b=7 and c=9;
a用到了 b沒(méi)有使用鸠窗，c沒(méi)有使用
(6) select * from mytable where a=3 order by b;
a用到了索引妓羊，b在結(jié)果排序中也用到了索引的效果，前面說(shuō)了稍计，a下面任意一段的b是排好序的
(7) select * from mytable where a=3 order by c;
a用到了索引躁绸，但是這個(gè)地方c沒(méi)有發(fā)揮排序效果，因?yàn)橹虚g斷點(diǎn)了臣嚣，使用 explain 可以看到 filesort
(8) select * from mytable where b=3 order by a;
b沒(méi)有用到索引净刮，排序中a也沒(méi)有發(fā)揮索引效果
MYSQL有哪些優(yōu)化技巧
1.避免使用select*，用什么取什么
2.當(dāng)只要一行數(shù)據(jù)時(shí)使用LIMIT 1
當(dāng)你查詢表的有些時(shí)候硅则，你已經(jīng)知道結(jié)果只會(huì)有一條結(jié)果淹父，單因?yàn)槟憧赡苄枰etch游標(biāo)，或是你也許會(huì)去檢查返回的記錄數(shù)怎虫。
在這種情況下暑认，加上LIMIT 1 可以增加性能。這樣一樣大审， MySQL數(shù)據(jù)庫(kù)引擎會(huì)在找到一條數(shù)據(jù)后停止搜索蘸际，而不是繼續(xù)往后查找下一條符合記錄的數(shù)據(jù)。
下面的示例徒扶，只是為了找一下是否有“中國(guó)”的用戶粮彤，很明顯，后面的會(huì)比前面的更有效率。（請(qǐng)注意导坟，第一條中是Select *屿良，第二條是Select 1）
// 沒(méi)有效率的：
$r = mysql_query("SELECT * FROM user WHERE country = 'China'");
if (mysql_num_rows($r) > 0) {
// ...
}
// 有效率的：
$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
if (mysql_num_rows($r) > 0) {
// ...
}
3.盡可能的使用 NOT NULL
不要以為 NULL 不需要空間，其需要額外的空間惫周，并且尘惧，在你進(jìn)行比較的時(shí)候，你的程序會(huì)更復(fù)雜闯两。 當(dāng)然褥伴，這里并不是說(shuō)你就不能使用NULL了谅将，現(xiàn)實(shí)情況是很復(fù)雜的漾狼，依然會(huì)有些情況下，你需要使用NULL值饥臂。
下面摘自MySQL自己的文檔
“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”
4.為經(jīng)常使用的搜索字段建索引
5.不要在數(shù)據(jù)很大的字段上建立索引
6.使用連接（JOIN）來(lái)代替子查詢(Sub-Queries)
連接（JOIN）.. 之所以更有效率一些逊躁，是因?yàn)镸ySQL不需要在內(nèi)存中創(chuàng)建臨時(shí)表來(lái)完成這個(gè)邏輯上的需要兩個(gè)步驟的查詢工作。
7.盡量在多個(gè)條件的時(shí)候隅熙，把會(huì)提取盡量少數(shù)據(jù)量的條件放在前面稽煤，減少后一個(gè)where條件的查詢時(shí)間。
MYSQL大數(shù)據(jù)量分頁(yè)優(yōu)化怎么做囚戚？
比如你要查詢 300w開(kāi)始后面10條數(shù)據(jù)酵熙；mysql會(huì)讀取300w加10條這么多的數(shù)據(jù)，只不過(guò) 過(guò)濾后返回最后10條而已３鄯弧Ｘ叶！
select * from table limit 3000000,10;
在查詢下一頁(yè)時(shí)把上一頁(yè)的行id作為參數(shù)傳遞給客戶端程序拳芙，然后sql就改成了
select * from table where id>3000000 limit 10;
這條語(yǔ)句執(zhí)行也是在毫秒級(jí)完成的察藐，id>300w其實(shí)就是讓mysql直接跳到這里了，不用依次在掃描全面所有的行
如果你的table的主鍵id是自增的舟扎，并且中間沒(méi)有刪除和斷點(diǎn)分飞，那么還有一種方式，比如100頁(yè)的10條數(shù)據(jù)
還有一種方法延遲關(guān)聯(lián):
把前面的select * 變成 select id 把拿到的id再去分別查詢其他字段
select table.* from table inner join ( select id from table limit 3000000,10 ) as tmp on tmp.id=table.id;
什么是索引選擇性?
索引的選擇性（Selectivity）睹限，是指不重復(fù)的索引值（也叫基數(shù)譬猫，Cardinality）與表記錄數(shù)（#T）的比值：
Index Selectivity = Cardinality / #T
顯然選擇性的取值范圍為(0, 1]，選擇性越高的索引價(jià)值越大羡疗，這是由B+Tree的性質(zhì)決定的删窒。
我是這樣理解的，因?yàn)槿绻@個(gè)比值很小 代表 表中的記錄大多數(shù)都是重復(fù)的顺囊。那么建立索引也不能起到區(qū)分這條數(shù)據(jù)和別的數(shù)據(jù)的作用肌索。在這一列上來(lái)說(shuō)，相反如果不重復(fù)的值很多，那么建立索引就能很快找到這條記錄了诚亚。
什么時(shí)候該建立索引 什么時(shí)候不應(yīng)該?
第一種情況是表記錄比較少晕换，例如一兩千條甚至只有幾百條記錄的表，沒(méi)必要建索引站宗，讓查詢做全表掃描就好了闸准。至于多少條記錄才算多，這個(gè)個(gè)人有個(gè)人的看法梢灭，我個(gè)人的經(jīng)驗(yàn)是以2000作為分界線夷家，記錄數(shù)不超過(guò) 2000可以考慮不建索引，超過(guò)2000條可以酌情考慮索引敏释。
另一種不建議建索引的情況是索引的選擇性較低
MYSQL數(shù)據(jù)庫(kù)事務(wù)四大特性
⑴ 原子性（Atomicity） 原子性是指事務(wù)包含的所有操作要么全部成功库快，要么全部失敗回滾，因此事務(wù)的操作如果成功就必須要完全應(yīng)用到數(shù)據(jù)庫(kù)钥顽，如果操作失敗則不能對(duì)數(shù)據(jù)庫(kù)有任何影響义屏。
⑵ 一致性（Consistency） 一致性是指事務(wù)必須使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變換到另一個(gè)一致性狀態(tài)，也就是說(shuō)一個(gè)事務(wù)執(zhí)行之前和執(zhí)行之后都必須處于一致性狀態(tài)蜂大。
拿轉(zhuǎn)賬來(lái)說(shuō)闽铐，假設(shè)用戶A和用戶B兩者的錢加起來(lái)一共是5000，那么不管A和B之間如何轉(zhuǎn)賬奶浦，轉(zhuǎn)幾次賬兄墅，事務(wù)結(jié)束后兩個(gè)用戶的錢相加起來(lái)應(yīng)該還得是5000，這就是事務(wù)的一致性澳叉。
⑶ 隔離性（Isolation） 隔離性是當(dāng)多個(gè)用戶并發(fā)訪問(wèn)數(shù)據(jù)庫(kù)時(shí)隙咸，比如操作同一張表時(shí)，數(shù)據(jù)庫(kù)為每一個(gè)用戶開(kāi)啟的事務(wù)耳高，不能被其他事務(wù)的操作所干擾扎瓶，多個(gè)并發(fā)事務(wù)之間要相互隔離。
即要達(dá)到這么一種效果：對(duì)于任意兩個(gè)并發(fā)的事務(wù)T1和T2泌枪，在事務(wù)T1看來(lái)概荷，T2要么在T1開(kāi)始之前就已經(jīng)結(jié)束，要么在T1結(jié)束之后才開(kāi)始碌燕，這樣每個(gè)事務(wù)都感覺(jué)不到有其他事務(wù)在并發(fā)地執(zhí)行误证。
關(guān)于事務(wù)的隔離性數(shù)據(jù)庫(kù)提供了多種隔離級(jí)別。
⑷ 持久性（Durability）
持久性是指一個(gè)事務(wù)一旦被提交了修壕，那么對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)的改變就是永久性的愈捅，即便是在數(shù)據(jù)庫(kù)系統(tǒng)遇到故障的情況下也不會(huì)丟失提交事務(wù)的操作。
MYSQL數(shù)據(jù)庫(kù)事務(wù)隔離級(jí)別以及對(duì)應(yīng)級(jí)別下能解決的問(wèn)題
MYSQL默認(rèn)是可重復(fù)讀
臟讀
就是在一個(gè)事務(wù)的處理過(guò)程中讀取到了另一個(gè)事務(wù)未提交的數(shù)據(jù)慈鸠。
比如：A向B轉(zhuǎn)賬100元蓝谨，然后查詢B賬戶多了100元，之后轉(zhuǎn)賬操作未提交發(fā)生回滾，再次查詢B賬戶錢又沒(méi)有了譬巫。
不可重復(fù)讀
一次事務(wù)范圍內(nèi)多次查詢同一個(gè)數(shù)據(jù)項(xiàng)咖楣，發(fā)現(xiàn)前后查詢的數(shù)值不一致。
幻讀
一次事務(wù)范圍內(nèi)多次查詢一個(gè)范圍的數(shù)據(jù)芦昔，發(fā)現(xiàn)前后查詢的數(shù)據(jù)條數(shù)不一樣诱贿。多了幾行或者少了幾行
函數(shù)依賴 完全函數(shù)依賴 部分函數(shù)依賴 傳遞函數(shù)依賴的概念
函數(shù)依賴
若在一張表中，在屬性（或?qū)傩越M）X的值確定的情況下咕缎，必定能確定屬性Y的值珠十，那么就可以說(shuō)Y函數(shù)依賴于X，寫作 X → Y
比如姓名函數(shù)依賴于學(xué)號(hào) 姓名→學(xué)號(hào) 因?yàn)橐粋€(gè)學(xué)號(hào)對(duì)應(yīng)一個(gè)姓名凭豪，但是反過(guò)來(lái)就不是焙蹭。同名的兩個(gè)學(xué)生，學(xué)號(hào)不同墅诡。
完全函數(shù)依賴
在一張表中壳嚎，若 X → Y桐智，且對(duì)于 X 的任何一個(gè)真子集（假如屬性組 X 包含超過(guò)一個(gè)屬性的話）末早，X ' → Y 不成立，那么我們稱 Y 對(duì)于 X 完全函數(shù)依賴
（學(xué)號(hào)说庭，課名） F→ 分?jǐn)?shù) （注：因?yàn)橥粋€(gè)的學(xué)號(hào)對(duì)應(yīng)的分?jǐn)?shù)不確定然磷，同一個(gè)課名對(duì)應(yīng)的分?jǐn)?shù)也不確定）
部分函數(shù)依賴
假如 Y 函數(shù)依賴于 X，但同時(shí) Y 并不完全函數(shù)依賴于 X刊驴，那么我們就稱 Y 部分函數(shù)依賴于 X姿搜，記作 X P→ Y
（學(xué)號(hào)，課名） P→ 姓名 比如姓名就只依賴于學(xué)號(hào)捆憎，并不是依賴于這個(gè)屬性組
傳遞函數(shù)依賴
如 Z 函數(shù)依賴于 Y舅柜，且 Y 函數(shù)依賴于 X （嚴(yán)格來(lái)說(shuō)還有一個(gè)X 不包含于Y，且 Y 不函數(shù)依賴于Z的前提條件）躲惰，那么我們就稱 Z 傳遞函數(shù)依賴于 X 致份，記作 X T→ Z
主屬性 非主屬性 碼的概念
碼
設(shè) K 為某表中的一個(gè)屬性或?qū)傩越M，若除 K 之外的所有屬性都完全函數(shù)依賴于 K（這個(gè)“完全”不要漏了）础拨，那么我們稱 K 為候選碼氮块，簡(jiǎn)稱為碼
例如：
對(duì)于表3，（學(xué)號(hào)诡宗、課名）這個(gè)屬性組就是碼滔蝉。該表中有且僅有這一個(gè)碼。（假設(shè)所有課沒(méi)有重名的情況）
主屬性
包含在任意一個(gè)碼中的屬性稱為主屬性
非主屬性
不包含在任何一個(gè)碼中的屬性稱為非主屬性
MYSQL三大范式
1NF
1NF的定義為：符合1NF的關(guān)系中的每個(gè)屬性都不可再分塔沃。表1所示的情況蝠引，就不符合1NF的要求。
1NF是所有關(guān)系型數(shù)據(jù)庫(kù)的最基本要求
2NF
2NF在1NF的基礎(chǔ)之上，消除了非主屬性對(duì)于碼的部分函數(shù)依賴
3NF
3NF在2NF的基礎(chǔ)之上螃概，消除了非主屬性對(duì)于碼的傳遞函數(shù)依賴
參考文章：
MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理
mysql優(yōu)化小技巧
mysql 大數(shù)據(jù)量分頁(yè)優(yōu)化
mysql 多列索引的生效規(guī)則
數(shù)據(jù)庫(kù)第一二三范式到底在說(shuō)什么边坤？
