yhhitall的个人空间

对于后端开发工程师来说，数据库设计，优化是一项必备的技能，瞧瞧我们是不是经常在项目中吐槽其他小伙伴编写的sql语句，既如此，千万不要让其他小伙伴有机会吐槽回来。甚至我们应该做到在编写sql语句的时候，脑海中已经浮现了该sql语句的执行轨迹，这样一来，相信我们写出的sql语句质量会非常高。

因为工作上的需要，抽空整理了一版数据库设计开发参考规范，我把它叫做必知必会之数据库规约，并分享给你，期望给你带来一些收获！

我将内容分为：

• sql规范

2.建表规范

2.1.范式化

#1.关系数据库表设计基础理论：第一范式、第二范式、第三范式
##1.1.第一范式
	强调列的原子性，字段不可再分割
##1.2.第二范式
	强调行的唯一性，不可存在相同的行（表中必须有主键字段）
##1.3.第三范式
	强调主外键关联，消除冗余性（需要注意，在互联网项目中，一般不建立主外键约束，在代码层面实现业务关联）
	因此今天我们有时候在强调反范式化设计，就是针对的第三范式

2.2.存储引擎

#1.mysql数据库，存储引擎建议选择InnoDB
##原因：
	InnoDB存储引擎支持事务、支持行级锁（并发性能更好）、支持Crash safe能力（redo log能力）

3.3.数据类型

#1.选择合适的数据类型
##1.1.整数
TinyInt,SmallInt,MediumInt,Int,BigInt 使用的存储8,16,24,32,64位存储空间。使用Unsigned表示不允许负数，可以使正数的上线提高一倍

##1.2.实数
Float,Double , 支持近似的浮点运算
Decimal，用于存储精确的小数（通常用于货币存储）

##1.3.字符串
VarChar，存储变长的字符串。需要1或2个额外的字节记录字符串的长度
Char，定长，适合存储固定长度的字符串，如MD5值。
Blob，Text 为了存储很大的数据而设计的。分别采用二进制和字符的方式

##1.4.时间
DateTime，保存大范围的值，占8个字节，存储范围（1001-9999）。
TimeStamp，推荐，与UNIX时间戳相同，占4个字节，存储范围（1970-2038）

如何选择？

• 尽量使用对应的数据类型。比如不要用字符串类型保存时间

• 选择更小的数据类型。能用TinyInt，就不用Int

• 标识列（identifier column），建议使用整型，不推荐字符串类型，占用更多空间，而且计算速度比整型慢

2.4.字符集

#1.统一字符集（客户端、服务端），建议使用utf-8字符集，mysql数据库需要注意真正的utf-8字符集应该选择：utf8mb4

2.5.命名

#1.见名知意，禁止拼音英文混用
#2.约定库名、表名、字段名小写、下划线风格，不超过32个字符
#3.禁止使用保留字

2.6.注释

#1.表、字段必须添加必要的注释（千万不要偷懒）

2.7.默认值

#1.字段定义为 NOT NULL 且需提供默认值
##原因：
	NULL的列使索引/索引统计/值比较都更加复杂，数据库自身更难优化
	NULL这种类型Msql内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多
	NULL值需要更多的存储空，无论是表还是索引中每行中的NULL的列都需要额外的空间来标识

2.8.手写schema

#1.禁止通过工具，或者orm框架生产schema。全部ddl sql必须手工提供

3.sql规范

3.1.select *

#1.大原则：客户端需要什么，就返回什么
#2.读取不需要的列，会增加cpu、Io、网络开销
#3.select * 不能有效利用覆盖索引

3.2.where条件

#1.禁止where条件属性上，执行隐式转换，隐式转换会让索引失效
   比如select id, name,phone from table where phone=18688438888 （phone是字符串类型）
#2.禁止where条件属性上，使用函数或者表达式，where条件属性上使用函数，会让索引失效，同理表达式让索引失效
   比如select id,name,age where age+1 = 10

3.3.外键关联

#1.禁止使用外键、级联。一切外键概念必须要应用层解决
##原因：
	外键与级联更新适用于单机低并发，不适合分布式、高并发集群
	外键影响数据库的插入速度
	级联更新是强阻塞，存在数据库更新风暴的风险

3.4.or连接条件

#1.尽量避免在where子句中，通过or连接条件
##原因：
	or 连接条件可能会使索引失效
	通过union all 替换 or连接条件

3.5.模糊查询

#1.主流关系数据库oracle、mysql支持前缀索引
#2.模糊查询应用场景，like子句中要放在后面
   比如：select id,name from table where name like '小明%'

3.6.表关联

#1.表关联数量，尽量不要超过5个表，连表越多，编译的时间和开销也就越大
#2.把连接表拆开成较小的几个执行，可读性更高
#3.表之间的关联，让小表成为驱动表
#4.多个表关联时，每一列上必须明确来源表
   比如：select A.id,B.name from A,B WHERE A.id=B.id

3.7.限制结果集

#1.如果明确查询结果最多只有1条记录，请使用好limit=1 或者rownum<=1

4.索引规范

4.1.索引原理

#1.索引的原理：空间换时间
##优势：
	减少查询扫描的数据量
	避免排序和零时表 
	将随机IO变为顺序IO
##代价：
	需要更多的存储空间
	影响更新维护效率（增删改）

4.2.索引选择

#1.B-tree索引
	实践中使用更多的索引类型
	支持精确查找、范围查找、前缀查找、支持排序
#2.hash索引
	查询效率更高，但只支持精确查找
	不支持范围、前缀查找，不支持排序

4.3.索引实践

#1.索引字段区分度要高(索引字段值不能有太多重复数据)
##1.1.比如：select id,name,age from user where sex=1
##1.2.解释：
	性别只有男，女，每次过滤掉的数据很少，不宜使用索引
	经验上，能过滤80%数据时就可以使用索引。对于订单状态，如果状态值很少，不宜使用索引，如果状态值很多，能够过滤大量数据，则应该建立索引
#2.用好复合索引
##2.1.复合索引，指多个字段联合起来创建索引，比如字段A、字段B，联合创建索引（A,B）
##2.2.利用复合索引，可以有效减少索引数量，索引(A,B),相当于建立了索引(A)，与索引(A,B)
#3.删除冗余重复的索引，原因参考索引的代价