Mysql开发规范

一、表设计

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类型。varchar的性能会比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。避免使用NULL字段,NULL字段很难查询优化,NULL字段的索引需要额外空间,NULL字段的复合索引无效。

25.将过大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据。

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

27.临时库、表名必须以tmp为前缀,日期为后缀。

28.禁止使用分区表。

29.备份库、备份表必须以bak为前缀,日期为后缀。

30.禁止在数据库中存储明文密码,把密码加密后存储。

 二、散表

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.不建议使用外键。

    (1) 外键用来保护参照完整性,可在业务端实现

    (2) 对父表和子表的操作会相互影响,降低可用性

11.联表查询时,JOIN列的数据类型必须相同,并且要建立索引。

    (1) 区分度最大的字段放在前面

    (2) 核?SQL优先考虑覆盖索引

    (3) 避免冗余和重复索引

    (4) 索引要综合评估数据密度和分布以及考虑查询和更新比例

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下线相关索引。

23.表必须有主键。

    (1)不使用更新频繁的列作为主键

    (2)尽量不选择字符串列作为主键

    (3)不使用UUID MD5 HASH这些作为主键(数值太离散了)

    (4)默认使?非空的唯一键作为主键

    (5)建议使用自增

24.重要的SQL必须被索引,比如:

    (1) UPDATE、DELETE语句的WHERE条件列

    (2) ORDER BY、GROUP BY、DISTINCT的字段


 

 四、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语句进行数学运算或者函数运算(MySQL不擅长数学运算和逻辑判断)

8.INSERT语句使用batch提交(INSERT INTO table VALUES(),(),()……),values的个数不应过多。

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

10.避免使用大表的JOIN和子查询。

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

12.不使用ORDER BY RAND(),使用其他方法替换。

13.建议使用合理的分页方式以提高分页的效率,比如改写limit:

select id from tlimit 10000, 10; => select id from t where id > 10000 limit10;

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条件,可以改写为IN。

23.SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。

24.避免单条SQL语句同时更新多个表。

25.禁止在从库上执行后台管理和统计类功能的QUERY,可以使用统计类从库。

26.禁止使用负向查询,例如 not in、!=、not like。

27.降低业务耦合度,为sacle out、sharding留有余地。

28.UNION ALL比UNION性能更好。


 五、行为规范

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

一、表设计

1.要尽量选择AUTO_INCREMENT类型。含有实际语义的列仅适用于读多写少并且读写量悬殊的场景,例如使用member_id做主键。

2.IP地址可以考虑通过使用int unsigned来保存,使用MySQL函数inet_ntoa和inet_aton来进行转化。

3.数值类型括号后面的数字只是表示宽度而跟存储范围没有关系。

4.多列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 

3.部分数据类型的存储范围

上一篇:生成随机字符+数字的存储过程


下一篇:[ERROR] Error in accept: Too many open files故障处理