Mysql优化-表分区

概念

存储引擎

1）MyISAM存储引擎（不推荐使用）

特点：

1. 不支持事务，表锁(表级锁，加锁会锁住整个表)，支持全文索引，对于一些OLAP(online analytical processing，在线分析处理)操作速度快
2. 官方存储引擎，除windows版本外，是所有mysql版本的默认存储引擎。

设计理念：

• myisam存储引擎表由myd和myi组成。myd用来存放数据文件，myi用来存放索引文件，可以通过myisampack工具进一步压缩 数据文件，因为该工具使用huffman编码静态算法来压缩数据。因此，使用该工具压缩后的表是只读的，当然也可以通过myisampack来解压数据文 件。

2）InnoDB存储引擎 特点：

1. 支持事务，主要面向在线事务处理(OLTP)方面的应用。
2. 行锁设计，支持外键，并支持orcle的非锁定读，即默认情况下读取操作不加锁。
3. 第三方存储引擎，被orcle收购。
4. windows版本默认存储引擎，其他系统mysql默认存储引擎为MyISAM。

设计理念：

• InnoDB默认将数据放到一个逻辑的表空间中，这个表空间就像黑盒子一样由InnoDB自身进行管理。从mysql4.1开始，它可以将每个 InnoDB存储引擎的表单独存放到一个独立的ibd文件中。与orcle类似，InnoDB存储引擎同样可以使用裸设备(row disk)来建立其表空间。
• Myisam优点数据文件和索引文件可以放置在不同的目录，平均分布I/O。缺点是不合适高并发操作，对事务处理(修改和删除操作)的支持较差。
• InnoDB优点是提供了具有提交、回滚和崩溃恢复能力的事务安全，适合高并发操作的事务处理。缺点是处理效率相对Myisam较差并且会占用更多的磁盘空间以保留数据和索引。
• So now, in MySQL 5.7, the storage engine used for a given table is expected to provide its own (“native”) partitioning handler. Currently, only the InnoDB and NDB storage engines do. MySQL 5.7及以上版本中MyISAM引擎已经不支持数据库分区了，所以今早抛弃MyISAM吧
• InnoDB引擎对FULLTEXT索引的支持是MySQL5.6新引入的特性 不要再因为为了支持FULLTEXT而使用MyISAM存储引擎了

如何提升并发写的效率？

已经基于行级锁的话，就没有办法从软件层面提升并发度了，否则会事务冲突。所以思路：行级锁、物理层面提升。

1. 弃用Myisam，改用Innodb，基于索引的行级锁技术，支持操作一张表时，并发的写(注意行级锁的使用，尽量避免表锁）
2. 读写分离，让主库专注于写，让从库专注于读取（物理提升）
3. 数据库分库：把不同的业务拆分到不同的数据库，甚至可以把同一个业务拆分在不同的数据库，引入了编程复杂（根据业务选择对应的子库）和分布式事务的问题（物理提升）

如何提升并发读的效率？

• SQL优化、索引、缓存、参数配置
• 架构调整：分区、分表、分库（读写分离或者业务拆分）

读写分离主从复制的优势

1. 主从复制，解决的是容灾类的问题，容灾需要保证数据库切换的实时性和数据的一致性，主机挂了的时候，可以借助中间件，让从机上升为Master
2. 读写分离，同时提升了数据库单机的读和写的能力，主库负责写和极少部分的即时性要求高的读，从而提升写的性能。从数据库只要负责读，通过二进制日志的形式批量写，并保持数据和主库一致，合作分工，同时提升读写的性能
3. 负载均衡，一主多从下，从库是水平扩展了多个数据库来分摊读的请求（即时性要求不高的读请求），以前一台数据库既负责读又负责写，现在多台数据库分摊读的请求

适用场景：大部分的读操作对数据的实时性要求并没有那么高，一般对时延的容忍在秒级以上 读写分离的本质：用硬件资源和带宽换性能。

分区、分表、分库

数据库分区和分表对比：

• 分表更复杂，但是性能稍微好一点点。但是如果Mysql可以高效的维护各个分区之间的关系的话，其实分表是没有必要的。错误的分表操作，会带来bug
• 分表的性能更好，不需要查询优化器来选择读取哪张表，但是分表编码更复杂，要通过代码指定数据存储到特定的表
• 分区只用操作数据库进行分区操作，代码不需要任何更改

数据库分库(物理层面进行拆分)：

• 读写分离：把读和写进行拆分，优势是没有分布式事务的问题，同时编程简单，通过中间件像操作一个数据库一样
• 不同业务的拆分：编程复杂(根据业务选择对应的数据库)，做关联业务联级操作的时候，有分布式事务的问题
• 一个大业务拆分为若干个子业务：编程复杂，分布式事务更严重

总的来说：优先考虑分区。当分区不能满足需求时，开始考虑分表，合理的分表对效率的提升会优于分区。

表分区

它是一种物理数据库设计技术，MySQL数据库默认使用水平分区。

• 水平分区：对表的行进行分区，不同分组中物理分隔的数据组合在一起，表中的所有列都可以在每个分区找到，维持了表的属性结构。
• 垂直分区：把某些特定的列划分到特定的分区，减少表的宽度，每个分区都保存了其中列所在的行。

分区类型:

• range分区：基于一个给定的连续区间范围(区间要求连续并且不能重叠)，把数据分配到不同的分区
• list分区：类似于range分区，区别在于list分区是居于枚举出的值列表分区，range是基于给定的连续区间范围分区
• hash分区：基于给定的分区个数，把数据分配到不同的分区
• key分区：类似于hash分区

其中最常用的是range和list分区

注意：无论哪种分区，要么你分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包含分区键， 也就是说不能使用主键/唯一键字段之外的其它字段分区

什么时候考虑使用分区？

• 一张表的查询速度已经慢到影响使用的时候。
• SQL经过优化请求时间依旧较长
• 数据量大
• 表中的数据是分段的
• 对数据的操作往往只涉及一部分数据，而不是所有的数据

分区解决的问题

• 和单个磁盘或文件系统分区相比，可以存储更多的数据。
• 优化查询。
  • where 子句中包含分区条件时，可以只扫描必要的分区。
  • 涉及聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需汇总得到结果。
• 对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据。
• 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量。

方案对比

我们在分区表、单库分表和分库分表三种方案之间进行对比如下（需要说明的是分库分表包括单库分表和分库分表）：

P.K.	分区表	单库分表	分库分表
连接数	单库限制	单库限制	无限制
存储能力	8192个分区	单库限制	无限制
不走分片键	全表锁	自研or中间件	自研or中间件
走分片键	性能高	性能高	性能高
并发能力	一般	一般	高
运维成本	低	高	很高
开发成本	低	高	很高
事务	本地事务	本地事务 分布式事务	本地事务 分布式事务

---

通过分区表、单库分表和分库分表三种方案的对比我们发现，单库分表相比分区表完全没有任何优势，它们都会受到单个数据库实例引发的连接数、存储能力、并发能力等的限制。单库分表相对于分区表甚至还会引入一些不必要的麻烦，例如跨分片键的操作，即使这种操作频率很低，但是只要有需求就需要自研或者引入第三方中间件，从而大大增加开发成本和维护成本。而分区表应对这类操作则不需要任何代价，甚至还可以通过引入一个从库给这些系统使用从而防止对核心主库的影响。

分区表和单库分表的并发能力有限，很多宝贵的资源都受到单个实例和服务器的限制，这才是一线互联网公司核心数据不使用分区表的主要原因。例如美团外卖订单表，淘宝订单表等，这些业务都有相同的特点：高并发、海量数据，所以只能选分库分表。所以那些高并发，海量数据场景下才会碰到的问题，例如冷热数据分离，数据归档，扩容等，就不在PK范围之内了。

但是为什么我还是要为分区表正名呢？因为满足高并发、海量数据的大表毕竟是小数公司。很多公司的很多业务表，虽然整个生命周期内也会有几亿，甚至上十亿，但是并不会有高并发的可能，这种业务表就非常适合分区表！毕竟分区表能够满足我们需求的情况下，它的开发成本和维护成本要比分库分表小很多呀！

分区总结

MySQL的分区发展这么多年，从来没见过官方有要将其抛弃的想法。这是因为，在很多特定业务场景下，它的便捷性和对性能的提升是显而易见的。

如果你的业务满足如下的特点，可以大胆尝试使用分区表：

1. 可预估生命周期内数据量在十亿量级，而不是百亿甚至千亿的海量数据；
2. 不会有高并发的可能，即你的用户是有一定局限性的，而不会成为全民爆款；

很多业务非常适合使用分区表，这类大表生命周期内的上限是绝对可以预估在10亿量级以下的，即使这些表将来超过10亿，那起码也是若干年以后的事情。一个方案能抗3~5年那绝对是一个优秀的方案，如果能抗10年，那对于现阶段来说，绝对是一个完美的方案了。

分区的局限

1. 分区不能建全文索引，不论哪个存储引擎（包括MYISAM引擎）。
2. MySql5 开始支持分区功能。
3. 分区不支持外键。
4. 不支持查询缓存 对于分区表来说，查询缓存是不支持的，涉及分区表的查询会自动关闭查询缓存，且不能开启。
5. 不支持查询缓存。
6. 所有分区都要使用同样的引擎。
7. 每一个唯一约束必须包含在分区表的分区键（也包括主键约束）。
8. 除COLUMNS分区外，只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列。
9. 最多支持1024个分区，包含子分区。
10. 能避免查询所有的分区，但仍然锁定了所有分区。
11. 分区函数能使用的函数和表达式有限。 ABS() CEILING() FLOOR() DAY() DAYOFMONTH() DAYOFWEEK() DAYOFYEAR() DATEDIFF() EXTRACT() HOUR() MICROSECOND() MINUTE() MOD() MONTH() QUARTER() SECOND() TIME_TO_SEC() TO_DAYS() WEEKDAY() YEAR() YEARWEEK()
12. 分区键数据类型 分区键必须要么是整型列，要么是整型列表达式。ENUM枚举类型的列不能被作为分区表达式。 但是，这个限制有两个特殊情况：
    • [LINEAR] KEY分区方式，只要不是TEXT或者BLOB类型，其他任何类型列都可以作为分区键。因为MySQL内部的hash算法能够正确处理这些类型。
    • RANGE COLUMNS 或者 LIST COLUMNS 分区方式，可以使用string，DATE和DATETIME类型作为分区列。
13. 分区键不能是一个子查询
14. 临时表和日志表都不能被分区。 CREATE Temporary TABLE t1 ( id INT NOT NULL, uid INT NOT NULL, PRIMARY KEY (id) ) PARTITION BY RANGE (id) ( PARTITION p0 VALUES LESS THAN(5) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN(10) ENGINE = MyISAM );
15. 分区并不能总是改善性能，要进行性能评测。

分区处理 null 值的方式

mysql 不禁止在分区键值上使用 null ， 分区键可能是一个字段或者一个用户定义的额表达式。一般情况下，mysql 的分区把 null 当作零值，或者一个最小值进行处理。

• range 分区中，null 值会被当作最小值来处理；
• list 分区中，null 值必须出现在枚举列表中，否则不被接受；
• hash/key 分区中，null 值会被当作零值来处理。

分区操作

代码语言：javascript复制

-- range添加新分区
alter table <table> add partition(
  partition p4 values less than MAXVALUE
);

-- list添加新分区
alter table <table> add partition(
  partition p4 values in (25,26,28)
);

-- hash重新分区
alter table <table> add partition partitions 4;

-- key重新分区
alter table <table> add partition partitions 4;

-- range重新分区
ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (
  PARTITION p0 VALUES LESS THAN MAXVALUE
);

-- list重新分区
ALTER TABLE <table> REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (
  PARTITION p0 VALUES in (1,2,3,4,5)
);

-- 删除分区不删除数据
alter table <table> remove partitioning;

-- 删除list或者range分区(同时删除分区对应的数据)
alter table <table> drop partition <分区名称>;

删除表的所有分区: 不会丢失数据

代码语言：javascript复制

ALTER TABLE t_question remove partitioning;

删除分区: 丢失数据 不可以删除hash或者key分区。

代码语言：javascript复制

alter table emp drop partition p1;

一次性删除多个分区 丢失数据

代码语言：javascript复制

alter table emp drop partition p1,p2;

增加分区:

代码语言：javascript复制

alter table emp add partition (partition p3 values less than (4000));
alter table empl add partition (partition p3 values in (40));

分解分区: Reorganizepartition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。分解前后分区的整体范围应该一致。 不会丢失数据

代码语言：javascript复制

alter table te
reorganize partition p1 into
(
	partition p1 values less than (100),
	partition p3 values less than (1000)
);

合并分区: Merge分区：把2个分区合并为一个。 不会丢失数据

代码语言：javascript复制

alter table te
reorganize partition p1,p3 into
(
  partition p1 values less than (1000)
);

重新定义hash分区表: 不会丢失数据

代码语言：javascript复制

Alter table emp partition by hash(salary) partitions 7;

重新定义range分区表: 不会丢失数据

代码语言：javascript复制

Alter table emp partition by range(salary)
(
	partition p1 values less than (2000),
	partition p2 values less than (4000)
);

重建分区： 这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。

代码语言：javascript复制

ALTER TABLE emp rebuild partitionp1,p2;

优化分区：

如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，可以使用“ALTER TABLE … OPTIMIZE PARTITION”来收回没有使用的空间，并整理分区数据文件的碎片。

代码语言：javascript复制

ALTER TABLE emp optimize partition p1,p2;

分析分区： 读取并保存分区的键分布。

代码语言：javascript复制

ALTER TABLE emp analyze partition p1,p2;

修补分区： 修补被破坏的分区。

代码语言：javascript复制

ALTER TABLE emp repairpartition p1,p2;

检查分区：

可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。 这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTER TABLE … REPAIR PARTITION”来修补该分区。

代码语言：javascript复制

ALTER TABLE emp CHECK partition p1,p2;

相关查询

代码语言：javascript复制

-- 跨分区查询
select sum(t.num) from
(
	select count(*) num from t_question PARTITION (p0)
	union all
	select count(*) num from t_question PARTITION (p1)
) t;

-- 查询表上有多少分区
SELECT PARTITION_NAME,PARTITION_DESCRIPTION,PARTITION_METHOD,TABLE_ROWS,CREATE_TIME
   FROM information_schema.PARTITIONS
   WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'player_log' 
   ORDER BY partition_description DESC;
  
-- 删除分区表一个分区的数据
alter table table_name truncate partition p5;

RANGE分区

在做分区表时，选择分区的依据字段时要谨慎，需要仔细斟酌这个字段拿来做为分区依据是否合适，这个字段加入到主键中做为复合主键是否适合。

使用range分区时表结构要么没有主键，要么分区字段必须是主键。

可以使用PRIMARY KEY (id,xxx)来将多个字段作为主键。

代码语言：javascript复制

-- 创建分区表
CREATE TABLE `mytable` (
   `id` int(11) NOT NULL ,
   `username` varchar(255) DEFAULT NULL,
   `email` varchar(20)     DEFAULT NULL,
   `age` tinyint(4)        DEFAULT NULL,
   `type` int(11)          DEFAULT NULL,
   `create_time` datetime  DEFAULT CURRENT_TIMESTAMP
   PRIMARY KEY (`id`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (age) (
    PARTITION p0 VALUES LESS THAN (20),
    PARTITION p1 VALUES LESS THAN (40),
    PARTITION p2 VALUES LESS THAN (60),
    PARTITION p3 VALUES LESS THAN (80),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

更新原有表

代码语言：javascript复制

alter table results partition by RANGE (month(ttime)) 
(
  PARTITION p0 VALUES LESS THAN (1),
	PARTITION p1 VALUES LESS THAN (2) , 
 	PARTITION p2 VALUES LESS THAN (3) ,
	PARTITION p3 VALUES LESS THAN (4) , 
 	PARTITION p4 VALUES LESS THAN (5) ,
	PARTITION p5 VALUES LESS THAN (6) , 
 	PARTITION p6 VALUES LESS THAN (7) ,
	PARTITION p7 VALUES LESS THAN (8) , 
 	PARTITION p8 VALUES LESS THAN (9) ,
	PARTITION p9 VALUES LESS THAN (10) , 
	PARTITION p10 VALUES LESS THAN (11),
	PARTITION p11 VALUES LESS THAN (12),
	PARTITION P12 VALUES LESS THAN (13) 
);

LIST分区

代码语言：javascript复制

-- 语法
create table `mytable` (
   `id` int(11) NOT NULL ,
   `username` varchar(255) DEFAULT NULL,
   `email` varchar(20)     DEFAULT NULL,
   `age` tinyint(4)        DEFAULT NULL,
   `type` int(11)          DEFAULT NULL,
   `create_time` datetime  DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
partition by LIST (type) (
  partition p0 values IN (0，1, 2),
  partition p1 values IN (3, 4)
);

更新原有表

代码语言：javascript复制

alter table results partition by LIST (type) 
(
  partition p0 values IN (0，1, 2),
  partition p1 values IN (3, 4)
);

比如试题表中的数据量太大 我根据状态进行分区

代码语言：javascript复制

-- 状态-2已确定题型-1未提交0待审核1已审核2已退回10正在纠错处理12审核中 101docHtml有问题102：ctype值有问题
select status,count(*) num from t_question group by `status`;

alter table t_question partition by LIST (status) 
(
  partition p0 values IN (-2,-1),
	partition p1 values IN (0),
	partition p2 values IN (1),
	partition p3 values IN (2),
	partition p4 values IN (10,12),
	partition p5 values IN (101,102),
	partition p6 values IN (103)
);

HASH分区

HASH分区 的目的将数据按照某列进行hash计算后更加均匀的分散到各个分区。

相比，RANGE分区 和 LIST分区 来说，HASH分区不用明确指定一个给定的列值或者列值集合，只需要基于将要进行HASH分区的列指定一个列值或者表达式，以及指定分区表将要被分割成的分区数量。

定义HASH分区的语句为 PARTITION BY HASH(expr)，其中 expr 是一个整型列（类型为MySQL整型的列）的列名或者返回一个整数的表达式。

如果没有显式添加 PARTITIONS 子句声明需要分割的分区数量，那么默认只会创建一个分区。

代码语言：javascript复制

create table t_p_hash(
	`name` varchar(50),
	age int
)engine=innodb
partition by hash(age)
partitions 4;

因为分区是按照整型列或者整数表达式进行的，这个值本身是离散的，如果对于连续的值进行HASH分区，则可以较好地将数据进行平均分布，例如自增长的主键。

但是需要注意的是，根据自增长主键作HASH分区并不能保证数据就一定会分布均匀。因为在数据插入失败进行了回滚时，当前的自增长主键值依然是增加了的，并且下次插入数据时的主键将会在当前失败了的主键基础上递增。

MySQL数据库还支持 LINEAR HASH分区 ，这可以看做 HASH分区 的一个变种。LINEAR HASH 分区的语法与 HASH分区 的语法大体一致，但是其内部使用的是一个更加复杂的算法来确定新行写入到分区中的位置。

相对于 HASH分区 来说，LINEAR HASH分区 在增加、删除、合并、拆分分区方面更加快捷，有利于处理含有大量数据的表，但是各个分区间数据的分布可能不大均衡。

代码语言：javascript复制

create table t_p_linearhash(
	`name` varchar(50),
	age int
)engine=innodb
partition by linear hash(age)
partitions 4;

常规hash和线性hash对比

常规hash是基于分区个数取模（%）运算。根据余数插入到指定的分区。打算分4个分区，根据id字段来分区。

怎么算出新插入一行数据，需要放到分区1，还是分区4呢? id的值除以4，余下1，这一行数据就分到1分区。

常规hash，可以让数据非常平均的分布每一个分区。比如分为4个取，取余数，余数总是0-3之间的值(总到这几个分区去)。分配打散比较均匀。

但是也是有缺点的：由于分区的规则在创建表的时候已经固定了，数据就已经打散到各个分区。现在如果需要新增分区、减少分区，运算规则变化了，原来已经入库的数据，就需要适应新的运算规则来做迁移。

实际上在分库分表的时候，使用hash方式，也是数据量迁移的问题。不过还好。

针对这个情况，增加了线性hash的方式。

线性HASH(LINEAR HASH)稍微不同点。

• 实际上线性hash算法，就是我们memcache接触到的那种一致性hash算法。使用虚拟节点的方式，解决了上面hash方式分区时，当新增加分区后，涉及到的数据需要大量迁移的问题。也不是不需要迁移，而是需要迁移的数据量小。
• 在技术实现上：线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。
• 线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY”子句中添加“LINEAR”关键字。

两者也有有相同的地方：

• 都是均匀分布的，预先指定n个分区，然后均匀网几个分区上面分布数据。根据一个字段值取hash值，这样得到的结果是一个均匀分布的值。后面添加新的分区多少需要考虑数据迁移。
• 常规HASH和线性HASH，因为都是计算整数取余的方式，那么增加和收缩分区后，原来的数据会根据现有的分区数量重新分布。
• HASH分区不能删除分区，所以不能使用DROP PARTITION操作进行分区删除操作；

考虑以后迁移数据量少，使用线性hash。

KEY分区

KEY分区 和 HASH分区 十分相似，不同之处在于 HASH分区 使用用户定义的函数进行分区，KEY分区 使用MySQL数据库提供的函数进行分区。

例如 InnoDB存储引擎 就是使用内部的 哈希函数 来进行分区。

代码语言：javascript复制

create table t_p_key(
	`name` varchar(50),
	create_time datetime
) engine=innodb
partition by key(create_time)
partitions 4;

KEY分区 也有类似于HASH分区那样的的 LINEAR KEY分区 ，所带来的效果也是一致的。

COLUMNS分区

COLUMNS分区 可以看作是对 RANGE分区 和 LIST分区 的一种加强，可以直接使用非整型的数据进行分区；

分区根据类型直接比较而得，不需要转化为整数。

使用的语法为 RANGE COLUMNS (expr) 和 LIST COLUMNS (expr) ，表达式 expr 不再必须为整型。

值得一提的是， RANGE COLUMNS分区还可以对多个列的值进行分区。

例如直接根据 字符串类型 的列作分区

代码语言：javascript复制

create table t_p_rangecolumns(
	`name` VARCHAR(50)
)engine=innodb
partition by list columns (`name`)(
	partition r1 values in ('张三', '王五'),
	partition r2 values in ((李四', '赵六')
);

创建分区表后，查看明细能够看到分区的表达式为字符串类型的字段 name

再来看下 RANGE COLUMNS 对多列进行分区

代码语言：javascript复制

create table t_p_rangecolumns(
	`name` varchar(50),
age int
)engine=innodb
partition by range columns (`name`, age)(
	partition r1 values less than ('张三', 21),
	partition r2 values less than ((李四', 23),
	partition r3 values less than ( (XVALUE, MAXVALUE)
)

COLUMNS分区支持的数据类型有以下几种

所有的整型类型，如 INT、SMALLINT、TINYINT、BIGINT。

浮点型的FLOAT和DECIMAL则不予支持

日期类型，仅包含 DATE 和 DATETIME

字符串类型，如 CHAR、VARCHAR、BINARY和VARBINARY。

不支持BLOB和TEXT类型

子分区

子分区(subpartition)：是分区表中对每个分区的再次分割，又被称为复合分区，

支持对range和list进行子分区，子分区即可以使用hash分区也可以使用key分区。

复合分区适用于保存非常大量的数据记录。

代码语言：javascript复制

-- 根据年进行分区
-- 再根据天数hash分区
-- 3个range分区(p0,p1,p2)又被进一步分成2个子分区，实际上整个分区被分成了 3 x 2 = 6个分区
create table ts (
	id int, 
	purchased date
) 
partition by range(year(purchased))
subpartition by hash(to_days(purchased)) subpartitions 2 
(
	partition p0 values less than (1990),
	partition p0 values less than (2000),
	partition p0 values less than maxvalue
);

项目案例

代码语言：javascript复制

alter table t_question partition by LIST (status) 
subpartition by key(id) subpartitions 2 
(
  partition p0 values IN (-2,-1),
	partition p1 values IN (0),
	partition p2 values IN (1),
	partition p3 values IN (2),
	partition p4 values IN (10,12),
	partition p5 values IN (101,102),
	partition p6 values IN (103)
);

查询创建的分区

代码语言：javascript复制

SELECT PARTITION_NAME,PARTITION_DESCRIPTION,PARTITION_METHOD,TABLE_ROWS,CREATE_TIME
   FROM information_schema.PARTITIONS
   WHERE TABLE_SCHEMA = 'xhkjedu_question' AND TABLE_NAME = 't_question' 
	 ORDER BY partition_description DESC;

查看是否用到分区

代码语言：javascript复制

explain partitions 
SELECT q.testid,q.handletime,q.docHtml,q.typeTextId,q.typeTextName,q.complexityText,q.count,q.sourcename,q.checkertime,q.scienceOrWord,q.subjectid  
FROM t_testquestion_director d INNER JOIN t_question q 
ON d.Testid=q.testid 
where d.directorid =2586 and q.subjectid=68 and q.deleter=0 and q.status=1 and q.papertype != 13 
order by q.checkertime desc 
limit 0,10;

如何选择适合自己的分区类型

使用分区和分表一样的思想：尽量让数据均匀分散，这样达到分流、压力减小的效果。

如果不能均匀分布，某个分区的操作量特别大，出现单点瓶颈。

虽然4种类型的分区方式。

其实总共两大类，按范围分区和按hash运算分区。

• range范围分区，适合按照范围来切分数据。比如按时间范围分区。
• hash，适合均匀分散数据。使用hash分区，麻烦点是后续增加分区，数据要迁移。有了线性hash分区法，这个迁移量减低了很多。

以用户表为例子，如果要使用分区方案。改使用哪种分区类型呢？ 用户编号不是自增的编号，如果使用范围来分区，各个分区的数据做不到均匀分布的。原因如下：

比如说用户编号为1-200000000的用户分配到p1分区，20000000-40000000分配到p2分区，40000000-60000000分配到p3区，这样类推下去。

由于用户编号不是自增，注册分配到的用户编号，可能是1到2千万之间的数字，也可能是4千万到6千万之间的一个数字。如果被分配到4千万到6千万的数字会更多，那么各个分区给到的数据是不均匀的。

故不好使用范围来分区。

比较好的办法是，使用hash取模，user_id%分区数。数据就可以分散均匀到4个分区去了。

常见错误

今天主要介绍下面这几个错误:

• ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning
• ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table’s partitioning function

主键必须包括表的分区函数中的所有列，一个惟一的索引必须包括表的分区函数中的所有列，是不是不太好理解意思

其实就是这么个意思：表上的每一个唯一性索引必须用于分区表的表达式上（其中包括主键索引）

下面我来举几个例子：

SQL

代码语言：javascript复制

CREATE TABLE t1 (  
     id INT NOT NULL,  
     aid DATE NOT NULL,  
     bid INT NOT NULL,   
     PRIMARY KEY (id)  
)  
PARTITION BY KEY(bid)  
PARTITIONS 10;

[Err] 1503 - A PRIMARY KEY must include all columns in the table’s partitioning function

SQL

代码语言：javascript复制

CREATE TABLE t1 (  
     id INT NOT NULL,  
     aid DATE NOT NULL,  
     bid INT NOT NULL,    
     UNIQUE KEY (id)  
)  
PARTITION BY KEY(bid)  
PARTITIONS 10;

[Err] 1503 - A PRIMARY KEY must include all columns in the table’s partitioning function

SQL

代码语言：javascript复制

CREATE TABLE t1 (  
     id INT NOT NULL,  
     aid DATE NOT NULL,  
     bid INT NOT NULL,   
     PRIMARY KEY (id,bid)  
)  
PARTITION BY KEY(bid)  
PARTITIONS 10;

Query OK, 0 rows affected (0.535 sec)

SQL

代码语言：javascript复制

CREATE TABLE t1 (  
     id INT NOT NULL,  
     aid DATE NOT NULL,  
     bid INT NOT NULL,    
     UNIQUE KEY (id,bid)  
)  
PARTITION BY KEY(bid)  
PARTITIONS 10;

Query OK, 0 rows affected (0.625 sec)

这一组例子说明了如果要用bid作为分区key，则bid至少要包含在主键或者唯一键。

再来看一组例子：

SQL

代码语言：javascript复制

CREATE TABLE t1 (  
     id INT NOT NULL,  
     aid DATE NOT NULL,  
     bid INT NOT NULL,  
     PRIMARY KEY (id,aid),
     UNIQUE KEY (bid)    
)  
PARTITION BY KEY(bid)  
PARTITIONS 10;

[Err] 1503 - A PRIMARY KEY must include all columns in the table’s partitioning function

SQL

代码语言：javascript复制

CREATE TABLE t1 (  
     id INT NOT NULL,  
     aid DATE NOT NULL,  
     bid INT NOT NULL,  
     PRIMARY KEY (id,aid),
     UNIQUE KEY (bid)    
)  
PARTITION BY KEY(aid)  
PARTITIONS 10;

[Err] 1503 - A PRIMARY KEY must include all columns in the table’s partitioning function

这2个分区表是无法建立的，因为没有一个分区键，可以同时属于两个唯一性索引的键。

下面再来看一组例子：

SQL

代码语言：javascript复制

CREATE TABLE t1 (  
     id INT NOT NULL,  
     aid DATE NOT NULL,  
     bid INT NOT NULL,  
     PRIMARY KEY (id,aid,bid)   
)  
PARTITION BY KEY(bid)  
PARTITIONS 10;

Query OK, 0 rows affected (0.475 sec)

SQL

代码语言：javascript复制

CREATE TABLE t1 (  
     id INT NOT NULL,  
     aid DATE NOT NULL,  
     bid INT NOT NULL,  
     UNIQUE KEY (id,aid,bid)   
)  
PARTITION BY KEY(bid)  
PARTITIONS 10;

Query OK, 0 rows affected (0.532 sec)

SQL

代码语言：javascript复制

CREATE TABLE t1 (  
     id INT NOT NULL,  
     aid DATE NOT NULL,  
     bid INT NOT NULL,  
     PRIMARY KEY (id,aid,bid),
     UNIQUE KEY (bid)    
)  
PARTITION BY KEY(bid)  
PARTITIONS 10;

Query OK, 0 rows affected (0.526 sec)

这三个都建立分区表成功了，因为bid同时属于一个或以上的唯一性索引的键

需要注意的问题

1、分区数量并非越多越好

分区表本身有一定的限制，单张表最多是只能有1024个分区的。另一方面，在对分区表进行查询时服务器需要扫描所有分区定义的列表来找到正确的分区，类似这样的线性搜索的效率不高，所以随着分区数的增长，成本会越来越高。

2、查询不能根据表达式来过滤分区

MySQL只能在使用分区函数列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即便这个表达式就是分区函数也不行。

例如使用 year( create_time) 进行查询，实际上便扫描了所有的分区

代码语言：javascript复制

explain partitions
select * from t_p_test where year(create_time) = '2017';

3、NULL值会使分区过滤无效

MySQL数据库允许对NULL值做分区，但是MySQL数据库的分区总是视NULL值小于任何一个非NULL值，不同分区对NULL值的处理也各不相同。

RANGE分区 会将NULL值放入最左边边的分区中， LIST分区 下使用NULL值则必须显式地指出在哪个分区存放，而 HASH分区 和 KEY分区 会将含有NULL值的记录返回为0。

4、分区列必须是唯一索引的一个组成部分

不论创建何种类型的分区，如果建表时没有指定主键、唯一索引，那么可以指定任何一个列为分区列。 但是当表中有主键或者唯一索引时，分区列就必须包含所有主键列和唯一索引列的一个组成部分。另外，分区表中无法使用外键约束。

例如创建表 t_p_test ，指定字段 id 为主键，但是定义分区时使用的是普通的整型列 age

代码语言：javascript复制

create table t_p_test(
	id bigint primary key not null auto_increment,
	`name` varchar(50),
	age int
)engine=innodb
partition by range( age)(
	partition p1 values less than ( 20),
	partition p2 values less than ( 30),
	partition p3 values less than ( MAXVALUE)
);

此时显然是无法成功进行分区的，执行语句后将会得到错误提示

Error Code: 1503. A PRIMARY KEY must include all columns in the table’s partitioning function

如果将分区列改为主键列，则能正常进行分区

代码语言：javascript复制

create table t_p_test(
	id bigint primary key not null auto_increment,
	`name` varchar(50),
	age int
)engine=innodb
partition by range( id)(
	partition p1 values less than (20),
	partition p2 values less than (30),
	partition p3 values less than (MAXVALUE)
);

需要注意的是，在包含唯一索引的表中进行分区，分区列只要求是唯一索引的一个组成部分，不需要整个唯一索引列都是分区列。

例如在表 t_p_test 中存在一个由id、age组成的唯一索引（该表中没有主键），那么在进行分区时可以只使用 id 或者 age 作分区列

代码语言：javascript复制

create table t_p_test(
	id bigint not null auto_increment,
	`name` varchar(50),
	age int,
unique index iname( id, age)
)engine=innodb
partition by range( age)(
	partition p1 values less than ( 20),
	partition p2 values less than ( 30),
	partition p3 values less than ( MAXVALUE)
);

5、索引列和分区列不匹配会导致查询无法过滤分区

正如先前所说，MySQL数据库支持的分区为局部分区，索引是按照分区子表定义的，也就是说每个分区的索引相互独立。当索引列并非分区列时，对索引列进行扫描势必也就需要扫描全部分区。

这里创建分区表，在 name 列上建立索引，同时使用 age 列进行分区

代码语言：javascript复制

create table t_p_test(
	name varchar(50) not null,
	age int,
	index i_uname(name)
)partition by range columns( age)(
	partition p1 values less than ( 18),
	partition p2 values less than (30),
	partition p3 values less than (50),
	partition p4 values less than (maxvalue)
);

insert into t_p_test( name, age) values( '张三', 19), ( '李四', 29), ( '王五', 22), ( '赵六', 14), ( '田七', 71);

分析查询执行计划，能够看到使用 name 字段进行等值查询时使用了索引，但是却扫描了所有的分区

代码语言：javascript复制

explain partitions
select * from t_p_test where name = '张三'；

应该尽量避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件，这一点在多表关联时尤为重要。

6、维护分区的成本可能会很高

新增或者删除分区很快，但并不代表着对分区的维护操作总是很快。在对分区进重组时（更新分区操作），需要创建临时表，然后将数据复制到临时表中，最后再删除原分区；这个操作可能会带来很高的维护成本，特别是在有较为庞大的数据量的情况下。

数据库 sql 全文检索 云数据库SQLServer

0 人点赞