一、查詢優化

1、永遠小表驅動大表

EXISTS

SELECT ... FROM table WHERE EXISTS (subquery)

該語法可以理解為：將主查詢的數據,放到子查詢中做條件驗證,根據驗證結果(TRUE 或 FALSE)來決定主查詢的數據結果是否得以保留.

提示

1 EXISTS (subquery) 只返回 TRUE 或 FALSE,因此子查詢中的 SELECT * 也可以是 SELECT 1 或select 'X',官方說法是實際執行時會忽略 SELECT 清單,因此沒有區別

2 EXISTS 子查詢的實際執行過程可能經過了優化而不是我們理解上的逐條對比,如果擔憂效率問題,可進行實際檢驗以確定是否有效率問題.

3 EXISTS 子查詢往往也可以用條件表達式、其他子查詢或者 JOIN 來替代,何種最優需要具體問題具體分析

2、order by關鍵字優化

ORDER BY子句,盡量使用Index方式排序,避免使用FileSort方式排序

以下兩種情況會使用Index排序：

• order by語句使用索引最左前列.

• 使用where子句與order by子句條件列組合滿足最左前列.

盡可能在索引列上完成排序操作,遵照索引建的最佳左前綴,如果不在索引列上,filesort有兩種算法：雙路排序和單路排序

由于單路是后出的,總體而言好過雙路

優化策略

• 增大sort_buffer_size參數的設置

• 增大max_length_for_sort_data參數的設置

提高Order By的速度

1. Order by時select * 是一個大忌,只Query需要的字段, 這點非常重要.在這里的影響是：

1.1 當Query的字段大小總和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 類型時,會用改進后的算法——單路排序, 否則用老算法——多路排序.

1.2 兩種算法的數據都有可能超出sort_buffer的容量,超出之后,會創建tmp文件進行合并排序,導致多次I/O,但是用單路排序算法的風險會更大一些,所以要提高sort_buffer_size.

2. 嘗試提高 sort_buffer_size

不管用哪種算法,提高這個參數都會提高效率,當然,要根據系統的能力去提高,因為這個參數是針對每個進程的

3. 嘗試提高 max_length_for_sort_data

提高這個參數, 會增加用改進算法的概率.但是如果設的太高,數據總容量超出sort_buffer_size的概率就增大,明顯癥狀是高的磁盤I/O活動和低的處理器使用率.

order by總結

3、group by優化

• group by實質是先排序后進行分組,遵照索引建的最佳左前綴

• 當無法使用索引列,增大max_length_for_sort_data參數的設置+增大sort_buffer_size參數的設置

• where高于having,能寫在where限定的條件就不要去having限定了.

二、慢查詢日志

1、是什么

MySQL的慢查詢日志是MySQL提供的一種日志記錄,它用來記錄在MySQL中響應時間超過閥值的語句,具體指運行時間超過long_query_time值的SQL,則會被記錄到慢查詢日志中.

具體指運行時間超過long_query_time值的SQL,則會被記錄到慢查詢日志中.long_query_time的默認值為10,意思是運行10秒以上的語句.

2、查看是否開啟(默認不開啟)：

SHOW VARIABLES LIKE '%slow_query_log%';

開啟慢查詢：使用set global slow_query_log=1開啟了慢查詢日志只對當前數據庫生效,

如果要永久生效,要修改配置文件,在[mysqld]下增加參數：

slow_query_log =1

slow_query_log_file=/var/lib/mysql/slow.log

3、哪些參數會被記錄到慢查詢日志

這是由long_query_time控制,默認是10,即大于10秒的sql會被記錄下來.

show variables like 'long_query_time%';查看

4、通過set global long_query_time=3;設置闕值時間,

5、查詢當前系統中有多少條慢sql

show global status like '%Slow_queries%';

6、配置慢查詢

在【mysqld】下配置：

slow_query_log=1;

slow_query_log_file=/var/lib/mysql/slow.log

long_query_time=3;

log_output=FILE