1
基础规范
1
基础规范
表存储引擎必须使用innoDB Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好 MyISAM引擎可以压缩表，可以全文检索 表字符集默认使用utf8，必要时使用utf8mb4 通用，无乱码风险，汉字3字节，英文1字节 utf8mb4是utf8的超集，有存储4字节例如表情符号时，使用它 数据表、数据字段必须加入中文注释 禁止使用存储过程，视图，触发器，Event
对数据库性能影响较大，互联网业务，能让站点层和服务层干的事情，不要交到数据库层 调试，排错，迁移都比较困难，扩展性较差
禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统，数据库中存储路径
使用第三方存储工具必须存储完整的绝对路径，本地存储尽量不要使用相对路径
禁止在线上环境做数据库压力测试 测试，开发，线上数据库环境必须隔离
2
命名规范
2
命名规范
库名，表名，列名必须用小写，采用下划线分隔，不超过32个字符，禁止拼音英文混用
abc，Abc，ABC都是给自己埋坑
库备份必须以bak为前缀，以日期为后缀，从库必须以-s为后缀，备库必须以-ss为后缀 线上环境、开发环境、测试环境数据库内网域名遵循命名规范
业务名称：xxx | 线上环境：dj.xxx.db | 开发环境：dj.xxx.rdb | 测试环境：dj.xxx.tdb
只允许使用内网域名，而不是ip连接数据库
3
表设计规范
3
表设计规范
单实例表个数必须控制在2000个以内 单表分表个数必须控制在1024个以内 单表列数目必须小于30 表必须有主键，例如自增主键，推荐使用UNSIGNED整数为主键
主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用 主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率 无主键的表删除，在row模式的主从架构，会导致备库夯住
禁止使用外键，如果有外键完整性约束，需要应用程序控制
外键使得表之间相互耦合，影响update/delete等SQL性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈
建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据
MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节 减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO） 更有效的利用缓存，避免读入无用的冷数据 经常一起使用的列放到一个表中（避免更多的关联操作）
谨慎使用MySQL分区表
分区表在物理上表现为多个文件，在逻辑上表现为一个表 谨慎选择分区键，跨分区查询效率可能更低 建议采用物理分表的方式管理大数据
禁止在表中建立预留字段
预留字段的命名很难做到见名知义 预留字段无法确认存储的数据类型，所以无法选择合适的类型 对预留字段类型的修改，会对表进行锁定
4
列设计规范
4
列设计规范
优先选择符合存储需要的最小的数据类型 根据业务区分使用tinyint/int/bigint，分别会占用1/4/8字节 根据业务区分使用char/varchar
字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高 字段长度相差较大，或者更新较少的业务场景，适合使用varchar，能够减少空间
根据业务区分使用datetime/timestamp
前者占用5个字节，后者占用4个字节，存储年使用YEAR，存储日期使用DATE，存储时间使用datetime
必须把字段定义为NOT NULL并设默认值 使用INT UNSIGNED存储IPv4，不要用char(15)
NULL列使用索引，索引统计，值都更加复杂，MySQL更难优化 NULL需要更多的存储空间 NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑
使用varchar(20)存储手机号，不要使用整数
牵扯到国家代号，可能出现+/-/()等字符，例如+86 手机号不会用来做数学运算 varchar可以模糊查询，例如like ‘138%’
使用tinyint来代替enum
ENUM增加新值要进行DDL操作
避免使用TEXT、BLOB数据类型
会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能
避免使用小数存储货币，同财务相关的金额类数据使用decimal类型
精准浮点:decimal | 非精准浮点:float,double
使用字段名_at表示时间
5
索引规范
5
索引规范
唯一索引使用规范命名，例如：uniq_[字段名]/[字段名]_unique 非唯一索引使用规范命名，例如：idx_[字段名]/[字段名]_index 联合索引使用规范命名，例如：[字段名]_[字段名]_index 单张表索引数量建议控制在5个以内
互联网高并发业务，太多索引会影响写性能 生成执行计划时，如果索引太多，会降低性能，并可能导致MySQL选择不到最优索引 异常复杂的查询需求，可以选择ES等更为适合的方式存储
组合索引字段数不建议超过5个
如果5个字段还不能极大缩小row范围，八成是设计有问题
建立组合索引，必须把区分度高的字段放在前面
区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数） 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好） 使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）
不建议在频繁更新的字段上建立索引
出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列 包含在ORDER BY、GROUP BY、DISTINCT中的字段 并不要将符合1和2中的字段的列都建立一个索引，通常将1、2中的字段建立联合索引效果更好 多表join的关联列
非必要不要进行JOIN查询，如果要进行JOIN查询，被JOIN的字段必须类型相同，并建立索引
有过因为JOIN字段类型不一致，而导致全表扫描的坑么？
理解组合索引最左前缀原则，避免重复建设索引，避免建立冗余索引
重复索引示例：primary key(id)、index(id)、unique index(id) 冗余索引示例：index(a,b,c)、index(a,b)、index(a)
优先考虑覆盖索引
覆盖索引就是包含了所有查询字段(where,select,ordery by,group by包含的字段)的索引
6
SQL规范
6
SQL规范
建议使用预编译语句进行数据库操作
预编译语句可以重复使用这些计划，减少SQL编译所需要的时间，还可以解决动态SQL所带来的SQL注入的问题 只传参数，比传递SQL语句更高效 相同语句可以一次解析，多次使用，提高处理效率
禁止使用select *，只获取必要字段
select *会增加cpu/io/内存/带宽的消耗 指定字段能有效利用索引覆盖 指定字段查询，在表结构变更时，能保证对应用程序无影响
insert必须指定字段，禁止使用insert into T values() 充分利用表上已经存在的索引
指定字段插入，在表结构变更时，能保证对应用程序无影响
禁止使用属性隐式转换
SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引
禁止在where条件列使用函数或者表达式
导致不能命中索引，全表扫描
禁止负向查询以及%开头的模糊查询
导致不能命中索引，全表扫描
禁止大表JOIN和子查询
会产生临时表，消耗较多内存与CPU，极大影响数据库性能
禁止使用order by rand() 进行随机排序
会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的CPU和IO及内存资源
在明显不会有重复值时使用UNION ALL而不是UNION
UNION会把两个结果集的所有数据放到临时表中后再进行去重操作 · UNION ALL不会再对结果集进行去重操作
同一个字段上的OR必须改写为IN，IN的值必须少于50个
拆分复杂的大SQL为多个小SQL
使用left join或 not exists来优化not in操作，因为not in 也通常会使用索引失效
应用程序必须捕获SQL异常
文章转载自技术爱，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
