一、MySQL執(zhí)行流程
查詢執(zhí)行流程
1.連接
1.1客戶端發(fā)起一條Query請(qǐng)求，監(jiān)聽(tīng)客戶端的‘連接管理模塊’接收請(qǐng)求
1.2將請(qǐng)求轉(zhuǎn)發(fā)到‘連接進(jìn)/線程模塊’
1.3調(diào)用‘用戶模塊’來(lái)進(jìn)行授權(quán)檢查
1.4通過(guò)檢查后，‘連接進(jìn)/線程模塊’從‘線程連接池’中取出空閑的被緩存的連接線程和客戶端請(qǐng)求對(duì)接，如果失敗則創(chuàng)建一個(gè)新的連接請(qǐng)求
2.處理
2.1先查詢緩存，檢查Query語(yǔ)句是否完全匹配，接著再檢查是否具有權(quán)限，都成功則直接取數(shù)據(jù)返回
2.2上一步有失敗則轉(zhuǎn)交給‘命令解析器’，經(jīng)過(guò)詞法分析，語(yǔ)法分析后生成解析樹(shù)
2.3接下來(lái)是預(yù)處理階段，處理解析器無(wú)法解決的語(yǔ)義，檢查權(quán)限等，生成新的解析樹(shù)
2.4再轉(zhuǎn)交給對(duì)應(yīng)的模塊處理
2.5如果是SELECT查詢還會(huì)經(jīng)由‘查詢優(yōu)化器’做大量的優(yōu)化，生成執(zhí)行計(jì)劃
2.6模塊收到請(qǐng)求后，通過(guò)‘訪問(wèn)控制模塊’檢查所連接的用戶是否有訪問(wèn)目標(biāo)表和目標(biāo)字段的權(quán)限
2.7有則調(diào)用‘表管理模塊’，先是查看table cache中是否存在，有則直接對(duì)應(yīng)的表和獲取鎖，否則重新打開(kāi)表文件
2.8根據(jù)表的meta數(shù)據(jù)，獲取表的存儲(chǔ)引擎類(lèi)型等信息，通過(guò)接口調(diào)用對(duì)應(yīng)的存儲(chǔ)引擎處理
2.9上述過(guò)程中產(chǎn)生數(shù)據(jù)變化的時(shí)候，若打開(kāi)日志功能，則會(huì)記錄到相應(yīng)二進(jìn)制日志文件中
3.結(jié)果
3.1Query請(qǐng)求完成后，將結(jié)果集返回給‘連接進(jìn)/線程模塊’
3.2返回的也可以是相應(yīng)的狀態(tài)標(biāo)識(shí)，如成功或失敗等
3.3‘連接進(jìn)/線程模塊’進(jìn)行后續(xù)的清理工作，并繼續(xù)等待請(qǐng)求或斷開(kāi)與客戶端的連接
二、Sql語(yǔ)句執(zhí)行順序
MySQL的語(yǔ)句一共分為10步，如下所標(biāo)注的那樣，最先執(zhí)行的總是FROM操作，最后執(zhí)行的是LIMIT操作。其中每一個(gè)操作都會(huì)產(chǎn)生一張?zhí)摂M的表，這個(gè)虛擬的表作為一個(gè)處理的輸入，只是這些虛擬的表對(duì)用戶來(lái)說(shuō)是透明的，但是只有最后一個(gè)虛擬的表才會(huì)被作為結(jié)果返回。如果沒(méi)有在語(yǔ)句中指定某一個(gè)子句，那么將會(huì)跳過(guò)相應(yīng)的步驟。
(7) SELECT
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>
下面我們來(lái)具體分析一下查詢處理的每一個(gè)階段
- FORM: 對(duì)FROM的左邊的表和右邊的表計(jì)算笛卡爾積。產(chǎn)生虛表VT1
- ON: 對(duì)虛表VT1進(jìn)行ON篩選，只有那些符合<join-condition>的行才會(huì)被記錄在虛表VT2中。
- JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就會(huì)作為外部行添加到虛擬表VT2中，產(chǎn)生虛擬表VT3。如果from子句中包含兩個(gè)以上的表的話，那么就會(huì)對(duì)上一個(gè)join連接產(chǎn)生的結(jié)果VT3和下一個(gè)表重復(fù)執(zhí)行步驟1~3這三個(gè)步驟，一直到處理完所有的表為止。
- WHERE： 對(duì)虛擬表VT3進(jìn)行WHERE條件過(guò)濾。只有符合<where-condition>的記錄才會(huì)被插入到虛擬表VT4中。
- GROUP BY: 根據(jù)group by子句中的列，對(duì)VT4中的記錄進(jìn)行分組操作，產(chǎn)生VT5.
- HAVING： 對(duì)虛擬表VT5應(yīng)用having過(guò)濾，只有符合<having-condition>的記錄才會(huì)被 插入到虛擬表VT6中。
- SELECT： 執(zhí)行select操作，選擇指定的列，插入到虛擬表VT7中。
- DISTINCT： 對(duì)VT7中的記錄進(jìn)行去重。產(chǎn)生虛擬表VT8.
- ORDER BY: 將虛擬表VT8中的記錄按照<order_by_list>進(jìn)行排序操作，產(chǎn)生虛擬表VT9.
- LIMIT：取出指定行的記錄，產(chǎn)生虛擬表VT10, 并將結(jié)果返回。
注意：
group by 存在時(shí)，select中除了聚集函數(shù)（sum、avg等）外，所有的基本列必須是group by里面存在的；having基本上同group by一起使用的，having類(lèi)似于where語(yǔ)句，只是having過(guò)濾是基于group by 分組后的數(shù)據(jù)，having一般通過(guò)select語(yǔ)句里面的聚集函數(shù)進(jìn)行過(guò)濾。
三、MySql索引
索引的類(lèi)型
- 普通索引
- 唯一索引
- 多列索引：一個(gè)索引包含多列，符合最左前綴
- 全文索引：InnoDB不支持，Myisam支持性能比較好，一般在 CHAR、VARCHAR 或 TEXT 列上創(chuàng)建。
索引使用規(guī)則
- 若查詢條件中不包含索引的最左列，無(wú)法使用索引
- 對(duì)于范圍查詢，只能利用索引的最左列
- 對(duì)于order by A語(yǔ)句，在A上建立索引，可以避免排序
- 對(duì)于group by A語(yǔ)句，在A上建立索引，可以避免排序
- 對(duì)于多列排序，需要所有所有列排序方向一致，才能利用索引。
InnoDB的索引結(jié)構(gòu)
- 非葉子節(jié)點(diǎn)是葉子節(jié)點(diǎn)的索引
- 葉子節(jié)點(diǎn)是數(shù)據(jù)層
- 任一值搜索深度相同
四、MySql存儲(chǔ)引擎
存儲(chǔ)引擎其實(shí)就是如何實(shí)現(xiàn)存儲(chǔ)數(shù)據(jù)，如何為存儲(chǔ)的數(shù)據(jù)建立索引以及如何更新，查詢數(shù)據(jù)等技術(shù)實(shí)現(xiàn)的方法。
MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲(chǔ)在文件（或內(nèi)存）中，這些技術(shù)中的每一種技術(shù)都使用不同的存儲(chǔ)機(jī)制，索引技巧，鎖定水平并且最終提供廣泛的不同功能和能力。在MySQL中將這些不同的技術(shù)及配套的相關(guān)功能稱(chēng)為存儲(chǔ)引擎。
1、InnoDB
（1）innodb存儲(chǔ)引擎最重要的是支持事務(wù)，以及事務(wù)相關(guān)聯(lián)功能。
（2）innodb支持自增長(zhǎng)列（auto_increment）,自增長(zhǎng)列的值不能為空，如果在使用的時(shí)候?yàn)榭盏脑捲鯐?huì)進(jìn)行自動(dòng)存現(xiàn)有的值開(kāi)始增值，如果有但是比現(xiàn)在的還大，則就保存這個(gè)值。
（3）innodb存儲(chǔ)引擎支持外鍵（foreign key） ,外鍵所在的表稱(chēng)為子表而所依賴(lài)的表稱(chēng)為父表。
（4）innodb存儲(chǔ)引擎支持mvcc的行級(jí)鎖。
（5）innodb存儲(chǔ)引擎索引使用的是B+Tree
2、MyISAM存儲(chǔ)引擎
（1）MyISAM這種存儲(chǔ)引擎不支持事務(wù)，不支持行級(jí)鎖，只支持并發(fā)插入的表鎖，主要用于高負(fù)載的select。
（2）MyISAM類(lèi)型的表支持三種不同的存儲(chǔ)結(jié)構(gòu)：靜態(tài)型、動(dòng)態(tài)型、壓縮型。
（3）MyISAM也是使用B+tree索引但是和Innodb的在具體實(shí)現(xiàn)上有些不同。
3、MEMORY存儲(chǔ)引擎
（1）memory存儲(chǔ)引擎相比前面的一些存儲(chǔ)引擎，有點(diǎn)不一樣，其使用存儲(chǔ)在內(nèi)從中的數(shù)據(jù)來(lái)創(chuàng)建表，而且所有的數(shù)據(jù)也都存儲(chǔ)在內(nèi)存中。
（2）每個(gè)基于memory存儲(chǔ)引擎的表實(shí)際對(duì)應(yīng)一個(gè)磁盤(pán)文件，該文件的文件名和表名是相同的，類(lèi)型為.frm。該文件只存儲(chǔ)表的結(jié)構(gòu)，而其數(shù)據(jù)文件，都是存儲(chǔ)在內(nèi)存中，這樣有利于對(duì)數(shù)據(jù)的快速處理，提高整個(gè)表的處理能力。
（3）memory存儲(chǔ)引擎默認(rèn)使用哈希（HASH）索引，其速度比使用B-+Tree型要快，如果讀者希望使用B樹(shù)型，則在創(chuàng)建的時(shí)候可以引用。
（4）memory存儲(chǔ)引擎文件數(shù)據(jù)都存儲(chǔ)在內(nèi)存中，如果mysqld進(jìn)程發(fā)生異常，重啟或關(guān)閉機(jī)器這些數(shù)據(jù)都會(huì)消失。所以memory存儲(chǔ)引擎中的表的生命周期很短，一般只使用一次。
4、BlackHole存儲(chǔ)引擎（黑洞引擎）
支持事務(wù)，而且支持mvcc的行級(jí)鎖，主要用于日志記錄或同步歸檔，這個(gè)存儲(chǔ)引擎除非有特別目的，否則不適合使用！
五、MySql性能優(yōu)化
1.索引的優(yōu)化
- 只要列中含有NULL值，就最好不要在此例設(shè)置索引，復(fù)合索引如果有NULL值，此列在使用時(shí)也不會(huì)使用索引
- 盡量使用短索引，如果可以，應(yīng)該制定一個(gè)前綴長(zhǎng)度
- 對(duì)于經(jīng)常在where子句使用的列，最好設(shè)置索引，這樣會(huì)加快查找速度
- 對(duì)于有多個(gè)列where或者order by子句的，應(yīng)該建立復(fù)合索引
- 對(duì)于like語(yǔ)句，以%或者‘-’開(kāi)頭的不會(huì)使用索引，以%結(jié)尾會(huì)使用索引
- 盡量不要在列上進(jìn)行運(yùn)算（函數(shù)操作和表達(dá)式操作）
- 盡量不要使用not in和<>操作
2.sql語(yǔ)句的優(yōu)化
- 查詢時(shí)，能不要就不用，盡量寫(xiě)全字段名
- 大部分情況連接效率遠(yuǎn)大于子查詢
- 多使用explain和profile分析查詢語(yǔ)句
- 查看慢查詢?nèi)罩?，找出?zhí)行時(shí)間長(zhǎng)的sql語(yǔ)句優(yōu)化
- 多表連接時(shí)，盡量小表驅(qū)動(dòng)大表，即小表 join 大表
- 在千萬(wàn)級(jí)分頁(yè)時(shí)使用limit
- 對(duì)于經(jīng)常使用的查詢，可以開(kāi)啟緩存
3.表的優(yōu)化
- 表的字段盡可能用NOT NULL
- 字段長(zhǎng)度固定的表查詢會(huì)更快
- 把數(shù)據(jù)庫(kù)的大表按時(shí)間或一些標(biāo)志分成小表
- 將表分區(qū)
參考資料：
[1] https://www.cnblogs.com/annsshadow/p/5037667.html
[2] https://www.cnblogs.com/Steven0805/p/6553538.html
[3] https://www.cnblogs.com/rollenholt/p/3776923.html
[4] https://blog.csdn.net/qh_java/article/details/14045827
[5] https://blog.csdn.net/seudongnan/article/details/57086633
