SQL查詢(xún)優(yōu)化
獲取有性能問(wèn)題的SQL
????????通過(guò)用戶(hù)
????????通過(guò)慢查日志獲取存在性能問(wèn)題的SQL
????????實(shí)時(shí)獲取存在性能問(wèn)題的SQL????
使用慢查詢(xún)?nèi)罩精@取有性能問(wèn)題的SQL
????slow_query_log 啟動(dòng)停止記錄慢查日志（默認(rèn)是關(guān)閉的）這個(gè)參數(shù)是動(dòng)態(tài)的 可以通過(guò)set global來(lái)設(shè)置這個(gè)參數(shù)
????????可以通過(guò)腳本來(lái)實(shí)現(xiàn)這個(gè)開(kāi)關(guān)的控制
set global slow_query_log=on;
slow_query_log_file 指定慢查詢(xún)?nèi)罩镜拇鎯?chǔ)路勁及文件
????默認(rèn)情況下保存在MySQL的數(shù)據(jù)目錄當(dāng)中锅纺，最好是將日志存儲(chǔ)和數(shù)據(jù)存儲(chǔ)分開(kāi)
long_query_time? 指定記錄慢查日志SQL執(zhí)行時(shí)間的伐值（可以精確到微秒）茄茁，但是單位是秒
set global long_query_time = 0;
記錄所有符合條件的語(yǔ)句
????包括查詢(xún)語(yǔ)句 和 數(shù)據(jù)修改語(yǔ)句 已經(jīng)回滾的SQL? 默認(rèn)值為10s ，通常為1毫秒
long_queries_not_using_indexes 是否記錄未使用索引的SQL
實(shí)時(shí)獲取有性能問(wèn)題的SQL
這里可以通過(guò)一個(gè)SQL腳本周期性的執(zhí)行這條SQL語(yǔ)句,這樣就可以獲取到實(shí)時(shí)監(jiān)控SQL性能的效果
????select id,`user`,`host`,DB,command,`time`,state,info from information_schema.processlist;
SQL的解析預(yù)處理及生成執(zhí)行計(jì)劃
查詢(xún)?yōu)槭裁磿?huì)慢
? ? ? ? 1.客戶(hù)端發(fā)送SQL請(qǐng)求給服務(wù)器
? ? ? ? 2.服務(wù)器檢查時(shí)候可以在查詢(xún)緩存中命中該SQL
? ? ? ? ? ? ????a.優(yōu)先檢查這個(gè)查詢(xún)是否命中查詢(xún)緩存中的數(shù)據(jù)(前提是查詢(xún)緩存是否打開(kāi)),檢測(cè)緩存是否命中的時(shí)候都需要對(duì)緩存進(jìn)行加鎖操作
????????????????????MySQL8.0已經(jīng)取消了查詢(xún)緩存,小型項(xiàng)目不想使用緩存的話可以建議在8.0以下的版本開(kāi)啟
? ? ? ? ? ? ????b.通過(guò)一個(gè)對(duì)大小寫(xiě)敏感的哈希查找實(shí)現(xiàn)的(要在查詢(xún)緩存當(dāng)中直接返回結(jié)果 是并不容易的)
????????????????????Hash查找只能進(jìn)行匹配
? ? ? ? ? ? ? ? ?c.對(duì)于一個(gè)對(duì)寫(xiě)比較頻繁的系統(tǒng)使用查詢(xún)緩存可能會(huì)降低查詢(xún)處理的效率
? ? ? ? 3.服務(wù)器端進(jìn)行SQL解析,預(yù)處理,再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃
????????4.根據(jù)執(zhí)行計(jì)劃,調(diào)用存儲(chǔ)引擎API來(lái)查詢(xún)數(shù)據(jù)
????????5.將結(jié)果返回給客戶(hù)端
MySQL依照上述的執(zhí)行計(jì)劃和存儲(chǔ)引擎進(jìn)行交互
這個(gè)階段包括了多個(gè)子過(guò)程
????解析SQL,預(yù)處理,優(yōu)化SQL執(zhí)行計(jì)劃
????????????語(yǔ)法解析階段是通過(guò)關(guān)鍵字對(duì)MySQL語(yǔ)句進(jìn)行解析,并生成一棵對(duì)應(yīng)的"解析樹(shù)"
????????????MySQL解析器將使用MySQL語(yǔ)法規(guī)則驗(yàn)證和解析查詢(xún)
????????????????????????包括檢查語(yǔ)法是否使用了正確的關(guān)鍵字
????????????????????????關(guān)鍵字的順序是否正確等
????????????預(yù)處理階段是根據(jù)目送去了規(guī)則進(jìn)一步檢查解析樹(shù)是否合法
????????????????????????檢查查詢(xún)中所涉及的表和數(shù)據(jù)列是否存在及名字或別名是否存在歧義等等,如果不語(yǔ)法檢查全部都通過(guò)了,查詢(xún)優(yōu)化器就可以生成查詢(xún)計(jì)劃了
????????????會(huì)造成MySQL生成錯(cuò)誤的執(zhí)行計(jì)劃的原因(MySQL是基于其成本模型選擇最優(yōu)的執(zhí)行計(jì)劃)
????????????????????????統(tǒng)計(jì)信息不準(zhǔn)確
????????????????????????執(zhí)行計(jì)劃中的成本估算不等同于實(shí)際的執(zhí)行計(jì)劃的成本
????????????????????????MySQL執(zhí)行的最有計(jì)劃與你認(rèn)為的最優(yōu)不一樣
????????????????????????MySQL從不會(huì)考慮其他并發(fā)的查詢(xún),這可能會(huì)印象當(dāng)前的查詢(xún)速度
????????????????????????MySQL有時(shí)候也會(huì)基于一些固定的規(guī)則來(lái)生成執(zhí)行計(jì)劃
????????????????????????MySQL不會(huì)考慮不受其版本控制的成本
????????????????????????????????存儲(chǔ)過(guò)程吃衅、用戶(hù)自定義函數(shù)
MySQL優(yōu)化器可優(yōu)化的SQL類(lèi)型
????????重新定義表的關(guān)聯(lián)順序
????????????????優(yōu)化器會(huì)根據(jù)統(tǒng)計(jì)信息來(lái)決定表的關(guān)聯(lián)順序
? ? ? ? ? 將外連接轉(zhuǎn)化為內(nèi)連接（也就是說(shuō)并不是所有的left join和right join這種外連接都是以外鏈接方式來(lái)執(zhí)行的【也就是出現(xiàn)外連接的效果等? ? ? ? ?同于內(nèi)連接，外連接+where 】）
????????????????????where條件和庫(kù)表結(jié)構(gòu)
? ??????使用等價(jià)變換規(guī)則
????????????????例如 where 5=5 and a>5優(yōu)化為a>5
????????優(yōu)化count()香椎、min()肤京、max()
? ??????將一個(gè)表達(dá)式轉(zhuǎn)化為常數(shù)表達(dá)式
????????使用等價(jià)變換規(guī)則（覆蓋索引，所查詢(xún)的數(shù)據(jù)中肾筐，索引包含了所有所需要查詢(xún)的列 ）
????????子查詢(xún)優(yōu)化
????????????????????將子查詢(xún)轉(zhuǎn)換為關(guān)聯(lián)查詢(xún)（減少查詢(xún)的次數(shù) ）
????????提前終止查詢(xún)（查詢(xún)的結(jié)果是不存在的），可以通過(guò)執(zhí)行計(jì)劃的擴(kuò)展列可以知道
????????對(duì)in()進(jìn)行優(yōu)化（在很多的SQL server中in的作用類(lèi)似于or缸剪，但是MySQL不一樣）
????????????????MySQL會(huì)先對(duì)in中的條件進(jìn)行排序吗铐，再通過(guò)二分查找判斷該值是否滿足條件，這樣相對(duì)與其他的關(guān)系型數(shù)據(jù)庫(kù)更快
確定查詢(xún)處理各個(gè)階段所消耗的時(shí)間
減少查詢(xún)所消耗的時(shí)間加快查詢(xún)的響應(yīng)速度
使用profile
還可以配合其他的命令
例如
????show profile cpu for query 3;
在使用profile的時(shí)候唬渗，都會(huì)拋出警告，提示在以后的版本當(dāng)中將不再使用profile奋渔，請(qǐng)使用Performance_Schema來(lái)進(jìn)行替換
使用performance_schema
在5.6之后的版本建議開(kāi)啟這個(gè)功能
開(kāi)啟監(jiān)控,使用performance_schema需要開(kāi)啟上面兩項(xiàng)
如果開(kāi)啟performance_schema對(duì)于全局都是有效的
特定的SQL查詢(xún)優(yōu)化
大表的數(shù)據(jù)修改最好是分批處理
對(duì)大表結(jié)構(gòu)的修改
????????對(duì)表的列的字段類(lèi)型進(jìn)行修改和改變字段的寬度都是會(huì)進(jìn)行鎖表的
????????無(wú)法接解決主從數(shù)據(jù)庫(kù)延遲問(wèn)題
????????????????1.方案一：利用主從復(fù)制的架構(gòu)嫉鲸，先在從服務(wù)器上進(jìn)行修改撑蒜，在進(jìn)行主從切換（存在一定的風(fēng)險(xiǎn)），再對(duì)用原主（從）服務(wù)器進(jìn)行修改
????????????????2.方案二：先在主服務(wù)器上建立一個(gè)新表玄渗，新表的結(jié)構(gòu)就是舊表進(jìn)行修改過(guò)后的結(jié)構(gòu)座菠，再將老表的數(shù)據(jù)導(dǎo)入新表當(dāng)中，并在老表中建立一系列的觸發(fā)器藤树，? ? ? ? ? ? ? ? ?將老表中的數(shù)據(jù)同步到新表當(dāng)中浴滴，當(dāng)數(shù)據(jù)完成同步之后，再對(duì)老表添加一個(gè)排它鎖并重新命名,最終刪除老表
????????????????????3.排他鎖又稱(chēng)為寫(xiě)鎖岁钓，簡(jiǎn)稱(chēng)X鎖升略，顧名思義，排他鎖就是不能與其他所并存甜紫，如一個(gè)事務(wù)獲取了一個(gè)數(shù)據(jù)行的排他鎖降宅，其他事務(wù)就不能再獲取該行的其? ? ? ? ? ? ? ? ? ? ?他鎖，包括共享鎖和排他鎖囚霸，但是獲取排他鎖的事務(wù)是可以對(duì)數(shù)據(jù)就行讀取和修改腰根。
使用匯總表優(yōu)化查詢(xún)
