一、MySQL三層邏輯架構(gòu)

MySQL的存儲(chǔ)引擎架構(gòu)將查詢處理與數(shù)據(jù)的存儲(chǔ)/提取相分離。下面是MySQL的邏輯架構(gòu)圖：

1、第一層負(fù)責(zé)連接管理、授權(quán)認(rèn)證、安全等等。

每個(gè)客戶端的連接都對(duì)應(yīng)著服務(wù)器上的一個(gè)線程。服務(wù)器上維護(hù)了一個(gè)線程池，避免為每個(gè)連接都創(chuàng)建銷毀一個(gè)線程。當(dāng)客戶端連接到MySQL服務(wù)器時(shí)，服務(wù)器對(duì)其進(jìn)行認(rèn)證?？梢酝ㄟ^(guò)用戶名和密碼的方式進(jìn)行認(rèn)證，也可以通過(guò)SSL證書進(jìn)行認(rèn)證。登錄認(rèn)證通過(guò)后，服務(wù)器還會(huì)驗(yàn)證該客戶端是否有執(zhí)行某個(gè)查詢的權(quán)限。

2、第二層負(fù)責(zé)解析查詢

編譯SQL，并對(duì)其進(jìn)行優(yōu)化(如調(diào)整表的讀取順序，選擇合適的索引等)。對(duì)于SELECT語(yǔ)句，在解析查詢前，服務(wù)器會(huì)先檢查查詢緩存，如果能在其中找到對(duì)應(yīng)的查詢結(jié)果，則無(wú)需再進(jìn)行查詢解析、優(yōu)化等過(guò)程，直接返回查詢結(jié)果。存儲(chǔ)過(guò)程、觸發(fā)器、視圖等都在這一層實(shí)現(xiàn)。

3、第三層是存儲(chǔ)引擎

存儲(chǔ)引擎負(fù)責(zé)在MySQL中存儲(chǔ)數(shù)據(jù)、提取數(shù)據(jù)、開(kāi)啟一個(gè)事務(wù)等等。存儲(chǔ)引擎通過(guò)API與上層進(jìn)行通信，這些API屏蔽了不同存儲(chǔ)引擎之間的差異，使得這些差異對(duì)上層查詢過(guò)程透明。存儲(chǔ)引擎不會(huì)去解析SQL。

二、對(duì)比InnoDB與MyISAM

1、 存儲(chǔ)結(jié)構(gòu)

MyISAM：每個(gè)MyISAM在磁盤上存儲(chǔ)成三個(gè)文件。分別為：表定義文件、數(shù)據(jù)文件、索引文件。第一個(gè)文件的名字以表的名字開(kāi)始，擴(kuò)展名指出文件類型。.frm文件存儲(chǔ)表定義。數(shù)據(jù)文件的擴(kuò)展名為.MYD (MYData)。索引文件的擴(kuò)展名是.MYI (MYIndex)。

InnoDB：所有的表都保存在同一個(gè)數(shù)據(jù)文件中（也可能是多個(gè)文件，或者是獨(dú)立的表空間文件），InnoDB表的大小只受限于操作系統(tǒng)文件的大小，一般為2GB。

2、 存儲(chǔ)空間

MyISAM： MyISAM支持支持三種不同的存儲(chǔ)格式：靜態(tài)表(默認(rèn)，但是注意數(shù)據(jù)末尾不能有空格，會(huì)被去掉)、動(dòng)態(tài)表、壓縮表。當(dāng)表在創(chuàng)建之后并導(dǎo)入數(shù)據(jù)之后，不會(huì)再進(jìn)行修改操作，可以使用壓縮表，極大的減少磁盤的空間占用。

InnoDB： 需要更多的內(nèi)存和存儲(chǔ)，它會(huì)在主內(nèi)存中建立其專用的緩沖池用于高速緩沖數(shù)據(jù)和索引。

3、 可移植性、備份及恢復(fù)

MyISAM：數(shù)據(jù)是以文件的形式存儲(chǔ)，所以在跨平臺(tái)的數(shù)據(jù)轉(zhuǎn)移中會(huì)很方便。在備份和恢復(fù)時(shí)可單獨(dú)針對(duì)某個(gè)表進(jìn)行操作。

InnoDB：免費(fèi)的方案可以是拷貝數(shù)據(jù)文件、備份 binlog，或者用 mysqldump，在數(shù)據(jù)量達(dá)到幾十G的時(shí)候就相對(duì)痛苦了。

4、 事務(wù)支持

MyISAM：強(qiáng)調(diào)的是性能，每次查詢具有原子性,其執(zhí)行數(shù)度比InnoDB類型更快，但是不提供事務(wù)支持。

InnoDB：提供事務(wù)支持事務(wù)，外部鍵等高級(jí)數(shù)據(jù)庫(kù)功能。 具有事務(wù)(commit)、回滾(rollback)和崩潰修復(fù)能力(crash recovery capabilities)的事務(wù)安全(transaction-safe (ACID compliant))型表。

5、 AUTO_INCREMENT

MyISAM：可以和其他字段一起建立聯(lián)合索引。引擎的自動(dòng)增長(zhǎng)列必須是索引，如果是組合索引，自動(dòng)增長(zhǎng)可以不是第一列，他可以根據(jù)前面幾列進(jìn)行排序后遞增。

InnoDB：InnoDB中必須包含只有該字段的索引。引擎的自動(dòng)增長(zhǎng)列必須是索引，如果是組合索引也必須是組合索引的第一列。

6、 表鎖差異

MyISAM： 只支持表級(jí)鎖，用戶在操作myisam表時(shí)，select，update，delete，insert語(yǔ)句都會(huì)給表自動(dòng)加鎖，如果加鎖以后的表滿足insert并發(fā)的情況下，可以在表的尾部插入新的數(shù)據(jù)。

InnoDB： 支持事務(wù)和行級(jí)鎖，是innodb的最大特色。行鎖大幅度提高了多用戶并發(fā)操作的新能。但是InnoDB的行鎖，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會(huì)鎖全表的。

7、 全文索引

MyISAM：支持 FULLTEXT類型的全文索引

InnoDB：不支持FULLTEXT類型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

8、表主鍵

MyISAM：允許沒(méi)有任何索引和主鍵的表存在，索引都是保存行的地址。

InnoDB：如果沒(méi)有設(shè)定主鍵或者非空唯一索引，就會(huì)自動(dòng)生成一個(gè)6字節(jié)的主鍵(用戶不可見(jiàn))，數(shù)據(jù)是主索引的一部分，附加索引保存的是主索引的值。

9、表的具體行數(shù)

MyISAM： 保存有表的總行數(shù)，如果select count() from table;會(huì)直接取出出該值。

InnoDB： 沒(méi)有保存表的總行數(shù)，如果使用select count(*) from table；就會(huì)遍歷整個(gè)表，消耗相當(dāng)大，但是在加了wehre條件后，myisam和innodb處理的方式都一樣。

10、CRUD操作

MyISAM：如果執(zhí)行大量的SELECT，MyISAM是更好的選擇。

InnoDB：如果你的數(shù)據(jù)執(zhí)行大量的INSERT或UPDATE，出于性能方面的考慮，應(yīng)該使用InnoDB表。

11、 外鍵

MyISAM：不支持

InnoDB：支持

三、sql優(yōu)化簡(jiǎn)介

1、什么情況下進(jìn)行sql優(yōu)化

性能低、執(zhí)行時(shí)間太長(zhǎng)、等待時(shí)間太長(zhǎng)、連接查詢、索引失效。

2、sql語(yǔ)句執(zhí)行過(guò)程

（1）編寫過(guò)程

select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

（2）解析過(guò)程

from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

3、sql優(yōu)化就是優(yōu)化索引

索引相當(dāng)于書的目錄。

索引的數(shù)據(jù)結(jié)構(gòu)是B+樹(shù)。

四、索引

1、索引的優(yōu)勢(shì)

（1）提高查詢效率（降低IO使用率）

（2）降低CPU使用率

比如查詢order by age desc，因?yàn)锽+索引樹(shù)本身就是排好序的，所以再查詢?nèi)绻|發(fā)索引，就不用再重新查詢了。

2、索引的弊端

（1）索引本身很大，可以存放在內(nèi)存或硬盤上，通常存儲(chǔ)在硬盤上。

（2）索引不是所有情況都使用，比如①少量數(shù)據(jù)②頻繁變化的字段③很少使用的字段

（3）索引會(huì)降低增刪改的效率

3、索引的分類

（1）單值索引

（2）唯一索引

（3）聯(lián)合索引

（4）主鍵索引

備注：唯一索引和主鍵索引唯一的區(qū)別：主鍵索引不能為null

4、創(chuàng)建索引

alter table user add INDEX `user_index_username_password` (`username`,`password`)

5、MySQL索引原理 -> B+樹(shù)

MySQL索引的底層數(shù)據(jù)結(jié)構(gòu)是B+樹(shù)

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

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í)的磁盤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è)磁盤塊能存儲(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，一般表的主鍵類型為INT（占用4個(gè)字節(jié)）或BIGINT（占用8個(gè)字節(jié)），指針類型也一般為4或8個(gè)字節(jié)，也就是說(shuō)一個(gè)頁(yè)（B+Tree中的一個(gè)節(jié)點(diǎn)）中大概存儲(chǔ)16KB/(8B+8B)=1K個(gè)鍵值（因?yàn)槭枪乐?，為方便?jì)算，這里的K取值為〖10〗^3）。也就是說(shuō)一個(gè)深度為3的B+Tree索引可以維護(hù)10^3 * 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次磁盤I/O操作。

數(shù)據(jù)庫(kù)中的B+Tree索引可以分為聚集索引（clustered index）和輔助索引（secondary index）。上面的B+Tree示例圖在數(shù)據(jù)庫(kù)中的實(shí)現(xiàn)即為聚集索引，聚集索引的B+Tree中的葉子節(jié)點(diǎn)存放的是整張表的行記錄數(shù)據(jù)。輔助索引與聚集索引的區(qū)別在于輔助索引的葉子節(jié)點(diǎn)并不包含行記錄的全部數(shù)據(jù)，而是存儲(chǔ)相應(yīng)行數(shù)據(jù)的聚集索引鍵，即主鍵。當(dāng)通過(guò)輔助索引來(lái)查詢數(shù)據(jù)時(shí)，InnoDB存儲(chǔ)引擎會(huì)遍歷輔助索引找到主鍵，然后再通過(guò)主鍵在聚集索引中找到完整的行記錄數(shù)據(jù)。

五、如何觸發(fā)聯(lián)合索引

1、對(duì)user表建立聯(lián)合索引username、password

2、觸發(fā)聯(lián)合索引

（1）使用聯(lián)合索引的全部索引鍵可觸發(fā)聯(lián)合索引

（2）使用聯(lián)合索引的全部索引鍵，但是用or連接的，不可觸發(fā)聯(lián)合索引

（3）單獨(dú)使用聯(lián)合索引的左邊第一個(gè)字段時(shí)，可觸發(fā)聯(lián)合索引

（4）單獨(dú)使用聯(lián)合索引的其它字段時(shí)，不可觸發(fā)聯(lián)合索引

六、分析sql的執(zhí)行計(jì)劃---explain

explain可以模擬sql優(yōu)化執(zhí)行sql語(yǔ)句。

1、explan使用簡(jiǎn)介

（1）用戶表

（2）部門表

（3）未觸發(fā)索引

（4）觸發(fā)索引

（5）結(jié)果分析

explain中第一行出現(xiàn)的表是驅(qū)動(dòng)表。

1. 指定了聯(lián)接條件時(shí)，滿足查詢條件的記錄行數(shù)少的表為[驅(qū)動(dòng)表]
2. 未指定聯(lián)接條件時(shí)，行數(shù)少的表為[驅(qū)動(dòng)表]

對(duì)驅(qū)動(dòng)表直接進(jìn)行排序就會(huì)觸發(fā)索引，對(duì)非驅(qū)動(dòng)表進(jìn)行排序不會(huì)觸發(fā)索引。

2、explain查詢結(jié)果簡(jiǎn)介

（1）id：SELECT識(shí)別符。這是SELECT的查詢序列號(hào)。

（2）select_type：SELECT類型：

1. SIMPLE： 簡(jiǎn)單SELECT(不使用UNION或子查詢)
2. PRIMARY： 最外面的SELECT
3. UNION：UNION中的第二個(gè)或后面的SELECT語(yǔ)句
4. DEPENDENT UNION：UNION中的第二個(gè)或后面的SELECT語(yǔ)句，取決于外面的查詢
5. UNION RESULT：UNION的結(jié)果
6. SUBQUERY：子查詢中的第一個(gè)SELECT
7. DEPENDENT SUBQUERY：子查詢中的第一個(gè)SELECT，取決于外面的查詢
8. DERIVED：導(dǎo)出表的SELECT(FROM子句的子查詢)

（3）table：表名

（4）type：聯(lián)接類型

1. system：表僅有一行(=系統(tǒng)表)。這是const聯(lián)接類型的一個(gè)特例。
2. const：表最多有一個(gè)匹配行，它將在查詢開(kāi)始時(shí)被讀取。因?yàn)閮H有一行，在這行的列值可被優(yōu)化器剩余部分認(rèn)為是常數(shù)。const用于用常數(shù)值比較PRIMARY KEY或UNIQUE索引的所有部分時(shí)。
3. eq_ref：對(duì)于每個(gè)來(lái)自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯(lián)接類型，除了const類型。它用在一個(gè)索引的所有部分被聯(lián)接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比較的帶索引的列。比較值可以為常量或一個(gè)使用在該表前面所讀取的表的列的表達(dá)式。
4. ref：對(duì)于每個(gè)來(lái)自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。如果聯(lián)接只使用鍵的最左邊的前綴，或如果鍵不是UNIQUE或PRIMARY KEY(換句話說(shuō)，如果聯(lián)接不能基于關(guān)鍵字選擇單個(gè)行的話)，則使用ref。如果使用的鍵僅僅匹配少量行，該聯(lián)接類型是不錯(cuò)的。ref可以用于使用=或<=>操作符的帶索引的列。
5. ref_or_null：該聯(lián)接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行。在解決子查詢中經(jīng)常使用該聯(lián)接類型的優(yōu)化。
6. index_merge：該聯(lián)接類型表示使用了索引合并優(yōu)化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長(zhǎng)的關(guān)鍵元素。
7. unique_subquery：該類型替換了下面形式的IN子查詢的ref：value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一個(gè)索引查找函數(shù)，可以完全替換子查詢，效率更高。
8. index_subquery：該聯(lián)接類型類似于unique_subquery。可以替換IN子查詢，但只適合下列形式的子查詢中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)
9. range：只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行。key列顯示使用了哪個(gè)索引。key_len包含所使用索引的最長(zhǎng)關(guān)鍵元素。在該類型中ref列為NULL。當(dāng)使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關(guān)鍵字列時(shí)，可以使用range
10. index：該聯(lián)接類型與ALL相同，除了只有索引樹(shù)被掃描。這通常比ALL快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。
11. all：對(duì)于每個(gè)來(lái)自于先前的表的行組合，進(jìn)行完整的表掃描。如果表是第一個(gè)沒(méi)標(biāo)記const的表，這通常不好，并且通常在它情況下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常數(shù)值或列值被檢索出。

（5）possible_keys：possible_keys列指出MySQL能使用哪個(gè)索引在該表中找到行。注意，該列完全獨(dú)立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實(shí)際上不能按生成的表次序使用。

（6）key：key列顯示MySQL實(shí)際決定使用的鍵(索引)。如果沒(méi)有選擇索引，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

（7）key_len：key_len列顯示MySQL決定使用的鍵長(zhǎng)度。如果鍵是NULL，則長(zhǎng)度為NULL。注意通過(guò)key_len值我們可以確定MySQL將實(shí)際使用一個(gè)多部關(guān)鍵字的幾個(gè)部分。

（8）ref：ref列顯示使用哪個(gè)列或常數(shù)與key一起從表中選擇行。

（9）rows：rows列顯示MySQL認(rèn)為它執(zhí)行查詢時(shí)必須檢查的行數(shù)。

（10）Extra：該列包含MySQL解決查詢的詳細(xì)信息。

1. Distinct：MySQL發(fā)現(xiàn)第1個(gè)匹配行后，停止為當(dāng)前的行組合搜索更多的行。
2. Not exists：MySQL能夠?qū)Σ樵冞M(jìn)行LEFT JOIN優(yōu)化，發(fā)現(xiàn)1個(gè)匹配LEFT JOIN標(biāo)準(zhǔn)的行后，不再為前面的的行組合在該表內(nèi)檢查更多的行。
3. range checked for each record (index map: #)：MySQL沒(méi)有發(fā)現(xiàn)好的可以使用的索引，但發(fā)現(xiàn)如果來(lái)自前面的表的列值已知，可能部分索引可以使用。對(duì)前面的表的每個(gè)行組合，MySQL檢查是否可以使用range或index_merge訪問(wèn)方法來(lái)索取行。
4. Using filesort：MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。通過(guò)根據(jù)聯(lián)接類型瀏覽所有行并為所有匹配WHERE子句的行保存排序關(guān)鍵字和行的指針來(lái)完成排序。然后關(guān)鍵字被排序，并按排序順序檢索行。
5. Using index：從只使用索引樹(shù)中的信息而不需要進(jìn)一步搜索讀取實(shí)際的行來(lái)檢索表中的列信息。當(dāng)查詢只使用作為單一索引一部分的列時(shí)，可以使用該策略。
6. Using temporary：為了解決查詢，MySQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)容納結(jié)果。典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時(shí)。
7. Using where：WHERE子句用于限制哪一個(gè)行匹配下一個(gè)表或發(fā)送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯(lián)接類型為ALL或index，查詢可能會(huì)有一些錯(cuò)誤。
8. Using sort_union(...), Using union(...), Using intersect(...)：這些函數(shù)說(shuō)明如何為index_merge聯(lián)接類型合并索引掃描。
9. Using index for group-by：類似于訪問(wèn)表的Using index方式，Using index for group-by表示MySQL發(fā)現(xiàn)了一個(gè)索引，可以用來(lái)查詢GROUP BY或DISTINCT查詢的所有列，而不要額外搜索硬盤訪問(wèn)實(shí)際的表。并且，按最有效的方式使用索引，以便對(duì)于每個(gè)組，只讀取少量索引條目。

通過(guò)相乘EXPLAIN輸出的rows列的所有值，你能得到一個(gè)關(guān)于一個(gè)聯(lián)接如何的提示。這應(yīng)該粗略地告訴你MySQL必須檢查多少行以執(zhí)行查詢。當(dāng)你使用max_join_size變量限制查詢時(shí)，也用這個(gè)乘積來(lái)確定執(zhí)行哪個(gè)多表SELECT語(yǔ)句。

到此這篇關(guān)于mysql索引詳解的文章就介紹到這了,更多相關(guān)mysql索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！