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