引言

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

一. 選擇合適的解決方法

1. 分庫分表。

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

2. 分區(qū)。

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

2.1. innodb存儲引擎。

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

• 共享空間結(jié)構(gòu) 

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

• 獨(dú)立空間結(jié)構(gòu)

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

2.2. myisam存儲引擎

myisam是mysql默認(rèn)存儲引擎，不支持事務(wù)，但是會對I/O進(jìn)行平均分配，相較于innodb查詢速度要快，對并發(fā)不友好，支持表鎖。格式frm同樣也是表結(jié)構(gòu)，myd為表的數(shù)據(jù)源，myi表的索引儲存(所以一張表的索引不是越多越好，因?yàn)樵谔砑雍托薷臄?shù)據(jù)時也需要對索引庫進(jìn)行修改和添加) 

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

1. RANGE分區(qū)：

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

2. LIST分區(qū)：

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

3. HASH分區(qū)：

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

4. KEY分區(qū)：

類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(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ù)以當(dāng)前的分區(qū)規(guī)則對原來數(shù)據(jù)進(jìn)行規(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ū)的功能。"新建表"或者是"設(shè)計(jì)表"，點(diǎn)擊"選項(xiàng)"，“分割區(qū)”就可以進(jìn)入分區(qū)的創(chuàng)建了。

四. 分區(qū)查詢的實(shí)驗(yàn)結(jié)果。

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

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

explain partitions select * from table_name ……       

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

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

最新評論