Java高级工程师总结的MySQL规范

1. 命名规范

• 库名、表名、字段名、索引名必须采用小写英文单词/缩写，禁用特殊符号，采用下划线分隔

• 库名、表名、字段名、索引名不得超过32个字符，中间不要留空格字符,不使用mysql保留关键字，不得使用下划线作起止字符

• 临时库、临时表名必须以tmp为前缀并以日期为后缀tmp_xxx_0904

• 备份库、备份表名必须以bak为前缀并以日期为后缀 bak_xxx_0904

• 分表后的表名后缀用数字递增，且从零开始,例如：test_0、test_100

• 库名：使用业务命名，见名知意

• 表名：使用业务命名，禁用’驼峰’命名，见名知意

• 列名：避免使用关键字，order，type，table

• 索引名：普通索引：idx列名，idx列名1列名2(列名太长使用缩写)，唯一索引：udx列名，

2. 表设计规范

• 只使用InnoDB存储引擎

• 表默认使用UTF8字符集

• 所有表都需要添加注释

• 表主键统一使用id int unsigned not null auto_increment

• 不建议使用联合主键，禁用字符做主键（字符无序且比整型占空间，减少随机读写）

• 单表数据量建议控制在2000万以内 根据记录大小来灵活限制 让表有更好的表现性能（表越大，查询等操作越慢） 降低运维成本（如DDL操作） 单个字段存储内容超过500k，必须要压缩

• 不在数据库中存储图片、文件等大数据，不建议使用text等大字段类型来存储数据

• 不使用外键

3. 字段设计规范

• 非特殊情况禁用NULL值，建议在定义是加上默认值

• 尽可能不要使用TEXT、BLOB类型

• 数据软删除时，使用 is_deleted的命名,并且数据类型是 tinyint（1表示为真 0表示为假）

• 用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

• 性别，类型，状态，级别等字段，用TINYINT类型

• 使用TINYINT来代替ENUM类型

• 字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量

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

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

3. VARCHAR 值不要大于255，因为VARCHAR(255+)和text在存储机制是一样的

4. VARCHAR(255+)的长度需要额外2个字节来存储，相应的增加了维护开销

5. 固定长度的字符串使用 CHAR 类型，所有单个字符的全部使用 CHAR 类型，而不应该使用VARCHAR 类型。

• 统一使用timestamp存储时间，所有业务表必须要有created_on,modified_on字段 timestamp标准写法： created_id INT(11) NOT NULL DEFAULT '0' COMMENT '创建人id', modified_id INT(11) NOT NULL DEFAULT '0' COMMENT '修改人id', created_name VARCHAR(30) NOT NULL DEFAULT '' COMMENT '创建人姓名', modified_name VARCHAR(30) NOT NULL DEFAULT '' COMMENT '修改人姓名', created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', modified_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', deleted TINYINT(1) NOT NULL DEFAULT '0' COMMENT '删除标志';

4. 索引规范

• 表统一使用id字段，int unsigned 类型自增，当主键

• 重要字段加上普通索引，如user_id,customer_id等

• 超过三个表禁止使用join。需要使用join的需要注意索引与sql性能，同时数据类型需要一致。多表关联查询时，需要保证被关联的字段设置了索引。

• 单张表中索引数量不超过6个

• 为varchar设置索引需要指定其长度，没必要对全文本进行索引建立，一般在20长度区分度在90%以上，可以使用count（distinct left(列名，索引长度)）/count(*)的区分度来判断。 前缀索引创建示例：create index idx_column_name on table_name(column_name(30));

• 在设计多列索引时，区分度最大的列放在前面 区分度公式：select count(distinct column)/count(*) from table,值越接近1越好 例如多列索引idx_abc(a,b,c)：a列的区分度>b列的区分度>c列的区分度

• 避免冗余或重复索引 合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c) 设计好where 过滤字段的顺序，一定程度上避免索引的冗余

• 不在区分度低的列上建立索引，例如‘性别’，‘类型’，‘状态’

• 不在索引列进行数学运算和函数运算，导致索引失效

• 防止因字段类型不同，造成隐式的转换，导致索引失效

• 禁止使用外键，如果有外键完整性约束，需要应用程序控制

• 不使用全文索引

5. SQL语句规范

• 避免出现select * 操作，只查询需要的字段，带上必要的where条件检索

• 避免使用存储过程、触发器、EVENTS等

• 避免使用大表的join

• 尽量使用join代替子查询

• 不使用反向查询，如 not in / not like / <>

• 使用in代替or，in的值不超过1000个

• 避免不必要的排序 order by xx

• 尽量避免使用%前导的查询，如like “%xxx”

• 数据修复时，需要先select，在删除。

• order by 后面的条件必须是同一索引的属性，排序顺序必须一致（比如都是升序或者降序）

6. SQL上线规范

• 禁止在线上做对数据数据库进行各种测试，如压力测试等

• 批量导入、导出数据必须提前通知DBA协助观察

• 批量更新数据，如update,delete 操作，需要DBA进行审查，并在执行过程中观察服务

• 禁止在主库上执行后台管理和统计类的功能查询

• 禁止有super权限的应用程序账号存在

• 产品出现非数据库导致的故障时及时通知DBA协助排查

• 数据库数据丢失，及时联系DBA进行恢复

• 对单表的多次ALTER操作必须合并为一次操作

• 重大项目的数据库方案选型和设计必须提前通知DBA参与

• 对特别重要的库表，提前与DBA沟通确定维护和备份优先级

• 不在业务高峰期批量更新、查询数据库

• 提交线上的建表需求，必须详细添加所有字段注释