数据库表设计规范
数据库命名规范
- 所有的数据库对象名称必须使用小写字母并用下划线表示,因为默认情况下,
mysql
对大小写敏感,mysql
数据库本质上是linux
系统下的一个文件,而linux
系统是大小写敏感的 - 所有数据库对象名称禁止使用
mysql
保留关键字 - 数据库对象的命名要能做到见名知意,并且最好不要超过
32
个字符。太长不方便使用,并且会在传输时增加网络开销 - 临时表必须以
tmp_
为前缀并以日期
为后缀 - 备份表必须以
bak_
为前缀并以日期
为后缀 - 所有存储相同数据的列名和列类型必须一致,比如
user
表中的user_id
和order
表中的user_id
数据库基本设计规范
-
所有表必须使用
Innodb存储引擎
Innodb引擎
是5.6
之后的默认存储引擎;mysql5.5
之前使用Myisam
(默认存储引擎)
Innodb引擎
优点:支持事务,行级锁,更好的恢复性,高并发下性能更好 -
数据库
和表
的字符集统一使用utf8mb4
-
数据库
,表
,字段
字符集一定要统一,统一字符集可以避免由于字符集转换产生的乱码 -
所有
表
和字段
都需要添加注释
-
禁止在数据库中存储图片,文件等二级制数据
索引设计规范(Innodb中主键实质上是一个索引)
-
限制每张表上索引数量,建议单表
不超过5个
索引。索引并不是越多越好,可以提高查询效率,但是会降低插入和更新的效率。甚至在一些情况下,还会降低查询效率,因为mysql
优化器在选择如何优化查询时,会根据统计信息,对每一个可用索引来进行评估,以生成一个最好的执行计划,如果同时有很多索引都可以用于查询,就会增加mysql
查询优化器生成查询计划的时间 -
每个
Innodb
表都必须有一个主键。Innodb
是一种索引索引组织表,是指数据存储的逻辑顺序和索引的顺序是相同,Innodb
是按照主键索引的顺序来组织表的,因此,每个Innodb
表都必须要有一个主键,如果我们没有指定主键,那么Innodb
会优先选择表中第一个非空唯一索引来作为主键,如果没有这个索引,那么Innodb
会自动生成一个占6字节的主键,而这个主键的性能并不是最好 -
不使用更新频繁的列作为主键,不使用多列联合主键。因为Innodb是一种索引组织表,如果主键上的值频繁更新,就意味着数据存储的逻辑顺序频繁变动,必然会带来大量的IO操作,降低数据库性能
-
不要使用
uuid
,md5
,hash
,字符串
列作为主键。因为这种主键不能保证主键的值是顺序增长的,如果后来的主键值在已有主键值的中间段,那么这个主键插入的时候,会将所有主键值大于它的列都向后移 -
最好选择能保证值的顺序为顺序增长的列为主键。并且数据不能重复,建议用mysql自增id建立主键
在select
,delete
,update
的where
从句中的列
包含在order by
,group by
,distinct
字段中的列 -
多表
join
的关联列:mysql
对关联操作的处理方式只有一种,那就是嵌套循环的关联方式,所以这种操作的性能对关联列上的索引的依赖性很大 -
复合索引:从左到右的顺序来使用
区分度(列中group by
的数目和此列总行数的比值趋近于1)最高的列放在联合索引的最左侧
在区分度差不多的情况下,尽量吧字段长度小的放在联合索引的最左侧,因为同样的行数,字段小的文件也小,读取时IO性能更优
使用最频繁的列放在联合索引的左侧,这样的话,可以较少地建立索引就能满足需求 -
避免建立冗余索引和重复索引
-
对于频繁的查询优先使用覆盖索引,就是包含了所有查询字段的索引,这样可以避免Innodb表进行索引的二次查找,并可以把随机IO变为顺序IO提高查询效率
-
尽量避免使用外键,
mysql
和别的数据库不同,会自动在外键上建立索引,会降低数据库的写性能 -
建议不使用外键约束,但是一定要在表与表之间的关联键上建立索引,虽然外键是为了保证数据的完整性,但是最好在代码中去保证
字段设计规范
-
优先选择符合存储需要的最小的数据类型
-
尽量将字符串转化为数字类型存储:如将
ip
存储为数字:inet_aton('255.255.255.255') = 4294967295
,反之,inet_ntoa(4294967295) = '255.255.255.255'
-
对于非负整型数据,优先使用无符号整型来存储,如:
id
,age
,无符号相对于有符号,可以多出一倍的存储空间 -
mysql
中,varchar(n)
中n表示字符数而不是字节数 -
避免使用
text
,blog
来存储字段,这种类型只能使用前缀索引,如果非要使用,建议将这种数据分离到单独的拓展表中 -
避免使用
enum
类型。枚举本身是一个字符串类型,但是内部确是用正数类型来存储的,所以最多可存储65535
种不同的值,修改的话必须使用alter语句,直接修改元数据,有操作风险;order by
效率低,必须转换并无法使用索引,禁止使用数值作为enum
值,因为enum
本身是索引顺序存储的,会造成逻辑混淆 -
尽可能把所有列定义为
not null
,使用默认值,索引null
列需要额外的空间来保存,占更多空间,进行比较和计算时,对null
值作特别的处理,可能造成索引失效 -
禁止使用字符串来存储日期型数据,可以使用
datetime
或者bigint
类型,无法使用日期函数计算,比较字符串存储要占更多的内存空间,datetime(8字节)和timestamp(本身是以int存储) -
财务相关数据,使用decimal类型 (精准浮点类型,在计算时不丢失精度)。
SQL开发规范
-
避免数据类型的隐式转换,一般出现在where从句中,会导致索引失效,如:
select id,name from user where id = 12
禁止select id,name from user where id = '12'
-
充分利用已存在的索引,避免使用
双%
的查询条件,不走索引, 可以使用select id,name from user where name = '测试%'
作为查询 -
一个
SQL
只能利用到复合索引
中的一列进行范围查询 -
使用
left join
或not exists
来优化not in
操作 -
禁止使用
select *
来查询,必须用字段名 -
数据库更适合处理批量操作,合并多个相同的操作到一起,提高处理效率
-
使用
in
代替or
,in
的值不要超过500个
,in
操作可以有效利用索引 -
禁止使用
order by rand()
进行随机排序 -
禁止在
where
从句中对列进行函数转换和计算
本文由 feng 创作,采用 知识共享署名4.0
国际许可协议进行许可
本站文章除注明转载/出处外,均为本站原创或翻译,转载前请务必署名
最后编辑时间为:2021-02-20 00:00:00