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/

相关文章