1.查詢優(yōu)化
1.1小表驅(qū)動(dòng)大表
emp有500000條數(shù)據(jù),empnos有7條數(shù)據(jù),emp是大表,empnos是小表
1.2order by關(guān)鍵字優(yōu)化
表字段多的時(shí)候刨晴，避免使用select *
ORDER BY子句，盡量使用Index方式排序，避免使用FileSort方式排序
盡可能在索引列上完成排序操作卵凑，遵照索引建的最佳左前綴
使用表test03,簡(jiǎn)歷聯(lián)合索引idx_test03_c1234(c1,c2,c3,c4)
order by使用索引最左前綴
如果where使用索引的最左前綴定義為常量,則order by能使用索引
![]()
image-20200512182448571
不能使用索引排序
-
排序不一致
image-20200512183207797 -
丟失最左前綴索引
image-20200512183220342 -
丟失中間索引,索引中斷
image-20200512183233368 -
使用了非索引字段進(jìn)行排序
image-20200512183244339 -
使用了in(相當(dāng)于范圍查詢)
image-20200512183256565
order引起索引失效的各種情況:
filesort的兩種算法:單路排序與雙路排序
雙路排序:MySQL 4.1 之前使用的雙路排序娶桦，通過(guò)兩次掃描磁盤(pán)得到數(shù)據(jù)艰匙。讀取行指針和 order by 列并對(duì)其進(jìn)行排序，掃描排序好的列表障本，按照列表中的值重新從列表中讀取對(duì)應(yīng)的數(shù)據(jù)輸出。
但是雙路排序會(huì)掃描兩次磁盤(pán)响鹃，磁盤(pán)IO是非常消耗性能的驾霜，所以后面被單路排序取代。
單路排序:從磁盤(pán)中讀取查詢需要的所有列买置，按照 order by 列在 sort_buffer 緩沖區(qū)對(duì)他們進(jìn)行排序寄悯，然后掃描排序后的列表輸出。因?yàn)閱温放判蛐矢於橐澹苊饬硕巫x取數(shù)據(jù)猜旬，把隨機(jī)IO變成了順序IO脆栋，但是會(huì)使用更多的空間。
但是單路排序算法可能會(huì)導(dǎo)致一個(gè)問(wèn)題：如果數(shù)據(jù)量過(guò)大洒擦，一次讀取不完椿争，就會(huì)導(dǎo)致讀取的次數(shù)比雙路排序多。
因?yàn)樽x取操作是在 sort_buffer 中熟嫩，如果數(shù)據(jù)量過(guò)大秦踪，超出了 sort_buffer 的容量，導(dǎo)致每次只能讀取 sort_buffer 容量大小的數(shù)據(jù)進(jìn)行排序掸茅，排完再取椅邓，導(dǎo)致多次IO。
優(yōu)化策略
- 增大sort_buffer_size參數(shù)的設(shè)置
- 增大max_length_for_sort_data參數(shù)的設(shè)置
1.3group by
同order by
group by 實(shí)質(zhì)是先排序后分組,遵照索引鍵的最佳左前綴
當(dāng)無(wú)法使用索引列昧狮，增大max_length_for_sort_data參數(shù)的設(shè)置+增大sort_buffer_size參數(shù)的設(shè)置
where高于having,能寫(xiě)在where限定的條件就不要去having限定了景馁。
2.慢查詢?nèi)罩?/h3>
查看慢查詢是否開(kāi)啟及日志存放位置: show variables like '%slow_query_log%';
開(kāi)啟慢查詢?nèi)罩? set global slow_query_log=1;
查看慢查詢閾值: show variables like 'long_query_time';
設(shè)置閾值為5s: set global long_query_time=5;
測(cè)試:
查看日志: cat /www/server/data/mysql-slow.log
3.show profiles
mysql提供可以用來(lái)分析當(dāng)前會(huì)話中語(yǔ)句執(zhí)行的資源消耗情況《好可以用于SQL的調(diào)優(yōu)測(cè)量
官網(wǎng)：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
分析步驟：
是否支持合住，看看當(dāng)前的SQL版本是否支持
-
開(kāi)啟功能，默認(rèn)是關(guān)閉撒璧，使用前需要開(kāi)啟:
查看是否開(kāi)啟: show variables like 'profiling';
image-20200512220411099開(kāi)啟: set profiling=on;
image-20200512220515182 -
運(yùn)行SQL
image-20200512220928439 -
查看結(jié)果透葛，show profiles；
image-20200512220946991 -
診斷SQL卿樱，show profile cpu,block io for query 上一步前面的問(wèn)題SQL 數(shù)字號(hào)碼僚害；
對(duì)上面第六句sql進(jìn)行cpu資源占用,磁盤(pán)IO進(jìn)行具體分析
show profile cpu,block io for query 6;
image-20200512221201424 -
日常開(kāi)發(fā)需要注意的結(jié)論
- converting HEAP to MyISAM 查詢結(jié)果太大，內(nèi)存都不夠用了往磁盤(pán)上搬了繁调。
- Creating tmp table 創(chuàng)建臨時(shí)表:拷貝數(shù)據(jù)到臨時(shí)表,用完再刪除
- Copying to tmp table on disk 把內(nèi)存中臨時(shí)表復(fù)制到磁盤(pán)贡珊，危險(xiǎn)！Ｉ婺佟门岔！
- locked
4.全局查詢?nèi)罩?/h3>
只能在測(cè)試環(huán)境使用,切勿在生產(chǎn)環(huán)境使用!!!
查看是否開(kāi)啟及全局日志文件存放位置: show variables like '%general_log%';
開(kāi)啟全局日志: set global general_log=1;
測(cè)試:
