如何獲取有性能問(wèn)題的sql
- 通過(guò)用戶(hù)反饋存在性能問(wèn)題的sql //用戶(hù)反饋蔬蕊，測(cè)試人員測(cè)試
- 通過(guò)慢查詢(xún)?nèi)罩精@取存在性能問(wèn)題的sql //主要手段
- 實(shí)時(shí)捕獲存在性能問(wèn)題的sql
慢查詢(xún)?nèi)罩?/h2>
# 查看my.cnf參數(shù)配置
mysqld --help --verbose | more
# 查看my.cnf參數(shù)配置
mysqld --help --verbose | more
主要的開(kāi)銷(xiāo)是磁盤(pán)io和磁盤(pán)空間
啟動(dòng)慢查詢(xún)?nèi)罩?/h3>
slow_query_log 啟動(dòng)停止慢查詢(xún)?nèi)罩?br>
slow_query_log_file 指定慢查詢(xún)?nèi)罩敬鎯?chǔ)日志及文件
long_query_time 指定慢查詢(xún)?nèi)罩緎ql執(zhí)行時(shí)間的閥值
log_queries_not_using_indexes 是否記錄未使用索引的sql
long_query_time=1
slow_query_log=1
slow_query_log_file=/var/lib/mysql/slow-query.log
log_queries_not_using_indexes=1
慢查詢(xún)分析工具
- mysqldumpslow mysql安裝之后就自帶的
查看詳細(xì)命令 mysqldumpslow --help
實(shí)時(shí)發(fā)現(xiàn)有性能問(wèn)題的sql
利用information_schema
//可以通過(guò)腳本去執(zhí)行，實(shí)時(shí)去查找執(zhí)行時(shí)間超過(guò)30秒的
select id,`user`,DB,`host`,command,`time`,state,info from information_schema.processlist where time >30
查詢(xún)?yōu)槭裁磿?huì)慢
mysql查詢(xún)執(zhí)行的過(guò)程
- 客戶(hù)端發(fā)送sql請(qǐng)求給服務(wù)器
- 服務(wù)器檢查是否可以在查詢(xún)緩存中命中該sql
- 服務(wù)器進(jìn)行sql解析穆端，預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃
- 根據(jù)執(zhí)行計(jì)劃传惠，調(diào)用引擎API來(lái)查詢(xún)數(shù)據(jù)
- 將結(jié)果返回給客戶(hù)端
影響的因素
查詢(xún)緩存
如果查詢(xún)緩存是打開(kāi)的迄沫，優(yōu)先檢查查詢(xún)緩存是否命中，使用hash查找來(lái)匹配緩存結(jié)果卦方。如果命中查詢(xún)緩存羊瘩，在返回之前，會(huì)檢查用戶(hù)權(quán)限盼砍，如果權(quán)限符合尘吗，則返回結(jié)果。
檢查緩存是否命中時(shí)浇坐，需要對(duì)緩存進(jìn)行加鎖睬捶，并且在數(shù)據(jù)被更新之后，緩存也就失效了近刘。如果系統(tǒng)比較繁忙擒贸，則不建議開(kāi)啟緩存。
查詢(xún)緩存的影響參數(shù)
query_cache_type 設(shè)置查詢(xún)緩存是否可用 off/on
query_cache_size 設(shè)置查詢(xún)緩存的內(nèi)存大小 0
query_cache_limit 設(shè)置查詢(xún)緩存可用存儲(chǔ)的最大值
query_cache_wlock_invalidate 設(shè)置數(shù)據(jù)表被鎖后是否返回緩存中的數(shù)據(jù)
query_cache_min_res_unit 設(shè)置查詢(xún)緩存分配的內(nèi)存塊最小單位
依照?qǐng)?zhí)行計(jì)劃對(duì)存儲(chǔ)引擎進(jìn)行交互
在過(guò)程中出錯(cuò)則返回
解析sql觉渴，預(yù)處理介劫，優(yōu)化sql執(zhí)行計(jì)劃
確定查詢(xún)處理各個(gè)階段所消耗的時(shí)間
使用profile
開(kāi)啟 set profiling = 1 這是一個(gè)session級(jí)的配置
執(zhí)行查詢(xún)
show profiles 查看每一個(gè)查詢(xún)所消耗的總時(shí)間信息
查看 show profile for query n;
+----------+------------+-------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------+
| 1 | 0.01540625 | show databases |
| 2 | 0.00012825 | SELECT DATABASE() |
| 3 | 0.00027775 | show databases |
| 4 | 0.00042550 | show tables |
| 5 | 0.00030150 | show tables |
+----------+------------+-------------------+
//查看
show profile for query 1;
//結(jié)果
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.001794 |
| checking permissions | 0.000015 |
| Opening tables | 0.000693 |
| init | 0.000022 |
| System lock | 0.000011 |
| optimizing | 0.000005 |
| statistics | 0.000027 |
| preparing | 0.000017 |
| executing | 0.012708 |
| Sending data | 0.000046 |
| end | 0.000007 |
| query end | 0.000005 |
| closing tables | 0.000004 |
| removing tmp table | 0.000010 |
| closing tables | 0.000007 |
| freeing items | 0.000019 |
| cleaning up | 0.000018 |
+----------------------+----------+
Performance Schema
從mysql5.5 引入的
傳送門(mén)
慢查詢(xún)基礎(chǔ)
簡(jiǎn)單的查詢(xún)衡量指標(biāo)：1響應(yīng)時(shí)間，2掃描行數(shù)案淋，3返回的行數(shù)
- 響應(yīng)時(shí)間主要是等待時(shí)間和服務(wù)時(shí)間座韵，服務(wù)時(shí)間是服務(wù)器處理這個(gè)查詢(xún)所消耗的時(shí)間。等待時(shí)間是等待資源的時(shí)間踢京，如io誉碴、鎖等待
- 掃描行數(shù)在一定程度上能說(shuō)明該查詢(xún)的效率， 較短的行訪(fǎng)問(wèn)較快瓣距，內(nèi)存中的行比磁盤(pán)上快黔帕。
優(yōu)化數(shù)據(jù)訪(fǎng)問(wèn)
- 確認(rèn)是否檢索了大量超過(guò)需要的數(shù)據(jù)。通常是訪(fǎng)問(wèn)了太多行旨涝，有時(shí)候也可能是太多列
- 確認(rèn)服務(wù)層是否分析了大量超過(guò)需要的數(shù)據(jù)行
- 是否向數(shù)據(jù)庫(kù)請(qǐng)求了不需要的數(shù)據(jù)
- 是否掃描了額外的記錄
掃描的行數(shù)和返回的行數(shù)
掃描的行數(shù)和訪(fǎng)問(wèn)類(lèi)型
重構(gòu)查詢(xún)方式
- 將復(fù)雜查詢(xún)分解成多個(gè)簡(jiǎn)單查詢(xún) //少連表 或單表查詢(xún)
- 切分查詢(xún) //如刪除多行數(shù)據(jù)蹬屹，該為多次刪除
- 分解關(guān)聯(lián)查詢(xún)
子查詢(xún)優(yōu)化為join查詢(xún)侣背，需注意一對(duì)多情況時(shí)白华，是否有數(shù)據(jù)重復(fù)
mysql 查詢(xún)執(zhí)行路徑
- 客戶(hù)端發(fā)送一條查詢(xún)給服務(wù)器
- 服務(wù)器先檢查查詢(xún)緩存，如果命中了緩存贩耐，則立刻返回存儲(chǔ)在緩存中的結(jié)果弧腥。否則進(jìn)入下一階段
- 服務(wù)端進(jìn)行SQL解析，預(yù)處理潮太，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃管搪。
- MySQL根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃虾攻，調(diào)用存儲(chǔ)引擎的API來(lái)執(zhí)行查詢(xún)。
- 將結(jié)果返回給客戶(hù)端更鲁。
查詢(xún)優(yōu)化
count
count是一種特殊函數(shù)霎箍，可以統(tǒng)計(jì)某個(gè)列值的數(shù)量，也可以統(tǒng)計(jì)行數(shù)澡为。在統(tǒng)計(jì)列值時(shí)漂坏，要求列值是非空的。
count可以通過(guò)索引覆蓋來(lái)實(shí)現(xiàn)優(yōu)化媒至，或者使用匯總表
select count(id2),count(id) from t
//結(jié)果 id2:2 id:3 count的列為null時(shí)顶别，count并不會(huì)將其統(tǒng)計(jì)
利用null 優(yōu)化count
select count(release_year='2008' or null) from film
優(yōu)化關(guān)聯(lián)查詢(xún)
- 確保on或者using子句中的列上有索引。一般來(lái)說(shuō)只需要在關(guān)聯(lián)順序中的第二個(gè)表的相應(yīng)列上建立索引拒啰。 如：表A驯绎、B用c列進(jìn)行關(guān)聯(lián)，關(guān)聯(lián)順序?yàn)锽谋旦、A剩失，則只需要在A(yíng)表上建立c列的索引。
- 確保任何的group by 和order by 中的表達(dá)式只涉及表中的一個(gè)列蛤织，這樣mysql才有可能使用索引來(lái)優(yōu)化這個(gè)過(guò)程
優(yōu)化子查詢(xún)
子查詢(xún)盡量換成關(guān)聯(lián)查詢(xún)赴叹。若使用5.6以上版本，則不需要進(jìn)行替換指蚜。
優(yōu)化group by 和distinct
mysql優(yōu)化器會(huì)在內(nèi)部處理時(shí)的時(shí)候相互轉(zhuǎn)化這兩類(lèi)查詢(xún)乞巧，都可以使用索引來(lái)優(yōu)化，也是最有效的方法摊鸡。
當(dāng)無(wú)法使用索引優(yōu)化時(shí)绽媒，group by 由臨時(shí)表或文件排序來(lái)做分組
limit優(yōu)化
可以參考mysql翻頁(yè)優(yōu)化
通常在where條件上加索引會(huì)由不錯(cuò)的性能，但是當(dāng)數(shù)據(jù)量大免猾，且翻頁(yè)多時(shí)是辕，如，limit 10010猎提，10获三。此時(shí)mysql需要查詢(xún)10010條數(shù)據(jù)，并且10000都被拋棄锨苏，只取最后10條疙教。要對(duì)此類(lèi)語(yǔ)句優(yōu)化，要么限制頁(yè)面中分頁(yè)的數(shù)量伞租，或者優(yōu)化最大偏移量的性能贞谓。
優(yōu)化最大偏移量，利用索引覆蓋來(lái)加快查詢(xún)葵诈。利用的是主鍵
limit和offset的問(wèn)題裸弦，都是offset的問(wèn)題祟同。它會(huì)導(dǎo)致mysql掃描大量不需要的行然后在拋棄。
//第一種寫(xiě)法
select * from actor where actor_id >=(select actor_id from actor order by actor_id limit 100,1) limit 10;
//第二種寫(xiě)法
select * from actor a join (select actor_id from actor order by actor_id limit 100,10) b on a.actor_id=b.actor_id
