本文为您介绍在创建轻量数据库服务后的相关使用规范建议。
数据库权限规范
所有 DDL(例如:创建表,更改表结构等)只有通过评审后,由 DBA 通过数据库管理工具(DMC)执行,在业务低峰期操作上线。
权限需要进行细粒度控制,读写权限分开,运维和开发权限要分开。
DDL 操作保留操作日志。
库表规范
InnoDB 是 MySQL 中支持事务的一种引擎,如果要在 MySQL 数据库中创建表,并且需要使用事务功能,那么必须使用 InnoDB 引擎来创建表,适配 MySQL 的其它引擎不支持事务。
小数类型需使用 decimal 类型来定义,禁止使用 float 和 double。
说明:
float 和 double 在存储的时候,存在精度损失的问题,很可能在值比较的时候得到的结果有误。如果存储的数据范围超过 decimal 的范围,建议将数据拆成整数和小数分开存储。
禁用保留字,例如 desc、range、match、delayed 等,请参见 MySQL 官方保留字。
数据表必须有主键,可以使用业务相关,有序且具有唯一性的字段作为主键,也可以使用业务无关的自增长字段作为主键。
说明:
无主键容易导致主库执行速度慢和复制延迟问题。
创建表时需为表中的字段设置默认值,并且将字段设置为 NOT NULL,以避免在插入数据时出现空值或缺失值的情况,数字类型默认值推荐给0,varchar 等字符类型的默认值推荐空字符串,如''。
建议表包含两个字段:create_time,update_time,且均为 datetime 类型。
说明:
数据仓库拖取数据时可以利用这两个统一字段无需询问业务。
在数据库出现意外时可以判断数据进入数据库和修改的时间,在极端情况可以帮助数据恢复的判断。
控制单表字段数量,字段上限50个。
如果存储的字符串长度几乎相等,使用 char 定长字符串类型。
字段允许适当跨表冗余,以避免关联查询,提高查询性能,但必须考虑数据一致。
说明:
冗余字段应遵循:
不是频繁修改的字段。
不是 varchar 超长字段和 text 字段。
合适的存储长度(不建议使用 LONG TEXT,BLOB 等长类型字段),不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。
索引规范
避免因为字段类型不同造成的隐式转换,导致索引失效。
业务上具有唯一特性的字段,即使是多个字段的组合,建议在所有具有唯一特性字段的最小集合上建立唯一索引。
例如:一个表含有 a,b,c,d,e,f 字段,在业务上 ab 和 ef 分别是具有唯一特性的字段集合,那么最好在最小集合 ab 和 ef 上分别建立唯一索引。
说明:
即使在应用层做了完善的校验控制,只要没有唯一索引,就可能会有脏数据产生。
同时需要考虑建立的唯一索引对查询是否真正有帮助,没有帮助的索引可以考虑删除。
需要考虑多建立的索引对插入性能的影响,根据唯一性相关的数据正确性需求,以及性能需求来权衡是不是需要多建立唯一性索引。
尽量在定长的字段(如:INT)上建立索引;在 varchar 字段上建立索引时,必须指定索引长度,无需对全字段建立索引,根据实际文本区分度决定索引长度即可。
说明:
索引长度与区分度是一对矛盾体,一般对字符串类型数据,长度为20的索引区分度会高达90%以上,可以使用
count(distinct left(列名,索引长度))/count(*)
的区分度来确定(有区分度的放前面,没有区分度的放后面)。页面搜索避免使用左模糊(如:SELECT * FROM users WHERE u_name LIKE ‘%hk’)或者全模糊,避免从索引扫描退化为全表扫描,如果需要请在应用层解决。
说明:
索引文件具有 B-tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。
利用覆盖索引来进行查询操作,避免回表,但是覆盖索引加的字段不能太多,要兼顾写性能。
说明:
能够建立索引的种类:主键索引、唯一索引、普通索引,而覆盖索引是一种查询的效果,利用 explain 的结果,extra 列会出现:using index。
SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好。
创建组合索引的时候,区分度最高的在左边。
单张表的索引数量控制在5个以内,或不超过表字段个数的20%。
创建索引避免有如下误解:
误认为一个查询就需要建一个索引。
误认为索引会消耗空间、严重拖慢更新和新增速度。
误认为业务的唯一性一律需要在应用层通过“先查后插”方式能解决。