01 MySQL 使用原则和设计规范
- MySQL 虽然具有很多特性并提供了很多功能,但是有些特性会严重影响它的性能,比如,在数据库里进行计算,写大事务、大 SQL、存储大字段等。
想要发挥 MySQL 的最佳性能,需要遵循 3 个基本使用原则。
- 首先是需要让 MySQL 回归存储的基本职能:MySQL 数据库只用于数据的存储,不进行数据的复杂计算,不承载业务逻辑,确保存储和计算分离;
- 其次是查询数据时,尽量单表查询,减少跨库查询和多表关联;
- 还有就是要杜绝大事务、大 SQL、大批量、大字段等一系列性能杀手。
- 大事务,运行步骤较多,涉及的表和字段较多,容易造成资源的争抢,甚至形成死锁。一旦事务回滚,会导致资源占用时间过长。
- 大 SQL,复杂的 SQL 意味着过多的表的关联,MySQL 数据库处理关联超过 3 张表以上的 SQL 时,占用资源多,性能低下。
- 大批量,意味着多条 SQL 一次性执行完成,必须确保进行充分的测试,并且在业务低峰时段或者非业务时段执行。
- 大字段,blob、text 等大字段,尽量少用。必须要用时,尽量与主业务表分离,减少对这类字段的检索和更新。
下面具体讲解数据库的基本设置规则:
- 必须指定默认存储引擎为 InnoDB,并且禁用 MyISAM 存储引擎,随着 MySQL 8.0 版本的发布,所有的数据字典表都已经转换成了 InnoDB,MyISAM 存储引擎已成为了历史。
- 默认字符集 UTF8mb4,以前版本的 UTF8 是 UTF8mb3,未包含个别特殊字符,新版本的 UTF8mb4 包含所有字符,官方强烈建议使用此字符集。
- 关闭区分大小写功能。设置
lower_case_tables_name=1
,即可关闭区分大小写功能,即大写字母 T 和小写字母 t 一样。
这里在实践中有个小问题,如何让系统中区分大小写的库表转换为不区分大小写的库表呢?因为要修改底层数据,还是比较麻烦的,操作步骤如下。
- MySQL dump 导出数据库。
- 修改参数
lower_case_tables_name=1
。 - 导入备份数据时,必须停止数据库,停止业务,影响非常大。
- 开启
per-table
表空间,开启后,每张业务表会单独创建一个独立于系统表空间的表空间,便于空间的回收,数据的迁移。
MySQL 数据库提供的功能很全面,但并不是所有的功能性能都高效。
- 存储过程、触发器、视图、event。为了存储计算分离,这类功能尽量在程序中实现。这些功能非常不完整,调试、排错、监控都非常困难,相关数据字典也不完善,存在潜在的风险。一般在生产数据库中,禁止使用。
- lob、text、enum、set。这些字段类型,在 MySQL 数据库的检索性能不高,很难使用索引进行优化。如果必须使用这些功能,一般采取特殊的结构设计,或者与程序结合使用其他的字段类型替代。比如:set 可以使用整型(0,1,2,3)、注释功能和程序的检查功能集合替代。