MySQL優(yōu)化框架

1. SQL語句優(yōu)化

2. 索引優(yōu)化

3. 數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化

4. InnoDB表優(yōu)化

5. MyISAM表優(yōu)化

6. Memory表優(yōu)化

7. 理解查詢執(zhí)行計劃

8. 緩沖和緩存

9. 鎖優(yōu)化

10. MySQL服務(wù)器優(yōu)化

11. 性能評估

12. MySQL優(yōu)化內(nèi)幕

MySQL優(yōu)化需要在三個不同層次上協(xié)調(diào)進(jìn)行：MySQL級別、OS級別和硬件級別。MySQL級別的優(yōu)化包括表優(yōu)化、查詢優(yōu)化和MySQL服務(wù)器配置優(yōu)化等，而MySQL的各種數(shù)據(jù)結(jié)構(gòu)又最終作用于OS直至硬件設(shè)備，因此還需要了解每種結(jié)構(gòu)對OS級別的資源的需要并最終導(dǎo)致的CPU和I/O操作等，并在此基礎(chǔ)上將CPU及I/O操作需要盡量降低以提升其效率。

數(shù)據(jù)庫層面的優(yōu)化著眼點(diǎn)：

1、是否正確設(shè)定了表結(jié)構(gòu)的相關(guān)屬性，尤其是每個字段的字段類型是否為最佳。同時，是否為特定類型的工作組織使用了合適的表及表字段也將影響系統(tǒng)性能，比如，數(shù)據(jù)頻繁更新的場景應(yīng)該使用較多的表而每張表有著較少字段的結(jié)構(gòu)，而復(fù)雜數(shù)據(jù)查詢或分析的場景應(yīng)該使用較少的表而每張表較多字段的結(jié)構(gòu)等。

2、是否為高效進(jìn)行查詢創(chuàng)建了合適的索引。

3、是否為每張表選用了合適的存儲引擎，并有效利用了選用的存儲引擎本身的優(yōu)勢和特性。

4、是否基于存儲引擎為表選用了合適的行格式(row format)。例如，壓縮表在讀寫操作中會降低I/O操作需求并占用較少的磁盤空間，InnoDB支持讀寫應(yīng)用場景中使用壓縮表，但MyISAM僅能在讀環(huán)境中使用壓縮表。

5、是否使用了合適的鎖策略，如在并發(fā)操作場景中使用共享鎖，而對較高優(yōu)先級的需求使用獨(dú)占鎖等。同時，還應(yīng)該考慮存儲引擎所支持的鎖類型。

6、是否為InnoDB的緩沖池、MyISAM的鍵緩存以及MySQL查詢緩存設(shè)定了合適大小的內(nèi)存空間，以便能夠存儲頻繁訪問的數(shù)據(jù)且又不會引起頁面換出。

操作系統(tǒng)和硬件級別的優(yōu)化著眼點(diǎn)：

1、是否為實際的工作負(fù)載選定了合適的CPU，如對于CPU密集型的應(yīng)用場景要使用更快速度的CPU甚至更多數(shù)量的CPU，為有著更多查詢的場景使用更多的CPU等?；诙嗪艘约俺€程(hyperthreading)技術(shù)，現(xiàn)代的CPU架構(gòu)越來越復(fù)雜、性能也越來越強(qiáng)了，但MySQL對多CPU架構(gòu)的并行計算能力的利用仍然是有著不太盡如人意之處，尤其是較老的版本如MySQL 5.1之前的版本甚至無法發(fā)揮多CPU的優(yōu)勢。不過，通常需要實現(xiàn)的CPU性能提升目標(biāo)有兩類：低遲延和高吞吐量。低延遲需要更快速度的CPU，因為單個查詢只能使用一顆；而需要同時運(yùn)行許多查詢的場景，多CPU更能提供更好的吞吐能力，然而其能否奏效還依賴于實際工作場景，因為MySQL尚不能高效的運(yùn)行于多CPU，并且其對CPU數(shù)量的支持也有著限制。一般來說，較新的版本可以支持16至24顆CPU甚至更多。

2、是否有著合適大小的物理內(nèi)存，并通過合理的配置平衡內(nèi)存和磁盤資源，降低甚至避免磁盤I/O?，F(xiàn)代的程序設(shè)計為提高性能通常都會基于局部性原理使用到緩存技術(shù)，這對于頻繁操作數(shù)據(jù)的數(shù)據(jù)庫系統(tǒng)來說尤其如此——有著良好設(shè)計的數(shù)據(jù)庫緩存通常比針對通用任務(wù)的操作系統(tǒng)的緩存效率更高。緩存可以有效地延遲寫入、優(yōu)化寫入，但并能消除寫入，并綜合考慮存儲空間的可擴(kuò)展性等，為業(yè)務(wù)選擇合理的外部存儲設(shè)備也是非常重要的工作。

3、是否選擇了合適的網(wǎng)絡(luò)設(shè)備并正確地配置了網(wǎng)絡(luò)對整體系統(tǒng)系統(tǒng)也有著重大影響。延遲和帶寬是網(wǎng)絡(luò)連接的限制性因素，而常見的網(wǎng)絡(luò)問題如丟包等，即是很小的丟包率也會贊成性能的顯著下降。而更重要的還有按需調(diào)整系統(tǒng)中關(guān)網(wǎng)絡(luò)方面的設(shè)置，以高效處理大量的連接和小查詢。

4、是否基于操作系統(tǒng)選擇了適用的文件系統(tǒng)。實際測試表明大部分文件系統(tǒng)的性能都非常接近，因此，為了性能而苦選文件系統(tǒng)并不劃算。但考慮到文件系統(tǒng)的修復(fù)能力，應(yīng)該使用日志文件系統(tǒng)如ext3、ext4、XFS等。同時，關(guān)閉文件系統(tǒng)的某些特性如訪問時間和預(yù)讀行為，并選擇合理的磁盤調(diào)度器通常都會給性能提升帶來幫助。

5、MySQL為響應(yīng)每個用戶連接使用一個單獨(dú)的線程，再加內(nèi)部使用的線程、特殊目的線程以及其它任何由存儲引擎創(chuàng)建的線程等，MySQL需要對這些大量線程進(jìn)行有效管理。Linux系統(tǒng)上的NPTL線程庫更為輕量級也更有效率。MySQL 5.5引入了線程池插件，但其效用尚不明朗。

使用InnoDB存儲引擎最佳實踐：

1、基于MySQL查詢語句中最常用的字段或字段組合創(chuàng)建主鍵，如果沒有合適的主鍵也最好使用AUTO_INCRMENT類型的某字段為主鍵。

2、根據(jù)需要考慮使用多表查詢，將這些表通過外鍵建立約束關(guān)系。

3、關(guān)閉autocommit。

4、使用事務(wù)(START TRANSACTION和COMMIT語句)組合相關(guān)的修改操作或一個整體的工作單元，當(dāng)然也不應(yīng)該創(chuàng)建過大的執(zhí)行單元。

5、停止使用LOCK TABLES語句，InnoDB可以高效地處理來自多個會話的并發(fā)讀寫請求。如果需要在一系列的行上獲取獨(dú)占訪問權(quán)限，可以使用SELECT ... FOR UPDATE鎖定僅需要更新的行。

6、啟用innodb_file_per_table選項，將各表的數(shù)據(jù)和索引分別進(jìn)行存放。

7、評估數(shù)據(jù)和訪問模式是否能從InnoDB的表壓縮功能中受益(在創(chuàng)建表時使用ROW_FORMAT=COMPRESSED選項)，如果可以，則應(yīng)該啟用壓縮功能。

EXPLAIN語句解析：

id:SELECT語句的標(biāo)識符，一般為數(shù)字，表示對應(yīng)的SELECT語句在原始語句中的位置。沒有子查詢或聯(lián)合的整個查詢只有一個SELECT語句，因此其id通常為1。在聯(lián)合或子查詢語句中，內(nèi)層的SELECT語句通常按它們在原始語句中的次序進(jìn)行編號。但UNION操作通常最后會有一個id為NULL的行，因為UNION的結(jié)果通常保存至臨時表中，而MySQL需要到此臨時表中取得結(jié)果。

select_type:

即SELECT類型，有如下值列表：

SIMPLE：簡單查詢，即沒有使用聯(lián)合或子查詢；

PRIMARY：UNION的最外圍的查詢或者最先進(jìn)行的查詢；

UNION：相對于PRIMARY，為聯(lián)合查詢的第二個及以后的查詢；

DEPENDENT UNION：與UNION相同，但其位于聯(lián)合子查詢中(即UNION查詢本身是子查詢)；

UNION RESULT：UNION的執(zhí)行結(jié)果；

SUBQUERY：非從屬子查詢，優(yōu)化器通常認(rèn)為其只需要運(yùn)行一次；

DEPENDENT SUBQUERY：從屬子查詢，優(yōu)化器認(rèn)為需要為外圍的查詢的每一行運(yùn)行一次，如用于IN操作符中的子查詢；

DERIVED：用于FROM子句的子查詢，即派生表查詢；

table:

輸出信息所關(guān)系到的表的表名，也有可能會顯示為如下格式：

<unionM,N>：id為M和N的查詢執(zhí)行聯(lián)合查詢后的結(jié)果；

<derivedN>：id為N的查詢執(zhí)行的結(jié)果集；

type:

MySQL官方手冊中解釋type的作用為“type of join(聯(lián)結(jié)的類型)”，但其更確切的意思應(yīng)該是“記錄(record)訪問類型”，因為其主要目的在于展示MySQL在表中找到所需行的方式。通常有如下所示的記錄訪問類型：

system: 表中僅有一行，是const類型的一種特殊情況；

const：表中至多有一個匹配的行，該行僅在查詢開始時讀取一次，因此，該行此字段中的值可以被優(yōu)化器看作是個常量(constant)；當(dāng)基于PRIMARY KEY或UNIQUE NOT NULL字段查詢，且與某常量進(jìn)行等值比較時其類型就為const，其執(zhí)行速度非常快；

eq_ref：類似于const，表中至多有一個匹配的行，但比較的數(shù)值不是某常量，而是來自于其它表；ed_ref出現(xiàn)在PRIMARY KEY或UNIQUE NOT NULL類型的索引完全用于聯(lián)結(jié)操作中進(jìn)行等值(=)比較時；這是除了system和const之外最好的訪問類型；

ref：查詢時的索引類型不是PRIMARY KEY或UNIQUE NOT NULL導(dǎo)致匹配到的行可能不惟一，或者僅能用到索引的左前綴而非全部時的訪問類型；ref可被用于基于索引的字段進(jìn)行=或<=>操作；

fulltext：用于FULLTEXT索引中用純文本匹配的方法來檢索記錄。

ref_or_null：類似于ref，但可以額外搜索NULL值；

index_merge：使用“索引合并優(yōu)化”的記錄訪問類型，相應(yīng)地，其key字段(EXPLAIN的輸出結(jié)果)中會出現(xiàn)用到的多個索引，key_len字段中會出現(xiàn)被使用索引的最長長度列表；將多個“范圍掃描(range scan)”獲取到的行進(jìn)行合并成一個結(jié)果集的操作即索引合并(index merge)。

unique_subquery：用于IN比較操作符中的子查詢中進(jìn)行的“鍵值惟一”的訪問類型場景中，如 value IN (SELECT primary_key FROM single_table WHERE some_expr)；

index_subquery：類似于unique_subquery，但子查詢中鍵值不惟一；

range：帶有范圍限制的索引掃描，而非全索引掃描，它開始于索引里的某一點(diǎn)，返回匹配那個值的范圍的行；相應(yīng)地，其key字段(EXPLAIN的輸出結(jié)果)中會輸出所用到的索引，key_len字段中會包含用到的索引的最長部分的長度；range通常用于將索引與常量進(jìn)行=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或IN()類的比較操作中；

index：同全表掃描(ALL)，只不過是按照索引的次序進(jìn)行而不行的次序；其優(yōu)點(diǎn)是避免了排序，但是要承擔(dān)按索引次序讀取整個表的開銷，這意味著若是按隨機(jī)次序訪問行，代價將非常大；

ALL：“全表掃描”的方式查找所需要的行，如果第一張表的查詢類型(EXPLAIN的輸出結(jié)果)為const，其性能可能不算太壞，而第一張表的查詢類型為其它結(jié)果時，其性能通常會非常差；

Extra:

Using where：MySQL服務(wù)器將在存儲引擎收到數(shù)據(jù)后進(jìn)行“后過濾(post-filter)”以限定發(fā)送給下張表或客戶端的行；如果WHERE條件中使用了索引列，其讀取索引時就由存儲引擎檢查，因此，并非所有帶有WHERE子句的查詢都會顯示“Using where”；

Using index：表示所需要的數(shù)據(jù)從索引就能夠全部獲取到，從而不再需要從表中查詢獲取所需要數(shù)據(jù)，這意味著MySQL將使用覆蓋索引；但如果同時還出現(xiàn)了Using where，則表示索引將被用于查找特定的鍵值；

Using index for group-by：類似于Using index，它表示MySQL可僅通過索引中的數(shù)據(jù)完成GROUP BY或DISTINCT類的查詢；

Using filesort：表示MySQL會對結(jié)果使用一個外部索引排序，而不是從表里按索引次序來讀取行。