1 编写目的
本手册是为指导开发人员按照文档中的规范进行 MySQL 数据库设计及 SQL 编码.
2 数据库对象定义规范
2.1 表的定义规则
2.1.1 表名的规定
使用英语
命名应该使用英文单词, 避免使用拼音, 特别不应该使用拼音简写;
命名不允许使用中文或者特殊字符, 因为不是所有数据库都支持;
只使用字母 (A-Za-z), 数字(0-9) 以及下划线(), 不使用其他字符;
应该以字母开头;
采用单数
表名使用的单词, 应是单数, 而不是复数; 如工人表, 选择 WORKER,
而不是 WORKERS;
除非一些约定俗成的单词, 如 SALES;
采用大写
MySQL 大小写转换需要一定的开销, 要求数据库配置为大小写敏感, 所以要求数据库对象名称建议一律大写, 也方便不同数据库间的移植;
禁止使用大小写混合的方式
单词分隔
命名的各单词之间可以使用下划线(_) 进行分隔;
一般不要超过 30 个字符
表名的前缀
一个项目或一个模块的表名定义建议前缀最好统一, 方便管理
2.1.2 表的设计规则
如果没有特殊要求, 表的缺省引擎采用 Innodb, 支持事务, 锁方式为行锁, 并发性能高.
每个表要求定义主键
平衡范式与冗余关系, 效率优先; 原则上表设计必须遵守第三范式, 如果因为合理的冗余字段将会给我们减少 join 的查询除外;
单行记录禁止超过 8K
禁止使用外键
单表列数目建议小于 30
如果有可能, 把表设计成固定长度的, 可以提高性能.
索引并不是越多越好, 索引固然可以提高相应的 select 的效率, 但同时也降低了 insert 及 update 的效率, 索引最好不要超过 5 个
2.1.3 列的规定
列名建议大写
单词采用单数
是否采用下划线要统一
列名应避免二义性
关联表中的同义字段列名应相同, 一个应用程序中的多个关联表, 含有同样含义的字段, 建议采用相同的列名, 同时字段名的数据类型也要求相同.
如客户 ID 一张表定义为 CUSTOMER_ID 类型 varchar
另一张表定义为 CUSTOMER_NUM 类型 int
如果两张表依赖客户 ID 字段关联查询时, 可能因为隐式转换导致索引失效.
主键
一定要显式定义主键
采用与业务无关的单独列
建议采用自增列
数据类型采用 int, 并尽可能小, 能用 tinyint 就不用 int, 能用 int 就不用 bigint
将主键放在表的第一列
禁止使用 varchar 类型作为主键语句设计
不建议使用 UUID 类型设计主键,
首先 UUID 长度过长, 另外 InnoDB 为聚集主键类型的引擎, 数据会按照主键进行排序, 由于 UUID 的无序性, InnoDB 会产生巨大的 IO 压力
索引列
索引列必须定义为 not null, 并设置 default 值
复合索引, 选择性高的字段排在前面.
字段
l 长度设计需要根据业务实际需要进行长度控制, 禁止预留过长空间. 例如 status 使用 varchar(128)进行存储
l 用尽量少的存储空间来存数一个字段的数据;
例如: 能使用 int 就不要使用 varchar,char, 能用 varchar(16)就不要 varchar(256);
l IP 地址最好使用 int 类型;
l 固定长度的类型最好使用 char, 例如: 邮编;
l 能使用 tinyint 就不要使用 smallint,int;
l 尽可能不用 TEXT,BLOB https://www.yidianzixun.com/channel/w/blob 类型.
l 存储年使用 YEAR 类型, 存储日期使用 DATE 类型, 存储时间 (精确到秒) 建议使用 TIMESTAMP 类型, 因为 TIMESTAMP 使用 4 字节, DATETIME 使用 8 个字节.
总之: MySQL 的字段选择原则, 数据越小, 性能越高, 请选择合适字段类型, MySQL 数据类型及范围介绍见附录 4.2
2.1.4 其它数据库对象定义规则
除表外, 其他对象命名也建议使用不同的前缀来区别. 建议如下:
视图 v_
序列 seq_
簇 c_
触发器 trg_
存储过程 sp_/p_
函数 f_/fn_
主键 pk_
外键 fk_
唯一索引 uk_
普通索引 idx_
说明:
1)为了避免不必要的冲突和麻烦, 数据库对象中不使用数据库关键字和保留字, 具体可阅附录 4.1
2)用户自定义数据库对象: 表, 视图, 主外键, 索引, 触发器, 函数, 存储过程等风格要保持一致.
2.1.5 建表格式规范
l 建表格式示范如下图:
建表格式示范. PNG
l 修改及查看表相关注释语句如下:
1)修改表的注释
alter table tablename comment '修改后的表的注释';
如: alter table TB_EXAMPLE comment '示例表 1';
修改表的注释. PNG
2)修改字段的注释
alter table tablename modify column field_name int comment '修改后的字段注释';
注意: 字段名和字段类型照写就行
如: alter table TB_EXAMPLE modify APP_NAME varchar(255) not null default ''comment'应用名 1';
字段名和字段类型修改. PNG
3) 查看表注释的方法
show create table tablename;
如: show create table TB_EXAMPLE;
查看表注释. PNG
4) 查看字段注释的方法
show full columns from tablename;
如: show full columns from TB_EXAMPLE;
查看字段注释. PNG
3 MySQL SQL 开发规范
3.1 SQL 书写规范
1,sql 编写时, 大小写一致
2, 关键字单占一行, 如 select,from,where,and,group by,order by 等
3, 注意行缩进和对齐, 建议语句中的关键字右对齐
4, 使用空格, SQL 语句内的算术运算符, 逻辑运算符(AND,OR,NOT), 比较运算符(=,<=,>=,>,<,<>,BETWEEN AND),IN,LIKE 等运算符前后都应加一空格.
5, 对较为复杂的 sql 语句加上注释, 说明算法, 功能.
注释风格: 注释单独成行, 放在语句前面.
单行注释:--
多行注释:/* */
6,select 后面的每一列 (列数目大于 1) 单独占一行, where 后面的每个条件 (条件数大于 1) 单独占一行.
7,update set 子句内容每一项单独占一行, 无缩进.
8****,insert 子句内容每个表字段单独占一行, 无缩进; values 每一项单独占一行, 无缩进 .
3.2 SQL 开发技巧规范
Sql 语句尽可能得简单
select * 尽量少用, 仅 select 出需要的字段, 只要一行数据时尽量使用 limit 1
尽量避免在 where 子句中使用 in,not in 或者 having, 使用 exists,not exists 代替
尽量避免两端模糊匹配 like %***%
尽量用 union all 代替 union
拒绝大 sql 语句, 大事物, 大批量, 避免锁表, 拆分大的 DELETE 或 INSERT 语句
如果你有一个大的处理, 一定把其拆分, 使用 LIMIT 条件是一个好的方法. 参考下面是一个示例:
代码如下:
- while (1) {
- // 每次只做 1000 条
- mysql_query("DELETE FROM logs WHERE log_date <='2009-11-01'LIMIT 1000");
- if (mysql_affected_rows() == 0) {
- // 没得可删了, 退出!
- break;
- }
- // 每次都要休息一会儿
- usleep(50000);
- }
数据表最好起别名, 便于 sql 优化器快速分析.
除非业务需要否则不允许在 sql 语句中使用 order by 子句, 禁止使用 order by rand()
多表连接查询
关联表个数限制的基本原则
在报表数据库或批处理数据库中经常会有需要关联多张表做查询的操作, 而这些表有的可能会是大表. 过多的表做关联可能给性能带来严重的影响, 请慎用关联查询.
如果不能满足这个限定条件, 可以考虑如下的两种处理方式:
第一, 增加冗余字段, 对于经常被关联使用的个别字段, 可以考虑在一边增加冗余字段的方式来减少关联, 这是一种反范式化的处理方式, 但经常被用于报表查询类型的系统中.
第二, 采用中间结果表方式. 这种方式就是将原来一个 SQL 完成的操作拆开成多个 SQL 进行, 将某两张或三张表的关联结果先取出, 然后再拿结果集与剩下的表继续做关联, 得到最终完整的结果.
2)连接语法
表连接分以下几种方式
• 等价连接(两边的表严格相等才返回)
A join B on 条件
• 左外连接(左边的表全返回, 右边表关联不上就用 null 返回)
A left join B on 条件
• 右外连接(右边的表全返回, 左边表关联不上就用 null 返回)
A right join B on 条件
• 全外连接(左右两表都不加限制, 全部返回)
Afull join B and 条件
3)连接查询的表字段前必须用表的别名标识.
如 select a.col1,b.col2....
4)避免笛卡尔出现
产生笛卡尔连接的原因就是在多张表进行关联的操作中缺少了表间的连接条件. 由于笛卡尔积产生的结果集将是多表记录的乘积关系, 因此当哪怕只有一张表的记录数比较大时, 其结果集都将被数倍以上地放大, 这势必给数据库性能带来严重影响. 所以在写关联语句时要严格检查连接条件是否有遗漏.
5)join 连接的字段, 字段类型必须相同, 防止隐式转换导致的索引失效.
避免嵌套查询
如: select a.name,(select b.address from table2 b where a.id=b.id) from a table1
用 join 代替:
- Select a.name ,b.address
- From table1 a
- left join table2 b on a.id = b.id
能用连接查询实现的子查询, 应避免使用子查询
如: Select b.address from table1 b where a.id in(select id from table2 a where sex=1)
应改为: select b.address from table1 b , table2 a where b.id=a.id and a.sex=1
或者:
- Select b.address
- From table1 b
- Inner join table2 a on b.id = a.id
- Where a.sex=1
Insert 语句也要写出字段名称, 避免因表结构发生改变时发生编译错误
如: insert into table (col1,col2,col3...) values(?,?,?...)
更新语句
查询, 更新语句中严格选择所操作的字段.
尽量减少表被访问的次数, 看下面这个 SQL:
- UPDATE tb_target a
- SET a.col1=
- (SELECT b.col1FROM tb_source b WHERE b.id = a.id),
- a.col2 =
- (SELECT b.col2 FROM tb_source b WHERE b.id = a.id),
- a.col3 =
- (SELECT b.col3 FROM tb_source b WHERE bid = a.id),
- a.col4 =
- (SELECT b.col4 FROM tb_source b WHERE b.id = a.id)
- WHERE a.id IN (SELECT b.id FROM tb_source b)
该语句作用其实就是用更新一个表的多个列, 但这样写会多次扫描源表.
如果改写成如下的方式, 则只需要扫描一次了:
- UPDATE tb_target a
- SET (a.col1,a.col2,a.col3,a.col4) =
- (SELECT b.col1,b.col2,b.col3,b.col4
- FROM tb_ source b
- WHERE b.id = a.id)
- WHERE EXISTS (SELECT 1 FROM tb_source b WHERE b.id = a.id)
减少控制语句的检查次数
如在 if...else 语句中, 尽可能最常用的, 数量多的符合条件前置以被先检查到, 减少条件筛选的次数.
比如在人口清洗中, 需要按民族分批处理时, 最大数量符合条件的就是汉族, 第一个处理
4 附录
4.1 常用保留字列表
- A
- ACTION ADD ALL ALTER ANALYZE AND AS ASC ASENSITIVE
- B
- BEFORE BETWEEN BIGINT BINARY BIT BLOB BOTH BY
- C
- CALL CASCADE CASE CHANGE CHARCHARACTER COLLATE COLUMN CONDITION CONNECTION CONSTRAINT CONTINUE CONVERT CREATE CROSS CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CHECK
- D
- DATABASE DATABASES DATE DAY_HOUR DAY_MICROSECOND DAY_MINUTE DAY_SECOND DEC DECIMAL DECLARE DEFAULT DELAYED DELETE DESC DESCRIBE DETERMINISTIC DISTINCT DISTINCTROW DIV DOUBLE DROP DUAL
- E
- EACH ELSE ELSEIF ENCLOSED ENUM ESCAPED EXISTS EXIT EXPLAIN
- F
- FALSE FETCH FLOAT FOR FORCE FOREIGN FORM FULLTEXT
- G
- GOTO GRANTGROUP
- H
- HAVING HIGH_PRIORITY HOUR_MICROSECOND HOUR_MINUTE HOUR_SECOND
- I
- IF IGNORE
- IN
- INDEX INFILE INNER INOUT INSENSITIVE INSERT INT INTEGER INTERVAL INTO IS ITERATE
- J
- JOIN
- K
- KEY KYES KILL
- L
- LEADING LEAVE LEFT LIKE LIMIT LINES LOAD LOCALTIME LOCALTIMESTAMP LOCK
- LONG LONGBLOB LONGTEXT LOOP LOW_PRIORITY
- M
- MATCH MEDIUMBLOB MEDIUMINT MEDIUMTEXT MIDDLEINT MINUTE_MICROSECOND MINUTE_SECOND MOD MODIFIES
- N
- NATURAL NO NO_WRITE_TO_BINLOG NOT NULL NUMERIC
- O
- ON OPTIMIZE OPTION OPTIONALLY OR ORDER OUT OUTER OUTFILE
- P
- PRECISION PRIMARY PROCEDURE PURGE
- R
- READ READS REFERENCES REGEXP RELEASE RENAME REPEAT REPLACE REQUIRE RESTRICT
- RETURN REVOKE RIGHT RLIKE REAL
- S
- SCHEMA SCHEMAS SECOND_MICROSECOND SELECT SENSITIVESEPARATOR SET SHOW SMALLINT SONAME SPATIAL SPECIFIC SQL SQL_BIG_RESULT SQL_CALC_FOUND_ROWS
- SQL_SMALL_RESULT SQLEXCEPTION SQLSTATE SQLWARNING SSL STARTING STRAIGHT_JOIN
- T
- TABLE TERMINATED TEXT THEN TIME TIMESTAMP TINYBLOB TINYINT TINYTEXT TO
- TRAILING TRIGGER TRUE
- U
- UNDO UNION UNIQUE UNLOCK UNSIGNED UPDATE USAGE USE USING UTC_DATE UTC_TIME UTC_TIMESTAMP
- V
- VALUES VARBINARY VARCHAR VARCHARACTER VARYING
- W
- WHEN WHERE WHILE WITH WRITE
- X
- XOR
- Y
- YEAR_MONTH
- Z
- ZEROFILL
4.2 MySQL 数据类型介绍
4.2.1 数字类型列表
数字类型列表. PNG
4.2.2 日期类型列表
日期类型列表. PNG
4.2.3 字符类型列表
字符类型列表. PNG
来源: http://www.jianshu.com/p/6febf3001edb