一种被很多人忽视的大表处理技术
https://mp.weixin.qq.com/s?__biz=MzIwMzY1OTU1NQ==&mid=2247485315&idx=1&sn=dc5d247b5fa4f17d470ad70ef0859e9f&chksm=96cd47cfa1baced94f2acb9c1a744491885f12ce0f486443a2cdcbb01bb98c1c6da42ab58616&mpshare=1&scene=1&srcid=0114r9mvintePTRozCDzQrst&key=74f39ec1fd4ae458e177008d9cd8e1fe8f95097ef2cb9bf440f54776fa7c4e6e267af552c5d108599087487438ae997dbca128f589b707f62db03566868728196e7ed534b540ba853b0215fc1579aff3&ascene=0&uin=MTA2NzUxMDAyNQ%3D%3D&devicetype=iMac+MacBookAir6%2C2+OSX+OSX+10.10.5+build(14F2511)&version=11020012&lang=zh_CN&pass_ticket=ZTinuJ3KLyCyWdibPp8h4%2BGPFKvgmlxfwh07O15xnXuncHUUs4T%2BSgAubJA8pQny ? 先基于下面的SQL和存储过程创建一张分区表,并插入1亿条记录: DROP?TABLE?if?exists?employees_partition;
数据插入完成后给 接下来分别尝试有分片键查询,二级索引(idx_name)查询,无分片键查询这三种非常典型查询,并查看执行计划(并且为了防止查询结果被缓存,每条SQL都加上SQL_NO_CACHE):
由下图可知,有分片键查询的性能简直狂拽吊炸天。而且我们看查询计划,能够选定特定分区p24,并且索引类型也是最优秀的const: query by partition key
由下图可知,二级索引查询查询性能也相当不错,但是条件没有分片键,所以无法选择特定分区,其查询计划显示的目标分区是p0~p31所有32个分区: query by secondary index
由下图可知,条件中既没有分片键,也没有普通索引,这时候查询性能就很差了,查询耗时近39秒,无法用到任何索引,而且目标分区是所有32个分区: query by nothing
笔者基于另一张没有分区,且数据总量也是1亿的表,执行条件不会走索引的SQL,耗时也是令人震惊的30s+: mysql>?select?SQL_NO_CACHE?*?from?employees_nopartition?where?`id`=‘8989898‘;
?
对于分区表,如果查询条件能够避免雷区,即不会有全表扫描查询,或者低效索引查询(这些条件在分库分表上性能也很差)。所有SQL的条件要么有分片键,要么有高效的索引,那么都性能提升是很明显的。 分区表对性能提升如此明显,为什么还是有那么多拒绝分区表的声音,或者说一线互联网公司还是以分库分表为主?笔者在以前的文章《分库分表技术演进&最佳实践-修订篇》中也列举了若干知名互联网公司的分库分表中间件,例如阿里的tddl、cobar,美团的zebra,360的atlas,开源社区的sharding-sphere,mycat等。这是因为分区表本身有诸多的限制,这些公司结合自己的业务特点,分区表完全不能满足自己的需求! 分区表的限制看上去帅气的分区表,MySQL官方列举了好多好多的限制,如下所示:
对于没有使用NDB存储引擎的表来说,分区最大数限制为8192,这个数量包含了子分区数量。
对于分区表来说,查询缓存是不支持的,涉及分区表的查询会自动关闭查询缓存,且不能开启。
InnoDB存储引擎的分区表不支持外键。
即使分区表是InnoDB或者MyISAM存储引擎,全文索引也不被支持。例如执行如下SQL会报错: [Err] 1214 - The used table type doesn‘t support FULLTEXT indexes: DROP?TABLE?if?exists?employees_partition;
但是去掉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枚举类型的列不能被作为分区表达式。但是,这个限制有两个特殊情况:
CREATE?TABLE?rc?(c1?INT,?c2?DATE)
我们都知道创建分区表时,可以为每个分区指定 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%的业务场景是远远达不到这个上限的。
现在应该没有对大表加外键的操作了吧?也基本上没有业务场景需要用到数据库的全文索引吧?有也是瞎搞,不接受反驳。
用这些功能的就更少了,不接受反驳。
用Window作为生产环境服务器的也是极少数,不接受反驳。
这个好像也没啥用,如果真的查询频率很高,为什么不用Redis或者memcache呢?
仔细看看分区键,以及分区键表达式限制,也就那么回事。一些常用的比如选择整型列例如用户ID作为分区键,选择字符串类型列例如订单号作为分区键,选择日期时间作为分区键也都是支持的。所以,那些限制只在极端业务场景才会碰到。 接下来是一些确实有影响的限制。我们在分区表、单库分表和分库分表三种方案之间进行对比如下(需要说明的是分库分表包括单库分表和分库分表):
通过分区表、单库分表和分库分表三种方案的对比我们发现,单库分表相比分区表完全没有任何优势,它们都会受到单个数据库实例引发的连接数、存储能力、并发能力等的限制。单库分表相对于分区表甚至还会引入一些不必要的麻烦,例如跨分片键的操作,即使这种操作频率很低,但是只要有需求就需要自研或者引入第三方中间件,从而大大增加开发成本和维护成本。而分区表应对这类操作则不需要任何代价,甚至还可以通过引入一个从库给这些系统使用从而防止对核心主库的影响。 分区表和单库分表的并发能力有限,很多宝贵的资源都受到单个实例和服务器的限制,这才是一线互联网公司核心数据不使用分区表的主要原因。例如美团外卖订单表,淘宝订单表等,这些业务都有相同的特点:高并发、海量数据,所以只能选分库分表。所以那些高并发,海量数据场景下才会碰到的问题,例如冷热数据分离,数据归档,扩容等,就不在PK范围之内了。 但是为什么我还是要为分区表正名呢?因为满足高并发、海量数据的大表毕竟是小数公司。很多公司的很多业务表,虽然整个生命周期内也会有几亿,甚至上十亿,但是并不会有高并发的可能,这种业务表就非常适合分区表!毕竟分区表能够满足我们需求的情况下,它的开发成本和维护成本要比分库分表小很多呀! 分区总结MySQL的分区发展这么多年,从来没见过官方有要将其抛弃的想法。这是因为,在很多特定业务场景下,它的便捷性和对性能的提升是显而易见的。厮大大说过:没有蹩脚的中间件,只有蹩脚的程序员!我对分区的评价则是:存在即合理! 如果你的业务满足如下的特点,可以大胆尝试使用分区表:
笔者就碰到很多业务非常适合使用分区表,这类大表生命周期内的上限是绝对可以预估在10亿量级以下的,即使这些表将来超过10亿,那起码也是若干年以后的事情。一个方案能抗3~5年那绝对是一个优秀的方案,如果能抗10年,那对于现阶段来说,绝对是一个完美的方案了。 笔者对一个重构为分区表的业务表估算如下: 目前存量数据2kw,日增长4w,即年增长约1500w。
所以,分区表在特定业务场景下,绝对是一个既省时又省力,还能减少以后维护成本的绝佳方案。了解每个技术的优缺点,然后以最小的代价,解决业务的痛点。而不是看着网上一些文章,自己没有经过任何求证,就否定一门技术。说不定在你对她转身离去的时候,你错过了很美丽的风景! ? ? ?
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |