千万级的表如何优化，这是一个很技术性的问题，通常我们的直觉思维会跳到拆分或者数据分区，在这里做一些补充和整理，总结一些与您在这方面的经验，并欢迎您的建议。图片来自Pexels。从一开始脑海里闪过火苗，到不断自我批评，再到后来也参考了一些团队的经验。我整理了下面的大纲。既然要彻底了解这个问题，就必须追本溯源。我把这个问题分为“千万级”、“大表”、“优化”三个部分，也对应图中我们标注的“数据量”、“对象”和“目标”。让我一步步解释，从而给出一系列的解决方案。数据量：几千万几千万其实只是一个感官上的数字，也就是我们印象中的数据量很大。这里需要提炼一下这个概念，因为随着业务和时间的变化，数据量也会发生变化。我们应该用动态的思维来看待这个指标，所以针对不同的场景应该有不同的处理策略。①数据量是千万级，有可能达到亿级甚至更多。通常是一些数据流和日志记录业务。里面的数据会随着时间的增长而逐渐增加。突破千万的门槛，非常容易。②数据量为千万级别，属于比较稳定的数据量。如果数据量比较稳定，通常是一些偏向状态的数据。比如有1000万个用户，那么这些用户的信息在表中都会有对应的一行。数据记录，随着业务的增长，这个量级比较稳定。③数据量几千万，不应该有这么多数据。在这种情况下，我们多半是被动发现的，等发现的时候往往已经晚了。比如你看到一张配置表，有几千万条数据；或者一些表中的数据已经保存了很长时间，99%的数据都是过期数据或者垃圾数据。数据量是一个整体的认识，我们需要对数据有更深入的认识，这就引出了第二部分的内容。对象：数据表数据操作的过程就像数据库中的多条管道，要处理的数据流经这些管道，这些数据的用途和归属是不同的。一般数据按照业务类型分为三种：①流式数据流式数据是无状态的，多个业务之间没有关系，每次业务来都会产生新的文档。比如交易流、支付流，只要能插入新的单据，就可以完成业务。特点是后面的数据不依赖前面的数据，所有数据按时间流向入库。②状态型数据状态型数据是有状态的。多个事务依赖于有状态的数据，必须保证数据的准确性。比如充值的时候，必须拿到原来的余额，才能支付成功。③配置数据这类数据数据量小，结构简单。一般是静态数据，变化频率很低。至此，我们就可以对整体的背景有了一个了解。如果我们要优化的话，其实是要面对这样一个3*3的矩阵。如果我们要考虑表的读写比例（读多写少，读少写多……），那么就会有3*3*4=24种。显然，穷举并没有显示出来，完全没有必要。可以根据不同的数据存储特性和业务特点，指定不同的业务策略。对此，我们采用抓住重点，梳理出一些常见的优化思路，尤其是里面的核心思想，这也是我们整个优化设计的一个标尺，难易程度决定了我们做这件事的动机和风险。至于优化方案，我想用一个面向业务的维度来阐述。目标：优化这个阶段就不得不说到优化方案了。总结的很多，比较全面。整体分为五个部分：其实我们平时说的分库分表等方案只是其中的一小部分，再扩展的话会更丰富。不难理解，我们需要支持的表数据量是几千万，比较大。DBA必须维护多个表。如何在业务发展过程中更好地管理和支持扩张？在保证性能的同时，这些就是摆在我们面前的几座大山。下面分别说说这五类改进方案：标准设计业务层优化架构层优化数据库优化管理优化标准设计这里先说标准设计，不提其他高端设计方案。黑格尔说：秩序是自由的首要条件。在分工协作的工作场景中尤为重要，否则团队之间相互牵制太多，就会出现很多问题。提一下下面的规范，其实只是开发规范的一部分，可以作为参考。规范的本质不是解决问题，而是有效地消除一些潜在的问题。对于千万手表要遵循的规范，我整理了一些细则如下，基本可以涵盖我们常见的一些设计和使用问题。比如表的字段设计不管是什么都是varchar(500)。其实是一种很不规范的实现方式。让我们扩展这些规范。配置说明：MySQL数据库默认使用InnoDB存储引擎。确保字符集设置统一。MySQL数据库相关的系统、数据库、表的字符集都使用UTF8，应用连接、显示等可以设置字符集的地方也统一设置为UTF8字符集。注意：UTF8格式不能存储表情数据，需要使用UTF8MB4，可以在MySQL字符集中设置。8.0默认为UTF8MB4，可根据公司业务情况统一或定制。MySQL数据库的事务隔离级别默认为RR（Repeatable-Read）。建议初始化时统一设置为RC（Read-Committed），更适合OLTP业务。合理规划数据库中的表，控制单表数据量。对于MySQL数据库，建议单表记录数控制在2000W以内。MySQL实例下，数据库和表的数量越少越好；数据库数量一般不超过50个，每个数据库下的数据表数量一般不超过500个（包括分区表）。建表规范：InnoDB禁止使用外键约束，在程序层面可以保证。要存储精确的浮点数，必须使用DECIMAL而不是FLOAT和DOUBLE。在整数定义中不需要定义显示宽度，例如：使用INT而不是INT(4)。ENUM类型已弃用，请改用TINYINT。尽量不要使用TEXT和BLOB类型。如果一定要用，建议把太大的字段或者不常用的大描述性字段拆分到其他表中；另外，禁止使用数据库存储图片或文件。存储年份时使用YEAR(4)，而不是YEAR(2)。建议将字段定义为NOTNULL。建议DBA提供SQL审计工具，建表的规范性需要通过审计工具进行审计。命名约定：库、表和字段都是小写的。库名、表名、字段名、索引名均使用小写字母，以“_”分隔。建议库名、表名、字段名不超过12个字符。（库名、表名、字段名最多支持64个字符，但为了统一规范、便于识别、减少传输量，统一不超过12个字符）库名、表名、字段名是自编的解释性的，不需要添加注释。对象命名规则简要总结如下表，供参考：命名列表索引规范：建议索引命名规则：idx_col1_col2[_colN],uniq_col1_col2[_colN]（字段过长建议缩写）。一个索引的字段数建议不超过5个，单表索引数控制在5个以内。通常建议InnoDB表具有主键列，尤其是在高可用性集群解决方案中是必须的。在构建复合索引时，将选择性高的字段放在最前面。UPDATE、DELETE语句需要根据WHERE条件添加索引。不建议使用%前缀模糊查询，比如LIKE"%weibo"，不能使用索引，会造成全表扫描。合理使用覆盖索引，例如：SELECTemail,uidFROMuser_emailWHEREuid=xx，如果uid不是主键，可以创建覆盖索引idx_uid_email(uid,email)来提高查询效率。避免在索引字段上使用函数，否则在查询时索引会失效。联系DBA确认是否需要修改索引。应用规范：避免使用存储过程、触发器、自定义函数等，容易将业务逻辑和DB耦合在一起，成为后期做分布式方案时的瓶颈。考虑使用UNIONALL，减少UNION的使用，因为UNIONALL不去重，但是排序操作少，速度相对UNION要快。如果不需要去重，先用UNIONALL。考虑使用limitN，而使用limitM，N少一些，尤其是表很大或者M比较大的时候。减少或避免排序。比如groupby语句中不需要排序，可以加上orderbynull。在计算表中的记录数时，使用COUNT(*)而不是COUNT(primary_key)和COUNT(1)。避免对InnoDB表使用COUNT(*)操作。如果统计统计的实时性要求比较强，可以使用Memcache或者Redis。对于非实时统计，可以使用单独的统计表并定期更新。在做字段更改操作（修改列/更改列）时，必须加上原来的评论属性，否则修改后评论会丢失。使用准备好的语句可以提高性能并避免SQL注入。SQL语句中的IN不应包含太多值。UPDATE和DELETE语句必须有明确的WHERE条件。WHERE条件中的字段值需要符合字段的数据类型，避免MySQL隐式类型转换。SELECT和INSERT语句必须明确指定字段名称，并且禁止使用SELECT*或INSERTINTOtable_namevalues()。INSERT语句使用batch(INSERTINTOtable_nameVALUES(),(),()...)提交，value的个数不宜太多。业务层优化业务层优化应该是最赚钱的优化方式，对业务层是完全可见的，主要包括业务拆分，数据拆分，以及两种常见的优化场景（读多写少，读少写多）！①业务拆分业务拆分分为以下两个方面：将混合业务拆分为独立业务，分离状态和历史数据。业务拆分实际上是将混合业务拆分为更清晰的独立业务。业务2...业务独立使得总的业务量还是很大的，但是各个部分相对独立，可靠性还是有保证的。对于状态和历史数据的分离，我可以举个例子来说明。例如：我们有一个表Account，假设用户余额为100，我们需要在数据发生变化后能够追溯数据变化的历史信息。如果更新了账户的状态数据，则加上100的余额，使得余额为200。这个过程可能对应update语句和insert语句。为此，我们可以将其转化为两个不同的数据源，account和account_hist。account_hist中会有两条insert记录，如下：account中有一条update语句，如下：这也是一个很基础的冷热分离，可以大大降低维护的复杂度，提高业务响应的效率。②按日期拆分数据：这种使用方式比较常见，尤其是按日期维度拆分。其实程序层面的改动很小，但是在可扩展性上的收获很大。数据按照日期维度拆分，比如test_20191021。数据按照周和月的维度进行拆分，比如test_201910。数据按季度和年份拆分，如test_2019。使用分区模式：分区模式也是一种常见的使用方式，以后还会有更多的方法，比如hash和range。在MySQL中，我不推荐使用分区表，因为随着存储容量的增长，虽然数据是垂直拆分的，但归根结底，数据其实很难做到水平扩展，MySQL中有更好的扩展方式。③在读多写少的优化场景下使用缓存，利用Redis技术将读请求放在缓存层面，可以大大降低MySQL层面的热点数据查询压力。④优化读少写多的场景对于读少写多的场景，可以进行三步优化：采用异步提交方式。异步对于应用层最直观的作用就是提高性能，产生最少的同步等待。使用队列技术，可以通过队列扩展大量的写请求，实现批量数据写入。降低写入频率很难理解。举个例子：对于业务数据，比如积分，业务优先级略低于金额。如果数据更新过于频繁，可以适当调整数据更新。降低更新频率的范围（例如，从每分钟到10分钟）。例如：更新状态数据，积分为200，如下图：可以转化为，如下图：如果业务数据在短时间内更新过于频繁，这样每分钟更新100条，积分从100到10000，那么可以按时间频率分批提交。例如：更新状态数据，得分为100，如下图：不需要生成100个事务（200条SQL语句）可以转化为2条SQL语句，如下图：对于业务指标，比如更新频率的细节，大家可以根据具体的业务场景来讨论决策。架构层优化架构层优化其实是我们认为技术含量比较高的一种工作。我们需要根据业务场景，在架构层面引入一些新的技巧。①系统水平扩展场景采用中间件技术：可实现数据路由和水平扩展。常见的中间件有MyCAT、ShardingSphere、ProxySQL等。采用读写分离技术：这是对读需求的扩展，更侧重于状态表。在允许一定延迟的情况下，可以采用多副本方式实现读取需求的横向扩展，也可以采用中间件实现，如MyCAT、ProxySQL、MaxScale、MySQLRouter等。使用负载平衡技术：常见的有基于域名服务的LVS技术或Consul技术。②NewSQL可用于兼顾OLTP+OLAP的业务场景，优先考虑兼容MySQL协议的HTAP技术栈，如TiDB。③离线统计业务场景有多种选择：采用NoSQL系统，主要有两种。一种是兼容MySQL协议的数据仓库系统。常见的有Infobright或者ColumnStore，还有一种是基于列存储的，属于异构方向，比如HBase技术。采用数据仓库系统，基于MPP架构，比如使用Greenplum统计，比如T+1统计。数据库优化数据库优化，其实可以打的牌有很多，但是相对来说，篇幅没那么大，下面一一说。①事务优化根据业务场景选择事务模型，是否是强事务依赖。对于事务降维策略，我们举几个小例子。降维策略一：将存储过程调用转换为透明的SQL调用对于新业务来说，使用存储过程显然不是一个好主意。与其他商业数据库相比，MySQL存储过程的功能和性能还有待验证，而且目前在轻量级业务处理中，存储过程的处理方式过于“重”。有些应用架构看起来是分布式部署的，但是数据库层的调用方式是基于存储过程的，因为存储过程封装了大量的逻辑，调试难度大，可移植性低。这样一来，业务逻辑和性能压力都在数据库层面，使得数据库层容易成为瓶颈，难以实现真正的分布式。因此，有一个明确的改进方向就是对存储过程进行改造，将其改造为SQL调用方式，这样可以大大提高业务的处理效率，并且数据库的接口调用足够简单清晰可控。降维策略二：将DDL操作转换为DML操作一些业务往往有向表中添加字段的迫切需求，这让DBA和商科学生非常疲惫。可以想象，一张表有几百个字段，而且基本上是name1,name2...name100，这样的设计本身就有问题，更不用说性能了。原因是业务需求是动态变化的。比如一个游戏装备有20个属性，一个月后可能会增加到40个属性。这样一来，所有装备都有40个属性，不管有没有用。used，而且这种方法有很多冗余。我们还在设计规范中提到了设计的一些基本元素。需要在这些基础上增加的是维护有限的字段。如果要实现这些功能的扩展，其实可以通过配置来实现。比如把一些动态添加的字段翻译成一些配置信息。配置信息可以通过DML进行修改和补充，数据录入也可以更加动态，易于扩展。降维策略三：将删除操作转化为高效操作有些业务需要定期清理一些周期性数据。比如表中的数据只保留一个月，超出时间范围的数据就要清理。而如果表的量级比较大，这种删除操作的成本太高，我们可以有两种解决方案，将删除操作转换成更高效的方式。首先是建立一个基于业务的周期表，比如按照月表、周表、日表的维度进行设计，这样数据清洗是一种相对可控、高效的方式。第二种方案是使用mysqlrename的操作方法。比如一张2000万元的大表，需要清除99%的数据，那么我们可以根据条件，快速筛选补充需要保留的1%的数据，实现“转型”.换位”。②SQL优化其实需要一个相对最小的设计，很多点都包含在规范设计中，如果按照规范去做，几乎离不开的问题都会被排除掉。这里补充几点：SQL语句简化，简化是SQL优化的利器，因为简单，所以优越。尽量避免或杜绝多表复杂关联。大表关联是大表处理的噩梦。一旦打开这个口子，需要关联的需求越来越多，性能优化已经没有回头路了，更何况大表关联是MySQL的软肋，即使HashJoin刚刚上线，也不要像掌握了绝对杀手锏，商业数据库早就存在了，问题还是层出不穷，SQL中尽量避免反连接和半连接，这是优化器的薄弱环节。什么是反连接和半连接？其实更容易理解。例如：notin,notexists是反连接，in,exists是半连接。这个问题出现在千万级的表上，性能相差几个数量级。③索引优化应该是大表优化需要把握的一个度：首先要有主键。规范设计中的第一项是这里不接受反驳。其次，SQL查询基于索引或唯一索引，使查询模型尽可能简单。最后，尽量避免范围数据查询，千万级大表的情况下尽量减少范围扫描。管理优化的部分应该是所有解决方案中最容易被忽视的部分。我把它放在最后。也向运维同仁致敬。很多本该正常的问题，我总是尽力而为（背黑锅）。千万级表的数据清洗一般比较耗时。建议在设计中改进冷热数据分离的策略。听起来可能有点啰嗦。让我举一个例子。转换为可逆的DDL操作。Drop操作默认提交，不可逆。它是数据库操作跑路的代名词。MySQL层面目前还没有对应的Drop操作恢复功能，除非通过备份恢复，但是我们可以考虑将Drop操作转换为一个可逆的DDL操作。默认情况下，MySQL中的每个表都有一个对应的ibd文件。其实Drop操作可以转化为rename操作，即将文件从testdb迁移到testdb_arch。在权限上，testdb_arch对业务是不可见的，rename操作可以顺利实现这个删除功能。如果在一定时间后确认可以清理，则数据清理对现有业务流程是不可见的，如下图所示：另外，还有两个额外的建议。一是大表变更尽量考虑非高峰期在线变更，比如使用pt-osc工具或者维护期间的变更，这里不再赘述。最后总结一下，其实就是一句话：千万级表的优化是基于业务场景，以成本为代价的，绝对不是孤立层面的优化。作者：杨建荣编辑：陶嘉龙、孙淑娟来源：转载自微信公众号杨建荣学习笔记（ID：jianrong-notes）
