***** ## 排序優化 ### 分析 * 1.觀察,至少跑一天,看看生產的慢SQL情況 * 2.開啟慢查詢日志,設置闕值,比如超過5秒鐘的就是慢SQL,并抓取出來 * 3.explain + 慢SQL分析 * 4.show profile * 5.進行SQL數據庫服務器的參數調優(運維orDBA來做) ### 總結 * 1.慢查詢的開啟并捕獲 * 2.explain+慢SQL分析 * 3.show profile查詢SQL在MySQL服務器里面的執行細節 * 4.SQL數據庫服務器的參數調優 ### 永遠小表驅動大表 ``` for i in range(5): for j in range(1000): pass for i in range(1000): for j in range(5): pass ``` ### order by優化 ~~~ order by子句,盡量使用index方式排序,避免使用filesort方式排序 ~~~ 1.建表，插入測試數據 ~~~ create table tbla( age int, birth timestamp not null ); insert into tbla(age,birth) values(22,now()); insert into tbla(age,birth) values(23,now()); insert into tbla(age,birth) values(24,now()); ~~~ 2.建立索引 ~~~ create index idx_tbla_agebrith on tbla(age,birth); ~~~ 3.分析 MySQL支持兩種方式的排序,filesort和index,index效率高,MySQL掃描索引本身完成排序。filesort方式效率較低 ### order by 滿足兩種情況下,會使用index方式排序 - 1.order by 語句使用索引最左前列 - 2.使用where子句與order by子句條件組合滿足索引最左前列 ### filesort有兩種算法-雙路排序和單路排序 雙路排序,MySQL4.1之前是使用雙路排序,字面意思就是兩次掃描磁盤,最終得到數據,讀取行指針和order by列,對他們進行排序,然后掃描已經排序好的列表,按照列表中的值重新從列表中讀取對應的數據輸出 <br>單路排序,從磁盤讀取查詢需要的所有列,按照order by列在buffer對他們進行排序,然后掃描排序后的列表進行輸出,它的效率更快一些,避免了第二次讀取數據,并且把隨機IO變成了順序IO,但是它會使用更多的空間 <br>優化策略調整MySQL參數 ``` 增加sort_buffer_size參數設置 增大max_lenght_for_sort_data參數的設置 ``` ### 提高order by的速度 - order by時select * 是一個大忌,只寫需要的字段 - 當查詢的字段大小總和小于max_length_for_sort_data而且排序字段不是text|blob類型時,會用改進后的算法--單路排序 - 兩種算法的數據都有可能超出sort_buffer的容量,超出之后,會創建tmp文件進行合并排序,導致多次I/O - 嘗試提高sort_buffer_size - 嘗試提高max_length_for_sort_data ### 練習 ``` 索引 a_b_c(a,b,c) order by a,b order by a,b,c order by a desc,b desc,c desc where a = const order by b,c where a = const and b = const order by c where a = const and b > const order by b,c order by a asc,b desc,c desc where g = const order by b,c where a = const order by c where a = const order by by a,d ```