通過(guò)MySQL慢查詢優(yōu)化MySQL性能的方法講解
隨著訪問(wèn)量的上升，MySQL數(shù)據(jù)庫(kù)的壓力就越大，幾乎大部分使用MySQL架構(gòu)的web應(yīng)用在數(shù)據(jù)庫(kù)上都會(huì)出現(xiàn)性能問(wèn)題，通過(guò)mysql慢查詢?nèi)罩靖櫽袉?wèn)題的查詢非常有用,可以分析出當(dāng)前程序里有很耗費(fèi)資源的sql語(yǔ)句。
慢查詢?nèi)罩疚覀兛梢酝ㄟ^(guò)my.cnf文件設(shè)置開(kāi)啟，下面先來(lái)看一下相關(guān)參數(shù)的意義
log-slow-queries <slow_query_log_file>
存放slow query日志的文件。你必須保證mysql server進(jìn)程mysqld_safe進(jìn)程用戶對(duì)該文件有w權(quán)限。
long_query_time
如果query time超過(guò)了該值，則認(rèn)為是較慢查詢，并被記錄下來(lái)。單位是秒，最小值是1,默認(rèn)值是10秒。10秒對(duì)于大多數(shù)應(yīng)用來(lái)講，太長(zhǎng)了。我們推薦從3秒開(kāi)始， 依次減少，每次都找出最”昂貴”的10條SQL語(yǔ)句并且優(yōu)化他們。日復(fù)一日，一步一步優(yōu)化。一次性找出很多條SQL語(yǔ)句，對(duì)于優(yōu)化來(lái)講，意義并不大。
log-queries-not-using-indexes
MySQL會(huì)將沒(méi)有使用索引的查詢記錄到slow query日志中。無(wú)論它執(zhí)行有多快，查詢語(yǔ)句沒(méi)有使用索引，都會(huì)被記錄。有的時(shí)候，有些沒(méi)有使用引索的查詢非?？?例如掃描很小的表)，但也有可能導(dǎo)致服務(wù)器變慢，甚至還會(huì)使用大量的磁盤(pán)空間。
log-slow-admin-statements
一些管理指令，也會(huì)被記錄。比如OPTIMEZE TABLE, ALTER TABLE等等。
開(kāi)啟慢查詢
方法一：在服務(wù)器上找到mysql的配置文件my.cnf , 然后再mysqld模塊里追加一下內(nèi)容
log_slow_queries = NO log-slow-queries = /var/run/mysqld/slow_querys.log long_query_time = 3 log-queries-not-using-indexes log-slow-admin-statements
然后重啟mysql服務(wù)器即可，這是通過(guò)一下命令看一下慢查詢?nèi)罩镜那闆r：
tail -f /var/run/mysqld/slow_querys.log
方法二：通過(guò)修改myssql的全局變量來(lái)處理，這樣做的好處是，不用重啟mysql服務(wù)器，登陸到mysql上執(zhí)行一下sql腳本即可
set global slow_query_log=ON; set global long_query_time=3;
然后通過(guò)一下命令查看是否成功
mysql> show variables like 'long%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec) mysql> show variables like 'slow%'; +---------------------+---------------+ | Variable_name | Value | +---------------------+---------------+ | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /tmp/slow.log | +---------------------+---------------+ 3 rows in set (0.00 sec)
分析慢查詢?nèi)罩?/strong>
方法一：通過(guò)查看mysql的慢查詢?nèi)罩痉治觯热缥覀兛梢詔ail -f slow_query.log查看里面的內(nèi)容，字段意義
# Time: 110107 16:22:11 # User@Host: root[root] @ localhost [] # Query_time: 9.869362 Lock_time: 0.000035 Rows_sent: 1 Rows_examined: 6261774 SET timestamp=1294388531; select count(*) from ep_friends;
- 第一行,SQL查詢執(zhí)行的時(shí)間
- 第二行,執(zhí)行SQL查詢的連接信息
- 第三行記錄了一些我們比較有用的信息
- Query_time SQL執(zhí)行的時(shí)間,越長(zhǎng)則越慢
- Lock_time 在MySQL服務(wù)器階段(不是在存儲(chǔ)引擎階段)等待表鎖時(shí)間
- Rows_sent 查詢返回的行數(shù)
- Rows_examined 查詢檢查的行數(shù)
方法二：使用mysqldumpslow命令分析，例如
mysqldumpslow -s c -t 10 /tmp/slow-log
這會(huì)輸出記錄次數(shù)最多的10條SQL語(yǔ)句，其中：
-s, 是表示按照何種方式排序，c、t、l、r分別是按照記錄次數(shù)、時(shí)間、查詢時(shí)間、返回的記錄數(shù)來(lái)排序，ac、at、al、ar，表示相應(yīng)的倒敘； -t, 是top n的意思，即為返回前面多少條的數(shù)據(jù)； -g, 后邊可以寫(xiě)一個(gè)正則匹配模式，大小寫(xiě)不敏感的；
比如
/path/mysqldumpslow -s r -t 10 /tmp/slow-log
得到返回記錄集最多的10個(gè)查詢。
/path/mysqldumpslow -s t -t 10 -g “l(fā)eft join” /tmp/slow-log
得到按照時(shí)間排序的前10條里面含有左連接的查詢語(yǔ)句。
慢查詢?nèi)罩镜牟蛔?/strong>
雖然記錄了slow query能夠幫助你優(yōu)化產(chǎn)品。但是MySQL目前版本，還有幾大蹩足的地方。
1.MySQL5.0版本, long_query_time時(shí)間粒度不夠細(xì),最小值為1秒。對(duì)于高并發(fā)性能的網(wǎng)頁(yè)腳本而言，1秒出現(xiàn)的意義不大。即出現(xiàn)1秒的查詢比較少。直到mysql5.1.21才提供更細(xì)粒度的long_query_time設(shè)定.
2.不能將服務(wù)器執(zhí)行的所有查詢記錄到慢速日志中。雖然MySQL普通日志記錄了所有查詢，但是它們是解析查詢之前就記錄下來(lái)了。這意味著普通日志沒(méi)辦法包含諸如執(zhí)行時(shí)間，鎖表時(shí)間，檢查行數(shù)等信息。
3.如果開(kāi)啟了log_queries_not_using_indexes選項(xiàng)，slow query日志會(huì)充滿過(guò)多的垃圾日志記錄，這些快且高效的全表掃描查詢(表小)會(huì)沖掉真正有用的slow queries記錄。比如select * from category這樣的查詢也會(huì)被記錄下來(lái)。開(kāi)啟了log_queries_not_using_indexes選項(xiàng)，slow query日志會(huì)充滿過(guò)多的垃圾日志記錄，這些快且高效的全表掃描查詢(表小)會(huì)沖掉真正有用的slow queries記錄。比如select * from category這樣的查詢也會(huì)被記錄下來(lái)。
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。如果你想了解更多相關(guān)內(nèi)容請(qǐng)查看下面相關(guān)鏈接
相關(guān)文章
這篇文章主要介紹了MySQL5.7完全卸載的詳細(xì)步驟以及把中間遇到的問(wèn)題做了分析，需要的朋友跟著操作下吧。2018-02-02
解決MySQL數(shù)據(jù)庫(kù)中文模糊檢索問(wèn)題的方法
解決MySQL數(shù)據(jù)庫(kù)中文模糊檢索問(wèn)題的方法...2007-11-11
mysql 主從復(fù)制如何跳過(guò)報(bào)錯(cuò)
這篇文章主要介紹了mysql 主從復(fù)制如何跳過(guò)報(bào)錯(cuò)，幫助大家更好的理解和使用MySQL 數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下2020-10-10
JMeter對(duì)MySQL數(shù)據(jù)庫(kù)進(jìn)行壓力測(cè)試的實(shí)現(xiàn)步驟
本文主要介紹了JMeter對(duì)MySQL數(shù)據(jù)庫(kù)進(jìn)行壓力測(cè)試的實(shí)現(xiàn)步驟，文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-01-01
MySQL中另外一類很重要的函數(shù)就是數(shù)值函數(shù),這些函數(shù)能處理很多數(shù)值方面的運(yùn)算,下面這篇文章主要給大家介紹了關(guān)于MySQL常見(jiàn)數(shù)值函數(shù)整理的相關(guān)資料,需要的朋友可以參考下2023-02-02
深度解析MySQL啟動(dòng)時(shí)報(bào)“The server quit without up
這篇文章主要介紹了MySQL啟動(dòng)時(shí)報(bào)“The server quit without updating PID file”錯(cuò)誤的原因,需要的朋友可以參考下2017-05-05
這篇文章主要介紹了簡(jiǎn)述Redis和MySQL的區(qū)別，小編覺(jué)得挺不錯(cuò)的，這里給大家分享下，需要的朋友可以了解。2017-10-10
Introduction 簡(jiǎn)介 MySQL 5.0 新特性教程是為需要了解5.0版本新特性的MySQL老用戶而寫(xiě)的。簡(jiǎn)單的來(lái)說(shuō)是介紹了“存儲(chǔ)過(guò)程、觸發(fā)器、視圖、信息架構(gòu)視圖”,在此感謝譯者陳朋奕的努力.2008-04-04
MySQL5.7限制general_log日志大小的實(shí)現(xiàn)
MySQL5.7.41中為避免通用查詢?nèi)罩緂eneral_log快速增長(zhǎng)占用硬盤(pán)空間,可以通過(guò)定時(shí)任務(wù)執(zhí)行腳本進(jìn)行每日備份或清理,從而限制其大小,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2024-10-10
這篇文章主要介紹了MySQL字段類型,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-10-10