数据库常见知识点总结-列式存储、主键、存储大数据量问题

Mysql：小主键，大问题

本篇讲解 Mysql 的「主键」问题，从「为什么」的角度来了解 Mysql 主键相关的知识，并拓展到主键的生成方案问题。再也不怕被问到 Mysql 时只知道 CRUD 了。

一、为什么需要主键

数据记录需具有「唯一性」(第一范式)

数据需要关联 「join」

数据库底层索引用于检索数据所需

以下废话连篇，可以直接跳过到下一节。

“「信息」是用来消除随机不定性的东西”（香农）。人通过获得、识别自然界和社会的不同信息来区别不同事物，得以认识和改造世界。「数据」是反映客观事物属性的记录，是信息的具体表现形式。数据经过加工处理之后，就成为信息；而信息需要经过数字化转变成数据才能存储和传输。「数据库」就是用于存储数据记录的。既已如此，「记录」便是具有确定性(相对)的信息，其确定性即唯一性。我们得出第一条原因：

「1.数据记录需具有唯一性」

世界是由客观存在及其关系组成的。「数据」是数字化和模型化的存在关系。数据除了本身的描述价值外，其价值还在于其相互关联性。为实现关联的准确性，数据需要有对外相互关联的标识。所以体现在数据存储上，「主键」的第二作用，也是存在的第二因素即：

「2.数据需要关联」

「数据」用于描述客观实在的，本身没有意义。只有在根据主观需求组织之后，通过一定方式满足人认识事物的过程才具有了意义。所以数据需要被检索，被组织。则主键第三个作用：

「3.数据库底层索引用于检索数据所需」

二、为什么主键不宜过长

这个问题的点在「长」上。那「短」比「长」有什么优势？（嘿嘿嘿，内涵）—— 短不占空间。但这么点磁盘空间相对整个数据量来说微不足道，而且我们一般不怎么用到主键列。那么原因应该在「快」上，而且和原始数据关系不大。以此自然得出和「索引」相关，而且和索引读取相关。那么为什么长主键在「索引」中会影响性能？

上面是 Innodb 的索引数据结构。左边是「聚簇索引」，通过主键定位数据记录。右边是「二级索引」，对列数据做索引，通过列数据查找数据主键。如果通过二级索引查询数据，流程如图上所示，先从二级索引树上搜索到「主键」，然后在聚簇索引上通过主键搜索到数据行。其中二级索引的叶子节点是直接存储的主键值，而不是主键指针。所以如果主键太长，一个二级索引树所能存储的索引记录就会变少，这样在有限的「索引缓冲」中，需要读取磁盘的次数就会变多，所以性能就会下降。

三、为什么建议使用自增 ID

InnoDB 使用「聚簇索引」，如上图所示，数据记录本身被存于主索引（一颗 B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录「按主键顺序存放」，因此每当有一条新的记录插入时，MySQL 会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB 默认为 15/16），则开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会「顺序添加」到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个「紧凑」的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上，如下图左侧所示。否则由于每次插入主键的值近似于随机，因此每次新记录都要被插到现有索引页的中间某个位置，MySQL 不得不为了将新记录插到合适位置而「移动数据」，如下图右侧所示，这样就造成了一定的开销。由于此，Mysql 为维护索引可能需要频繁的刷新缓冲，增加了方法磁盘 IO 的次数，而且时常需要对索引结构进行重组织。

四、业务 Key VS 逻辑 Key

「业务 Key」，即使用具有业务意义的 id 作为 Key，比如使用订单流水号作为订单表的主键 Key。「逻辑 Key」，即无关业务的 Key，按某种规则生成 Key，如自增 Key。

业务 Key 的优点

Key 具有业务意义，在查询时可以直接作为搜索关键字使用

不需要额外的列和索引空间

可以减少一些 join 操作。

业务 Key 的缺点

当业务发生变化时，有时需要变更主键

涉及多列 Key 时比较难操作

业务 Key 往往比较长，所占空间更大，导致更大的磁盘 IO

在 Key 确定前不能持久化数据，有时我们没有在确定数据 Key 时，就想先添加一条记录，之后再更新业务 Key

设计一个兼具易用和性能的 Key 生成方案比较难

逻辑 Key 的优点

不会因为业务的变动而需要修改 Key 逻辑

操作简单，且易于管理

逻辑 Key 往往更小，性能更优

逻辑 Key 更容易保证唯一性

更易于优化

逻辑 Key 缺点

查询主键列和主键索引需要额外的磁盘空间

在插入数据和更新数据时需要额外的 IO

更多的 join 可能

如果没有唯一性策略限制，容易出现重复的 Key

测试环境和正式环境 Key 不一致，不利于排查问题

Key 的值没有和数据关联，不符合三范式

不能用于搜索关键字

依赖不同数据库系统的具体实现，不利于底层数据库的替换

五、主键生成

一般情况下，我们都使用 Mysql 的自增 ID，来作为表的「主键」，这样简单，而且从上面讲到的来看，性能也是最好的。但是在分库分表的情况情况下，自增 ID 则不能满足需求。我们可以来看看不同数据库生成 ID 的方式，也看一些分布式 ID 生成方案。利于我们思考甚至实现自己的分布式 ID 生成服务。

数据库的实现

Mysql 自增

Mysql 在内存中维护一个「自增计数器」，每次访问 auto-increment 计数器的时候， InnoDB 都会加上一个名为「AUTO-INC 锁」直到该语句结束(注意锁只持有到语句结束,不是事务结束)。AUTO-INC 锁是一个特殊的表级别的锁，用来提升包含 auto_increment 列的并发插入性。

在分布式的情况下，其实可以独立一个服务和数据库来做 id 生成，依旧依赖 Mysql 的表 id 自增能力来为第三方服务统一生成 id。为性能考虑可以不同业务使用不同的表。

Mongodb ObjectId

Mongodb 为防止主键冲突，设计了一个 ObjectId 作为主键 id。它由一个 12 字节的十六进制数字组成，其中包含以下几部分：

Time：时间戳。4 字节。秒级。

Machine：机器标识。3 字节。一般是机器主机名的散列值，这样就确保了不同主机生成不同的机器 hash 值，确保在分布式中不造成冲突，同一台机器的值相同。

PID：进程 ID。2 字节。上面的 Machine 是为了确保在不同机器产生的 objectId 不冲突，而 pid 就是为了在同一台机器不同的 mongodb 进程产生的 objectId 不冲突。

INC：自增计数器。3 字节。前面的九个字节保证了一秒内不同机器不同进程生成的 objectId 不冲突，自增计数器，用来确保在同一秒内产生的 objectId 也不会发现冲突，允许 256 的 3 次方等于 16777216 条记录的唯一性。

Cassandra TimeUUID

Cassandra 使用下面规则生成一个唯一的 id：time + MAC + sequence

方案

Zookeeper 自增：通过 zk 的自增机制实现。

Redis 自增：通过 Redis 的自增机制实现。

UUID：使用 UUID 字符串作为 Key。

snowflake 算法：和 Mongodb 的实现类似，1位符号位 + 41位时间戳（毫秒级）+ 10位数据机器位 + 12位毫秒内的序列。

开源实现

百度 UidGenerator：基于「snowflake」算法。

美团 Leaf：同时实现了基于 Mysql 自增（优化）和 snowflake 算法的机制。

列式存储

时间序列数据库(TSDB)初识与选择

十分钟了解 Apache Druid

Apache Druid 底层存储设计

Apache Druid 的集群设计与工作流程

Mysql 大表问题和解

Mysql 存储大数据量问题

Mysql 单表适合的最大数据量是多少？

我们说 Mysql 单表适合存储的最大数据量，自然不是说能够存储的最大数据量，如果是说能够存储的最大量，那么，如果你使用自增 ID，最大就可以存储 2^32 或 2^64 条记录了，这是按自增 ID 的数据类型 int 或 bigint 来计算的；如果你不使用自增 id，且没有 id 最大值的限制，如使用足够长度的随机字符串，那么能够限制单表最大数据量的就只剩磁盘空间了。显然我们不是在讨论这个问题。

影响 Mysql 单表的最优最大数量的一个重要因素其实是索引。

我们知道 Mysql 的主要存储引擎 InnoDB 采用 B+树结构索引。（至于为什么 Mysql 选择 b+树而不是其他数据结构来组织索引，不是本文讨论的话题，之后的文章会讲到。）那么 B+树索引是如何影响 Mysql 单表数据量的呢？

B+树

一棵 B+树如下所示：

B+树

Mysql 的 B+树索引存储在磁盘上，Mysql 每次读取磁盘 Page 的大小是 16KB，为了保证每次查询的效率，需要保证每次查询访问磁盘的次数，一般设计为 2-3 次磁盘访问，再多性能将严重不足。Mysql B+树索引的每个节点需要存储一个指针（8Byte）和一个键值（8Byte）。因此计算16KB/(8B+8B)=1K 16KB 可以存储 1K 个节点，3 次磁盘访问(即 B+树 3 的深度)可以存储 1K _ 1K _ 1K 即 10 亿数据。

如果查询依赖非主键索引，那么还涉及「二级索引」。这样数据量将更小。

拆分

分而治之——没有什么问题不能通过拆分一次来解决，不行就拆多次。

Mysql 单表存储的数据量有限。一个解决大数据量存储的办法就是分库分表。说白了就是一个数据库一张表放不下那么多数据，那就分多个数据库多张表存储。

拆分可分为「垂直拆分」和「水平拆分」。

「垂直拆分」是按照不同的表（或者 Schema）来切分到不同的数据库（主机）之上，「水平拆分」则是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面或多张相同 Schema 的不同表中。

「垂直拆分」的最大特点就是规则简单，实施也更为方便，尤其适合各业务之间的耦合度非常低，相互影响很小，业务逻辑非常清晰的系统。在这种系统中，可以很容易做到将不同业务模块所使用的表分拆到不同的数据库中。根据不同的表来进行拆分，对应用程序的影响也更小，拆分规则也会比较简单清晰。

「水平拆分」与「垂直切分」相比，相对来说稍微复杂一些。因为要将同一个表中的不同数据拆分到不同的数据库中，对于应用程序来说，拆分规则本身就较根据表名来拆分更为复杂，后期的数据维护也会更为复杂一些。

「垂直拆分」最直接的就是按领域拆分服务，隔离领域数据库。如此每个库所承担的数据压力就减少了。

「水平拆分」就是将同一个 Schema 的数据拆分到不同的库或不同的表中，这样每个表的数据量也将减小，查询效率将更高效。「水平拆分」就涉及到表的分片规则问题。

「几种典型的分片规则包括：」

 按照用户 ID 求模，将数据分散到不同的数据库，具有相同数据用户的数据都被分散到一个库中。 按照日期，将不同月甚至日的数据分散到不同的库中。 按照某个特定的字段求摸，或者根据特定范围段分散到不同的库中。

实现

门面模式——没有什么问题不能通过添加一个中间层来解决。

「垂直拆分」的一个方案就是在应用层使用多个数据源，按业务访问不同的数据源。另外更好方案其实就是微服务化。按不同的业务领域来拆分微服务，明确领域边界，隔离领域数据库。这样将对数据的存取内聚到独立的服务之中，对外提供统一的接口。在需要同时依赖多个服务时，我们可以通过添加「门面应用」来组合底层服务的数据，以提供更符合上层业务需求的接口，这些服务往往更接近真实的业务。而底层的服务则是更加内聚的资源服务。

代理模式——没有什么问题不能通过添加一个中间层来解决。

对于「水平拆分」应该尽量屏蔽拆分带来的数据访问困恼，为了让上层业务无需关心下层数据组织方式。「水平拆分」往往通过添加一个代理层来做这些事情，代理层对上提供虚拟表，这些虚拟表就像我们在单库上设计的单表一样；代理层对下解析和拆分执行 sql，然后按相应规则在不同的库和表执行相应的 sql 请求，再合并数据，并将合并后的结果返回给上层调用者。

「一般代理方式分为如下两种：」

进程内代理

进程内代理即将代理层嵌入到业务服务内部，拦截 sql 请求并做相应的处理。这样的好处是简单，但是侵入性大，且不够灵活。

进程内代理

进程外代理

进程外代理即将代理独立成服务，代理真实业务服务和数据库之间的请求。这样是比较复杂的，需要高可用的代理服务架构。但是这样对业务的侵入性低，且易于升级扩展。

进程外代理

问题

分布式事务问题

什么是分布式事务？本地事务的定义就是一系列相关的数据库操作完成后要满足 ACID 四大特性，而分布式事务就是将同一进程的操作放到不同的微服务进程中，即不同微服务应用进程的数据库操作满足事务要求，或者对不同数据库的一系列操作需满足事务要求。

这里就有两个问题需要解决。一个是因为应用的分布式造成的，一个是因为数据库本身的分布式造成的。数据库本身的分布式事务问题一般由数据库自身解决，大多数分布式数据库都可以做到一定的数据一致性保证，如 HBase 保证的强一致性，Cassandra 保证的最终一致性。

应用数据的一致性事务方案我们也可以参考分布式数据库的实现原理来实现。业界也有很多分布式事务的解决思路，如：

XA 方案

TCC 方案

本地消息表

可靠消息最终一致性方案

最大努力通知方案

多表 Join 问题

通过分析 Join sql，将 sql 拆分成独立的查询请求，然后分别执行，并将结果合并计算返回给调用者。这个地方会涉及到很多执行优化的问题。

数据统计问题

当数据被分片到不同的数据库或不同的表中时，要对数据做一些全局的或涉及大量数据的统计时便会遇到一些问题。如求 Max，Min，Sum 等聚合问题。如果统计的数据有一定的业务规则，如只会按用户维度去统计，如统计某个用户的订单量，那么对订单表的分片，其实可以采用按用户 id 来分片，如此就可以解决这类统计问题。但是这种方案不通用。很多分片代理服务都需要将 sql 分片到不同的节点上去执行，然后再合并结果返回。

ID 问题

使用「分库分表」之后，就无法使用 Mysql 的表自增作为 id，因为不同库和表的自增将出现冲突的 id。解决这个问题就需要引入分布式 id 生成技术（将在以后的文章中讲到）。

「推荐系列：」

列式存储时间序列数据库(TSDB)初识与选择十分钟了解 Apache DruidApache Druid 底层存储设计Apache Druid 的集群设计与工作流程