Mysql 数据库开发规范

时间:2023-03-08 16:41:16

设计范式参看DDL与DDL

库表基础规范

1.注释

每个表要添加注释,对 status 型需指明主要值的含义,如”0-离线,1-在线”

2.表的字段数量

  • 单表字段数一般考虑上限为 30左右,再多的话考虑垂直分表,一是冷热数据分离,二是大字段分离,三是常在一起做条件和返回列的不分离。
  • 表字段控制少而精,可以提高IO效率,内存缓存更多有效数据,从而提高响应速度和并发能力,后续 alter table 也更快。

3.所有表都必须要显式指定主键

  • 如果没有主键或唯一索引,update/delete是通过所有字段来定位操作的行,相当于每行就是一次全表扫描
  • 只有需要全局唯一主键时,使用外部自增id服务。
  • 主键尽量采用自增方式,InnoDB表实际是一棵索引组织表,顺序存储可以提高存取效率。对于主键字段值是从其它地方插入(非自己使用AUTO_INCREMENT生产),去掉auto_increment定义。比如一些31天表、历史月份表上,不要auto_increment属性;全局id服务获取的主键也不需要 auto_increment 属性。

4.表的数据量控制在5000w以内(需要做缓存处理)

表字段数量不要超过20个,如果有需要建立主副表,主键一一关联,避免单行数据过多以及修改记录binlog ROW模式导致文件过大。
特别对于有一个text/blob或很大长度的varchar字段时,更应考虑单独存储。但也要注意查询条件尽量放在一个表上。

字段规范

具体字段类型请参考

1.数字类型定义

  • 对于整数的存储,建议区分开 TINYINT / INT / BIGINT 的选择,因为三者所占用的存储空间也有很大的差别,能确定不会使用负数的字段,建议添加unsigned定义。
  • 对于整型数值,mysql支持在类型名称后面的小括号内指定显示宽度,例如int(5)表示当数值宽度小于5位时候在数值前面填满宽度,一般配合zerofill属性使用。如果一个列指定为zerofill,则MySQL自动为该列添加unsigned属性。bigint(20), int(11),一般不要随便改动这个显示宽度,c++里面需要这个长度去截取字段
  • 使用tinyint来代替 enum和boolean,tinyint使用1个字节,一般用于status,type,flag的列。ENUM类型在需要修改或增加枚举值时,需要在线DDL,成本较高;ENUM列值如果含有数字类型,可能会引起默认值混淆
  • 使用Decimal 代替float/double存储精确浮点数。对于货币、金额这样的类型,使用decimal,如 decimal(9,2)。
  • 如果是固定精度的小数,也不建议使用DECIMAL,建议乘以固定倍数转换成整数(如 BIG INT)存储,可以大大节省存储空间,且不会带来任何附加维护成本

2.timestamp  初始值

  • datetime 和 timestamp类型所占的存储空间不同,前者8个字节(5.5版本前是5字节),后者4个字节,这样造成的后果是两者能表示的时间范围不同。优先使用timestamp,datetime也没问题
  • timestamp显示与时区有关,内部总是以 UTC 毫秒 来存的。取出来的是服务器时间。datetime 跟时区无关,存什么取什么,因为它存的时候不会转换成时间戳。因此,可以先手动转换成 utc 时间存入,取出来时再转换成本地时间。这样就跟时区相关了。
  • timestamp可以在insert/update行时,自动更新时间字段(如 f_set_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP),但一个表只能有一个这样的定义。
  • 默认时间,要么current_timestamp,要么’1970-01-02 01:01:01’,不要设置为 '' 或0
  • 如果一定要使用int的型存储时间戳,约定统一使用 int unsigned default 0

3. varchar 类型

  • char定长,它会删除字符串尾部的空格,varchar不会,varchar向前补1-2字节;。InnoDB建议使用varchar类型,不区分固定长度和可变长度。
  • 把 BLOB或TEXT列分离到单独的表中,它还使你在主数据表上运行 SELECT *查询的时候不会通过网络传输大量的BLOB或TEXT值

4.字段都使用 NOT NULL

  • 如果是索引字段,一定要定义为not null 。因为null值会影响cordinate统计,影响优化器对索引的选择
  • 如果不能保证insert时一定有值过来,定义时使用default '',或 0

索引规范

索引具体介绍请参考

1.任何新的select,update,delete上线,都要先explain,看索引使用情况

尽量避免extra列出现:Using File Sort,Using Temporary,rows超过1000的要谨慎上线。

explain解读

  • type:ALL, index, range, ref, eq_ref, const, system, NULL(从左到右,性能从差到好)
  • possible_keys:指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
  • key:表示MySQL实际决定使用的键(索引)

  如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX

  • ref:表示选择 key 列上的索引,哪些列或常量被用于查找索引列上的值
  • rows:根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数
  • Extra
    • Using temporary:表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询
    • Using filesort:MySQL中无法利用索引完成的排序操作称为“文件排序”

还可以使用 explain extended sql语句 与 show warnings 等分析单个语句,使用 select count( distinct 字段)/count(*) from <table_name> 分析索引必要性。

2.避免冗余索引

InnoDB表是一棵索引组织表,主键是和数据放在一起的聚集索引,普通索引最终指向的是主键地址。不要在频繁更新的列上创建索引。

3.前缀索引

对超过30个字符长度的列创建索引时,考虑使用 index firstname_lastname4 (firstname, lastname(4)) 表示截取 lastname 前 4个字符做索引,既可以提高查找效率,也可以节省空间。

4.联合索引

MySQL中的索引可以以一定顺序引用多个列,这种索引叫做联合索引,一般的,一个联合索引是一个有序元组,其中各个元素均为数据表的一列。mysql使用联合索引时,从左向右匹配,遇到断开或者范围查询时,无法用到后续的索引列。

5.覆盖索引

INNODB存储引擎中,secondary index(非主键索引,又称为辅助索引、二级索引)没有直接存储行地址,而是存储主键值。

例如SELECT email,uid FROM user_email WHERE uid=xx,如果uid不是主键,适当时候可以将索引添加为index(uid,email),以获得性能提升(不需要回表查询)。如果用户需要查询secondary index中所不包含的数据列,则需要先通过secondary index查找到主键值,然后再通过主键查询到其他数据列,因此需要查询两次(二次检索)。

SQL 设计

1.能确定返回结果只有一条时,使用 limit 1

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

2.使用like模糊匹配,%不要放首位

会导致索引失效,有这种搜索需求是,考虑其它方案,如sphinx全文搜索

3.使用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。不允许滥用left join

4.考虑使用union all,少使用union,注意考虑去重

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

5.分页优化

大页情况下不使用跳跃式分页,原因 MySQL的 limit m,n 工作原理是 提前读取 原则,即先读取符合where条件的前面m+n条记录,然后抛弃前m条,返回后面n条,所以m越大,偏移量越大,性能就越差。

假如有类似下面分页语句:

SELECT FROM table1 ORDER BY ftime DESC LIMIT 10000,10;   这种分页方式会导致大量的io,因为MySQL使用的是提前读取策略。

优化方式有:通过二级索引消除排序,利用索引覆盖避免回表,使用 where条件限制读取范围。推荐使用 where 条件缩小范围:

下一页:SELECT FROM table1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 101 ( 如果数据原本是倒序显示 )

上一页 SELECT FROM table1 WHERE ftime > last_time ORDER BY ftime DESC LIMIT 99

对于上下页可以使用 where 约束,从而避免提前读取。

6. count 计数

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

7.减少与数据库交互的次数,尽量采用批量SQL语句

  • INSERT ... ON DUPLICATE KEY UPDATE ...,插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE,如果不重复则直接插入,影响1行。
  • REPLACE INTO类似,但它是冲突时删除旧行。INSERT IGNORE相反,保留旧行,丢弃要插入的新行。
  • INSERT INTO VALUES(),(),(),合并插入。

8.杜绝危险SQL

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

9.是否应该 order by 主键

许多排序的场景,如果主键id是增长的,如果 order by f_create_time 查询慢,有可能使用了filesort,此时最简单的办法是看能否换成 order by id,因为id作为主键是递增的,并且附带在了每个二级索引后面。
但是也要谨慎使用 order by id,特别是在explain结果看到filesort的情况下,优化器极有可能放弃这个filesort,而选择了它所认为更高效的扫描方式,实则更慢。


http://seanlook.com/2016/05/11/mysql-dev-principle-ec/

http://seanlook.com/categories/MySQL/