MySQL SQL語(yǔ)句分析與查詢優(yōu)化詳解
如何獲取有性能問(wèn)題的SQL
1、通過(guò)用戶反饋獲取存在性能問(wèn)題的SQL
2、通過(guò)慢查詢?nèi)罩精@取性能問(wèn)題的SQL
3、實(shí)時(shí)獲取存在性能問(wèn)題的SQL
使用慢查詢?nèi)罩精@取有性能問(wèn)題的SQL
首先介紹下慢查詢相關(guān)的參數(shù)
1、slow_query_log 啟動(dòng)定制記錄慢查詢?nèi)罩?br />
設(shè)置的方法，可以通過(guò)MySQL命令行設(shè)置set global slow_query_log=on
或者修改/etc/my.cnf文件，添加slow_query_log=on
2、slow_query_log_file 指定慢查詢?nèi)罩镜拇鎯?chǔ)路徑及文件
建議日志存儲(chǔ)和數(shù)據(jù)存儲(chǔ)分開(kāi)存儲(chǔ)
3、long_query_time 指定記錄慢查詢?nèi)罩維QL執(zhí)行時(shí)間的閾值
① 記錄所有符合條件的SQL
② 數(shù)據(jù)修改語(yǔ)句
③ 包括查詢語(yǔ)句
④ 已經(jīng)回滾的SQL
注意：
時(shí)間可以精確到微秒，存儲(chǔ)的單位是秒，默認(rèn)值為10秒，例如我們想查詢1微秒的值，這里就要設(shè)置成0.001秒
4、log_queries_not_using_indexes 是否記錄未使用索引的SQL
5、log_output 設(shè)置慢日志查詢的保存格式(如果需要保存為文件請(qǐng)修改成FILE)
慢查詢使用日志中記錄的信息
1、第一行記錄的信息為使用sbtest做的測(cè)試
2、第二行記錄的信息為慢查詢?nèi)罩镜臅r(shí)間
3、第三行記錄的信息為所使用鎖的時(shí)間
4、第四行記錄的信息為返回的數(shù)據(jù)行數(shù)
5、第五行記錄的信息為掃描數(shù)據(jù)的行數(shù)
6、第六行記錄的信息為時(shí)間戳
7、第七行記錄的信息為查詢的SQL語(yǔ)句
使用慢查詢獲取有性能問(wèn)題的SQL
常使用的慢查詢?nèi)罩痉治龉ぞ?mysqldumpslow)
介紹：匯總除查詢條件外其他完全相同的SQL，并將分析結(jié)果按照參數(shù)中所指定的順序輸出
慢查詢?nèi)罩緦?shí)例
慢查詢的相關(guān)配置設(shè)置
命令行執(zhí)行參數(shù)查看分析的結(jié)果
]# cd /var/lib/mysql/log
]# mysqldumpslow -s r -t 10 slow-mysql
常使用的慢查詢?nèi)罩痉治龉ぞ?pt-query-digest)
使用工具前，需要先安裝該工具，如果已有，可略過(guò)下面的安裝步驟
1、perl模塊
]# yum install -y perl-CPAN perl-Time-HiRes perl-IO-Socket-SSL perl-DBD-mysql perl-Digest-MD5
2、切換至src目錄下載rpm包
]# cd /usr/local/src
]# wget https://www.percona.com/downloads/percona-toolkit/3.0.7/binary/redhat/7/x86_64/percona-toolkit-3.0.7-1.el7.x86_64.rpm
3、安裝工具包
]# rpm -ivh percona-toolkit-3.0.7-1.el7.x86_64.rpm
執(zhí)行命令分析慢查詢?nèi)罩?/strong>
]# 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ì)大小寫(xiě)敏感的哈希查找實(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類(lèi)型
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ì)被改寫(xiě)成 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)注明出處
相關(guān)文章
詳解如何利用Xtrabackup進(jìn)行mysql增量備份
這篇文章主要為大家介紹了如何利用Xtrabackup進(jìn)行mysql增量備份詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2022-10-10
今天小編就為大家分享一篇關(guān)于MySQL執(zhí)行狀態(tài)的查看與分析，小編覺(jué)得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來(lái)看看吧2019-03-03
MySQL主從數(shù)據(jù)一致性怎么恢復(fù)，MySQL主從數(shù)據(jù)庫(kù)的數(shù)據(jù)不一致，猜測(cè)備庫(kù)在同步過(guò)程中出現(xiàn)了問(wèn)題，現(xiàn)在的問(wèn)題很明確，就是如何恢復(fù)主從庫(kù)數(shù)據(jù)的一致性，下文為大家分享解決方法，感興趣的小伙伴們可以參考一下2016-04-04
MySQL 創(chuàng)建索引(Create Index)的方法和語(yǔ)法結(jié)構(gòu)及例子
MySQL 創(chuàng)建索引(Create Index)的方法和語(yǔ)法結(jié)構(gòu)及例子2009-07-07
這篇文章主要為大家介紹了MySQL監(jiān)控Innodb信息工作流程,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2024-02-02
mysql中用的最多存儲(chǔ)引擎就是innodb和myisam。做為Mysql的默認(rèn)存儲(chǔ)引擎,myisam值得我們學(xué)習(xí)一下，以下是我對(duì)《高性能MYSQL》書(shū)中提到的myisam的理解,請(qǐng)大家多多指教2012-04-04
CentOS7.4手動(dòng)安裝MySQL5.7的方法
這篇文章主要介紹了CentOS7.4手動(dòng)安裝MySQL5.7的方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-09-09
這篇文章主要介紹了MySql數(shù)據(jù)庫(kù)備份的幾種方式，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-12-12
這篇文章主要介紹了MySQL基本增刪改查語(yǔ)句練習(xí),本文給大家分享sql增刪改查語(yǔ)句是平時(shí)開(kāi)發(fā)最常用的，需要的朋友可以參考下2023-01-01
教會(huì)你完全搞定MySQL數(shù)據(jù)庫(kù) 輕松八句話
只要掌握下面的方法，就基本上能搞定mysql數(shù)據(jù)庫(kù)。2010-09-09