MySQL 实用技巧 6 则

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 的硬盘,这样能保证既有业务的快速响应

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

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

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 条是高危操作,会影响业务,建议在低峰期操作

  • 发表于:
  • 原文链接:https://kuaibao.qq.com/s/20181206B07DC600?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券