記錄一次sql優化查詢

場景：關聯查詢，一張主表關聯4張表進行查詢。主表資料量是16萬，其中被關聯的一張表的數量是6萬。

遇到頁面響應速度過慢的情況，首先考慮是否是sql查詢緩慢引起的。

第一步開啟mysql的慢查詢日志(網上教程很多，本篇文章不再贅述)

第二步分析慢查詢日志，這裡要說下分析工具。常用的有兩種，一是mysql自帶的

mysqldumpslow

工具，二是

pt-query-slow

工具。後者需要自行安裝，下面針對兩種工具的使用進行說明：

(1) 

mysqldumpslow

 執行

mysqldumpslow

，如果出現指令

mysqldumpslow

不存在的情況，需要進行如下處理(要清楚mysql的安裝位置)：

ln -s /www/server/mysql/bin/mysqldumpslow /usr/bin/
           

 簡單使用：

mysqldumpslow -t 10 mysql-slow.log
           

 含義：傳回查詢時間最慢的前10條記錄

 相關參數：

  -s：是表示按照何種方式排序

   c：通路計數

   l：鎖定時間

   r：傳回記錄

   t：查詢時間

  al：平均鎖定時間

  ar：平均傳回記錄數

  at：平均查詢時間

  -t：是top n的意思，即為傳回前面多少條的資料

  -g：後邊可以寫一個正則比對模式，大小寫不敏感的

(2) 

pt-query-slow

 安裝：

yum install perl-DBI  
yum install perl-DBD-MySQL  
yum install perl-Time-HiRes  
yum install perl-IO-Socket-SSL  
wget percona.com/get/pt-query-digest  
chmod u+x pt-query-digest  
mv pt-query-digest /usr/bin/
           

 使用：

pt-query-digest  /www/server/data/mysql-slow.log
           

 常見用法：

 (1)分析最近12小時内的查詢

pt-query-digest --since=12h /www/server/data/mysql-slow.log > slow_report1.log
           

 (2)分析指定時間範圍内的查詢

pt-query-digest /www/server/data/mysql-slow.log --since '2020-03-20 09:30:00' --until '2020-03-27 13:00:00'  > slow_report2.log
           

 通過檢視找到耗時較長的sql：

SELECT
 	`c`.*, 
     　　c.price unit_price,
	d. NAME AS category_name,
	b.nickname creator,
	a.nickname owner_name
FROM
	`material` `c`
LEFT JOIN `category` `d` ON `c`.`category_id` = `d`.`id`
LEFT JOIN `admin` `a` ON `c`.`operator_id` = `a`.`id`
LEFT JOIN `admin` `b` ON `c`.`creator_id` = `b`.`id`
 LEFT JOIN `stock` `s` ON `c`.`id` = `s`.`material_id`
WHERE
	`c`.`status` = '1'
AND `c`.`company_id` = '1'
  ORDER BY
   	`c`.`created_at` DESC
  LIMIT 0,
   10
               

 對這條sql執行explain：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	c		idx_company_id, idx_status	idx_company_id	4	const	33872	Using where; Using temporary;  Using filesort
		d	eq_ref	PRIMARY			skxx.c.category_id
		a							Using where
		b					=skxx.c.category_id
		s	ALL	1683	Using where;  Using join buffer (Block Nested Loop)

看到執行計劃後發現第一行Extra出現了

Using temporary

和

Using filesort

，最後一行出現

Using join buffer (Block Nested Loop)

，這幾項說明在查詢時使用了臨時表和檔案排序，性能很不好。嘗試給所有關聯條件添加索引後，再次執行explain，發現

Using temporary

和 

Using join buffer (Block Nested Loop)

都不見了，但是仍然有檔案排序！定位到 

order by

 發現列created_at并未添加索引，于是添加索引，再次執行explain，

Using filesort

已經沒有了！！

有關explain的使用，網上教程很多，這裡不再詳細介紹