MySQL SQL語(yǔ)句分析與查詢優(yōu)化詳解

]# pt-query-digest --user=root --password=redhat --host=127.0.0.1 slow-mysql > slow.rep
分析的結(jié)果如下

MySQL服務(wù)器處理查詢請(qǐng)求的整個(gè)過(guò)程

1、客戶端發(fā)送SQL請(qǐng)求給服務(wù)器
2、服務(wù)器檢查是否存在在緩存服務(wù)器中命中該SQL
3、服務(wù)器端進(jìn)行SQL解析，預(yù)處理，再由優(yōu)化器對(duì)應(yīng)執(zhí)行計(jì)劃
4、根據(jù)執(zhí)行計(jì)劃，調(diào)用存儲(chǔ)引擎API來(lái)查詢數(shù)據(jù)
5、將結(jié)果返回給客戶端

查詢緩存對(duì)SQL性能的影響

1、優(yōu)先檢查整個(gè)查詢是否命中查詢緩存中的數(shù)據(jù)
2、通過(guò)一個(gè)對(duì)大小寫敏感的哈希查找實(shí)現(xiàn)的

查詢緩存的優(yōu)化參數(shù)

query_cache_type 設(shè)置查詢緩存是否可用
ON，OFF，DEMAND

注意：DEMAND表示只有在查詢語(yǔ)句中使用SQL——CACHE和SQL_NO_CACHE來(lái)控制是否需要緩存

query_cache_size 設(shè)置查詢緩存的內(nèi)存大小

query_cache_limit 設(shè)置查詢緩存可用存儲(chǔ)的最大值

query_cache_wlock_invalidate 設(shè)置數(shù)據(jù)表被鎖后是否返回緩存中的數(shù)據(jù)(默認(rèn)是關(guān)閉的，建議也是關(guān)閉的此選項(xiàng))

query_cache_min_res_unit 設(shè)置查詢緩存分配的內(nèi)存塊最小的值

會(huì)造成MySQL生成錯(cuò)誤的執(zhí)行計(jì)劃的原因

1、統(tǒng)計(jì)信息不準(zhǔn)確
2、執(zhí)行計(jì)劃中的成本估算不等同于實(shí)際的執(zhí)行計(jì)劃的成本
3、MySQL優(yōu)化器所認(rèn)為的最優(yōu)可能與你所認(rèn)為的最優(yōu)不一樣
4、MySQL從不考慮其他并發(fā)的查詢，這可能會(huì)影響當(dāng)前查詢數(shù)據(jù)
5、MySQL有時(shí)候也會(huì)基于一些固定的規(guī)則來(lái)生成執(zhí)行計(jì)劃
6、MySQL不會(huì)考慮不受其控制的成本

MySQL優(yōu)化器可優(yōu)化的SQL類型

1、重新定義表的關(guān)聯(lián)順序
優(yōu)化器會(huì)根據(jù)統(tǒng)計(jì)信息來(lái)決定表的關(guān)聯(lián)順序

2、將外鏈接轉(zhuǎn)換成內(nèi)連接
where條件和庫(kù)表結(jié)構(gòu)等

3、使用等價(jià)變換規(guī)則
(5=5 and a > 5)將會(huì)被改寫成 a > 5

4、優(yōu)化count(), min()和max()
select tables optimized away
優(yōu)化器已經(jīng)從執(zhí)行計(jì)劃中移除了該表，并以一個(gè)常數(shù)取而代之

5、將一個(gè)表達(dá)式轉(zhuǎn)換為常數(shù)表達(dá)式

6、使用等價(jià)變換規(guī)則

7、子查詢優(yōu)化

8、對(duì)in()條件進(jìn)行優(yōu)化

如何確定查詢處理各個(gè)階段所消耗的時(shí)間

使用profile

set profiling = 1;
執(zhí)行查詢：
show profiles;

show profile for query N;

查詢的每個(gè)階段所消耗的時(shí)間

使用profile查看語(yǔ)句所消耗的時(shí)間

特定的SQL查詢優(yōu)化

1、利用主從切換的原理進(jìn)行大表的表結(jié)構(gòu)修改，例如，現(xiàn)在從服務(wù)器上修改，修改完畢以后，進(jìn)行主從切換，再在原來(lái)老的主上進(jìn)行大表的修改，存在一定的風(fēng)險(xiǎn)。
2、在主服務(wù)器上創(chuàng)建于一個(gè)新的表，表結(jié)構(gòu)就是將要修改大表后表結(jié)構(gòu)，再把老表的數(shù)據(jù)重新導(dǎo)入到新表中，并在老表中建立一系列的觸發(fā)器，把老表的數(shù)據(jù)同步更新到新表中，當(dāng)老表中的數(shù)據(jù)全部同步到新表以后，再對(duì)老表加排它鎖，把新表改成老表的名稱，刪除重命名的老表，如下圖所示

使用pt-online-schema-change命令來(lái)修改大表，具體操作如下圖所示

上圖的參數(shù)解釋

--alter 所使用的sql語(yǔ)句
--user 數(shù)據(jù)庫(kù)的登錄用戶
--password 登錄用戶的密碼
D 指定所有修改表的數(shù)據(jù)庫(kù)名稱
t 表的名稱
--charset 指定數(shù)據(jù)庫(kù)的字符串
--excute 執(zhí)行

原創(chuàng)作品，轉(zhuǎn)載請(qǐng)注明出處