|
優(yōu)化應(yīng)貫穿整個(gè)產(chǎn)品開發(fā)周期中，比如編寫復(fù)雜SQL時(shí)查看執(zhí)行計(jì)劃，安裝MySQL服務(wù)器時(shí)盡量合理配置(見過太多完全使用默認(rèn)配置安裝的情況)，根據(jù)應(yīng)用負(fù)載選擇合理的硬件配置等。
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會(huì)有更好的讀寫速度，并且SSD成本逐漸降低，升級(jí)成本會(huì)在可接受范圍。 3、網(wǎng)絡(luò)：目前市場(chǎng)上千兆萬(wàn)兆網(wǎng)卡已很常見。 4、CPU：雖然很多情況下CPU用不完，但也不能讓它成為瓶頸。 生產(chǎn)環(huán)境的MySQL多數(shù)情況部署在Linux系統(tǒng)中，Linux系統(tǒng)本身可以優(yōu)化的配置并不多。硬件的選型是復(fù)雜，涉及計(jì)算機(jī)組成的原理性知識(shí)，需要額外了解。
1.2 MySQL服務(wù)性能分析 MySQL服務(wù)器的性能通常通過監(jiān)控命令查看系統(tǒng)工作狀態(tài)，確定哪些因素成為瓶頸。 1.2.1 SHOW GLOBAL STATUS 顯示了目前MySQL的工作狀態(tài)，包含很多參數(shù)，下面對(duì)一些參數(shù)進(jìn)行說(shuō)明，其余的參考官方說(shuō)明： ==================================== 1. Aborted_clients 2. Aborted_connections 3. Binlog_cache_disk_use和Binlog_cache_use 4. Bytes_recevied和Bytes_sent 5. Com_* 6. Create_tmp_tables 7. Handler_read_rnd_next 8. Open_files 9. Qcache_* 10. Select_full_join 11. Select_full_range_join 12. Sort_meger_passes 13. Table_locks_waited 14. Threads_created ====================================
1.2.2 SHOW ENGINE INNODB STATUS 暫時(shí)的數(shù)據(jù)包含了太多InnoDB核心信息，并且需要比較深的了解InnoDB引擎工作原理，這里不做過多說(shuō)明，請(qǐng)查閱針對(duì)此的專項(xiàng)文檔。 注: 通常包含SEMAPHORES、TRANSACTIONS、FILE I/O、LOG、BUFFER POOL AND MEMORY等一些詳細(xì)值，有些參數(shù)是上一次執(zhí)行以來(lái)的平均值，所以建議隔一段時(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)化 一般來(lái)說(shuō)在編寫SQL時(shí)，注意查詢是否能使用到索引，是否在大表中或者高頻率查詢中引起全表掃描，這些主要通過經(jīng)驗(yàn)分析配合execution plan得到比較理想的查詢消耗。
2.1 查詢基礎(chǔ) 了解查詢過程，才能知道哪些步驟可能出現(xiàn)瓶頸，execution plan結(jié)果也會(huì)有所體現(xiàn)，MySQL查詢的一般過程： 1. Client往服務(wù)器發(fā)送查詢指令。 用圖表示如下：
解析與預(yù)處理過程: - 解析器將查詢分解后構(gòu)造解析樹，進(jìn)行語(yǔ)法解析與驗(yàn)證查詢，檢查SQL是否有效。 - 預(yù)處理器解析語(yǔ)義：如檢查表和列是否存在，是否存在歧義等。 - 預(yù)處理器檢查權(quán)限。 查詢優(yōu)化器: 該過程比較復(fù)雜，將解析樹的結(jié)果變成執(zhí)行計(jì)劃，優(yōu)化器的任務(wù)是尋找最好的方式(但并不是總能選擇最好的方案)，MySQL使用基于開銷的優(yōu)化器，預(yù)測(cè)不同執(zhí)行計(jì)劃的開銷。 - MySQL不考慮不受它控制的開銷，如用戶存儲(chǔ)過程與用戶自定義的函數(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ù)是否沒有在存儲(chǔ)引擎層被過來(lái)掉？(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è)影響并利用查詢緩存，簡(jiǎn)化設(shè)計(jì)等也是可以考慮的）。
訪問類型： Full Table Scan > Index Scan > Range Scan > Unique Index Lookup > Constant. 訪問速度以此遞增。 對(duì)于使用where語(yǔ)句來(lái)過濾數(shù)據(jù)的話，最好到最壞的情況是： 1. 對(duì)索引查找用where來(lái)消除不匹配的數(shù)據(jù)行，在存儲(chǔ)引擎層。 2. 使用覆蓋索引 (Extra 為Using Index) 來(lái)避免訪問行，取得索引數(shù)據(jù)后過濾行，發(fā)生在MySQL服務(wù)器層，但不需要讀取行數(shù)據(jù)。 3. 從表中查詢數(shù)據(jù)，然后過濾 (Using Where), 發(fā)生在服務(wù)器端并且要讀取行數(shù)據(jù)。 后面會(huì)針對(duì)執(zhí)行計(jì)劃結(jié)果做詳細(xì)介紹。
2.3 關(guān)于執(zhí)行計(jì)劃 執(zhí)行計(jì)劃結(jié)果樣例如下圖(也可用其他的可視化工具，如mysql workbench)：
所代表的含義可在官方文檔中找到詳細(xì)說(shuō)明 ( https://dev./doc/refman/5.5/en/explain-output.html )， 這里說(shuō)明一些比較重要的結(jié)果: TYPE字段的值： 前面所說(shuō)的訪問速度依次遞增就和這個(gè)有關(guān): Full Table Scan > Index Scan > Range Scan > Unique Index Lookup > Constant. 這里列出一些常見的說(shuō)明： 1、const: 最多匹配一行, 如 SELECT * FROM rental where rental_id=1。 2、eq_ref: 讀取的行依次匹配前一個(gè)表。 3、ref: 連接僅使用左索引或者索引不是PRIMARY或UNIQUE(或者說(shuō)得到的不是一行的結(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. 使用索引來(lái)執(zhí)行全表掃描，以按索引順序查找數(shù)據(jù)行。 在Extra列張則沒有Using index，這種情況與ALL的區(qū)別是ALL是按行掃描。 ************** 6、ALL: 全表掃描，比較糟糕 (但有時(shí)候數(shù)據(jù)比較少的情況下，MySQL會(huì)直接進(jìn)行全表掃描讀取數(shù)據(jù)，效率更高)。
2.4 優(yōu)化特定的查詢 查詢優(yōu)化的一個(gè)辦法是遷移舊數(shù)據(jù)，騰出內(nèi)存空間重新平衡索引結(jié)構(gòu)，使得更快的查詢速度，很多應(yīng)用保留半年或三個(gè)月的數(shù)據(jù)都能滿足需求，對(duì)于舊數(shù)據(jù)，額外提供平臺(tái)訪問或者在應(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則會(huì)統(tǒng)計(jì)這個(gè)表達(dá)式有值(Non-NULL)的次數(shù)。 COUNT另外一種工作方式就是統(tǒng)計(jì)行數(shù)，當(dāng)MySQL知道括號(hào)中的表達(dá)式不會(huì)為NULL的時(shí)候，則使用這種方式，COUNT(*)是個(gè)例子，它不會(huì)展開成所有列，則是忽略所以的列并統(tǒng)計(jì)。
2.4.2 優(yōu)化limit和offset 偏移量很大的查詢代價(jià)很高，如LIMIT 10000, 10, 則會(huì)產(chǎn)生10010數(shù)據(jù)，然后只截取10行。解決辦法： 1. 限制分頁(yè)能讀取的數(shù)據(jù)頁(yè)數(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;
|
|
|
