MySQL 系列教程之（十一）Explain 與慢查詢優化丨【綻放吧！數據庫】

慢查詢與 Explain 優化

慢查詢

慢查詢日志由long_query_time執行時間超過幾秒的SQL語句組成.

慢查詢日志可用于查找需要很長時間才能執行的查詢，因此是優化的候選者。

慢查詢配置

-- // 查看“慢查詢”的配置信息 show variables like "%slow%"; -- // 查看“慢查詢”的時間定義 show variables like "long%"; -- //設置“慢查詢”的時間定義 set long_query_time=2; -- //開啟慢日志 set global slow_query_log='ON';

查詢慢查詢日志的開啟狀態和慢查詢日志儲存的位置

mysql> show variables like "%slow%"; +---------------------------+-----------------------------------------------+ | Variable_name | Value | +---------------------------+-----------------------------------------------+ | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | F:\Mysql-5.7.25\data\DESKTOP-7TLR5VO-slow.log | +---------------------------+-----------------------------------------------+ 5 rows in set, 1 warning (0.00 sec)

查看慢查詢日志

Explain

根據表，列，索引和WHERE子句中的條件的詳細信息，Mysql優化器會考慮許多技術來有效地執行SQL查詢中涉及的查找。優化程序選擇執行最有效查詢的操作集稱為“ 查詢執行計劃 ”，也稱為 EXPLAIN計劃。

EXPLAIN返回SELECT語句中使用的每個表的一行信息 。它按照MySQL在處理語句時讀取它們的順序列出輸出中的表。

mysql> explain select * from orders where order_num = 20005\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: orders partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)

MySQL索引使用的注意事項

MySQL 索引通常是被用于提高 WHERE 條件的數據行匹配時的搜索速度，在索引的使用過程中，存在一些使用細節和注意事項。

1.不要在列上使用函數和進行運算

不要在列上使用函數，這將導致索引失效而進行全表掃描。

select * from news where year(publish_time) = 2017

為了使用索引，防止執行全表掃描，可以進行改造。

select * from news where publish_time = '2017-01-01'

還有一個建議，不要在列上進行運算，這也將導致索引失效而進行全表掃描。

select * from news where id / 100 = 1

為了使用索引，防止執行全表掃描，可以進行改造。

select * from news where id = 1 * 100

2.盡量避免使用 != 或 not in或 <> 等否定操作符

應該盡量避免在 where 子句中使用 != 或 not in 或 <> 操作符，因為這幾個操作符都會導致索引失效而進行全表掃描。

3.盡量避免使用 or 來連接條件 ?

應該盡量避免在 where 子句中使用 or 來連接條件，因為這會導致索引失效而進行全表掃描。

select * from news where id = 1 or id = 2

4.多個單列索引并不是最佳選擇

MySQL 只能使用一個索引，會從多個索引中選擇一個限制最為嚴格的索引，因此，為多個列創建單列索引，并不能提高 MySQL 的查詢性能。

假設，有兩個單列索引，分別為 news_year_idx(news_year) 和 news_month_idx(news_month)。現在，有一個場景需要針對資訊的年份和月份進行查詢，那么，SQL 語句可以寫成：

select * from news where news_year = 2017 and news_month = 1

事實上，MySQL 只能使用一個單列索引。為了提高性能，可以使用復合索引 news_year_month_idx(news_year, news_month) 保證 news_year 和 news_month 兩個列都被索引覆蓋。

5.復合索引的最左前綴原則

復合索引遵守“最左前綴”原則，即在查詢條件中使用了復合索引的第一個字段，索引才會被使用。因此，在復合索引中索引列的順序至關重要。如果不是按照索引的最左列開始查找，則無法使用索引。

假設，有一個場景只需要針對資訊的月份進行查詢，那么，SQL 語句可以寫成：

select * from news where news_month = 1

此時，無法使用 news_year_month_idx(news_year, news_month) 索引，因為遵守“最左前綴”原則，在查詢條件中沒有使用復合索引的第一個字段，索引是不會被使用的。

6.覆蓋索引的好處

如果一個索引包含所有需要的查詢的字段的值，直接根據索引的查詢結果返回數據，而無需讀表，能夠極大的提高性能。因此，可以定義一個讓索引包含的額外的列，即使這個列對于索引而言是無用的。

7.范圍查詢對多列索引的影響(組合索引)

查詢中的某個列有范圍查詢，則其右邊所有列都無法使用索引優化查找。

舉個例子，假設有一個場景需要查詢本周發布的資訊文章，其中的條件是必須是啟用狀態，且發布時間在這周內。那么，SQL 語句可以寫成：

select * from news where publish_time >= '2017-01-02' and publish_time <= '2017-01-08' and enable = 1

這種情況下，因為范圍查詢對多列查詢的影響，將導致 news_publish_idx(publish_time, enable) 索引中 publish_time 右邊所有列都無法使用索引優化查找。換句話說，news_publish_idx(publish_time, enable) 索引等價于 news_publish_idx(publish_time) 。

對于這種情況，我的建議：對于范圍查詢，務必要注意它帶來的副作用，并且盡量少用范圍查詢，可以通過曲線救國的方式滿足業務場景。

例如，上面案例的需求是查詢本周發布的資訊文章，因此可以創建一個news_weekth 字段用來存儲資訊文章的周信息，使得范圍查詢變成普通的查詢，SQL 可以改寫成：

select * from news where news_weekth = 1 and enable = 1

然而，并不是所有的范圍查詢都可以進行改造，對于必須使用范圍查詢但無法改造的情況，我的建議：不必試圖用 SQL 來解決所有問題，可以使用其他數據存儲技術控制時間軸，例如 Redis 的 SortedSet 有序集合保存時間，或者通過緩存方式緩存查詢結果從而提高性能。

8.索引不會包含有NULL值的列 ?

只要列中包含有 NULL 值都將不會被包含在索引中，復合索引中只要有一列含有 NULL值，那么這一列對于此復合索引就是無效的。

因此，在數據庫設計時，除非有一個很特別的原因使用 NULL 值，不然盡量不要讓字段的默認值為 NULL。

9.隱式轉換的影響

當查詢條件左右兩側類型不匹配的時候會發生隱式轉換，隱式轉換帶來的影響就是可能導致索引失效而進行全表掃描。下面的案例中，date_str 是字符串，然而匹配的是整數類型，從而發生隱式轉換。

select * from news where date_str = 201701

因此，要謹記隱式轉換的危害，時刻注意通過同類型進行比較。

10.like 語句的索引失效問題

like 的方式進行查詢，在 like “value%” 可以使用索引，但是對于 like “%value%” 這樣的方式，執行全表查詢，這在數據量小的表，不存在性能問題，但是對于海量數據，全表掃描是非常可怕的事情。所以，根據業務需求，考慮使用 ElasticSearch 或 Solr 是個不錯的方案。

--為什么下列SQL不能命中phone索引？ select uid from user where phone=13811223344;

總結

SQL語句優化

避免嵌套查詢(子查詢)

避免多表查詢

索引優化

適當建立索引

合理使用索引

以上所講的索引的使用

數據庫結構優化

列,表,庫

系統配置優化

對mysql各項配置進行調優

服務器硬件優化

讀寫分離(主從復制)

【綻放吧！數據庫】有獎征文火熱進行中：https://bbs.huaweicloud.com/blogs/285617

MySQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。