一個(gè)sql執(zhí)行很慢的就叫慢sql，一般來(lái)說(shuō)sql語(yǔ)句執(zhí)行超過(guò)5s就能夠算是慢sql恍飘，需要進(jìn)行優(yōu)化了
為何要對(duì)慢SQL進(jìn)行治理
每一個(gè)SQL都需要消耗一定的I/O資源榨崩，SQL執(zhí)行的快慢直接決定了資源被占用時(shí)間的長(zhǎng)短。假設(shè)業(yè)務(wù)要求每秒需要完成100條SQL的執(zhí)行章母，而其中10條SQL執(zhí)行時(shí)間長(zhǎng)導(dǎo)致每秒只能完成90條SQL母蛛，所有新的SQL將進(jìn)入排隊(duì)等待，直接影響業(yè)務(wù)
治理的優(yōu)先級(jí)
- master數(shù)據(jù)庫(kù)->slave數(shù)據(jù)庫(kù)：采用讀寫分離架構(gòu)乳怎，讀在從庫(kù)slave上執(zhí)行彩郊，寫在主庫(kù)master上執(zhí)行。但由于從庫(kù)的數(shù)據(jù)都是在主庫(kù)復(fù)制過(guò)去的蚪缀，主庫(kù)如果等待較多的情況秫逝，會(huì)加大從庫(kù)的復(fù)制延時(shí)
- 執(zhí)行SQL次數(shù)多的優(yōu)先治理
- 某張表被告并發(fā)集中訪問(wèn)的優(yōu)先治理
MySQL執(zhí)行原理
為了更好的優(yōu)化慢SQL，我們來(lái)簡(jiǎn)單了解下MySQL的執(zhí)行原理
綠色部分為SQL實(shí)際執(zhí)行部分椿胯，主要分為兩步：
- 解析：詞法解析->語(yǔ)法解析->邏輯計(jì)劃->查詢優(yōu)化->物理執(zhí)行計(jì)劃筷登，過(guò)程中會(huì)檢查緩存是否可用剃根，如果沒(méi)有可用緩存則進(jìn)入下一步mysql_execute_command執(zhí)行
- 執(zhí)行：檢查用戶哩盲、表權(quán)限->表加上共享讀鎖->取數(shù)據(jù)到query_cache->取消共享讀鎖
如何發(fā)現(xiàn)慢查詢SQL
-- 修改慢查詢時(shí)間,只能當(dāng)前會(huì)話有效?
set long_query_time=1? -
-- 啟用慢查詢 ,加上global，不然會(huì)報(bào)錯(cuò)的?
set global slow_query_log='ON'?--
-- 是否開啟慢查詢?
show variables like "%slow%"?--
-- 查詢慢查詢SQL狀況?
show status like "%slow%"? --
-- 慢查詢時(shí)間（默認(rèn)情況下MySQL認(rèn)位10秒以上才是慢查詢）
show variables like "long_query_time"? --
除了sql的方式狈醉，我們也可以在配置文件（my.ini）中修改廉油，加入配置時(shí)必須要在[mysqld]后面加入
-- 開啟日志?
slow_query_log = on --
-- 記錄日志的log文件（注意:window上必須寫絕對(duì)路徑）
slow_query_log_file = D:/mysql5.5.16/data/show-slow.log--
-- 最長(zhǎng)查詢的秒數(shù)?
long_query_time = 2 --
-- 表示記錄沒(méi)有使用索引的查詢
log-queries-not-using-indexes --
開啟慢查詢會(huì)帶來(lái)CPU損耗與日志記錄的IO開銷，所以建議間斷性的打開慢查詢?nèi)罩緛?lái)觀察MySQL運(yùn)行狀態(tài)
慢查詢分析示例
假設(shè)我們有一條SQL
SELECT * FROM `emp` where ename like '%mQspyv%'?
執(zhí)行時(shí)間為1.163s苗傅，而我們?cè)O(shè)置的慢查詢時(shí)間為1s抒线，這時(shí)我們可以打開慢查詢?nèi)罩具M(jìn)行日志分析：
# Time: 150530 15:30:58 -- -- 該查詢發(fā)生在2015-5-30 15:30:58
# User@Host: root[root] @ localhost [127.0.0.1] -- --是誰(shuí),在什么主機(jī)上發(fā)生的查詢
# Query_time: 1.134065 Lock_time: 0.000000 Rows_sent: 8 Rows_examined: 4000000 Query_time: --查詢總共用了多少時(shí)間,Lock_time: 在查詢時(shí)鎖定表的時(shí)間,Rows_sent: 返回多少rows數(shù)據(jù),Rows_examined: 表掃描了400W行數(shù)據(jù)才得到的結(jié)果?
如果我們的慢SQL很多，人工分析肯定分析不過(guò)來(lái)渣慕，這時(shí)候我們就需要借助一些分析工具嘶炭，MySQL自帶了一個(gè)慢查詢分析工具mysqldumpslow，以下是常見使用示例
mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log # 取出使用最多的10條慢查詢
mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log # 取出查詢時(shí)間最慢的3條慢查詢
mysqldumpslow -s t -t 10 -g “l(fā)eft join” /database/mysql/slow-log #得到按照時(shí)間排序的前10條里面含有左連接的查詢語(yǔ)句
mysqldumpslow -s r -t 10 -g 'left join' /var/run/mysqld/mysqldslow.log # 按照掃描行數(shù)最多的
SQL語(yǔ)句常見優(yōu)化
只要簡(jiǎn)單了解過(guò)MySQL內(nèi)部?jī)?yōu)化機(jī)制逊桦，就很容易寫出高性能的SQL
1.不使用子查詢：
SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name='hechunyang')?
在MySQL5.5版本中眨猎，內(nèi)部執(zhí)行計(jì)劃器是先查外表再匹配內(nèi)表，如果外表數(shù)據(jù)量很大强经，查詢速度會(huì)非常慢
在MySQL5.6中睡陪，有對(duì)內(nèi)查詢做了優(yōu)化，優(yōu)化后SQL如下
SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;
但也僅針對(duì)select語(yǔ)句有效匿情，update兰迫、delete子查詢無(wú)效，所以生成環(huán)境不建議使用子查詢
2.避免函數(shù)索引
SELECT * FROM t WHERE YEAR(d) >= 2016;
即使d字段有索引炬称，也會(huì)全盤掃描汁果，應(yīng)該優(yōu)化為：
SELECT * FROM t WHERE d >= '2016-01-01';
3.使用IN替換OR
SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
非聚簇索引走了3次，使用IN之后只走一次：
SELECT * FROM t WHERE LOC_IN IN (10,20,30);
4.LIKE雙百分號(hào)無(wú)法使用到索引
SELECT * FROM t WHERE name LIKE '%de%';
應(yīng)優(yōu)化為右模糊
SELECT * FROM t WHERE name LIKE 'de%';
5.增加LIMIT M,N 限制讀取的條數(shù)
6.避免數(shù)據(jù)類型不一致
SELECT * FROM t WHERE id = '19';
應(yīng)優(yōu)化為
SELECT * FROM t WHERE id = 19;
7.分組統(tǒng)計(jì)時(shí)可以禁止排序
SELECT goods_id,count(*) FROM t GROUP BY goods_id;
默認(rèn)情況下MySQL會(huì)對(duì)所有GROUP BY co1玲躯，col2 …的字段進(jìn)行排序据德，我們可以對(duì)其使用ORDER BY NULL禁止排序鲸伴，避免排序消耗資源
SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;
8.去除不必要的ORDER BY語(yǔ)句
總結(jié)
總的來(lái)說(shuō)，我們知道曼查詢的SQL后晋控，優(yōu)化方案可以做如下嘗試：
- SQL語(yǔ)句優(yōu)化汞窗，盡量精簡(jiǎn)，去除非必要語(yǔ)句
- 索引優(yōu)化赡译，讓所有SQL都能夠走索引
- 如果是表的瓶頸問(wèn)題仲吏，則分表，單表數(shù)據(jù)量維持在1000W以內(nèi)
- 如果是單庫(kù)瓶頸問(wèn)題蝌焚，則分庫(kù)裹唆，讀寫分離
- 如果是物理機(jī)器性能問(wèn)題，則分多個(gè)數(shù)據(jù)庫(kù)節(jié)點(diǎn)
