MySQL InnoDB第四章：分区RANGE、HASH、KEY、COLUMNS和子分区SUBPARTITION

r囧r小猫 2021-12-23 22:03 357阅读 0赞

### **4.8.1分区概述** ###

1、MySQL的分区  
    ①MySQL数据库支持的分区类型为水平分区，不支持垂直分区。水平分区：指将同一表中不同行的记录分配到不同的物理文件中。垂直分区：不同列  
    ②MySQL数据库的分区是局部分区索引，一个分区既存放了数据又存放了索引。全局分区是指数据存放在各个分区中，但是所有数据的索引放在一个对象中  
2、查看当前数据库是否分区  
    ①SHOW VARIABLES LIKE '%partition%'\\G  
    ②SHOW PLUGINS\\G：这个命令主要看\*\*\*\*\*\*2.row\*\*\*\*\*\*\*这部分

3、分区优点  
    ①错误的饿观点：只要启用了分区，数据库就会运行得更快  
    ②分区可能会给某些SQL语句性能带来提高，但是分区主要用于数据库高可用性的管理  
4、MySQL数据库支持的分区  
    ①RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区（常用）  
    ②LIST分区：类似RANGE分区类型，只是LIST分区面向的是离散的值  
    ③HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。  
    ④KEY分区：根据MySQL数据库提供的哈希函数来进行分区

5、创建分区规则  
    ①如果建表语句中指定了唯一索引，但没有指定主键，那么分区列（分区字段）必须是唯一索引之一  
    ②如果建表语句中指定了主键（主键都not null），那么不管有没有指定唯一索引，分区列都必须是主键之一（有时候会有复合主键）  
    ③如果建表语句中没有指定主键或者唯一索引，那么可以指定任何一个列（字段）为分区  
    ④以上只针对唯一索引，单纯的索引不影响任何规则

6、查看每个分区的具体信息：select \* from information\_schema.partitions where table\_schema=database() and table\_name='xxx表名'\\G  
   查看表的分区名、分区描述和记录的数量：select table\_name,partition\_name,partition\_description,table\_rows from information\_schema.partitions where table\_schema=database() and table\_name='xxx表名'\\G  
   移除分区：ALTER TABLE xxx表名（已分区的表） REMOVE PARTITIONS;

## ##

### 4.8.2 分区类型 ###

如果创建了分区，那么不能直接删除全部的分区。删除表才能删除全部的分区，不然至少要留下一个分区

1、RANGE分区：主要用于日期列的分区  
    ①EXPLAIN：使用EXPLAIN关键字可以模拟SQL优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。   
    ➤ 通过EXPLAIN，我们可以分析出以下结果：  
        表的读取顺序  
        数据读取操作的操作类型  
        哪些索引可以使用  
        哪些索引被实际使用  
        表之间的引用  
        每张表有多少行被优化器查询  
    ②Partition Pruning分区修剪：查询条件限制在某个分区，那么SQL优化器就只会在那个分区搜索，而不会搜索所有分区，有助于提高查询速度  
    ③VALUES LESS THAN (xxx分区条件)：定义分区区间,非整形的范围值需要使用单引号，并且可以使用MAXVALUE作为分区的最高值  
    ④分区条件：在5.7版本中DATE、DATETIME列、YEAR()、TO\_DAYS()、TO\_SECONDS()、UNIX\_TIMESTAMP()等函数

2、LIST分区  
    ①RANGE分区，分区列的值是连续的。LIST分区，分区列的值是离散的  
    ②RANGE分区定义分区区间：VALUES LESS THAN，LIST分区定义分区区间：VALUES IN  
    ③INSERT插入多行数据的过程中遇到分区未定义的值时候  
        MyISAM:MyISAM引擎会将之前的行数据都插入（正确分区区间的值），但之后的数据不会插入（不在区间范围的值及其之后的值）  
        InnoDB：InnoDB引擎会将其视为一个事务，遇到分区未定义的值会把所有数据回滚，因此不会插入任何值

3、HASH分区  
    ①HASH分区的目的是将数据均匀的分布到预定义的各个分区中，保证各分区的数据数量大致一样  
        注意：即使是根据自增长主键进行的HASH分区也不能保证分区数据的均匀（只能保证比较均匀），因为插入的自增长ID并非总是连续的，如果该主键值因为某种原因被回滚了，则该值将不会再次被自动使用  
    ②RANGE和LIST分区，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中。HASH分区：MySQL自动的完成这些工作，用户所要做的只是基于将要进行哈希分区的列值指定一个列值或表达式，  
以及指定被分区的表将要被分割成的分区数量  
    ③PARTITION BY HASH(expr)：expr是一个返回一个整数的表达式。它可以是表的某一个字段，或者其他表达式  
    ④PARTITIONS num：num是一个非负整数，表示将要被分割成分区的数量。如果建表或者修改表的时候没有加上这一句，那么这个表的分区的数量就默认为1  
    LINEAR HASH分区：语法跟HASH分区相似，只是改了关键字。LINEAR HASH分区的判断方法：  
        1)取大于分区数量num的下一个2的幂值V，V=POWER(2,CEILING(LOG(2,num分区数量)))=num  
        2)所在分区N=expr & (V-1) = 2  
    这个LINEAR HASH分区不太理解这个分区方式。。。

4、KEY分区  
    ①KEY分区和HASH分区语法相似，只是修改了关键字为KEY  
    ②HASH分区使用用户定义的函数进行分区，KEY分区使用MySQL数据库提供的函数进行分区  
    ③在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的效果，分区的编号是通过2的幂算法得到的，而不是通过模数算法

5、COLUMNS分区  
    ①RANGE、LIST、HASH和KEY分区条件：数据必须是整型，如果不是整型，就需要通过函数将其转化为整型，如YEAR()、TO\_DAYS()、MONTH()等函数  
    ②COLUMNS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较得到，不需要转化为整型  
    ③对于RANGE COLUMNS分区，可以对多个列的值进行分区。就是说expr可以为多个表的字段，中间逗号分开  
    ④COLUMNS分区支持的数据类型  
        1)所有的整数类型，FLOAT和DECIMAL不支持  
        2)日期类型，如DATE和DATETIME，其它的不支持  
        3)字符串类型，BLOB和TEXT不支持  
    ⑤使用方法，在原来分区关键字之后添加COLUMNS关键字

## ##

### 4.8.3 子分区 ###

①子分区（subpartitioning）是在分区的基础上再进行分区，有时也称这种分区为复合分区（composite partitioning）  
    ②MySQL数据库允许在RANGE和LIST分区上再进行HASH或KEY的子分区，注意关键字是SUBPARTITIONS，如

create table t( a  int, b date )engine=innodb 
            PARTITION BY RANGE ( (YEAR(b)) )
            SUBPARTITION BY HASH( TO_DAYS(b) )
            SUBPARTITIONS 2(
                PARTITION p0 VALUES LESS THAN (1990),
                PARTITION P1 VALUES LESS THAN (2000),
                PARTITION P2 VALUES LESS THAN MAXVALUE);

代码解释：表t先根据b列进行了RANGE分区，然后又进行了一次HASH分区，所以分区数量为（3\*2=6）个。现在的子分区名字是没有指出的，如果想显示的指出，方法如下：

create table t( a  int, b date )engine=innodb 
            PARTITION BY RANGE ( (YEAR(b)) )
            SUBPARTITION BY HASH( TO_DAYS(b) )
            PARTITION p0 VALUES LESS THAN (1990)(
               SUBPARTITION s0,
               SUBPARTITION s1
            ),
            PARTITION P1 VALUES LESS THAN (2000)(
               SUBPARTITION s2,
               SUBPARTITION s3
            ),
            PARTITION P2 VALUES LESS THAN MAXVALUE)(
               SUBPARTITION s4,
               SUBPARTITION s5
            );

意关键字是SUBPARTITION，没有S结尾的。  
    ③每个子分区的数量必须相同  
    ④要在一个分区表的任何分区上使用SUBPARTITION来明确定义任何子分区，就必须定义所有的子分区  
    ⑤每个SUBPARTITION子句必须包括子分区的一个名字  
    ⑥子分区的名字必须是唯一的  
    ⑦子分区可以用于特别大的表，在多个磁盘间分布分配数据和索引  
        注意：InnoDB存储引擎使用表空间自动的进行数据和索引的管理，因此会忽略DATA DIRECTORY和INDEX DIRECTORY语法。在分配数据和索引的时候，可以改用MyISAM引擎

## ##

### 4.8.4 分区中的NULL值 ###

①MySQL数据库的分区视NULL值小于任何的一个非NULL值，和数据库中处理NULL值得ORDER BY操作一样  
    ②RANGE分区，如果插入NULL值，则MySQL数据库会将该值放入最左边的分区。如果删除分区，NULL值得记录也会被删除  
    ③LIST分区，要显示指出哪个分区expr允许放入NULL值，否则会报错  
    ④HASH和KEY分区对于NULL的处理方式一样，任何分区函数都会将含有NULL值得记录返回为0（意思就是把NULL当做0去插入分区）

### 4.8.5 分区和性能 ###

①数据库的应用分为两类：  
        OLTP：在线事务处理，如Blog、电子商务、网络游戏  
        OLAP：在线分析处理，如数据仓库、数据集市  
    ②OLAP应用，分区可以提高查询的性能。OLTP应用：分区不一定能提高性能，使用InnoDB存储引擎作为OLTP应用的表时候要小心，设计时确认数据的访问模式，否则不仅不会提高查询性能，反而使应用执行更慢  
    ③对于一张大表，一般的B+树需要2-3次磁盘IO

### 4.8.6 在表和分区间交换数据 ###

①将分区或子分区中的数据与另一个非分区的表中的数据进行交换。  
        如果非分区表中的数量为空，那么相当于将分区中的数据移动到非分区表中。  
        若分区表的数据为空，则相当于将外部表中的数据导入到分区中  
        语法：ALTER TABLE xxx表名（已分区的表） EXCHANGE PARTITION xxx分区名 WITH　TABLE xxx表名（未分区的表）  
        使用条件：  
            1)要交换数据的表和分区表有相同的表结构，但是表中没有分区，创建一个与分区表结构相同的表，语句：CREATE TABLE xxx表名（未分区的表） LIKE xxx表名（已分区的表）;  
            2)在非分区表中的数据必须在交换的分区定义内（非分区表的数据所属字段是分区表的expr的）  
            3)被交换的表中不能含有外键，或者其他的表含有对该表的外键引用  
            4)用户除了需要ALTER、INSERT、CREATE、权限外，还需要DROP的权限  
            注意：①使用该语句时，不会触发交换表和被交换表上的触发器  
                ②AUTO\_INCREMENT列会被重置