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会影响分区过滤-无效
  • 分区成本
  • 维护成本
  • 锁表成本（分区会锁住所有分区的底层表）
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/gabin/p/13729190.html