mysql常用规范
常用规范总结
-
表、存储过程、视图要有统一的命名规范，例如：表以t_打头，存储过程以sp_打头，视图以v_打头；
-
数据库和表的字符集用UTF8，如有emoji表情包的需求，必须使用UTF8MB4。
-
数字类型不要用varchar来存储，避免隐式类型转换，造成索引失效，进而影响查询效率；
-
判断两个字段相等或不等时候，注意字段为NULL的情况
-
不要在表中预留json类型的万能字段
-
单表索引不超过5个
-
where/order by/group by/join关联列等可以考虑做索引，以提升查询效率
-
避免使用%号在前的查询条件，用不到索引
-
insert和select都要明确指定影响的字段，确保表结构变更时SQL语句依然可用
-
用join代替子查询
-
如果有明确业务含义的字段可做主键，就不建议用自增ID做主键了。有人喜欢用自增ID作为primary key，再用明确业务含义的字段做unique index，其实没这个必要。
-
多表关联尽量用join而不是逗号，这样能清晰的看到表间的关联关系，可维护性更好
-
超过百万记录的表就算大表，而超过500万记录的表要考虑分区或分表，否则，表太大，表结构调整、数据修改、备份恢复都会有性能问题
-
四表及以上的关联要特别注意查询效率问题。因为表越多，意味着数据库引擎做出最优查询计划的代价就越高，在有限时间内得出的一定不是最优解，甚至可能是最次解。关于表关联的限制，有个公式可以参考:
T+BT<=5其中T为表的总数量，BT为里面大表的数量。例如：
- 1个大表最多4表关联
- 2个大表最多3表关联
- 不允许3个大表同时出现在一个sql语句中
针对多表关联，我们可用拆SQL语句或者业务宽表（即添加冗余字段）的方案解决性能问题。
-
大量表数据的更新操作能分批的要分批做，比如每批2000~5000条记录，否则，会带来几个问题：
- 主库操作耗时久，从库迟迟得不到更新，造成主从时延；
- binlog format=row时，会产生大量的binlog，日志的网络传输也要耗费大量时间，加剧主从复制延时。这里，要特别注意的是delete和update语句，看起来只是一条sql，但binlog的数量=受影响的记录数，所以如果delete或update了百万级的记录，也会产生等量的binlog，这是很惊人的。可以考虑使用limit限制每次delete或update的数据量。
- 大量数据更新很可能涉及到事务（一条update或delete语句就是一个事务），虽然mysql是行锁，但行锁在事务存续期间始终存在，会在更长的时间内阻塞住其它想更新该行的语句。若事务里涉及到的记录数还很多，意味着绝大部分的SQL都会被迫挂住，从而导致数据库连接池很快耗尽、tomcat线程池也会随之耗尽，进而影响整个微服务集群的可用性。生产中就出过因为数据库写阻塞导致整个docker集群不可用的案例。
- 大表表结构调整，比如常见的增删列、修改列类型、增删索引等，在mysql5.6之前是直接锁表的，那时只能靠pt-online-schema-change等工具解决表可用性问题。到了5.7 mysql支持online DDL，部分操作可不用锁表了。不过，修改列类型依然会暴力锁表，建议还用pt-online-schema-change工具替代。增删列、增删索引则不会锁表，支持并发的读写访问，可使用alter table语句在线修改。但这两类操作，尤其是增删列，代价高昂，意味着底层表数据的重新组织，基本上等价于表重建了，必须放到业务低谷时段执行。
- 不要在代码的for循环里访问数据库，减少数据库的访问次数
- 不推荐显式使用事务，即使要使用，也不能起大事务，原因见15。业务逻辑里面不涉及数据库操作或者只有读操作，不要添加事务注解，添加事务注解就会占用一个数据库连接。
- 测试环境下单条SQL语句原则上不建议超过5秒，不允许超过10秒，否则生产环境必然出问题，不要抱侥幸心理
- 对数据查询实时性有一定容忍度的功能，建议在代码层面使用读写分离，减少主从模式下主机负载过大导致的读操作性能不佳的问题。对实时性要求较高的数据依然优先连主库，防止数据库变更主从延时无法避免时，影响功能。
- 尽量避免使用显式行锁（select for update），防止代码抛异常后，事务没释放导致死锁；或者处理逻辑耗时很长，造成阻塞；
- 自己编写建表语句，不要从mysql上导出建表语句去现网执行，导出的建表语句会带mysql的一些默认设置，如果测试环境和现网数据库版本不一致，或者默认配置不一致，容易产生问题。
- 表要有主键，避免全表扫描，否则极易引发性能问题。此类问题在生产环境几万条数据的表上就曾出现过。