MySql之分區分表

MySql之分區分表

分表的概念

分表：將一個大表按照一定的規則分解成多張具有獨立存儲空間的實體表，每個表都對應三個文件，MYD數據文件，.MYI索引文件，.frm表結構文件。這些表可以分布在同一塊磁盤上，也可以在不同的機器上。app讀寫的時候根據事先定義好的規則得到對應的表名，然后去操作它。

常用的算法：hash或求余（取模）等方式

分表的好處：減小數據庫的負擔，縮短查詢時間

分表的類型：①垂直切分：是指數據表列的拆分，把一張列比較多的表拆分為多張表

            ②水平拆分是指數據表行的拆分，把一張的表的數據拆成多張表來存放

分表的方式：①mysql集群

            ②預先估計會出現大數據量并且訪問頻繁的表，將其分為若干個表

            ③利用merge存儲引擎來實現分表

分表的案例：

1.創建庫、表、數據

使用：select  * from member；  查詢表

2.將member分為兩個表tb_member1,tb_member2。

tb_member1

tb_member2

創建主表

使用降序查看b_member表的結構

3.將數據分到兩個表中

mysql> insert into tb_member1(id,name,sex) select id,name,sex from member where id%2=0;

mysql> insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;

查看主表：mysql> select * from tb_member;  此時 總表只是一個外殼，存取數

據發生在一個一個的子表里面。

mysql> select * from tb_member where id=3; 查詢基于id的條件語句更加快捷

4.查看文件表：每個子表都有自已獨立的相關表文件，而主表只是一個殼，并沒有完整的相關表文件

分區的概念

分區：分區和分表相似，都是按照規則分解表。不同在于分表將大表分解為若干個獨立的實體表，而分區是將數據分段劃分在多個位置存放，分區后，表還是一張表，但數據散列到多個位置。app讀寫的時候操作的還是表名字，db自動去組織分區的數據。

分區的主要形式：①水平分區（Horizontal Partitioning）：這種形式分區是對表的行進行分區，所有在表中定義的列在每個數據集中都能找到，所以表的特性依然得以保持。

               ②垂直分區（Vertical Partitioning）這種分區方式一般來說是通過對表的垂直劃分來減少目標表的寬度，使某些特定的列被劃分到特定的分區，每個分區都包含了其中的列所對應的行。

分區技術支持：①在5.6之前，使用這個參數查看當將配置是否支持分區

                mysql> SHOW VARIABLES LIKE '%partition%'; 

              ②在5.6及以采用后，則采用如下方式進行查

                mysql> show plugins;

分區案例

1.創建庫、表、范圍分區表

插入15條數據

查看存放數據庫表文件

計算基于id值的有多少行

從information_schema系統庫中的partitions表中查看分區信息

從某個分區中查詢數據

當刪除了一個分區，也同時刪除了該分區中所有的數據。

新增分區

分區合并

再次查看存放數據庫表文件

查看合并后的分區

案例：未分區表和分區表性能測試

1.創建數據庫和一個未分區的表

2.創建分區表,按日期的年份拆分

3.插入10萬條測試語句

4.在表中插入數據

查詢表明分區表比未分區表的執行時間少很多。

在通過explain語句來分析執行情況

explain語句顯示了SQL查詢要處理的記錄數目可以看出分區表比未分區表的明顯掃描的記錄要少很多。

創建索引后與不不創建索引差異不大 1不創建索引重啟mysql服務在次查詢即可

分區表的類型

1.RANGE分區：基于屬于一個給定連續區間的列值，把多行分配給分區。這些區間要連續且不能相互重疊，使用VALUES LESS THAN操作符來進行定義。

案例：

創建maxvalue分區，所有不在指定范圍內的記錄都會被存儲到maxvalue所在的分區中。

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

LIST分區通過使用“PARTITION BY LIST(expr)”來實現，其中“expr” 是某列值或一個基于某個列值、并返回一個整數值的表達式，然后通過“VALUES IN (value_list)”的方式來定義每個分區，其中“value_list”是一個通過逗號分隔的整數列表。

案例：

查詢語句

3.HASH分區 ：這種模式允許DBA通過對表的一個或多個列的Hash Key進行計算，最后通過這個Hash碼不同數值對應的數據區域進行分區。

hash分區的目的是將數據均勻的分布到預先定義的各個分區中，保證各分區的數據量大致一致。在RANGE和LIST分區中，必須明確指定一個給定的列值或列值集合應該保存在哪個分區中；而在HASH分區中，MYSQL自動完成這些工作，用戶所要定一個列值或者表達式，以及指定被分區的表將要被分割成的分區數量。

hash的分區函數頁需要返回一個整數值。partitions子句中的值是一個非負整數，不加的partitions子句的話，默認為分區數為1。

該記錄會被放入分區p2中。因為插入2010-04-01進入表t_hash,那么

MOD(YEAR('2010-04-01'),4)=2

4.key分區：key分區和hash分區相似，不同在于hash分區是用戶自定義函數進行分區，key分區使用mysql數據庫提供的函數進行分區，NDB cluster使用MD5函數來分區，對于其他存儲引擎mysql使用內部的hash函數。

上面的RANGE、LIST、HASH、KEY四種分區中，分區的條件必須是×××，如果不是×××需要通過函數將其轉換為×××。

5.columns分區：mysql-5.5開始支持COLUMNS分區，可視為RANGE和LIST分區的進化，COLUMNS分區可以直接使用非×××數據進行分區。COLUMNS分區支持以下數據類型：
　　所有×××，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL則不支持。
　　日期類型，如DATE和DATETIME。其余日期類型不支持。
　　字符串類型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類型不支持。
　　COLUMNS可以使用多個列進行分區。