一、 mysql分區(qū)簡介 

        數(shù)據(jù)庫分區(qū) 

        數(shù)據(jù)庫分區(qū)是一種物理數(shù)據(jù)庫設(shè)計技術(shù)。雖然分區(qū)技術(shù)可以實現(xiàn)很多效果，但其主要目的是為了在特定的SQL操作中減少數(shù)據(jù)讀寫的總量以縮減sql語句的響應(yīng)時間，同時對于應(yīng)用來說分區(qū)完全是透明的。 

        MYSQL的分區(qū)主要有兩種形式:水平分區(qū)和垂直分區(qū) 

        水平分區(qū)(Horizontal Partitioning) 

        這種形式的分區(qū)是對根據(jù)表的行進(jìn)行分區(qū)，通過這樣的方式不同分組里面的物理列分割的數(shù)據(jù)集得以組合，從而進(jìn)行個體分割(單分區(qū))或集體分割(1個或多個分區(qū))。 所有在表中定義的列在每個數(shù)據(jù)集中都能找到，所以表的特性依然得以保持。水平分區(qū)一定要通過某個屬性列來分割。常見的比如年份，日期等。 

        垂直分區(qū)(Vertical Partitioning) 

        這種分區(qū)方式一般來說是通過對表的垂直劃分來減少目標(biāo)表的寬度，使某些特定的列被劃分到特定的分區(qū)，每個分區(qū)都包含了其中的列所對應(yīng)所有行。 

        可以用 show variables like '%partition%'; 命令查詢當(dāng)前的mysql數(shù)據(jù)庫版本是否支持分區(qū)。 

        分區(qū)的作用:數(shù)據(jù)庫性能的提升和簡化數(shù)據(jù)管理 

        在掃描操作中，mysql優(yōu)化器只掃描保護(hù)數(shù)據(jù)的那個分區(qū)以減少掃描范圍獲得性能的提高。 分區(qū)技術(shù)使得數(shù)據(jù)管理變得簡單，刪除某個分區(qū)不會對另外的分區(qū)造成影響，分區(qū)有系統(tǒng)直接管理不用手工干預(yù)。 

        mysql從5.1版本開始支持分區(qū)。每個分區(qū)的名稱是不區(qū)分大小寫。同個表中的分區(qū)表名稱要唯一。 

        二、 mysql分區(qū)類型 

        根據(jù)所使用的不同分區(qū)規(guī)則可以分成幾大分區(qū)類型。 

        RANGE 分區(qū): 

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

        LIST 分區(qū): 

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

        HASH分區(qū): 

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

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

        復(fù)合分區(qū): 基于RANGE/LIST 類型的分區(qū)表中每個分區(qū)的再次分割。子分區(qū)可以是 HASH/KEY 等類型。 

        三、 mysql分區(qū)表常用操作示例 以部門員工表為例子: 

        1) 創(chuàng)建range分區(qū) 

        create table emp 

        (empno varchar(20) not null , 

        empname varchar(20), 

        deptno int, 

        birthdate date, 

        salary int 

        ) 

        partition by range(salary) ( 

        partition p1 values less than (1000), partition p2 values less than (2000), partition p3 values less than maxvalue 

        ); 

        以員工工資為依據(jù)做范圍分區(qū)。 

        create table emp 

        (empno varchar(20) not null , 

        empname varchar(20), 

        deptno int, 

        birthdate date not null, 

        salary int 

        ) 

        partition by range(year(birthdate)) ( 

        partition p1 values less than (1980), partition p2 values less than (1990), partition p3 values less than maxvalue 

        ); 

        以year(birthdate)表達(dá)式(計算員工的出生日期)作為范圍分區(qū)依據(jù)。這里最值得注意的是表達(dá)式必須有返回值。 

        2) 創(chuàng)建list分區(qū) 

        create table emp 

        (empno varchar(20) not null , 

        empname varchar(20), 

        deptno int, 

        birthdate date not null, 

        salary int 

        ) 

        partition by list(deptno) 

        ( 

        partition p1 values in (10), 

        partition p2 values in (20), 

        partition p3 values in (30) 

        ); 

        以部門作為分區(qū)依據(jù)，每個部門做一分區(qū)。 

        3) 創(chuàng)建hash分區(qū) 

        HASH分區(qū)主要用來確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布。在RANGE和LIST分區(qū)中，必須明確指定一個給定的列值或列值集合應(yīng)該保存在哪個分區(qū)中;而在HASH分區(qū)中，MySQL自動完成這些工作，你所要做的只是基于將要被哈希的列值指定一個列值或表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。 

        create table emp 

        (empno varchar(20) not null , 

        empname varchar(20), 

        deptno int, 

        birthdate date not null, 

        salary int 

        ) 

        partition by hash(year(birthdate)) 

        partitions 4; 

        4) 創(chuàng)建key分區(qū) 

        按照KEY進(jìn)行分區(qū)類似于按照HASH分區(qū)，除了HASH分區(qū)使用的用戶定義的表達(dá)式，而KEY分區(qū)的哈希函數(shù)是由MySQL 服務(wù)器提供，服務(wù)器使用其自己內(nèi)部的哈希函數(shù)，這些函數(shù)是基于與PASSWORD()一樣的運算法則。“CREATE TABLE ... PARTITION BY KEY”的語法規(guī)則類似于創(chuàng)建一個通過HASH分區(qū)的表的規(guī)則。它們唯一的區(qū)別在于使用的關(guān)鍵字是KEY而不是HASH，并且KEY分區(qū)只采用一個或多個列名的一個列表。 create table emp 

        (empno varchar(20) not null , 

        empname varchar(20), 

        deptno int, 

        birthdate date not null, 

        salary int 

        ) 

        partition by key(birthdate) 

        partitions 4; 

        5) 創(chuàng)建復(fù)合分區(qū) 

        range - hash(范圍哈希)復(fù)合分區(qū) 

        create table emp 

        (empno varchar(20) not null , empname varchar(20), 

        deptno int, 

        birthdate date not null, 

        salary int 

        ) 

        partition by range(salary) subpartition by hash(year(birthdate)) 

        subpartitions 3 

        ( 

        partition p1 values less than (2000), partition p2 values less than maxvalue 

        ); 

        range- key復(fù)合分區(qū) 

        create table emp 

        (empno varchar(20) not null , empname varchar(20), 

        deptno int, 

        birthdate date not null, 

        salary int 

        ) 

        partition by range(salary) subpartition by key(birthdate) subpartitions 3 

        ( 

        partition p1 values less than (2000), partition p2 values less than maxvalue 

        ); 

        list - hash復(fù)合分區(qū) 

        CREATE TABLE emp ( 

        empno varchar(20) NOT NULL, 

        empname varchar(20) , 

        deptno int, 

        birthdate date NOT NULL, 

        salary int 

        ) 

        PARTITION BY list (deptno) 

        subpartition by hash(year(birthdate)) subpartitions 3 

        ( 

        PARTITION p1 VALUES in (10), PARTITION p2 VALUES in (20) 

        ) ; 

        list - key 復(fù)合分區(qū) 

        CREATE TABLE empk ( 

        empno varchar(20) NOT NULL, 

        empname varchar(20) , 

        deptno int, 

        birthdate date NOT NULL, 

        salary int 

        ) 

        PARTITION BY list (deptno) subpartition by key(birthdate) subpartitions 3 

        ( 

        PARTITION p1 VALUES in (10), PARTITION p2 VALUES in (20) 

        ) ; 

        6) 分區(qū)表的管理操作 

        刪除分區(qū): 

        alter table emp drop partition p1; 

        不可以刪除hash或者key分區(qū)。 

        一次性刪除多個分區(qū)，alter table emp drop partition p1,p2; 

        增加分區(qū): 

        alter table emp add partition (partition p3 values less than (4000)); 

        alter table empl add partition (partition p3 values in (40)); 

        分解分區(qū): 

        Reorganize partition關(guān)鍵字可以對表的部分分區(qū)或全部分區(qū)進(jìn)行修改，并且不會丟失數(shù)據(jù)。分解前后分區(qū)的整體范圍應(yīng)該一致。 alter table te 

        reorganize partition p1 into 

        ( 

        partition p1 values less than (100), partition p3 values less than (1000) ); ----不會丟失數(shù)據(jù) 

        合并分區(qū): 

        Merge分區(qū):把2個分區(qū)合并為一個。 

        alter table te 

        reorganize partition p1,p3 into (partition p1 values less than (1000)); ----不會丟失數(shù)據(jù) 

        重新定義hash分區(qū)表: 

        Alter table emp partition by hash(salary) partitions 7; 

        ----不會丟失數(shù)據(jù) 

        重新定義range分區(qū)表: 

        Alter table emp partition by range(salary) 

        ( 

        partition p1 values less than (2000), partition p2 values less than (4000) ); ----不會丟失數(shù)據(jù) 

        刪除表的所有分區(qū): 

        Alter table emp remove partitioning;--不會丟失數(shù)據(jù) 

        重建分區(qū): 

        這和先刪除保存在分區(qū)中的所有記錄，然后重新插入它們，具有同樣的效果。它可用于整理分區(qū)碎片。 

        ALTER TABLE emp rebuild partition p1,p2; 

        優(yōu)化分區(qū): 

        如果從分區(qū)中刪除了大量的行，或者對一個帶有可變長度的行(也就是說，有VARCHAR，BLOB，或TEXT類型的列)作了許多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”來收回沒有使用的空間，并整理分區(qū)數(shù)據(jù)文件的碎片。 

        ALTER TABLE emp optimize partition p1,p2; 

        分析分區(qū): 

        讀取并保存分區(qū)的鍵分布。 

        ALTER TABLE emp analyze partition p1,p2; 

        修補(bǔ)分區(qū): 

        修補(bǔ)被破壞的分區(qū)。 

        ALTER TABLE emp repair partition p1,p2; 

        檢查分區(qū): 

        可以使用幾乎與對非分區(qū)表使用CHECK TABLE 相同的方式檢查分區(qū)。 ALTER TABLE emp CHECK partition p1,p2; 

        這個命令可以告訴你表emp的分區(qū)p1,p2中的數(shù)據(jù)或索引是否已經(jīng)被破壞。如果發(fā)生了這種情況，

        使用“ALTER TABLE ... REPAIR PARTITION”來修補(bǔ)該分區(qū)。 

        【mysql分區(qū)表的局限性】 

        1. 在5.1版本中分區(qū)表對唯一約束有明確的規(guī)定，每一個唯一約束必須包含在分區(qū)表的分區(qū)鍵(也包括主鍵約束)。 

        CREATE TABLE emptt ( 

        empno varchar(20) NOT NULL , 

        empname varchar(20)， 

        deptno int, 

        birthdate date NOT NULL, 

        salary int , 

        primary key (empno) 

        ) 

        PARTITION BY range (salary) 

        ( 

        PARTITION p1 VALUES less than (100), 

        PARTITION p2 VALUES less than (200) 

        ); 

        這樣的語句會報錯。MySQL Database Error: A PRIMARY KEY must include all columns in the table's partitioning function; 

        CREATE TABLE emptt ( 

        empno varchar(20) NOT NULL , 

        empname varchar(20) , 

        deptno int(11), 

        birthdate date NOT NULL, 

        salary int(11) , 

        primary key (empno，salary) 

        ) 

        PARTITION BY range (salary) 

        ( 

        PARTITION p1 VALUES less than (100), 

        PARTITION p2 VALUES less than (200) 

        ); 

        在主鍵中加入salary列就正常。 

        2. MySQL分區(qū)處理NULL值的方式 

        如果分區(qū)鍵所在列沒有not null約束。 

        如果是range分區(qū)表，那么null行將被保存在范圍最小的分區(qū)。 如果是list分區(qū)表，那么null行將被保存到list為0的分區(qū)。 

        在按HASH和KEY分區(qū)的情況下，任何產(chǎn)生NULL值的表達(dá)式mysql都視同它的返回值為0為了避免這種情況的產(chǎn)生，建議分區(qū)鍵設(shè)置成NOT NULL。 

        3. 分區(qū)鍵必須是INT類型，或者通過表達(dá)式返回INT類型，可以為NULL。唯一的例外是當(dāng)分區(qū)類型為KEY分區(qū)的時候，可以使用其他類型的列作為分區(qū)鍵( BLOB or TEXT 列除外)。 

        4. 對分區(qū)表的分區(qū)鍵創(chuàng)建索引，那么這個索引也將被分區(qū),分區(qū)鍵沒有全局索引一說。 

        5. 只有RANG和LIST分區(qū)能進(jìn)行子分區(qū)，HASH和KEY分區(qū)不能進(jìn)行子分區(qū)。 6. 臨時表不能被分區(qū)。 

        四、 獲取mysql分區(qū)表信息的幾種方法 

        1. show create table 表名 

        可以查看創(chuàng)建分區(qū)表的create語句 

        2. show table status 

        可以查看表是不是分區(qū)表 

        3. 查看information_schema.partitions表 

        select 

        partition_name part, 

        partition_expression expr, 

        partition_description descr, 

        table_rows 

        from information_schema.partitions where 

        table_schema = schema() 

        and table_name='test'; 

        可以查看表具有哪幾個分區(qū)、分區(qū)的方法、分區(qū)中數(shù)據(jù)的記錄數(shù)等信息 4. explain partitions select語句通過此語句來顯示掃描哪些分區(qū)，及他們是如何使用的. 

        五、 分區(qū)表性能比較 

        1. 創(chuàng)建兩張表: part_tab(分區(qū)表),no_part_tab(普通表) 

        CREATE TABLE part_tab 

        ( c1 int default NULL, c2 varchar(30) default NULL, c3 date not null) 

        PARTITION BY RANGE(year(c3)) 

        (PARTITION p0 VALUES LESS THAN (1995), PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , PARTITION p3 VALUES LESS THAN (1998) , 

        PARTITION p4 VALUES LESS THAN (1999) , PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), PARTITION p11 VALUES LESS THAN (MAXVALUE) ); CREATE TABLE no_part_tab 

        ( c1 int default NULL, c2 varchar(30) default NULL, c3 date not null); 

        2. 用存儲過程插入800萬條數(shù)據(jù) 

        CREATE PROCEDURE load_part_tab() 

        begin 

        declare v int default 0; 

        while v < 50000000 

        do 

        insert into part_tab 

        values (v,'testing 

        partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652)); 

        set v = v + 1; 

        end while; 

        end; 

        insert into no_part_tab select * from part_tab; 

        3. 測試sql性能 

        查詢分區(qū)表: 

        select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'; +----------+ 

        | count(*) | 

        +----------+ 

        | 795181 | 

        ----------+ +

        1 row in set (2.62 sec) 

        查詢普通表: 

        select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'; +----------+ 

        | count(*) | 

        +----------+ 

        | 795181 | 

        +----------+ 

        1 row in set (7.33 sec) 

        分區(qū)表的執(zhí)行時間比普通表少70%。 

        4. 通過explain語句來分析執(zhí)行情況 

        mysql> explain select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'; 

        +----+-------------+----------+------+---------------+------+---------+------+---------+-------------+ 

        | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 

        +----+-------------+----------+------+---------------+------+---------+------+---------+-------------+ 

        | 1 | SIMPLE | part_tab | ALL | NULL | NULL | NULL | NULL | 7980796 | Using where | 

        +----+-------------+----------+------+---------------+------+---------+------+---------+-------------+ 

        1 row in set 

        mysql> explain select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'; 

        +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+ 

        | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 

        +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+ 

        | 1 | SIMPLE | no_part_tab | ALL | NULL | NULL | NULL | NULL | 8000206 

        | Using where | 

        +----+-------------+-------------+------+---------------+------+---------+------+--

        -------+-------------+ 

        1 row in set 

        mysql > 

        分區(qū)表執(zhí)行掃描了7980796行，而普通表則掃描了8000206行。 

        DROP PROCEDURE IF EXISTS test // 

        CREATE PROCEDURE test /* 存儲過程

        名 */ (IN inparms INT, OUT outparams varchar(32)) /* 輸入?yún)?/p>

        數(shù) */ BEGIN /* 語句塊

        頭 */ DECLARE var CHAR(10); /* 變量聲

        明 */ IF inparms = 1 THEN /* IF條件開始

        */ SET var = 'hello'; /* 賦值 */ ELSE 

        SET var = 'world'; 

        END IF; /* IF結(jié)束 */ INSERT INTO t1 VALUES (var); /* SQL

        語句 */ SELECT name FROM t1 LIMIT 1 INTO outparams; END