可以通過explain 語句來對查詢進(jìn)行查看.對可優(yōu)化的地方做出改進(jìn)
前言
優(yōu)化的主要組成部分：
- 查詢語句的優(yōu)化
- 索引設(shè)計(jì)的優(yōu)化
- 庫表設(shè)計(jì)的優(yōu)化
一個(gè)查詢操作實(shí)際上是多個(gè)子任務(wù)組成，優(yōu)化查詢實(shí)際上是優(yōu)化子任務(wù)的執(zhí)行
-
一個(gè)查詢的子任務(wù)清單：
- 網(wǎng)絡(luò)IO
- 磁盤IO
- CPU計(jì)算
- 互斥鎖等待
優(yōu)化數(shù)據(jù)訪問
避免存儲多余數(shù)據(jù)
- 避免大量無用數(shù)據(jù)存在于數(shù)據(jù)表中,加大查詢語句的執(zhí)行復(fù)雜度
- 避免大量無用的行
避免無用的查詢
- ：例如在分頁查詢時(shí),添加合適的LIMIT
- 聯(lián)查時(shí)只取出有用的數(shù)據(jù)话原，避免下面的“一把抓“式的請求
SELECT * FROM A INNER JOIN B WHRER A.ID = B.A_ID
- 按需取出響應(yīng)的列
- 添加緩存
- 在慢查詢?nèi)罩局性穸治鲆粋€(gè)查詢的查詢時(shí)間是否合理可以利用快速上限法進(jìn)行估量： 估算這個(gè)查詢大約會經(jīng)過的隨機(jī)IO數(shù) * 具體硬件下一次IO的時(shí)間,對查詢進(jìn)行估計(jì)
- 優(yōu)化聯(lián)查！優(yōu)化聯(lián)查！聯(lián)查時(shí)掃描的行是 矩陣式的 M * N ,很恐怖鳖孤，所以找到好的對飲關(guān)系是優(yōu)化聯(lián)查的
重構(gòu)查詢時(shí)的方式
一個(gè)復(fù)雜的查詢還是多個(gè)簡單的查詢
對于mysql 來說岗憋，ＭＹＳＱＬ從設(shè)計(jì)上讓連接和斷開鏈接都很輕量級，在返回一個(gè)小的查詢結(jié)果方面很高效。對于網(wǎng)絡(luò)鏈接來說現(xiàn)代網(wǎng)絡(luò)鏈接的效率相對與磁盤IO的效率來說已經(jīng)不是問題欢策。通常來將吆寨，兩者的區(qū)別不僅僅在于傳輸速度，磁盤IO在查詢時(shí)還有Lookup的過程踩寇。
對于復(fù)雜的查詢來說啄清，將其分解成多個(gè)簡單的查詢“乘铮或許效率更快些辣卒。但是同樣的，具場景具體分析
將復(fù)雜的操作切分帶來的另一個(gè)好處是可以分解復(fù)雜操作長時(shí)間對于讀/寫鎖長時(shí)間占有睛榄，從而避免影響其他業(yè)務(wù)操作的執(zhí)行
- 客戶端發(fā)送一條查詢給服務(wù)器
- 服務(wù)器首先查詢緩存荣茫。如果命中，則立即返回存儲在存儲在緩存中的結(jié)果场靴。否則進(jìn)入下一階段
- 服務(wù)器端進(jìn)行SQL解析啡莉，預(yù)處理，再由優(yōu)化器生成對應(yīng)的執(zhí)行計(jì)劃
- MYSQL根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃旨剥，調(diào)用存儲引擎的API來執(zhí)行查詢
- 將結(jié)果集返回給客戶端
MYSQL 客戶端和服務(wù)器端通信
總的來說咧欣，MYSQL客戶端和服務(wù)器端是一個(gè)半雙工的通信狀態(tài)，也就是說轨帜，在同一個(gè)時(shí)間魄咕，只能有一端像另一端發(fā)送數(shù)據(jù)（不要被JDBC的線程池的概念混淆）
SHOW ALL PROCESSLIST
各個(gè)MYSQL鏈接對應(yīng)的狀態(tài) ：
- SLEEP ： 線程正在等待客戶端發(fā)送新的請求
- QUERY ： 線程正在執(zhí)行查詢或者正在將結(jié)果發(fā)送給客戶端
- LOCKED : 線程正在等待表鎖
- Sending Data : 這表示多種情況： 線程可能在多個(gè)狀態(tài)之間傳送數(shù)據(jù)，或者在 生成結(jié)果集阵谚，或者在客戶端返回?cái)?shù)據(jù)
查詢緩存
查詢緩存是通過一個(gè)對 大小寫敏感的哈希查找實(shí)現(xiàn)的蚕礼。如果緩存中緩存有對應(yīng)數(shù)據(jù)。數(shù)據(jù)會直接返回給客戶端梢什。
- 上面的這個(gè)大小寫敏感奠蹬，不代表SQL語法關(guān)鍵字敏感。
- 雖然語法檢查在查詢緩存之后嗡午，但是你可以考慮到這一點(diǎn)囤躁。語法錯(cuò)誤的查詢在第一次執(zhí)行的就沒成功，就不會有緩存荔睹！
查詢處理優(yōu)化
查詢的下一周期是將一個(gè)SQL 轉(zhuǎn)換為一個(gè)執(zhí)行計(jì)劃狸演，MYSQL再依照這個(gè)計(jì)劃和存儲引擎進(jìn)行交互。這包括多個(gè)子階段：解析SQL僻他，預(yù)處理宵距，優(yōu)化SQL執(zhí)行計(jì)劃。
語法解析器和預(yù)處理
階段所做工作
- 語法解析器負(fù)責(zé)語法檢查
- 預(yù)處理器根據(jù)MYSQL規(guī)則 進(jìn)一步檢查 解析樹是否合法吨拗，例如檢查數(shù)據(jù)表和數(shù)據(jù)列是否存在满哪，還會解析名字和別名婿斥，看他們是否有歧義
查詢優(yōu)化器
- 可以通過
show status like 'Last_query_cost'的值來得知MYSQL計(jì)算的當(dāng)前查詢的成本
explain SELECT * FROM commonservice.regions where amapid is not null;
show status like 'Last_query_ cost'
| Variable_name | Value |
|---|---|
| 'Last_query_cost' | '142998.599000' |
上面的查詢結(jié)果上面的查詢需要 142998個(gè)數(shù)據(jù)頁的隨機(jī)查找才能完成這個(gè)查詢。當(dāng)然這只是查詢優(yōu)化器的評估結(jié)果哨鸭，且優(yōu)化器在評估時(shí)不會考拉
-
影響MYSQL 錯(cuò)誤選擇執(zhí)行計(jì)劃的因素
- 統(tǒng)計(jì)信息不準(zhǔn)確民宿，例如：InnoDB因?yàn)槠?a target="_blank" rel="nofollow">MVCC的架構(gòu)，并不能維護(hù)一個(gè)數(shù)據(jù)表的行數(shù)的精確統(tǒng)計(jì)信息
- 執(zhí)行計(jì)劃中的成本估算不等于實(shí)際執(zhí)行計(jì)劃,也就是說優(yōu)化器給出的執(zhí)行計(jì)劃也可能不是最優(yōu)的像鸡，比如說活鹰，有些頁面讀取雖多，但是其頁面大部分都是順序讀寫只估。
- MYSQL 從不考慮其他并發(fā)執(zhí)行的查詢志群，這可能會影響當(dāng)前查詢速度
MYSQL 的查詢優(yōu)化的優(yōu)化策略分為兩種
靜態(tài)優(yōu)化 ： 靜態(tài)優(yōu)化不依賴于特別的數(shù)值，如WHERE 條件中帶入的一些常數(shù)等仅乓，靜態(tài)優(yōu)化在第一次完成后就一直有效赖舟，即使使用不同的參數(shù)重復(fù)查詢也不會發(fā)生變化，可以將靜態(tài)優(yōu)化認(rèn)為是一種 “編譯時(shí)優(yōu)化”
動態(tài)優(yōu)化 : 動態(tài)優(yōu)化則和查詢的上下文有關(guān)夸楣，例如WHERE 條件中的取值宾抓，索引中條目對應(yīng)的數(shù)據(jù)行數(shù)等，這需要在每次查詢的時(shí)候重新評估 可以將動態(tài)優(yōu)化認(rèn)為是一種”運(yùn)行時(shí)優(yōu)化“
MYSQL能夠優(yōu)化的的類型
＋ 重新定義關(guān)聯(lián)表的順序 : 數(shù)據(jù)表的關(guān)聯(lián)并不是按照在查詢中指定的順序執(zhí)行
＋ 將外鏈接轉(zhuǎn)換為內(nèi)鏈接 : 并不是所有的OUTER JOIN 語句都必須以外鏈接的方式執(zhí)行豫喧。例如石洗，WHERE條件，庫表結(jié)構(gòu)都可能會讓外鏈接等價(jià)于一個(gè)內(nèi)連接
- 使用等價(jià)變換規(guī)則 利用等價(jià)變換來簡化病規(guī)范表達(dá)式
- 優(yōu)化COUNT() ,MIN() ,MAX() ： 通過B-Tree索引 直接查找其中的一個(gè)方向
- **預(yù)估并轉(zhuǎn)化為常數(shù)表達(dá)式 : **
- 覆蓋索引掃描 ： 當(dāng)索引中的列包含查詢中需要的列紧显，MYSQL會直接返回索引對應(yīng)的列讲衫，不再去查表
- **子查詢優(yōu)化 : ** Mysql 在某些情況下可以將子查詢轉(zhuǎn)換為一種效率更高的形式，從而減少多個(gè)查詢對數(shù)據(jù)進(jìn)行訪問
- 提前終止查詢： 當(dāng)發(fā)現(xiàn)已經(jīng)滿足查詢需求的時(shí)候孵班，MYSQL 總是能夠立刻終止查詢涉兽。比如使用LIMIT語句或者發(fā)現(xiàn)了一個(gè)不成立的條件，這時(shí)候MYSQL會立即返回一個(gè)空結(jié)果
- 列表IN ()的優(yōu)化 ： ** 在MYSQL 中篙程，MYSQL首選將IN()列表中的數(shù)據(jù)先進(jìn)行排序枷畏，然后通過二分查找的方式來確定列表中的值是否滿足條件（時(shí)間復(fù)雜度 ： O(lgN) ）,如果等價(jià)的轉(zhuǎn)換為OR操作的話，時(shí)間復(fù)雜度為 O(n) ：所以說 單列 多值匹配時(shí)虱饿，利用IN () 效率會更高**
Mysql 如果執(zhí)行關(guān)聯(lián)查詢
- 對于UNION查詢拥诡，MYSQL先將一系列的單個(gè)查詢結(jié)果放到一個(gè)臨時(shí)表中，然后再讀出臨時(shí)表中數(shù)據(jù)完成UNION查詢
執(zhí)行計(jì)劃 : MYSQL 并不會生成查詢字節(jié)碼來執(zhí)行查詢氮发。MYSQL生成查詢的一顆指令樹渴肉，然后通過存儲引擎執(zhí)行完成這可指令樹并返回結(jié)果。最終執(zhí)行計(jì)劃包含了重構(gòu)查詢的全部信息
優(yōu)化特定類型的查詢
優(yōu)化COUNT()查詢
- 如果想要查詢行數(shù)爽冕，使用count() 進(jìn)行查詢仇祭，如果在count()函數(shù)中指定了某列，查詢的是這個(gè)列有值（非 NULL）的值的個(gè)數(shù)颈畸！
優(yōu)化關(guān)聯(lián)查詢
- 確保ON或者USING 子句的列上有索引乌奇，一般來說嚣艇，除非有其他理由，否則只需要在關(guān)聯(lián)順序中的第二個(gè)表上建立索引
- 確保仁和Group by 或者Order by中的表達(dá)式只涉及到一個(gè)表中的列
GROUP By 和 DISTINCT
- 在無法使用索引的時(shí)候华弓，GROUP BY使用臨時(shí)表或者文件排序來分組
- 如果需要對 關(guān)聯(lián)查詢做分組，并且是按照查找表中的某個(gè)列進(jìn)行分組困乒，那么通常采用查找表的標(biāo)識列分組的效率比其他列更高
