MySQL優(yōu)化 - 性能分析與查詢優(yōu)化

    優(yōu)化應(yīng)貫穿整個(gè)產(chǎn)品開發(fā)周期中，比如編寫復(fù)雜SQL時(shí)查看執(zhí)行計(jì)劃，安裝MySQL服務(wù)器時(shí)盡量合理配置(見過太多完全使用默認(rèn)配置安裝的情況)，根據(jù)應(yīng)用負(fù)載選擇合理的硬件配置等。

創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比射洪網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式射洪網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋射洪地區(qū)。費(fèi)用合理售后完善，十多年實(shí)體公司更值得信賴。

1、性能分析

 性能分析包含多方面：CPU、Memory、磁盤/網(wǎng)絡(luò)IO、MySQL服務(wù)器本身等。

1.1 操作系統(tǒng)分析

常規(guī)的操作系統(tǒng)分析，在Linux中通常包含一些性能監(jiān)控命令，如top、vmstat、iostat、strace、iptraf等。

1、內(nèi)存：內(nèi)存是大項(xiàng)，高查詢消耗大量的查詢緩存，內(nèi)存必須足夠，并且給系統(tǒng)本身要預(yù)留一些。

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

3、網(wǎng)絡(luò)：目前市場上千兆萬兆網(wǎng)卡已很常見。

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

生產(chǎn)環(huán)境的MySQL多數(shù)情況部署在Linux系統(tǒng)中，Linux系統(tǒng)本身可以優(yōu)化的配置并不多。硬件的選型是復(fù)雜，涉及計(jì)算機(jī)組成的原理性知識，需要額外了解。

1.2 MySQL服務(wù)性能分析

MySQL服務(wù)器的性能通常通過監(jiān)控命令查看系統(tǒng)工作狀態(tài)，確定哪些因素成為瓶頸。

1.2.1  SHOW GLOBAL STATUS

顯示了目前MySQL的工作狀態(tài)，包含很多參數(shù)，下面對一些參數(shù)進(jìn)行說明，其余的參考官方說明：

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

1. Aborted_clients
如果該值隨時(shí)間增加，檢查是否優(yōu)雅關(guān)閉連接，檢查max_allowed_packet配置變量是否被超過導(dǎo)致強(qiáng)制中斷。

2. Aborted_connections
接近于0，檢查網(wǎng)絡(luò)問題，如果有少量是正常的，比如鑒權(quán)失敗等。

3. Binlog_cache_disk_use和Binlog_cache_use
大部分事務(wù)應(yīng)該在緩沖中進(jìn)行，如果disk cache很大，可考慮增加內(nèi)存緩存。

4. Bytes_recevied和Bytes_sent
如果值很大，檢查是否查詢超過需要的數(shù)據(jù)。

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

6. Create_tmp_tables
優(yōu)化查詢降低該值。

7. Handler_read_rnd_next
Handler_read_rnd_next / Handler_read_rnd顯示全表掃面大致平均值，如果很大，只能優(yōu)化查詢。

8. Open_files
不應(yīng)該接近于open_files_limit，如果接近就應(yīng)該適當(dāng)增加open_files_limit。

9. Qcache_*
查詢緩存相關(guān)。

10. Select_full_join
全聯(lián)接無索引聯(lián)接，盡量避免，優(yōu)化查詢。

11. Select_full_range_join
值過高說明使用了范圍查詢聯(lián)接表，范圍查詢比較慢，可優(yōu)化。

12. Sort_meger_passes
如果值較大可考慮增加sort_buffer_size，查明是那個(gè)查詢導(dǎo)致使用文件排序。

13. Table_locks_waited
表被鎖定導(dǎo)致服務(wù)器鎖等待，InnoDB的行鎖不會使得該變量增加，建議開啟慢查詢?nèi)罩尽?/p>

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

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

1.2.2  SHOW ENGINE INNODB STATUS

暫時(shí)的數(shù)據(jù)包含了太多InnoDB核心信息，并且需要比較深的了解InnoDB引擎工作原理，這里不做過多說明，請查閱針對此的專項(xiàng)文檔。

注: 通常包含SEMAPHORES、TRANSACTIONS、FILE I/O、LOG、BUFFER POOL AND MEMORY等一些詳細(xì)值，有些參數(shù)是上一次執(zhí)行以來的平均值，所以建議隔一段時(shí)間再打印一次得到這段時(shí)間的統(tǒng)計(jì)，有點(diǎn)類似iostat的統(tǒng)計(jì)磁盤平均讀寫一樣。

1.2.3  開啟慢查詢?nèi)罩九渲?/strong>

排查導(dǎo)致MySQL運(yùn)行緩慢的問題SQL，開啟慢查詢?nèi)罩九渲茫赡苡泻苡袔椭?/p>

slow_query_log=1
slow_query_log_file=/YOUR_DIR/mysql_slow.log

配合慢查詢?nèi)罩痉治龉ぞ?如mysqlsla)

2、查詢性能優(yōu)化

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

2.1 查詢基礎(chǔ)

了解查詢過程，才能知道哪些步驟可能出現(xiàn)瓶頸，execution plan結(jié)果也會有所體現(xiàn)，MySQL查詢的一般過程：

1. Client往服務(wù)器發(fā)送查詢指令。
2. 服務(wù)器查詢緩存，如果存在則直接返回，否則下一步。
3. 服務(wù)器解析、預(yù)處理和優(yōu)化查詢，生成執(zhí)行計(jì)劃。
4. 執(zhí)行引擎調(diào)用存儲引擎API執(zhí)行查詢。
5. 服務(wù)器將結(jié)果返回至客戶端。

用圖表示如下：

解析與預(yù)處理過程:

- 解析器將查詢分解后構(gòu)造解析樹，進(jìn)行語法解析與驗(yàn)證查詢，檢查SQL是否有效。

- 預(yù)處理器解析語義：如檢查表和列是否存在，是否存在歧義等。

- 預(yù)處理器檢查權(quán)限。

查詢優(yōu)化器:

該過程比較復(fù)雜，將解析樹的結(jié)果變成執(zhí)行計(jì)劃，優(yōu)化器的任務(wù)是尋找最好的方式(但并不是總能選擇最好的方案)，MySQL使用基于開銷的優(yōu)化器，預(yù)測不同執(zhí)行計(jì)劃的開銷。

- MySQL不考慮不受它控制的開銷，如用戶存儲過程與用戶自定義的函數(shù)

- 不考慮正在運(yùn)行的其他查詢

2.2 優(yōu)化數(shù)據(jù)訪問 (這一點(diǎn)很重要)

1. 應(yīng)用程序是否獲取超過需要的數(shù)據(jù)量？(PS: 多次遇到過查詢表所有數(shù)據(jù)然后再程序中只讀取10行之類的代碼)

2. MySQL 服務(wù)器是否分析了超過需要的行？數(shù)據(jù)是否沒有在存儲引擎層被過來掉？(Using index , Using where)

典型的錯(cuò)誤如下：

1. 提取超過需要的行，然后在程序中只要一部分 (應(yīng)該使用limit限制數(shù)據(jù)量）。

2. 多表join提取所有的列 (應(yīng)該只讀取需要的列)。

3. 提取所有的列（提取不需要的列可能導(dǎo)致優(yōu)化索引失效，增加磁盤IO，浪費(fèi)內(nèi)存等, 但如果是知道這個(gè)影響并利用查詢緩存，簡化設(shè)計(jì)等也是可以考慮的）。

訪問類型：

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

訪問速度以此遞增。

對于使用where語句來過濾數(shù)據(jù)的話，最好到最壞的情況是：

1. 對索引查找用where來消除不匹配的數(shù)據(jù)行，在存儲引擎層。

2. 使用覆蓋索引 (Extra 為Using Index) 來避免訪問行，取得索引數(shù)據(jù)后過濾行，發(fā)生在MySQL服務(wù)器層，但不需要讀取行數(shù)據(jù)。

3. 從表中查詢數(shù)據(jù)，然后過濾 (Using Where), 發(fā)生在服務(wù)器端并且要讀取行數(shù)據(jù)。

后面會針對執(zhí)行計(jì)劃結(jié)果做詳細(xì)介紹。

2.3 關(guān)于執(zhí)行計(jì)劃

執(zhí)行計(jì)劃結(jié)果樣例如下圖(也可用其他的可視化工具，如mysql workbench)：

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

這里說明一些比較重要的結(jié)果:

TYPE字段的值：

前面所說的訪問速度依次遞增就和這個(gè)有關(guān):

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

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

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

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

3、ref: 連接僅使用左索引或者索引不是PRIMARY或UNIQUE(或者說得到的不是一行的結(jié)果)，如果得到的幾行數(shù)據(jù)，這是個(gè)比較好的類型。

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

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

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

1. 如果索引是查詢的覆蓋索引，并滿足表中所需的所有數(shù)據(jù)，則僅掃描索引樹。 在這種情況下，Extra列為Using index。 僅索引掃描通常比ALL更快，因?yàn)樗饕拇笮⊥ǔＰ∮诒頂?shù)據(jù)。

2. 使用索引來執(zhí)行全表掃描，以按索引順序查找數(shù)據(jù)行。 在Extra列張則沒有Using index，這種情況與ALL的區(qū)別是ALL是按行掃描。

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

6、ALL: 全表掃描，比較糟糕 (但有時(shí)候數(shù)據(jù)比較少的情況下，MySQL會直接進(jìn)行全表掃描讀取數(shù)據(jù)，效率更高)。

2.4 優(yōu)化特定的查詢

    查詢優(yōu)化的一個(gè)辦法是遷移舊數(shù)據(jù)，騰出內(nèi)存空間重新平衡索引結(jié)構(gòu)，使得更快的查詢速度，很多應(yīng)用保留半年或三個(gè)月的數(shù)據(jù)都能滿足需求，對于舊數(shù)據(jù)，額外提供平臺訪問或者在應(yīng)用層做路由。

2.4.1 優(yōu)化COUNT (遇到過一知半解的使用，導(dǎo)致想優(yōu)化卻適得其反)

COUNT有兩種不同的工作方式：統(tǒng)計(jì)值的數(shù)量和統(tǒng)計(jì)行的數(shù)量。

值是一個(gè)非空(Non-NULL)的表達(dá)式(NULL則表示沒有值)，如果在COUNT()中定義了列名或其他表達(dá)式，COUNT則會統(tǒng)計(jì)這個(gè)表達(dá)式有值(Non-NULL)的次數(shù)。

COUNT另外一種工作方式就是統(tǒng)計(jì)行數(shù)，當(dāng)MySQL知道括號中的表達(dá)式不會為NULL的時(shí)候，則使用這種方式，COUNT(*)是個(gè)例子，它不會展開成所有列，則是忽略所以的列并統(tǒng)計(jì)。

2.4.2 優(yōu)化limit和offset

偏移量很大的查詢代價(jià)很高，如LIMIT 10000, 10, 則會產(chǎn)生10010數(shù)據(jù)，然后只截取10行。解決辦法：

1. 限制分頁能讀取的數(shù)據(jù)頁數(shù)。

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

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