基于Springboot搭建java项目（九）——Mysql数据库规范

说明：有一些建议或者规定不一定十分合理，后续可能会修改。另外，MySQL版本不断进化，也会导致有一些条款失效，请大家根据自身的情况谨慎参考。

一、表设计

1、库名、表名、字段名必须使用小写字母，“_”分割。

2、库名、表名、字段名建议不超过32个字符。（允许的最大字符数是64）

3、库名、表名、字段名见名知意,建议使用名词而不是动词。

4、建议避免使用保留字命名。（如下是保留字）

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

5、建议使用InnoDB存储引擎。

6、存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。

7、建议使用UNSIGNED存储非负数值。

8、使用短数据类型，比如取值范围为0-80时，使用TINYINT UNSIGNED。

9、不建议使用ENUM或SET类型，使用TINYINT来代替。

10、尽可能不使用TEXT、BLOB类型。

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

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

13、表字符集选择utf8 / utf8mb4。

14、使用VARBINARY存储变长字符串。

15、存储年使用YEAR类型。存储日期使用DATE类型。

16、存储时间（精确到秒）建议使用TIMESTAMP类型，因为TIMESTAMP使用4字节，DATETIME使用8个字节。

17、建议字段定义为NOT NULL。选择合适的默认值

18、将过大字段拆分到其他表中。

19、一对多的数据设计且更新的频率较高的一定用多表关联

20、禁止在数据库中使用VARBINARY、BLOB存储图片、文件等。

21、建议保持表身段苗条，单表字段数上限控制在20~50个

25、建议每张表（除日志表外）都包含id, ctime, utime, isvalid 这5个字段。日志表仅需要id、ctime

二、索引

1、非唯一索引必须按照“idx_字段名称_字段名称[字段名]”进行命名。

2、唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名。

3、索引名称必须使用小写。

4、索引中的字段数建议不超过5个。

5、单张表的索引数量控制在5个以内。

6、唯一键由3个以下字段组成，并且字段都是整形时，使用唯一键作为主键。

7、没有唯一键或者唯一键不符合5中的条件时，使用自增（或者通过发号器获取）id作为主键。

8、唯一键不和主键重复。

9、索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面。

10、ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。

11、使用EXPLAIN / PROFILE判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary。

12、UPDATE、DELETE语句需要根据WHERE条件添加索引。

13、尽量少使用负向查询

14、不建议使用%前缀模糊查询，例如LIKE “%weibo”。

15、对长度过长的VARCHAR字段建立索引时，添加crc32或者MD5 Hash字段，对Hash字段建立索引。

16、 合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。 合理利用覆盖索引。

17、字符字段必须建前缀索引

18、不在索引列进行数学运算或函数运算

三、SQL语句

1、使用prepared statement，可以提供性能并且避免SQL注入。

2、SQL语句中同一字段将or改写为in()，IN包含的值不应过多，建议不超过3000个。

3、UPDATE、DELETE语句不使用LIMIT。

4、WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化。

5、SELECT语句只获取需要的字段。

6、SELECT、INSERT语句必须显式的指明字段名称，不使用SELECT *，不使用INSERT INTO table()。

7、使用SELECT column_name1, column_name2 FROM table WHERE [condition]而不是SELECT column_name1 FROM table WHERE [condition]和SELECT column_name2 FROM table WHERE [condition]。

8、WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引。

9、避免在SQL语句进行数学运算或者函数运算，容易将业务逻辑和DB耦合在一起。

10、INSERT语句使用batch提交（INSERT INTO table VALUES(),(),()……），values的个数不应过多。

11、避免使用存储过程、触发器、函数等，容易将业务逻辑和DB耦合在一起，并且MySQL的存储过程、触发器、函数中存在一定的bug。

12、避免使用JOIN。

13、使用合理的SQL语句减少与数据库的交互次数。

14、不使用ORDER BY RAND()，使用其他方法替换。

15、建议使用合理的分页方式以提高分页的效率。

16、统计表中记录数时不使用COUNT(*)，而是COUNT(primary_key)和COUNT(1)。

17、禁止在从库上执行后台管理和统计类型功能的QUERY。

四、散表

1、对于字段类型简单，字段数不多表，单表数据量建议控制在5000w以下。

2、可以结合使用hash、range、lookup table进行散表。

3、散表如果使用md5（或者类似的hash算法）进行散表，表名后缀使用16进制，比如user_ff。

4、推荐使用CRC32求余（或者类似的算术算法）进行散表，表名后缀使用数字，数字必须从0开始并等宽，比如散100张表，后缀从00-99。

5、使用时间散表，表名后缀必须使用特定格式，比如按日散表user_20110209、按月散表user_201102。