建表规范

一、命名规则

1. 数据库名、表名、字段名仅允许使用小写字母和数字，且禁止以数字开头，

示例：goods_category、agent_operate_201812_log

2. 命名需遵循“见名知意”原则，禁止使用无意义缩写

示例：使用goods_category，而非gc。

3. 特殊表命名规范：

• 配置表：以xx_config命名，示例：shop_payment_config。

• 日志表：以xx_log命名，示例：system_log。

• 临时表：以temp_xx命名，示例：temp_order_info_export。

　

二、字段设计

1. 时间字段统一规范

创建时间用create_time，更新时间用update_time，字段类型均为int(11) unsigned。

2. 字符串类型字段

• 长度几乎相等时，使用char定长类型。

• 长度超过5000字符时，定义为text类型并独立建表，通过主键关联，避免影响其他字段索引效率。

3. 数值类型字段

• 存储小数时，使用decimal类型，禁止使用float和double（避免精度损失）；若超出decimal范围，拆分整数和小数分开存储。

• 商品价格统一转为分存储，字段类型设为int。

• 根据存储范围选择合适整数类型，参考下表：

存储对象	存储范围	推荐类型	占用字节	表示范围（无符号）
人	150岁以内	unsigned tinyint	1	0~255
乌龟	数百岁	unsigned smallint	2	0~65535
恐龙化石	数千万年	unsigned int	4	0~约42.9亿
太阳	约50亿年	unsigned bigint	8	0~约10^19

4. 字段约束

无特殊要求时，所有字段均定义为not null（避免索引额外占用空间，简化比较和计算逻辑）。

5. 字符集规范

数据库和数据表默认使用utf8，需存储 emoji 表情时使用utf8mb4。

6. 其他限制

• 禁止设置预留字段（命名难达见名知意，类型选择困难，修改时会锁定整张表）。

• 禁止存储图片、文件等大二进制数据，建议存储在对象存储服务（如阿里云oss、腾讯云cos），数据库仅记录文件地址。

• 条件允许时，将字符串转换为数字类型存储（如IP地址使用ip2long和long2ip转换）。

• 避免使用enum类型（排序操作效率低）。
  　

三、表结构优化

1. 适当冗余字段以提升查询性能，但需确保数据一致性。

2. 实行冷热数据分离，减小表宽度（表越宽，内存占用和IO消耗越大）。

3. 分库分表阈值：单表行数超过500万行或容量超过2GB时，推荐分库分表。

4. 存储引擎：默认使用InnoDB（支持事务、行级锁，并发性能和恢复性更优）。

5. 注释要求：所有数据表和字段必须添加comment注释，条件允许时建立数据字典。

　

四、环境限制

禁止在开发环境、测试环境直接连接生产环境数据库。

索引规范

一、索引创建原则

1. 唯一索引

业务上具有唯一特性的字段（含多字段组合），必须创建唯一索引（忽略插入速度影响，提升查询效率，避免脏数据）。

2. 数量限制

每张表索引数量尽量不超过5个（索引会降低插入/更新效率，过多索引会增加优化器生成执行计划的时间）。

3. 组合索引

• 区分度最高的字段放在最左侧。

• 避免创建重复索引和冗余索引（示例：primary key(id)与index(id)、unique key(id)为重复索引；index(a,b,c)与index(a,b)、index(a)为冗余索引）。

4. 字符串索引

varchar字段建立索引时必须指定长度，根据区分度调整（一般长度20区分度可达90%），可通过count(distinct left(列名,索引长度))/count(*)计算区分度。

　

二、索引使用规范

1. 关联查询限制

• 禁止3个及以上表JOIN，关联字段数据类型必须一致。

• 多表关联时，被关联字段必须建立索引。

2. 排序优化

order by场景需利用索引有序性，避免范围查询后使用非索引字段排序

正例：where a=5 and b=10 order by c，索引a_b_c生效；

反例：where a>10 order by b，索引a_b不生效）

3. 模糊查询

严禁左模糊或全模糊查询，需实现类似功能时使用搜索引擎。

4. 分页优化

超多分页场景使用延迟关联或子查询（避免offset过大导致效率低下），正例：

SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

　

三、索引适用场景

优先为以下字段建立索引：

1. 经常出现在where从句中的字段。

2. 包含在order by、group by、distinct中的字段。

　

四、常见误解

1. 避免“宁滥勿缺”

并非每个查询都需要建立索引。

2. 避免“宁缺毋滥”

合理索引可提升查询效率，无需过度担心空间消耗。

3. 重视唯一索引

业务唯一性不能仅依赖应用层“先查后插”，必须配合数据库唯一索引。

SQL 开发规范

一、基础语法规范

1. 计数函数

使用count(*)统计行数，禁止用count(列名)或count(常量)替代（count(*)统计含NULL值的行，符合SQL92标准）。

2. 分页逻辑

代码中分页查询时，若count为0应直接返回结果，避免冗余执行。

3. 禁止使用存储过程

难以调试扩展、可移植性差

4. 禁止使用外键与级联

适用于单机低并发，不适合分布式高并发场景，易引发更新风暴和插入性能问题，外键逻辑在应用层实现。

5. 查询字段

禁止使用select *，必须指定具体字段（避免无法使用覆盖索引，减少CPU、IO和网络带宽消耗）。

　

二、性能优化规范

1. 预编译语句

优先使用框架查询构造器（底层封装预编译），特殊情况手动预编译（复用执行计划，避免SQL注入）。

2. 数据类型

避免隐式转换（反例：select user_name,age from admin where admin_id = '11140'）。

3. 子查询优化

避免使用子查询，可转化为JOIN查询（子查询结果集无索引，性能较差），仅当子查询为简单SQL且在in子句中时可优化。

4. 关联表数量

JOIN关联表数量建议不超过5个（MySQL最多允许61个，过多关联会占用大量内存，易导致内存溢出）。

5. 交互优化

减少数据库交互，合并相同操作（如批量更新），禁止在循环中执行SQL。

　

三、语句编写规范

1. 条件判断

同一列or判断时，用in替代or（in更易利用索引），in的值不超过500个（示例：select user_name,age from admin where city in (1024, 1028)）。

2. 函数使用

禁止在SQL语句中对字段进行函数转换和计算（如格式化时间、转换IP，应在程序中处理）。

3. 结果集合并

无重复值时使用UNION ALL替代UNION（UNION会去重，效率低于UNION ALL）。

4. 复杂SQL拆分

将复杂大SQL拆分为多个小SQL，可通过并行执行提升效率。

5. 大批量操作

分批执行大批量修改/插入操作（避免锁定大量数据行，导致阻塞和连接耗尽）。