mysql千万级大表的优化

  • Z时代
  • 2024-01-10
  • 分类：综合

database

千万级大表，这是一个很有技术含量的问题。一般碰到这种问题，我们下意识的会想对表进行拆分或者分区，但是其实，要从多个维度去考虑这个事情。

问题分解

我们首先找到关键字：

千万级
大表
优化

那么也就对应了相应的知识点：

数据量
操作对象
动作和结果

数据量

千万级是什么概念呢？

假设我们的主键是bigint类型，占据64位（8字节）空间，从btree进行索引的查找。需要遍历几次呢？

这里我们首先需要了解一个东西，即操作系统的分页。一般现在的操作系统页大小为4K，mysql的innodb的页大小默认配置的为16K。 而根据b+tree结构，每个节点保存的KEY的数量为pagesize/(keysize+pointsize)（如果是B-TREE索引结构，则是pagesize/(keysize+datasize+pointsize)）。一个节点可以存贮的数据量为16*1024/（8+8）*8，最多可以存储128个索引值。

假设我们的数据为3000W（2的25次方为33554432）可以得出，(log2^25)/log128 ≈ 25/7 ≈ 3.57。因此一个千万量级，且存储引擎是MyISAM或者InnoDB的表，其索引树的高度在3~5之间。可以看出，高度并不是特别的大。

当然，数据量我们也要通过业务属性来进行合理优化。

  1. 流水、日志型记录。

    通常是一些数据流水，日志记录的业务，里面的数据随着时间的增长会逐步增多，超过千万门槛是很容易的一件事情。

  2. 一个相对稳定的数据。

    比如一个稳定的用户表，业务稳定期不会得到大规模增长。

  3. 过分的冗余了数据。

操作对象，数据表

数据操作的过程就好比数据库中存在着多条管道，这些管道中都流淌着要处理的数据，这些数据的用处和归属是不一样的。

根据业务区分的话，一般分为3中数据类型。

  1. 流水型数据

  2. 状态型数据

  3. 配置型数据

流水型数据

流水型数据，一般也称之为日志型数据，并没有实际的强业务关联，每次业务都一定会产生，因此，体量比较大，并且不依赖前面和后面的数据，数据一般按时间线性增长。

状态型数据

状态型数据就是有状态，这个状态怎么理解呢？可以引申为事务，强关联。当A成功B也必须成功这种。

配置型数据

配置型数据，一般数据量都很小，这里就不用参与讨论了。

通常，对业务的数据量分析，应该具有一个辩证的思维去看待，进行合理的优化。

优化，动作和结果

优化，我们可以从哪些方面开始呢？

  1. 规范设计，这个可以参照（阿里巴巴mysql规范），记住，是参照，不是抄。

  2. 业务层优化

  3. 架构层优化

  4. 数据库优化

  5. 管理优化

其实我们通常所说的分库分表等方案只是其中的一小部分，如果展开之后就比较丰富了。

作为一个业务开发者角度，我们并不多赘述DBA应该尽的职责，我们多从业务角度去考虑怎么优化。

规范设计

规范的本质不是解决问题，而是杜绝一些潜在的问题实现。

举个栗子，索引的建立，数据库字段的长度。这些都应该有规范可依循。

配置规范

innodb
项目内部字符集统一，utf8mb4
默认事务级别调整，RR=》RC，提高并发性能（根据业务来）
预估数据表数据量，提前进行合理的建表拆分。mysql建议在2000W以内

建表规范

外键不适合innodb
浮点数建议用decimal而非float和double
整型定义不去进行自定义宽度
enum使用tiny替代
尽量少用长文本、长二进制类型，如果业务要求使用，尽量拆分
字段not null

命名规范

小驼峰
长度建议小于12
尽量要能见名知意

索引规范

命名： idx_col1_col2......,uniq_col1_col2......
联合索引字段不超过5个
单表索引不超过5个
主键索引一般选用自增或者雪花算法实现，保证整体有序
不建议使用%前缀模糊查询，例如LIKE “%weibo”，无法用到索引，会导致全表扫描
UPDATE、DELETE语句需要根据WHERE条件添加索引。
避免在索引字段上使用函数，否则会导致查询时索引失效。

开发规范（主流业务瓶颈基本出在mysql）

避免使用mysql自带功能：存储过程、触发器、自定义函数
减少排序
统计尽量使用缓存，进行离线计算
字段变更需标注原有意义
使用预处理防止sql注入
sql in长度限制
禁止select *
批量insert长度的限制

业务层优化

此处常见的就是对数据表的业务类型进行分析。读多写少，读少写多

业务拆分：

  1. 将混合业务拆分为独立业务

  2. 讲状态和日志型数据分离

表水平拆分：

  1. 按照日期拆分（全局日志类型）

  2. 按照取模拆分 （恒定关联的数据类型）

  3. 分区，mysql我个人不建议

另外，对高频读数据可以进行缓存优化

高频写的业务一般进行解耦，异步写入，防止阻塞，并且还可以通过降低写入频率（比如合并写入）

架构层优化

这一块其实通常是DBA职责，如果我们已经晋级高T的话，通常会需要和DBA协商。

  1. 系统扩展，使用中间件

  2. 读写分离

  3. 负载均衡

  4. 离线业务剥离

  5. nosql异构

数据库优化

事务优化，减小事务影响的数据和表

sql优化，通常出现slow sql进行explain分析，尽量减少关联和查询条件

索引优化，尽量命中索引，尽量减少范围查询，尽量在大表中杜绝全表扫描。

总结：

千万级大表的优化是根据业务场景，以成本为代价进行优化的，绝对不是孤立的一个层面的优化。一定要结合业务进行，否则，优化就是吹牛逼。

以上是 mysql千万级大表的优化 的全部内容， 来源链接： utcz.com/z/535452.html

回到顶部