今天,腾讯云学院来分享一篇技术文章——有关MySQL的开发规范。文章开始之前,先给大家派送一个超级福利!

腾讯云学院邀请到本文作者——“知数堂”培训联合创始人、知名MySQL专家叶金荣老师和吴炳锡老师,为大家现场直播分享!大家可以在线与两位老师答疑互动哦~↓↓↓

直播主题

MySQL Schema设计及SQL开发规范实践

直播时间

9月17日 20:00

预约方式

↓ 直接扫码预约直播 ↓

事实上,InnoDB适用于几乎99%的MySQL应用场景,从MySQL 8.0开始所有系统表都改成InnoDB表了,是时候全面转向InnoDB引擎了。

注:从MyISAM转到InnoDB前,请参考这篇文章:

若为了节省磁盘空间,则建议选择latin1。但仍强烈建议选择utf8mb4字符集,因为它更通用,能兼容现有的其他字符集,尤其是在移动互联时代的emoji表情符,可以有效避免“乱码”问题。

注1:不同表JOIN时,若关联字段为字符串类型,且表(或关联字段)的字符集不同时,可能会造成类型隐式转换,从而无法使用索引,变成全表扫描。注2:无论什么方式连接到MySQL,都记得总是先执行命令“SET NAMES UTF8MB4”,确保各层都使用UTF8MB4字符集。

InnoDB的data page size默认是16KB,当一条记录物理长度超过约8KB(innodb data page size的一半,约8010字节,非精确值)时,InnoDB会对其采用“overflow page”方式存储,类似ORACLE中的“行迁移”。

因此,当必须使用大对象字段(尤其是TEXT/BLOB类型)且读写频繁的话,则最好把这些列拆分到子表中,不要和主表放在一起存储。

【参考】:。

在一些使用分区表后明显可以提升性能或者运维便利性的场景下,还是建议使用分区表。

比如老叶就在zabbix的数据库采用TokuDB引擎的前提下,又根据时间维度使用了分区表。这样的好处是保证zabbix日常应用不受到影响前提下,方便管理员例行删除过去数据,只需要删除相应分区即可,不需再执行一个非常慢的DELETE而影响整体性能。

但是,表分区也不是万能的,我之前分享过一个表分区太多导致主从复制严重延迟的案例。

参考:。

在一些合适的场景下,用存储过程、触发器也完全没问题。

我们以前就是利用存储完成游戏业务逻辑处理,性能上不是问题,而且一旦需求有变更,只需修改存储过程,变更代价很低。我们还利用触发器维护一个频繁更新的表,对这个表的所有变更都将部分字段同步更新到另一个表中(类似物化视图的变相实现),也不存在性能问题。

不要把MySQL的存储过程和触发器视为洪水猛兽,用好的话,没有问题的,真遇到问题了再优化也不迟。另外,MySQL因为没有物化视图,因此视图能不用就尽量少用吧。

注:若打算进行分库分表等分布式架构设计的话,则最好不使用存储过程,因为它不支持跨多实例,会造成后期实例拆分工作很难开展。

除了常见的建议外,还有其他几个要点:6.1、用INT UNSIGNED存储IPV4地址,用INET_ATON()、INET_NTOA()进行转换,基本上没必要使用CHAR(15)来存储。

6.2、枚举类型可以使用TINYINT存储,记住千万别用CHAR/VARCHAR 来存储枚举数据。

6.3、还个早前一直在传播的“常识性误导”,建议用TIMESTAMP取代DATETIME。其实从5.6开始,建议优先选择DATETIME存储日期时间,因为它的可用范围比TIMESTAMP更大,物理存储上仅比TIMESTAMP多1个字节,整体性能上的损失并不大。

6.4、所有字段定义中,默认都加上NOT NULL约束,除非必须为NULL(但我也想不出来什么场景下必须要在数据库中存储NULL值,可以用0来表示)。在对该字段进行COUNT()统计时,统计结果更准确(值为NULL的不会被COUNT统计进去),或者执行 WHERE column IS NULL 检索时,也可以快速返回结果。此外,NULL值过多也可能会造成索引树倾斜,索引统计信息不准确。

6.5、不要直接 SELECT * 读取全部字段,尤其是表中存在 TEXT/BLOB 大对象字段时代价更大,上面第2条已经提过。

除了常见的建议外,还有几个要点:

7.1、一般而言,单表建议不超过5个索引,每个索引中由不超过5个字段组成。

7.2、当发现mysqld进程长时间消耗CPU接近或超过100%时,99.99%是因为索引使用不当导致。

7.1、超过20个字节长度的字符串字段,最好创建前缀索引而非整列索引(例如:ALTER TABLE t1 ADD INDEX(user(20))),可以有效提高索引利用率。

7.2、有时候优化器指定的索引或执行计划可能并不是最优的,可以手工指定最优索引,或者修改session级的 optimizer_switch 选项,关闭某些导致效果反而更差的特性(比如index merge通常是好事,但也遇到过用上index merge后反而更差的,这时候要么强制指定其中一个索引,要么可以临时关闭 index merge 特性)。

7.3、哪怕是基于索引的条件过滤,如果优化器意识到总共需要扫描的数据量超过约30%时(大概是20% ~ 30%区间,非精确值),就可能直接改变执行计划为全表扫描,不再使用索引。

7.4、多表JOIN时,如果JOIN之后有排序,排序字段一定要属于驱动表,才能利用驱动表上的索引完成排序。

8.1、利用 pt-query-digest 定期分析slow query log并进行优化。

8.2、永远不要在业务高峰期执行DDL、备份或需要长时间才能执行完的其他SQL命令。

8.3、强烈建议启用 sql_safe_updates 选项以避免误操作导致全表被更新或删除。

8.4、重要业务上线前,一定要仔细确人重要的SQL都被优化过了。

8.5、一定要对用户输入值进行类型强制转换,避免潜在的SQL注入风险。

【参考】:

最后,记得关注由叶金荣、吴炳锡、祝百万、张松坡、邵宗文五位隐世高人强强联手,由知数堂和腾讯云学院联合推出的《MySQL 数据库架构及优化》课程!五位高人本门武功绝学特别面向以下武林人士开放:

技术总监

技术主管

开发工程师

DBA工程师

运维工程师

“神功”共分三大式、十六大招,若能耐心研习,必得五位高人真传,独步武林的梦想指日可待。

“神功”第一大式:MySQL应用开发规范

MySQL有趣的历史,温故知新

MySQL应用开发规范,避雷专用

开发者必备的备份及恢复技能,误操作时的“无效救心丸”

带你见识常见MySQL架构,尤其是如何快速进行读写分离架构设计

云数据库与传统数据库优劣势对比

不同规格的云数据库配置性能测试及分析

云数据库日常管理要点

“神功”第二大式:深入理解MySQL优化

深入理解MySQL索引的方方面面,必学

理解MySQL事务,快人一步更充分理解MySQL独特的事务机制

InnoDB的行锁和其他数据库区别在哪?如何减少死锁的概率,看这里就对

欲成“神功”,InnoDB引擎必然是绕不过的,学完定会有恍然大悟的感觉

“神功”得有“神技”相伴,SQL开发的优化及技巧就是“神技”

怎么才能随心所欲的“御剑”,就看智能DBA平台

“神功”第三大式:MySQL应用架构设计

如何从低段位升级到高段位,看MySQL升级注意事项

数据迁移与同步,升级过程中可别走火入魔了

作为高手面临的挑战之一,如何设计多IDC的DB架构

高手挑战之二,如何设计高并发的DB架构

集采百家之长,看看游戏、电商、金融这三大行业的经典案例

好了,五位世外高人的《MySQL 数据库架构及优化》独门心法吹水先打住。

这们心法到底在哪里能学到呢,点击“阅读原文”直达,开启神功修炼之旅吧。

对啦~ 有任何课程疑问,记得扫码添加腾讯云学院君咨询喔!