本文是 【极客时间-每日一课】的视频学习笔记，链接：https://time.geekbang.org/dailylesson/detail/100056829
MySQL 核心应用开发规范
一、核心思想
1 表越窄越好
表中字段类型选择原则：够用就好
尽量设计“窄表”，而非“宽表”
通过 show table status 查看表状态，如果 Avg_row_length 超过 100 字节则建议优化；
如果 Data_free 值较大，说明碎片率较高，要注意优化。
2 表越小越好
生产环境里的表，数据量越小越好，对表的读写效率更高
通常建议单表数量不超过 5000w 行，当然也要看表宽度，这不是固定值
一个只有3字节整型 INT 列的表，三层高度的 B+ 树最多约可存储 9.7亿 条数据
3 请求需要够高效
每个 SQL 尽快完成
事务尽快结束
核心目的是让这些事务或者 SQL 持有的锁尽快释放，降低影响
通过监控关注那些长时间运行的 事务/ SQL
顺便也要关注长时间的 SLEEP 等 SQL 注入风险行为
3、请求要足够高效
3.1、事务要尽快提交或回滚；（修改、删除数据会产生行锁，或者产生表锁，将导致其他sql或者事务被阻塞）
3.2、监控：
3.2.1、可以监控MySQL的线程状态，监控InnoDB的事务状态，一般是大于5秒
3.2.2、设置修改、锁定行数的阈值，比如大于10行可以避免潜在长时间锁或者事务SQL运行的风险
3.2.3、检查或者监控SQL注入的风险（例如：SLEEP函数，UNION ALL函数）
几个 Schema 设计原则
- 如上所述，字段类型选择尽量小，够用就好
- 尽量不使用外键，减少行锁，提高并发性能
- 自增INT/BIGINT主键（InnoDB引擎表）「备注：CHAR或者UUID主键，数据存储的瞬息离散随机，会造成数据页在存储时有大量的碎片，造成磁盘浪费，性能下降」
- 字符集和库表的设计要一致
（mysql实例-database-数据库表-字段-存储过程-event）否者报错。eg：utf8：3个字节，utf8mb4：4个字节，join类型转换无法走索引 - 在多表 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审核系统检查符合标准后才能上线 。
若有收获，就点个赞吧
0 人点赞
