如何优化MySQL千万级⼤表，5个⽅案
千万级⼤表如何优化，这是⼀个很有技术含量的问题，通常我们的直觉思维都会跳转到拆分或者数据分区，在此做了⼀些补充和梳理，想和⼤家做⼀些这⽅⾯的经验总结，整理了下⾯的⼤纲内容。
既然要吃透这个问题，我们势必要回到本源，我把这个问题分为三部分:
“千万级”，“⼤表”，“优化”，
也分别对应我们在图中标识的
“数据量”，“对象”和“⽬标”。
我来逐步展开说明⼀下，从⽽给出⼀系列的解决⽅案。
1.数据量：千万级
千万级其实只是⼀个感官的数字，就是我们印象中的数据量⼤。这⾥我们需要把这个概念细化，因为随着业务和时间的变化，数据量也会有变化，我们应该是带着⼀种动态思维来审视这个指标，从⽽对于不同的场景我们应该有不同的处理策略。
1) 数据量为千万级，可能达到亿级或者更⾼
通常是⼀些数据流⽔，⽇志记录的业务，⾥⾯的数据随着时间的增长会逐步增多，超过千万门槛是很容易的⼀件事情。
2) 数据量为千万级，是⼀个相对稳定的数据量
如果数据量相对稳定，通常是在⼀些偏向于状态的数据，⽐如有1000万⽤户，那么这些⽤户的信息在表中都有相应的⼀⾏数据记录，随着业务的增长，这个量级相对是⽐较稳定的。
3) 数据量为千万级，不应该有这么多的数据
这种情况是我们被动发现的居多，通常发现的时候已经晚了，⽐如你看到⼀个配置表，数据量上千万；或者说⼀些表⾥的数据已经存储了很久，99%的数据都属于过期数据或者垃圾数据。
数据量是⼀个整体的认识，我们需要对数据做更近⼀层的理解，这就可以引出第⼆个部分的内容。
2.对象：数据表
数据操作的过程就好⽐数据库中存在着多条管道，这些管道中都流淌着要处理的数据，这些数据的⽤处和归属是不⼀样的。
⼀般根据业务类型把数据分为三种：
（1）流⽔型数据redis支持的五种数据类型
流⽔型数据是⽆状态的，多笔业务之间没有关联，每次业务过来的时候都会产⽣新的单据，⽐如交易流⽔、⽀付流⽔，只要能插⼊新单据就能完成业务，特点是后⾯的数据不依赖前⾯的数据，所有的数据按时间流⽔进⼊数据库。
（2）状态型数据
状态型数据是有状态的，多笔业务之间依赖于有状态的数据，⽽且要保证该数据的准确性，⽐如充值时必须要拿到原来的余额，才能⽀付成功。
（3）配置型数据
此类型数据数据量较⼩，⽽且结构简单，⼀般为静态数据，变化频率很低。
⾄此，我们可以对整体的背景有⼀个认识了，如果要做优化，其实要⾯对的是这样的3*3的矩阵，如果要考虑表的读写⽐例（读多写少，读少写多...），那么就会是3*3*4=24种，显然做穷举是不显⽰的，⽽且也完全没有必要，可以针对不同的数据存储特性和业务特点来指定不同的业务策略。
对此我们采取抓住重点的⽅式，把常见的⼀些优化思路梳理出来，尤其是⾥⾯的核⼼思想，也是我们整个优化设计的⼀把尺⼦，⽽难度决定了我们做这件事情的动⼒和风险。
⽽对于优化⽅案，我想采⽤⾯向业务的维度来进⾏阐述。
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业务更适合。
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所⽰，供参考。
4）索引规范
（1）索引建议命名规则：idx_col1_col2[_colN]、uniq_col1_col2[_colN]（如果字段过长建议采⽤缩写）。
（2）索引中的字段数建议不超过5个。
（3）单张表的索引个数控制在5个以内。
（4）InnoDB表⼀般都建议有主键列，尤其在⾼可⽤集⽅案中是作为必须项的。
（5）建⽴复合索引时，优先将选择性⾼的字段放在前⾯。
（6）UPDATE、DELETE语句需要根据WHERE条件添加索引。
（7）不建议使⽤%前缀模糊查询，例如LIKE “%weibo”，⽆法⽤到索引，会导致全表扫描。
（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，如下图所⽰

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。