Mysql建表规范备忘录

本文主要是介绍Mysql建表规范备忘录，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一、基础规范

(1) 必须使用INNODB存储引擎

• SQL:   ENGINE=InnoDB

(2) 创建数据库,字符集统一为utf8mb4,比较规则为utf8mb4_general_ci

      创建数据表,字符集统一为utf8mb4

      (DBA在创建数据库时已经指定比较规则collate属性,开发在提交DDL时,无需再特殊指定,保持跟数据库比较规则一致即可)

• Utf8mb4可以支持emoji/特殊符号.
• 统一的数据库字符集以及比较规则可减少字符隐式转换,表关联时提高查询性能
• 建表时设置 CHARSET=utf8mb4 即可,无需对字段重复设置

(3) 所有表,字段都必须要有注释

• 注释可增加表的可读性,方便问题查找跟踪
• SQL:  COMMENT ‘主键ID’

(4) 不使用blob和text等大容量字段

• 大对象索引会补全所有长度建立索引，效率低
• 需要额外存储
• 大字段需放在专门存储服务器上，在数据表字段中存储路径

(5) 表必须创建无符号(unsigned)自增(auto_increment)整型（int,bigint）主键

不要使用非数字类型做主键（防止发生数据、索引块的分裂）

• id做主键，可以明显减小二级索引大小
• id可以在去重、查询数据量、统计查询时发挥作用
• id可以在数据增量拉取和同步时发挥作用
• SQL:  `id` int unsigned NOT NULL AUTO_INCREMENT comment '主键ID',
• primary key(`id`)

(6)必须:所有表添加是否删除(deleted)、创建时间(create_time)和更新时间(update_time)字段

• 是否删除字段.标识该记录是否删除,消除物理删除.(所有记录不允许物理删除,只能通过该标记位标示是否删除),需要开发在程序中维护该字段
• 创建时间字段.标示记录的创建时间,设置DEFAULT CURRENT_TIMESTAMP由数据库自动维护
• 更新时间字段.标示记录的更新时间,设置DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP由数据库自动维护
• 在create_time和update_time列上创建索引,方便检索数据.
• SQL:
• `deleted` tinyint not null DEFAULT 0 COMMENT '是否删除 0 未删除 1 删除 默认是0',

 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间，默认当前时间',       `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间，默认当前时间'

   并且创建基于create_time和update_time的索引

          key idx_create_time(`create_time`);

          key idx_update_time(`update_time`);

 (7) 列尽量避免使用NULL：应该指定列为NOT NULL，除非你想存储NULL。

应该用0、一个特殊的值或者一个空串代替空值。

• 在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂
• 同时在保存与处理NULL类形时，也会做更多的工作，所以从效率上来说，不建议用过多的NULL

 (8) 字段类型选择与字段长度设计

• 根据业务场景合理的设计表字段类型以及字段长度
• 如时间类型字段使用datetime或timestamp
• 列长度设计需合理,也要考虑扩展性,不宜定义过长,如mobile varchar(20)
• 如果列长度过长,且需要创建索引时,尽量使用前缀索引,如index idx_abc(abc(20))
• 禁止在数据库中存储明文密码，把密码加密后存储
• 存储ip最好用int存储而非char(15)，通过MySQL函数inet_ntoa和inet_aton来进行转化。Ipv6地址目前没有转化函数，需要使用DECIMAL或者两个bigINT来存储。
• 数据库中不允许存储大文件，或者照片，可以将大对象放到磁盘上，数据库中存储它的路径

说明:  MYSQL数据库varchar和char括号后面的数字表示的是最大存储的字符数,  如 varchar(20)可以存放20个字母或汉字 需根据实际使用长度建立合适的字符长度,可使用char_length()函数查看预估将来存储字段长度

常用字段长度建议

        省份   province varchar(10)

        城市   city varchar(10)

        姓名  name varchar(20)

        电话   mobile varchar(20)

        链接   url varchar(128或者255)

        类型type,标示deleted,flag,状态state,status   设置为tinyint

        备注   comment varchar(100或300或500)  需在程序中控制输入字符长度

        所有的ID字段,如stu_id,stu_user_id,user_id,app_id,tea_id等都设置为int类型

        所有的时间字段,如只有日期,则设置为date类型

                                  如含有日期和时间信息,则设置为datetime或timestamp类型

                                  如只有年,月,或者年月信息,则建议设置为char类型

       原则:数据类型选择优先级 timestamp>datetime,date>int>char>varchar>text,blob

• 数据库字段长度见附录二

(9)不要使用数据库保留关键字

• 尽量不要使用数据库保留关键字,数据库保留关键字见附录一

(10)命名规范

• 库名、表名、字段名必须使用小写字母和下划线’_’，不能超过32个字符，不使用关键字，取名需有意义。
• 备份表以_bak_日期结尾,如"_bak_20191202"
• 临时表以_tmp_日期结尾,如"_tmp_20191202"
• 唯一索引以uni_开头,按照"uni_字段名称_字段名称"进行命名
• 普通索引以idx_开头,按照"idx_字段名称_字段名称"进行命名

(11)不使用外键做数据完整性约束，从业务代码上控制

(12)浮点数类型字段使用decimal，禁止使用float、double类型

(13) 不允许使用ENUM和SET类型

• 插入非法值的时候，默认会插入一个空值
• ddl成本太高
• 且在比较时存在隐式转换

 

数据库建表示例：

create table test_01(id int unsigned not null auto_increment comment 'ID主键',name varchar(20) not null default '' comment '姓名',sex char(1) not null default '' comment '性别(男,女)',age smallint not null default 0 comment '年龄',address varchar(100) not null default '' comment '地址',phone varchar(20) not null default '' comment '电话',deleted tinyint not null DEFAULT 0 COMMENT '是否删除 0 未删除 1 删除 默认是0',create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间，默认当前时间',update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间，默认当前时间',primary key(id),unique key uni_phone(phone),index idx_name(name),index idx_address(address(20)),index idx_create_time(create_time),index idx_update_time(update_time)) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci comment '示例表';

注：程序中不允许物理删除字段内容,而是通过deleted字段维护数据逻辑删除(该字段开发必须在程序中维护)

 

二、索引规范

(1)主键准则

• 使用无符号(unsigned),自增(auto_increment),int或bigint列作为ID主键

(2)建立索引原则

• 单张表中索引数量不超过7个（建议3-4个），组合索引中的字段数不超过3个
• 区分度高的建立索引(如手机号,时间)，区分度低的不建立索引 (如性别,BU信息)
• 根据SQL建立合理的复合索引，考虑最左前缀原则，一般是区分度较高的放在前边
• 索引字段的默认值不能为NULL，要改为其他的default或者空。NULL非常影响索引的查询效率
• 避免冗余和重复索引
• 索引要综合评估数据密度和分布以及考虑查询和更新比例

 (3)索引使用原则

• 不在索引列进行数学运算和函数运算
• 关联字段且需要走索引的字段必须在字符集保持一致，否则无法应用索引
• 注意隐式转换规则，优先级时间类型>数字类型>字符类型
• 使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary
• 建立索引必须给DBA提供对应的SQL语句或者业务场景，SQL变更需要确认索引是否需要变更并通知DBA
   

三、SQL规范

(1) 事务设计原则：事务要简单，整个事务的时间长度不要太长，默认使用自动提交 

• 代码中手动开启事务时，一定要显示提交或者回滚事务
• 禁止可能造成长事务的情况：
• 程序中手动开启事务，没有在正常结束或者异常结束时关闭事务
• 事务中含有慢查询、或者外部调用，外部调用失败时，事务没有关闭
• 在高并发写情况下需合理设计事务：
• 选择正确的事务隔离级别（默认是Repeatable Read），建议使用Read Committed
• 需要在数据库解决并发写冲突的，首选乐观锁解决，其次考虑悲观锁。
• 程序中有请求其他系统服务的，可以做成异步请求或者放到事务外边
• 保证事务中所有的dml语句有序执行

(2)优化原则

• 业务端需要控制减少数据查询范围

解读:减少数据查询范围,能减少数据库压力并提高响应时间.(许多慢SQL都是因为没有控制查询范围引起的)程序应该在源端控制查询范围,不能大范围查询数据.

• 不要用select *，查询哪几个字段就select 这几个字段

解读：避免后期如果表字段做了增加操作而导致的应用异常;有大字段的表，减少不必要的 IO;减少无用数据检索;需要 GROUP BY 和 ORDER BY 的减少不必要的数据排序

• SQL语句尽可能简单,尽量减少JOIN操作
• 禁止在业务数据库环境写复杂的多表关联查询

解读：开发人员在设计可以逆范式，同时考虑冗余字段的实时性以及扩展性问题

       关联查询需要消耗大量的数据库资源,导致数据库性能下降.

• 索引字段不要出现隐式转换，优先级时间类型>数字类型>字符类型

解读：比如mobile字段是varchar类型，且有索引，如下语句会导致不走索引，select from where mobile=11111111111;

• 在SQL语句中，禁止使用前缀是%的like模糊匹配查询

解读：可以从业务端优化，比如先在前端实现模糊匹配，然后传入到后台；如果非得实现全文检索功能，可以考虑ES大表分页注意效率。

• 注意大表分页查询效率，Limit越大，效率越低。

解读：可以改写limit，比如：
select id from t limit 10000, 10; => select id from t where id > 10000 limit10;
SELECT FROM table ORDER BY TIME DESC LIMIT 10000,10; => SELECT FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10. => SELECT FROM table inner JOIN(SELECT id FROM table ORDER BY TIME LIMIT 10000,10) as t USING(id)

• 判断一个结果集中的记录是否在另一个结果集中存在，用exists优于in、any或者count()
• 注意AND和OR运算符的优先级，AND运算符的优先级高于OR运算符，如果WHERE条件中同时出现，务必使用小括号明确优先级
• 索引尽量不使用NOT的否定谓词，使用NOT EXISTS代替NOT IN

解读：首先是效率问题，其次是在使用NOT IN时，子查询只要有一个值是null，那么没有结果命中

• 不要在where条件中对索引列使用or

解读：同一个字段改写成where in list方式，不同字段采用union、union all方式

• 大表更新时，不要一次全部更新，分批次更新。

解读：
对数据的更新要打散后批量更新如一次更新2000条记录，不要一次更新太多数据。对于这种操作不要先查询出批次数据，再逐条更新，使用update xxx where 更新字段=未更新值 limit 2000，根据返回的更新行数来判断是否已经更新完毕

• IN条件里面的数据数量要少

解读：mysql虽然没有in list个数限制和sql长度限制，但是对于数据包的大小限制，通过max-allowed-packet设置，默认时4MB。

• 使用MySQL的explain进行执行计划分析

解读:通过explain查看sql的执行计划,优化SQL避免对数据表做全表扫描操作

(3)程序设计原则

• 写SQL语句采用集合的思想，而不是记录的思想
• 如非特殊需求，必须使用连接池连接数据库，保证会话是长连接。
• 根据业务系统进行合理的读写分离，支付、实时性要求较高的业务场景不要做读写分离。
• OLTP业务使⽤绑定变量方式(变量通过占位符方式，而非字符串拼接方式)

解读：一次解析，多次使用；降低SQL注入概率；如果是分析型系统最好采用直接量，采用绑定变量情况在直方图分布不均匀情况下出现执行计划错误情况

• 禁止使用触发器、函数、存储过程

解读：暴露在应用程序之外，不可控，调试困难

 

四、流程规范

(1) 所有的线上操作,需要提前向DBA提交数据库规划,建表语句,程序中SQL语句以供审核.以便DBA合理建库,优化数据表,索引以及优化SQL

(2) 所有的建表操作需要确定建立哪些索引后才可以建表上线
(3) 禁止私自进行批量导入、导出数据，必须提前通知DBA进行相关操作
(4) 新系统数据库和表，高并发大数据量复杂业务场景设计.
(5) 推广活动或上线新功能必须提前通知技术团队进行流量评估和支持
(6) 不在业务高峰期批量更新、使用大查询操作数据库

 

五、安全规范

(1)严禁使用公网IP连接数据库
(2)生产、开发、测试环境分离，保证基本配置参数一致
(3)禁止线下测试、线下开发环境直连线上数据库

 

六、附录

附录一:MYSQL关键字

ADD	ALL	ALTER
ANALYZE	AND	AS
ASC	ASENSITIVE	BEFORE
BETWEEN	BIGINT	BINARY
BLOB	BOTH	BY
CALL	CASCADE	CASE
CHANGE	CHAR	CHARACTER
CHECK	COLLATE	COLUMN
CONDITION	CONNECTION	CONSTRAINT
CONTINUE	CONVERT	CREATE
CROSS	CURRENT_DATE	CURRENT_TIME
CURRENT_TIMESTAMP	CURRENT_USER	CURSOR
DATABASE	DATABASES	DAY_HOUR
DAY_MICROSECOND	DAY_MINUTE	DAY_SECOND
DEC	DECIMAL	DECLARE
DEFAULT	DELAYED	DELETE
DESC	DESCRIBE	DETERMINISTIC
DISTINCT	DISTINCTROW	DIV
DOUBLE	DROP	DUAL
EACH	ELSE	ELSEIF
ENCLOSED	ESCAPED	EXISTS
EXIT	EXPLAIN	FALSE
FETCH	FLOAT	FLOAT4
FLOAT8	FOR	FORCE
FOREIGN	FROM	FULLTEXT
GOTO	GRANT	GROUP
HAVING	HIGH_PRIORITY	HOUR_MICROSECOND
HOUR_MINUTE	HOUR_SECOND	IF
IGNORE	IN	INDEX
INFILE	INNER	INOUT
INSENSITIVE	INSERT	INT
INT1	INT2	INT3
INT4	INT8	INTEGER
INTERVAL	INTO	IS
ITERATE	JOIN	KEY
KEYS	KILL	LABEL
LEADING	LEAVE	LEFT
LIKE	LIMIT	LINEAR
LINES	LOAD	LOCALTIME
LOCALTIMESTAMP	LOCK	LONG
LONGBLOB	LONGTEXT	LOOP
LOW_PRIORITY	MATCH	MEDIUMBLOB
MEDIUMINT	MEDIUMTEXT	MIDDLEINT
MINUTE_MICROSECOND	MINUTE_SECOND	MOD
MODIFIES	NATURAL	NOT
NO_WRITE_TO_BINLOG	NULL	NUMERIC
ON	OPTIMIZE	OPTION
OPTIONALLY	OR	ORDER
OUT	OUTER	OUTFILE
PRECISION	PRIMARY	PROCEDURE
PURGE	RAID0	RANGE
READ	READS	REAL
REFERENCES	REGEXP	RELEASE
RENAME	REPEAT	REPLACE
REQUIRE	RESTRICT	RETURN
REVOKE	RIGHT	RLIKE
SCHEMA	SCHEMAS	SECOND_MICROSECOND
SELECT	SENSITIVE	SEPARATOR
SET	SHOW	SMALLINT
SPATIAL	SPECIFIC		SQL
SQLEXCEPTION	SQLSTATE	SQLWARNING
SQL_BIG_RESULT	SQL_CALC_FOUND_ROWS	SQL_SMALL_RESULT
SSL	STARTING	STRAIGHT_JOIN
TABLE	TERMINATED	THEN
TINYBLOB	TINYINT	TINYTEXT
TO	TRAILING	TRIGGER
TRUE	UNDO	UNION
UNIQUE	UNLOCK	UNSIGNED
UPDATE	USAGE	USE
USING	UTC_DATE	UTC_TIME
UTC_TIMESTAMP	VALUES	VARBINARY
VARCHAR	VARCHARACTER	VARYING
WHEN	WHERE	WHILE
WITH	WRITE	X509
XOR	YEAR_MONTH	ZEROFILL

 

附录二:

数字型
类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度
				浮点数值
DOUBLE	8 字节	(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度
				浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值
字符类型
类型	大小	描述
CHAR	0-255字符	定长字符串
VARCHAR	0-65536字符	变长字符串
TINYBLOB	0-255字符	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字符	短文本字符串
BLOB	0-65 535字符	二进制形式的长文本数据
TEXT	0-65 535字符	长文本数据
MEDIUMBLOB	0-16 777 215字符	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字符	中等长度文本数据
LOGNGBLOB	0-4 294 967 295字符	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字符	极大文本数据
时间类型
类型	大小	范围	格式	用途
	(字节)
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	8	1970-01-01 00:00:00/2037 年某时	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

这篇关于Mysql建表规范备忘录的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---