别看不起分区表：我要为你点个赞

程序猿DD

发布于 2019-03-08 11:52:50

3950

发布于 2019-03-08 11:52:50

文章被收录于专栏：程序猿DD

来源：阿飞的博客

分区表带来的性能提升

我们先基于下面的SQL和存储过程创建一张分区表，并插入1亿条记录：

DROP TABLE if exists employees_partition;
CREATE TABLE if not exists `employees_partition` (
  `id` int(11) NOT NULL ,
  `name` varchar(32) DEFAULT NULL COMMENT '员工姓名',
  `job_no` varchar(16) NOT NULL COMMENT '员工工号',
   UNIQUE key(job_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY (job_no) PARTITIONS 32 ;

-- 创建存储过程
DROP PROCEDURE IF EXISTS insertemployees;
CREATE PROCEDURE insertemployees()
BEGIN
DECLARE i INT;
  SET i=1;
  WHILE(i<=100000000) DO
    insert into employees_partition values(i, CONCAT(i, '-NAME'), CONCAT('NO.', i));
    SET i=i+1; 
  END WHILE;
END;

-- 调用存储过程
call insertemployees();

数据插入完成后，还给name列加上索引。

接下来分别尝试有分片键查询，二级索引（idx_name）查询，无分片键查询这三种非常典型查询，并查看执行计划（并且为了防止查询结果被缓存，每条SQL都加上SQL_NO_CACHE）：

有分片键查询

由下图可知，有分片键查询的性能简直狂拽吊炸天。而且我们看查询计划，能够选定特定分区p24，并且索引类型也是最优秀的const：

二级索引查询

由下图可知，二级索引查询查询性能也相当不错，但是条件没有分片键，所以无法选择特定分区，其查询计划显示的目标分区是p0~p31所有32个分区：

说明：二级索引查询具体查询性能与索引列的可选性有很大的关系，由于笔者构造的索引列的可选性为1，所以查询性能很好。如果是一个状态列，1亿条数据不同的值只有不到10个，那查询性能就要差很多了，不止是分区表，普通表也是如此。

无分片键查询

由下图可知，条件中既没有分片键，也没有普通索引，这时候查询性能就很差了，查询耗时近39秒，无法用到任何索引，而且目标分区是所有32个分区：

说明：事实上不止分区表，就是普通表，这种查询性能也是极差的，因为需要全表扫描。

笔者基于另一张没有分区，且数据总量也是1亿的表，执行条件不会走索引的SQL，耗时也是令人震惊的30s+：

mysql> select SQL_NO_CACHE * from employees_nopartition where `id`='8989898';
+---------+--------------+------------+
| id      | name         | job_no     |
+---------+--------------+------------+
| 8989898 | 8989898-NAME | NO.8989898 |
+---------+--------------+------------+
1 row in set, 1 warning (30.78 sec)

mysql> show create table employees_nopartition\G
*************************** 1. row ***************************
       Table: employees_nopartition
Create Table: CREATE TABLE `employees_nopartition` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL COMMENT '员工姓名',
  `job_no` varchar(16) NOT NULL COMMENT '员工工号',
  UNIQUE KEY `job_no` (`job_no`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

总结

对于分区表，如果查询条件能够避免雷区，即不会有全表扫描查询，或者低效索引查询（这些条件在分库分表上性能也很差）。所有SQL的条件要么有分片键，要么有高效的索引，那么都性能提升是很明显的。

分区表对性能提升如此明显，为什么还是有那么多拒绝分区表的声音，或者说一线互联网公司还是以分库分表为主？笔者在以前的文章《分库分表技术演进&最佳实践-修订篇》中也列举了若干知名互联网公司的分库分表中间件，例如阿里的tddl、cobar，美团的zebra，360的atlas，开源社区的sharding-sphere，mycat等。这是因为分区表本身有诸多的限制，这些公司结合自己的业务特点，分区表完全不能满足自己的需求！

分区表的限制

看上去帅气的分区表，MySQL官方列举了好多好多的限制，如下所示：

分区最大数

对于没有使用NDB存储引擎的表来说，分区最大数限制为8192，这个数量包含了子分区数量。

不支持查询缓存

对于分区表来说，查询缓存是不支持的，涉及分区表的查询会自动关闭查询缓存，且不能开启。

InnoDB分区表不支持外键

InnoDB存储引擎的分区表不支持外键。

全文索引

即使分区表是InnoDB或者MyISAM存储引擎，全文索引也不被支持。例如执行如下SQL会报错: [Err] 1214 - The used table type doesn't support FULLTEXT indexes：

DROP TABLE if exists employees_partition;
CREATE TABLE if not exists `employees_partition` (
  `id` int(11) NOT NULL ,
  `uname` varchar(32) DEFAULT NULL COMMENT '员工姓名',
  `job_no` varchar(16) NOT NULL COMMENT '员工工号',
  FULLTEXT (`uname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY(uname) PARTITIONS 32 ;

但是去掉PARTITION BY KEY(uname) PARTITIONS 32 ;则OK。

空间列

一些POINT或者GEOMETRY这样的空间数据类型列，不能被用在分区表中。例如在分区表中定义一个名为geo的空间类型列：geo GEOMETRY; 或者geo POINT; 会报错：[Err] 1178 - The storage engine for the table doesn't support GEOMETRY。如果不是分区表，则能成功创建该表。

临时/日志表

临时表和日志表都不能被分区。对日志表中执行 ALTER TABLE … PARTITION BY …会报错。

算术&逻辑运算符

分区表达式中可以使用+, -, * 这些运算符。但是位运算符例如|, &, ^, <<, >>, 和 ~ 是不支持的。例如PARTITION BY HASH(id+1) PARTITIONS 32 是支持的，但是PARTITION BY HASH(id<<1) PARTITIONS 32 则不支持。此外，分区表达式还有很多的内置函数不支持，分区表达式支持的内置函数可参考：https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-functions.html

分区键数据类型

分区键必须要么是整型列，要么是整型列表达式。ENUM枚举类型的列不能被作为分区表达式。但是，这个限制有两个特殊情况：

[LINEAR] KEY分区方式，只要不是TEXT或者BLOB类型，其他任何类型列都可以作为分区键。因为MySQL内部的hash算法能够正确处理这些类型。PARTITION BY KEY(uname) PARTITIONS 32 是可以的，PARTITION BY HASH(uname) PARTITIONS 32 则不行。
RANGE COLUMNS 或者 LIST COLUMNS 分区方式，可以使用string，DATE和DATETIME类型作为分区列，例如下面的SQL什么是有效的:

CREATE TABLE rc (c1 INT, c2 DATE)
PARTITION BY RANGE COLUMNS(c2) (
    PARTITION p0 VALUES LESS THAN('1990-01-01'),
    PARTITION p1 VALUES LESS THAN('1995-01-01'),
    PARTITION p2 VALUES LESS THAN('2000-01-01'),
    PARTITION p3 VALUES LESS THAN('2005-01-01'),
    PARTITION p4 VALUES LESS THAN(MAXVALUE)
);

CREATE TABLE lc (c1 INT, c2 CHAR(1))
PARTITION BY LIST COLUMNS(c2) (
    PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'),
    PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'),
    PARTITION p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL)
);

Window系统不支持DATA DIRECTORY和INDEX DIRECTORY

我们都知道创建分区表时，可以为每个分区指定 DATA DIRECTORY 和 INDEX DIRECTORY。但是Window系统或者MyISAM的子分区是不支持该语法的。

单数据库实例&服务器资源

分区表归根结底是在一个数据库实例上。那么它就会受到单数据库实例的连接数限制、 IO瓶颈、 swap空间、 FD等诸多限制。

分区限制参考：22.6 Restrictions and Limitations on Partitioning

https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html#id3385325

分区PK.分库分表

看到这么多的限制，不要慌张。毕竟任何一项都有优缺点，没有银弹。我们先对分区表一些我认为完全可以接受的限制做一个说明。

分区最大数

8192个分区数限制，虽然不像分库分表可以无限制扩容下去，但是即使按照单表千万的行业标准，也能妥妥的容纳几百亿的的数据。除了淘宝订单，头条评论这种海量数据，我相信99%的业务场景是远远达不到这个上限的。

全文索引&InnoDB分区表不支持外键

现在应该没有对大表加外键的操作了吧？也基本上没有业务场景需要用到数据库的全文索引吧？有也是瞎搞，不接受反驳。

空间列&临时表&日志表

用这些功能的就更少了，不接受反驳。

Window系统不支持DATA DIRECTORY和INDEX DIRECTORY

用Window作为生产环境服务器的也是极少数，不接受反驳。

不支持查询缓存

这个好像也没啥用，如果真的查询频率很高，为什么不用Redis或者memcache呢？

分区键限制

仔细看看分区键，以及分区键表达式限制，也就那么回事。一些常用的比如选择整型列例如用户ID作为分区键，选择字符串类型列例如订单号作为分区键，选择日期时间作为分区键也都是支持的。所以，那些限制只在极端业务场景才会碰到。

接下来是一些确实有影响的限制。我们在分区表、单库分表和分库分表三种方案之间进行对比如下（需要说明的是分库分表包括单库分表和分库分表）：

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

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

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

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