數(shù)據(jù)庫(kù)三大范式是什么

第一范式：每個(gè)列都不可以再拆分。

第二范式：在第一范式的基礎(chǔ)上，非主鍵列完全依賴于主鍵，而不能是依賴于主鍵的一部分。

第三范式：在第二范式的基礎(chǔ)上，非主鍵列只依賴于主鍵，不依賴于其他非主鍵。

在設(shè)計(jì)數(shù)據(jù)庫(kù)表結(jié)構(gòu)的時(shí)候，要盡量遵守三大范式，如果不遵守，必須有足夠的理由。比如性能，事實(shí)上我們經(jīng)常會(huì)為了性能而妥協(xié)數(shù)據(jù)庫(kù)的設(shè)計(jì)。

引擎

MySQL存儲(chǔ)引擎MyISAM與InnoDB區(qū)別

存儲(chǔ)引擎Storage engine：MySQL中的數(shù)據(jù)、索引以及其他對(duì)象是如何存儲(chǔ)的，是一套文件系統(tǒng)的實(shí)現(xiàn)。

常用的存儲(chǔ)引擎有以下：

• Innodb引擎：Innodb引擎提供了對(duì)數(shù)據(jù)庫(kù)ACID事務(wù)的支持。并且還提供了行級(jí)鎖和外鍵的約束。它的設(shè)計(jì)的目標(biāo)就是處理大數(shù)據(jù)容量的數(shù)據(jù)庫(kù)系統(tǒng)。
• MyISAM引擎(原本MySQL的默認(rèn)引擎)：不提供事務(wù)的支持，也不支持行級(jí)鎖和外鍵。
• MEMORY引擎：所有的數(shù)據(jù)都在內(nèi)存中，數(shù)據(jù)的處理速度快，但是安全性不高。

MyISAM與InnoDB區(qū)別

	Innodb	MyISAM
存儲(chǔ)結(jié)構(gòu)	每張表都保存在同一個(gè)數(shù)據(jù)文件中	每張表被存放在三個(gè)文件：表定義文件、數(shù)據(jù)文件、索引文件
數(shù)據(jù)和索引存儲(chǔ)方式	數(shù)據(jù)和索引是集中存儲(chǔ)的，查詢時(shí)做到覆蓋索引會(huì)非常高效	數(shù)據(jù)和索引是分開存儲(chǔ)的，索引的葉子節(jié)點(diǎn)存儲(chǔ)的是行數(shù)據(jù)地址，需要再尋址一次才能得到數(shù)據(jù)
記錄存儲(chǔ)順序	按主鍵大小有序插入	按記錄插入順序保存
索引	聚簇索引	非聚簇索引
索引的實(shí)現(xiàn)方式	B+樹索引，Innodb 是索引組織表	B+樹索引，myisam 是堆表
全文索引	不支持	支持
哈希索引	支持	不支持
外鍵	支持	不支持
事務(wù)	支持	不支持
鎖粒度（鎖是避免資源爭(zhēng)用的一個(gè)機(jī)制，MySQL鎖對(duì)用戶幾乎是透明的）	行級(jí)鎖定、表級(jí)鎖定，鎖定力度越小并發(fā)能力越高	表級(jí)鎖定
SELECT		MyISAM更優(yōu)
select count(*)		myisam更快，因?yàn)閙yisam內(nèi)部維護(hù)了一個(gè)計(jì)數(shù)器，可以直接調(diào)取。
INSERT、UPDATE、DELETE	InnoDB更優(yōu)

存儲(chǔ)引擎選擇

MyISAM：適用于管理非事務(wù)表，它提供高速存儲(chǔ)和檢索， 以及全文搜索能力的場(chǎng)景。比如博客系統(tǒng)、新聞門戶網(wǎng)站。

InnoDB：適用于更新操作頻繁，或者要保證數(shù)據(jù)的完整性，并發(fā)量高，支持事務(wù)和外鍵的場(chǎng)景。比如OA自動(dòng)化辦公系統(tǒng)。

如果沒有特別的需求，使用默認(rèn)的Innodb即可。

索引

什么是索引？

索引是一種數(shù)據(jù)結(jié)構(gòu)，是數(shù)據(jù)庫(kù)管理系統(tǒng)中一個(gè)排序的數(shù)據(jù)結(jié)構(gòu)，以協(xié)助快速查詢數(shù)據(jù)庫(kù)表中數(shù)據(jù)。索引的實(shí)現(xiàn)通常使用B+樹或hash表。

更通俗的說，索引就相當(dāng)于目錄。為了方便查找書中的內(nèi)容，通過對(duì)內(nèi)容建立索引形成目錄。

索引有哪些優(yōu)缺點(diǎn)？

索引的優(yōu)點(diǎn)

• 可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。
• 通過使用索引，可以在查詢的過程中，使用優(yōu)化器，提高系統(tǒng)的性能。

索引的缺點(diǎn)

• 時(shí)間方面：創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，具體地，當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，會(huì)降低增/刪/改的執(zhí)行效率；
• 空間方面：索引需要占物理空間。

索引有哪幾種類型？

主鍵索引：數(shù)據(jù)列不允許重復(fù)，不允許為NULL，一個(gè)表只能有一個(gè)主鍵。
唯一索引：數(shù)據(jù)列不允許重復(fù)，允許為NULL值，一個(gè)表允許多個(gè)列創(chuàng)建唯一索引。

• 可以通過 ALTER TABLE table_name ADD UNIQUE (column); 創(chuàng)建唯一索引
• 可以通過 ALTER TABLE table_name ADD UNIQUE (column1,column2); 創(chuàng)建唯一組合索引

普通索引：基本的索引類型，沒有唯一性的限制，允許為NULL值，一個(gè)表允許多個(gè)列創(chuàng)建普通索引。

• 可以通過ALTER TABLE table_name ADD INDEX index_name (column);創(chuàng)建普通索引
• 可以通過ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);創(chuàng)建組合索引

全文索引：是目前搜索引擎使用的一種關(guān)鍵技術(shù)，MyISAM存儲(chǔ)引擎才有全文索引。

• 可以通過ALTER TABLE table_name ADD FULLTEXT (column);創(chuàng)建全文索引

索引的數(shù)據(jù)結(jié)構(gòu)（B+樹，Hash）

在MySQL中使用較多的索引有Hash索引，B+樹索引等，索引的數(shù)據(jù)結(jié)構(gòu)和具體存儲(chǔ)引擎的實(shí)現(xiàn)有關(guān)，而我們經(jīng)常使用的InnoDB存儲(chǔ)引擎，默認(rèn)索引實(shí)現(xiàn)為：B+樹索引。對(duì)于哈希索引來說，底層的數(shù)據(jù)結(jié)構(gòu)就是哈希表，因此在絕大多數(shù)需求為單條記錄等值查詢的時(shí)候，可以選擇哈希索引，查詢性能最快；其余大部分場(chǎng)景，建議選擇B+樹索引。

1）B+樹索引

MySQL通過存儲(chǔ)引擎存取數(shù)據(jù)，基本上90%的人用的就是InnoDB了，按照實(shí)現(xiàn)方式分，InnoDB的索引類型目前只有兩種：BTREE（B樹）索引和HASH索引。B樹索引是MySQL數(shù)據(jù)庫(kù)中使用最頻繁的索引類型，基本所有存儲(chǔ)引擎都支持BTree索引。通常我們說的索引不出意外指的就是（B樹）索引（實(shí)際是用B+樹實(shí)現(xiàn)的，因?yàn)樵诓榭幢硭饕龝r(shí)，mysql一律打印BTREE，所以簡(jiǎn)稱為B樹索引）

B+樹數(shù)據(jù)結(jié)構(gòu)

眾所周知，一顆傳統(tǒng)的M階B+樹需要滿足以下幾個(gè)要求：

• 從根節(jié)點(diǎn)到葉節(jié)點(diǎn)的所有路徑都具有相同的長(zhǎng)度
• 所有數(shù)據(jù)信息都存儲(chǔ)在葉子節(jié)點(diǎn)，非葉子節(jié)點(diǎn)僅作為葉節(jié)點(diǎn)的索引存在
• 葉子節(jié)點(diǎn)通過指針連在一起
• 根節(jié)點(diǎn)至少擁有兩個(gè)子樹
• 每個(gè)樹節(jié)點(diǎn)最多擁有M個(gè)子樹
• 每個(gè)樹節(jié)點(diǎn)(除了根節(jié)點(diǎn))擁有至少M(fèi)/2個(gè)子樹

B+樹是為了磁盤及其他存儲(chǔ)輔助設(shè)備而設(shè)計(jì)的一種平衡查找樹(不是二叉樹)，在B+樹中，所有記錄的節(jié)點(diǎn)按大小順序存放在同一層的葉節(jié)點(diǎn)中，各葉子節(jié)點(diǎn)用指針進(jìn)行連接，而B+樹索引本質(zhì)上就是B+樹在數(shù)據(jù)庫(kù)中的實(shí)現(xiàn)，與純粹的B+樹數(shù)據(jù)結(jié)構(gòu)還是有點(diǎn)區(qū)別。

B+樹的一些特性：

1、B+樹中的B不是代表的二叉（Binary） ，而是代表平衡（Balance），因?yàn)锽+樹是從最早的平衡二叉樹演化而來，但是B+樹不是一個(gè)二叉樹。

2、B+樹是為磁盤或其他直接存取輔助設(shè)備設(shè)計(jì)的一種平衡查找樹，在B+樹中，所有的記錄節(jié)點(diǎn)都是按照鍵值大小順序存在同一層的葉子節(jié)點(diǎn)，由葉子節(jié)點(diǎn)指針進(jìn)行相連。

3、B+樹在數(shù)據(jù)庫(kù)中的特點(diǎn)就是高扇出，因此在數(shù)據(jù)庫(kù)中B+樹的高度一般都在2_{4層，這也就是說查找一個(gè)鍵值記錄時(shí)，最多只需要2到4次IO，當(dāng)前的機(jī)械硬盤每秒至少可以有100次IO，2}4次IO意味著查詢時(shí)間只需要0.02~0.04秒。

4、B+樹索引并不能找到一個(gè)給定鍵值的具體行，B+樹索引能找到的只是被查找的鍵值所在行的頁(yè)，然后數(shù)據(jù)庫(kù)把頁(yè)讀到內(nèi)存，再內(nèi)存中進(jìn)行查找，最后找到要查找的數(shù)據(jù)。

5、數(shù)據(jù)庫(kù)中B+樹索引可以分為，聚簇索引和非聚簇索引，但是不管是聚簇索引還是非聚簇索引，其內(nèi)部都是B+樹實(shí)現(xiàn)的，即高度是平衡的，葉子節(jié)點(diǎn)存放著所有的數(shù)據(jù)，聚簇索引和非聚簇索引不同的是，葉子節(jié)點(diǎn)是否存儲(chǔ)的是一整行信息。每張表只能有一個(gè)聚簇索引。

6、B+樹的每個(gè)數(shù)據(jù)頁(yè)（葉子節(jié)點(diǎn)）是通過一個(gè)雙向鏈表進(jìn)行鏈接，數(shù)據(jù)頁(yè)上的數(shù)據(jù)的順序是按照主鍵順序存儲(chǔ)的。

2）哈希索引

簡(jiǎn)要說下，類似于數(shù)據(jù)結(jié)構(gòu)中簡(jiǎn)單實(shí)現(xiàn)的HASH表（散列表）一樣，當(dāng)我們?cè)贛ySQL中用哈希索引時(shí)，主要就是通過Hash算法（常見的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ǔ)。當(dāng)然這只是簡(jiǎn)略模擬圖。

數(shù)據(jù)庫(kù)為什么使用B+樹而不是B樹

• B+樹的葉子節(jié)點(diǎn)存儲(chǔ)了所有的數(shù)據(jù)，非葉子節(jié)點(diǎn)中存儲(chǔ)的是比較關(guān)鍵字。而B樹所有的節(jié)點(diǎn)都會(huì)存儲(chǔ)數(shù)據(jù)。B+樹的葉子節(jié)點(diǎn)之間存在一個(gè)指針連接，B樹不存在指針連接。B+樹這種設(shè)計(jì)結(jié)構(gòu)能帶來什么好處呢？B+樹所有的數(shù)據(jù)都存儲(chǔ)在葉子節(jié)點(diǎn)，那么順著葉子節(jié)點(diǎn)從左往右即可完成對(duì)數(shù)據(jù)的遍歷，極大了簡(jiǎn)化了排序操作。這也是mysql設(shè)計(jì)索引是采用B+樹的原因，不僅僅能方便查找，而且有助于排序，在mysql的索引中葉子節(jié)點(diǎn)之間數(shù)雙向鏈表可正反遍歷，更加靈活；

• B樹只適合隨機(jī)檢索，而B+樹同時(shí)支持隨機(jī)檢索和順序檢索；

• B+樹空間利用率更高，可減少I/O次數(shù)，磁盤讀寫代價(jià)更低。一般來說，索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)的磁盤上。這樣的話，索引查找過程中就要產(chǎn)生磁盤I/O消耗。B+樹的內(nèi)部結(jié)點(diǎn)并沒有指向關(guān)鍵字具體信息的指針，只是作為索引使用，其內(nèi)部結(jié)點(diǎn)比B樹小，盤塊能容納的結(jié)點(diǎn)中關(guān)鍵字?jǐn)?shù)量更多，一次性讀入內(nèi)存中可以查找的關(guān)鍵字也就越多，相對(duì)的，IO讀寫次數(shù)也就降低了。而IO讀寫次數(shù)是影響索引檢索效率的最大因素；

• B+樹的查詢效率更加穩(wěn)定。B樹搜索有可能會(huì)在非葉子結(jié)點(diǎn)結(jié)束，越靠近根節(jié)點(diǎn)的記錄查找時(shí)間越短，只要找到關(guān)鍵字即可確定記錄的存在，其性能等價(jià)于在關(guān)鍵字全集內(nèi)做一次二分查找。而在B+樹中，順序檢索比較明顯，隨機(jī)檢索時(shí)，任何關(guān)鍵字的查找都必須走一條從根節(jié)點(diǎn)到葉節(jié)點(diǎn)的路，所有關(guān)鍵字的查找路徑長(zhǎng)度相同，導(dǎo)致每一個(gè)關(guān)鍵字的查詢效率相當(dāng)。

• B-樹在提高了磁盤IO性能的同時(shí)并沒有解決元素遍歷的效率低下的問題。B+樹的葉子節(jié)點(diǎn)使用指針順序連接在一起，只要遍歷葉子節(jié)點(diǎn)就可以實(shí)現(xiàn)整棵樹的遍歷。而且在數(shù)據(jù)庫(kù)中基于范圍的查詢是非常頻繁的，而B樹不支持這樣的操作。

• 增刪文件（節(jié)點(diǎn)）時(shí)，效率更高。因?yàn)锽+樹的葉子節(jié)點(diǎn)包含所有關(guān)鍵字，并以有序的鏈表結(jié)構(gòu)存儲(chǔ)，這樣可很好提高增刪效率。

索引算法有哪些？

索引算法有 BTree算法和Hash算法

BTree算法

BTree是最常用的mysql數(shù)據(jù)庫(kù)索引算法，也是mysql默認(rèn)的算法。因?yàn)樗粌H可以被用在=,>,>=,<,<=和between這些比較操作符上，而且還可以用于like操作符，只要它的查詢條件是一個(gè)不以通配符開頭的常量， 例如：

-- 只要它的查詢條件是一個(gè)不以通配符開頭的常量
select * from user where name like 'jack%';
-- 如果一通配符開頭，或者沒有使用常量，則不會(huì)使用索引，例如：
select * from user where name like '%jack';

Hash算法

Hash算法只能用于對(duì)等比較，例如=,<=>（相當(dāng)于=）操作符。由于是一次定位數(shù)據(jù)，不像BTree索引需要從根節(jié)點(diǎn)到枝節(jié)點(diǎn)，最后才能訪問到葉子節(jié)點(diǎn)這樣多次IO訪問，所以檢索效率遠(yuǎn)高于BTree索引。

創(chuàng)建索引的原則？索引設(shè)計(jì)的原則？

索引雖好，但也不是無限制的使用，最好符合以下幾個(gè)原則

1. 為常作為查詢條件的字段建立索引，where子句中的列，或者連接子句中指定的列

2. 為經(jīng)常需要排序、分組操作的字段建立索引

3. 更新頻繁字段不適合創(chuàng)建索引

4. 不能有效區(qū)分?jǐn)?shù)據(jù)的列不適合做索引列(如性別，男女未知，最多也就三種，區(qū)分度實(shí)在太低)

5. 對(duì)于定義為text、image和bit的數(shù)據(jù)類型的列不要建立索引

6. 最左前綴原則，就是最左邊的優(yōu)先。指的是聯(lián)合索引中，優(yōu)先走最左邊列的索引。對(duì)于多個(gè)字段的聯(lián)合索引，如 index(a,b,c) 聯(lián)合索引，則相當(dāng)于創(chuàng)建了 a 單列索引，(a,b)聯(lián)合索引，和(a,b,c)聯(lián)合索引（但并不是建立了多個(gè)索引樹）。mysql會(huì)一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。使用短索引，如果對(duì)長(zhǎng)字符串列進(jìn)行索引，應(yīng)該指定一個(gè)前綴長(zhǎng)度，這樣能夠節(jié)省大量索引空間

7. 非空字段：應(yīng)該指定列為NOT NULL，除非你想存儲(chǔ)NULL。在mysql中，含有空值的列很難進(jìn)行查詢優(yōu)化，因?yàn)樗鼈兪沟盟饕?、索引的統(tǒng)計(jì)信息以及比較運(yùn)算更加復(fù)雜。你應(yīng)該用0、一個(gè)特殊的值或者一個(gè)空串代替空值

8. 不要過度索引。索引需要額外的磁盤空間，并降低寫操作的性能。在修改表內(nèi)容的時(shí)候，索引會(huì)進(jìn)行更新甚至重構(gòu)，索引列越多，這個(gè)時(shí)間就會(huì)越長(zhǎng)

什么情況使用了索引，查詢還是慢

• 索引全表掃描
• 索引過濾性不好
• 頻繁回表的開銷

MySQL使用自增主鍵的好處

1. 自增主鍵按順序存放，增刪數(shù)據(jù)速度快，對(duì)于檢索非常有利；
2. 數(shù)字型，占用空間小，易排序；
3. 使用整形才可以使用AUTO_INCREAMENT，不用擔(dān)心主鍵重復(fù)問題。

什么是聚簇索引？何時(shí)使用聚簇索引與非聚簇索引

• 聚簇索引：將數(shù)據(jù)與索引放到了一塊，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)存儲(chǔ)了行數(shù)據(jù)，找到索引也就找到了數(shù)據(jù)
• 非聚簇索引：將數(shù)據(jù)與索引分開存儲(chǔ)，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)存儲(chǔ)的是行數(shù)據(jù)的地址

聚簇索引的優(yōu)點(diǎn)

• 數(shù)據(jù)訪問更快。聚族索引將索引和數(shù)據(jù)保存在同一個(gè)B+樹中，因此從聚族索引中獲取數(shù)據(jù)通常比非聚族索引中查找更快。
• 當(dāng)你需要取出一定范圍內(nèi)的數(shù)據(jù)時(shí)，用聚簇索引也比用非聚簇索引好。
• 使用覆蓋索引掃描的查詢可以直接使用節(jié)點(diǎn)中的主鍵值。

聚簇索引的缺點(diǎn)

• 插入速度嚴(yán)重依賴于插入順序，按照主鍵的順序插入是最快的方式，否則將會(huì)出現(xiàn)頁(yè)分裂，嚴(yán)重影響性能。因此，對(duì)于InnoDB表，我們一般都會(huì)定義一個(gè)自增的ID列作為主鍵。
• 更新主鍵的代價(jià)很高，因?yàn)閷?huì)導(dǎo)致被更新的行移動(dòng)。因此，對(duì)于InnoDB表，我們一般定義主鍵為不可更新。
• 通過輔助索引訪問需要兩次索引查找，第一次找到主鍵值，第二次根據(jù)主鍵值找到行數(shù)據(jù)。

幾個(gè)概念

• 對(duì)于普通索引，如 name 字段，則需要根據(jù) name 字段的索引樹（非聚簇索引）找到葉子節(jié)點(diǎn)對(duì)應(yīng)的主鍵，然后再通過主鍵去主鍵索引樹查詢一遍，才可以得到要找的記錄，這就叫回表查詢。先定位主鍵值，再定位行記錄，它的性能較掃描一遍索引樹的效率更低

• InnoDB的行鎖是建立在索引的基礎(chǔ)之上的，行鎖鎖的是索引，不是數(shù)據(jù)，所以提高并發(fā)寫的能力要在查詢字段添加索引

• 主索引和輔助索引：主索引就是主鍵索引，輔助索引就是根據(jù)業(yè)務(wù)需要，自己設(shè)置的普通的非主鍵的索引。這個(gè)在Myisam里面區(qū)別不大，但是在Innodb的時(shí)候差別很大

• 聚簇索引：Innodb的主索引采用的是聚簇索引，一個(gè)表只能有1個(gè)聚簇索引，因?yàn)楸頂?shù)據(jù)存儲(chǔ)的物理位置是唯一的。聚簇索引的value存的就是真實(shí)的數(shù)據(jù)，不是數(shù)據(jù)的地址。主索引樹里面包含了真實(shí)的數(shù)據(jù)。key是主鍵值，value值就是data，key值按照B+樹的規(guī)則分散排布的葉子節(jié)點(diǎn)。

• 非聚簇索引：Myisam的主索引和輔助索引都采用的是非聚簇索引，索引和表數(shù)據(jù)是分離的，索引的value值存儲(chǔ)的是行數(shù)據(jù)的地址。

• Innodb的索引：主索引采用聚簇索引，葉子節(jié)點(diǎn)的value值，直接存儲(chǔ)的真實(shí)的數(shù)據(jù)。輔助索引是非聚簇索引，value值指向主索引的位置。所以在Innodb中，根據(jù)輔助索引查詢值需要遍歷2次B+樹，同時(shí)主鍵的長(zhǎng)度越短越好，越短輔助索引的value值就越小。Innodb中根據(jù)主鍵進(jìn)行范圍查詢，會(huì)特別快。

• Myisam的索引：主索引和輔助索引都是非聚簇索引

• B+樹：不管是什么索引，在mysql中的數(shù)據(jù)結(jié)構(gòu)都是B+樹的結(jié)構(gòu)，可以充分利用數(shù)據(jù)塊，來減少IO查詢的次數(shù)，提升查詢的效率。一個(gè)數(shù)據(jù)塊data里面，存儲(chǔ)了很多個(gè)相鄰key的value值，所有的非葉子節(jié)點(diǎn)都不存儲(chǔ)數(shù)據(jù)，都是指針。

• mysql采用B+樹的優(yōu)點(diǎn)：IO讀取次數(shù)少（每次都是頁(yè)讀取），范圍查找更快捷（相鄰頁(yè)之間有指針）

聯(lián)合索引是什么？組合索引是什么？

MySQL可以使用多個(gè)字段組合建立一個(gè)索引，叫做聯(lián)合索引。在聯(lián)合索引中，如果想要命中索引，需要按照建立索引時(shí)的字段順序挨個(gè)使用，否則無法命中索引。

聯(lián)合索引數(shù)據(jù)結(jié)構(gòu)和實(shí)現(xiàn)原理，使用聯(lián)合索引是怎么進(jìn)行查詢的

假設(shè)，我們對(duì)(a,b)字段建立索引，那么入下圖所示

如上圖所示他們是按照a來進(jìn)行排序，在a相等的情況下，才按b來排序。

因此，我們可以看到a是有序的1，1，2，2，3，3。而b是一種全局無序，局部相對(duì)有序狀態(tài)！什么意思呢？

從全局來看，b的值為1，2，1，4，1，2，是無序的，因此直接執(zhí)行b = 2這種查詢條件沒有辦法利用索引。

從局部來看，當(dāng)a的值確定的時(shí)候，b是有序的。例如a = 1時(shí)，b值為1，2是有序的狀態(tài)。當(dāng)a=2時(shí)候，b的值為1,4也是有序狀態(tài)。因此，你執(zhí)行a = 1 and b = 2是a,b字段能用到索引的。而你執(zhí)行a > 1 and b = 2時(shí)，a字段能用到索引，b字段用不到索引。因?yàn)閍的值此時(shí)是一個(gè)范圍，不是固定的，在這個(gè)范圍內(nèi)b值不是有序的，因此b字段用不上索引。

綜上所示，最左匹配原則，在遇到范圍查詢的時(shí)候，就會(huì)停止匹配。

什么是最左前綴原則？什么是最左匹配原則？為什么需要注意聯(lián)合索引中的順序？

• 最左前綴原則，就是最左邊的優(yōu)先。指的是聯(lián)合索引中，優(yōu)先走最左邊列的索引。對(duì)于多個(gè)字段的聯(lián)合索引，如 index(a,b,c) 聯(lián)合索引，則相當(dāng)于創(chuàng)建了 a 單列索引，(a,b)聯(lián)合索引，和(a,b,c)聯(lián)合索引（但并不是建立了多個(gè)索引樹）。mysql會(huì)一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

• =和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會(huì)幫你優(yōu)化成索引可以識(shí)別的形式。

• 如果建立的索引順序是 (a,b) 那么直接采用 where b = 5 這種查詢條件是無法利用到索引的，這一條最能體現(xiàn)最左匹配的特性。

事務(wù)

什么是數(shù)據(jù)庫(kù)事務(wù)？

事務(wù)是邏輯上的一組操作，要么都執(zhí)行，要么都不執(zhí)行。

事務(wù)的四大特性(ACID)介紹一下?

關(guān)系性數(shù)據(jù)庫(kù)需要遵循ACID規(guī)則，具體內(nèi)容如下：

特性	說明
原子性 Atomic	事務(wù)是最小的執(zhí)行單位，不允許分割。事務(wù)包含的所有操作要么全部成功，要么全部失敗回滾。
一致性 Consistency	事務(wù)執(zhí)行之前和執(zhí)行之后都必須處于一致性狀態(tài)。舉例：拿轉(zhuǎn)賬來說，假設(shè)用戶A和用戶B兩者的錢加起來一共是5000，那么不管A和B之間如何轉(zhuǎn)賬，轉(zhuǎn)幾次賬，事務(wù)結(jié)束后兩個(gè)用戶的錢相加起來應(yīng)該還得是5000，這就是事務(wù)的一致性。
隔離性 Isolation	隔離性是當(dāng)多個(gè)用戶并發(fā)訪問數(shù)據(jù)庫(kù)時(shí)，比如操作同一張表時(shí)，數(shù)據(jù)庫(kù)為每一個(gè)用戶開啟的事務(wù)，不能被其他事務(wù)的操作所干擾，多個(gè)并發(fā)事務(wù)之間是相互隔離的。數(shù)據(jù)庫(kù)規(guī)定了多種事務(wù)隔離級(jí)別，不同的隔離級(jí)別對(duì)應(yīng)不同的干擾程度。隔離級(jí)別越高，數(shù)據(jù)一致性越好，但并發(fā)性越差。
持久性 Durability	持久性是指一個(gè)事務(wù)一旦被提交了，那么對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)的改變就是永久性的，即便是在數(shù)據(jù)庫(kù)系統(tǒng)遇到故障的情況下，也不會(huì)丟失提交事務(wù)的操作。

什么是臟讀？不可重復(fù)讀？幻讀？

• 臟讀(Dirty Read)：一個(gè)事務(wù)讀取到另外一個(gè)事務(wù)未提交的數(shù)據(jù)。舉例：一個(gè)事務(wù)1讀取了被另一個(gè)事務(wù)2修改但還未提交的數(shù)據(jù)。由于某種異常事務(wù)2回滾，則事務(wù)1讀取的是無效數(shù)據(jù)。

• 不可重復(fù)讀(Non-repeatable read)：一個(gè)事務(wù)讀取同一條記錄2次，得到的結(jié)果不一致。這可能是兩次查詢過程中間，另一個(gè)事務(wù)更新了這條記錄。

• 幻讀(Phantom Read)：幻讀發(fā)生在兩個(gè)完全相同的查詢，得到的結(jié)果不一致。這可能是兩次查詢過程中間，另一個(gè)事務(wù)增加或者減少了行記錄。

不可重復(fù)度和幻讀區(qū)別

不可重復(fù)讀的重點(diǎn)是修改，幻讀的重點(diǎn)在于新增或者刪除。

什么是事務(wù)的隔離級(jí)別？MySQL的默認(rèn)的隔離級(jí)別是什么？

為了達(dá)到事務(wù)的四大特性，數(shù)據(jù)庫(kù)定義了4種不同的事務(wù)隔離級(jí)別，由低到高依次為Read uncommitted、Read committed、Repeatable read、Serializable，后三個(gè)級(jí)別可以逐個(gè)解決臟讀、不可重復(fù)讀、幻讀這幾類問題。

隔離級(jí)別	臟讀	不可重復(fù)讀	幻讀
READ-UNCOMMITTED	會(huì)出現(xiàn)	會(huì)出現(xiàn)	會(huì)出現(xiàn)
READ-COMMITTED	不會(huì)出現(xiàn)	會(huì)出現(xiàn)	會(huì)出現(xiàn)
REPEATABLE-READ	不會(huì)出現(xiàn)	不會(huì)出現(xiàn)	會(huì)出現(xiàn)
SERIALIZABLE	不會(huì)出現(xiàn)	不會(huì)出現(xiàn)	不會(huì)出現(xiàn)

SQL 標(biāo)準(zhǔn)定義了四個(gè)隔離級(jí)別

• READ-UNCOMMITTED(讀未提交)：最低的隔離級(jí)別，一個(gè)事務(wù)可以讀取另一個(gè)事務(wù)更新但未提交的數(shù)據(jù)?？赡軙?huì)導(dǎo)致臟讀、不可重復(fù)讀或幻讀。
• READ-COMMITTED(讀已提交)：一個(gè)事務(wù)提交后才能被其他事務(wù)讀取到，可以阻止臟讀，但是不可重復(fù)讀或幻讀仍有可能發(fā)生。
• REPEATABLE-READ(可重復(fù)讀)：對(duì)同一記錄的多次讀取結(jié)果都是一致的，除非數(shù)據(jù)是被本身事務(wù)所修改，可以阻止臟讀和不可重復(fù)讀，但幻讀仍有可能發(fā)生。
• SERIALIZABLE(可串行化)：最高的隔離級(jí)別，完全服從ACID的隔離級(jí)別。所有的事務(wù)依次逐個(gè)執(zhí)行，這樣事務(wù)之間就完全不可能產(chǎn)生干擾，也就是說，該級(jí)別可以防止臟讀、不可重復(fù)讀以及幻讀。

這里需要注意的是：MySQL 默認(rèn)采用的 REPEATABLE_READ隔離級(jí)別，Oracle 默認(rèn)采用的 READ_COMMITTED隔離級(jí)別

事務(wù)隔離機(jī)制的實(shí)現(xiàn)基于鎖機(jī)制和并發(fā)調(diào)度。其中并發(fā)調(diào)度使用的是MVVC（多版本并發(fā)控制），通過保存修改的舊版本信息來支持并發(fā)一致性讀和回滾等特性。

因?yàn)楦綦x級(jí)別越低，事務(wù)請(qǐng)求的鎖越少，所以大部分?jǐn)?shù)據(jù)庫(kù)系統(tǒng)的隔離級(jí)別都是READ-COMMITTED(讀取已提交)，但是你要知道的是InnoDB 存儲(chǔ)引擎默認(rèn)使用 REPEATABLE-READ（可重復(fù)讀）并不會(huì)有任何性能損失。

InnoDB 存儲(chǔ)引擎在 分布式事務(wù) 的情況下一般會(huì)用到SERIALIZABLE(可串行化)隔離級(jí)別。

MySQL數(shù)據(jù)庫(kù)可重復(fù)讀隔離級(jí)別是怎么實(shí)現(xiàn)的，MVCC并發(fā)版本控制原理

MySQL可重復(fù)讀是通過MVCC實(shí)現(xiàn)的

MVCC(Multi Version Concurrency Control的簡(jiǎn)稱)，代表多版本并發(fā)控制。與MVCC相對(duì)的，是基于鎖的并發(fā)控制，Lock-Based Concurrency Control)。MVCC最大的優(yōu)勢(shì)：讀不加鎖，讀寫不沖突。在讀多寫少的OLTP應(yīng)用中，讀寫不沖突是非常重要的，極大的增加了系統(tǒng)的并發(fā)性能

MVCC是通過在每行記錄后面保存兩個(gè)隱藏的列來實(shí)現(xiàn)的。這兩個(gè)列，一個(gè)保存了行的創(chuàng)建時(shí)間，一個(gè)保存行的過期時(shí)間（或刪除時(shí)間）。當(dāng)然存儲(chǔ)的并不是實(shí)際的時(shí)間值，而是系統(tǒng)版本號(hào)（system version number)。每開始一個(gè)新的事務(wù)，系統(tǒng)版本號(hào)都會(huì)自動(dòng)遞增。事務(wù)開始時(shí)刻的系統(tǒng)版本號(hào)會(huì)作為事務(wù)的版本號(hào)，用來和查詢到的每行記錄的版本號(hào)進(jìn)行比較。

InnoDB MVCC 實(shí)現(xiàn)原理

InnoDB 中 MVCC 的實(shí)現(xiàn)方式為：每一行記錄都有兩個(gè)隱藏列：DATA_TRX_ID、DATA_ROLL_PTR（如果沒有主鍵，則還會(huì)多一個(gè)隱藏的主鍵列）。

DATA_TRX_ID

記錄最近更新這條行記錄的事務(wù) ID，大小為 6 個(gè)字節(jié)

DATA_ROLL_PTR

表示指向該行回滾段（rollback segment）的指針，大小為 7 個(gè)字節(jié)，InnoDB 便是通過這個(gè)指針找到之前版本的數(shù)據(jù)。該行記錄上所有舊版本，在 undo 中都通過鏈表的形式組織。

DB_ROW_ID

行標(biāo)識(shí)（隱藏單調(diào)自增 ID），大小為 6 字節(jié)，如果表沒有主鍵，InnoDB 會(huì)自動(dòng)生成一個(gè)隱藏主鍵，因此會(huì)出現(xiàn)這個(gè)列。另外，每條記錄的頭信息（record header）里都有一個(gè)專門的 bit（deleted_flag）來表示當(dāng)前記錄是否已經(jīng)被刪除。

增刪改查

假設(shè)初始版本號(hào)為1：

INSERT

insert into user (id,name) values (1,'Tom');

id	name	create_version	delete_version
1	Tom	1

下面模擬一下文章開頭的場(chǎng)景：

SELECT (事務(wù)A)

select * from user where id = 1;

此時(shí)讀到的版本號(hào)為1

UPDATE(事務(wù)B)

update user set name = 'Jerry' where id = 1;

在更新操作的時(shí)候，該事務(wù)的版本號(hào)在原來的基礎(chǔ)上加1，所以版本號(hào)為2。先將要更新的這條數(shù)據(jù)標(biāo)記為已刪除，并且刪除的版本號(hào)是當(dāng)前事務(wù)的版本號(hào)，然后插入一行新的記錄

id	name	create_version	delete_version
1	Tom	1	2
1	Jerry	2

SELECT (事務(wù)A)

此時(shí)事務(wù)A再重新讀數(shù)據(jù)：

select * from user where id = 1;

由于事務(wù)A一直沒提交，所以此時(shí)讀到的版本號(hào)還是為1，所以讀到的還是Tom這條數(shù)據(jù)，也就是可重復(fù)讀

DELETE

delete from user where id = 1;

在刪除操作的時(shí)候，該事務(wù)的版本號(hào)在原來的基礎(chǔ)上加1，所以版本號(hào)為3刪除時(shí)，將當(dāng)前版本號(hào)作為刪除版本號(hào)

id	name	create_version	delete_version
1	Jerry	2	2

鎖

對(duì)MySQL的鎖了解嗎

當(dāng)數(shù)據(jù)庫(kù)有并發(fā)事務(wù)的時(shí)候，可能會(huì)產(chǎn)生數(shù)據(jù)的不一致，這時(shí)候需要一些機(jī)制來保證訪問的次序，鎖機(jī)制就是這樣的一個(gè)機(jī)制。

隔離級(jí)別與鎖的關(guān)系

在Read Uncommitted級(jí)別下，讀取數(shù)據(jù)不需要加共享鎖，這樣就不會(huì)跟被修改的數(shù)據(jù)上的排他鎖沖突

在Read Committed級(jí)別下，讀操作需要加共享鎖，在語句執(zhí)行完以后釋放共享鎖；

在Repeatable Read級(jí)別下，讀操作需要加共享鎖，事務(wù)執(zhí)行完畢后才釋放共享鎖。

在SERIALIZABLE級(jí)別下，是限制性最強(qiáng)的隔離級(jí)別，該級(jí)別下鎖定整個(gè)范圍的鍵，并一直持有鎖，直到事務(wù)完成。

按照鎖的粒度分?jǐn)?shù)據(jù)庫(kù)鎖有哪些？

在關(guān)系型數(shù)據(jù)庫(kù)中，可以按照鎖的粒度把數(shù)據(jù)庫(kù)鎖分為行級(jí)鎖(INNODB引擎)、表級(jí)鎖(MYISAM引擎)和頁(yè)級(jí)鎖(BDB引擎 )。

MyISAM和InnoDB存儲(chǔ)引擎使用的鎖：

• MyISAM采用表級(jí)鎖(table-level locking)。
• InnoDB支持行級(jí)鎖(row-level locking)和表級(jí)鎖，默認(rèn)采用行級(jí)鎖

行級(jí)鎖，表級(jí)鎖和頁(yè)級(jí)鎖對(duì)比

行級(jí)鎖 行級(jí)鎖是MySQL中鎖定粒度最細(xì)的一種鎖，表示只針對(duì)當(dāng)前操作的行進(jìn)行加鎖。行級(jí)鎖能大大減少數(shù)據(jù)庫(kù)操作的沖突。其加鎖粒度最小，但加鎖的開銷也最大。行級(jí)鎖分為共享鎖 和 排他鎖。

特點(diǎn)：鎖定粒度最小，對(duì)當(dāng)前操作的行記錄加鎖，發(fā)生鎖沖突的概率最低，并發(fā)度也最高；加鎖開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；

表級(jí)鎖 表級(jí)鎖是MySQL中鎖定粒度最大的一種鎖，表示對(duì)當(dāng)前操作的整張表加鎖，它實(shí)現(xiàn)簡(jiǎn)單，資源消耗較少，被大部分MySQL引擎支持。最常使用的MYISAM與INNODB都支持表級(jí)鎖定。表級(jí)鎖定分為表共享讀鎖（共享鎖）與表獨(dú)占寫鎖（排他鎖）。

特點(diǎn)：鎖定粒度大，對(duì)當(dāng)前操作的整張表加鎖，發(fā)出鎖沖突的概率最高，并發(fā)度最低；加鎖開銷小，加鎖快；不會(huì)出現(xiàn)死鎖；

頁(yè)級(jí)鎖 頁(yè)級(jí)鎖是MySQL中鎖定粒度介于行級(jí)鎖和表級(jí)鎖中間的一種鎖。表級(jí)鎖速度快，但沖突多，行級(jí)沖突少，但速度慢。所以取了折衷的頁(yè)級(jí)，一次鎖定相鄰的一組記錄。

特點(diǎn)：開銷和加鎖時(shí)間界于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般

共享鎖和排他鎖的區(qū)別

共享鎖

共享鎖 share lock 又稱讀鎖 read lock，簡(jiǎn)稱S鎖，是讀取操作創(chuàng)建的鎖。其他用戶可以并發(fā)讀取數(shù)據(jù)，但任何事務(wù)都不能對(duì)數(shù)據(jù)進(jìn)行修改（獲取數(shù)據(jù)上的排他鎖），直到已釋放所有共享鎖。

如果事務(wù)T對(duì)數(shù)據(jù)A加上共享鎖后，則其他事務(wù)只能對(duì)A再加共享鎖，不能加排他鎖。獲得共享鎖的事務(wù)只能讀數(shù)據(jù)，不能修改數(shù)據(jù)

讀取為什么要加讀鎖呢：防止數(shù)據(jù)在被讀取的時(shí)候被別的線程加上寫鎖

使用方式：在需要執(zhí)行的語句后面加上 for update就可以了

排他鎖

排他鎖 exclusive lock 又稱寫鎖 writer lock，簡(jiǎn)稱X鎖。排他鎖是悲觀鎖的一種實(shí)現(xiàn)。

若事務(wù)T對(duì)數(shù)據(jù)A加上排他鎖，則只允許事務(wù)T讀取和修改數(shù)據(jù)A，其他任何事務(wù)都不能再對(duì)A加任何類型的鎖，直到事務(wù)T釋放X鎖。排他鎖會(huì)阻塞所有的排他鎖和共享鎖

數(shù)據(jù)庫(kù)的樂觀鎖和悲觀鎖是什么？怎么實(shí)現(xiàn)的？

數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS）中的并發(fā)控制的任務(wù)是確保在多個(gè)事務(wù)同時(shí)存取同一數(shù)據(jù)時(shí)不破壞事務(wù)的隔離性和一致性以及數(shù)據(jù)庫(kù)的統(tǒng)一性。樂觀并發(fā)控制（樂觀鎖）和悲觀并發(fā)控制（悲觀鎖）是并發(fā)控制主要采用的技術(shù)手段。

悲觀鎖：假定會(huì)發(fā)生并發(fā)沖突，每次去查詢數(shù)據(jù)的時(shí)候都認(rèn)為別人會(huì)修改，每次查詢完數(shù)據(jù)的時(shí)候就把事務(wù)鎖起來，直到提交事務(wù)。實(shí)現(xiàn)方式：使用數(shù)據(jù)庫(kù)中的鎖機(jī)制

樂觀鎖：假設(shè)不會(huì)發(fā)生并發(fā)沖突，每次去查詢數(shù)據(jù)的時(shí)候都認(rèn)為別人不會(huì)修改，所以不會(huì)上鎖，在修改數(shù)據(jù)的時(shí)候才把事務(wù)鎖起來。實(shí)現(xiàn)方式：樂觀鎖一般會(huì)使用版本號(hào)機(jī)制或CAS算法實(shí)現(xiàn)

兩種鎖的使用場(chǎng)景

從上面對(duì)兩種鎖的介紹，我們知道兩種鎖各有優(yōu)缺點(diǎn)，不可認(rèn)為一種好于另一種，像樂觀鎖適用于寫比較少的情況下（多讀場(chǎng)景），即沖突真的很少發(fā)生的時(shí)候，這樣可以省去了鎖的開銷，加大了系統(tǒng)的整個(gè)吞吐量。

但如果是多寫的情況，一般會(huì)經(jīng)常產(chǎn)生沖突，這就會(huì)導(dǎo)致上層應(yīng)用會(huì)不斷的進(jìn)行retry，這樣反倒是降低了性能，所以一般多寫的場(chǎng)景下用悲觀鎖就比較合適。

SQL優(yōu)化

如何定位及優(yōu)化SQL語句的性能問題？創(chuàng)建的索引有沒有被使用到？或者說怎么才可以知道這條語句運(yùn)行很慢的原因？

對(duì)于低性能的SQL語句的定位，最重要也是最有效的方法就是使用執(zhí)行計(jì)劃，MySQL提供了explain命令來查看語句的執(zhí)行計(jì)劃。我們知道，不管是哪種數(shù)據(jù)庫(kù)，或者是哪種數(shù)據(jù)庫(kù)引擎，在對(duì)一條SQL語句進(jìn)行執(zhí)行的過程中都會(huì)做很多相關(guān)的優(yōu)化，對(duì)于查詢語句，最重要的優(yōu)化方式就是使用索引。而執(zhí)行計(jì)劃，就是顯示數(shù)據(jù)庫(kù)引擎對(duì)于SQL語句執(zhí)行的詳細(xì)情況，其中包含了是否使用索引，使用什么索引，使用的索引的相關(guān)信息等。

執(zhí)行計(jì)劃包含的信息

id 由一組數(shù)字組成。表示一個(gè)查詢中各個(gè)子查詢的執(zhí)行順序;

• id相同執(zhí)行順序由上至下。
• id不同，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行。
• id為null時(shí)，表示一個(gè)合并結(jié)果集的操作的執(zhí)行id為null，常出現(xiàn)在包含union等查詢語句中。

select_type 每個(gè)子查詢的查詢類型，一些常見的查詢類型。

id	select_type	description
1	SIMPLE	不包含任何子查詢或union查詢
2	PRIMARY	包含子查詢時(shí)最外層查詢就顯示為 PRIMARY
3	SUBQUERY	在select或where子句中出現(xiàn)的子查詢
4	DERIVED	from字句中出現(xiàn)的子查詢
5	UNION	union連接的兩個(gè)select查詢，第一個(gè)查詢是dervied派生表，除了第一個(gè)表外，第二個(gè)以后的表select_type都是union。
6	UNION RESULT	包含union的結(jié)果集，在union和union all語句中，因?yàn)樗恍枰獏⑴c查詢，所以id字段為null
7	dependent subquery	與dependent union類似，表示這個(gè)subquery的查詢要受到外部表查詢的影響。
8	dependent union	與union一樣，出現(xiàn)在union 或union all語句中，但是這個(gè)查詢要受到外部查詢的影響

table 顯示的查詢表名，如果查詢使用了別名，那么這里顯示的是別名。

type訪問類型(非常重要，可以看到有沒有走索引)

依次從好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL。

除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一個(gè)索引。

類型	描述
system	表中只有一行數(shù)據(jù)或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在這個(gè)情況通常都是all或者index。
const	使用唯一索引或者主鍵，返回記錄是1行記錄的等值where條件時(shí)，通常type是const。其他數(shù)據(jù)庫(kù)也叫做唯一索引掃描。
eq_ref	出現(xiàn)在要連接多個(gè)表的查詢計(jì)劃中，驅(qū)動(dòng)表只返回一行數(shù)據(jù)，且這行數(shù)據(jù)是第二個(gè)表的主鍵或者唯一索引，且必須為not null，唯一索引和主鍵是多列時(shí)，只有所有的列都用作比較時(shí)才會(huì)出現(xiàn)eq_ref。
ref	像eq_ref那樣要求連接順序，也沒有主鍵和唯一索引的要求，只要使用相等條件檢索時(shí)就可能出現(xiàn)，常見于普通索引的等值查找。或者多列主鍵、唯一索引中，使用第一個(gè)列之外的列作為等值查找也會(huì)出現(xiàn)，總之，返回?cái)?shù)據(jù)不唯一的等值查找就可能出現(xiàn)。
fulltext	全文索引檢索，要注意，全文索引的優(yōu)先級(jí)很高，若全文索引和普通索引同時(shí)存在時(shí)，mysql不管代價(jià)，優(yōu)先選擇使用全文索引。
ref_or_null	與ref方法類似，只是增加了null值的比較。實(shí)際用的不多。
unique_subquery	用于where中的in形式子查詢，子查詢返回不重復(fù)值唯一值。
index_subquery	用于in形式子查詢使用到了輔助索引或者in常數(shù)列表，子查詢可能返回重復(fù)值，可以使用索引將子查詢?nèi)ブ亍?/td>
range	索引范圍掃描，常見于使用>,<,is null,between ,in ,like等運(yùn)算符的查詢中。
index_merge	表示查詢使用了兩個(gè)以上的索引，最后取交集或者并集。常見and ，or的條件使用了不同的索引，官方排序這個(gè)在ref_or_null之后，但是實(shí)際上由于要讀取多個(gè)索引，性能可能都不如range。
index	索引全表掃描。把索引從頭到尾掃一遍，常見于使用索引列就可以處理，不需要讀取數(shù)據(jù)文件的查詢、可以使用索引排序或者分組的查詢。
ALL	全表掃描數(shù)據(jù)文件

possible_keys 可能使用的索引，注意不一定會(huì)使用。查詢涉及到的字段上若存在索引，則該索引將被列出來。當(dāng)該列為 NULL時(shí)就要考慮當(dāng)前的SQL是否需要優(yōu)化了。

key 顯示MySQL在查詢中實(shí)際使用的索引，若沒有使用索引，顯示為NULL。

key_length 索引長(zhǎng)度

ref 表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值

rows 這里是執(zhí)行計(jì)劃中估算的掃描行數(shù)，不是精確值。

extra 的信息非常豐富，常見的有：

• Using index 使用覆蓋索引
• Using where 使用了where子句來過濾結(jié)果集
• Using filesort 使用文件排序，使用非索引列進(jìn)行排序時(shí)出現(xiàn)，非常消耗性能，盡量?jī)?yōu)化。
• Using temporary 使用了臨時(shí)表

SQL優(yōu)化的目標(biāo)可以參考阿里開發(fā)手冊(cè)

【推薦】SQL性能優(yōu)化的目標(biāo)：至少要達(dá)到 range 級(jí)別，要求是ref級(jí)別，如果可以是consts最好。
說明：
1） consts 單表中最多只有一個(gè)匹配行（主鍵或者唯一索引），在優(yōu)化階段即可讀取到數(shù)據(jù)。
2） ref 指的是使用普通的索引（normal index）。
3） range 對(duì)索引進(jìn)行范圍檢索。
反例：explain表的結(jié)果，type=index，索引物理文件全掃描，速度非常慢，這個(gè)index級(jí)別比較range還低，與全表掃描是小巫見大巫。

SQL的生命周期？一條SQL查詢語句是如何執(zhí)行的？MySQL總體架構(gòu)—>SQL執(zhí)行流程—>語句執(zhí)行順序

MySQL 的邏輯架構(gòu)圖

-- 比如，你有個(gè)最簡(jiǎn)單的表，表里只有一個(gè) ID 字段，在執(zhí)行下面這個(gè)查詢語句時(shí)：
mysql> select * from T where ID=10;

MySQL的框架有幾個(gè)組件, 各是什么作用? Server層和存儲(chǔ)引擎層各是什么作用？

大體來說，MySQL 可以分為 Server 層和存儲(chǔ)引擎層兩部分。

Server 層包括連接器、分析器、優(yōu)化器、執(zhí)行器等，涵蓋 MySQL 的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)（如日期、時(shí)間、數(shù)學(xué)和加密函數(shù)等），所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)，比如存儲(chǔ)過程、觸發(fā)器、視圖等。

而存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。其架構(gòu)模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎。現(xiàn)在最常用的存儲(chǔ)引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了默認(rèn)存儲(chǔ)引擎。

SQL執(zhí)行流程，SQL的生命周期？

連接器

第一步，客戶端與數(shù)據(jù)庫(kù)server層的連接器進(jìn)行連接。連接器負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接。

查詢緩存

連接建立完成后，會(huì)判斷查詢緩存是否開啟，如果已經(jīng)開啟，會(huì)判斷sql是select還是update/insert/delete，對(duì)于select，嘗試去查詢緩存，如果命中緩存直接返回?cái)?shù)據(jù)給客戶端， 如果緩存沒有命中，或者沒有開啟緩存， 會(huì)進(jìn)入到下一步分析器。

分析器

分析器進(jìn)行詞法分析和語法分析，分析器先會(huì)做“詞法分析”，分析SQL中的字符串分別是什么，校驗(yàn)數(shù)據(jù)庫(kù)表和字段是否存在，然后進(jìn)行語法分析，判斷SQL是否滿足MySQL語法。

優(yōu)化器

優(yōu)化器對(duì)sql執(zhí)行計(jì)劃分析，得到最終執(zhí)行計(jì)劃，得到優(yōu)化后的執(zhí)行計(jì)劃交給執(zhí)行器。

優(yōu)化器是在表里面有多個(gè)索引的時(shí)候，決定使用哪個(gè)索引，或者在一個(gè)語句有多表關(guān)聯(lián)（join）的時(shí)候，決定各個(gè)表的連接順序。

執(zhí)行器

開始執(zhí)行的時(shí)候，要先判斷一下你對(duì)這個(gè)表 T 有沒有執(zhí)行查詢的權(quán)限，如果沒有，就會(huì)返回沒有權(quán)限的錯(cuò)誤，如果有權(quán)限，執(zhí)行器調(diào)用存儲(chǔ)引擎api執(zhí)行sql，得到響應(yīng)結(jié)果， 將結(jié)果返回給客戶端，如果緩存是開啟狀態(tài)， 會(huì)更新緩存

詳細(xì)邏輯架構(gòu)圖

1.連接：應(yīng)用服務(wù)器與數(shù)據(jù)庫(kù)服務(wù)器建立一個(gè)連接

2.獲得請(qǐng)求SQL：數(shù)據(jù)庫(kù)進(jìn)程拿到請(qǐng)求sql

3.查詢緩存：如果查詢命中緩存則直接返回結(jié)果

4.語法解析和預(yù)處理：

首先通過mysql關(guān)鍵字將語句解析，會(huì)生成一個(gè)內(nèi)部解析樹，mysql解析器將對(duì)其解析，查看是否是有錯(cuò)誤的關(guān)鍵字，關(guān)鍵字順序是否正確；預(yù)處理器則是根據(jù)mysql的規(guī)則進(jìn)行進(jìn)一步的檢查，檢查mysql語句是否合法，如庫(kù)表是否存在，字段是否存在，字段之間是否模棱兩可等等，預(yù)處理器也會(huì)驗(yàn)證權(quán)限。

5.查詢優(yōu)化器：sql語句在優(yōu)化器中轉(zhuǎn)換成執(zhí)行計(jì)劃，一條sql語句可以有多種方式查詢，最后返回的結(jié)果肯定是相同，但是不同的查詢方式效果不同，優(yōu)化器的作用就是：選擇一種合適的執(zhí)行計(jì)劃。mysql是基于成本的優(yōu)化器，他將預(yù)測(cè)執(zhí)行此計(jì)劃的成本，并選擇成本最小的那條

6.執(zhí)行計(jì)劃，執(zhí)行SQL：在解析和優(yōu)化后，MySQL將生成查詢對(duì)應(yīng)的執(zhí)行計(jì)劃，由執(zhí)行計(jì)劃調(diào)用存儲(chǔ)引擎的API來執(zhí)行查詢

7.將結(jié)果返回給客戶端

8.關(guān)掉連接，釋放資源

一條更新語句的執(zhí)行流程又是怎樣的呢？

-- 如果要將 ID=2 這一行的值加 1
mysql> update T set c=c+1 where ID=2;

你執(zhí)行語句前要先連接數(shù)據(jù)庫(kù)，這是連接器的工作。

前面我們說過，在一個(gè)表上有更新的時(shí)候，跟這個(gè)表有關(guān)的查詢緩存會(huì)失效，所以這條語句就會(huì)把表 T 上所有緩存結(jié)果都清空。這也就是我們一般不建議使用查詢緩存的原因。

接下來，分析器會(huì)通過詞法和語法解析知道這是一條更新語句。優(yōu)化器決定要使用 ID 這個(gè)索引。然后，執(zhí)行器負(fù)責(zé)具體執(zhí)行，找到這一行，然后更新。

與查詢流程不一樣的是，更新流程還涉及兩個(gè)重要的日志模塊，它們正是我們今天要討論的主角：redo log（重做日志）和 binlog（歸檔日志）。

在 MySQL 里也有這個(gè)問題，如果每一次的更新操作都需要寫進(jìn)磁盤，然后磁盤也要找到對(duì)應(yīng)的那條記錄，然后再更新，整個(gè)過程 IO 成本、查找成本都很高。為了解決這個(gè)問題，MySQL 的設(shè)計(jì)者就用了類似酒店掌柜粉板的思路來提升更新效率。

而粉板和賬本配合的整個(gè)過程，其實(shí)就是 MySQL 里經(jīng)常說到的 WAL 技術(shù)，WAL 的全稱是 Write-Ahead Logging，它的關(guān)鍵點(diǎn)就是先寫日志，再寫磁盤，也就是先寫粉板，等不忙的時(shí)候再寫賬本。

具體來說，當(dāng)有一條記錄需要更新的時(shí)候，InnoDB 引擎就會(huì)先把記錄寫到 redo log（粉板）里面，并更新內(nèi)存，這個(gè)時(shí)候更新就算完成了。同時(shí)，InnoDB 引擎會(huì)在適當(dāng)?shù)臅r(shí)候，將這個(gè)操作記錄更新到磁盤里面，而這個(gè)更新往往是在系統(tǒng)比較空閑的時(shí)候做，這就像打烊以后掌柜做的事。

如果今天賒賬的不多，掌柜可以等打烊后再整理。但如果某天賒賬的特別多，粉板寫滿了，又怎么辦呢？這個(gè)時(shí)候掌柜只好放下手中的活兒，把粉板中的一部分賒賬記錄更新到賬本中，然后把這些記錄從粉板上擦掉，為記新賬騰出空間。

write pos 是當(dāng)前記錄的位置，一邊寫一邊后移，寫到第 3 號(hào)文件末尾后就回到 0 號(hào)文件開頭。checkpoint 是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件。

write pos 和 checkpoint 之間的是“粉板”上還空著的部分，可以用來記錄新的操作。如果 write pos 追上 checkpoint，表示“粉板”滿了，這時(shí)候不能再執(zhí)行新的更新，得停下來先擦掉一些記錄，把 checkpoint 推進(jìn)一下。

有了 redo log，InnoDB 就可以保證即使數(shù)據(jù)庫(kù)發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失，這個(gè)能力稱為crash-safe。

要理解 crash-safe 這個(gè)概念，可以想想我們前面賒賬記錄的例子。只要賒賬記錄記在了粉板上或?qū)懺诹速~本上，之后即使掌柜忘記了，比如突然停業(yè)幾天，恢復(fù)生意后依然可以通過賬本和粉板上的數(shù)據(jù)明確賒賬賬目。

重要的日志模塊：binlog

前面我們講過，MySQL 整體來看，其實(shí)就有兩塊：一塊是 Server 層，它主要做的是 MySQL 功能層面的事情；還有一塊是引擎層，負(fù)責(zé)存儲(chǔ)相關(guān)的具體事宜。上面我們聊到的粉板 redo log 是 InnoDB 引擎特有的日志，而 Server 層也有自己的日志，稱為 binlog（歸檔日志）。

我想你肯定會(huì)問，為什么會(huì)有兩份日志呢？

因?yàn)樽铋_始 MySQL 里并沒有 InnoDB 引擎。MySQL 自帶的引擎是 MyISAM，但是 MyISAM 沒有 crash-safe 的能力，binlog 日志只能用于歸檔。而 InnoDB 是另一個(gè)公司以插件形式引入 MySQL 的，既然只依靠 binlog 是沒有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系統(tǒng)——也就是 redo log 來實(shí)現(xiàn) crash-safe 能力。

這兩種日志有以下三點(diǎn)不同。

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實(shí)現(xiàn)的，所有引擎都可以使用。
• redo log 是物理日志，記錄的是“在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改”；binlog 是邏輯日志，記錄的是這個(gè)語句的原始邏輯，比如“給 ID=2 這一行的 c 字段加 1 ”。
• redo log 是循環(huán)寫的，空間固定會(huì)用完；binlog 是可以追加寫入的?！白芳訉憽笔侵?binlog 文件寫到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志。

常用SQL查詢語句優(yōu)化方法

• 不要使用select * from t，用具體的字段列表代替“*”，使用星號(hào)會(huì)降低查詢效率，如果數(shù)據(jù)庫(kù)字段改變，可能出現(xiàn)不可預(yù)知隱患。

• 應(yīng)盡量避免在where子句中使用!=或<>操作符，避免在where子句中字段進(jìn)行null值判斷，存儲(chǔ)引擎將放棄使用索引而進(jìn)行全表掃描。

• 避免使用左模糊，左模糊查詢將導(dǎo)致全表掃描。

• IN語句查詢時(shí)包含的值不應(yīng)過多，否則將導(dǎo)致全表掃描。

• 為經(jīng)常作為查詢條件的字段，經(jīng)常需要排序、分組操作的字段建立索引。

• 在使用聯(lián)合索引字段作為條件時(shí)，應(yīng)遵循最左前綴原則。

• OR前后兩個(gè)條件都要有索引，整個(gè)SQL才會(huì)使用索引，只要有一個(gè)條件沒索引整個(gè)SQL就不會(huì)使用索引。

• 盡量用union all代替union，union需要將結(jié)果集合并后再進(jìn)行唯一性過濾操作，這就會(huì)涉及到排序，增加大量的CPU運(yùn)算，加大資源消耗及延遲。

數(shù)據(jù)庫(kù)優(yōu)化

數(shù)據(jù)庫(kù)結(jié)構(gòu)優(yōu)化

一個(gè)好的數(shù)據(jù)庫(kù)設(shè)計(jì)方案對(duì)于數(shù)據(jù)庫(kù)的性能往往會(huì)起到事半功倍的效果。

需要考慮數(shù)據(jù)冗余、查詢和更新的速度、字段的數(shù)據(jù)類型是否合理等多方面的內(nèi)容。

將字段很多的表分解成多個(gè)表

對(duì)于字段較多的表，如果有些字段的使用頻率很低，可以將這些字段分離出來形成新表。

因?yàn)楫?dāng)一個(gè)表的數(shù)據(jù)量很大時(shí)，會(huì)由于使用頻率低的字段的存在而變慢。

增加中間表

對(duì)于需要經(jīng)常聯(lián)合查詢的表，可以建立中間表以提高查詢效率。

通過建立中間表，將需要通過聯(lián)合查詢的數(shù)據(jù)插入到中間表中，然后將原來的聯(lián)合查詢改為對(duì)中間表的查詢。

增加冗余字段

設(shè)計(jì)數(shù)據(jù)表時(shí)應(yīng)盡量遵循范式理論的規(guī)約，盡可能的減少冗余字段，讓數(shù)據(jù)庫(kù)設(shè)計(jì)看起來精致、優(yōu)雅。但是，合理的加入冗余字段可以提高查詢速度。

表的規(guī)范化程度越高，表和表之間的關(guān)系越多，需要連接查詢的情況也就越多，性能也就越差。

注意：

冗余字段的值在一個(gè)表中修改了，就要想辦法在其他表中更新，否則就會(huì)導(dǎo)致數(shù)據(jù)不一致的問題。

大表怎么優(yōu)化？某個(gè)表有近千萬數(shù)據(jù)，CRUD比較慢，如何優(yōu)化？分庫(kù)分表是怎么做的？分表分庫(kù)了有什么問題？有用到中間件么？他們的原理知道么？

當(dāng)MySQL單表記錄數(shù)過大時(shí)，數(shù)據(jù)庫(kù)的CRUD性能會(huì)明顯下降，一些常見的優(yōu)化措施如下：

1. 限定數(shù)據(jù)的查詢范圍： 務(wù)必禁止不帶任何限制數(shù)據(jù)范圍條件的查詢語句。比如：當(dāng)用戶在查詢訂單歷史的時(shí)候，我們可以控制在一個(gè)月的范圍內(nèi)；

2. 讀/寫分離： 經(jīng)典的數(shù)據(jù)庫(kù)拆分方案，主庫(kù)負(fù)責(zé)寫，從庫(kù)負(fù)責(zé)讀；

3. 緩存： 使用MySQL的緩存，另外對(duì)重量級(jí)、更新少的數(shù)據(jù)可以考慮使用應(yīng)用級(jí)別的緩存；

分庫(kù)分表

分庫(kù)分表主要有垂直分表和水平分表

垂直分表：

垂直拆分是指數(shù)據(jù)表列的拆分，把一張列比較多的表拆分為多張表。例如，用戶表中既有用戶的登錄信息又有用戶的基本信息，可以將用戶表拆分成兩個(gè)單獨(dú)的表，甚至放到單獨(dú)的庫(kù)做分庫(kù)。如下圖所示，這樣來說大家應(yīng)該就更容易理解了。

適用場(chǎng)景：如果一個(gè)表中某些列常用，另外一些列不常用

垂直拆分的優(yōu)點(diǎn)：可以使得行數(shù)據(jù)變小，在查詢時(shí)減少讀取的Block數(shù)，減少I/O次數(shù)。此外，垂直分區(qū)可以簡(jiǎn)化表的結(jié)構(gòu)，易于維護(hù)。

垂直拆分的缺點(diǎn)：主鍵會(huì)出現(xiàn)冗余，需要管理冗余列，并會(huì)引起Join操作，可以通過在應(yīng)用層進(jìn)行Join來解決。對(duì)于應(yīng)用層來說，邏輯算法增加開發(fā)成本。此外，垂直分區(qū)會(huì)讓事務(wù)變得更加復(fù)雜；

水平分表：

保持?jǐn)?shù)據(jù)表結(jié)構(gòu)不變，通過某種策略進(jìn)行存儲(chǔ)數(shù)據(jù)分片。這樣每一片數(shù)據(jù)分散到不同的表或者庫(kù)中，達(dá)到了分布式的目的。水平拆分可以支撐非常大的數(shù)據(jù)量。

水平拆分是指數(shù)據(jù)表行的拆分，表的行數(shù)超過200萬行時(shí)，就會(huì)變慢，這時(shí)可以把一張表的數(shù)據(jù)拆成多張表來存放。舉個(gè)例子：我們可以將用戶信息表拆分成多個(gè)用戶信息表，這樣就可以避免單一表數(shù)據(jù)量過大對(duì)性能造成影響。

水平拆分可以支持非常大的數(shù)據(jù)量。需要注意的一點(diǎn)是：水平分表僅僅是解決了單一表數(shù)據(jù)過大的問題，但由于表的數(shù)據(jù)還是在同一臺(tái)機(jī)器上，其實(shí)對(duì)于提升MySQL并發(fā)能力沒有什么意義，所以 水平拆分最好分庫(kù) 。

適用場(chǎng)景：支持非常大的數(shù)據(jù)量存儲(chǔ)

水平拆分優(yōu)點(diǎn)：支持非常大的數(shù)據(jù)量存儲(chǔ)

水平拆分缺點(diǎn)：給應(yīng)用增加復(fù)雜度，通常查詢時(shí)需要多個(gè)表名，查詢所有數(shù)據(jù)都需UNION操作；分片事務(wù)難以解決 ，跨庫(kù)join性能較差，邏輯復(fù)雜。

《Java工程師修煉之道》的作者推薦 盡量不要對(duì)數(shù)據(jù)進(jìn)行分片，因?yàn)椴鸱謺?huì)帶來邏輯、部署、運(yùn)維的各種復(fù)雜度 ，一般的數(shù)據(jù)表在優(yōu)化得當(dāng)?shù)那闆r下支撐千萬以下的數(shù)據(jù)量是沒有太大問題的。如果實(shí)在要分片，盡量選擇客戶端分片架構(gòu)，這樣可以減少一次和中間件的網(wǎng)絡(luò)I/O。

下面補(bǔ)充一下數(shù)據(jù)庫(kù)分片的兩種常見方案：

• 客戶端代理： 分片邏輯在應(yīng)用端，封裝在jar包中，通過修改或者封裝JDBC層來實(shí)現(xiàn)。 當(dāng)當(dāng)網(wǎng)的 Sharding-JDBC 、阿里的TDDL是兩種比較常用的實(shí)現(xiàn)。
• 中間件代理： 在應(yīng)用和數(shù)據(jù)庫(kù)中間加了一個(gè)代理層。分片邏輯統(tǒng)一維護(hù)在中間件服務(wù)中。 Mycat 、360的Atlas、網(wǎng)易的DDB等等都是這種架構(gòu)的實(shí)現(xiàn)。

MySQL的主從復(fù)制原理以及流程

主從復(fù)制：將主數(shù)據(jù)庫(kù)中的DDL和DML操作通過二進(jìn)制日志（BINLOG）傳輸?shù)綇臄?shù)據(jù)庫(kù)上，然后將這些日志重新執(zhí)行，從而使得從數(shù)據(jù)庫(kù)的數(shù)據(jù)與主數(shù)據(jù)庫(kù)保持一致。

主從復(fù)制的作用

高可用和故障切換：主數(shù)據(jù)庫(kù)出現(xiàn)問題，可以切換到從數(shù)據(jù)庫(kù)。

負(fù)載均衡：可以進(jìn)行數(shù)據(jù)庫(kù)層面的讀寫分離。

數(shù)據(jù)備份：可以在從數(shù)據(jù)庫(kù)上進(jìn)行日常備份。

復(fù)制過程

Binary log：主數(shù)據(jù)庫(kù)的二進(jìn)制日志

Relay log：從數(shù)據(jù)庫(kù)的中繼日志

第一步：master在每個(gè)事務(wù)更新數(shù)據(jù)完成之前，將該操作記錄串行地寫入到binlog文件中。

第二步：salve開啟一個(gè)I/O Thread，該線程在master打開一個(gè)普通連接，將這些事件寫入到中繼日志中。如果讀取的進(jìn)度已經(jīng)跟上了master，就進(jìn)入睡眠狀態(tài)并等待master產(chǎn)生新的事件。

第三步：SQL Thread會(huì)讀取中繼日志，并順序執(zhí)行該日志中的SQL事件，從而與主數(shù)據(jù)庫(kù)中的數(shù)據(jù)保持一致。

讀寫分離有哪些解決方案？

讀寫分離是依賴于主從復(fù)制，而主從復(fù)制又是為讀寫分離服務(wù)的。主從復(fù)制要求slave不能寫只能讀

方案一

利用中間件來做代理，使用mysql-proxy代理，負(fù)責(zé)對(duì)數(shù)據(jù)庫(kù)的請(qǐng)求識(shí)別出讀還是寫，并分發(fā)到不同的數(shù)據(jù)庫(kù)中。

優(yōu)點(diǎn)：直接實(shí)現(xiàn)讀寫分離和負(fù)載均衡，不用修改代碼，數(shù)據(jù)庫(kù)和應(yīng)用程序弱耦合，master和slave用一樣的帳號(hào)，mysql官方不建議實(shí)際生產(chǎn)中使用

缺點(diǎn)：降低性能， 不支持事務(wù)，代理存在性能瓶頸和可靠性風(fēng)險(xiǎn)增加。

方案二

使用AbstractRoutingDataSource+aop+annotation在dao層決定數(shù)據(jù)源。

如果采用了mybatis， 可以將讀寫分離放在ORM層，比如mybatis可以通過mybatis plugin攔截sql語句，所有的insert/update/delete都訪問master庫(kù)，所有的select 都訪問salve庫(kù)，這樣對(duì)于dao層都是透明。plugin實(shí)現(xiàn)時(shí)可以通過注解或者分析語句是讀寫方法來選定主從庫(kù)。

不過這樣依然有一個(gè)問題， 也就是不支持事務(wù)， 所以我們還需要重寫一下DataSourceTransactionManager， 將read-only的事務(wù)扔進(jìn)讀庫(kù)， 其余的有讀有寫的扔進(jìn)寫庫(kù)。

方案三

使用AbstractRoutingDataSource+aop+annotation在service層決定數(shù)據(jù)源，可以支持事務(wù)

缺點(diǎn)：類內(nèi)部方法通過this.xx()方式相互調(diào)用時(shí)，aop不會(huì)進(jìn)行攔截，需進(jìn)行特殊處理

大數(shù)據(jù)量處理

大批量數(shù)據(jù)刪除，怎么一次刪除100萬條數(shù)據(jù)

方法一：

在MySQL數(shù)據(jù)庫(kù)使用中，有的表存儲(chǔ)數(shù)據(jù)量比較大，達(dá)到每天三百萬條記錄左右，此表中建立了三個(gè)索引，這些索引都是必須的，其他程序要使用。由于要求此表中的數(shù)據(jù)只保留當(dāng)天的數(shù)據(jù)，所以每當(dāng)在凌晨的某一時(shí)刻當(dāng)其他程序處理萬其中的數(shù)據(jù)后要?jiǎng)h除該表中昨天以及以前的數(shù)據(jù)，使用delete刪除表中的上百萬條記錄時(shí)，MySQL刪除速度非常緩慢每一萬條記錄需要大概4分鐘左右，這樣刪除所有無用數(shù)據(jù)要達(dá)到八個(gè)小時(shí)以上，這是難以接受的。

查詢MySQL官方手冊(cè)得知?jiǎng)h除數(shù)據(jù)的速度和創(chuàng)建的索引數(shù)量是成正比的，于是刪除掉其中的兩個(gè)索引后測(cè)試，發(fā)現(xiàn)此時(shí)刪除速度相當(dāng)快，一百萬條記錄在一分鐘多一些，可是這兩個(gè)索引其他模塊在每天一次的數(shù)據(jù)整理中還要使用，于是想到了一個(gè)折中的辦法：

在刪除數(shù)據(jù)之前刪除這兩個(gè)索引，此時(shí)需要三分鐘多一些，然后刪除其中無用數(shù)據(jù)，此過程需要不到兩分鐘，刪除完成后重新創(chuàng)建索引，因?yàn)榇藭r(shí)數(shù)據(jù)庫(kù)中的數(shù)據(jù)相對(duì)較少，約三四十萬條記錄(此表中的數(shù)據(jù)每小時(shí)會(huì)增加約十萬條)，創(chuàng)建索引也非?？?#xff0c;約十分鐘左右。這樣整個(gè)刪除過程只需要約15分鐘。對(duì)比之前的八個(gè)小時(shí)，大大節(jié)省了時(shí)間。

刪除大表的部分?jǐn)?shù)據(jù)，通常采用以下步驟：

刪除大表的多行數(shù)據(jù)時(shí)，會(huì)超出innod block table size的限制，最小化的減少鎖表時(shí)間的方案是：

1、選擇不需要?jiǎng)h除的數(shù)據(jù)，并把它們存在一張相同結(jié)構(gòu)的空表里

2、重命名原始表，并給新表命名為原始表名

3、刪掉原始表

方法二：

分批刪除，如果你要?jiǎng)h除一個(gè)表里面的前 10000 行數(shù)據(jù)，有以下三種方法可以做到：

第一種，直接執(zhí)行 delete from T limit 10000;

第二種，在一個(gè)連接中循環(huán)執(zhí)行 20 次 delete from T limit 500;

第三種，在 20 個(gè)連接中同時(shí)執(zhí)行 delete from T limit 500。

方案一，單個(gè)語句占用鎖的時(shí)間較長(zhǎng)，會(huì)導(dǎo)致其他客戶端等待資源時(shí)間較長(zhǎng)。

方案二，串行化執(zhí)行，將相對(duì)長(zhǎng)的事務(wù)分成多次相對(duì)短的事務(wù)，則每次事務(wù)占用鎖的時(shí)間相對(duì)較短，其他客戶端在等待相應(yīng)資源的時(shí)間也較短。這樣的操作，同時(shí)也意味著將資源分片使用（每次執(zhí)行使用不同片段的資源），可以提高并發(fā)性。

方案三，人為制造鎖競(jìng)爭(zhēng)，加劇并發(fā)。

方案二相對(duì)比較好，具體還要結(jié)合實(shí)際業(yè)務(wù)場(chǎng)景。

MySQL大數(shù)據(jù)量分頁(yè)查詢方法及其優(yōu)化

limit偏移量不變，隨著查詢記錄量越來越大，所花費(fèi)的時(shí)間也會(huì)越來越多。

limit查詢記錄數(shù)不變，隨著查詢偏移的增大，尤其查詢偏移大于10萬以后，查詢時(shí)間急劇增加。

原因分析

select * from user where sex = 1 limit 100,10

由于 sex 列是索引列，MySQL會(huì)走 sex 這棵索引樹，命中 sex=1 的數(shù)據(jù)。

然后又由于非聚簇索引中存儲(chǔ)的是主鍵 id 的值，且查詢語句要求查詢所有列，所以這里會(huì)發(fā)生一個(gè)回表的情況，在命中 sex 索引樹中值為1的數(shù)據(jù)后，拿著它葉子節(jié)點(diǎn)上的值也就是主鍵 id 的值去主鍵索引樹上查詢這一行其他列（name、sex）的值，最后返回到結(jié)果集中，這樣第一行數(shù)據(jù)就查詢成功了。

最后這句 SQL 要求limit 100, 10，也就是查詢第101到110個(gè)數(shù)據(jù)，但是 MySQL 會(huì)查詢前110行，然后將前100行拋棄，最后結(jié)果集中就只剩下了第101到110行，執(zhí)行結(jié)束。

小結(jié)一下，在上述的執(zhí)行過程中，造成 limit 大偏移量執(zhí)行時(shí)間變久的原因有：

limit a, b會(huì)查詢前a+b條數(shù)據(jù)，然后丟棄前a條數(shù)據(jù)

MySQL數(shù)據(jù)庫(kù)的查詢優(yōu)化器是采用了基于代價(jià)的方式，而查詢代價(jià)的估算是基于CPU代價(jià)和IO代價(jià)。如果MySQL在查詢代價(jià)估算中，認(rèn)為全表掃描方式比走索引掃描的方式效率更高的話，就會(huì)放棄索引，直接全表掃描

優(yōu)化方式

t5表有200萬數(shù)據(jù)，id為主鍵，text為普通索引

使用覆蓋索引
如果一條SQL語句，通過索引可以直接獲取查詢的結(jié)果，不再需要回表查詢，就稱這個(gè)索引為覆蓋索引。

在MySQL數(shù)據(jù)庫(kù)中使用explain關(guān)鍵字查看執(zhí)行計(jì)劃，如果extra這一列顯示Using index，就表示這條SQL語句使用了覆蓋索引。

讓我們來對(duì)比一下使用了覆蓋索引，性能會(huì)提升多少吧。

沒有使用覆蓋索引

select * from t5 order by text limit 1000000, 10;

這次查詢花了3.690秒，讓我們看一下使用了覆蓋索引優(yōu)化會(huì)提升多少性能吧。

使用了覆蓋索引

select id, `text` from t5 order by text limit 1000000, 10;

從上面的對(duì)比中，超大分頁(yè)查詢中，使用了覆蓋索引之后，花了0.201秒，而沒有使用覆蓋索引花了3.690秒，提高了18倍多，這在實(shí)際開發(fā)中，就是一個(gè)大的性能優(yōu)化了。

子查詢優(yōu)化

因?yàn)閷?shí)際開發(fā)中，用SELECT查詢一兩列操作是非常少的，因此上述的覆蓋索引的適用范圍就比較有限。

所以我們可以通過把分頁(yè)的SQL語句改寫成子查詢的方法獲得性能上的提升。

select * from t5 where id>=(select id from t5 order by text limit 1000000, 1) limit 10;

其實(shí)使用這種方法，提升的效率和上面使用了覆蓋索引基本一致。

但是這種優(yōu)化方法也有局限性：

這種寫法要求主鍵ID必須是連續(xù)的

Where子句不允許再添加其他條件

延遲關(guān)聯(lián)
和上述的子查詢做法類似，我們可以使用JOIN，先在索引列上完成分頁(yè)操作，然后再回表獲取所需要的列。

select a.* from t5 a inner join (select id from t5 order by text limit 1000000, 10) b on a.id=b.id;