分区优化

分区表的应用场景

屏幕快照 2021-01-09 上午11.27.12.png

分区表的限制

屏幕快照 2021-01-09 上午11.27.57.png

分区表的原理

前一章节有介绍

分区表的类型

1. 范围分区
   根据列值在给定范围内将行分配给分区
2. 列表分区
   类似于按range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选

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)

);

3. 列分区
   mysql从5.5开始支持column分区，可以认为i是range和list的升级版，在5.5之后，可以使用column分区替代range和list，但是column分区只接受普通列不接受表达式

 CREATE TABLE `list_c` (

 `c1` int(11) DEFAULT NULL,

 `c2` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50500 PARTITION BY RANGE COLUMNS(c1)

(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,

 PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */



 CREATE TABLE `list_c` (

 `c1` int(11) DEFAULT NULL,

 `c2` int(11) DEFAULT NULL,

 `c3` char(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50500 PARTITION BY RANGE COLUMNS(c1,c3)

(PARTITION p0 VALUES LESS THAN (5,'aaa') ENGINE = InnoDB,

 PARTITION p1 VALUES LESS THAN (10,'bbb') ENGINE = InnoDB) */



 CREATE TABLE `list_c` (

 `c1` int(11) DEFAULT NULL,

 `c2` int(11) DEFAULT NULL,

 `c3` char(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50500 PARTITION BY LIST COLUMNS(c3)

(PARTITION p0 VALUES IN ('aaa') ENGINE = InnoDB,

 PARTITION p1 VALUES IN ('bbb') ENGINE = InnoDB) */

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

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 HASH(store_id)
PARTITIONS 4;


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 LINEAR HASH(YEAR(hired))
PARTITIONS 4;

5. key分区
   类似于hash分区，区别在于key分区只支持一列或多列，且mysql服务器提供其自身的哈希函数，必须有一列或多列包含整数值

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

6. 子分区
   在分区的基础之上，再进行分区后存储

CREATE TABLE `t_partition_by_subpart`
(
  `id` INT AUTO_INCREMENT,
  `sName` VARCHAR(10) NOT NULL,
  `sAge` INT(2) UNSIGNED ZEROFILL NOT NULL,
  `sAddr` VARCHAR(20) DEFAULT NULL,
  `sGrade` INT(2) NOT NULL,
  `sStuId` INT(8) DEFAULT NULL,
  `sSex` INT(1) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`, `sGrade`)
)  ENGINE = INNODB
PARTITION BY RANGE(id)
SUBPARTITION BY HASH(sGrade) SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN(5),
PARTITION p1 VALUES LESS THAN(10),
PARTITION p2 VALUES LESS THAN(15)
);

如何使用分区表

1. 全量扫描数据，不要任何索引
   使用简单的分区方式存放表，不要任何索引，根据分区规则大致定位需要的数据为止，通过使用where条件将需要的数据限制在少数分区中，这种策略适用于以正常的方式访问大量数据
2. 索引数据，并分离热点
   如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中，这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效的使用缓存

在使用分区表的时候需要注意的问题

1. null值会使分区过滤无效
2. 分区列和索引列不匹配，会导致查询无法进行分区过滤
3. 选择分区的成本可能很高
4. 打开并锁住所有底层表的成本可能很高
5. 维护分区的成本可能很高