怎么在MySQL中對查詢進行優化

本篇文章給大家分享的是有關怎么在MySQL中對查詢進行優化，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

一、創建索引規范

在學習索引優化之前，需要對創建索引的規范有一定的了解，此規范來自于阿里巴巴開發手冊。

主鍵索引：pk_column_column

唯一索引：uk_column_column

普通索引：idx_column_column

二、索引失效原因

創建索引需知道在什么情況下索引會失效，只有了解索引失效的原因，在創建索引時才不會出現一些已知錯誤。

1.帶頭大哥不能死

這局經典的語句就是涵蓋創建索引時一定要符合最左側原則。

例如表結構為 u_id，u_name，u_age，u_sex，u_phone，u_time

創建索引為 idx_user_name_age_sex 。

查詢條件必須帶上u_name這一列。

2.不在索引列上做任何操作

不在索引列上做任何計算、函數、自動或者手動的類型轉換，否則會進行全表掃描。簡而言之不要在索引列上做任何操作。

3.倆邊類型不等

例如建立了索引idx_user_name，name字段類型為varchar

在查詢時使用where name = kaka，這樣的查詢方式會直接造成索引失效。

正確的用法為where name = "kaka" 。

4.不適當的like查詢會導致索引失效

創建索引為idx_user_name

執行語句為select * from user where name like "kaka%";可以命中索引。

執行語句為select name from user where name like "%kaka";可以使用到索引（僅在8.0以上版本）。

執行語句為select * from user where name like ''%kaka";會直接導致索引失效

5.范圍條件之后的索引會失效

創建索引為idx_user_name_age_sex

執行語句select * from user where name = 'kaka' and age > 11 and sex = 1;

上面這條sql語句只會命中name和age索引，sex索引會失效。

復合索引失效需要查看key_len的長度即可。

總結：%在后邊會命令索引，當使用了覆蓋索引時任何查詢方式都可命中索引。

以上就是咔咔關于索引失效會出現的原因總結，在很多文章中沒有標注MySQL版本，所以你有可能會看到is null 、or索引會失效的結論。

三、SQL優化殺手锏之 Explain

在寫完SQL語句之后必須要做的一件事情就是使用Explain進行SQL語句檢測，看是否命中索引。

下圖就是使用explain輸出格式，接下來將會對輸出格式進行簡單的解釋。

1.id 這列就是查詢的編號，如果查詢語句中沒有子查詢或者聯合查詢這個標識就一直是1。

如存在子查詢或者聯合查詢這個編號會自增。

2.select_type

最常見的類型就是SIMPLE和PRIMARY，此列知道就行了。

3.table

理解為表名即可

4. **type

此列是在優化SQL語句時最需要關注的列之一，此列顯示了查詢使用了何種類型。

以下排序從最優到最差。

• system：表內只有一行數據

• const：最多只會有一條記錄匹配，常用于主鍵或者唯一索引為條件查詢

• eq_ref：當連接使用的索引為主鍵和唯一時會出現

• ref：使用普通索引=或<=> 運算符進行比較將會出現

• fulltext：使用全文索引

• ref_or_null：跟ref類型類似，只是增加了null值的判斷，實際用的不多。語句為where name = 'kaka' and name is null，name為普通索引。

• index_merge：查詢語句使用了倆個以上的索引，常見在使用and、or會出現，官方文檔將此類型放在ref_or_null之后，但是在很多的情況下由于讀取索引過多性能有可能還不如range

• unique_subquery：用于where中的in查詢，完全替換子查詢，效率更高。語句為value IN (SELECT primary_key FROM single_table WHERE some_expr)

• index_subquery：子查詢中的返回結果字段組合是一個索引（或索引組合），但不是一個主鍵或唯一索引

• range：索引范圍查詢，常見于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等運算符的查詢中。

• index：索引全表掃描，把索引從頭到尾掃一遍

• all：全表掃描,性能最差。

5.possible_keys

此列顯示的可能會使用到的索引

6. **key

優化器從possible_keys中命中的索引

7.key_len

查詢用到的索引長度（字節數），key_len只計算where條件用到的索引長度，而排序和分組就算用到了索引，也不會計算到key_len中。

8.ref

如果是使用的常數等值查詢，這里會顯示const。

如果是連接查詢，被驅動表的執行計劃這里會顯示驅動表的關聯字段。

如果是條件使用了表達式或者函數，或者條件列發生了內部隱式轉換，這里可能顯示為func。

9. **rows

這是mysql估算的需要掃描的行數（不是精確值）。

這個值非常直觀顯示 SQL 的效率好壞, 原則上 rows 越少越好。

10.filtered

此列表示存儲引擎返回的數據在server層過濾后，剩下多少滿足查詢的記錄數量的比例，注意是百分比，不是具體記錄數

11. **extra

在大多數情況下會出現以下幾種情況。

• Using index：使用了覆蓋索引，查詢列都為索引字段

• Using where：使用了where語句

• Using temporary ：查詢結果進行排序的時候使用了一張臨時表

• Using filesort ：對數據使用一個外部的索引排序

• Using index condition：使用了索引下推，關于索引下推可以查看咔咔之前文章MySQL索引一文

12.總結

以上就是關于Explain所有列的說明，在平時開發的過程中，一般只會關注type、key、rows、extra這四列。

• type優化目標至少達到range級別，要求是ref級別，如果可以consts最好。

• key是查詢使用到的索引，如果此列為空，要么未建立索引，要么索引失效。

• rows是這條SQL語句掃描的行數，越少越好。

• extra：此列為擴展列，如果出現臨時表、文件排序則需要優化。

四、SQL優化殺手锏之 慢查詢

上文說到了可以直接使用explain來分析自己的SQL語句是否合理，接下來再聊一個點那就是慢查詢。

查看慢查詢是否打開

查看是否記錄沒有使用索引的SQL語句

開啟慢查詢、開啟記錄沒有使用到索引的SQL語句

set global log_queries_not_using_idnexes='on';

set global log_queries_not_using_indexes='on';

查詢以上倆個配置是否打開

設置慢查詢時間，這個時間由自己把控，一般1s即可 set globle long_query_time=1;

如果查看這個時間沒有變，則關于客戶端在重新連接一次即可。

查看慢查詢存儲位置

然后隨便執行一條不執行索引的語句即可在這個日志中查看到此語句

上圖中一般需要主要觀察的是Query_time、SQL語句內容。

以上就是關于如何使用慢查詢來查看項目中出現問題的SQL語句。

五、優化大法

此處跟大家聊一些常用的SQL語句優化方案，以上的倆個工具要好好的利用，輔助我們進行打怪。

• 禁止使用select *，需要什么字段查詢什么字段

• where字段設置索引

• group by、order by字段設置索引

• 舍棄offset，limit分頁，使用延遲關聯來實現分頁(數據量不大時可不用)

• 寫分頁時當count為0時，直接返回避免執行分頁語句

• 利用覆蓋索引進行查詢避免回表

• 建立復合索引時區分度最高的放在最左側

• 統計數據行數只用count(*)，別整的花里胡哨的

• 關于in和exist，如果查詢的倆個表大小一致則性能差別可忽略，如果子查詢表大用exist，否則使用in

• 查詢一行數據時加上limit 1

• 選擇合理的數據類型，在滿足條件下數據類型越小越好

• 聯合查詢join最多三個表，并且需要join的字段數據類型保持一致

• in操作能避免盡量避免，無法避免的情況下in元素控制在1000以內

• 數據更新頻繁，區分度不高的列不適合建立索引

• explain中的type至少要達到range，要求為ref

• 聯合索引滿足最左側原則

以上就是怎么在MySQL中對查詢進行優化，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。