如何优化MySQL千万级大表，我写了6000字的解读
这是学习笔记的第 2138 篇文章数据库
千万级大表如何优化，这是一个颇有技术含量的问题，一般咱们的直觉思惟都会跳转到拆分或者数据分区，在此我想作一些补充和梳理，想和你们作一些这方面的经验总结，也欢迎你们提出建议。 缓存
从一开始脑海里开始也是火光四现，到不断的自我批评，后来也参考了一些团队的经验，我整理了下面的大纲内容。性能优化
既然要吃透这个问题，咱们势必要回到本源，我把这个问题分为三部分:微信
“千万级”，“大表”，“优化”，架构
也分别对应咱们在图中标识的负载均衡
“数据量”，“对象”和“目标”。运维
我来逐步展开说明一下，从而给出一系列的解决方案。 异步
1.数据量：千万级分布式
千万级其实只是一个感官的数字，就是咱们印象中的数据量大。 这里咱们须要把这个概念细化，由于随着业务和时间的变化，数据量也会有变化，咱们应该是带着一种动态思惟来审视这个指标，从而对于不一样的场景咱们应该有不一样的处理策略。函数
1) 数据量为千万级，可能达到亿级或者更高
一般是一些数据流水，日志记录的业务，里面的数据随着时间的增加会逐步增多，超过千万门槛是很容易的一件事情。
2) 数据量为千万级，是一个相对稳定的数据量
若是数据量相对稳定，一般是在一些偏向于状态的数据，好比有1000万用户，那么这些用户的信息在表中都有相应的一行数据记录，随着业务的增加，这个量级相对是比较稳定的。
3) 数据量为千万级，不该该有这么多的数据
这种状况是咱们被动发现的居多，一般发现的时候已经晚了，好比你看到一个配置表，数据量上千万;或者说一些表里的数据已经存储了好久，99%的数据都属于过时数据或者垃圾数据。
数据量是一个总体的认识，咱们须要对数据作更近一层的理解，这就能够引出第二个部分的内容。
2.对象：数据表
数据操做的过程就比如数据库中存在着多条管道，这些管道中都流淌着要处理的数据，这些数据的用处和归属是不同的。
通常根据业务类型把数据分为三种：
（1）流水型数据
流水型数据是无状态的，多笔业务之间没有关联，每次业务过来的时候都会产生新的单据，好比交易流水、支付流水，只要能插入新单据就能完成业务，特色是后面的数据不依赖前面的数据，全部的数据按时间流水进入数据库。
（2）状态型数据
状态型数据是有状态的，多笔业务之间依赖于有状态的数据，并且要保证该数据的准确性，好比充值时必需要拿到原来的余额，才能支付成功。
（3）配置型数据
此类型数据数据量较小，并且结构简单，通常为静态数据，变化频率很低。
至此，咱们能够对总体的背景有一个认识了，若是要作优化，其实要面对的是这样的3*3的矩阵，若是要考虑表的读写比例（读多写少，读少写多...），那么就会是3*3*4=24种，显然作穷举是不显示的，并且也彻底没有必要，能够针对不一样的数据存储特性和业务特色来指定不一样的业务策略。
对此咱们采起抓住重点的方式，把常见的一些优化思路梳理出来，尤为是里面的核心思想，也是咱们整个优化设计的一把尺子，而难度决定了咱们作这件事情的动力和风险。
数据量增加状况 |
数据表类型 |
业务特色 |
优化核心思想 |
优化难度 |
数据量为千万级，是一个相对稳定的数据量 |
状态表 |
OLTP业务方向 |
能不拆就不拆读需求水平扩展 |
**** |
数据量为千万级，可能达到亿级或者更高 |
流水表 |
OLTP业务的历史记录 |
业务拆分，面向分布式存储设计 |
**** |
OLAP业务统计数据源 |
设计数据统计需求存储的分布式扩展 |
*** |
||
数据量为千万级，不该该有这么多的数据 |
配置表 |
通用业务 |
小而简，避免大一统 |
* |
而对于优化方案，我想采用面向业务的维度来进行阐述。
3.目标：优化
在这个阶段，咱们要说优化的方案了，总结的有点多，相对来讲是比较全了。
总体分为五个部分：
其实咱们一般所说的分库分表等方案只是其中的一小部分，若是展开以后就比较丰富了。
其实不难理解，咱们要支撑的表数据量是千万级别，相对来讲是比较大了，DBA要维护的表确定不止一张，如何可以更好的管理，同时在业务发展中可以支撑扩展，同时保证性能，这是摆在咱们面前的几座大山。
咱们分别来讲一下这五类改进方案：
优化设计方案1.规范设计
在此咱们先提到的是规范设计，而不是其余高大上的设计方案。
黑格尔说：秩序是自由的第一条件。在分工协做的工做场景中尤为重要，不然团队之间互相牵制太多，问题多多。
规范设计我想提到以下的几个规范，其实只是属于开发规范的一部份内容，能够做为参考。
规范的本质不是解决问题，而是有效杜绝一些潜在问题，对于千万级大表要遵照的规范，我梳理了以下的一些细则，基本能够涵盖咱们常见的一些设计和使用问题，好比表的字段设计无论三七二十一，都是varchar(500),实际上是很不规范的一种实现方式，咱们来展开说一下这几个规范。
1）配置规范
（1）MySQL数据库默认使用InnoDB存储引擎。
（2）保证字符集设置统一，MySQL数据库相关系统、数据库、表的字符集使都用UTF8，应用程序链接、展现等能够设置字符集的地方也都统一设置为UTF8字符集。
注：UTF8格式是存储不了表情类数据，须要使用UTF8MB4，可在MySQL字符集里面设置。在8.0中已经默认为UTF8MB4，能够根据公司的业务状况进行统一或者定制化设置。
（3）MySQL数据库的事务隔离级别默认为RR（Repeatable-Read），建议初始化时统一设置为RC（Read-Committed），对于OLTP业务更适合。
（4）数据库中的表要合理规划，控制单表数据量，对于MySQL数据库来讲，建议单表记录数控制在2000W之内。
（5）MySQL实例下，数据库、表数量尽量少；数据库通常不超过50个，每一个数据库下，数据表数量通常不超过500个（包括分区表）。
2）建表规范
（1）InnoDB禁止使用外键约束，能够经过程序层面保证。
（2）存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。
（3）整型定义中无需定义显示宽度，好比：使用INT，而不是INT(4)。
（4）不建议使用ENUM类型，可以使用TINYINT来代替。
（5）尽量不使用TEXT、BLOB类型，若是必须使用，建议将过大字段或是不经常使用的描述型较大字段拆分到其余表中；另外，禁止用数据库存储图片或文件。
（6）存储年时使用YEAR(4)，不使用YEAR(2)。
（7）建议字段定义为NOT NULL。
（8）建议DBA提供SQL审核工具，建表规范性须要经过审核工具审核后
3）命名规范
（1）库、表、字段所有采用小写。
（2）库名、表名、字段名、索引名称均使用小写字母，并以“_”分割。
（3）库名、表名、字段名建议不超过12个字符。（库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减小传输量，统一不超过12字符）
（4）库名、表名、字段名见名知意，不须要添加注释。
对于对象命名规范的一个简要总结以下表4-1所示，供参考。
命名列表
对象中文名称 |
对象英文全称 |
MySQL对象简写 |
视图 |
view |
view_ |
函数 |
function |
func_ |
存储过程 |
procedure |
proc_ |
触发器 |
trigger |
trig_ |
普通索引 |
index |
idx_ |
惟一索引 |
unique index |
uniq_ |
主键索引 |
primary key |
pk_ |
4）索引规范
（1）索引建议命名规则：idx_col1_col2[_colN]、uniq_col1_col2[_colN]（若是字段过长建议采用缩写）。
（2）索引中的字段数建议不超过5个。
（3）单张表的索引个数控制在5个之内。
（4）InnoDB表通常都建议有主键列，尤为在高可用集群方案中是做为必须项的。
（5）创建复合索引时，优先将选择性高的字段放在前面。
（6）UPDATE、DELETE语句须要根据WHERE条件添加索引。
（7）不建议使用%前缀模糊查询，例如LIKE “%weibo”，没法用到索引，会致使全表扫描。
（8）合理利用覆盖索引，例如：
（9）SELECT email,uid FROM user_email WHERE uid=xx，若是uid不是主键，能够建立覆盖索引idx_uid_email(uid,email)来提升查询效率。
（10）避免在索引字段上使用函数，不然会致使查询时索引失效。
（11）确认索引是否须要变动时要联系DBA。
5）应用规范
（1）避免使用存储过程、触发器、自定义函数等，容易将业务逻辑和DB耦合在一块儿，后期作分布式方案时会成为瓶颈。
（2）考虑使用UNION ALL，减小使用UNION，由于UNION ALL不去重，而少了排序操做，速度相对比UNION要快，若是没有去重的需求，优先使用UNION ALL。
（3）考虑使用limit N，少用limit M，N，特别是大表或M比较大的时候。
（4）减小或避免排序，如：group by语句中若是不须要排序，能够增长order by null。
（5）统计表中记录数时使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1)；InnoDB表避免使用COUNT(*)操做，计数统计实时要求较强可使用Memcache或者Redis，非实时统计可使用单独统计表，定时更新。
（6）作字段变动操做（modify column/change column）的时候必须加上原有的注释属性，不然修改后，注释会丢失。
（7）使用prepared statement能够提升性能而且避免SQL注入。
（8）SQL语句中IN包含的值不该过多。
（9）UPDATE、DELETE语句必定要有明确的WHERE条件。
（10）WHERE条件中的字段值须要符合该字段的数据类型，避免MySQL进行隐式类型转化。
（11）SELECT、INSERT语句必须显式的指明字段名称，禁止使用SELECT * 或是INSERT INTO table_name values()。
（12）INSERT语句使用batch提交（INSERT INTO table_name VALUES(),(),()……），values的个数不该过多。
优化设计方案2：业务层优化
业务层优化应该是收益最高的优化方式了，并且对于业务层彻底可见，主要有业务拆分，数据拆分和两类常见的优化场景（读多写少，读少写多）
1）业务拆分
ü 将混合业务拆分为独立业务
ü 将状态和历史数据分离
业务拆分实际上是把一个混合的业务剥离成为更加清晰的独立业务，这样业务1，业务2。。。独立的业务使得业务总量依旧很大，可是每一个部分都是相对独立的，可靠性依然有保证。
对于状态和历史数据分离，我能够举一个例子来讲明。
例如：咱们有一张表Account，假设用户余额为100。
咱们须要在发生数据变动后，可以追溯数据变动的历史信息，若是对帐户更新状态数据，增长100的余额，这样余额为200。
这个过程可能对应一条update语句，一条insert语句。
对此咱们能够改造为两个不一样的数据源，account和account_hist
在account_hist中就会是两条insert记录，以下:
而在account中则是一条update语句，以下：
这也是一种很基础的冷热分离，能够大大减小维护的复杂度，提升业务响应效率。
2）数据拆分
2.1 按照日期拆分，这种使用方式比较广泛，尤为是按照日期维度的拆分，其实在程序层面的改动很小，可是扩展性方面的收益很大。
数据按照日期维度拆分，如test_20191021
数据按照周月为维度拆分,如test_201910
数据按照季度，年维度拆分,如test_2019
2.2 采用分区模式，分区模式也是常见的使用方式，采用hash,range等方式会多一些，在MySQL中我是不大建议使用分区表的使用方式，由于随着存储容量的增加，数据虽然作了垂直拆分，可是归根结底，数据其实难以实现水平扩展，在MySQL中是有更好的扩展方式。
2.3 读多写少优化场景
采用缓存，采用Redis技术，将读请求打在缓存层面，这样能够大大下降MySQL层面的热点数据查询压力。
2.4 读少写多优化场景，能够采用三步走：
1) 采用异步提交模式，异步对于应用层来讲最直观的就是性能的提高，产生最少的同步等待。
2) 使用队列技术，大量的写请求能够经过队列的方式来进行扩展，实现批量的数据写入。
3) 下降写入频率，这个比较难理解，我举个例子
对于业务数据，好比积分类，相比于金额来讲业务优先级略低的场景，若是数据的更新过于频繁，能够适度调整数据更新的范围（好比从原来的每分钟调整为10分钟）来减小更新的频率。
例如：更新状态数据，积分为200，以下图所示
能够改造为，以下图所示。
若是业务数据在短期内更新过于频繁，好比1分钟更新100次，积分从100到10000，则能够根据时间频率批量提交。
例如：更新状态数据，积分为100，以下图所示。
无需生成100个事务（200条SQL语句）能够改造为2条SQL语句，以下图所示。
对于业务指标，好比更新频率细节信息，能够根据具体业务场景来讨论决定。
优化设计方案3：架构层优化
架构层优化其实就是咱们认为的那种技术含量很高的工做，咱们须要根据业务场景在架构层面引入一些新的花样来。
3.1.系统水平扩展场景
3.1.1采用中间件技术，能够实现数据路由，水平扩展，常见的中间件有MyCAT，ShardingSphere,ProxySQL等
3.1.2 采用读写分离技术，这是针对读需求的扩展，更侧重于状态表，在容许必定延迟的状况下，能够采用多副本的模式实现读需求的水平扩展，也能够采用中间件来实现，如MyCAT,ProxySQL,MaxScale,MySQL Router等
3.1.3 采用负载均衡技术，常见的有LVS技术或者基于域名服务的Consul技术等
3.2.兼顾OLTP+OLAP的业务场景，能够采用NewSQL，优先兼容MySQL协议的HTAP技术栈，如TiDB
3.3.离线统计的业务场景，有几类方案可供选择。
3.3.1 采用NoSQL体系，主要有两类，一类是适合兼容MySQL协议的数据仓库体系，常见的有Infobright或者ColumnStore，另一类是基于列式存储，属于异构方向，如HBase技术
3.3.2 采用数仓体系，基于MPP架构,如使用Greenplum统计，如T+1统计
优化设计方案4：数据库优化
数据库优化，其实可打的牌也很多，可是相对来讲空间没有那么大了，咱们来逐个说一下。
4.1 事务优化
根据业务场景选择事务模型，是不是强事务依赖
对于事务降维策略，咱们来举出几个小例子来。
4.1.1 降维策略1：存储过程调用转换为透明的SQL调用
对于新业务而言，使用存储过程显然不是一个好主意，MySQL的存储过程和其余商业数据库相比，功能和性能都有待验证，并且在目前轻量化的业务处理中，存储过程的处理方式太“重”了。
有些应用架构看起来是按照分布式部署的，但在数据库层的调用方式是基于存储过程，由于存储过程封装了大量的逻辑，难以调试，并且移植性不高，这样业务逻辑和性能压力都在数据库层面了，使得数据库层很容易成为瓶颈，并且难以实现真正的分布式。
因此有一个明确的改进方向就是对于存储过程的改造，把它改造为SQL调用的方式，能够极大地提升业务的处理效率，在数据库的接口调用上足够简单并且清晰可控。
4.1.2 降维策略2：DDL操做转换为DML操做
有些业务常常会有一种紧急需求，老是须要给一个表添加字段，搞得DBA和业务同窗都挺累，能够想象一个表有上百个字段，并且基本都是name1，name2……name100，这种设计自己就是有问题的，更不用考虑性能了。究其缘由，是由于业务的需求动态变化，好比一个游戏装备有20个属性，可能过了一个月以后就增长到了40个属性，这样一来，全部的装备都有40个属性，无论用没用到，并且这种方式也存在诸多的冗余。
咱们在设计规范里面也提到了一些设计的基本要素，在这些基础上须要补充的是，保持有限的字段，若是要实现这些功能的扩展，其实彻底能够经过配置化的方式来实现，好比把一些动态添加的字段转换为一些配置信息。配置信息能够经过DML的方式进行修改和补充，对于数据入口也能够更加动态、易扩展。
4.1.3 降维策略3：Delete操做转换为高效操做
有些业务须要按期来清理一些周期性数据，好比表里的数据只保留一个月，那么超出时间范围的数据就要清理掉了，而若是表的量级比较大的状况下，这种Delete操做的代价实在过高，咱们能够有两类解决方案来把Delete操做转换为更为高效的方式。
第一种是根据业务创建周期表，好比按照月表、周表、日表等维度来设计，这样数据的清理就是一个相对可控并且高效的方式了。
第二种方案是使用MySQL rename的操做方式，好比一张2千万的大表要清理99%的数据，那么须要保留的1%的数据咱们能够很快根据条件过滤补录，实现“移形换位”。
4.2 SQL优化
其实相对来讲须要的极简的设计，不少点都在规范设计里面了，若是遵照规范，八九不离十的问题都会杜绝掉，在此补充几点：
4.2.1 SQL语句简化，简化是SQL优化的一大利器，由于简单，因此优越。
4.2.2 尽量避免或者杜绝多表复杂关联，大表关联是大表处理的噩梦，一旦打开了这个口子，愈来愈多的需求须要关联，性能优化就没有回头路了，更况且大表关联是MySQL的弱项，尽管Hash Join才推出，不要像掌握了绝对大杀器同样，在商业数据库中早就存在，问题照样层出不穷。
4.2.3 SQL中尽量避免反链接，避免半链接，这是优化器作得薄弱的一方面，什么是反链接，半链接？其实比较好理解，举个例子，not in ,not exists就是反链接，in,exists就是半链接，在千万级大表中出现这种问题，性能是几个数量级的差别。
4.3 索引优化
应该是大表优化中须要把握的一个度。
4.3.1 首先必须有主键，规范设计中第一条就是，此处不接收反驳。
4.3.2 其次，SQL查询基于索引或者惟一性索引，使得查询模型尽量简单。
4.3.3 最后，尽量杜绝范围数据的查询，范围扫描在千万级大表状况下仍是尽量减小。
优化设计方案4：管理优化
这部分应该是在全部的解决方案中最容易被忽视的部分了，我放在最后，在此也向运维同事致敬，老是为不少认为本应该正常的问题尽职尽责（背锅）。
千万级大表的数据清理通常来讲是比较耗时的，在此建议在设计中须要完善冷热数据分离的策略，可能听起来比较拗口，我来举一个例子，把大表的Drop 操做转换为可逆的DDL操做。
Drop操做是默认提交的，并且是不可逆的，在数据库操做中都是跑路的代名词，MySQL层面目前没有相应的Drop操做恢复功能，除非经过备份来恢复，可是咱们能够考虑将Drop操做转换为一种可逆的DDL操做。
MySQL中默认每一个表有一个对应的ibd文件，其实能够把Drop操做转换为一个rename操做，即把文件从testdb迁移到testdb_arch下面；从权限上来讲，testdb_arch是业务不可见的，rename操做能够平滑的实现这个删除功能，若是在必定时间后确承认以清理，则数据清理对于已有的业务流程是不可见的，以下图所示。
此外，还有两个额外建议，一个是对于大表变动，尽量考虑低峰时段的在线变动，好比使用pt-osc工具或者是维护时段的变动，就再也不赘述了。
最后总结一下，其实就是一句话：
千万级大表的优化是根据业务场景，以成本为代价进行优化的，绝对不是孤立的一个层面的优化。
近期热文：
我的新书 《MySQL DBA工做笔记》
我的公众号：jianrong-notes
QQ群号：763628645
QQ群二维码以下，我的微信号：jeanron100, 添加请注明：姓名+地区+职位，不然不予经过