数据库规范详解

主键索引应以 pk_ 开头， 唯一索引要以 uk_ 开头，普通索引要以 idx_ 打头。

 

一、数据库的库名、表名、字段名命名规范：

   1、驼峰表示法：即参考java中命名变量名的规范来命名，也就是如果只有一个单词时，全部小写；多个单词时，第一个单词全小写，后面的所有单词都是首字母大写。举例说明：

     userName，id，userPasswd，creatTime，updateTime，age等。

   2、下划线表示法：就是多个单词之间用下划线分隔开，全部单词小写，32个字符之内，不用保留关键字，所有字段为not null。举例说明：

     user_name,id,user_passwd,create_time,update_time,age等。

综合上面两种命名规范，个人感觉驼峰法更好。感觉看起来美观，可读性强，占用空间小，打字更方便，对使用过java的人来说，更有好。 C/C++ 和 python 是下划线，java 和 go 是驼峰。其实只要写过java或者go程序的人，基本都会觉得驼峰更加优美。下划线真心难打。然而下划线已经是DBA的规范了，就难以改变了。

   

 

   3、字段规范。

      1）非负数值：unsigned

      2）存储IPV4：int unsigned

      3）存储浮点数值：decimal，勿用float和double

      4）合理选择整型范围：tinyint、smallint、mediumint、int、bigint

      5）所有字段均有：not null修饰，并设置default值

      6）合理选择时间类型：year、date、datetime、timestamp

      7）varchar(N)尽可能小：N表示字符数，而不是字节数

      8）尽可能不用：text、blob、enum（用tinyint替换）

   4、库表规范。

      1）禁止分区表；

      2）合理分库分表；

      3）分离冷热数据；

      4）推荐hash散列表，表名后缀使10进制数，数字必须从0开始。按⽇期时间分表需符合YYYY[MM][DD][HH]格式，例如2013071601。年份必须4位数字表示。例如按日散表user_20110209、 按月散表user_201102；

      5）所有表都需要注释；

      6）禁止存放大字段、大数据量（5000W）

 

 

二、全局规范。默认存储引擎，默认字符集

 

 

 

 

 

三、主键规范。每张表必须有主键，推荐使用自增的unsigned整型数据。禁止使用varchar类型作为主键语句设计。

 

 

四、索引规范。

 

 

 

 

 

 

 

 

五、SQL语句规范。

 

 

二、主键规范。

 

命名规范
库名、表名、字段名必须使用小写字母并采用下划线分割

 

库名、表名、字段名禁止超过32个字符，须见名知意

 

库名、表名、字段名支持最多64个字符，统一规范、易于辨识以及减少传输量不要超过32

 

库名、表名、字段名禁止使用MySQL保留关键字

 

临时库、临时表名必须以tmp为前缀并以日期为后缀

 

备份库、备份表名必须以bak为前缀并以日期为后缀

 

基本规范
使用INNODB存储引擎

 

5.5以后的默认引擘，支持事务，行级锁，更好的恢复性，高并发下性能更好，对多核，大内存，ssd等硬件支持更好

 

表字符集使用UTF8

 

使用utf8字符集，如果是汉字，占3个字节，但ASCII码字符还是1个字节

 

统一，不会有转换产生乱码风险

 

所有表都需要添加注释

 

单库的表在500张内

 

单表数据量建议控制在500万行以内

 

不在数据库中存储图片、文件等大数据

 

禁止在线上做数据库压力测试

 

禁止从测试、开发环境直连数据库

 

库表设计规范
禁止使用分区表

 

MySQL的分区表实际性能不是很好，且管理维护成本较高

 

拆分大字段和访问频率低的字段，分离冷热数据

 

用HASH进行散表，表名后缀使用十进制数，下标从0开始

 

首次分表尽量多的分，避免二次分表，二次分表的难度和成本较高

 

按日期时间分表需符合YYYY[MM][DD][HH]格式

 

采用合适的分库分表策略，如百库十个表、十库百表等

 

索引设计规范
索引是一把双刃剑，它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间

 

单张表中索引数量不超过5个

 

单个索引中的字段数不超过5个

 

对字符串使用前缀索引，前缀索引长度不超过10个字符

 

如果有一个CHAR(200) 列，如果在前10个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前10 个字符进行索引能够节省大量索引空间，也可能会使查询更快

 

表必须有主键

 

不使用更新频繁地列作为主键

 

尽量不选择字符串列作为主键

 

不使用UUID、MD5、HASH 作为主键

 

默认使用非空的唯一键

 

主键建议选择自增或发号器

 

重要的SQL必须被索引

 

SELECT、UPDATE、DELETE语句的WHERE条件列

 

ORDER BY、GROUP BY、DISTINCT的字段

 

多表JOIN的字段

 

区分度最大的字段放在索引前面

 

核心SQL优先考虑覆盖索引

 

select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

 

避免冗余或重复索引

 

合理创建联合索引（避免冗余），index(a,b,c) 相当于 index(a) 、index(a,b) 、index(a,b,c)

 

索引不是越多越好，按实际需要进行创建

 

每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能

 

不在低基数列上建立索引，例如‘性别’

 

不在索引列进行数学运算和函数运算

 

尽量不要使用外键

 

外键用来保护参照完整性，可在业务端实现

 

对父表和子表的操作会相互影响，降低可用性

 

INNODB本身对Online DDL的限制

 

不使用%前导的查询，如like “%xxx”

 

无法使用索引

 

不使用反向查询，如 not in /  not like

 

无法使用索引，导致全表扫描

 

全表扫描导致buffer pool利用降低

 

字段设计规范
尽可能不要使用TEXT、BLOB类型

 

删除这种值会在数据表中留下很大的"空洞"

 

可以考虑把BLOB或TEXT列分离到单独的表中

 

用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

 

浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围浮点数的缺点是会引起精度问题

 

将字符转化为数字

 

使用TINYINT来代替ENUM类型

 

字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量

 

the best strategy is to allocate only as much space as you really need

 

VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N

 

VARCHAR(N)，N尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存

 

如果可能的话所有字段均定义为not null

 

使用UNSIGNED存储非负整数

 

同样的字节数，存储的数值范围更大。如tinyint 有符号为 -128-127，无符号为0-255

 

INT类型固定占用4个字节存储

 

使用TIMESTAMP存储时间

 

因为TIMESTAMP使用4字节，DATETIME使用8个字节, 同时TIMESTAMP具有自动赋值以及自动更新的特性

 

使用INT UNSIGNED 存储IPV4

 

使用VARBINARY存储大小写敏感的变长字符串

 

禁止在数据库中存储明文密码

 

SQL设计规范
使用预编译语句prepared statement

 

只传参数，比传递SQL语句更高效

 

一次解析，多次使用

 

降低SQL注入概率

 

java 方法如下： 
protectedbooleanupdateSalary(Connection conn,BigDecimalx,String ID) throws SQLException{ 
PreparedStatementpstmt = null; 
try { 
pstmt = conn.prepareStatement("UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?"); 
pstmt.setBigDecimal(1, x); 
pstmt.setString(2, ID); 
return true; 
} finally{ 
if (pstmt!=null){ 
pstmt.close(); 
} 
} 

 

} 

 

 

 

尽量避免相同语句由于书写格式的不同，而导致多次语法分析

 

避免隐式转换

 

会导致索引失效，如 select userid from table where userid=’1234’

 

充分利用前缀索引

 

必须是最左前缀

 

不可能同时用到两个范围条件

 

避免使用存储过程、触发器、EVENTS等

 

让数据库做最擅长的事

 

降低业务耦合度，为Scale Out、Sharding 留点余地

 

避开BUG

 

避免使用大表的join

 

MySQL最擅长的是单表的主键/二级索引查询

 

Join消耗较多的内存，产生临时表

 

避免在数据库中进行数学运算

 

容易将业务逻辑和DB耦合在一起

 

MySQL不擅长数学运算和逻辑判断

 

无法使用索引

 

减少与数据库的交互次数

 

Insert … on duplicate key update

 

Replace into 、 insert ignore、insert into values(),(),()…

 

Update … where id in (1,2,3,4)

 

Alter table tbl_name add column col1, add column col2

 

拒绝大SQL，拆分成小SQL

 

充分利用query cache

 

充分利用多核CPU

 

使用in代替or，in的值不超过1000个

 

禁止使用order by rand()

 

因为ORDER BY rand()会将数据从磁盘中读取，进行排序，会消耗大量的IO和CPU，可以在程序中获取一个rand值，然后通过在从数据库中获取对应的值

 

使用union all 而不是union

 

程序应有捕获SQL异常的处理机制

 

禁止单条SQL语句同时更新多个表

 

不使用select *

 

消耗cpu和IO、消耗网络带宽

 

无法使用覆盖索引

 

减少表结构变更带来的影响         

 

行为规范
批量导入、导出数据必须提前通知DBA协助观察

 

批量更新数据，如update,delete 操作，需要DBA进行审查，并在执行过程中观察服务

 

禁止在从库上执行后台管理和统计类的功能查询

 

禁止有super权限的应用程序账号存在

 

产品出现非数据库导致的故障时及时通知DBA协助排查

 

上线新功能必须提前通知DBA进行流量评估

 

数据库数据丢失，及时联系DBA进行恢复

 

对单表的多次alter操作必须合并为一次操作

 

不在MySQL数据库中存放业务逻辑

 

重大项目的数据库方案选型和设计必须提前通知DBA参与

 

对特别重要的库表，提前与DBA沟通确定维护和备份优先级

 

不在业务高峰期批量更新、查询数据库

 

提交线上建表需求，必须详细注明所有相关SQL

#########################