慢查詢(xún)?nèi)罩?開(kāi)啟撒網(wǎng)模式

    開(kāi)啟了MySQL慢查詢(xún)?nèi)罩局�后，MySQL會(huì)自動(dòng)將執(zhí)行時(shí)間超過(guò)指定秒數(shù)的SQL統(tǒng)統(tǒng)記錄下來(lái)，這對(duì)于搜羅線(xiàn)上慢SQL有很大的幫助。

SHOW VARIABLES LIKE 'slow%'

以我剛安裝的mysql5.7為例 查詢(xún)結(jié)果是這樣子的：

　　slow_launch_time：表示如果建立線(xiàn)程花費(fèi)了比這個(gè)值更長(zhǎng)的時(shí)間,slow_launch_threads 計(jì)數(shù)器將增加
　　slow_query_log：是否開(kāi)啟慢查詢(xún)?nèi)罩?ON開(kāi)啟，OFF關(guān)閉 默認(rèn)沒(méi)有開(kāi)啟
　　slow_query_log_file：日志保存路徑

SHOW VARIABLES LIKE 'long%'

　　long_query_time：達(dá)到多少秒的sql就記錄日志

客戶(hù)端可以用set設(shè)置變量的方式讓慢查詢(xún)開(kāi)啟，但是個(gè)人不推薦，因?yàn)檎鎸?shí)操作起來(lái)會(huì)有一些問(wèn)題，比如說(shuō)，重啟MySQL后就失效了，或者是開(kāi)啟了慢查詢(xún)，我又去改變量值，它就不生效了。

編輯MySQL的配置文件：

vim /etc/my.cnf

加入如下三行：

　　slow_query_log=ON
　　slow_query_log_file=/var/lib/mysql/localhost-centos-slow.log
　　long_query_time=3

我這里設(shè)置的是3秒

 重啟MySQL

systemctl restart mysqld;

服務(wù)器開(kāi)一個(gè)監(jiān)控：

tail -f /var/lib/mysql/localhost-centos-slow.log

客戶(hù)端走一條SQL：

SELECT SLEEP(3)

此時(shí)發(fā)現(xiàn)sql已經(jīng)被記錄到日志里了。（有時(shí)候不一定，我看到很多博客講的是超過(guò)指定秒數(shù)，但我實(shí)驗(yàn)得出的結(jié)果是達(dá)到指定秒數(shù)）

EXPLAIN 點(diǎn)對(duì)點(diǎn)分析你

　　explain是一個(gè)神奇的命令，可以查看sql的具體的執(zhí)行計(jì)劃。

以一條聯(lián)查sql為例：

SELECT a.id,a.cn_name,a.role_id,r.name
FROM tb_usr_admins a
INNER JOIN tb_base_roles r ON r.id=a.role_id
WHERE a.cn_name="接單人員"

查詢(xún)結(jié)果是：

加上explain命令來(lái)執(zhí)行：

EXPLAIN
SELECT a.id,a.cn_name,a.role_id,r.name
FROM tb_usr_admins a
INNER JOIN tb_base_roles r ON r.id=a.role_id
WHERE a.cn_name="接單人員"

查詢(xún)結(jié)果是：

這就是這條SQL的執(zhí)行計(jì)劃，下面來(lái)說(shuō)明一下這個(gè)執(zhí)行計(jì)劃怎么看

　　id：代表優(yōu)先級(jí)  id值越大，越先執(zhí)行，id值相同，從上往下執(zhí)行。（比如示例的這條sql的執(zhí)行計(jì)劃，就是先執(zhí)行第一行，再執(zhí)行第二行）

　　select_type：表示select類(lèi)型 取值如下

　　　　simple 簡(jiǎn)單表 即不使用表連接或者子查詢(xún)
　　　　primary 包含union或者子查詢(xún)的主查詢(xún) 即外層的查詢(xún)
　　　　union UNION中的第二個(gè)或者后面的查詢(xún)語(yǔ)句
　　　　subquery 一般子查詢(xún)中的子查詢(xún)被標(biāo)記為subquery，也就是位于select列表中的查詢(xún)
　　　　derived 派生表 該臨時(shí)表是從子查詢(xún)派生出來(lái)的
　　　　等等

　　type：表示MySQL在表中查找數(shù)據(jù)的方式，或者叫訪(fǎng)問(wèn)類(lèi)型，以下對(duì)于type取值的說(shuō)明 從上往下性能由最差到最好

　　　　all:全表掃描，MySQL遍歷全表來(lái)找到匹配的行
　　　　index：索引全掃描，MySQL遍歷掙個(gè)索引來(lái)查詢(xún)匹配的行
　　　　range：索引范圍掃描，常見(jiàn)于<、<=、>、>=、between等操作符
　　　　ref：使用非唯一索引或唯一索引的前綴掃描，返回匹配的單行數(shù)據(jù)
　　　　eq_ref：類(lèi)似ref，區(qū)別就在于使用的索引是唯一索引，簡(jiǎn)單來(lái)說(shuō)，就是多表連接中使用primary key或者unique index作為關(guān)聯(lián)條件。
　　　　const/system：?jiǎn)伪碇凶疃嘤幸粋�(gè)匹配行，查詢(xún)起來(lái)非常迅速，常見(jiàn)于根據(jù)primary key或者唯一索引unique index進(jìn)行的單表查詢(xún)
　　　　null：mysql不用訪(fǎng)問(wèn)表或者索引，直接就能夠得到查詢(xún)的結(jié)果，例如select 1+2 as result。

　　possible_keys：表示查詢(xún)時(shí)可能使用的索引

　　key：表示實(shí)際使用的索引

　　key_len：使用到索引字段的長(zhǎng)度

　　rows：掃描數(shù)量

　　Extra：執(zhí)行情況的說(shuō)明和描述，包含不適合在其他列中顯示但是對(duì)執(zhí)行計(jì)劃非常重要的額外信息，常用取值如下：

　　　　Using index：直接訪(fǎng)問(wèn)索引就取到了數(shù)據(jù)，高性能的表現(xiàn)。
　　　　Using where：直接在主鍵索引上過(guò)濾數(shù)據(jù)，必帶where子句，而且用不上索引
　　　　Using index condition：先條件過(guò)濾索引，再查數(shù)據(jù)，
　　　　Using filesort：使用了外部文件排序 只要見(jiàn)到這個(gè) 就要優(yōu)化掉
　　　　Using temporary：創(chuàng)建了臨時(shí)表來(lái)處理查詢(xún) 只要見(jiàn)到這個(gè) 也要盡量?jī)?yōu)化掉

優(yōu)化爭(zhēng)議無(wú)數(shù)的count()

統(tǒng)計(jì)列與統(tǒng)計(jì)行？

　　COUNT()是一個(gè)特殊的函數(shù)，有兩種不同的作用，它可以統(tǒng)計(jì)某個(gè)列值的數(shù)量，也可以統(tǒng)計(jì)行數(shù)。

　　在統(tǒng)計(jì)列值的時(shí)候要求列值是非空的，也就是不統(tǒng)計(jì)null。

　　當(dāng)我們統(tǒng)計(jì)行的時(shí)候，常見(jiàn)的是COUNT(*)，這種情況下，通配符*并不會(huì)像我們猜想的那樣擴(kuò)展成所有的列，實(shí)際上，它會(huì)忽略所有的列而直接統(tǒng)計(jì)所有的行數(shù)

解密MyiSAM的‘快’

　　這是一個(gè)容易產(chǎn)生誤解的事情：MyiSAM的count()函數(shù)總是非�？臁�

　　不過(guò)它是有前提條件的，條件是沒(méi)有任何where條件的count(*)才非常快，因?yàn)榇藭r(shí)無(wú)須實(shí)際的去計(jì)算表的行數(shù)，mysql可以利用存儲(chǔ)引擎的特性直接獲得這個(gè)值，如果mysql知道某列不可能有null值，那么mysql內(nèi)部會(huì)將count(列)表達(dá)式優(yōu)化為count(*)。

　　當(dāng)統(tǒng)計(jì)帶有where條件的查詢(xún)，那么mysql的count()和其他存儲(chǔ)引擎就沒(méi)有什么不同了。

COUNT(1)、COUNT(*)、COUNT(列)

　�。ㄏ忍崆吧昝�，本人是在innodb庫(kù)里做的實(shí)驗(yàn)。）

　　1.count(1)和count(*)直接就是統(tǒng)計(jì)主鍵，他們兩個(gè)的效率是一樣的。如果刪除主鍵，他們都走全表掃描。

　　2.如果count(列)中的字段是索引的話(huà)，count(列)和count(*)一樣快，否則count(列)走全表掃描。

優(yōu)化order by 語(yǔ)句

MySQL的排序方式

　　優(yōu)化order by語(yǔ)句就不得不了解mysql的排序方式。

　　1.第一種通過(guò)有序索引返回?cái)?shù)據(jù)，這種方式的extra顯示為Using Index,不需要額外的排序，操作效率較高。

　　2.第二種是對(duì)返回的數(shù)據(jù)進(jìn)行排序，也就是通�？吹降腢sing filesort，filesort是通過(guò)相應(yīng)的排序算法，將數(shù)據(jù)放在sort_buffer_size系統(tǒng)變量設(shè)置的內(nèi)存排序區(qū)中進(jìn)行排序，如果內(nèi)存裝載不下，它就會(huì)將磁盤(pán)上的數(shù)據(jù)進(jìn)行分塊，再對(duì)各個(gè)數(shù)據(jù)塊進(jìn)行排序，然后將各個(gè)塊合并成有序的結(jié)果集。

filesort的優(yōu)化

　　了解了MySQL排序的方式，優(yōu)化目標(biāo)就清晰了：盡量減少額外的排序，通過(guò)索引直接返回有序數(shù)據(jù)。where條件和order by使用相同的索引。

　　1.創(chuàng)建合適的索引減少filesort的出現(xiàn)。

　　2.查詢(xún)時(shí)盡量只使用必要的字段，select 具體字段的名稱(chēng)，而不是select * 選擇所有字段，這樣可以減少排序區(qū)的使用，提高SQL性能。

優(yōu)化group by 語(yǔ)句

為什么order by后面不能跟group by ?

　　事實(shí)上，MySQL在所有的group by 后面隱式的加了order by ，也就是說(shuō)group by語(yǔ)句的結(jié)果會(huì)默認(rèn)進(jìn)行排序。

　　如果你要在order by后面加group by ，那結(jié)果執(zhí)行的SQL是不是這樣：select * from tb order by ... group by ... order by ... ？ 這不是搞笑嗎？

禁止排序

　　既然知道問(wèn)題了，那么就容易優(yōu)化了，如果查詢(xún)包括group by但又不關(guān)心結(jié)果集的順序，而這種默認(rèn)排序又導(dǎo)致了需要文件排序，則可以指定order by null 禁止排序。

例如：

select * from tb group by name order by null;

優(yōu)化limit 分頁(yè)

　　一個(gè)非常常見(jiàn)又非常頭痛的場(chǎng)景：‘limit 1000,20’。

　　這時(shí)MySQL需要查詢(xún)1020條記錄然后只返回最后20條，前面的1000條都將被拋棄，這樣的代價(jià)非常高。如果所有頁(yè)面的訪(fǎng)問(wèn)頻率都相同，那么這樣的查詢(xún)平均需要訪(fǎng)問(wèn)半個(gè)表的數(shù)據(jù)。

第一種思路 在索引上分頁(yè)

　　在索引上完成分頁(yè)操作，最后根據(jù)主鍵關(guān)聯(lián)回原表查詢(xún)所需要的其他列的內(nèi)容。

例如：

SELECT * FROM tb_user LIMIT 1000,10

可以?xún)?yōu)化成這樣：

SELECT * FROM tb_user u 
INNER JOIN (SELECT id FROM tb_user LIMIT 1000,10) AS b ON b.id=u.id

第二種思路 將limit轉(zhuǎn)換成位置查詢(xún)

這種思路需要加一個(gè)參數(shù)來(lái)輔助，標(biāo)記分頁(yè)的開(kāi)始位置：

SELECT * FROM tb_user WHERE id > 1000 LIMIT 10

優(yōu)化子查詢(xún)

　　子查詢(xún)，也就是查詢(xún)中有查詢(xún)，常見(jiàn)的是where后面跟一個(gè)括號(hào)里面又是一條查詢(xún)sql

　　盡可能的使用join關(guān)聯(lián)查詢(xún)來(lái)代替子查詢(xún)。

　　當(dāng)然 這不是絕對(duì)的，比如某些非常簡(jiǎn)單的子查詢(xún)就比關(guān)聯(lián)查詢(xún)效率高，事實(shí)效果如何還要看執(zhí)行計(jì)劃。

　　只能說(shuō)大部分的子查詢(xún)都可以?xún)?yōu)化成Join關(guān)聯(lián)查詢(xún)。

改變執(zhí)行計(jì)劃

提高索引優(yōu)先級(jí)

　　use index 可以讓MySQL去參考指定的索引，但是無(wú)法強(qiáng)制MySQL去使用這個(gè)索引，當(dāng)MySQL覺(jué)得這個(gè)索引效率太差，它寧愿去走全表掃描。。。

SELECT * FROM tb_user USE INDEX (user_name)

　　注意：必須是索引，不能是普通字段，（親測(cè)主鍵也不行）。

忽略索引

　　ignore index 可以讓MySQL忽略一個(gè)索引

SELECT * FROM tb_user IGNORE INDEX (user_name) WHERE user_name="張學(xué)友" 

強(qiáng)制使用索引

　　force index 使用了force index 之后 盡管效率非常低，MySQL也會(huì)照你的話(huà)去執(zhí)行

SELECT * FROM tb_user FORCE INDEX (user_name) WHERE user_name="張學(xué)友" 

個(gè)人分享

查看執(zhí)行計(jì)劃時(shí)建議依次觀察以下幾個(gè)要點(diǎn)：

　　1.SQL內(nèi)部的執(zhí)行順序。
　　2.查看select的查詢(xún)類(lèi)型。
　　3.實(shí)際有沒(méi)有使用索引。
　　4.Extra描述信息

PS:一定要養(yǎng)成查看執(zhí)行計(jì)劃的習(xí)慣，這個(gè)習(xí)慣非常重要。

標(biāo)簽： CentOS Mysql 服務(wù)器

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請(qǐng)聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請(qǐng)與原作者聯(lián)系。