不停机不影响业务,DBA必掌握:MySQL 可以这样优雅地变更表结构

关系型数据库存储的是结构化的数据类型,如果早期的设计不合理,后期进行表结构变更,对于 DBA 来说是家常便饭。

MySQL 数据表记录少到成千上万,多到成千万上亿,变更大数据量数据表的结构,对 DBA 而言是个头疼的事情。互联网业务 7×24 不间断提供服务,如何在不停机不影响业务的情况下优雅地进行表结构变更,这是每个 DBA 必须掌握的技能。

1.MySQL 在线变更表结构方案

典型问题:

目前可选的在线变更表结构方案有哪些?

在线变更表结构方案如下:

  • 直接 ALTER TABLE

  • oak-online-alter-table

    参考:http://shlomi-noach.github.io/openarkkit/oak-online-alter-table.html

  • 5.7 新增 online rename index

    参考:https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

  • pt-online-schema-change

    参考:https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html

  • gh-ost

    参考:https://github.com/github/gh-ost

  • OnlineSchemaChange

    参考:https://github.com/facebookincubator/OnlineSchemaChange

  • LHM

    参考:https://github.com/soundcloud/lhm

  • 腾讯 GSC 引擎

  • 自定义脚本

2.MySQL 下如何优雅的对一个大数据量的表进行自动的分库分表存储

典型问题:

当一个表预期数据量足够大的时候,如何进行分库分表存储,读写分离,来实现高效、稳定的数据存储和读。

分库分表分区是解决大数据量时的一个分而治之的思路,建议依次考虑的顺序如下

1.分区:表分区之后只是引擎存储的工作去保证,对用户相对透明,因为对应用侵入度较低;

2.分表:在同一个 schema 中的多个表,应用可能需要根据业务逻辑去判断业务对应的表,这种情况下单库内路由也相对比较好办;

3.分库:这个方法最大的问题就是分布式事务,目前市场有很多开源中间件可以选择,如当当或者 360 的,但未必能够满足需求,需要进行选择。

其实可以考虑为什么会出现大数据量呢?如果从生命周期角度考虑,对于这样大量的数据,是否可以分为热、温和冷三种类型呢?如果存在,那么:

1)冷数据(历史数据):是否就可以从现行数据表中进行定期剥离呢?比如交易记录,后续只是进行查询,完全可以将完全交易的数据进行定期转存到历史库

2)温数据:对于访问频度相对低一点的数据,如果考虑存储成本,是否可以采用分区的形式将这些数据放在相对廉价的存储上面

3)热数据:对于频繁访问的数据,一般是整个系统的性能瓶颈点,是否可以考虑 SSD 的硬盘,这样能保证既有业务的快速响应

对于数据生命周期的管理还是需要考虑业务实际场景:

当数据量比较大时落地实现的所有功能都交给数据库吗?作为架构设计中的业务架构、应用架构、技术架构、数据架构和部署运行架构中的架构之一,应该是与其他架构设计逻辑整合的一起的,因此需要应用人员和业务人员的参与,有部分功能为了保障数据库整体性能需要提升到应用逻辑中去完成,这样可以更好的提升数据库性能,我们在实战中的一些经验,比如不用存储过程、不用外键、不用复杂表操作,尽量单表操作,这些不是不做了,是数据库不做了,约束交给应用去做了,这样应用在从数据库得到快速响应后可以在应用层面进行逻辑处理,而这种处理的服务器一般可以较好的进行扩展,提高响应能力。

3.MySQL 单表量级达到 5 千万以上,如何添加修改字段而不产生锁表?

典型问题:

1、MySQL 如果单实例,没有主从 单库 ,单表量级达到 5 千万以上,该表的插入和查询都很多, 如何添加修改字段而不产生锁表?

2、如果有主从,表的量级达到千万以上,如何修改添加表字段?是先从库添加完再由从库变更为主库去用,让其在同步么?

1、单实例情况,建议使用 pt 或者 gh-ost 工具,二者均不会产生锁表,前者通过触发器实现,或者通过解析 binlog 实现。

2、多实例情况,如果使用 pt 工具,是在主库做变更;如果使用 gh-ost 工具,主从均可操作。

4.生产环境下,变更 Mysql 的表结构步骤是什么?

1.确认表的元数据信息,包括:

  • 字段类型

  • 数据量

  • 存储引擎

2.对需求里的改表语句进行审核,如果存在不规范的地方,联系开发进行修正

3.确认当前节点是什么角色,也就是主节点和从节点

4.确认主从状态是否正常

5.根据数据量、业务场景、业务容忍度,选择变更的方案以及预估需要的时间

6.确定操作时间点,如果数据量大,建议在低峰进行

7.通知研发开始进行变更操作,告知研发观察对应业务

8.变更过程中,留意 MySQL 监控和机器监控,观察主从状态、主从连接数、主从机器负载

9.变更过程出现问题,及时 KILL 相关操作

10.变更顺利完成,进行数据校验

11.告知研发

5.MySQL 建表的最佳实践是什么?

简单列一下:

  • 表名跟业务绑定,表名使用小写字母和下划线命名

  • 除存放日志的表和中间临时表外,其他表原则上必须有主键

  • 创建表必须包含行记录的创建时间字段和修改时间字段

  • 优先选择存储引擎类型为 InnoDB

  • 表和字段必须有 Comment 注释

  • 字符集优先选择 UTF-8

  • 根据数据尺寸决定数据长度,尽量减少冗余

  • 组合索引不能超过5列,最好保持在 3 列以内

  • 组合索引最常使用的字段或区分度高的字段考虑放在索引第一列

  • 索引不宜太多,维护索引也需要成本,单表索引数量建议不超过 5 个

  • 尽量避免使用触发器、存储过程、自定义函数(UDF)、视图

  • 预估容量,是否需要使用分区表,是否需要分表分库

  • 所有字段建议设置默认值,INT 为 0,VARCHAR 为 ”

6.MySQL 的表空间设置个和优化策略主要有哪些?

1.innodb_file_per_table 参数设置为 ON

2.定期执行 OPTIMIZE TABLE tableName;

3.定期执行 ALTER TABLE tableName ENGINE = InnoDB;

注意:第 2、3 条是高危操作,会影响业务,建议在低峰期操作

7.请教 MySQL 调优经验,针对大量的同时读写数据

典型问题:

我们正在使用 zabbix 监控,用的数据库是 MySQL,想问问专家们有没有调优经验,主要是会存在大量的同时读写数据,针对这块希望得到具体的指导。

1.影响 IO 的参数:innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_log_at_trx_commit、sync_binlog、write/read thread、innodb_io_capacity、innodb_max_dirty_pages_pct、innodb_flush_method、innodb_adaptive_flushing、innodb_adaptive_flushing_method、innodb_stats_on_metadata、innodb_change_buffering、innodb_old_blocks_time、binlog_cache_size、innodb_file_per_table

2.这方面比较成熟了,几篇文章参考:

【mysql】关于 IO / 内存方面的一些优化(http://www.cnblogs.com/chenpingzhao/p/5119161.html)

Innodb IO 优化-配置优化(http://wubx.net/innodb-io-optimize-conf/)

如果有条件,可以上 SSD