最近做一個CRM系統，發現了慢查詢日志里記載了許多的慢sql，于是就對其進行了sql優化，在優化的程序中，自己也歸納整理了一些sql優化的方案，今天就來和大家聊聊，
**1、慢查詢的分析**
常見的分析慢查詢sql的方法大概有三種：explain、show profile、trace 分析 sql優化器，本文主要介紹explain的方法去分析慢sql，其余兩種方法有興趣的同學可以去了解下，
**2、explain各引數解釋**
（1）explain語法：explain+“需分析的sql”；
例：我想分析“select * from table1 where b=500;”這條sql的執行效率，那么直接在sql命令列下執行“expalin select * from table1 where b=500;”就可以查看了；執行結果如下：
根據上圖，可以看到有許多個欄位，那這些欄位分別有什么意義呢？見下圖（紅框標出的為重點關注欄位）：
其中各個欄位又可能有多個不同的值，重點關注欄位select_type、type、Extra的可能值如下圖所示：
其中，上圖中的“type”的值的查詢性能從上到下依次是最好到最差，
**3、常用sql優化方法**
（1）使用索引
大家對MySQL的索引應該不會感到陌生吧，資料量大的時候，最常見的加快查詢效率的方法那肯定是加索引了，要知道MySQL的B+索引樹可是能在2~4層樹就能從上億的資料中提取出相關資料的，不加索引的話得進行上億次磁盤io查找（關于B+樹的具體原理，大家可以參考下這篇文章，寫得很不錯，http://www.liuzk.com/410.html，索引雖好用，但是存在很多不走索引的情況，下面列舉常見幾種不走索引的情況，以及如何讓它走索引：
①對查詢條件使用函式
如：select * from table1 where date(c) ='2020-08-20';
應改為：select * from table1 where c>='2020-08-20 00:00:00' and c<='2020-08-20 23:59:59';
②隱式轉換：如把varchar型別當成int型去寫
例：select * from table1 where a=1000; （其中，a欄位在資料庫中是varchar型別）
應改為：select * from table1 where a='1000';
③模糊查詢
如：select * from table1 where a like '%1111%';
應改為：select * from table1 where a like '1111%'; 但要看具體業務，可能不對
注意：select * from table1 where a like '%1111'; 這種也是不走索引的
可以這么理解：like 匹配是%在前面的都不走索引
④范圍查詢
如：select * from table1 where b>=1 and b <=2000; （這條資料查詢范圍過大，是全表掃描，優化器選擇不走索引）
應改為：select * from table1 where b>=1 and b <=1000;
select * from table1 where b>=1001 and b <=2000;
⑤計算操作 （但一般在代碼層面做操作，很少會在資料庫做操作）
如：select * from table1 where b-1 =1000;
應改為：select * from table1 where b =1000 + 1;
⑥OR 操作
如果條件中有OR,即使其中有條件帶索引也不會使用，換言之,就是要求使用的所有欄位,都必須建立索引，所以除非每個列都建立了索引，否則不建議使用OR，在多列OR中，可以考慮用UNION 替換
如：select * from table1 where create_time = '2020-08-20 11:49:30' OR b > 854;
應改為：select * from table1 where create_time = '2020-08-20 11:49:30' UNION select * from table1 where b > 854;
（2）分頁優化
如：select * from table1 order by a limit 99000,10; （其中，a欄位有索引）
但結果不走索引，原因是掃描整個索引并查找到沒索引的行的成本比掃描全表的成本更高，所以優化器放棄使用索引，（關鍵是讓排序時回傳的欄位盡可能少）
應改為：select * from table1 f inner join (select id from table1 order by a limit 99000,10)g on f.id = g.id;
或者 select * from table1 where id >= (select id from table1 order by a limit 99000,1) limit 10;
（3）連接查詢優化
在專案中，表連接查詢是比較常見的，尤其是一些統計模塊，表連接有兩種演算法，一種叫Nested-Loop Join 演算法（簡稱NLJ），另一種是Block Nested-Loop Join 演算法（簡稱BNL）（關聯欄位不存在索引時會使用到），感興趣的小伙伴可以深入去了解下相關原理哦，我們進行關聯查詢優化的方法是：盡量讓 BNL變成 NLJ ，就是說在關聯欄位上加上索引，
（4）order by和group by優化
①首先我們要知道MySQL中有兩種排序方式，一種是通過有序索引直接回傳有序資料（Extra欄位：Using index）；另一種是通過 Filesort 進行的排序，不走索引（Extra欄位：Using filesort）；毫無疑問，肯定是有序索引排序更快，
常見order by優化方法：
①在排序欄位上添加索引
如：select c,id from table1 order by c; （c有索引）
②多個欄位排序，可以在多個排序欄位上添加聯合索引來優化排序陳述句
如：select id,a,b from table1 order by a,b; （a,b是聯合索引）
注意：select id,a,b from table1 order by b,a; （a,b是聯合索引）此時不走索引，最左匹配前綴原則了解下
③對于先等值查詢再排序的陳述句，可以通過在條件欄位和排序欄位添加聯合索引來優化
如：select id,a,b from table1 where a=1000 order by b; （a,b是聯合索引）
如果a,b不是聯合索引的話，即時有a索引，b索引，排序也不會走索引
④去掉不必要的回傳欄位
如：select * from table1 order by a,b; /* 根據a和b欄位排序查出所有欄位的值 */
應改為：select id,a,b from table1 order by a,b; /* 根據a和b欄位排序查出id,a,b欄位的值 */
不走索引原因：掃描整個索引并查找到沒索引的行的成本比掃描全表的成本更高，
所以優化器放棄使用索引，
默認情況，會對 group by 欄位排序，因此group by優化方式與 order by 基本一致，
本篇文章就寫到這，你們作業中還有什么好的優化sql的方法嗎？歡迎在評論區分享！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/14907.html
標籤：MySQL
上一篇：PB2018已經發布，可下載