核心原则:

1、定义宽表 执行show table status:

​ 1.1、当avg_row_length的值超过100个字节时(定义为宽表),需要看看怎么优化;

​ 1.2、当Data_free的值大于0,表明有碎片;

2、表越小越好

​ 2.1、表越小IO压力越小

​ 2.2、窄表数据量大性能也好(比如5亿数据)

​ 2.3、宽表数据量小有可能性能也不好(比如50万)

​ 2.4、实验测算,只有一个int类型,单表可存9.7亿,b+tree的根节点到叶子io也只有三次;

3、请求要足够高效 3.1、事务要尽快提交或回滚;(修改、删除数据会产生行锁,或者产生表锁,将导致其他sql或者事务被阻塞)

3.2、监控:

​ 3.2.1、可以监控MySQL的线程状态,监控InnoDB的事务状态,一般是大于5秒 3.2.2、设置修改、锁定行数的阈值,比如大于10行 可以避免潜在长时间锁或者事务SQL运行的风险

​ 3.2.3、检查或者监控SQL注入的风险(例如:SLEEP函数,UNION ALL函数)

重要的细节

​ 1、schema设计原则

​ 1.1、尽量小的原则

​ 1.2、禁止使用外键「增加行锁」(高并发不建议)

​ 1.3、自增INT/BIGINT主键(InnoDB引擎表)「备注:CHAR或者UUID主键,数据存储的瞬息离散随机,会造成数据页在存储时有大量的碎片,造成磁盘浪费,性能下降」

​ 1.4、字符集和库表的设计要一致(mysql实例-database-数据库表-字段-存储过程-event)否者报错。eg:utf8:3个字节,utf8mb4:4个字节,join类型转换无法走索引。

​ 1.5、MySQL尽可能高效的建议 表的维度:

​ 1.5.1、单表数据量尽量不超过5000万;

​ 1.5.2、单表的物理大小不要超过20个G

​ 1.5.3、索引的数量不要超过5个 实例维度:

​ 1.5.4、总的大小不要超过500G

​ 1.5.5、总表数量不超过5000个(包括分区表)

2、库表字段设计规范

​ 2.1、每个表建议不超过50个字段

​ 2.2、优先选择utf8mb4字符集(支持移动终端的emoji符号,表情包)

​ 2.3、严禁在数据库中明文存储用户的一些核心数据(最好要单向加密)

​ 2.4、用好INT数据类型(最好加上UNSIGNED,可扩大使用范围)「金额用途,扩大N倍,用bigint或者int来存储」

​ 2.5、遇到BLOB、TEXT字段,尽量要拆出去,再用主键做关联

​ 2.6、字符类型尽可能采用varchar的数据类型(灵活、高效)「char占用空间固定」,最好不要变长更新(会照成磁盘碎片)

​ 2.7、日期时间数据建议采用datetime(0000-9999年)类型(比timestamp「1970-2038年」多一个字节),5.6以后多个datetime数据类型自动更新为当前时间,5.6之前只有一个可以可以更新为当前时间

3、SQL开发建议

​ 3.1、多表join时,join列的数据类型要一致(长度、类型,字符集)

​ 3.2、多表join时,把过滤后结果集较小的表作为驱动表(统一采用inner join让优化器自动优化,如果优化器优化错误,可以采用straight_join强制执行顺序 ) 3.3、【建议】在查询的where条件中用上函数或表达式要8.0版本(8.0支持函数索引、表达式索引、支持不可见索引、支持倒叙索引)

​ 3.4、不要看到where条件中出现的列就直接创建索引

​ 3.5、尽可能不要去执行select*操作(会导致io代价高)

​ 3.6、不要执行like ’%x%‘,可以用like ’x%'前缀索引

​ 3.7、尽量不要用 ‘!=’ 条件(扫描的数据量超过20%~30%范围时,会把执行计划变成全表扫描,不管有无索引)「但不是所有的都会如此,具体问题具体分析,比如反例:图3、图4」 3.8、如果能确定返回结果数量的话,最好加上limit n「优化器通常会再进一步优化,取到以后会立即换回,不再继续扫描」

​ 3.9、优先使用union all,代替union(减少临时表的生成「5.6之前也会生成临时表」)

​ 3.10、所有SQL都要通过SQL审核系统检查符合标准后才能上线 。

Q.E.D.


曙光在头上,不抬起头,便永远只能看见物质的闪光。