mysql的分区和分表

aifeng 2018-05-22

分区

分区就是把一个数据表的文件和索引分散存储在不同的物理文件中。

mysql支持的分区类型包括Range、List、Hash、Key,其中Range比较常用:

RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。

LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

案例:

建立一个user 表 以id进行分区 id 小于10的在user_1分区id小于20的在user_2分区

create table user(

    id int not null auto_increment,

    username varchar(10),

    primary key(id)

)engine = innodb charset=utf8

partition by range (id)(

partition user_1 values less than (10),

partition user_2 values less than (20)

);

建立后添加分区:

maxvalue 表示最大值   这样大于等于20的id 都出存储在user_3分区

alter table user add partition(

    partition user_3 values less than maxvalue

);

删除分区:

alter table user drop partition user_3;

现在打开mysql的数据目录

可以看见多了user#P#user_1.ibd 和user#P#user_2.ibd  这两个文件

如果表使用的存储引擎是MyISAM类型,就是:

user#P#user_1.MYD,user#P#user_1.MYI和user#P#user_2.MYD,user#P#user_2.MYI

分区模式详解:

* Range(范围) – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区,80年代(1980's)的数据,90年代(1990's)的数据以及任何在2000年(包括2000年)后的数据。

CREATE TABLE users (  

       id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  

       usersname VARCHAR(30) NOT NULL DEFAULT '',  

       email VARCHAR(30) NOT NULL DEFAULT ''  

)  

PARTITION BY RANGE (id) (  

       PARTITION p0 VALUES LESS THAN (3000000),  

      

       PARTITION p1 VALUES LESS THAN (6000000), 

     

       PARTITION p2 VALUES LESS THAN (9000000),  

     

       PARTITION p3 VALUES LESS THAN MAXVALUE     

);  

在这里,将用户表分成4个分区,以每300万条记录为界限,每个分区都有自己独立的数据、索引文件的存放目录。

还可以将这些分区所在的物理磁盘分开完全独立,可以提高磁盘IO吞吐量。

CREATE TABLE users (  

       id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  

       usersname VARCHAR(30) NOT NULL DEFAULT '',  

       email VARCHAR(30) NOT NULL DEFAULT ''  

)  

PARTITION BY RANGE (id) (  

       PARTITION p0 VALUES LESS THAN (3000000)  

       DATA DIRECTORY = '/data0/data'  

       INDEX DIRECTORY = '/data0/index',  

  

       PARTITION p1 VALUES LESS THAN (6000000)  

       DATA DIRECTORY = '/data1/data'  

       INDEX DIRECTORY = '/data1/index',  

  

       PARTITION p2 VALUES LESS THAN (9000000)  

       DATA DIRECTORY = '/data2/data'  

       INDEX DIRECTORY = '/data2/index',  

  

       PARTITION p3 VALUES LESS THAN MAXVALUE     

       DATA DIRECTORY = '/data3/data'   

       INDEX DIRECTORY = '/data3/index'  

);  

* List(预定义列表) – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如:DBA根据用户的类型进行分区。 

CREATE TABLE user (  

     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  

     name VARCHAR(30) NOT NULL DEFAULT '' ,

     user_type   int not null

)  

PARTITION BY LIST (user_type ) (  

     PARTITION p0 VALUES IN (0,4,8,12) , 

     PARTITION p1 VALUES IN (1,5,9,13) ,  

     PARTITION p2 VALUES IN (2,6,10,14),  

     PARTITION p3 VALUES IN (3,7,11,15)   

);

分成4个区,同样可以将分区设置的独立的磁盘中。

* Key(键值)

CREATE TABLE user (  

     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  

     name VARCHAR(30) NOT NULL DEFAULT '',  

     email VARCHAR(30) NOT NULL DEFAULT ''  

)  

PARTITION BY KEY (id) PARTITIONS 4 (  

     PARTITION p0,  

     PARTITION p1,  

     PARTITION p2,  

     PARTITION p3

);

* Hash(哈希)

CREATE TABLE user (  

     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  

     username VARCHAR(30) NOT NULL DEFAULT '',  

     email VARCHAR(30) NOT NULL DEFAULT ''  

)  

PARTITION BY HASH (id) PARTITIONS 4 (  

     PARTITION p0 ,  

     PARTITION p1,  

     PARTITION p2,

     PARTITION p3  

);

分表

分表和分区类似,区别是,分区是把一个逻辑表文件分成几个物理文件后进行存储,而分表则是把原先的一个表分成几个表。进行分表查询时可以通过union或者视图。

分表又分垂直分割和水平分割,其中水平分分割最为常用。水平分割通常是指切分到另外一个数据库或表中。例如对于一个会员表,按对3的模进行分割:

table = id%3

如果id%3 = 0 则将用户数据放入到user_0表中,如id%3=1就放入user_1表中,依次类推。

在这里有个问题,这个uid应该是所有会员按序增长的,可他是怎么得到的呢?使用auto_increment是不行的,这样就用到序列了。

对于一些流量统计系统,其数据量比较大,并且对过往数据的关注度不高,这时按年、月、日进行分表,将每日统计信息放到一个以日期命名的表中;或者按照增量进行分表,如每个表100万数据,超过100万就放入第二个表。还可以按Hash进行分表,但是按日期和取模余数分表最为常见,也容易扩展。

分表后可能会遇到新的问题,那就是查询,分页和统计。通用的方法是在程序中进行处理,辅助视图。

相关推荐