MySQL基础教程(17)分库、分表与分区表

Tanglu MySQL 2020-01-04 1213 0

一、MySQL分库与分区表介绍

1、分库、分表

MySQL的拆分需求主要来自性能、容量、隔离三大问题

· 性能:写请求相应时间增长、DDL时间太长、连接数过多

· 容量:单库备份时间超过1小时,数据库容量大于1T

· 隔离:保证核心业务稳定性,日志表剥离,边缘业务拆分


2、分区表

由于MySQL默认是以文件的形式将表中数据存储在磁盘中,所以当一张表存放大量数据后文件体积会非常大,这样会影响查询效率和数据安全。为了解决这种大表性能问题MySQL提供了分区表功能,通过分区表可以把一张大表拆分为多张逻辑小表,这些小表虽然在磁盘上也被拆分成了多个物理文件(文件名格式为表名+分区名),但是对应用来说是透明的,看上去仍然是一张表(一个表名)。虽然分区表可以解决大表问题,但是它会给维护带来一定的麻烦,比如在对某一个分区做DDL的时候,会导致其它分区出现MDL锁。所以并不推荐因为数据量大而使用分区表,因为MySQL是索引组织表,数据量即便再大,定位记录也只需要3、4 次 I/O。它的本质更多是为了方便OLAP场景下的数据管理,可以考虑分区表的应用场景是需要定期清理历史流水类数据。

下面的一个执行计划可以看出一个原本在普通表上不管记录数再多,最多也只有4次IO的SQL,通过分区后需要访问 4 个分区,假设每个分区需要3次I/O,也要12 次I/O。

SELECT * FROM t WHERE d = 'aaa'
******** 1. row ********
           id: 1
  select_type: SIMPLE
        table: t
   partitions: p2018,p2019,p2020,p2021
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where


二、MySQL分区表优势、限制与建议

· 对于海量数据的维护更方便,可以删除单个分区来清理不需要的数据

· 查询数据不需要全表扫描,只需要查询对应分区即可

· 从5.6开始每张表支持8192个分区

· 进行分区的字段必须是主键或唯一索引列的组成部分。比如要基于order_date列进行分区,那么主键必须包含order_date列(联合主键也可以)

· 不能使用外键约束,不能使用存储过程,不支持全文索引,不支持临时表

· 分区还可以建立子分区,也叫复合分区

· MySQL在第一次打开分区表的时候需要访问所有的分区,需注意open_files_limit参数的限制

· 在server层认为分区表是同一张表,所有分区共用同一个MD 锁;在引擎层认为是不同的表,MDL锁之后的执行过程,会根据分区表规则只访问必要的分区

· 分区并不是越细越好,对于现在的硬件来说,单表数据即便在一千万行,只要没有特别大的索引,也不算大表

· 分区不要提前预留太多,在使用之前预先创建即可。比如按月分区,可以在年底再创建下一年的12个新分区

· 对于没有数据的历史分区,要及时的 drop 掉


三、MySQL分区表类型

range(常用):范围分区,适合按照月份或者编号范围进行分区,比如每10万个ID为一个分区,每个月的数据为一个分区等等。但是存在热点数据集中在一个分区问题,无法提升性能

hash(常用):哈希分区,按照HASH算法将所有数据随机分到每个区,解决range热点数据集中问题

list:列表分区,适合对字符进行分区,比如按省份分区,但是数据不会太均匀

key:HASH分区的一种延伸方式


四、MySQL表分区示例

1、创建orders表时按年创建分区

CREATE TABLE `orders` (
  `ORDERKEY` int NOT NULL,
  `ORDERSTATUS` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `ORDERDATE` date NOT NULL,
  `ORDERPRIORITY` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `COMMENT` varchar(79) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`ORDERKEY`,`ORDERDATE`),
  KEY `idx_orderdate` (`ORDERDATE`)
)
PARTITION BY RANGE  COLUMNS(ORDERDATE)
(
  PARTITION p0000 VALUES LESS THAN ('1992-01-01') ENGINE = InnoDB,
  PARTITION p1992 VALUES LESS THAN ('1993-01-01') ENGINE = InnoDB,
  PARTITION p1993 VALUES LESS THAN ('1994-01-01') ENGINE = InnoDB,
  PARTITION p1994 VALUES LESS THAN ('1995-01-01') ENGINE = InnoDB,
  PARTITION p1995 VALUES LESS THAN ('1996-01-01') ENGINE = InnoDB,
  PARTITION p1996 VALUES LESS THAN ('1997-01-01') ENGINE = InnoDB,
  PARTITION p1997 VALUES LESS THAN ('1998-01-01') ENGINE = InnoDB,
  PARTITION p1998 VALUES LESS THAN ('1999-01-01') ENGINE = InnoDB,
  PARTITION p9999 VALUES LESS THAN (MAXVALUE)



2、为已有表创建分区,已有数据会按照规则自动分配到分区中

CREATE TABLE `ad_unit_cost` (
`user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '关联所属用户', 
`plan_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '关联推广计划    id', 
`unit_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '关联推广单元    id', 
`cost` bigint(20) NOT NULL DEFAULT '0' COMMENT '推广单元花费金额', 
`date_` date NOT NULL COMMENT '数据日期,精确到天,yyyy-MM-dd'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE ad_unit_cost PARTITION BY RANGE (TO_DAYS(date_)) (
PARTITION p0 VALUES LESS THAN (TO_DAYS('2019-01-01')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2019-02-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2019-03-01')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2019-04-01')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2019-05-01')),
PARTITION p5 VALUES LESS THAN (TO_DAYS('2019-06-01')),
PARTITION p6 VALUES LESS THAN (MAXVALUE) );



3、执行结果中可以看到分区表信息,即便在一个分区中进行了全表扫描,影响的行数也比不分区的要小很多

mysql_partition.png


4、通过分区表删除历史数据

#传统删除方式会产生大量binlog,导致主从延迟
DELETE FROM Orders WHERE o_orderdate >= '1998-01-01' AND o_orderdate < '1999-01-01'
#通过分区进行删除只有一条 DDL 日志,TRUNCATE和DROP视情况选择
ALTER TABLE orders_par TRUNCATE PARTITION p1998



5、对已分区的表增加分区

show create table student;
alter table student drop partition student_2020;  #先删除最后一个分区,这个过程不会清空该分区数据,只会重新分配到其它分区中
alter table student add partition (partition student_2020 values less than (2021))  #为2020新增分区
alter table student add partition (partition student_2021 values less than maxvalue)  #再创建一个新的最后分区


MySQL表分区示例——列表分区

create table student (
id int not null,
name varchar(20) not null,
age int not null
)
partition by list(age)
(
partition age_1 values in (20),
partition age_2 values in (30,40)
);


六、MySQL表分区示例——HASH分区

用hash分区可以指定一个分区数量,然后数据会很平均的分配到这些分区中

create table student (
id int not null,
name varchar(20) not null,
age int not null
)
partition by hash(id)
partitions 5  #创建了5个分区,假如插入5W数据,那么每个分区大概就是1W条数据

评论