image.png

    一、基础规范

    • 表存储引擎必须使用InnoDB
    • 表字符集默认使用utf8，必要时候使用utf8mb4（通用，无乱码风险，汉字3字节，英文1字节）

    二、命名规范

    • 库名，表名，列名必须用小写，采用下划线分隔

    三、列设计规范

    • 根据业务区分使用tinyint/int/bigint，分别会占用1/4/8字节
    • 根据业务区分使用char/varchar

    （1）字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高
    （2）字段长度相差较大，或者更新较少的业务场景，适合使用varchar，能够减少空间

    • 根据业务区分使用datetime/timestamp

    解读：前者占用5个字节，后者占用4个字节，存储年使用YEAR，存储日期使用DATE，存储时间使用datetime

    • 必须把字段定义为NOT NULL并设默认值

    解读：
    （1）NULL的列使用索引，索引统计，值都更加复杂，MySQL更难优化
    （2）NULL需要更多的存储空间
    （3）NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑

    • 使用varchar(20)存储手机号，不要使用整数

    五、索引规范

    • 唯一索引使用uniq_[字段名]来命名
    • 非唯一索引使用idx_[字段名]来命名
    • 单张表索引数量建议控制在5个以内
    • 不建议在频繁更新的字段上建立索引

    • 非必要不要进行JOIN查询，如果要进行JOIN查询，被JOIN的字段必须类型相同，并建立索引

    • 理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c)

    六、SQL规范

    • 禁止使用select *，只获取必要字段

    解读：
    （1）select *会增加cpu/io/内存/带宽的消耗
    （2）指定字段能有效利用索引覆盖
    （3）指定字段查询，在表结构变更时，能保证对应用程序无影响

    • 隐式类型转换会使索引失效，导致全表扫描

    【强制】 update语句的 Where条件列、 order by、 group by、 distinct、多表join关
    联的字段需要有索引

    可建立联合索引,遵守最左前缀原则,尽量复用联合索引,避免冗余索引

    七、其他

    • 定义宽表

      1. 执行show table status：

    • 当avg_row_length的值超过100个字节时（定义为宽表），需要看看怎么优化；

    • 当Data_free的值大于0，表明有碎片；
    • 表越小IO压力越小
    • 实验测算，只有一个int类型，单表可存9.7亿，b+tree的根节点到叶子io也只有三次；

    总结：
    一、核心原则：

    2、表越小越好
    2.1、表越小IO压力越小
    2.2、窄表数据量大性能也好（比如5亿数据）
    2.3、宽表数据量小有可能性能也不好（比如50万）
    2.4、实验测算，只有一个int类型，单表可存9.7亿，b+tree的根节点到叶子io也只有三次；
    3、请求要足够高效

    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.3、严禁在数据库中明文存储用户的一些核心数据（最好要单向加密）
    2.4、用好INT数据类型（最好加上UNSIGNED，可扩大使用范围）「金额用途，扩大N倍，用bigint或者int来存储」
    2.5、遇到BLOB、TEXT字段，尽量要拆出去，再用主键做关联
    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.6、不要执行like ’%x%‘，可以用like ’x%’前缀索引
    3.7、尽量不要用 ‘!=’ 条件（扫描的数据量超过20%~30%范围时，会把执行计划变成全表扫描，不管有无索引）「但不是所有的都会如此，具体问题具体分析，比如反例：图3、图4」
    3.8、如果能确定返回结果数量的话，最好加上limit n「优化器通常会再进一步优化，取到以后会立即换回，不再继续扫描」
    3.9、优先使用union all，代替union（减少临时表的生成「5.6之前也会生成临时表」）

    1. 同样一句sql，性能比较：关键字大写>所有语句大写>所有语句小写
    2. 为查询结果相同的sql，为每一列起别名以后使sql语句性能降低
    3. 建议在日常开发中将sql的关键字大写，以提高效率