Mysql慢優化

在mysql中，long_query_time的值為10，當sql語句執行的時間超過這個數值時，則會被記錄到慢查詢日志中。

Mysql語句查詢流程

1、客戶端發送sql語句到服務端；

2、服務端查看是否打開了緩存，若緩存打開，則查詢緩存，若緩存命中，則直接返回從緩存中查詢到的數據（在Msyql8.0中，已沒有緩存的概念）；

3、使用分析器對sql語句進行語法分析，判斷其是否有語法錯誤；

4、通過優化器生成后續執行計劃；

5、通過執行器調用存儲引擎的接口，執行sql語句。

定位慢sql的方法

日志查詢：開啟慢查詢日志，并使用mysqldumpslow等命令分析慢查詢日志，查找到慢sql語句。

服務監控：從業務的根基監控慢sql，主要通過字節碼插樁、連接層擴展、使用ORM框架等方式，對服務中的慢sql進行監控和警示。

定位到慢sql后，通過explain命令對sql語句進行分析，查看慢sql的如何執行。

優化sql的方式

避免過多的列查詢

進行查詢時，盡量避免使用select *，使用select 列名的方式進行查詢，只查詢需要的列。

對于分頁優化，通過延遲關聯、書簽兩種方式。

延遲關聯

對于延遲關聯，在偏移量很大時，如limit 10000,10需要查詢10010條數據然后舍去前10000數據，這樣會造成不必要的查詢資源浪費，因此可使用延遲關聯，如下列sql：

select * from a where tid?= "1" limit 10000,10

該語句可被優化為先查詢所需要的id，這些必須要滿足tid="1"的條件，且limit同條件同為10000,10，因為未優化前，要先滿足tid = "1"才能查詢對應的偏移量，優化后的sql語句如下：

select * from a where id in (select id from a where tid?= "1" limit 10000,10)

in后面僅查詢了id一個屬性，因此浪費的查詢開銷更小，最后通過in只需要查詢10條需要的數據。

該方法的主要思路是查詢出需要的主鍵，然后主鍵表關聯原表即可。

書簽

對于書簽，通過記錄上一次查詢返回的最后一行數據，下一次查詢時從這個數據開始，避免了重復行的查詢，一般通過屬性>last_max_id作為條件，查詢時不需要offset，只需要查詢的數據即可。

索引優化

索引覆蓋

使用非主鍵索引查詢時需要回表，但如果索引的葉子節點中已包含了要查詢的字段，則不需要進行回表查詢，這種方法就是索引覆蓋。將需要查詢的字段與主鍵一起建立聯合索引。

避免使用<>、!=等操作符

使用上述操作符會導致索引失效，可考慮使用>、<、=、between等代替上述操作符。

適當使用前綴索引

前綴索引適用于前幾位區分度較大、字段長度很大的字段查詢，如查詢郵箱時，由于格式一般為@xxx.com，因此比較適合使用前綴索引，添加的方式：

alter table a add index index2(email(6))

對于前綴索引，無法進行

避免在索引列上使用函數

在索引列上使用函數會導致索引失效，因為首先需要計算出函數值后再進行比較等操作，這樣無法利用索引。

正確使用聯合索引

對于聯合索引的使用，需要滿足最左前綴原則（也稱最左匹配原則），指的是使用聯合索引時查詢條件從索引的最左側的列開始，不跳過中間的列。

join優化

優化子查詢

對于where、select列表中的子查詢，往往會導致性能問題，因為可能會為每一行的外層查詢執行一次子查詢，而使用join可以對其進行優化。如下列sql語句，使用子查詢的方式如下：

select * from a where id in (select id from b);

那么查詢a的每一行時，會執行子查詢select id from b，導致性能下降，此時可使用join來進行優化：

select * from a ioin b on a.id = b.id

此時連接后表中只有a、b數據表中id相等的數據行，和上述sql語句是等效的，同時減少了循環查詢的次數。

小表驅動大表

在執行 join?操作時，盡量讓行數較少的表驅動行數較多的表，這樣可以減少查詢過程中需要處理的數據量，同時連接得到的數據表的額外數據量也更少，減少了空間浪費，如下列sql語句：

select * from A left join B on A.id = B.id

其中A數據表數據量較小，B數據表數據量較大。

適當增加冗余字段

對于一些查詢頻率較高的字段，可考慮使用增加冗余字段，在查詢時不用使用join關聯其他數據表，直接查詢即可。

避免使用join關聯過多的表

一般使用join關聯不超過3個數據表。

union優化

union是用于關聯兩個或多個select查詢的結果，對于使用union查詢的語句，可將where、limit等條件查詢語句，下推到各select中，每個分支僅處理滿足條件的數據，減少了不必要的數據合并和過濾，如下列語句，在未實現條件下推時的查詢如下：

select * from(select * from aunionselect * from b
)as c
where c.id = 1; 

條件下推后，sql語句如下：

select * from a where id = 1
union 
select * from b where id = 1;

除上述方法外，還可利用索引的有序性，按照索引順序掃描得出的自然有序結果，從而避免了排序操作。