MySQL操作规范

单实例表个数必须控制在2000个以内
表存储引擎必须使用InnoDB
表字符集默认使用utf8，必要时候使用utf8mb4
1）通用，无乱码风险，汉字3字节，英文1字节
2）utf8mb4是utf8的超集，有存储4字节如表情符号，使用它
禁止使用存储过程，视图，触发器，Event
1）对数据库性能影响较大，互联网业务，能让站点层和服务层做的事情，不要交到数据库层
2）调试，排错，迁移都比较困难，扩展性较差
单表分表个数必须控制在1024个以内
表必须有主键，推荐使用UNSIGNED整数为主键
1）删除无主键的表，如果是row模式的主从架构，从库会挂住
禁止使用外键，如果要保证完整性，应由程序实现
1）外键使得表之间相互耦合，影响update/delete等SQL性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈
建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据

根据业务区分使用tinyint/int/bigint，分别会占用1/4/8字节
根据业务区分使用char/varchar
1）字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高
2）字段长度相差较大，或者更新较少的业务场景，适合使用varchar，能够减少空间
根据业务区分使用datetime/timestamp
1）前者占用5个字节，后者占用4个字节，存储年使用YEAR，存储日期使用DATE，存储时间使用datetime
日期字段，如果是1970～2037年内的时间，请设置为unsigned且用int（性能最佳），如果是日志类型的，只是显示作用的，请设为datetime
必须把字段定义为NOT NULL并设默认值
1）NULL的列使用索引，索引统计，值都更加复杂，MySQL更难优化
2）NULL需要更多的存储空间
3）NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有坑
使用TINYINT来代替ENUM
使用vachar(20)存储手机号，不要使用整数
1）涉及到国家代号，可能出现+/-/()等字符，例如+86
2）手机号不会用来做数学运算
3）varchar可以模糊查询，例如like’138%’
和金钱有关的字段，应使用decimal
建表时请为每个字段添加中文注释，以便生成相应的数据字典

唯一索引使用uni_[字段名]命名
非唯一索引使用idx_[字段名]命名
单张表索引数量建议控制在5个以内
1）互联网高并发业务，太多索引会影响写性能
2）生成执行计划时，如果索引太多，会降低性能，并可能导致MySQL选择不到最优索引
3）异常复杂的查询需求，可以选择ES等更为适合的方式存储
组合索引字段数不建议超过5个
1）如果5个字段还不能极大缩小row数，八成是设计有问题
不建议在频繁更新的字段上建立索引
非必要不要进行JOIN查询，如果要进行JOIN查询，被JOIN的字段必须类型相同，并建立索引
理解组合索引最左前缀原则，避免重复建立索引，如果建立了(a,b,c),相当于建立了(a)，(a,b)，(a,b,c)

只需要一条数据时，禁止查询全表
禁止用select * 这种效率低下的方式，要明确写出需要的字段
1）select * 会增加cpu/io/内存/带宽消耗
2）指定字段能有效利用索引覆盖
3）指定字段查询，在表结构变更时，能保证对程序无影响
能用group by解决的问题，禁止用distinct
禁止把join时，该写在on条件里的，写到where去；也同样禁止把该卸载where条件里面的，写到on去
如果知道该字段不可能出现负值，请使用unsigned
如果只需要判断数据是否存在，而不需要取出数据，使用子查询更加高效，例:select exists(select 1 from table where xxx)
能用between的时候不要用>=、<=、in、or，能用in时候不要用or
Like不允许使用全模糊查询，只允许使用右模糊查询
避免在where子句中对索引字段施加函数，这样会导致索引失效
insert必须指定字段，禁止使用insert into T values()
禁止大表JOIN和子查询，同一个字段上的OR必须改写为IN，IN的值必须少于50个

文章目录