数据库设计规范

数据库设计规范

1 命名规范

• 库名、表名、字段名必须使用小写字母，并采用下划线分割
• 表名以 t_ 开头，字段名禁用数据库关键字
• 普通索引以idx_col1_col2命名，唯一索引以uk_col1_col2命名。
• 库名、表名、字段名禁止超过32个字符，需见名知意
• 临时库、表名须以tmp加日期为后缀，如 t_member_tmp20190523。备份表也类似，形如_bak20190523
• 不具有时间特性的，直接以 t_tablename_001 这样的方式命名。

2 库表基础规范

• 使用Innodb存储引擎(5.5版本以上MySQL)
• 表字符集统一使用UTF8
  • UTF8字符集存储汉字占用3个字节，存储英文字符占用一个字节
  • 校对字符集使用默认的 utf8_general_ci
  • 连接的客户端也使用utf8，建立连接时指定charset或SET NAMES UTF8;
• 所有表都要添加注释
• 控制单表字段数量
• 单表字段数上限30左右，再多的话考虑垂直分表，一是冷热数据分离，二是大字段分离，三是常在一起做条件和返回列的不分离。
• 表字段控制少而精，可以提高IO效率，内存缓存更多有效数据，从而提高响应速度和并发能力，后续 alter table 也更快。
• 所有表都必须要显式指定主键
• 主键尽量采用自增方式，InnoDB表实际是一棵索引组织表，顺序存储可以提高存取效率，充分利用磁盘空间。还有对一些复杂查询可能需要自连接来优化时需要用到。
  • 如果没有主键或唯一索引，update/delete是通过所有字段来定位操作的行，相当于每行就是一次全表扫描
  • 少数情况可以使用联合唯一主键，需与开发负责人或经理协商
• 不强制使用外键参考
  • 即使2个表的字段有明确的外键参考关系，也不使用 FOREIGN KEY ，因为新纪录会去主键表做校验，影响性能。
• 单表数据量尽量控制在5000w以内
• 数据库中不允许存储明文密码
• 适度使用存储过程、视图，禁止使用触发器、事件
  • 存储过程（procedure）虽然可以简化业务端代码，在传统企业写复杂逻辑时可能会用到，而在互联网企业变更是很频繁的，在分库分表的情况下要升级一个存储过程相当麻烦。又因为它是不记录log的，所以也不方便debug性能问题。如果使用过程，一定考虑如果执行失败的情况。
  • 使用视图一定程度上也是为了降低代码里SQL的复杂度，但有时候为了视图的通用性会损失性能（比如返回不必要的字段）。
  • 触发器（trigger）也是同样，但也不应该通过它去约束数据的强一致性，mysql只支持“基于行的触发”，也就是说，触发器始终是针对一条记录的，而不是针对整个sql语句的，如果变更的数据集非常大的话，效率会很低。掩盖一条sql背后的工作，一旦出现问题将是灾难性的，但又很难快速分析和定位。
  • 事件（event）也是一种偷懒的表现， mysql无法对它做失败预警。

3 字段规范

• char、varchar、text等字符串类型定义
  • 对于长度基本固定的列，如果该列恰好更新又特别频繁，适合char
  • varchar虽然存储变长字符串，但不可太小也不可太大。UTF8最多能存21844个汉字，或65532个英文
  • varbinary(M)保存的是二进制字符串，它保存的是字节而不是字符，所以没有字符集的概念，M长度0-255（字节）。只用于排序或比较时大小写敏感的类型，不包括密码存储
  • TEXT类型与VARCHAR都类似，存储可变长度，最大限制也是2^16，但是它20bytes以后的内容是在数据页以外的空间存储，对它的使用需要多一次寻址，没有默认值。 一般用于存放容量平均都很大、操作没有其它字段那样频繁的值。 具体使用text和blob，还是varchar看具体场景，varchar可能会导致行溢出，效果差不多，但因为每行占用字节数过多，会导致buffer_pool能缓存的数据行、页下降。另外text和blob上面一般不会去建索引，而是利用sphinx之类的第三方全文搜索引擎，如果确实要创建（前缀）索引，那就会影响性能。凡事看具体场景。另外尽可能把text/blob拆到另一个表中
  • BLOB可以看出varbinary的扩展版本，内容以二进制字符串存储，无字符集，区分大小写，有一种经常提但不用的场景：不要在数据库里存储图片。
• 使用tinyint来代替 enum和boolean
  • ENUM类型在需要修改或增加枚举值时，需要在线DDL，成本较高；ENUM列值如果含有数字类型，可能会引起默认值混淆
• tinyint使用1个字节，一般用于status,type,flag的列
  • 建议使用 UNSIGNED 存储非负数值, 相比不使用 unsigned，可以扩大一倍使用数值范围
  • int使用固定4个字节存储，int(11)与int(4)只是显示宽度的区别
  • 使用Decimal 代替float/double存储精确浮点数,对于货币、金额这样的类型，使用decimal，如 decimal(9,2)。float默认只能能精确到6位有效数字,金额单位分，使用int存储。后端接口返回统一按分输出。
• timestamp与datetime选择
  • datetime 和 timestamp类型所占的存储空间不同，前者8个字节，后者4个字节，这样造成的后果是两者能表示的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，后者范围为 1970-01-01 08:00:01 到 2038-01-19 11:14:07 。所以 TIMESTAMP 支持的范围比 DATATIME 要小。
  • timestamp显示与时区有关，内部总是以 UTC 毫秒来存的。还受到严格模式的限制
  • 优先使用timestamp，datetime也没问题
  • where条件里不要对时间列上使用时间函数
• 建议字段都定义为NOT NULL
  • 如果是索引字段，一定要定义为not null 。因为null值会影响cordinate统计，影响优化器对索引的选择
  • 如果不能保证insert时一定有值过来，定义时使用default ‘’ ，或 0
  • 时间字段如果需要为空时，可以设置默认最小时间’1000-01-01 00:00:00’或者默认最大时间’9999-12-31 23:59:59’，此时系统（代码中处理）在查询时会自动将值置为NULL
• 同一意义的字段定义必须相同
  • 比如不同表中都有 user_id 字段，那么它的类型、字段长度要设计成一样

4 索引规范

任何新的select,update,delete上线，都要先explain，看索引使用情况 尽量避免extra列出现：Using File Sort，Using Temporary，rows超过1000的要谨慎上线(如果是分区表，一定要用到分区字段)。

• 索引个数限制
  • 索引是双刃剑，会增加维护负担，增大IO压力，索引占用空间是成倍增加的
  • 单张表的索引数量控制在5个以内，或不超过表字段个数的20%。若单张表多个字段在查询需求上都要单独用到索引，需要经过开发负责人或经理评估。
• 避免冗余索引
  • InnoDB表是一棵索引组织表，主键是和数据放在一起的聚集索引，普通索引最终指向的是主键地址，所以把主键做最后一列是多余的。如 crm_id作为主键，联合索引(user_id, crm_id)上的crm_id就完全多余。
• 没有特殊要求，使用自增id作为主键
  • 主键是一种聚集索引，顺序写入。组合唯一索引作为主键的话，是随机写入，适合写少读多的表
  • 主键不允许更新
• 索引尽量建在选择性高的列上
  • 不在低基数列上建立索引，例如性别、类型。但有一种情况，idx_feedbackid_type (feedback_id, type)，如果经常用 type=1 比较，而且能过滤掉90%行，那这个组合索引就值得创建。有时候同样的查询语句，由于条件取值不同导致使用不同的索引，也是这个道理。
  • 索引选择性计算方法（基数 ÷ 数据行数） Selectivity = Cardinality / Total Rows = select count(distinct col1)/count(*) from tblname，越接近1说明col1上使用索引的过滤效果越好
  • 走索引扫描行数超过30%时，改全表扫描
• 最左前缀原则
  • where条件里面字段的顺序与索引顺序无关，mysql优化器会自动调整顺序
  • 遇到范围查询(>、<、between、like)也会停止索引匹配，比如 c1=a and c2 > 2 and c3=c，只有c1,c2列上的比较能用到索引，(c1,c2,c3)排列的索引才可能会都用上
  • mysql使用联合索引时，从左向右匹配，遇到断开或者范围查询时，无法用到后续的索引列,比如索引idx_c1_c2_c3 (c1,c2,c3)，相当于创建了(c1)、(c1,c2)、(c1,c2,c3)三个索引，where条件包含上面三种情况的字段比较则可以用到索引，但像 where c1=a and c3=c 只能用到c1列的索引，像 c2=b and c3=c等情况就完全用不到这个索引
• 对超过30个字符长度的列创建索引时，考虑使用前缀索引
  • 前缀索引也有它的缺点是，如果在该列上 ORDER BY 时无法使用索引，也不能把它们用作覆盖索引(Covering Index)
  • 如果在varbinary或blob这种以二进制存储的列上建立前缀索引，要考虑字符集，括号里表示的是字节数

合理使用覆盖索引减少IO ,INNODB存储引擎中，secondary index(非主键索引，又称为辅助索引、二级索引)没有直接存储行地址，而是存储主键值。如果用户需要查询secondary index中所不包含的数据列，则需要先通过secondary index查找到主键值，然后再通过主键查询到其他数据列，因此需要查询两次。覆盖索引则可以在一个索引中获取所有需要的数据列，从而避免回表进行二次查找，节省IO因此效率较高。 例如

Plain Text
SELECT email，uid FROM user_email WHERE uid=xx

如果uid不是主键，适当时候可以将索引添加为index(uid，email)，以获得性能提升。
尽量不要在频繁更新的列上创建索引 如不在定义了 ON UPDATE CURRENT_STAMP 的列上创建索引，维护成本太高（好在mysql有insert buffer，会合并索引的插入）

5 SQL设计

• 程序中杜绝直接 SELECT * 读取全部字段 即使需要所有字段，减少网络带宽消耗，能有效利用覆盖索引，表结构变更对程序基本无影响

• 能确定返回结果只有一条时，使用 limit 1 在保证数据不会有误的前提下，能确定结果集数量时，多使用limit，尽快的返回结果。

• 禁止在where条件列上使用函数

  • 会导致索引失效，如lower(email)，qq % 4。可放到右边的常量上计算
  • 返回小结果集不是很大的情况下，可以对返回列使用函数，简化程序开发

• 小心隐式类型转换

  • 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
  • 十六进制的值和非数字做比较时，会被当做二进制串
  • 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
  • 有一个参数是 decimal 类型，如果另外一个参数是整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
  • 两个参数都是字符串，会按照字符串来比较，不做类型转换
  • 两个参数都是整数，按照整数来比较，不做类型转换
  • 所有其他情况下，两个参数都会被转换为浮点数再进行比较。
  • 如果一个索引建立在string类型上，如果这个字段和一个int类型的值比较。如phone定义的类型是varchar，但where使用phone in (098890)，两个参数都会被当成成浮点型。发生这个隐式转换并不是最糟的，最糟的是string转换后的float，mysql无法使用索引，这才导致了性能问题。

• 使用like模糊匹配，%不要放首位

  • 会导致索引失效，有这种搜索需求是，考虑其它方案，如sphinx全文搜索涉及到复杂sql时，务必先参考已有索引设计，先explain
  • 简单SQL拆分，不以代码处理复杂为由(比如 OR 条件： phone=‘10000’ or mobile=‘10000’，两个字段各自有索引，但只能用到其中一个。可以拆分成2个sql，或者union all。
    )
  • 先explain的好处是可以为了利用索引，增加更多查询限制条件

• 使用join时，where条件尽量使用充分利用同一表上的索引

  • 如 select t1.a,t2.b * from t1,t2 and t1.a=t2.a and t1.b=123 and t2.c= 4 ，如果t1.c与t2.c字段相同，那么t1上的索引(b,c)就只用到b了。此时如果把where条件中的t2.c=4改成t1.c=4，那么可以用到完整的索引,这种情况可能会在字段冗余设计（反范式）时出现,正确选取inner join和left join,少用子查询，改用join

• 少使用union，注意考虑去重

  • union all不去重，而少了排序操作，速度相对比union要快，如果没有去重的需求，优先使用union all

• IN的内容尽量不超过200个

  • 超过500个值使用批量的方式，否则一次执行会影响数据库的并发能力，因为单SQL只能且一直占用单CPU，而且可能导致主从复制延迟

• 拒绝大事务
  比如在一个事务里进行多个select，多个update，如果是高频事务，会严重影响MySQL并发能力，因为事务持有的锁等资源只在事务rollback/commit时才能释放。但同时也要权衡数据写入的一致性。

• 避免使用is null, is not null这样的比较

• order by … limit
  这种查询更多的是通过索引去优化，但order by的字段有讲究，比如主键id与time都是顺序递增，那就可以考虑order by id而非 time 。

• 杜绝危险SQL
  去掉where 1=1 这样无意义或恒真的条件，如果遇到update/delete或遭到sql注入就恐怖了
  SQL中不允许出现DDL语句。一般也不给予create/alter这类权限，但阿里云RDS只区分读写用户

• count计数
  首先count()、count(1)、count(col1)是有区别的，count()表示整个结果集有多少条记录，count(1)表示结果集里以primary key统计数量，绝大多数情况下count()与count(1)效果一样的，但count(col1)表示的是结果集里 col1 列 NOT null 的记录数。优先采用count()
  大数据量count是消耗资源的操作，甚至会拖慢整个库，查询性能问题无法解决的，应从产品设计上进行重构。例如当频繁需要count的查询，考虑使用汇总表
  遇到distinct的情况，group by方式可能效率更高。

• c1 < a order by c2
  与上面不同的是，order by之前有个范围查询，由前面的内容可知，用不到类似(c1,c2)的索引，但是可以利用(c2,c1)索引。另外还可以改写成join的方式实现。
  分页优化 建议使用合理的分页方式以提高分页效率，大页情况下不使用跳跃式分页 假如有类似下面分页语句: SELECT c1 FROM table1 ORDER BY ftime DESC LIMIT 10000,10; 这种分页方式会导致大量的io，因为MySQL使用的是提前读取策略。 推荐分页方式：

Plain Text
SELECT c1 FROM table1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 10

即传入上一次分页的界值

Plain Text
SELECT * FROM table as t1 inner JOIN (SELECT id FROM table ORDER BY time LIMIT 10000, 10) as t2 ON t1.id=t2.id

6 行为规范

• 不允许在开发负责人或经理不知情的情况下导现网数据
• 大批量更新，如修复数据，避开高峰期，并通知开发负责人。直接执行sql的由运维或开发负责人同事操作
• 及时处理已下线业务的SQL
• 复杂sql上线审核
  因为目前还没有SQL审查机制，复杂sql如多表join,count,group by，主动上报开发负责人评估。