資料庫 之 Mysql的表分區

標籤：ash   https   分割   方法   後端   執行時間   val   主鍵   不同   

把表分區，但是還在同一台伺服器上，

分區是將資料分成多個位置上，分成多張表，實際是一張邏輯表（是虛表），被分成了多張表，但是管理的時候可以按照管理一張表的時候管理。

分區在邏輯上是同一張表，分表的話，每一張表是獨立的

以下是三種常見的分區方法：

可以按range(範圍)切分，如1--10萬行或者時間範圍

按hash值切分：模數

按list(列表)來切分：如華東片區或者華北片區

分區相關選項查看如下，分區要是主鍵或者是主鍵的組成部分

MariaDB [(none)]> help create table;中的選項partition_options就是分區的選項，partition_definition為分區的選項

例子

例一：根據範圍（值大小）分區

以下語句表示一張students表建立成三個分區

一般從最小的數值開始定義，這裡有個預設的內建變數maxvalue，為該表的最大值

MariaDB [sunny]> create table testpartition (id int,name varchar(100),age tinyint unsigned not null,gender enum('F','M')) partition by range(age)(partition youngman values less than (40),partition middleman values less than (60),partition oldman values less than maxvalue);

用desc查看錶的結構為一張表

MariaDB [sunny]> desc testpartition;

在shell命令執行

[[email protected] mysql]#cd /var/lib/mysql/sunny

可以看到對於表testpartition有三個單獨的資料表空間，即三個testpartition.*ibd的檔案，但是表結構只有一個，即testpartition.frm 

對shell命令下，產生一組隨機值，插入testpartition表裡

定義gender數組

[[email protected] sunny]#gender=('F' 'M')

定義數組

插入1000行資料，注意外部使用雙引號，sql語句內使用單引號

[[email protected] sunny]#for i in {1..1000};do mysql -uroot -pPass1234 -e "insert into sunny.testpartition values ($i,'stud$i',$[$RANDOM%80+18],'${gender[$RANDOM%2]}');";done;

此時，在sunny.testpartition產生1000條記錄

通過這個例子，我們可以得出結論是，雖然我們通過範圍進行了表切分，但是我們是對同一張表進行操作。但是這1000條記錄是被分割到不同的資料表空間裡進行儲存

驗證，如下命令，可以看到 testpartition#P#middleman.ibd，testpartition#P#oldman.ibd和testpartition#P#youngman.ibd已經有大小

[[email protected] sunny]#ll /var/lib/mysql/sunny -h

例二：根據id值hash後進行分區

注意，要指定分區數量，如以下partitions 5表示對5進行模數，即分成5個區，注意hash欄位的類型，字元類型如char或者varchar不能被hash，因為需要填充多餘的類型,這樣的欄位不能被hash

MariaDB [sunny]> create table hashpartition (id int,name varchar(100) not null,age tinyint unsigned,gender enum ('F','M')) partition by hash(id) partitions 5;

在路徑/var/lib/mysql/sunny下產生5個資料表空間

 hashpartition#P#p0.ibd -- hashpartition#P#p4.ibd 

例三：根據列表劃分

假設表listpartition有一個欄位majorid，有九個值，為1--9，根據majorid進行列表分區

MariaDB [sunny]> create table listpartition (id int,name varchar(100) not null,age tinyint unsigned,gender enum ('F','M'),majorid tinyint unsigned not null) partition by list(majorid) (partition p0 values in (1,4,7),partition p1 values in (2,5,8),partition p2 values in (3,6,9));

那麼majorid 為1,4,7三個值的資料會被分配到listpartition#P#p0.ibd裡

majorid 為2,5,8三個值的資料會被分配到listpartition#P#p1.ibd裡

majorid 為3,6,9三個值的資料會被分配到listpartition#P#p2.ibd裡

到此，表分區介紹完成。

如果表分區還不能解決效能的問題，建議考慮分表或者是分庫

分庫的原因

伺服器面臨大量寫操作而無法負載，解決方案如下

1.分庫：如使用者資訊放在一個庫上，購物資訊放在另一個庫上

2.分庫不能解決的話，就通過路由裝置，將大量的寫請求分散到後端的伺服器上，將同一個使用者的資訊都發到同一伺服器上，可以使得同一使用者快速查看到所有的訂單。

節點級的的冗餘：每一個主機後端有多個從伺服器，用來分散讀操作，降低壓力，因此，每一個主伺服器就是一個叢集。當主伺服器異常時，後端的從伺服器直接選舉出新的主伺服器

資料級的冗餘：當使用者請求到來時，每一個節點上做資料存放的槽，如每個節點做四個槽，每一個槽就是資料存放單位，每一個槽都有一個id，當使用者請求過來時，對槽進行模數，將命中槽的請求發往對應的槽。注意，這裡可以對槽進行備份，每一個槽都有副本進行冗餘，副本可以放在另一個伺服器上閒置槽位上，當任何一個伺服器異常，也不會影響資料，對槽模數可以保證資料讀取的均衡請求。每個槽上的資料稱為資料片。分區可以實現分散寫操作。分區的架構中，前端的路由裝置很關鍵，在分區層級做冗餘，每一個分區的副本可以提供讀操作。但是分區技術比較複雜。正常情況下，一個網站通常有三層：內容，業務，資料層。隨著業務規模的增加，迭代增加，修改架構。

分表的原因

分表將大表分解成若干個執行個體的表，每一個表是獨立可用。

當一張的資料達到幾百萬時，你查詢一次所花的時間會變多，如果有聯集查詢的話，我想有可能會死在那兒了。分表的目的就在於此，減小資料庫的負擔，縮短查詢時間。

根據個人經驗，mysql執行一個sql的過程如下：

1,接收到sql;2,把sql放到排隊隊列中 ;3,執行sql;4,返回執行結果。在這個執行過程中最花時間在什麼地方呢？第一，是排隊等待的時間，第二，sql的執行時間。其實這二個是一回事，等待的同時，肯定有sql在執行。所以我們要縮短sql的執行時間。

mysql中有一種機制是表鎖定和行鎖定，為什麼要出現這種機制，是為了保證資料的完整性，我舉個例子來說吧，如果有二個sql都要修改同一張表的同一條資料，這個時候怎麼辦呢，是不是二個sql都可以同時修改這條資料呢？很顯然mysql對這種情況的處理是，一種是表鎖定（myisam儲存引擎），一個是行鎖定（innodb儲存引擎）。表鎖定表示你們都不能對這張表進行操作，必須等我對錶操作完才行。行鎖定也一樣，別的sql必須等我對這條資料操作完了，才能對這條資料進行操作。如果資料太多，一次執行的時間太長，等待的時間就越長，這也是我們為什麼要分表的原因。

過於表分區，分表，分庫的區別，建議查看部落格：https://www.cnblogs.com/langtianya/p/4997768.html

更多關於表分區的內容，建議查看部落格：http://blog.51yip.com/mysql/949.html

資料庫 之 Mysql的表分區