引言

在一些系統(tǒng)中有時某張表會出現(xiàn)百萬或者千萬的數(shù)據(jù)量，盡管其中使用了索引，查詢速度也不一定會很快。這時候可能就需要通過分庫，分表，分區(qū)來解決這些性能瓶頸。

一. 選擇合適的解決方法

1. 分庫分表。

分庫分表從名字上就明白是需要創(chuàng)建額外的新數(shù)據(jù)庫或新表，可以建在其他的機器上也可以是和當前數(shù)據(jù)庫同一臺機器。在優(yōu)化查詢上可能需要買新機器或者要修改邏輯代碼了。比如一張user表，當數(shù)據(jù)超過10W，就可以創(chuàng)建新的user表，假設是user2。通過接收的UID，和分組10W(假定)，取模就是對應的user表名的索引數(shù)字。

2. 分區(qū)。

分區(qū)這里是指表分區(qū)，mysql數(shù)據(jù)庫管理系統(tǒng)提供的表功能，分區(qū)后邏輯上是同一張表，物理上數(shù)據(jù)存儲是分開的。能否優(yōu)化查詢還取決于在查詢中是否使用到了分區(qū)字段，這個就和索引的使用有點類似，但是好處就是這個不像分庫分表，可以幾乎不修改業(yè)務邏輯代碼就可以提升速度。下面再總結一下mysql數(shù)據(jù)保存格式和innodb，myisam。

2.1. innodb存儲引擎。

innodb，支持事務處理，外來鍵，在查詢方面要慢于myisam。對并發(fā)友好，支持行鎖和表鎖，行鎖的形成要看查詢條件。有共享空間結構和獨立空間結構，保存的格式有frm和ibddata1(共享結構)，ibd(獨立結構)。

• 共享空間結構 

共享結構為innodb默認的結構，除了frm保存innodb表結構外，整個數(shù)據(jù)庫所有表的索引和數(shù)據(jù)源都保存在ibdata中?？梢酝ㄟ^在mysql-ini中添加 innodb_file_per_table=1設置為獨立空間結構。

• 獨立空間結構

獨立空間結構就是每個對應的表保存對應的數(shù)據(jù)源和索引在一個后綴為ibd的文件中，表結構同樣也保存在frm中。

2.2. myisam存儲引擎

myisam是mysql默認存儲引擎，不支持事務，但是會對I/O進行平均分配，相較于innodb查詢速度要快，對并發(fā)不友好，支持表鎖。格式frm同樣也是表結構，myd為表的數(shù)據(jù)源，myi表的索引儲存(所以一張表的索引不是越多越好，因為在添加和修改數(shù)據(jù)時也需要對索引庫進行修改和添加) 

二. 表分區(qū)的幾種分區(qū)類型。

1. RANGE分區(qū)：

基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。

2. LIST分區(qū)：

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

3. HASH分區(qū)：

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

4. KEY分區(qū)：

類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL服務器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。具體介紹可以搜索以下"mysql表分區(qū)的分區(qū)類型"。 

三. 表分區(qū)的創(chuàng)建。

par格式為保存的是分區(qū)的規(guī)則，ibd就是分別為每一塊分區(qū)后的數(shù)據(jù)源，以下為innodb分區(qū)，myisam的是有多個myd文件同時也存在par。

1. 新建表時添加分區(qū)。

 比如以下創(chuàng)建一張employees 的表，并創(chuàng)建了list類型的4個分區(qū)，以store_id 字段為分區(qū)字段。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id)
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

2. 存在的表添加分區(qū)

將原來的數(shù)據(jù)以當前的分區(qū)規(guī)則對原來數(shù)據(jù)進行規(guī)整，以下是對ztest表添加range類型的3個分區(qū)，以id字段為分區(qū)字段。

alter table rm_ztest partition by RANGE (id)   
(
    PARTITION p0 VALUES LESS THAN (948),  
    PARTITION p1 VALUES LESS THAN (960),
    PARTITION p3 VALUES LESS THAN MAXVALUE
)

3. navicat for mysql工具添加或創(chuàng)建。

平時我使用上面的工具比較多，所以他也有一個添加分區(qū)的功能。"新建表"或者是"設計表"，點擊"選項"，“分割區(qū)”就可以進入分區(qū)的創(chuàng)建了。

四. 分區(qū)查詢的實驗結果。

我就拿了一張幾百萬數(shù)據(jù)的表備份了副本，其中一張創(chuàng)建了分區(qū)，并使用id分區(qū)字段進行查詢。

從上面兩張截圖可以看出使用了分區(qū)的查詢速度要比未使用分區(qū)快差不多1倍，但是如果不使用id為查詢條件或沒有使用到，速度二者是一樣的，甚至有時分區(qū)過的還要慢于未分區(qū)的，所以在使用上還需結合當前業(yè)務做合理的選擇。

explain partitions select * from table_name ……       

可以查看當前查詢是否使用了分區(qū)，分區(qū)使用的是哪幾個等等信息。 

以上就是MySQL表分區(qū)配置入門指南的詳細內(nèi)容，更多關于MySQL表分區(qū)配置的資料請關注腳本之家其它相關文章！

最新評論