1、查詢截取優化

SQL調優過程：

1. 觀察，至少跑1天，看看生產的慢SQL情況。
2. 開啟慢查詢日志，設置闕值，比如超過5秒鐘的就是慢SQL，并將它抓取出來。
3. explain + 慢SQL分析。
4. show profile。
5. 運維經理 or DBA，進行SQL數據庫服務器的參數調優。

總結：

• 慢查詢的開啟并捕獲
• explain + 慢SQL分析
• show profile查詢SQL在Mysql服務器里面的執行細節和生命周期情況
• SQL數據庫服務器的參數調優。

1.1、in 和 exists（小表驅動大表）

在查詢的優化中永遠小表驅動大表。

舉例說明：

for(int i=5;.......)
{
     for(int j=1000;......)
     {}
}

如果小的循環在外層，對于數據庫連接來說就只連接5次，進行5000次操作；但如果1000在外，則需要進行1000次數據庫連接，從而浪費資源，增加消耗。這就是為什么要小表驅動大表。

• 當B表的數據集小于A表的數據集時，用in優于exists。

即B表的數據集小于A表的數據集時，用 select ... from A where ... in (selct ... from B) 的語法。如下：

select * from A where id in (select id from B);
等價于:
for select id from B
for select * from A where A.id = B.id

• 當A表的數據集小于B表的數據集時，用exists優于in

即A表的數據集小于B表的數據集時，用 select ... from A where exists (selct ... from B where ...) 的語法。如下：

select * from A where exists (select 1 from B where B.id = A.id)
等價于：
for select * from A
for select * from B where B.id = A.id

如上所述，在查詢中永遠遵循小表驅動大表的原則。

1.1.1、判斷驅動表與非驅動表

1.2、order by 優化

MySQL支持二種方式的排序：FileSort和Index

• Index效率高，它指MySQL掃描索引本身完成排序
• FileSort方式效率較低。應盡量使用Index方式排序，避免使用FileSort方式排序。

當 ORDER BY滿足以下情況時，會使用Index方式排序：

1. ORDER BY語句遵循索引的最左前綴原則，并且中間兄弟不能斷。
2. where 子句和 order by 子句拼起來符合最左前綴，并且 where 子句中的條件是常量

當出現以下情況時，會使用 FileSort 排序：

1. 不滿足最左前綴原則
2. 多字段之間的排序規則不一致，比如a是降序，b是升序。只要索引的字段的排序規則都一樣，不管是升序還是降序，都能使用 Index 排序。
3. 用于排序的字段不屬于索引

舉例說明，假設給表 tblA 建索引 tblA(age, birth)，下面的 order by 語句遵循最左前綴原則，所以能使用 Index 方法進行排序，如下：

下面 where 和 order by 子句都不遵循最左前綴原則，所以只能使用 FileSort 方式進行排序，如下：

或者，where 子句和 order by 子句拼起來符合最左前綴，并且 where 子句中的條件是常量，此時排序也會使用 Index 進行排序的，如下：

1.3、group by優化

Group By 的優化和 order by差不多，group by 實質上是先排序后進行分組，也需遵照索引的最佳左前綴。

where高于having，能寫在where限定的條件就不要去having限定了。

2、慢查詢日志

MySQL的慢查詢日志是MySQL提供的一種日志記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢日志中。long_query_time的默認值為10，意思是運行10秒以上的語句。

通過慢查詢日志文件可以查看哪些SQL超出了我們的最大忍耐時間值，比如一條sql執行超過5秒鐘就算慢SQL，我們就可以收集到超過5秒的sql，結合 explain 進行全面分析。

2.1、開啟慢查詢日志

默認情況下，MySQL數據庫并沒有開啟慢查詢日志的記錄，需要我們手動來設置這個參數。

我們可以通過以下命令來查看是否有開啟慢查詢日志：

-- 查看日志是否開啟
SHOW VARIABLES LIKE '%slow_query_log%';

查詢結果如下：

可以看到當前是未開啟的，并且可以看到慢查詢日志文件的目錄是在 /var/lib/mysql/192-slow.log 下。該文件是系統默認指定的，格式為：host_name-slow.log。

要想開啟慢查詢日志，可通過如下命令進行操作：

-- 開啟慢查詢日志
set global slow_query_log=1  -- 該命令只對當前數據庫生效

要想永久生效，需要修改配置文件 my.cnf，在 [mysqld] 下增加或修改參數 slow_query_log 和 slow_query_log_file ，然后需重啟MySQL服務器，修改如下：

slow_query_log =1
slow_query_log_file=/var/lib/manongyanjiuseng-slow.log

不過一般我們也不建議啟動該參數，因為開啟慢查詢日志會或多或少帶來一定的性能影響。

2.2、設置慢SQL時間閾值

判定某條 SQL 是否算是慢 SQL，是否會放到慢查詢日志文件上，這個是由參數 long_query_time 控制的，默認情況下，long_query_time 的值為10秒。

SQL 的執行時間只有大于 long_query_time 的值時才會被記錄，小于或者等于時并不會被記錄。

可通過以下命令查看時間閾值：

SHOW VARIABLES LIKE 'long_query_time%';

可通過以下命令設置時間閾值：

set global long_query_time=3;   -- 設置為 3 秒

設置完后，需要重啟一下命令行窗口或者打開新窗口才可以看到設置后的效果。

也可以在配置文件中設置閾值，配置如下：

#[mysqld]下配置:
slow_query_log=1;
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3;
log_output=FILE;

2.3、查詢慢SQL

可通過以下命令來查詢當前系統中有多少條慢查詢記錄：

show global status like '%Slow_queries%';

我們可以通過 select sleep(xx秒); 命令來模擬一個執行時間為 xx 秒的 SQL，以此來驗證上面的命令。如下：

2.4、使用 mysqldumpslow 工具分析慢SQL

在生產環境中，如果要手工地去分析日志，查找、分析SQL，這顯然是個體力活，由此 MySQL 提供了日志分析工具mysqldumpslow，我們可以通過該工具來分析慢 SQL 。

mysqldumpslow 命令的參數如下：

• -s, 是sort的意思，表示按照何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，ac、at、al、ar，表示相應的倒序；
• -t, 是top n的意思，即為返回前面多少條的數據；
• -g, 是grep的意思，后邊可以寫一個正則匹配模式，大小寫不敏感的；

例子：

• mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log：得到返回記錄集最多的10個SQL 
• mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log：得到訪問次數最多的10個SQL
• mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log：得到按照時間排序的前10條里面含有左連接的查詢語句

另外建議在使用這些命令時結合│和more 使用，不然可能會出現數據量多大不斷刷屏的情況，mysqldumpslow -s r-t 10 /ar/lib/mysql/atguigu-slow.log | more

3、show profiles（分析SQL執行情況）

Show Profile 是 mysql 提供的可以用來分析當前會話中 sql 語句執行的資源消耗情況的工具，可用于 sql 調優的測量。

show profile 默認是處于關閉狀態的，并且開啟后只存活于當前會話，也就說每次使用前都需要開啟。默認也只保存最近15次的運行結果。

我們可以通過下面語句來查看是否開啟：

show variables like 'profiling';

如未開啟，則可以通過以下命令開啟：

set profiling=on;

如下圖，OFF 為未開啟狀態，ON 為已開啟狀態。

3.1、show profiles的使用

我們可以隨便執行一些 SQL 語句，然后通過 show profiles 來查看這些語句的執行情況。示例如下：

說明如下：

• Query_ID：每條SQL語句都有一個query id
• Duration：該SQL語句的執行消耗時間，單位為秒

3.1.1、show profiles的參數

可以通過使用一些參數來針對指定的SQL進行分析，如下：

show profile cpu,block io for query Query_ID;  -- Query_ID為上圖中show profiles列表中的Query_ID

示例如下：

show profile 的常用查詢參數如下：

• ALL：顯示所有的開銷信息。
• BLOCK IO：顯示塊IO開銷。
• CONTEXT SWITCHES：上下文切換開銷。
• CPU：顯示CPU開銷信息。
• IPC：顯示發送和接收開銷信息。
• MEMORY：顯示內存開銷信息。
• PAGE FAULTS：顯示頁面錯誤開銷信息。
• SOURCE：顯示和Source_function，Source_file，Source_line相關的開銷信息。
• SWAPS：顯示交換次數開銷信息。

如果在show profile診斷結果中出現了以下4條結果中的任何一條，則sql語句需要優化。

• converting  HEAP to MyISAM：查詢結果太大，內存不夠，數據往磁盤上搬了。
• Creating tmp table：創建臨時表。先拷貝數據到臨時表，用完后再刪除臨時表。
• Copying to tmp table on disk：把內存中臨時表復制到磁盤上，危險?。?！
• locked

4、全局查詢日志（切勿在生產環境開啟該功能）

全局查詢日志功能會記錄下你所查詢的所有SQL語句。但非常不建議在生產環境上開啟該功能，因為所有的操作都會被記錄下來，記錄的日志量會非常大，對磁盤是一個很大的壓力，對MySQL數據的性能有一定的影響。

可以通過修改配置文件或者直接執行命令來開啟記錄全局查詢日志的功能。

• 以修改配置文件的方式開啟

在配置文件 my.cnf 文件中添加以下配置：

#開啟記錄全局日志的功能
general_log=1
#記錄全局日志的文件的路徑
general_log_file=/path/logfile
#輸出格式：FILE-以文件格式記錄 TABLE-記錄在數據庫表general_log中
log_output=FILE

• 直接執行命令來開啟

可以通過以下命令來開啟記錄全局查詢日志的功能：

set global general_log = 1;
set global log_output = 'TABLE';  -- 配置日志記錄在數據庫表中

示例如下：

當開啟并配置記錄在數據庫表中后，所有的SQL執行語句都會被記錄在 mysql.general_log 表中，可以通過查看該表數據來查看到所有的SQL語句。