企业常用Mysql规范

1、命名规范

1. 所有表名库名字段索引名均小写字母，用下划线分割
2. MySQL所有均忽略大小写设置lower_case_table_names为1.
3. 库名、表名、字段名最大支持64个字符，为了统一规范、易于辨识以及减少传输量,禁止超过32个字符
4. 库名、表名、字段名禁止使用MySQL保留字
5. 库名、表名、字段名必须见名知意。命名与业务、产品线等相关联
6. 临时库、表名必须以tmp为前缀，并以日期为后缀。例如 tmp_test01_20130704
7. 线上账号采用见名知意，以RW、RO开头，例如：RW_XX，访问XX应用的可读写的账号

2、基础规范

1. 线上必须使用innodb存储引擎。
2. 表字符集必须使用UTF8。
3. 所有表必须要有注释，所有字段必须有用途注释。
4. 禁止数据库存放图片以及文件等大数据。
5. 每张表建议控制在100w以内。
6. 禁止在线上做数据库压力测试。
7. 禁止本地以及开发测试环境直连线上环境。
8. 禁止个人账号直连线上主库

3、库表设计

1. 禁止使用分区表
2. 大字段字段单独存放，不允许使用text字段， 大文本字段建议存储至mongodb或者hbase之类的KV数据库
3. 采用合适的分表分库策略，尽量让数据均匀分布， 建议使用用户id、订单id取模进行分表
4. 禁止表中存放业务逻辑
5. 所有表必须有主键，且主键采用INT自增模式.
6. 所有表中字段避免过多导致数据量以及索引过多

4、字段设计

1. 建议使用UNSIGNED存储非负数值
2. 建议使用INT UNSIGNED存储IP
3. 用DECIMAL代替FLOAT和DOUBLE存储精确浮点数，例如支付相关数据
4. INT类型固定占4字节存储，例如INT(10)仅代表显示字符宽度为10位，不代表存储长度
5. 区分使用TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT数据类型。例如取值范围为0-80时，使用TINYINT UNSIGNED
6. 建议使用TINYINT来代替ENUM类型
7. 尽可能不使用TEXT、BLOB类型
8. 使用VARBINARY存储大小写敏感的变长字符串或二进制内容
9. 使用尽可能小的VARCHAR字段。VARCHAR(N)中的N表示字符数而非字节数
10. 区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。 存储时间(精确到秒)建议使用TIMESTAMP类型
11. 所有字段均定义为NOT NULL并且有DEFAULT值

索引规范

1. 使用prepared statement，可以提升性能并避免SQL注入
2. 用IN代替OR。SQL语句中IN包含的值不应过多，200以内
3. 禁止隐式转换。数值类型禁止加引号、字符串类型必须加引号
4. 避免使用JOIN和子查询。必要时推荐用JOIN代替子查询
5. 避免在MySQL中进行数学运算和函数运算
6. 减少与数据库交互次数，尽量采用批量SQL语句
7. 拆分复杂SQL为多个小SQL，避免大事务
8. 获取大量数据时，建议分批次获取数据，每次获取数据少于2000条，结果集应小于1M
9. 用UNION ALL代替UNION
10. 统计行数用COUNT(*)
11. SELECT只获取必要的字段，禁止使用SELECT *
12. SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数
13. INSERT语句必须指定字段列表，禁止使用 INSERT INTO TABLE()
14. 禁止单条SQL语句同时更新多个表
15. 避免使用存储过程、触发器、视图、自定义函数等
16. 建议使用合理的分页方式以提高分页效率
17. 禁止在主库上执行后台管理和统计类功能的QUERY，必要时申请统计类从库
18. 程序应有捕获SQL异常的处理机制，必要时通过rollback显式回滚
19. 重要SQL必须被索引：update、delete的where条件列、order by、group by、distinct字段、多表join字段
20. 禁止使用%前导查询，例如：like “%abc”，无法利用到索引
21. 禁止使用负向查询，例如 not in、!=、not like
22. 使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort、Using Temporary

行为规范

1. 表结构变更必须通知DBA进行审核
2. 禁止有super权限的应用程序账号存在
3. 禁止有DDL、DCL权限的应用程序账号存在。
4. 重要项目的数据库方案选型和设计必须提前通知DBA参与
5. 批量导入、导出数据必须通过DBA审核，并在执行过程中观察服务
6. 批量更新数据，如UPDATE、DELETE操作，必须DBA进行审核，并在执行过程中观察服务
7. 产品出现因数据库导致的故障时，如被攻击，必须及时通DBA，便于维护服务稳定
8. 业务部门程序出现BUG等影响数据库服务的问题，必须及时通知DBA，便于维护服务稳定
9. 业务部门推广活动或上线新功能，必须提前通知DBA进行服务和访问量评估，并留出必要时间以便DBA完成扩容
10. 出现业务部门人为误操作导致数据丢失，需要恢复数据的，必须第一时间通DBA，并提供准确时间地点、误操作语句等重要线索
11. 提交线上建表改表需求，必须详细注明涉及到的所有SQL语句（包括INSERT、DELETE、UPDATE），便于DBA进审核和优化
12. 不要在MySQL数据库中存放业务逻辑

SQL建表示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE TABLE `risk_rule_item` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`property` VARCHAR(45) NOT NULL DEFAULT '' COMMENT '规则名称，例如：age，sex等',
`alias` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '规则中文名，例如：姓名，性别等',
`code` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '规则项编码',
`dict_code` INT(11) NULL DEFAULT '0' COMMENT '字典编号',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`is_valid` TINYINT(1) NOT NULL DEFAULT '1' COMMENT '是否有效：0无效，1有效，默认1',
PRIMARY KEY (`id`)
)
COMMENT='规则项列表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

索引优化

1. 字段类型转换导致不用索引，如字符串类型的不用引号，数字类型的用引号等，这有可能会用不到索引导致全表扫描；
2. mysql 不支持函数转换，所以字段前面不能加函数，否则这将用不到索引；
3. 不要在字段前面加减运算；
4. 字符串比较长的可以考虑索引一部份减少索引文件大小，提高写入效率；
5. like % 在前面用不到索引；
6. 根据联合索引的第二个及以后的字段单独查询用不到索引；
7. 不要使用 select *；
8. 排序请尽量使用升序 ;
9. or 的查询尽量用 union 代替 （Innodb）；
10. 复合索引高选择性的字段排在前面；
11. order by /group by字段包括在索引当中减少排序，效率会更高。

• ← Previous Post
• Next Post →