MySQL 使用规范及优化

存储引擎统一使用 Innodb

现在 Innodb 各方面都很完善，从 5.5 版本开始 MySQL 就将默认的存储引擎从 MyISAM 换成了 InnoDB，它比 MyISAM 多了一些很重要的功能如事务、行锁、数据恢复等，因此不建议使用 MyISAM。

字符集统一使用 UTF8mb4

现在都移动互联网时代了，emoji 表情人人都会用，UTF8 不支持 emoji。

表名和字段名都要添加注释

注释可以方便大家协作开发和维护。

禁止在数据库中存储文件

文件会占用大量存储空间，从而拖慢数据库的查询速度，数据库存储文件路径就行了，文件应该存储在专用的文件服务器上。

尽可能给字段加默认值以避免 null

因为值为 null 的数据在按条件查询和排序这两个场景需要做一些额外的判断。

网上很多文章说 null 会导致索引失效，这其实并不对，MySQL 官网文档明确指出可以用索引查询值为 null 的数据。另外，我看到网上很多文章说 null 会额外占用存储空间。其实准确来说是用了 MyIsam 存储引擎才会出现这种情况，如果用的是 Innodb 存储引擎，null 并不会占用任何空间，关于这方面的资料可参考 MySQL 官网文档：

• https://dev.mysql.com/doc/refman/5.7/en/is-null-optimization.html
• https://dev.mysql.com/doc/internals/en/innodb-field-contents.html
• https://dev.mysql.com/doc/internals/en/myisam-introduction.html

金额类数据使用 decimal 类型

float 和 double 这两种数据类型存储较大的数字会损失精度，涉及到金额的或者要求精确计算的业务推荐使用 decimal 类型。

避免使用 enum 枚举类型

修改 enum 时需要使用 alter 语句重构整个表非常耗时。

对 enum 类型的数据排序很麻烦且效率低。

优先选择占用空间小的数据类型

列的字段越大，建立索引时所需要的空间也就越大，能存储的索引节点的数量也就越少，索引的性能就会下降。因此要优先选择占用空间小的数据类型，比如整数类型尽量使用 tinyint、smallint、mediumint 而不是 int，字符串类型尽量使用 varchar 不是 text。对于非负的数据如自增 id，可以使用 unsigned ，因为无符号相对于有符号可以多出一倍的存储空间。

限制每张表的索引数量

创建索引之后，数据入库时还需要额外的时间去更新索引，索引是牺牲了插入数据和更新数据的性能去提升查询数据的性能。像性别、业务状态这类取值范围很小的字段就没必要加索引，一般来说这一类字段往往一个状态值就可以匹配到很多数据，MySQL 遇到这种情况时会用全表扫描代替索引，因为这时候全表扫描的查询速度比索引更快。关于这方面的资料可参考 MySQL 官网文档：

• https://dev.mysql.com/doc/refman/8.0/en/table-scan-avoidance.html
• https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html

创建联合索引时应考虑列的顺序

在两个或多个列上创建的索引叫联合索引，联合索引遵循最左前缀匹配原则。例如某联合索引有 a，b，c 三列，MySQL 可以支持按 “a”，“a,b”，“a,b,c” 3种组合进行查找，但不支持按 “b,c” 进行查找。所以创建联合索引的时候应该考虑列的顺序，但 SQL 语句里面的查询条件不需要考虑顺序。关于这方面的资料可参考 MySQL 官网文档：

• https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html

不要在索引列上做计算

在索引列上做计算无法利用索引提升查询速度，例如 select id where age + 1 = 10

避免数据类型的隐式转换

隐式转换会导致索引失效，比如 id 是 int 类型，但 id 作为查询条件时的值却是字符串类型：

select name,phone from customer where id = '111';

避免用 like 做全文搜索

以通配符结尾的 like 查询可以利用索引，但是以通配符开头的 like 查询，如 select * from user where username like '%admin%'，无法利用索引，只能做全表扫描，如果数据量大，推荐使用全文搜索引擎。

避免使用 select *

select * 会把整个表的字段都查出来返回给客户端，需要更多的网络传输时间，特别是某些字段占用存储空间比较大的时候。

使用预编译语句

预编译语句在编译之后只需要传参数，比传输整个 SQL 语句更高效，还可以避免 SQL 注入的问题。

合并 exists 子查询语句

对于某些使用了 exists 子查询语句的查询条件，能合并就合并，减少查询次数。

使用 limit 提升查询速度

一些子查询可以用 limit 提前降低数据规模，以提升查询速度。

拆分复杂语句

SQL 语句应尽可能保持简单，对于很复杂的查询应该进行拆分。一条 SQL 搞不定就用两条，不要有一条 SQL 搞定所有查询的想法。如果某个功能模块需要查询的表比较多，可以考虑开多几条线程执行不同的 SQL 语句，压榨 MySQL 的性能。

开启慢查询日志功能

开启慢查询日志功能让 MySQL 记录那些执行速度较慢的 SQL 语句，帮助我们分析定位问题所在。具体如何配置参考官网文档：

• https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

不要使用 MySQL 的查询缓存

MySQL 会监测使用了查询缓存的表，只要表的结构或者数据被修改，例如对某个表使用了 insert、 update、delete、truncate table、alter table 等语句，这个表相关的缓存都会失效。虽然查询缓存可以提升系统性能，但是 MySQL 是以 SQL语句 为 key 来做缓存的，即使 SQL 语句功能相同，然而只要多了一个空格或者大小写有差异都会导致匹配不到缓存。因此 MySQL 的查询缓存命中率低不够智能，让使用者维护起来费心费力得不偿失，从 8.0 版本开始 MySQL 的开发者就已经删除了这个功能。

谨慎使用分区表

分区表在物理上表现为多个文件，在逻辑上表现为一个表。

分区表的分区键设计不太灵活，如果不走分区键，很容易出现全表锁。一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难。自己分库分表，自己掌控业务场景与访问模式，更加可控。

大表优化方案

一般按以下步骤来优化 MySQL，成本由低到高

1. 优化查询 SQL 和索引
2. 用 Redis 做数据缓存
3. 做读写分离
4. 做垂直拆分，将一个大的系统分为多个小的系统，比如将后台管理模块跟业务逻辑模块的表拆分成两个库
5. 做水平切分，这一步最麻烦。水平拆分是通过某种策略将数据分片来存储，有库内分表和分库两部分，每片数据会分散到不同的MySQL 表或库，达到分布式的效果，能够支持非常大的数据量。MySQL 自带的分区表也是一种库内分表，库内分表仅仅是解决了单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻 MySQL 服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的IO、CPU、网络，这个就要通过分库来解决。分库分表是解决数据库瓶颈的葵花宝典，9成业务都是读多写少，所以应该优先考虑优化索引，优化表结构，缓存，读写分离这些。