一、表设计
1.采用ER模型,首先遵循范式设计,再根据实际需要进行反规范化设计。
2.库名、表名、字段名必须使用小写字母,“_”分割(TICKET库除外)。
3.库名、表名、字段名不能太长,要见名知意,建议使用名词而不是动词,避免使用系统关键字。
4.表和字段要有注释,说明其含义,对于冗余字段还要特别说明其维护方法,外键字段说明参照与哪个表。
5.建议使用InnoDB存储引擎。
6.尽量保持表行长度较小。一个表中的所有字段长度,不应超过一个数据块(16K)的一半。
7.主键尽量选择AUTO_INCREMENT列,特殊情形可以使用含有实际语义的列。【FAQ】
8.保持字段名和数据类型的一致性,例如member_id就不要使用varchar()。
9.大表要有明确的数据保留策略。
10.存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。
11.建议使用UNSIGNED存储非负数值。
12.建议使用INT UNSIGNED存储IPV4。【FAQ】
13.整形定义中不添加长度,比如使用INT,而不是INT(4)。【FAQ】
14.使用短数据类型,比如取值范围为0-80时,使用TINYINT UNSIGNED。
15.不建议使用ENUM类型,使用TINYINT来代替。
16.尽可能不使用TEXT、BLOB类型。
17.字符个数固定并且较小时建议使用CHAR(),比如MD5、身份证号、性别等。
18.VARCHAR(N),N表示的是字符数不是字节数,比如VARCHAR(255),可以最大可存储255个汉字,需要根据实际的宽度来选择N。
19.VARCHAR(N),N尽可能小,因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节,进行排序和创建临时表一类的内存操作时,会使用N的长度申请内存。
20.表字符集选择UTF8。
21.使用VARBINARY存储大小写敏感的变长字符串或二进制内容。
22.存储年使用YEAR类型,存储日期使用DATE类型。
23.存储时间(精确到秒)建议使用TIMESTAMP类型,因为TIMESTAMP使用4字节,DATETIME使用8个字节。使用TIMESTAMP时注意update问题。【FAQ】
24.字段尽量定义为NOT NULL。
25.将过大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据。
26.禁止在数据库中使用VARBINARY、BLOB存储图片、文件等。
27.临时库、表名必须以tmp为前缀,日期为后缀。
28.禁止使用分区表。
二、散表
1.对于字段类型简单,字段数不多表,单表数据量建议控制在5000w以下。
2.可以结合使用hash、range、lookup table进行散表。
3.如按日期时间分表需符合YYYY[MM][DD][HH]格式,例如2013071601。年份必须用4位数字表示。例如按日散表user_20110209、 按月散表user_201102。
三、索引规范
1.非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名,字段名称过长可以使用缩写。
2.唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名,字段名称过长可以使用缩写。
3.索引名称必须使用小写。
4.索引中的字段数建议不超过3个。
5.单张表的索引数量控制在5个以内。
6.唯一键由3个以下的字段组成。字段是整形时,根据表设计规范,考虑使用唯一键作为主键。
7.唯一键不和主键重复。
8.索引字段的顺序需要考虑字段值的基数,基数大的放在前面。
9.禁止冗余和重复索引。
10.不建议使用外键。
11.联表查询时,JOIN列的数据类型必须相同,并且要建立索引。
12.不在低基数列上建立索引,例如性别、status、type。
13.ORDER BY,GROUP BY,DISTINCT的字段需要添加在索引的后面。
14.使用EXPLAIN判断SQL语句是否合理使用索引,尽量避免extra列出现:Using File Sort,Using Temporary。
15.UPDATE、DELETE语句需要根据WHERE条件添加索引。更新频繁的列不适合创建索引。
16.对长度过长的VARCHAR字段建立索引时,添加crc32或者MD5 Hash字段,对Hash字段建立索引。
17.普通长度的字符串依情形使用前缀索引。
18.合理创建联合索引(避免冗余),(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。范围查询中高基数值的列在前。如果可以避免回表(在索引中即可完成检测)也可酌情添加联合索引。
19.合理利用覆盖索引。
20.不在索引列进行数据运算或函数运算(会导致无法使用索引,或者全表扫描)。
21.SQL变更需要确认索引是否需要变更并通知DBA。
22.SQL下线时通知DBA下线相关索引。
四、SQL规范
1.使用prepared statement,可以提供性能并且避免SQL注入。
2.SQL语句中IN包含的值不应过多。连续的数值使用between and代替IN。
3.UPDATE、DELETE语句尽量不用LIMIT。
4.WHERE条件中必须使用合适的类型,避免MySQL进行隐式类型转化。数值类型禁止加引号;字符串类型必须加引号。
5.SELECT语句只获取需要的字段。SELECT、INSERT语句必须显式的指明字段名称,不使用SELECT *,不使用INSERT INTO table()。
6.使用SELECT column_name1, column_name2 FROM table WHERE [condition]而不是SELECT column_name1 FROM table WHERE [condition]和SELECT column_name2 FROM table WHERE [condition]。
7.避免在SQL语句进行数学运算或者函数运算,容易将业务逻辑和DB耦合在一起。
8.INSERT语句使用batch提交(INSERT INTO table VALUES(),(),()……),values的个数不应过多。
9.避免使用存储过程、触发器、函数、定时器等,容易将业务逻辑和DB耦合在一起,并且MySQL的存储过程、触发器、函数中存在一定的bug。
10.避免使用JOIN和子查询。
11.使用合理的SQL语句减少与数据库的交互次数。
12.不使用ORDER BY RAND(),使用其他方法替换。
13.建议使用合理的分页方式以提高分页的效率。
14.统计表中记录数时使用COUNT(*),而不是COUNT(primary_key)和COUNT(1)。
15.NULL值不可以直接和比较运算符比较,应使用is null和is not null。
16.不建议使用%前缀模糊查询,例如LIKE “%weibo”,会导致全表扫描。
17.避免不必要的排序,酌情将排序交给应用层去完成。
18.避免SQL提示(USE INDEX,FORCE INDEX等)在代码中出现。
19.用到并行度需谨慎。高并发的SQL语句需要提交给DBA做压测。
20.查询datetime类型的列,不需要使用DATE_FORMAT或STR_TO_DATE()函数。
21.拆分复杂SQL为多个小SQL,避免大事务。
22.避免使用OR条件。
23.SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。
24.避免单条SQL语句同时更新多个表。
25.禁止在从库上执行后台管理和统计类功能的QUERY,可以使用统计类从库。
26.禁止使用负向查询,例如 not in、!=、not like。
五、行为规范
1.表结构变更必须通知DBA进行审核。
2.禁止在线上做数据库压力测试,禁止从测试环境直连数据库。
3.禁止有super权限的应用程序账号存在。
4.禁止有DDL、DCL权限的应用程序账号存在。
5.重要项目的数据库方案选型和设计必须提前通知DBA参与。
6.批量导入、导出数据,大批量更新数据如UPDATE、DELETE操作,必须DBA进行审核,并在执行过程中观察服务。
7.产品出现因数据库导致的故障时,如被攻击,必须及时通知DBA,便于维护服务稳定。
8.业务部门程序出现BUG等影响数据库服务的问题,必须及时通知DBA,便于维护服务稳定。
9.业务部门推广活动或上线新功能,必须提前通知DBA进行服务和访问量评估,并留出必要时间以便DBA完成扩容。
10.出现业务部门人为误操作导致数据丢失,需要恢复数据的,必须第一时间通DBA,并提供准确时间地点、误操作语句等重要线索。
11.提交线上建表改表需求,必须详细注明涉及到的所有SQL语句(包括INSERT、DELETE、UPDATE),便于DBA进行审核和优化。
12.不要在MySQL数据库中存放业务逻辑。
六、FAQ
一、表设计
6.要尽量选择AUTO_INCREMENT类型。含有实际语义的列仅适用于读多写少并且读写量悬殊的场景,例如使用member_id做主键。
12.通过MySQL函数inet_ntoa和inet_aton来进行转化。
13.数值类型括号后面的数字只是表示宽度而跟存储范围没有关系。
23.多列TIMESTAMP,应注意update问题。尤其是为TIMESTAMP列而不是第1列指定自动默认或更新,必须通过将第1个TIMESTAMP列显式分配一个常量DEFAULT值来禁用自动初始化和更新。
http://dev.mysql.com/doc/refman/5.1/zh/column-types.html#timestamp-4-1
七、参考资料
1.mysql系统关键字
http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html
2.mysql开发规范
http://www.iamcjd.com/?p=1237#MySQL-1-3
1.采用ER模型,首先遵循范式设计,再根据实际需要进行反规范化设计。
2.库名、表名、字段名必须使用小写字母,“_”分割(TICKET库除外)。
3.库名、表名、字段名不能太长,要见名知意,建议使用名词而不是动词,避免使用系统关键字。
4.表和字段要有注释,说明其含义,对于冗余字段还要特别说明其维护方法,外键字段说明参照与哪个表。
5.建议使用InnoDB存储引擎。
6.尽量保持表行长度较小。一个表中的所有字段长度,不应超过一个数据块(16K)的一半。
7.主键尽量选择AUTO_INCREMENT列,特殊情形可以使用含有实际语义的列。【FAQ】
8.保持字段名和数据类型的一致性,例如member_id就不要使用varchar()。
9.大表要有明确的数据保留策略。
10.存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。
11.建议使用UNSIGNED存储非负数值。
12.建议使用INT UNSIGNED存储IPV4。【FAQ】
13.整形定义中不添加长度,比如使用INT,而不是INT(4)。【FAQ】
14.使用短数据类型,比如取值范围为0-80时,使用TINYINT UNSIGNED。
15.不建议使用ENUM类型,使用TINYINT来代替。
16.尽可能不使用TEXT、BLOB类型。
17.字符个数固定并且较小时建议使用CHAR(),比如MD5、身份证号、性别等。
18.VARCHAR(N),N表示的是字符数不是字节数,比如VARCHAR(255),可以最大可存储255个汉字,需要根据实际的宽度来选择N。
19.VARCHAR(N),N尽可能小,因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节,进行排序和创建临时表一类的内存操作时,会使用N的长度申请内存。
20.表字符集选择UTF8。
21.使用VARBINARY存储大小写敏感的变长字符串或二进制内容。
22.存储年使用YEAR类型,存储日期使用DATE类型。
23.存储时间(精确到秒)建议使用TIMESTAMP类型,因为TIMESTAMP使用4字节,DATETIME使用8个字节。使用TIMESTAMP时注意update问题。【FAQ】
24.字段尽量定义为NOT NULL。
25.将过大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据。
26.禁止在数据库中使用VARBINARY、BLOB存储图片、文件等。
27.临时库、表名必须以tmp为前缀,日期为后缀。
28.禁止使用分区表。
二、散表
1.对于字段类型简单,字段数不多表,单表数据量建议控制在5000w以下。
2.可以结合使用hash、range、lookup table进行散表。
3.如按日期时间分表需符合YYYY[MM][DD][HH]格式,例如2013071601。年份必须用4位数字表示。例如按日散表user_20110209、 按月散表user_201102。
三、索引规范
1.非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名,字段名称过长可以使用缩写。
2.唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名,字段名称过长可以使用缩写。
3.索引名称必须使用小写。
4.索引中的字段数建议不超过3个。
5.单张表的索引数量控制在5个以内。
6.唯一键由3个以下的字段组成。字段是整形时,根据表设计规范,考虑使用唯一键作为主键。
7.唯一键不和主键重复。
8.索引字段的顺序需要考虑字段值的基数,基数大的放在前面。
9.禁止冗余和重复索引。
10.不建议使用外键。
11.联表查询时,JOIN列的数据类型必须相同,并且要建立索引。
12.不在低基数列上建立索引,例如性别、status、type。
13.ORDER BY,GROUP BY,DISTINCT的字段需要添加在索引的后面。
14.使用EXPLAIN判断SQL语句是否合理使用索引,尽量避免extra列出现:Using File Sort,Using Temporary。
15.UPDATE、DELETE语句需要根据WHERE条件添加索引。更新频繁的列不适合创建索引。
16.对长度过长的VARCHAR字段建立索引时,添加crc32或者MD5 Hash字段,对Hash字段建立索引。
17.普通长度的字符串依情形使用前缀索引。
18.合理创建联合索引(避免冗余),(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。范围查询中高基数值的列在前。如果可以避免回表(在索引中即可完成检测)也可酌情添加联合索引。
19.合理利用覆盖索引。
20.不在索引列进行数据运算或函数运算(会导致无法使用索引,或者全表扫描)。
21.SQL变更需要确认索引是否需要变更并通知DBA。
22.SQL下线时通知DBA下线相关索引。
四、SQL规范
1.使用prepared statement,可以提供性能并且避免SQL注入。
2.SQL语句中IN包含的值不应过多。连续的数值使用between and代替IN。
3.UPDATE、DELETE语句尽量不用LIMIT。
4.WHERE条件中必须使用合适的类型,避免MySQL进行隐式类型转化。数值类型禁止加引号;字符串类型必须加引号。
5.SELECT语句只获取需要的字段。SELECT、INSERT语句必须显式的指明字段名称,不使用SELECT *,不使用INSERT INTO table()。
6.使用SELECT column_name1, column_name2 FROM table WHERE [condition]而不是SELECT column_name1 FROM table WHERE [condition]和SELECT column_name2 FROM table WHERE [condition]。
7.避免在SQL语句进行数学运算或者函数运算,容易将业务逻辑和DB耦合在一起。
8.INSERT语句使用batch提交(INSERT INTO table VALUES(),(),()……),values的个数不应过多。
9.避免使用存储过程、触发器、函数、定时器等,容易将业务逻辑和DB耦合在一起,并且MySQL的存储过程、触发器、函数中存在一定的bug。
10.避免使用JOIN和子查询。
11.使用合理的SQL语句减少与数据库的交互次数。
12.不使用ORDER BY RAND(),使用其他方法替换。
13.建议使用合理的分页方式以提高分页的效率。
14.统计表中记录数时使用COUNT(*),而不是COUNT(primary_key)和COUNT(1)。
15.NULL值不可以直接和比较运算符比较,应使用is null和is not null。
16.不建议使用%前缀模糊查询,例如LIKE “%weibo”,会导致全表扫描。
17.避免不必要的排序,酌情将排序交给应用层去完成。
18.避免SQL提示(USE INDEX,FORCE INDEX等)在代码中出现。
19.用到并行度需谨慎。高并发的SQL语句需要提交给DBA做压测。
20.查询datetime类型的列,不需要使用DATE_FORMAT或STR_TO_DATE()函数。
21.拆分复杂SQL为多个小SQL,避免大事务。
22.避免使用OR条件。
23.SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。
24.避免单条SQL语句同时更新多个表。
25.禁止在从库上执行后台管理和统计类功能的QUERY,可以使用统计类从库。
26.禁止使用负向查询,例如 not in、!=、not like。
五、行为规范
1.表结构变更必须通知DBA进行审核。
2.禁止在线上做数据库压力测试,禁止从测试环境直连数据库。
3.禁止有super权限的应用程序账号存在。
4.禁止有DDL、DCL权限的应用程序账号存在。
5.重要项目的数据库方案选型和设计必须提前通知DBA参与。
6.批量导入、导出数据,大批量更新数据如UPDATE、DELETE操作,必须DBA进行审核,并在执行过程中观察服务。
7.产品出现因数据库导致的故障时,如被攻击,必须及时通知DBA,便于维护服务稳定。
8.业务部门程序出现BUG等影响数据库服务的问题,必须及时通知DBA,便于维护服务稳定。
9.业务部门推广活动或上线新功能,必须提前通知DBA进行服务和访问量评估,并留出必要时间以便DBA完成扩容。
10.出现业务部门人为误操作导致数据丢失,需要恢复数据的,必须第一时间通DBA,并提供准确时间地点、误操作语句等重要线索。
11.提交线上建表改表需求,必须详细注明涉及到的所有SQL语句(包括INSERT、DELETE、UPDATE),便于DBA进行审核和优化。
12.不要在MySQL数据库中存放业务逻辑。
六、FAQ
一、表设计
6.要尽量选择AUTO_INCREMENT类型。含有实际语义的列仅适用于读多写少并且读写量悬殊的场景,例如使用member_id做主键。
12.通过MySQL函数inet_ntoa和inet_aton来进行转化。
13.数值类型括号后面的数字只是表示宽度而跟存储范围没有关系。
23.多列TIMESTAMP,应注意update问题。尤其是为TIMESTAMP列而不是第1列指定自动默认或更新,必须通过将第1个TIMESTAMP列显式分配一个常量DEFAULT值来禁用自动初始化和更新。
http://dev.mysql.com/doc/refman/5.1/zh/column-types.html#timestamp-4-1
七、参考资料
1.mysql系统关键字
http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html
2.mysql开发规范
http://www.iamcjd.com/?p=1237#MySQL-1-3