MySQL高級特性
1. 分區表:分區表是一種粗粒度的、簡易的索引策略,適用於大數據量的過濾場景。最適合的場景是,在沒有合適的索引時,對幾個分區進行全表掃描,或者是只有一個分區和索引是熱點,而且這個分區和索引
能夠在內存中;限制單表分區數不要超過150個,並且注意某些導致無法分區過濾的細節,分區表對單條記錄的查詢並沒有什麼優勢,需要注意這類查詢的性能。
1). 對於用戶來說,分區表是一個獨立的邏輯表,但是底層由多個物理子表組成。實現分區的代碼實際上是對一組底層表的句柄對象(Handler)的封裝。
2). MySQL實現分區的方式--對底層表的封裝--意味著索引也是按照分區的子表定義的,而沒有全局索引。
3). MySQL在創建表的時候使用PARTITION BY子句定義每個分區存放的數據。在執行查詢的時候,優化器會根據分區過濾那些沒有我們需要的數據的分區,這樣查詢就無法掃描所有分區--只要查找包含
需要數據的分區就可以了。
4). 下面場景中分區可以起到很大的作用:
a. 表非常大以至於無法全部放到內存中,或者只在表的最後部分有熱點數據,其他均是歷史數據。
b. 分區表的數據更容易維護。
c. 分區表的數據可以分布在不同的物理設備上,從而高效的利用多個硬件設備。
e. 可以使用分區表來避免某些特殊的瓶頸,例如InnoDB的單個索引的互斥訪問、ext3文件系統的inode鎖競爭。
f. 如果需要,還可以備份和恢復獨立的分區,這在非常大的數據集的場景下效果非常好。
5). 分區本身也有一些限制,下面是其中比較重要的幾點:
a. 一個表最多只有1024個分區
b. 在MySQL5.1中,分區表達式必須是整數或者返回整數表達式。在MySQL5.5中,某些場景可以直接使用列進行分區。
c. 如果分區字段中有主鍵或者唯一索引的列,那麼所有主鍵列和唯一索引列都必須包含進來。
d. 分區中無法使用外鍵約束。
6). 為了保證大數據量的可擴展性,一般有下面兩個策略:
a. 全量掃描數據,不要任何索引:可以使用簡單的分區方式存放表,不要任何索引,根據分區的規則大致定位需要的數據位置。只要能夠使用WHERE條件,將需要的數據限制在少數分區中,則效率是很高的。
b. 索引數據,並分離熱點:如果數據有明顯的"熱點",而除了這部分數據,其他數據都很少被訪問到,那麼可以將這部分熱點數據單獨放在一個分區中,讓這個分區的數據能夠有機會都緩存在內存中。這樣查詢
就可以訪問一個很小的分區表,能夠使用索引,也能夠有效地使用緩存。
2. 視圖:對於好幾個表的復雜查詢,使用視圖有時候會大大簡化問題。當視圖使用臨時表時,無法將WHERE條件下推到各個具體的表中,也不能使用任何索引,需要特別注意這列查詢的性能。如果為了便利,
使用視圖是很合適的。
3. 外鍵:外鍵限制會將約束放到MySQL中,這對於必須維護外鍵的場景,性能會更高。不過這也會帶來額外的復雜性和額外的索引消耗,還會增加多表之間的交互,會導致系統中更多的鎖和競爭。外鍵可以被看
作是一個確保系統完整性的額外的特性,但是如果設計的是一個高性能的系統,那麼外鍵就顯得很臃腫了。很多人在更在意系統的性能的時候都不會使用外鍵,而是通過應用程序來維護的。
4. 存儲過程:MySQL本身實現了存儲過程、觸發器、存儲函數和事件,老實說,這些特性並沒什麼特別的。而且對於基於語句的復制還有很多問題。通常,使用這些特性可以幫你節省很多網絡開銷--很多情況下,
減少網路開銷可以大大提升系統的性能。在某些經典的場景下,你可以使用這些特性(例如中心化業務邏輯、繞過全線系統等),但需要注意在MySQL中,這些特性並沒有別的數據庫系統那麼成熟和全面。
5. 綁定變量:當查詢語句的解析和執行計劃生成消耗了主要時間,那麼綁定變量可以在一定程度上解決問題。因為只需要解析一次,對於大量重復類型的查詢語句,性能會有很大的提高。另外,執行計劃的緩存
和傳輸使用的二進制協議,這都使得綁定變量的方式比普通SQL語句執行的方式要更快。
6. 插件:使用C或者C++編寫的插件可以讓你最大程度的擴展MySQL功能。插件功能非常強大。
7. 字符集:字符集是一種字節到字符之間的映射,而校對規則是一個字符集的排序方法。很多人使用Latin1(默認字符集,對英語和某些歐洲語言有效)或者UTF-8。如果使用的是UTF-8,那麼在使用臨時表和緩沖區
的時候需要注意:MySQL會按照每個字符三個字節的最大占用空間來分配存儲空間,這可能消耗更多的內存或者磁盤空間。注意讓字符集和MySQL字符集配置相符,否則可能會由於字符集轉換讓某些索引無法使用。
8. 全文索引:在5.6版本之前只有MyISAM支持全文索引,不過據說5.6開始,InnoDB也將支持全文索引。MyISAM因為在鎖粒度和崩潰恢復上的缺點,使得在大型全文索引場景中基本無法使用。這時,我們通常
幫助客戶構建和使用Sphinx來解決全文索引問題。
9. XA事務:很少有人使用MySQL的XA事務特性。除非你真正明白參數innodb_support_xa的意義,否則不要修改這個參數的值,並不是只有顯式使用XA事務時才需要設置這個參數。InnoDB和二進制日志也是
需要使用XA事務來做協調的,從而確保在系統崩潰的時候,數據能夠一致地恢復。
10. 查詢緩存:完全相同的查詢在重復執行的時候,查詢緩存可用立即返回結果,而無須在數據庫重新執行一次。根據我們的經驗,在高並發壓力環境中查詢緩存會導致系統性能的下降,甚至僵死。如果你一定要使用
查詢緩存,那麼不要設置太大的內存,而且只有在明確收益的時候才使用。那該如何判斷是否應該使用查詢緩存呢?建議使用Percona Server,觀察更細致的日志,並做一些簡單的計算。還可以查看緩存命中率(
並不是總是有用)、"INSERTS和SELECT比率"(這個參數也並不直觀)或者"命中和寫入比率"(這個參考意義較大)。查詢緩存時一個非常方便的緩存,可對應用程序完全透明,無需任何額外的編碼,但是,如果希望
有更高的緩存效率,我們建議使用memcached或者其他類似的解決方案。