网络八股文之外, 你需要知道的MySQL知识

今天聊下MySQL的一些重要知识，有些可能和你之前所了解的并不一样，但却是事实！follow me~

1.上线需求时, SQL脚本直接执行吗?

你可能会说这不是废话吗？其实不然。对于create表或insert数据这种脚本，当然直接执行即可，但是对于alert表、create索引、update、delete这类脚本，如果你们公司有DBA的话，你把要上线执行的脚本提工单给DBA让他执行，他肯定是会先查询下你要操作表的数据量，根据数据量去做出下一步操作的依据。此时会出现两种结果：

1. DBA直接告诉你脚本已经执行，OK，完事
2. DBA通知你表数据量大，请走无锁变更（云RDS的功能），并且还会建议你在业务低峰期执行

为什么对于alert表、create索引、update、delete这类脚本尤其要关注数据量呢？因为这些操作在数据量大时是非常消耗数据库资源的，如cpu、内存，同时会不同程度的锁表，这个对你正在运行的生产环境来说极其重要。本来你的系统运行的很丝滑，结果你开始在生产环境执行要上线需求的脚本，这些脚本导致锁表(MySQL的机制)，并且是长时间锁表(数据量大)，那么你的业务系统访问数据读写数据的性能将会大幅下降，系统读写无法正常使用，从而导致不必要的生产事故。

执行脚本这种场景，多大数据量是警戒线呢？这里建议一般是300万，如果你的系统并发量很高，数据量超过100万时就要区别对待要执行的脚本了。所以养成好习惯，在线上执行脚本前要先查下表的数据量，大数据量时建议选在业务低峰时间执行脚本

2.多大数据量才算大表, 都要分库分表吗？

是500万？是2000万？是1亿？正如上个话题聊的，我们是要分场景去区分的。一个负责记录业务操作记录的类似log功能表，1个亿的数据量也是正常的，你可能会说谁家的MySQL一个表会存1亿数据，你不分表吗？太low！其实这是非常正常的，对MySQL来说完全没问题。分库分表是我们解决问题的一种思路，但并不是大表必须要做的。比如我们系统的一个审核记录表，目前数据量有5千多万，日增数据量为24万左右，如下：

该表是属于写多，读也多，是一个比较重要的表，目前的单表查询以及接口查询耗时如下：
单表索引字段查询耗时25ms

涉及该表的接口查询(接口中还有其它RPC调用)，RT如下：

性能依然还不错。后续对该表的优化也并不是优先考虑分库分表！可以考虑表分区，可以归档旧数据等等。这里要传达的核心就是：我们要充分挖掘、利用MySQL的优点，把他的性能发挥到极致。分库分表是一种重要的思路，但是你要分库分表必须给出详尽的技术选型说明以及需求、方案评审，总之你要说服技术委员会，否则中大型公司中是不会你想用什么就用什么。毕竟引入一个组件就多了一个变量和复杂性。
另外针对大表必须要注意：

1. 查询字段必须有索引，否则直接搞坏系统
2. 单表磁盘占用超过10G之后就要考虑归档

3. 开发中库表的把控

代码review是我们开发人的共识，是质量把控的重要一环，review对库表来说同样非常重要。开始开发之前，一般中大型公司的team都是要review你的需求开发详设的，这期间就包括需求涉及的库表的review。核心item有：

1. 新增的表结构是否设计合理
2. 设置的索引是否合理(是否是冗余重复的？是否违背最左匹配原则？)
   a. 比如生产表上已有联合索引(col_a, col_b)，那就不要再对col_a单独设置索引了
   b. 比如生产表上已有联合索引(col_a, col_b), 时间字段就不要作为第一个索引字段，根据时间范围查询时，联合索引范围查询字段后面的索引字段不会走索引。所以联合索引中时间字段一般放在最后
3. 有没有涉及到大表的CRUD，查询字段必须要有索引

4.索引是耗资源的

MySQL的表空间占用大小 = 存储的数据大小+索引占用的大小，所以索引字段并不是越多越好，同样消耗实实在在的磁盘空间，比如下图中这个表的大小为17.4GB，业务数据7GB左右，索引数据10GB左右

5.关于批量写表

业务代码中需要进行批量写表时，要控制批量的大小，比如一次1000条，不能不管数量直接foreach拼接就完事了。之前出现过生产事故就是由批量update造成的，开发小伙伴组装好list之后，直接调用mybatis的foreach批量更新，dev、fat、uat测试均没有任何问题，上线之后某天突然服务接口大量超时，告警不断。最后排查下来，是一个批量update的sql语句把MySQL资源耗尽了。那个update语句结构是这样的：

update 表 set col_a=xxx, col_b=xxx where xxx and id in(...);

线上环境业务触发相关操作，执行后，这个in条件中有8万多条数据，直接导致锁表时间很长，db所在机器cpu和内存被大量占用，最终导致系统接口大量超时不可用。

Okay，今天就到这儿，回聊~