MySQL最佳化 - 效能分析與查詢最佳化

標籤：mysql最佳化 - 效能分析與查詢最佳化

MySQL最佳化 - 效能分析與查詢最佳化

    最佳化應貫穿整個產品開發週期中，比如編寫複雜SQL時查看執行計畫，安裝MySQL伺服器時盡量合理配置(見過太多完全使用預設配置安裝的情況)，根據應用負載選擇合理的硬體設定等。

 

1、效能分析

 效能分析包含多方面：CPU、Memory、磁碟/網路IO、MySQL伺服器本身等。

1.1 作業系統分析

常規的作業系統分析，在Linux中通常包含一些效能監控命令，如top、vmstat、iostat、strace、iptraf等。

1、記憶體：記憶體是大項，高查詢消耗大量的查詢快取，記憶體必須足夠，並且給系統本身要預留一些。

2、磁碟：配備高速磁碟+RAID會有更好的讀寫速度，並且SSD成本逐漸降低，升級成本會在可接受範圍。

3、網路：目前市場上千兆萬兆網卡已很常見。

4、CPU：雖然很多情況下CPU用不完，但也不能讓它成為瓶頸。

生產環境的MySQL多數情況部署在Linux系統中，Linux系統本身可以最佳化的配置並不多。硬體的選型是複雜，涉及電腦群組成的原理性知識，需要額外瞭解。

 

1.2 MySQL服務效能分析

MySQL伺服器的效能通常通過監控命令查看系統工作狀態，確定哪些因素成為瓶頸。

1.2.1  SHOW GLOBAL STATUS

顯示了目前MySQL的工作狀態，包含很多參數，下面對一些參數進行說明，其餘的參考官方說明：

====================================

1. Aborted_clients
如果該值隨時間增加，檢查是否優雅關閉串連，檢查max_allowed_packet組態變數是否被超過導致強制中斷。

2. Aborted_connections
接近於0，檢查網路問題，如果有少量是正常的，比如鑒權失敗等。

3. Binlog_cache_disk_use和Binlog_cache_use
大部分事務應該在緩衝中進行，如果disk cache很大，可考慮增加記憶體緩衝。

4. Bytes_recevied和Bytes_sent
如果值很大，檢查是否查詢超過需要的資料。

5. Com_*
盡量讓如Com_rollback這些不常見的變數超過預期，用innotop檢查。

6. Create_tmp_tables
最佳化查詢降低該值。

7. Handler_read_rnd_next
Handler_read_rnd_next / Handler_read_rnd顯示全表掃面大致平均值，如果很大，只能最佳化查詢。

8. Open_files
不應該接近於open_files_limit，如果接近就應該適當增加open_files_limit。

9. Qcache_*
查詢快取相關。

10. Select_full_join
全聯結無索引聯結，盡量避免，最佳化查詢。

11. Select_full_range_join
值過高說明使用了範圍查詢聯結表，範圍查詢比較慢，可最佳化。

12. Sort_meger_passes
如果值較大可考慮增加sort_buffer_size，查明是那個查詢導致使用檔案排序。

13. Table_locks_waited
表被鎖定導致伺服器鎖等待，InnoDB的行鎖不會使得該變數增加，建議開啟慢查詢日誌。

14. Threads_created
如果值在增加，可考慮增加thread_cache_size。

====================================

 

1.2.2  SHOW ENGINE INNODB STATUS

暫時的資料包含了太多InnoDB核心資訊，並且需要比較深的瞭解InnoDB引擎工作原理，這裡不做過多說明，請查閱針對此的專項文檔。

注: 通常包含SEMAPHORES、TRANSACTIONS、FILE I/O、LOG、BUFFER POOL AND MEMORY等一些詳細值，有些參數是上一次執行以來的平均值，所以建議隔一段時間再列印一次得到這段時間的統計，有點類似iostat的統計磁碟平均讀寫一樣。

 

1.2.3  開啟慢查詢日誌配置

排查導致MySQL運行緩慢的問題SQL，開啟慢查詢日誌配置，可能有很有協助：

slow_query_log=1slow_query_log_file=/YOUR_DIR/mysql_slow.log

配合慢查詢日誌分析工具(如mysqlsla)

 

 

2、查詢效能最佳化

    一般來說在編寫SQL時，注意查詢是否能使用到索引，是否在大表中或者高頻率查詢中引起全表掃描，這些主要通過經驗分析配合execution plan得到比較理想的查詢消耗。

 

2.1 查詢基礎

瞭解查詢過程，才能知道哪些步驟可能出現瓶頸，execution plan結果也會有所體現，MySQL查詢的一般過程：

1. Client往伺服器發送查詢指令。
2. 伺服器查詢快取，如果存在則直接返回，否則下一步。
3. 伺服器解析、預先處理和最佳化查詢，產生執行計畫。
4. 執行引擎調用儲存引擎API執行查詢。
5. 伺服器將結果返回至用戶端。

用圖表示如下：

650) this.width=650;" src="http://images2017.cnblogs.com/blog/422439/201708/422439-20170802234006178-1287445185.jpg" style="border:0px;" />

 

解析與預先處理過程:

- 解析器將查詢分解後構造解析樹，進行文法解析與驗證查詢，檢查SQL是否有效。

- 前置處理器解析語義：如檢查表和列是否存在，是否存在歧義等。

- 前置處理器檢查許可權。

查詢最佳化工具:

該過程比較複雜，將解析樹的結果變成執行計畫，最佳化器的任務是尋找最好的方式(但並不是總能選擇最好的方案)，MySQL使用基於開銷的最佳化器，預測不同執行計畫的開銷。

- MySQL不考慮不受它控制的開銷，如使用者預存程序與使用者自訂的函數

- 不考慮正在啟動並執行其他查詢

 

2.2 最佳化資料訪問 (這一點很重要)

1. 應用程式是否擷取超過需要的資料量？(PS: 多次遇到過查詢表所有資料然後再程式中唯讀取10行之類的代碼)

2. MySQL 伺服器是否分析了超過需要的行？資料是否沒有在儲存引擎層被過來掉？(Using index , Using where)

 

典型的錯誤如下：

1. 提取超過需要的行，然後在程式中只要一部分 (應該使用limit限制資料量）。

2. 多表join提取所有的列 (應該唯讀取需要的列)。

3. 提取所有的列（提取不需要的列可能導致最佳化索引失效，增加磁碟IO，浪費記憶體等, 但如果是知道這個影響並利用查詢快取，簡化設計等也是可以考慮的）。

 

訪問類型：

Full Table Scan > Index Scan > Range Scan > Unique Index Lookup > Constant.

訪問速度以此遞增。

對於使用where語句來過濾資料的話，最好到最壞的情況是：

1. 對索引尋找用where來消除不匹配的資料行，在儲存引擎層。

2. 使用覆蓋索引 (Extra 為Using Index) 來避免訪問行，取得索引資料後過濾行，發生在MySQL伺服器層，但不需要讀取行資料。

3. 從表中查詢資料，然後過濾 (Using Where), 發生在伺服器端並且要讀取行資料。

後面會針對執行計畫結果做詳細介紹。

 

 

2.3 關於執行計畫

執行計畫結果範例如(也可用其他的視覺化檢視，如mysql workbench)：

650) this.width=650;" src="http://images2017.cnblogs.com/blog/422439/201708/422439-20170803001739990-422717760.jpg" style="border:0px;" />

650) this.width=650;" src="http://images2017.cnblogs.com/blog/422439/201708/422439-20170803001747819-930242748.jpg" style="border:0px;" />

 

所代表的含義可在官方文檔中找到詳細說明 ( https://dev.mysql.com/doc/refman/5.5/en/explain-output.html )，

這裡說明一些比較重要的結果:

TYPE欄位的值：

前面所說的訪問速度依次遞增就和這個有關:

Full Table Scan > Index Scan > Range Scan > Unique Index Lookup > Constant.

這裡列出一些常見的說明：

1、const:  最多匹配一行, 如 SELECT * FROM rental where rental_id=1。

2、eq_ref: 讀取的行依次匹配前一個表。

3、ref: 串連僅使用左索引或者索引不是PRIMARY或UNIQUE(或者說得到的不是一行的結果)，如果得到的幾行資料，這是個比較好的類型。

4、range:  使用索引的範圍掃描，如使用了 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()等條件。

5、index: 除了索引樹被掃描之外，索引連線類型與ALL相同。這有兩種方式：

**************

1. 如果索引是查詢的覆蓋索引，並滿足表中所需的所有資料，則僅掃描索引樹。 在這種情況下，Extra列為Using index。 僅索引掃描通常比ALL更快，因為索引的大小通常小於表資料。

2. 使用索引來執行全表掃描，以按索引順序尋找資料行。 在Extra列張則沒有Using index，這種情況與ALL的區別是ALL是按行掃描。

**************

6、ALL: 全表掃描，比較糟糕 (但有時候資料比較少的情況下，MySQL會直接進行全表掃描讀取資料，效率更高)。

 

 

2.4 最佳化特定的查詢

    查詢最佳化的一個辦法是遷移舊資料，騰出記憶體空間重新平衡索引結構，使得更快的查詢速度，很多應用保留半年或三個月的資料都能滿足需求，對於舊資料，額外提供平台訪問或者在應用程式層做路由。

2.4.1 最佳化COUNT (遇到過一知半解的使用，導致想最佳化卻適得其反)

COUNT有兩種不同的工作方式：統計值的數量和統計行的數量。

值是一個非空(Non-NULL)的運算式(NULL則表示沒有值)，如果在COUNT()中定義了列名或其他運算式，COUNT則會統計這個運算式有值(Non-NULL)的次數。

COUNT另外一種工作方式就是統計行數，當MySQL知道括弧中的運算式不會為NULL的時候，則使用這種方式，COUNT(*)是個例子，它不會展開成所有列，則是忽略所以的列並統計。

 

2.4.2 最佳化limit和offset

位移量很大的查詢代價很高，如LIMIT 10000, 10, 則會產生10010資料，然後只截取10行。解決辦法：

1. 限制分頁能讀取的資料頁數。

2. 可考慮使用覆蓋索引，如 select id, name, description from book limit 100,10;

在ID上有索引改進為：select id, name, description from book  inner join (select id from book limit 100, 10) as b;

MySQL最佳化 - 效能分析與查詢最佳化