mysql优化参考(四)-分区

一、分区概念：

　　mysql目前应该是只支持水平分区，一般的水平分区如果是表，则相当于原来存储在一个文件的表分为多个文件

　　应用场景：

• 单个分区维护更容易
• 文件分布到不同硬件
• 避免瓶颈，比如数据量过大导致文件存储限制或者访问速度问题
  • innoDB单独索引的互斥访问（初步理解是分区把索引也分了，所以基于索引的锁机制在分区场景下可以被复用）
  • ext3文件系统的innode锁竞争

二、分区类型：

• 范围分区
  • 原表：

    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 NOT NULL,
        store_id INT NOT NULL
    );

  • 分区表

    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 NOT NULL,
        store_id INT NOT NULL
    )
    PARTITION BY RANGE (store_id) (
        PARTITION p0 VALUES LESS THAN (6),
        PARTITION p1 VALUES LESS THAN (11),
        PARTITION p2 VALUES LESS THAN (16),
        PARTITION p3 VALUES LESS THAN (21)
    );

    按照一定规则来分区

    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 NOT NULL,
        store_id INT NOT NULL
    )
    PARTITION BY RANGE (job_code) (
        PARTITION p0 VALUES LESS THAN (100),
        PARTITION p1 VALUES LESS THAN (1000),
        PARTITION p2 VALUES LESS THAN (10000)
    );

    maxvalue

    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 RANGE ( YEAR(separated) ) (
        PARTITION p0 VALUES LESS THAN (1991),
        PARTITION p1 VALUES LESS THAN (1996),
        PARTITION p2 VALUES LESS THAN (2001),
        PARTITION p3 VALUES LESS THAN MAXVALUE
    );

    函数分区

    CREATE TABLE quarterly_report_status (
        report_id INT NOT NULL,
        report_status VARCHAR(20) NOT NULL,
        report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )
    PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
        PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
        PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
        PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
        PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
        PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
        PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
        PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
        PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
        PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
        PARTITION p9 VALUES LESS THAN (MAXVALUE)
    );

    CREATE TABLE members (
        firstname VARCHAR(25) NOT NULL,
        lastname VARCHAR(25) NOT NULL,
        username VARCHAR(16) NOT NULL,
        email VARCHAR(35),
        joined DATE NOT NULL
    )
    PARTITION BY RANGE( YEAR(joined) ) (
        PARTITION p0 VALUES LESS THAN (1960),
        PARTITION p1 VALUES LESS THAN (1970),
        PARTITION p2 VALUES LESS THAN (1980),
        PARTITION p3 VALUES LESS THAN (1990),
        PARTITION p4 VALUES LESS THAN MAXVALUE
    );

  • RANGE COLUMNS使用DATE或 DATETIME列作为分区列

    CREATE TABLE members (
        firstname VARCHAR(25) NOT NULL,
        lastname VARCHAR(25) NOT NULL,
        username VARCHAR(16) NOT NULL,
        email VARCHAR(35),
        joined DATE NOT NULL
    )
    PARTITION BY RANGE COLUMNS(joined) (
        PARTITION p0 VALUES LESS THAN ('1960-01-01'),
        PARTITION p1 VALUES LESS THAN ('1970-01-01'),
        PARTITION p2 VALUES LESS THAN ('1980-01-01'),
        PARTITION p3 VALUES LESS THAN ('1990-01-01'),
        PARTITION p4 VALUES LESS THAN MAXVALUE
    );

• 列表分区
  • 和范围分区差不多，主要是这里必须指定具体的值

  • 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)
    );

• 列分割
  
  • 范围列分割
  • 列表列分割
• 哈希分区
  • 基于哈希算法（应该是取模）来分配分区
• Key分区
  • 哈希算法的一种变异，一般是基于主键或唯一键来计算的
• 子分区
  • 子分区是在其他分区的基础上进行再次分区
• mysql分区如何处理null
  • Range：认为是小于任何值
  • List：需要自行指定
  • Hash和Key：等于0

三、分区管理

• 范围分区和列表分区的管理
• 哈希和秘钥分区的管理
• 用表交换分区和子分区
• 分区维护
• 获取有关分区的信息

四、分区修剪

五、分区的限制

• 分区键-主键和唯一键
  • 如果表中包含主键和唯一键，则都必须包含在分区函数或分区列中
  • 分区表无法使用外键约束
• 与存储引擎相关的分区限制

  • 合并存储引擎。  用户定义的分区和MERGE 存储引擎不兼容。使用MERGE存储引擎的表 无法分区。分区表不能合并。

  • 联合存储引擎。 FEDERATED不支持表 分区；无法创建分区 FEDERATED表。

  • CSV存储引擎。 CSV不支持 使用存储引擎的分区表；无法创建分区CSV表。

  • InnoDB存储引擎。  InnoDB外键和MySQL分区不兼容。分区 InnoDB表不能有外键引用，也不能有被外键引用的列。InnoDB具有或由外键引用的表不能被分区。

    InnoDB不支持将多个磁盘用于子分区。（目前仅支持 MyISAM。）

  • 用户定义的分区和NDB存储引擎（NDB群集）。  按KEY（包括 LINEAR KEY）进行分区是NDB存储引擎支持的唯一分区类型 。在正常情况下，在NDB Cluster中无法使用[ LINEAR] 以外的任何分区类型创建NDB Cluster表KEY，并且尝试这样做会失败并显示错误。
  • 异常（不适用于生产环境）：可以通过将newNDB Cluster SQL节点上的系统变量设置为来覆盖此限制 ON。如果选择执行此操作，则应注意[LINEAR] KEY生产环境中不支持使用分区类型以外的表。在这种情况下，您可以创建和使用分区类型不是KEY 或的表LINEAR KEY，但这完全由您自己承担风险。
  • 升级分区表。  执行升级时，必须转储并重新加载由分区划分的表KEY以及使用除存储引擎以外的任何存储引擎的表 NDB。
  • 所有分区使用相同的存储引擎。  分区表的所有分区必须使用相同的存储引擎，并且整个表必须使用相同的存储引擎。另外，如果未在表级别上指定引擎，则在创建或更改分区表时必须执行以下任一操作：
• 与功能有关的分区限制
• 分区和锁定
• 数量限制：一个表最多只能有1024个分区，5.7+可以支持8196
• 分区表达式：早期版本需要是整数或者返回整数，5.5可以直接使用列分区

六、原理

　　由多个底层表组成，底层表由句柄对象标识，可以直接访问各个分区。

• select：打开并锁住所有底层表，优化器先尝试过滤分区，再调用存储引擎接口查询数据
• insert：打开并锁住所有底层表，确定哪个分区接受这条记录，再将记录写入对应底层表
• delete：打开并锁住所有底层表，确定哪个分区有这条记录，再将记录从对应底层表删除
• update：打开并锁住所有底层表，确定哪个分区有这条记录，再将记录从对应底层表进行更新

七、如何使用

• 无索引
• 分离冷热索引
• 参考酒店按城市分区，可以将酒店的数量切割开（没试过，只是个猜想）

八、使用分区需要注意的问题

• null会影响分区过滤-无效
• 分区成本
• 维护成本
• 锁表成本（分区会锁住所有分区的底层表）