一扫步、MySQL執(zhí)行流程
查詢執(zhí)行流程
1.連接
1.1客戶端發(fā)起一條Query請(qǐng)求，監(jiān)聽客戶端的‘連接管理模塊’接收請(qǐng)求
1.2將請(qǐng)求轉(zhuǎn)發(fā)到‘連接進(jìn)/線程模塊’
1.3調(diào)用‘用戶模塊’來進(jìn)行授權(quán)檢查
1.4通過檢查后氓皱，‘連接進(jìn)/線程模塊’從‘線程連接池’中取出空閑的被緩存的連接線程和客戶端請(qǐng)求對(duì)接，如果失敗則創(chuàng)建一個(gè)新的連接請(qǐng)求
2.處理
2.1先查詢緩存贮懈，檢查Query語句是否完全匹配匀泊，接著再檢查是否具有權(quán)限，都成功則直接取數(shù)據(jù)返回
2.2上一步有失敗則轉(zhuǎn)交給‘命令解析器’朵你，經(jīng)過詞法分析，語法分析后生成解析樹
2.3接下來是預(yù)處理階段揣非，處理解析器無法解決的語義抡医，檢查權(quán)限等，生成新的解析樹
2.4再轉(zhuǎn)交給對(duì)應(yīng)的模塊處理
2.5如果是SELECT查詢還會(huì)經(jīng)由‘查詢優(yōu)化器’做大量的優(yōu)化早敬，生成執(zhí)行計(jì)劃
2.6模塊收到請(qǐng)求后忌傻，通過‘訪問控制模塊’檢查所連接的用戶是否有訪問目標(biāo)表和目標(biāo)字段的權(quán)限
2.7有則調(diào)用‘表管理模塊’，先是查看table cache中是否存在搞监，有則直接對(duì)應(yīng)的表和獲取鎖水孩，否則重新打開表文件
2.8根據(jù)表的meta數(shù)據(jù)，獲取表的存儲(chǔ)引擎類型等信息琐驴，通過接口調(diào)用對(duì)應(yīng)的存儲(chǔ)引擎處理
2.9上述過程中產(chǎn)生數(shù)據(jù)變化的時(shí)候俘种，若打開日志功能秤标，則會(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)求或斷開與客戶端的連接
二、Sql語句執(zhí)行順序
MySQL的語句一共分為10步悬包，如下所標(biāo)注的那樣衙猪，最先執(zhí)行的總是FROM操作，最后執(zhí)行的是LIMIT操作布近。其中每一個(gè)操作都會(huì)產(chǎn)生一張?zhí)摂M的表垫释，這個(gè)虛擬的表作為一個(gè)處理的輸入，只是這些虛擬的表對(duì)用戶來說是透明的撑瞧，但是只有最后一個(gè)虛擬的表才會(huì)被作為結(jié)果返回饶号。如果沒有在語句中指定某一個(gè)子句，那么將會(huì)跳過相應(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>
下面我們來具體分析一下查詢處理的每一個(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條件過濾。只有符合<where-condition>的記錄才會(huì)被插入到虛擬表VT4中践宴。
- GROUP BY: 根據(jù)group by子句中的列鲸匿，對(duì)VT4中的記錄進(jìn)行分組操作，產(chǎn)生VT5.
- HAVING： 對(duì)虛擬表VT5應(yīng)用having過濾阻肩，只有符合<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類似于where語句刻诊，只是having過濾是基于group by 分組后的數(shù)據(jù)，having一般通過select語句里面的聚集函數(shù)進(jìn)行過濾牺丙。
三则涯、MySql索引
索引的類型
- 普通索引
- 唯一索引
- 多列索引：一個(gè)索引包含多列，符合最左前綴
- 全文索引：InnoDB不支持冲簿，Myisam支持性能比較好粟判，一般在 CHAR、VARCHAR 或 TEXT 列上創(chuàng)建峦剔。
索引使用規(guī)則
- 若查詢條件中不包含索引的最左列档礁，無法使用索引
- 對(duì)于范圍查詢，只能利用索引的最左列
- 對(duì)于order by A語句吝沫，在A上建立索引呻澜，可以避免排序
- 對(duì)于group by A語句，在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ǔ)引擎诽嘉。
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)有的值開始增值稀余，如果有但是比現(xiàn)在的還大悦冀，則就保存這個(gè)值。
（3）innodb存儲(chǔ)引擎支持外鍵（foreign key） ,外鍵所在的表稱為子表而所依賴的表稱為父表睛琳。
（4）innodb存儲(chǔ)引擎支持mvcc的行級(jí)鎖盒蟆。
（5）innodb存儲(chǔ)引擎索引使用的是B+Tree
2踏烙、MyISAM存儲(chǔ)引擎
（1）MyISAM這種存儲(chǔ)引擎不支持事務(wù)，不支持行級(jí)鎖历等，只支持并發(fā)插入的表鎖讨惩，主要用于高負(fù)載的select。
（2）MyISAM類型的表支持三種不同的存儲(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ù)來創(chuàng)建表魂角，而且所有的數(shù)據(jù)也都存儲(chǔ)在內(nèi)存中。
（2）每個(gè)基于memory存儲(chǔ)引擎的表實(shí)際對(duì)應(yīng)一個(gè)磁盤文件智绸，該文件的文件名和表名是相同的野揪，類型為.frm。該文件只存儲(chǔ)表的結(jié)構(gòu)瞧栗，而其數(shù)據(jù)文件斯稳，都是存儲(chǔ)在內(nèi)存中，這樣有利于對(duì)數(shù)據(jù)的快速處理沼溜，提高整個(gè)表的處理能力平挑。
（3）memory存儲(chǔ)引擎默認(rèn)使用哈希（HASH）索引，其速度比使用B-+Tree型要快系草，如果讀者希望使用B樹型通熄，則在創(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語句，以%或者‘-’開頭的不會(huì)使用索引癞己，以%結(jié)尾會(huì)使用索引
- 盡量不要在列上進(jìn)行運(yùn)算（函數(shù)操作和表達(dá)式操作）
- 盡量不要使用not in和<>操作
2.sql語句的優(yōu)化
- 查詢時(shí)膀斋，能不要就不用，盡量寫全字段名
- 大部分情況連接效率遠(yuǎn)大于子查詢
- 多使用explain和profile分析查詢語句
- 查看慢查詢?nèi)罩颈匝牛页鰣?zhí)行時(shí)間長(zhǎng)的sql語句優(yōu)化
- 多表連接時(shí)仰担，盡量小表驅(qū)動(dòng)大表，即小表 join 大表
- 在千萬級(jí)分頁時(shí)使用limit
- 對(duì)于經(jīng)常使用的查詢绩社，可以開啟緩存
3.表的優(yōu)化
- 表的字段盡可能用NOT NULL
- 字段長(zhǎng)度固定的表查詢會(huì)更快
- 把數(shù)據(jù)庫的大表按時(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
