表分區

MySQL在5.1版引入的分區是一種簡單的水平拆分，用戶需要在建表的時候加上分區參數，對應用是透明的無需修改代碼

對用戶來說，分區表是一個獨立的邏輯表，但是底層由多個物理子表組成，實現分區的代碼實際上是通過對一組底層表的對象封裝，但對SQL層來說是一個完全封裝底層的黑盒子。MySQL實現分區的方式也意味著索引也是按照分區的子表定義，沒有全局索引。

640?wx_fmt=png

用戶的SQL語句是需要針對分區表做優化，SQL條件中要帶上分區條件的列，從而使查詢定位到少量的分區上，否則就會掃描全部分區，可以通過 EXPLAIN PARTITIONS來查看某條SQL語句會落在那些分區上，從而進行SQL優化，如下圖5條記錄落在兩個分區上：

  1. mysql> explain partitions select count(1) from user_partition where id in (1,2,3,4,5);
  2. +----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
  3. | id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
  4. +----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
  5. |  1 | SIMPLE      | user_partition | p1,p4      | range | PRIMARY       | PRIMARY | 8       | NULL |    5 | Using where; Using index |
  6. +----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
  7. 1 row in set (0.00 sec)

分區的好處是：

  • 可以讓單表存儲更多的數據

  • 分區表的數據更容易維護，可以通過清楚整個分區批量刪除大量數據，也可以增加新的分區來支持新插入的數據。另外，還可以對一個獨立分區進行優化、檢查、修復等操作

  • 部分查詢能夠從查詢條件確定只落在少數分區上，速度會很快

  • 分區表的數據還可以分布在不同的物理設備上，從而搞笑利用多個硬件設備

  • 可以使用分區表賴避免某些特殊瓶頸，例如InnoDB單個索引的互斥訪問、ext3文件系統的inode鎖競爭

  • 可以備份和恢復單個分區

分區的限制和缺點：

  • 一個表最多只能有1024個分區

  • 如果分區字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進來

  • 分區表無法使用外鍵約束

  • NULL值會使分區過濾無效

  • 所有分區必須使用相同的存儲引擎

分區的類型：

  • RANGE分區：基于屬于一個給定連續區間的列值，把多行分配給分區

  • LIST分區：類似于按RANGE分區，區別在于LIST分區是基于列值匹配一個離散值集合中的某個值來進行選擇

  • HASH分區：基于用戶定義的表達式的返回值來進行選擇的分區，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL中有效的、產生非負整數值的任何表達式

  • KEY分區：類似于按HASH分區，區別在于KEY分區只支持計算一列或多列，且MySQL服務器提供其自身的哈希函數。必須有一列或多列包含整數值

分區適合的場景有：

最適合的場景數據的時間序列性比較強，則可以按時間來分區，如下所示：

  1. CREATE TABLE members (
  2.    firstname VARCHAR(25) NOT NULL,
  3.    lastname VARCHAR(25) NOT NULL,/li>
  4.    username VARCHAR(16) NOT NULL,
  5.    email VARCHAR(35),
  6.    joined DATE NOT NULL
  7. )
  8. PARTITION BY RANGE( YEAR(joined) ) (
  9.    PARTITION p0 VALUES LESS THAN (1960),
  10.    PARTITION p1 VALUES LESS THAN (1970),
  11.    PARTITION p2 VALUES LESS THAN (1980),
  12.    PARTITION p3 VALUES LESS THAN (1990),
  13.    PARTITION p4 VALUES LESS THAN MAXVALUE
  14. );

查詢時加上時間范圍條件效率會非常高，同時對于不需要的歷史數據能很容的批量刪除。

如果數據有明顯的熱點，而且除了這部分數據，其他數據很少被訪問到，那么可以將熱點數據單獨放在一個分區，讓這個分區的數據能夠有機會都緩存在內存中，查詢時只訪問一個很小的分區表，能夠有效使用索引和緩存

另外MySQL有一種早期的簡單的分區實現 - 合并表（merge table），限制較多且缺乏優化，不建議使用，應該用新的分區機制來替代

垂直拆分

垂直分庫是根據數據庫里面的數據表的相關性進行拆分，比如：一個數據庫里面既存在用戶數據，又存在訂單數據，那么垂直拆分可以把用戶數據放到用戶庫、把訂單數據放到訂單庫。垂直分表是對數據表進行垂直拆分的一種方式，常見的是把一個多字段的大表按常用字段和非常用字段進行拆分，每個表里面的數據記錄數一般情況下是相同的，只是字段不一樣，使用主鍵關聯

比如原始的用戶表是：

20190307150113.jpg

垂直拆分后是：

20190307150158.jpg

垂直拆分的優點是：

  • 可以使得行數據變小，一個數據塊(Block)就能存放更多的數據，在查詢時就會減少I/O次數(每次查詢時讀取的Block 就少)

  • 可以達到最大化利用Cache的目的，具體在垂直拆分的時候可以將不常變的字段放一起，將經常改變的放一起

  • 數據維護簡單

缺點是：

  • 主鍵出現冗余，需要管理冗余列

  • 會引起表連接JOIN操作（增加CPU開銷）可以通過在業務服務器上進行join來減少數據庫壓力

  • 依然存在單表數據量過大的問題（需要水平拆分）

  • 事務處理復雜

水平拆分

概述

水平拆分是通過某種策略將數據分片來存儲，分庫內分表和分庫兩部分，每片數據會分散到不同的MySQL表或庫，達到分布式的效果，能夠支持非常大的數據量。前面的表分區本質上也是一種特殊的庫內分表

庫內分表，僅僅是單純的解決了單一表數據過大的問題，由于沒有把表的數據分布到不同的機器上，因此對于減輕MySQL服務器的壓力來說，并沒有太大的作用，大家還是競爭同一個物理機上的IO、CPU、網絡，這個就要通過分庫來解決

前面垂直拆分的用戶表如果進行水平拆分，結果是：

20190307150227.jpg

實際情況中往往會是垂直拆分和水平拆分的結合，即將 Users_A_M和 Users_N_Z再拆成 Users和 UserExtras，這樣一共四張表

水平拆分的優點是:

  • 不存在單庫大數據和高并發的性能瓶頸

  • 應用端改造較少

  • 提高了系統的穩定性和負載能力

缺點是：

  • 分片事務一致性難以解決

  • 跨節點Join性能差，邏輯復雜

  • 數據多次擴展難度跟維護量極大

分片原則

  • 能不分就不分，參考單表優化

  • 分片數量盡量少，分片盡量均勻分布在多個數據結點上，因為一個查詢SQL跨分片越多，則總體性能越差，雖然要好于所有數據在一個分片的結果，只在必要的時候進行擴容，增加分片數量

  • 分片規則需要慎重選擇做好提前規劃，分片規則的選擇，需要考慮數據的增長模式，數據的訪問模式，分片關聯性問題，以及分片擴容問題，最近的分片策略為范圍分片，枚舉分片，一致性Hash分片，這幾種分片都有利于擴容

  • 盡量不要在一個事務中的SQL跨越多個分片，分布式事務一直是個不好處理的問題

  • 查詢條件盡量優化，盡量避免Select * 的方式，大量數據結果集下，會消耗大量帶寬和CPU資源，查詢盡量避免返回大量結果集，并且盡量為頻繁使用的查詢語句建立索引。

  • 通過數據冗余和表分區賴降低跨庫Join的可能

這里特別強調一下分片規則的選擇問題，如果某個表的數據有明顯的時間特征，比如訂單、交易記錄等，則他們通常比較合適用時間范圍分片，因為具有時效性的數據，我們往往關注其近期的數據，查詢條件中往往帶有時間字段進行過濾，比較好的方案是，當前活躍的數據，采用跨度比較短的時間段進行分片，而歷史性的數據，則采用比較長的跨度存儲。

總體上來說，分片的選擇是取決于最頻繁的查詢SQL的條件，因為不帶任何Where語句的查詢SQL，會遍歷所有的分片，性能相對最差，因此這種SQL越多，對系統的影響越大，所以我們要盡量避免這種SQL的產生。

2/3首頁123尾頁