Programming 数据库

数据库设计和一些业务注意的要点

包括数据库索引设计

Posted by Haiming on October 14, 2019

数据库在任何的业务之中都非常重要，尤其是对于互联网企业的业务，大部分都是“数据密集型”，说白了，不同于游戏等“计算密集型”业务，其主要就是将数据传输和搬运给需要的人，那么这种场合下面，数据库的设计就更重要了。

本文之中，我对于数据库的索引，结构和一些其他需要注意的细节方面做一些简要的梳理。下文的很多情况是针对于互联网公司的，高并发访问、低频写入、高频读取、产生大量日志、单个业务逻辑简单、分布式部署、团队规模大、产品线复杂、互相关联、快速迭代……由于互联网公司的业务需求和其他一些不一样，因此很多情况下其数据库的架构也完全不同。

本文之中并不对于 MySQL 的本身内置索引结构的实现，例如 B+ Tree等等，做一些比较具体的叙述。而是从业务层面，看如何设计数据库表和索引可以更大程度上满足业务的需求。

话不多说，开讲。

1. MySQL 索引设计

下面的内容参考自：MySQL 索引设计概要

首先，索引只是对数据库的一种数据结构建模，是独立的一个体系。对一个表，可以从列 A 为索引进行建模，也可以从列 B 为索引进行建模。那么，从这个角度看，一个数据表的索引可以有很多，每个索引之间也可以没有联系。

1.1 磁盘 IO

MySQL 之中的数据是以文件的形式存储在磁盘上的。从磁盘上面进行随机访问数据是非常费时的（寻道等等复杂操作），所以 数据库程序。和 操作系统 提供了 缓冲池 和 内存 来提高数据的访问速度。下面这张图就是 磁盘IO的结构。

Disk-IO

除此之外，还要知道的是数据库对数据的读取，并不是以行为单位进行的。也就是说，不管度多少行，都需要先把该行或者多行所在的页全都堵进来，然后再加载对应的数据记录。

读取所耗费的时间，只和文件有关，与行数无关。

MySQL 之中页的大小可以不同，大部分为 16KB， 也可能是 8KB 或其他值。索引或者行记录是否在缓冲池之中极大的影响了访问索引或者数据的成本。

1.2 随机读取

随机读取的成本很大，读取一个页面到缓存池，需要 10ms 左右的时间。

Disk-IO-Total-Time

上面是一次读取之中大概估算的每个环节耗时，其中真正用于数据计算的有 6ms。

1.3 内存读取

如果在数据库的缓冲池之中，没有找到对应的数据页，那么会去内存之中找对应的页面。

Read-from-Memory

这一步是发生在从磁盘之中读取之前的。也就是说，读取的顺序为：

缓冲区——内存读取——磁盘读取

1.4 顺序读取

在顺序读取的情况下，磁盘的读取速度会快非常多。大概在 40MB/s 左右。如果在这个情况下，一个页面的大小在4KB，那么一秒可以读取 10000 个页面，读取一个页面的时间也就是 0.1 ms。这样相比之下速度和内存之中读取数据是相当的。

数据的顺序读取还有另外两个优势：

  1. 同时读取多个界面，意味着总时间的消耗会大幅度的减少，磁盘的吞吐量会急速上升。
  2. 数据库管理程序对一些可能将会使用的页面进行预读，那么就会减少查询请求的等待和响应的时间。换句话说，一口气读完所有数据，留着以后备用。

那么综上可以看到，数据库的查询操作时间大部分都消耗在从磁盘或者内存之中读取数据。那么因为 IO 的代价非常巨大，如何在一次数据库之中减少随机 IO 的次数，往往可以大幅度降低查询所耗费的时间。

1.5 查询过程

本节主要描述 MySQL 查询的执行过程，以及数据库之中本身数据的内容组成结构对于查询性能的影响。

索引片

索引片，就是 SQL 查询在执行过程之中扫描的一个索引片段。在这个范围之内的索引将被顺序扫描。

根据索引片包含的列数不同，可以分为 宽索引 和 窄索引

Thin-Index-and-Fat-Index

宽索引和窄索引不是绝对的，比如对于：

SELECT id, username, age FROM users WHERE username="draven"

这一句 SQL 而言，（id，username） 就是一个窄索引，因为其并没有包含存在于 SQL 之中的 age 列。而（id，username，age）就是该查询的一个 宽索引， 因为其包含这个查询之中全部的数据列。

宽索引可以避免 二次的随机 IO。 窄索引就有随机 IO 的问题，其需要在得到结果之后再根据结果的主键 ID 从主键索引之中查找对应的数据。也就是说根据ID 从原来的表之中再次查找，像这样：

Thin-Index-and-Clustered-Index

1.6 过滤因子

如果是窄索引，那么对于表的随机访问是不可避免的，这个时候，如何让索引片变“薄” 就是我们需要做的了。也就是，在多重条件之下，怎么能让每一次筛选之后的结果变少。比如：

Filter-Facto

对于这个结构而言，选取第三个做过滤因子肯定比选取第一个做过滤因子好太多。

Combined-Filter-Facto

但是，当三个过滤条件都是等值谓词的时候，几个索引列的顺序是无所谓的。也就是索引 (name, age, sex) 和 (age, sex, name) 对于上图中的条件来说是完全一样的，这两个索引在执行查询时都有着完全相同的效果。

上面这种设计，最好列和列之间不要有太多的相关性，比如一个城市和邮编之间的关系。

对于同一个列，不同的值也有不同的性能差别。

Same-Columns-Filter-Facto

在考虑一个问题的时候，要多种情况综合考虑。

1.7 匹配列和过滤列

匹配列，是用来精准匹配的。但是过滤列，是用来进行模糊的范围筛选的。比如：

SELECT * FROM users
WHERE name = "draven" AND sex = "male" AND age > 20;

上面这种情况，name 和 sex 才是匹配列，会以这两个列作为匹配列，然后将 age 当作过滤列进行过滤。

Match-Columns-Filter-Columns

1.8 索引的设计

综上所述，总体而言，如何减少随机读取的次数，是设计索引的时候需要重视的问题。

三星索引

三星索引是对于一个查询语句可能的最好索引。如果一个查询语句的索引是三星索引，那么只需要进行 一次磁盘的随机读，及一个窄索引片的顺序扫描 就可以得到全部的结果。

根据书中对三星索引的定义，我们可以理解为主键索引对于 WHERE id = 1 就是一个特殊的三星索引，我们只需要对主键索引树进行一次索引访问并且顺序读取一条数据记录查询就结束了。

Three-Star-Index

为了满足三颗星，要：

  1. 取所有等值谓词之中的列，作为索引开头的开始的列。
  2. 将 ORDER BY 放到索引之中
  3. 将查询语句剩余的列全都加入索引之中。

Behind-Three-Star-Index

下面是三星索引的三颗星的意义：

  1. 减少索引片的大小，和需要扫描的数据行。
  2. 避免排序，减少硬盘 IO 和内存的使用
  3. 避免每一个索引对应的数据行都要进行一次随机 IO 才能从聚集索引之中读取剩余数据。

2. 互联网 MySQL 数据库应用潜规则

https://cloud.tencent.com/developer/article/1159740

一共分为六个部分：

一、基础规范 二、命名规范 三、表设计规范 四、字段设计规范 五、索引设计规范 六、SQL使用规范

首先，对于高并发大数据的互联网业务，总体思想为：解放数据库 CPU， 将计算转移到服务层。因为在高并发的情况下，计算这些功能很可能会将数据库直接拖死，业务逻辑放到服务层可以更好扩展性能。数据库回归到最原始的存储和索引功能。

2.1 基础规范

  1. 必须使用 InnoDB 存储引擎

    支持事务，支持行级锁，并发性能更好，CPU 以及内存缓存页优化，可以使资源利用率更高。

  2. 必须使用 UTF8mb4 字符集：无需转码，无乱码风险，而且支持emoji 等等
  3. 数据表，数据字段必须加入注释：不然谁知道每个字段的作用？
  4. 禁止使用存储过程，视图，触发器，Event：再看我们一开始的话： 架构核心思路是“解放数据库CPU，将计算转移到服务层“。这个和我们的想法不符。
  5. 禁止存储大文件或者大照片：数据库天生属性是用来存储结构化的数据结构，这些弄文件系统再好不过。

2.2 命名规范

  1. 只允许使用 内网域名，而不是 IP 连接数据库： 虽然相对于需要 DNS 的域名访问而言，IP 直连更快，但是对于大数据库的扩展和迁移考虑，域名更好。
  2. 开发环境，测试环境，线上环境数据库内网域名遵循命名规范。
  3. 库名，表名，字段名：小写，下划线风格，不超过 32 个字符。禁止拼音和英文混用。
  4. 表名，非唯一索引名，唯一索引名必须都按照指定格式。

2.3 表设计规范

  1. 单个实例之中，表的数目必须小于 500

  2. 单个表之中，列数目必须小于 30.

  3. 表必须有主键，例如自增主键。

    • 主键递增，那么数据可以按照行进行插入。这样做可以避免 page 分裂，减少表碎片，提升空间和内存的使用

    如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：

    img

    这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

    如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：

    img

    此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。 在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。 mysql 在频繁的更新、删除操作，会产生碎片。而含碎片比较大的表，查询效率会降低。此时需对表进行优化，这样才会使查询变得更有效率。

    • 主键要选择较短的数据类型，InnoDB 普通索引都会保存主键的值，较短的数据类型，可以有效减少索引的磁盘空间，提高索引的缓存效率。
    • 无主键的表删除，在 row 模式下面的主从结构，会导致从库 hang 住。

    看这篇文章：https://blog.51cto.com/175779/2309757

    主要理解就是因为没有主见，所以当主库之中的表被删除的时候，MySQL 同步没法针对性的利用主键去搜索一些需要的信息，在这种情况的处理之下，其会对表之中的每一条数据生成一条 delete 数据并执行，对于文中的例子，其要执行的执行数为 200,000 * 200,000 = 40,000,000,000 次，400多亿。

  4. 禁止使用外键约束，可以冗余外键（例如将国家编号放到城市表之中，或者将支行编号放到账户户头号码之中），如果有外键完整性约束（完整性约束，就是为了避免错误数据插入导致比如外键和其他表的主键对应不上等等），需要使用应用程序控制。

    外键会导致表和表之间耦合，那么在 update 和 delete 的时候都会涉及到相关联的表，影响性能，甚至会造成死锁。高并发情况下容易造成数据库的性能牺牲。

2.4 字段设计规范

  1. 必须把字段定义为 NOT NULL， 并且提供默认值。
    • Null 的列使索引/索引统计/值比较 都更加复杂，对 MySQL 而言更难优化。
    • null 的这种类型在 MySQL 内部要进行特殊处理，增加数据库处理记录的复杂性。同等条件下，表中有很多空字段的时候，数据库的处理性能会降低很多。
    • 对 null 进行处理的时候，只能采用 is null 或者 is not null ，不可以采用之前的操作符号，比如 ==， < , > 等符号。比如 where name != 'shenjian'，那么存在 name 为 null 的记录，查询结果就不会包含 name 为 null 的记录。
  2. 禁止使用 TEXT， BLOB 类型： 之前讲过，大文件直接按照文件来放置，不要放在数据库里面徒增消耗
  3. 禁止使用小数存储货币：按理来讲，是不是觉得货币这种天生应该是带有两位小数？但是有这样一个案例：分销，三天100块，平摊下来是每天33.33 元，那么就会有0.01 的误差在这里。因此，从此之后，设计都是以分为单位的整数。
  4. 必须使用 nvarchar（20）存储手机号。
    • 手机号涉及到区号或者国家代号的情况下，可能会出现 +-（）等等非数字的符号。
    • varchar 可以做模糊查询，类似于 like“138%”
  5. 禁止使用 ENUM，可以使用 TINYINT 代替。臭名昭著的 MySQL ENUM 类型
    • 增加新的ENUM要DDL（Data Definition Language)操作
    • ENUM 内部实际存储的就是整数。
    • 上面这篇文章之中提到过的，ENUM作为 MySQL 特有的一种奇葩类型，十分不推荐。

2.5 索引设计规范

  1. 单表索引建议在 5个以内

  2. 单索引字段数不允许超过5个

    因为当字段超过五个的时候，已经起不到有效过滤数据的作用了。

  3. 禁止在更新非常频繁，区分度不高的属性上面建立索引。

    • 从索引的属性上看，每次更新都会变更B+树，更新频繁的字段建立索引，会大大降低数据库的性能
    • 重复很高的数据，类似“性别”，建立索引是没什么意义的，不能有效过滤数据，性能和全表重新扫描一遍没有什么区别。

  4. 建立组合索引，必须把区分度高的字段放在前面：之前有提到过，其本身区分度越高，那么对于之后的索引的数据筛选就越方便，需要的数据越少。

2.6 SQL 使用规范

  1. 禁止使用 SELECT *，只获取必要的字段，需要显式的说明列属性。

    • 首先，从性能方向考虑，读取不需要的列会增加 CPU，IO，NET 消耗。
    • 不能有效的利用覆盖索引，select * 这种完全没有任何必要去建立索引
    • 容易在增加或者删除字段之后出现程序的bug，比如数据库删除了一个字段，但是程序之中，比如 DTO 没删除，那么取出来的时候就会全部当成 null， 那么最后得到的就是 NullPointerException。

  2. 禁止使用 INSERT INTO t_xxx VALUES(xxx), 必须显式的指定插入的列属性。

    容易在增加字段或者删除字段之后出现 bug。比如数据库的前几列是自动生成好的，如果不指定哪个字段对应哪个字段，那么就会导致从头到尾按照顺序进行插入，最后就是全都一团乱麻。

  3. 禁止使用属性隐式转换。

    SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中 phone 的索引。原因在于 phone 是 varchar 类型，SQL 带入的是整型，所以无法命中索引，加一个引号变成 SELECT uid FROM t_user WHERE phone=’13812345678’ 就好了。

  4. 禁止在 WHERE 条件的属性上面使用函数或者表达式

    SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描。

    正确的写法为：

    SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')

  5. 禁止负向查询，以及 % 开头的模糊查询。

    • 负向查询条件，会导致全表扫描，类似 NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等
    • % 开头的模糊查询，会导致全表扫描。

    此军规争议比较大，部分网友反馈不这么做很多业务实现不了，稍微解释一下：

    一般来说，WHERE过滤条件不会只带这么一个“负向查询条件”，还会有其他过滤条件，举个例子：查询沈剑已完成订单之外的订单(好拗口)：

    SELECT oid FROM t_order WHERE uid=123 AND status != 1;

    订单表5000w数据，但uid=123就会迅速的将数据量过滤到很少的级别(uid建立了索引)，此时再接上一个负向的查询条件就无所谓了，扫描的行数本身就会很少。

    但如果要查询所有已完成订单之外的订单：

    SELECT oid FROM t_order WHERE status != 1;

    这就挂了，立马CPU100%，status索引会失效，负向查询导致全表扫描。

  6. 禁止大表使用 JOIN 查询，禁止大表进行子查询：会产生临时表，消耗很多的内存和 CPU，极大的影响数据库的性能。

  7. 禁止使用 OR 条件，必须改为 IN 查询。

    这一点在旧版本之中的 MySQL 十分明显，OR 查询是没法命中索引的。

  8. 应用程序必须捕获 SQL 异常，并有相应的处理

2.7 行为规范

  1. 禁止使用应用程序配置文件内的帐号手工访问线上数据库
  2. 禁止非DBA对线上数据库进行写操作，修改线上数据需要提交工单，由DBA执行，提交的SQL语句必须经过测试
  3. 分配非DBA以只读帐号，必须通过VPN+跳板机访问授权的从库
  4. 开发、测试、线上环境隔离

数据库设计之中一些需要注意的点

  1. 不管什么业务和设计，都要考虑分库分表的情况。

  2. 数据库之中不使用外键的原因： 不可保证在分库分表之后，有外键关系的数据库之间仍然在一个实例上面，这样就可能出现查找失败的情况。

    逻辑上，外键的约束要在业务代码之中描述。

  3. 大文件，包括视频和图片等等，最好放在文件系统之中描述。数据库天然适合存放具有结构关系的数据，而对于这种大文件，放在数据库之中会拖慢性能。同样的，类似于商品描述这种信息，在业务部署上面应该放到CDN或者单独的数据库之中，因为其基本不会改动，读取远远大于写入。

CATALOG
    FEATURED TAGS
    Programming 记录 Study 日常 Angular TCP HTTP HTTPS JavaScript Docker Spring Boot Java 算法 数据结构 HashMap 面试 后端 Python Web Redis MQ AOP Linux MyBatis Shell Go API Spring Kubernetes 源码 MySQL Algorithm LeetCode Network Scala Flink
    FRIENDS