----------------------------------------------------------------------------2019/11/9 更新
MySQL设计规范
三大范式
第一范式1NF:属性不可分【反例:address可1分为国家,省市,地区】
第二范式2NF:属性完全依赖主键
【反例:订单编号和商品编号位于同一张表中,前者与订单信息强相关,后者与商品信息强相关】【该拆表了】
第三范式3NF:不允许数据冗余【两张表很多属性相同】
配置规范(Innodb+UTF8)
- 存储引擎选择Innodb
【行级锁高并发,支持事务,缓存索引和数据,支持热备】
- 字符集通常选用UTF8
命名规范(小写+下划线,避开关键字)
小写+下划线,不能使用保留关键字
【MySQL对象名默认规定大小写敏感,且在生产环境中MySQL通常运行在Linux系统下,Linux系统本身也是大小写敏感的。】
【https://dev.mysql.com/doc/mysqld-version-reference/en/keywords-8-0.html
建议在设计数据表之后逐一排查有没有使用关键字。】
建表规范
-
Innodb禁止使用外键约束,可以通过程序层面保证
外键是否需要问题
对于互联网行业,注重高并发,不推荐使用外键。【外键的本质就是将数据一致性交给数据库处理】
对于传统软件行业,对并发量没有那么高,通过使用外键来降低开发成本也是可取的
-
同财务相关的最好使用定点数decimal(存储精准浮点数)
-
避免使用text,blob,如果一定要使用,单独出扩展表
【MySQL内存临时表不支持text,blob这样的大数据类型,只能使用磁盘临时表完成,并且会导致二次查询】
-
建议字段定义为NOT NULL(主键定义为NOT NULL,其他字段根据业务场景变化)
-
尽可能选择存储空间最小的字段【栗子:IP转化为整型存储】
-
非负型数据优先使用无符号存储
-
char VS varchar
char 定长 浪费空间 查询速度快
varchar 变长 节省空间 查询速度较慢
出于存储空间的考虑,优先选择varchar
-
日期类型选择
DATETIME:记录年月日时分秒,表示的时间范围最大
如果记录的日期要让不同时区的人使用,使用TIMESTAMP
索引规范
-
索引的适用情况
对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效。
对于中到大型的表,索引就非常有效。
-
添加索引的位置
where子句中的字段
-
联合索引
联合索引又称为复合索引,指查询中同时包含多个索引。
最左前缀匹配原则:MySQL遵循最左优先,所以在where子句中通常将使用最频繁的索引放在前面
-
索引使用注意事项
单表索引最好不超过5个【索引可以提高查询效率,但是同时会减小插入更新效率】
不建议模糊查询LIKE '%weibo',无法用到索引,会导致全表扫描
应用规范
-
避免使用存储过程,触发器,自定义函数,容易使业务逻辑和DB耦合在一起,会成为分布式方案瓶颈
-
优先使用Union All代替Union,但Union有去重功能,在不需要去重功能优先使用Union All
-
优先使用Limit N代替Linit M,N
-
减少或者避免排序,如group by不需要排序时增加order by null
-
使用prepared statement可以提高性能并且避免SQL注入
-
SQL中IN的值不应该包含过多
-
禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询
-
禁止使用 order by rand() 进行随机排序
-
WHERE 从句中禁止对列进行函数转换和计算
-
拆分复杂的大 SQL 为多个小 SQL
- 大 SQL 逻辑上比较复杂,需要占用大量 CPU 进行计算的 SQL
- MySQL 中,一个 SQL 只能使用一个 CPU 进行计算
- SQL 拆分后可以通过并行执行来提高处理效率
-
用join操作代替子查询
-
使用join关联的表最好不超过5个
-
对应同一列进行 or 判断时,使用 in 代替 or
MySQL常用函数
- 数学函数
- 字符串操作函数
- 条件函数
- 日期时间函数
- 系统信息函数
参考资料
一份非常完整的MySQL规范
https://baijiahao.baidu.com/s?id=1622786252178335118&wfr=spider&for=pc