经验篇——MySQL使用经验分享-一一网

序

工作快5年了，有些经验整理出来做一个经验分享系列篇文章，本文是 MySQL 的经验分享，经验这东西就简单罗列吧

两方面

设计

1. 主键使用自增 ID

在使用 InnoDB 存储引擎的情况
所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
InnoDB 数据文件本身是一颗 B+ Tree，非单调的主键会造成在插入新记录时数据文件为了维持 B+ Tree 的特性而频繁的分裂调整，十分低效

2. 在某些业务场景中使用逻辑删除字段代替物理删除

有些业务数据可能暂时没用，但也是一些人员辛苦收集上来的数据，本身就具有价值，考虑后续业务的变化，应该使用逻辑删除，不能直接将数据 delete

3. 字符集使用 utf8mb4

相信很多人都碰到过这种情况，可爱的用户使用了可爱的表情却被系统无情的告知系统异常。

4. 添加注释

是个靠谱的程序员都会写注释
flowable 的表都没有注释，代码也很少注释。哎~

5. 大字段单独建扩展表存放

在未使用索引排序而是文件排序的情况下，MySQL 对某列字段进行排序，会将整行数据取出，大字段可能会导致 MySQL 无法将结果集放入排序缓冲区而借助磁盘进行排序。

6. 图片附件等存 url 即可

我们一般都将文件上传到阿里云文件存储服务器，将 url 放到数据库中存储
通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机 IO 操作，文件很大时，IO 操作很耗时

7. 对于非负型的数据（如自增 ID、整型 IP）来说，要优先使用无符号整型来存储

无符号相对于有符号可以多出一倍的存储空间

8. 如果字符类型字段长度固定或基本固定，使用 char 定长字符串

例如：身份证号，手机号，电话，密码等
定长字符串存储结构固定，按固定偏移量即可查找；而变长字符串需要额外存储字符串长度，根据长度来截取字符串

9. 列定义为 NOT NULL

可为 NULL 的列使得索引、索引统计和值比较都更复杂。
可为 NULL 的列会使用更多的存储空间，在 MySql 里也需要特殊处理。
当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节，在 MyISAM 里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

10. 用 Decimal 类型存小数（money）

Decimal 类型为精准浮点数，在计算时不会丢失精度。
不要等到资损的时候才注意这个问题

11. 不使用外键约束

在互联网行业，对扩展性和高并发的要求更高，数据库只用来做他它更擅长的事，而且需要把性能瓶颈上升到应用层面通过扩机器来解决。所以对于外键约束建议放到应用层来做。
外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻

塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

12. 排序字段放入联合索引

避免使用文件排序，文件排序性能糟糕

13. 联合索引的选择：区分度最高的放最左侧，使用最频繁的放到左侧，尽量把字段长度小的列放在联合索引的最左侧

这个是有讲究的，讲究最左前缀匹配原则

14. 关闭查询缓存

有一篇单独的文章介绍查询缓存。

15. 防止因字段类型不同造成的隐式转换，导致索引失效

检索

1. WHERE 从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致无法使用索引

2. 不要使用 SELECT * ，尽量考虑覆盖索引

3. 大表避免使用全模糊和左模糊查询

有很多方案可以解决，比如 elasticsearch 。

4. 避免使用子查询

子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响
通常子查询在 in 子句中，且子查询中为简单 SQL (不包含 union、group by、order by、limit 从句)时,才可以把子查询转化为关联查询进行优化