MySQL分区表

时间 2019-11-08 标签 mysql 分区表

　　当数据库数据量涨到必定数量时，性能就成为咱们不能不关注的问题，如何优化呢？经常使用的方式不外乎那么几种：mysql

　　一、分表，即把一个很大的表达数据分到几个表中，这样每一个表数据都很少。算法

　　　　优势：提升并发量，减少锁的粒度sql

　　　　缺点：代码维护成本高，相关sql都须要改动数据库

　　二、分区，全部的数据还在一个表中，但物理存储数据根据必定的规则存放在不一样的文件中，文件也能够放到另外磁盘上服务器

　　　　优势：代码维护量小，基本不用改动，提升IO吞吐量并发

　　　　缺点：表的并发程度没有增长less

　　三、拆分业务，这个本质仍是分表。函数

　　　　优势：长期支持更好高并发

　　　　缺点：代码逻辑重构，工做量很大性能

　　固然，每种状况都有合适的应用场景，须要根据具体业务具体选择。因为分表和拆分业务和mysql自己关系不大属于业务层面，咱们只说和数据库关系最紧密的方式：表分区。不过使用表分区有个前提就是你的数据库必须支持。那么，怎么知道个人数据库是否支持表分区呢？请执行下面命令　　

show plugins;  ---在mysql控制台中执行

听说5.4一下的版本是另一个命令，不过我没有测试

 show variables like '%part%';

　　数据库的表分区通常有两种方式：纵向和横向。纵向就是把表中不一样字段分到不一样数据文件中。横向是把表中前一部分数据放到一个文件中，另外一部分数据放到一个文件中。mysql只支持后后一种方式，横向拆分。

一、建立分区表

　　若是要使用表的分区优点，不但要数据库版本支持分区，关键要建分区表，这个表和普通表不同，而且必须建表的时候就要指定分区，不然没法把普通表改为分区表。那么，若是建立一个分区表呢？其余很简单，请看下面建表语句

1 CREATE TABLE `T_part` (
2     `f_id` INT DEFAULT NULL,
3     `f_name` VARCHAR (20) DEFAULT NULL,
4     PRIMARY KEY (`f_id`)
5 ) ENGINE = myisam DEFAULT CHARSET = utf8 
6 PARTITION BY RANGE (f_id)(        -----指定分区方式
7     PARTITION p0  VALUES less THAN (10),-- 分了两个区
8     PARTITION p1  VALUES less THAN (20)
9 )

　　上面语句建了一个“T_part”表，有两个字段f_id和f_name，而且根据RANGE方式把表分红两个区p0、p1，当f_id小于10放入p0分区，当f_id大于0小于20放入分区p1. 那么当f_id大于20的数据放入哪一个分区呢？你猜对了，insert语句会报错。

　　看到了吧，建立分区表就这么简单！固然，你随时能够添加删除分区，不过要注意，删除分区的时候会把当前分区下全部数据都删除。

alter table T_part add partition(partition p2 values less than (MAXVALUE));  ---新增分区
alter table T_part DROP partition p2; ----删除分区

二、表分区的几种方式

　　mysql支持5种分区方式：RANGE分区、LIST分区、HASH分区、LINEAR HASH分区和KEY分区。每种分区都有本身的使用场景。

　　1）RANGE分区：

　　　　RANGE分区的表是经过以下一种方式进行分区的，每一个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操做符来进行定义。

　　　　上面的例子就是RANGE分区.

　　2）LIST分区：

　　　　MySQL中的LIST分区在不少方面相似于RANGE分区。和按照RANGE分区同样，每一个分区必须明肯定义。它们的主要区别在于，LIST分区中每一个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。LIST分区经过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，而后经过“VALUES IN (value_list)”的方式来定义每一个分区，其中“value_list”是一个经过逗号分隔的整数列表。

CREATE TABLE `T_list` (
    `f_id` INT DEFAULT NULL,
    `f_name` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION by list(f_id)
(
  PARTITION p0 VALUES in(1,2,3),  ----区间值不能重复
  PARTITION p1 VALUES in(4,5,6)
);

　　3)HASH分区：

　　　　HASH分区主要用来确保数据在预先肯定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪一个分区中；而在HASH分区中，MySQL 自动完成这些工做，你所要作的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它能够仅仅是字段类型为MySQL 整型的一列的名字。此外，你极可能须要在后面再添加一个“PARTITIONS num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。

CREATE TABLE `T_hash` (
    `f_id` INT DEFAULT NULL,
    `f_name` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION BY HASH(f_id) ---能够指定多列
PARTITIONS 4；---分区个数

　　　　“expr”还能够是MySQL 中有效的任何函数或其余表达式，只要它们返回一个既很是数、也非随机数的整数。（换句话说，它既是变化的但又是肯定的）。可是应当记住，每当插入或更新（或者可能删除）一行，这个表达式都要计算一次；这意味着很是复杂的表达式可能会引发性能问题，尤为是在执行同时影响大量行的运算（例如批量插入）的时候。最有效率的哈希函数是只对单个表列进行计算，而且它的值随列值进行一致地增大或减少，由于这考虑了在分区范围上的“修剪”。也就是说，表达式值和它所基于的列的值变化越接近，MySQL就能够越有效地使用该表达式来进行HASH分区。

　　4）LINEAR HASH分区：

　　　　MySQL还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-oftwo）运算法则，而常规哈希使用的是求哈希函数值的模数。线性哈希分区和常规哈希分区在语法上的惟一区别在于，在“PARTITION BY” 子句中添加“LINEAR”关键字.

　　5）KEY分区：

　　　　按照KEY进行分区相似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其余存储引擎的表，服务器使用其本身内部的哈希函数，这些函数是基于与PASSWORD()同样的运算法则。

　　　　KEY分区的语法和HASH语法相似，只是把关键字改为KEY。　　

CREATE TABLE `T_key` (
    `f_id` INT DEFAULT NULL,
    `f_name` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION BY LINEAR key(f_id)
PARTITIONS 3；

　　6）子分区：

　　　　子分区的意思就是在分区的基础上再次分区。且每一个分区必须有相同个数的子分区。

CREATE TABLE `T_part` (
    `f_id` INT DEFAULT NULL,
    `f_name` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`f_id`)
) 
PARTITION BY RANGE (f_id)
SUBPARTITION BY HASH(F_ID)
SUBPARTITIONS 2
(
    PARTITION p0     VALUES         less THAN (10),
    PARTITION p1    VALUES        less THAN (20)
)

　　　　上面语句的意思是，创建两个range分区，每一个分区根据hash有分别有两个子分区，实际上整个表分红2×2=4个分区。固然，要详细定义每一个分区属性也是能够的

CREATE TABLE `T_part` (
    `f_id` INT DEFAULT NULL,
    `f_name` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`f_id`)
) 
PARTITION BY RANGE (f_id)
SUBPARTITION BY HASH(F_ID)
(
    PARTITION p0     VALUES less THAN (10)
    (
        SUBPARTITION s0 
            DATA DIRECTORY = '/disk0/data' 
            INDEX DIRECTORY = '/disk0/idx',
        SUBPARTITION s1 
            DATA DIRECTORY = '/disk1/data' 
            INDEX DIRECTORY = '/disk1/idx'
    ),
    PARTITION p1    VALUES less THAN (20)
    (
        SUBPARTITION s2
            DATA DIRECTORY = '/disk0/data' 
            INDEX DIRECTORY = '/disk0/idx',
        SUBPARTITION s3 
            DATA DIRECTORY = '/disk1/data' 
            INDEX DIRECTORY = '/disk1/idx'
    )
)

　　　　这样能够对每一个分区指定具体存储磁盘。前提磁盘是存在的。

　　MySQL 中的分区在禁止空值（NULL）上没有进行处理，不管它是一个列值仍是一个用户定义表达式的值。通常而言，在这种状况下MySQL 把NULL视为0。若是你但愿回避这种作法，你应该在设计表时不容许空值；最可能的方法是，经过声明列“NOT NULL”来实现这一点。