MySQL数据库开发设计规范10.docx
《MySQL数据库开发设计规范10.docx》由会员分享，可在线阅读，更多相关《MySQL数据库开发设计规范10.docx（13页珍藏版）》请在冰豆网上搜索。
MySQL数据库开发设计规范10
MySQL数据库开发设计规范
文档资料信息
文档名称：
MySQL数据库开发设计规范
文档版本号：
文档版本日期：
准备者：
准备日期：
审定者：
审定日期：
版本历史
版本号：
版本日期：
修订者：
描述：
文件名：
1.0
初始版本
MySQL数据库开发设计规范1.0
注意事项
范围和简介
1.1简介
针对关系型数据库MySQL的相关特性，拟定的相关开发过程的规范，其旨在通过该规范的约束和建议，使开发人员可以在他们所编写的代码中保持统一正确的风格，提供代码的可读性以及减少出现错误的几率。
1.2范围
本规范仅适用于关系型数据库系统MySQL以及其对应的SQL脚本语言。
1.3术语和定义
本规范采用以下的术语描述：
★规则：
编程时强制必须遵守的原则。
★建议：
编程时必须加以考虑的原则。
★说明：
对此规则或建议进行必要的解释。
★示例：
对此规则或建议给出样例。
命名规范
1.4总则
【规则】库、表、字段命名的字符范围为：
a-z,0-9和_（下划线）。
【规则】长度控制在30个字符以内
【规则】采用英文单词、英文短语（包括缩写）作为名称
【规则】特殊约定或缩写，要有注释说明
【规则】禁用保留字命名
【说明】mysql保留字
ADD
CONNECTION
X509
HAVING
CURRENT_TIMESTAMP
ALL
CONSTRAINT
DIV
VARYING
DAY_MICROSECOND
ALTER
CONTINUE
DOUBLE
HOUR_MINUTE
LOCALTIMESTAMP
ANALYZE
CONVERT
DROP
HOUR_SECOND
MINUTE_MICROSECOND
AND
CREATE
DUAL
IF
NO_WRITE_TO_BINLOG
AS
CROSS
EACH
IGNORE
SQLEXCEPTION
ASC
WHEN
ELSE
IN
SQL_BIG_RESULT
ASENSITIVE
WHERE
ELSEIF
INDEX
SQL_CALC_FOUND_ROWS
BEFORE
WHILE
ENCLOSED
INFILE
MINUTE_SECOND
BETWEEN
WITH
ESCAPED
INNER
SECOND_MICROSECOND
BIGINT
DATABASE
EXISTS
INOUT
HOUR_MICROSECOND
BINARY
DATABASES
EXIT
INSENSITIVE
SQL_SMALL_RESULT
BLOB
DAY_HOUR
EXPLAIN
INSERT
UTC_TIMESTAMP
BOTH
DAY_MINUTE
FETCH
INT
VARCHARACTER
BY
DAY_SECOND
FLOAT
INT1
STRAIGHT_JOIN
CALL
DEC
FLOAT4
INT2
HIGH_PRIORITY
CASCADE
DECIMAL
FLOAT8
INT3
CURRENT_DATE
CASE
DECLARE
FOR
INT4
CURRENT_TIME
CHANGE
DEFAULT
FORCE
INT8
CURRENT_USER
CHAR
DELAYED
FOREIGN
INTEGER
DETERMINISTIC
CHARACTER
DELETE
FROM
INTERVAL
DISTINCTROW
CHECK
DESC
FULLTEXT
INTO
LOW_PRIORITY
COLLATE
DESCRIBE
GOTO
IS
UTC_TIME
COLUMN
WRITE
GRANT
ITERATE
REFERENCES
CONDITION
DISTINCT
GROUP
JOIN
YEAR_MONTH
KEY
MODIFIES
RELEASE
OPTIONALLY
ZEROFILL
KEYS
NATURAL
RENAME
TABLE
VALUES
KILL
NOT
REPEAT
TERMINATED
VARBINARY
LABEL
NULL
REPLACE
THEN
VARCHAR
LEADING
NUMERIC
REQUIRE
TINYBLOB
SQLSTATE
LEAVE
ON
RESTRICT
TINYINT
SQLWARNING
LEFT
OPTIMIZE
RETURN
TINYTEXT
SSL
LIKE
OPTION
REVOKE
TO
STARTING
LIMIT
FALSE
RIGHT
TRAILING
READS
LINEAR
OR
RLIKE
TRIGGER
REAL
LINES
ORDER
SCHEMA
UNDO
TRUE
LOAD
OUT
SCHEMAS
UNION
REGEXP
LOCALTIME
OUTER
SELECT
UNIQUE
MEDIUMINT
LOCK
OUTFILE
SENSITIVE
UNLOCK
MEDIUMTEXT
LONG
PRECISION
SEPARATOR
UNSIGNED
MIDDLEINT
LONGBLOB
PRIMARY
SET
UPDATE
MOD
LONGTEXT
PROCEDURE
SHOW
USAGE
MEDIUMBLOB
LOOP
PURGE
SMALLINT
USE
READ
XOR
RAID0
SPATIAL
USING
SQL
MATCH
RANGE
SPECIFIC
UTC_DATE
1.5库命名
【规则】使用项目名称作为库名
【规则】单库仅使用a-z，分库名称为“库名_编号”
1.6表命名
【规则】单表仅使用a-z、_，分表名称为“表名_编号
【建议】表名代表用途、内容，格式:
名称_后缀
【建议】不使用tab或tbl作为后缀
【说明】常用后缀
´关联表命名为re_表a_表b,
´临时表必须使用后缀”_tmp”
´备份表必须使用后缀“_bak”
´字典表统一使用后缀以”_dic”
´历史归档表必须使用后缀”_his”,例:
task_his
´日志表必须使用后缀“_log”,例:
task_log
´类型表必须使用后缀”_type”.例:
task_type
´含有大字段blob或text列必须使用后缀”_lob”
1.7字段命名
【规则】不使用任何前缀
【建议】字段名表达精确，避免歧义，
【建议】遵循“见名知意”的原则,格式:
名称_后缀
【建议】布尔型的字段，以助动词（has/is）开头
【说明】常用后缀
´流水号,命名以id结尾.例:
task_id
´类别,命名以type结尾.例:
station_type
´名称,命名以name结尾.例:
enduser_name
´描述,命名以desc结尾.例:
station_desc
´代码,命名以code结尾.例:
error_code
´标志,命名以flag结尾.例:
debug_flag
´时间,命名以time结尾.例:
insert_time
1.8索引命名
【规则】命名格式：
前缀_表名（或缩写）_字段名（或缩写）
【建议】复合索引，取每字段前三个字符加下划线组合
【说明】常用前缀
´主键必须使用前缀“pk_”
´外键必须使用前缀“fk_”
´UNIQUE约束必须使用前缀“uk_”
´普通索引必须使用前缀“idx_”
1.9视图
【规则】命名格式：
单表：
v_表名（缩写），多表：
v_用途（缩写）
1.10用户
【规则】与数据库名字保持一致
设计规范
1.11总则
【规则】表使用innodb存储引擎
【规则】表、重要字段必须有注释
【建议】拒绝大事务、大SQL、大批量、大字段
【建议】数据库层不做运算，运算放到程序端
1.12库设计规范
【规则】库表字符集和前端程序、中间件必须保持一致UTF8
【说明】对恶魔字符集emoji用utf8mb4（ios常用）
【规则】创建DB和表时显示指定字符集
【示例】
createdatabasesakiladefaultcharactersetutf8;
【规则】禁止使用外键、routine、trigger
1.13表设计规范
【规则】innodb表必须有主键列，使用intunsigned/bigintunsigned，缺省auto_increment，并禁止更新。
【规则】必须显示指定engine和charset
【规则】必须添加comment注释
【示例】
CREATETABLE`report_operation`（
`id`intUNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'primarykey',
`passport_id`int（11）NOTNULLDEFAULT'0'COMMENT'affiliate_id',
`offer_id`int（11）NOTNULLDEFAULT'0'COMMENT'offer_id',
`click`int（11）NOTNULLDEFAULT'0'COMMENT'thenumberofclick',
`conversion`int（11）NOTNULLDEFAULT'0'COMMENT'thenumberofconversion',
`revenue`decimal（10,3）NOTNULLDEFAULT'0.000'COMMENT'revenue',
`cr`decimal（10,4）NOTNULLDEFAULT'0.0000'COMMENT'therateofclicking',
`datetime`dateNOTNULLDEFAULT'0000-00-00'COMMENT'date',
PRIMARYKEY（`id`）
）ENGINE=InnoDBAUTO_INCREMENT=131DEFAULTCHARSET=utf8COMMENT='theinfoofofferrunning';
【建议】表数量不超过300个
【建议】单表行数纯int不超过2000W，含char不超过1000W
【建议】临时表和备份表定期清理
1.14字段设计规范
【规则】所有字段必须为NOTNULL，空值则指定default值
【规则】禁用ENUM、SET类型
【规则】单表字段数上限控制在20~50个
【规则】对货币等对精度敏感的数据，应该用定点数
【规则】单个字符的全部使用CHAR类型
【规则】变长使用varchar类型
【建议】少用TEXT/BLOB类型，用则拆分
【建议】使用整数保存ip地址和timestamp时间
【建议】单行不超过200byte，单表不超过50个纯int字段，或不超过20个varchar字段
【建议】字段的宽度足够用，但也不要过宽
【建议】短数据使用tinyint或smallint
【建议】使用unsigned存储非负数值。
【建议】精确到天，使用date类型
【建议】精确到时间（年月日时分秒），使用DATETIME或TIMESTAMP
1.15索引设计规范
【规则】主键列为独立于业务的AUTO_INCREMENT列或全局ID为主键
【规则】禁用外键，使用程序保证约束
【规则】字段过长列使用前缀索引
【规则】重要SQL需求的索引优先
【建议】单个索引长度不超过255字节
【建议】单个表上索引不超过5个
【建议】复合索引字段的顺序：
区分度大的列放在前
【建议】重复值过高的列（sex:
male、female），索引无效，不应该创建索引
【建议】索引过多，影响insert及update的效率，索引数最好不要超过4个。
应用设计规则
【规则】禁用存储过程
【规则】禁用触发器；
【规则】禁用视图
【规则】禁止使用短连接频繁连接数据库
【规则】设计分区表时，严禁将经常update的字段设计为分区键
【规则】应用账号密码必须加密存储，程序命令行禁止显示账号密码
【规则】禁止使用BASE库存放业务数据，BASE库只能存放配置数据
【规则】应用设计时，必须考虑好程序使用的相关主键和索引，严禁有全表扫描程序上线；
【建议】程序或手工生成的临时数据应及时清除
【建议】手工创建的临时表的命名方式必须以TMP_YYYYMMDD_开头，手工创建的备份表的命名方式必须以BAK_YYYYMMDD_开头，由系统统一删除30天以前的数据
【建议】避免频繁创建和删除临时表，以减少系统表资源的消耗。
【建议】尽量使用表变量来代替临时表。
【建议】需要重复引用大型表或常用表中的某个数据集时，可以使用临时表。
【建议】应用程序失效数据时必须考虑周全，应清除所有相关数据，避免垃圾数据产生
【建议】应用设计时，考虑尽量减少对大字段操作的频率
【建议】频繁访问接口表可以使用分区表，但必须制定数据清理机制，避免接口表不断膨胀
SQL设计规范
【规则】永远用小结果集驱动大的结果集；
【规则】必须查询执行计划，严禁有全表扫描.
【规则】尽可能在索引中完成排序；
【规则】只取出自己需要的Columns,严禁出现select*;
【规则】仅仅使用最有效的过滤条件;
【规则】尽可能避免复杂的Join和子查询；能不使用子查询就不使用子查询
【规则】禁止使用selectforupdate语句.
【规则】禁止WHERE子句的左列上使用函数、括算术运算、其他表达式运算。
【规则】避免数据隐形转换
【规则】禁止单步commit，分批commit（100-10000）
【规则】SQL中禁用HINT信息
【规则】SQL中禁止出现不带任何条件的DELETE和UPDATE语句；
【建议】关键SQL语句，表关联原则上不能超过2个
【建议】应用程序的select操作尽量放在update，delete，insert操作之前，以缩短数据库事务时间
【建议】在业务运行期间运行的程序中尽量避免出现ddl（create、drop等）操作
【建议】尽量使用PK键值作为应用中更新或删除记录条件
【建议】SQL语句统一使用小写格式，避免同一SQL语句因为书写不一致导致多次解析
【建议】大批量数据dml（update、delete、insert等）操作时，需要分批提交
【建议】insert表时建议写字段名，避免表顺序变动后字段插错顺序
【建议】like子句尽量前端匹配，前端不加%，以便能够利用索引
【建议】使用简单的SQL语句，复杂的SQL可以拆分成多个小的SQL执行。
【建议】尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
【建议】尽量避免大事务操作。
【建议】groupby语句如果得到的resultset需要sort,建议加上orderby；
【建议】尽量避免全表扫描
●避免在where子句中使用!
=或<>操作符
●避免在where子句中对字段进行null值判断（在字段上设置默认值0，确保表中列没有null值）
●避免在where子句中使用or来连接条件unionall代替
●避免like'%abc%'
【建议】delete必须写清楚条件，确认需要删除的数据范围。
【示例】全表清理的各种写法：
●deletefromtest_delete;
●deletefromtest_deletewhereidin（1,2,3,4,5）;
●truncatetabletest_delete;
●deletefromtest_deletewhereid>0;
●deletefromtest_deletewhere'a'='a';
●deletefromtest_deletewhereid=1or'a'='a';
●deletefromtest_deletewhereid;
●deletefromtest_deletewhereexists（select*from（select*fromtest_deletewhereid=5）asb）;
【建议】使用leftjoin替代notin/notexist
【示例】leftjoin替代写法
select*fromt1whereidnotin（selectidfromt2）;
select*fromt1wherenotexists（selectidfromt2wheret1.id=t2.id）;
替代写法
selecttable1.*fromtable1leftjointable2ontable1.id=table2.idwheretable2.idisnull;
【建议】重写in子查询
selectdistinctt1.*fromt1wheretabidin（selecttabidfromt）;
重写为：
selectdistinctt1.*fromt,t1wheret1.tabid=t.tabid;
selectafromt2whereb=some_const---返回单值
select*fromt1wheret1.col_namein（selectafromt2whereb=some_const）;
重写为：
select*fromt1wheret1.col_name=（selectafromt2whereb=some_const）;
【建议】重写exist子查询
select*fromt1whereexists（select*fromt2wheret2.column1=t1.column1andt2.column2=t1.column2）;
重写为
selectt1.*fromt1,t2wheret2.column1=t1.column1andt2.column2=t1.column2;
升级会员 