MySQL千万级数据量优化⽅案
前⾔
千万级⼤表如何优化，这是⼀个很有技术含量的问题，通常我们的直觉思维都会跳转到拆分或者数据分区。除此之外，还有其他的思路和解决⽅案。根据本⼈多年的⼯作经验，做了如下总结。
⽅案
"千万级⼤表优化"这句话有3个关键字: 千万级，⼤表和优化。接下来将就这3个关键字展开讨论。
数据量：千万级
随着业务的发展，应⽤需要处理的数据量也是动态变化的。这也意味着要带着⼀种动态思维来系统的数据量，从⽽对于不同的场景我们应该有不同的处理策略。
①数据量为千万级，可能达到亿级或者更⾼
这类数据通常是数据流⽔，⽇志，关注和聊天记录，⾥⾯的数据随着时间的增长⽽逐渐增多，可以达到亿级别。
②数据量为千万级，增长可以预期
如果数据量相对稳定，通常存储偏向于状态的数据，⽐如⽤户，商品和订单，这类信息在表中都有相应的⼀⾏或者多⾏数据记录，随着业务的增长，增长量级相对是⽐较稳定的。
③数据量为千万级，冗余数据居多
这种情况⼀般出现在配置或者跟时间轴相关的业务表中。⼤部分数据属于⽆效数据或过期数据。
数据量是⼀个粗略的认识，我们需要对数据的结构和内容做更进⼀步的分析。
对象：数据表
数据根据不同的操作类型和业务特征可以分为如下⼏类：
①流⽔型数据
流⽔型数据是⽆状态的，只存在插⼊和查询两种操作。⽐如交易流⽔、⽀付流⽔，只要能插⼊新单据就能完成业务，特点是后⾯的数据不依赖前⾯的数据，所有的数据按时间流⽔进⼊数据库，数据热点只存在于最近的时间段。
②状态型数据
状态型数据是有状态的，多笔业务之间依赖于有状态的数据，⽽且要保证该数据的准确性，⽐如充值时必须要拿到原来的余额，才能⽀付成功。这类数据的操作必须依赖事务机制来保证⼀致性。存在插⼊，更新和查询等多种操作。
③配置型数据
此类型数据数据量较⼩，⽽且结构简单，⼀般为静态数据，变化频率很低。
针对不同的数据存储特性和业务特点来指定不同的业务策略。对此我们把常见的优化思路梳理出来，尤其是⾥⾯的核⼼思想，也是我们整个优化设计的框架，⽽难度决定了我们做这件事情的动⼒和风险。
数据量增长情况数据表类型业务特点优化核⼼思想难度
增长⽐较平均状态表OLTP业务
⽅向
进⾏容量规
划，保证存储2
年内的业务数
据尽量保证稳
定，读需求⽔
平扩展
★★★★
增长⽐较迅速流⽔表OLTP业务
历史记录
业务拆分，使⽤
海量存储机制
★★★★OLAP业务
统计数据
使⽤⼤数据存
储机制
统计数据储机制
增长⽐较缓慢配置表通⽤业务分布式配置中⼼，个性化存储
数据量增长情况数据表类型业务特点优化核⼼思想难度
⽬标：优化
在这个阶段，我们要说优化的⽅案了，我们要⽀撑的表数据量是千万级别，相对来说是⽐较⼤了，DBA 要维护的表肯定不⽌⼀张，如何能够更好的管理，同时在业务发展中能够⽀撑扩展，同时保证性能，这是摆在我们⾯前的⼏⼤挑战。根据我多年的⼯作经验整体分为五个部分：
规范设计
业务层优化
架构层优化
规范设计
配置规范：
MySQL 数据库默认使⽤ InnoDB 存储引擎。
保证字符集设置统⼀，MySQL 数据库相关系统、数据库、表的字符集都使⽤ UTF8MB4，应⽤程序连接、展⽰等可以设置字符集的地⽅也都统⼀设置为 UTF8MB4 字符集。
注：UTF8 格式是存储不了表情类数据，需要使⽤ UTF8MB4，可在 MySQL 字符集⾥⾯设置。在 8.0 中已经默认为 UTF8MB4，可以根据公司的业务情况进⾏统⼀或者定制化设置。
MySQL 数据库的事务隔离级别默认为 RR(Repeatable-Read)，建议初始化时统⼀设置为 RC(Read-Committed)，对于 OLTP 业务更适合。
数据库中的表要合理规划，控制单表数据量，对于 MySQL 数据库来说，建议单表记录数控制在 2000W 以内。将历史数据同步到⼤数据存储
MySQL 实例下，数据库、表数量尽可能少；数据库⼀般不超过 50 个，每个数据库下，数据表数量⼀般不超过 500 个(包括分区表)。常见mpp数据库
建表规范：
InnoDB 禁⽌使⽤外键约束，可以通过程序层⾯保证。
存储精确浮点数必须使⽤ DECIMAL 替代 FLOAT 和 DOUBLE。
整型定义中⽆需定义显⽰宽度，⽐如：使⽤ INT，⽽不是 INT(4)。
不建议使⽤ ENUM 类型，可使⽤ TINYINT 来代替。
尽可能不使⽤ TEXT、BLOB 类型，如果必须使⽤，建议将过⼤字段或是不常⽤的描述型较⼤字段拆分到其他表中；另外，禁⽌⽤数据库存储图⽚或⽂件。
存储年时使⽤ YEAR(4)，不使⽤ YEAR(2)。
建议字段定义为 NOT NULL。字段为NULL会导致索引失效。
建议 DBA 提供 SQL 审核⼯具，建表规范性需要通过审核⼯具审核后。
命名规范：
库、表、字段全部采⽤⼩写。
库名、表名、字段名、索引名称均使⽤⼩写字母，并以“_”分割。
库名、表名、字段名建议不超过 12 个字符。(库名、表名、字段名⽀持最多 64 个字符，但为了统⼀规范、易于辨识以及减少传输量，统⼀不超过 12 字符)
库名、表名、字段名见名知意，不需要添加注释。
对于对象命名规范的⼀个简要总结如下表所⽰，供参考：
命名列表
索引规范：
索引建议命名规则：idx_col1_col2[_colN]、uniq_col1_col2[_colN](如果字段过长建议采⽤缩写)。
索引中的字段数建议不超过 5 个。
单张表的索引个数控制在 5 个以内。
InnoDB 表⼀般都建议有主键列，尤其在⾼可⽤集⽅案中是作为必须项的。
建⽴复合索引时，优先将选择性⾼的字段放在前⾯。
UPDATE、DELETE 语句需要根据 WHERE 条件添加索引。
不建议使⽤ % 前缀模糊查询，例如 LIKE “%weibo”，⽆法⽤到索引，会导致全表扫描。
合理利⽤覆盖索引，例如：SELECT email，uid FROM user_email WHERE uid=xx，如果 uid 不是主键，可以创建覆盖索引
idx_uid_email(uid，email)来提⾼查询效率。
避免在索引字段上使⽤函数，否则会导致查询时索引失效。
确认索引是否需要变更时要联系 DBA。
应⽤规范：
避免使⽤存储过程、触发器、⾃定义函数等，容易将业务逻辑和DB耦合在⼀起，后期做分布式⽅案时会成为瓶颈。
考虑使⽤ UNION ALL，减少使⽤ UNION，因为 UNION ALL 不去重，⽽少了排序操作，速度相对⽐ UNION 要快，如果没有去重的需求，优先使⽤ UNION ALL。
考虑使⽤ limit N，少⽤ limit M，N，特别是⼤表或 M ⽐较⼤的时候。
减少或避免排序，如：group by 语句中如果不需要排序，可以增加 order by null。
统计表中记录数时使⽤ COUNT(*)，⽽不是 COUNT(primary_key) 和 COUNT(1)。
InnoDB 表避免使⽤ COUNT(*) 操作，计数统计实时要求较强可以使⽤ Memcache 或者 Redis，⾮实时统计可以使⽤单独统计表，定时更新。
做字段变更操作(modify column/change column)的时候必须加上原有的注释属性，否则修改后，注释会丢失。
使⽤ prepared statement 可以提⾼性能并且避免 SQL 注⼊。
SQL 语句中 IN 包含的值不应过多。
UPDATE、DELETE 语句⼀定要有明确的 WHERE 条件。
WHERE 条件中的字段值需要符合该字段的数据类型，避免 MySQL 进⾏隐式类型转化。在隐式转换的时候，容易导致s。
SELECT、INSERT 语句必须显式的指明字段名称，禁⽌使⽤ SELECT * 或是 INSERT INTO table_name values()。
INSERT 语句使⽤ batch 提交(INSERT INTO table_name VALUES(),(),()……)，values 的个数不应过多。
业务层优化
业务层优化应该是收益最⾼的优化⽅式了，⽽且对于业务层完全可见，主要有业务拆分，数据拆分和两类常见的优化场景(读多写少，读少写多)。
①业务拆分
业务拆分分为如下两个⽅⾯：
将混合业务拆分为独⽴业务
将状态和历史数据分离
业务拆分其实是把⼀个混合的业务剥离成为更加清晰的独⽴业务，这样业务 1，业务 2......独⽴的业务使得业务总量依旧很⼤，但是每个部分都是相对独⽴的，可靠性依然有保证。
例如：我们有⼀张表 Account，假设⽤户余额为 100。
我们需要在发⽣数据变更后，能够追溯数据变更的历史信息，如果对账户更新状态数据，增加 100 的余额，这样余额为 200。
这个过程可能对应⼀条 update 语句，⼀条 insert 语句。对此我们可以改造为两个不同的数据源，account 和 account_hist。
在 account_hist 中就会是两条 insert 记录，如下：
⽽在 account 中则是⼀条 update 语句，如下：
这也是⼀种很基础的冷热分离，可以⼤⼤减少维护的复杂度，提⾼业务响应效率。
②数据拆分
按照⽇期拆分：这种使⽤⽅式⽐较普遍，尤其是按照⽇期维度的拆分。其实在程序层⾯的改动很⼩，但是扩展性⽅⾯的收益很⼤。
数据按照⽇期维度拆分，如 order_20191021。
数据按照周⽉为维度拆分，如 test_201910。
数据按照季度，年维度拆分，如 test_2019。
采⽤分区模式：分区模式也是常见的使⽤⽅式，采⽤ hash，range 等⽅式很多。
在 MySQL 中我是不⼤建议使⽤分区表的使⽤⽅式，因为随着存储容量的增长，数据虽然做了垂直拆分，但是归根结底，数据其实难以实现⽔平扩展，在 MySQL 中是有更好的扩展⽅式。
③读多写少优化场景
采⽤缓存，采⽤ Redis 技术，将读请求打在缓存层⾯，这样可以⼤⼤降低 MySQL 层⾯的热点数据查询压⼒。
④读少写多优化场景
读少写多优化场景，可以采⽤三步⾛：
采⽤异步提交模式，异步对于应⽤层来说最直观的就是性能的提升，产⽣最少的同步等待。
使⽤队列技术，⼤量的写请求可以通过队列的⽅式来进⾏扩展，实现批量的数据写⼊。
举个例⼦：对于评论类业务数据，相⽐于⾦额来说属于业务优先级略低的场景。如果数据更新过于频繁，可以适度调整数据更新的范围(⽐如从原来的每分钟调整为 10 分钟)来减少更新的频率。对于业务指标，⽐如更新频率细节信息，可以根据具体业务场景来讨论决定。
架构层优化
架构层优化是业务框架的基础设施优化，我们需要根据业务场景在架构层⾯引⼊新的解决⽅案，达到事半功倍的效果。
①系统⽔平扩展场景
采⽤中间件技术，可以实现数据分⽚路由和读写分离。常见的中间件有ShardingSphere，zebra,Atlas 等。根据实现⽅案的不同分为客户端代理和服务端代理。
数据分⽚路由主要指的是分库分表。通过指定对应数据记录的分⽚键将数据路由到不同的数据库表，实现数据库容量的⽔平扩展。

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