MySQL 数据存储 / 索引 / 事务隔离级别 / 主从复制 / 分库分表

数据存储引擎

存储引擎是 MYSQL 的核心技术，不同的存储引擎使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力。常见的引擎分为三种：InnoDB 存储引擎（MYSQL 默认的事务性引擎）、MyISAM 存储引擎、Memory 存储引擎。三种存储引擎的功能对比如下表所示：

总结三种引擎的使用选择如下：

• InnoDB：适合要提供提交、回滚和崩溃后的安全恢复的事务安全能力，并要求实现并发控制；

• MyISAM：适合于只读的数据，或者表比较小、可以忍受修复操作数据库；

• Memory：适用于快速查找数据，用于数据分析中产生的中间数据。

数据库三大范式

• 第一范式：数据库表的每一列都是不可分割的基本数据项，即同一列不能有多个值；

• 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。如：设计订单信息表时，商品名称、商品价格等商品信息与表的主键不相关，而只与商品编号相关，因此可将表设计为订单信息表和商品信息表。

• 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。如果一张表中出现另一张表的非主键，可以将这两张表用外键关联，而不是将另一张表的非主键直接写在当前表中。设计数据库表的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。

数据库索引类型及原理

索引是一种对数据库表中一列或多列的值进行排序的一种数据结构，指向表中特定的数据内容，从而提高查询效率。

一、为什么要用索引

假设有一张存储了 10 万个数据（每条数据包含姓名、年龄、身份证号等信息）的表，若没有索引，要想查找姓名为"张三”的身份信息，需要从上到下依次对表中的所有数据进行扫描，找到所有名为张三的数据，这也叫全表查询。

可以看出，全表查询的效率非常低，需要逐条对比，因此就需要通过对每条数据建立索引，从而直接通过索引快速查询到数据信息，大大提高了查询效率。

二、数据库索引的类型及原理

1、B+树索引： 适合范围查询、顺序查询，不适合插入、删除数据，是 InnoDB、MyISAM 的索引方式。

B 树与 B+树都是用于大量数据查询的一种数据结构，二者有以下特点：

B 树：

• 由二叉搜索转变为 N 叉搜索，树的高度大大降低，查询次数少；

• 叶子节点、非叶子节点都可以存储多个数据，每次可以读取一页数据，IO 次数更少；

• 通过中序遍历，可以访问树上所有节点，但需要多次往返各个节点之间，效率有待提升；

由 B 树的特点可知，对于范围查询而言，B 树需要通过中序遍历来进行查找，不够完美，B+树在此基础上进行了改进。

B+树的特点为：

• 依旧为 N 叉树，但是非叶子只保存索引不存储数据，所有数据存储在同一层的叶子节点上，查询性能更稳定；

• 非叶子节点可保存更多索引，相同的数据，B+树的高度更低，查询的 IO 次数更少；

• 所有叶子节点形成一个有序链表，不需要通过中序遍历进行顺序查询，更适合范围查询。

对比可知，B+树最大的优点就是适合范围查询，这在实际应用中是非常广泛的，因此 InnoDB 选用的就是 B+Tree。B+树的缺点就是插入、删除操作非常复杂，一般只用在数据库的查询操作中。

2、哈希索引： 适合单一数据的查找、删除、插入，不适合范围查找，是 Memory 的索引方式。

哈希表的查询、删除、插入的平均时间复杂度都是 O(1)，适合每次只查询一条信息。但是对于需要排序查询（对查询的数据进行排序输出）、范围查询（如：大于或小于某值的范围查询），采用哈希索引的时间复杂度会从 O(1)退化为 O(n)，相当于全表查询，效率极低。总结哈希索引的特点如下：

• 只支持等值比较查询，不支持范围查询；

• 访问数据的速度非常快，但当哈希冲突较多时，查询效率会大大降低；

• 哈希索引数据不是按顺序存储的，即无序的，无法用于排序查询。因此，哈希索引只适用于特定的场合，不要轻易使用。

三、索引分类

1、聚簇索引：叶子节点存储整行数据

按照每张表的主键构造一颗 B+树，叶子节点存放整张表的行数据。每张表只能有一个聚簇索引，如果没有主键，InnoDB 会选择非空的唯一索引代替。如果没有这样的索引，InnoDB 会隐式的定义一个主键来作为聚簇索引。

优点：

• 索引和数据保存在同一颗 B+树中，数据访问更快；

• 聚簇索引对于主键的范围查找速度非常快。

2、非聚簇索引（辅助索引）：叶子节点存储主键值

在聚簇索引之上创建的索引称之为非聚簇索引，其叶子节点存储的数据为主键值，访问数据通常需要二次查找。

假设一张表中有 id（主键），order_id（唯一键）两个字段。若使用“where id=14"的条件进行查询，就会走聚簇索引，直接可以查询出对应行数据；若使用“where order_id= 1100202"的条件进行查询，就会走非聚簇索引，会先根据 order_id 查询到所在行的主键 id，再根据主键 id 走聚簇索引就查到行数据。如下图所示：

回表与覆盖索引

所谓回表查询，就是先通过非聚簇索引定位到主键，再通过聚簇索引定位到数据行。

而索引覆盖则是一种避免回表查询的优化策略，即：一个索引包含了所有需要查询的字段值，查询时直接返回索引的数据，而不需要回表查询。

举例：假设在学生表的年龄上建立了索引，那么当进行 select age from student_table where age < 20 的查询时，在索引的叶子节点上，已经包含了 age 信息，不会再次进行回表查询。

覆盖索引的优点：

• 覆盖索引只需要读取索引，极大减小了数据访问量；

• 避免了回表查询，提高了查询效率。

四、索引失效的情况

五、联合索引最左前缀匹配原则

最左前缀匹配：最左优先，以最左边为起点的任何连续的索引都可以匹配上。遇到范围查询(>、<、between、like)就会停止匹配。

假设有联合索引（a,b,c）：

1. where 条件为（a,b,c）、（b,a,c）、（c,a,b）等，会走联合索引；

2. where 条件为（a）、（a,b）、（a,b,c），会走联合索引；

3. where 条件为（b）、（c）、（b,c），不会走索引，会全表扫描；

4. where 条件为（a,c）时，会走索引，但只使用 a 的索引。

为什么要遵循最左匹配原则

假设有如下 B+树，联合索引为（a,b)：

由图可知，最左边的 a 都是有序的，分别是 ： 1、1、2、2、3、3， 但是右边的 b 不一定有序： 1、2、1、4、3、2。但是在 a 相同的情况下 b 是有序的， 如： a=1 时 b =1，2 ； a=2 时， b= 1，4； a=3 时 ，b=1，2。

因此，在筛选数据的时候， 若直接筛选 b ，整个就是无序的，需要做全表扫描，此时索引失效；若先筛选 a 再筛选 b ，就可以利用 B+树的有序性来加快查找速度。综上，在使用联合索引时需要遵循最左匹配原则。

六、创建索引的原则

1. 遵循最左前缀匹配原则

2. 为频繁作为查询条件的字段创建索引（如：订单 id)

3. 更新频繁的字段不适合创建索引（如：订单状态）

4. 不能有效区分数据或重复值比较多的字段不适合创建索引（如：性别）

5. 尽量拓展索引，不要新建索引。

6. 定义有外键的字段一定要创建索引。

（外键：一个表中存放的另一个表的主键。）

锁

当多个用户对数据库并发操作时，会存在数据读取不一致的问题，造成数据混乱。数据库中锁的作用就是保证数据的一致性，与线程同步含义相同。数据库中的锁分为两大类：悲观锁和乐观锁。

悲观锁（Pessimistic Lock） ：适用于多写的应用类型

总是假设最坏的情况，每次有事务去拿数据时都会觉得别人会修改，所以每次使用时都会给该数据上锁，而其他事务就会阻塞，直到这个事务释放锁把数据转让给下一个用户。

悲观锁按使用性质可划分为以下几类：

1. 共享锁（Share Lock）：也叫读锁（S 锁），允许多个事务对同一数据共享一把锁，都能访问到数据，但 只能读不能修改。

2. 排他锁（Exclusive Lock）：也叫写锁（X 锁），一个事务获取了某数据的排他锁，其它事务就不能获取其它锁，只有获取排他锁的事务能对数据进行读取和修改。（独占式锁）

3. 更新锁：简称 U 锁，在数据修改操作的初始化阶段锁定可能要被修改的资源，从而避免共享锁竞争排他锁造成的死锁现象。

悲观锁按作用范围可划分为：

1. 行锁：锁的作用范围是行级别。对于 UPDATE、INSERT、DELETE 语句，会自动加排他锁。InnoDB 默认采用行锁。 数据库能够确对哪些行进行操作的情况下使用行锁（如使用主键时），如果不知道就使用表锁（不使用主键时）。

行锁的优势：锁的粒度小，发生锁冲突的概率低，并发处理的能力高。

行锁的劣势：开销大，加锁慢。

2、表锁：锁的作用范围是整张表。表锁的优势：开销小，加锁快。表锁的劣势：锁的粒度大，发生锁冲突的概率高，并发处理的能力低。

乐观锁（Optimistic Lock）：适用于多读的应用类型

与悲观锁相反，总是假设最好的情况，每次有事务去拿数据的时候都认为别人不会修改，所以不会给该数据上锁。但在更新的时候会判断在此期间有没有事务更新了该数据。乐观锁两种常见的实现方式：

1. 版本号机制：一般是在数据表中加上一个数据版本号 version 字段，表示数据被修改的次数，当数据被修改时，version 值会加一。当线程 A 要更新数据值时，在读取数据的同时也会读取 version 值，在提交更新时，若刚才读取到的 version 值为当前数据库中的 version 值相等时才更新，否则重试更新操作，直到更新成功。

2. CAS 算法（compare and swap）：一种无锁算法，即在不使用锁的情况下实现多线程之间的变量同步。CAS 采用自旋的模式，会浪费 CPU 资源。原理：通过原子操作来更新数据的值的，比较读取的当前值 V 与当前线程先前取出的值 A 是否一样，若一样表示该值在此期间未被其它线程修改，则更新该值，否则重新从数据表读取数据赋给 A，再进行 V 和 A 的比较，直到更新成功。

数据库事务隔离级别

事务就是访问数据库进行的一组数据操作，所有操作必须成功，否则就会回滚所有操作导致失败。

一、事务的四大特性（ACID）

1. 原子性（Atomicity）： 事务开始后的所有操作要么全部完成，要么全部不完成，不能只完成一部分。事务执行过程中发生错误，会回滚已有操作并恢复到事务开始前的状态。

2. 一致性（Consistency）： 事务开始前和结束后，数据库的完整性没有被破坏。比如：A 向 B 转账 1000 元，A 的账户中会减少 1000 元，而 B 的账户中会增加 1000 元。

3. 隔离性（Isolation）： 多个事务并发执行时，同一时间只允许一个事务请求同一数据，不同的事务之前不会互相干扰。如：A 在从一张银行卡取款的过程中，其他人不能向这张银行卡转账。

4. 持久性（Durability）： 事务完成之后，事务对数据库的所有更改应该保存在数据库中，不能回滚。

二、事务并发的三大问题

1. 脏读： 一个事务读取到了另一个事务未提交的数据。比如：事务 A 读取了事务 B 更新但尚未提交的数据，B 提交失败发生回滚操作，那么 A 读取的数据是脏数据。

2. 不可重复读： 一个事务多次读取同一数据，另一事务在其读取过程中对该数据进行了修改（update 操作）并提交，导致这个事务前后读取的数据结果不一致。

3. 幻读： 一个事务多次读取同一数据，另一事务在其读取过程中对该数据进行了插入或删除（insert 操作）并提交，导致这个事务前后读取的数据结果不一致。

三、事务隔离级别及实现原理

事务隔离是通过加锁来实现的，锁的竞争会带来性能的损失。事务隔离级别分为以下四种：

1. 读未提交（READ UNCOMMITTED）： 不加锁，性能最好，但是无法解决脏读、不可重复读、幻读问题；实现：不加锁，可看作无隔离。

2. 读提交（READ COMMITTED）： 一个事务只能读取其它事务已经提交的数据，但不能解决不可重复读、幻读问题；实现：事务每次操作数据时都会重新生成一次快照，来记录当前数据的版本，在快照时间之前提交的数据版本则可以被读到。（MVCC）

3. 可重复读（REPEATABLE READ）： 一个事务在开始后直到提交前的任意时刻读取的数据都是一样的，不会读到其它事务对已有数据的修改，但可以读取其它事务插入的新数据，即无法解决幻读问题。（mysql 中默认的隔离级别，MVCC）实现：事务开始时生成一个当前事务全局性的快照，后面每次读取的数据都是该次快照的数据版本。

4. 串行化（SERIALIZABLE）： 隔离效果就好，可以解决脏读、不可重复读、幻读问题，但需要加锁，性能较差。实现：一个事务读的时候会加共享锁，其他事务可以并发读，但不能写；该事务写的时候加排它锁，其他事务不能写也不能读。

MVCC（多版本并发控制）实现机制

MVCC 是一种多版本并发控制机制，通过保存数据在某个时间点的快照来实现的。不同的存储引擎的 MVCC 实现是不同的，典型的有乐观（Optimistic）并发控制和悲观（pessimistic）并发控制。

InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间（创建版本号），一个保存行的删除时间（删除版本号）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务版本号，用来和查询到的每行记录的版本号进行比较。保存这两个额外的系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作简单，性能好。不足之处就是每行记录都需要额外的存储空间，需要更多的行检查和维护工作。

在 READ COMMITTED 隔离级别下，一个事务多次输入 SELECT 查找语句时，InnoDB 每次查询时都会生成一个快照，记录当前已提交的数据版本，在这之前的数据版本都可以被读取到。

在 REPEATABLE READ 隔离级别下，一个事务多次输入 SELECT 查找语句时，InnoDB 只在事务开始时生成一个当前事务全局性的快照，每次查找都是读取早于当前事务版本的数据行。

MySQL 数据库性能优化的方法

1. 硬件优化。提升服务器的硬件配置，如 CPU、内存大小等。

2. 数据库调优，如增加索引。

3. 引入缓存，减小数据库压力。

4. 读写分离。增加从库，抗住更多的读请求。

5. 分库分表。单表数据超千万时，考虑分库分表。

MySQL 主从复制，读写分离

一、为什么要主从复制

1. 高可用性： 若主库发生故障，可快速切换到其中一个从库，从而保证系统业务的可用性。

2. 负载均衡： 主库用于写数据，各个从库用于读数据，实现读写分离，将流量分布到各个库上，从而实现负载均衡。

3. 可扩展性好： 当业务量很大的时候，为了抗住更多的读请求，可以增加从库，从而分担流量。

二、主从复制的原理

MySQL 主从复制是一个异步的复制过程，主库发送更新事件到从库，从库读取更新记录，并执行更新记录，使得从库的内容与主库保持一致。

主从复制的流程为：

1. 当主库进行 insert、update、delete 操作时，会按顺序写入到 binlog（二进制日志）中；

2. 从库启动 I/O 线程，跟主库建立客户端连接；

3. 主库启动 binlog dump 线程，读取主库上 binlog 的内容发送给从库的 I/O 线程；

4. 从库的 I/O 线程接收到 binlog 内容后，将内容写入到本地的 relay log（中继日志）；

5. 从库启动 SQL 线程，读取 relay log 的内容，并完成对从库数据的更新。

上图为一个从库的流程，实际中，有 N 个从库，主库就会对应有 N 个 binlog dump 线程，而每个从库都会有自己的 I/O 线程和 SQL 线程。

MySQL 分库分表

一、为什么要分库分表

MySQL 单表最多能存储 5000w 数据，但是单表数据表达 1000w 以后，即使添加从库、优化索引，查询的性能依旧很差。这时候就需要通过分库分表，从而有效减小单台数据库的压力。

二、数据表的两种拆分方式

1、垂直拆分

数据表列的拆分，把一张列比较多的表拆分为多张表，如：把主键和常用的列放一张表，把主键和不常用的列放另一张表。如下图所示：

垂直拆分还有一种理解，即从业务的角度进行拆分，如：一个数据中既存在用户表，又存在订单表，那么就可以把用户表存在用户库，订单表存在订单库中。如下图所示：

优点：使每条数据变小，一个数据块 block 可以存储更多数据，查询时可减小 I/O 次数；

缺点：

1. 主键出现冗余，需要管理冗余列，查询所有数据时需要关联查询 JOIN 操作；

2. 依旧会出现单表数据量过大的情况。

应用场景：数据表中某些列常用，而某些列不常用的情况。

2、水平拆分

数据表行的拆分，数据数量超过千万级别时，数据表的查询效率就会很慢，就可以把一张表的数据按行拆分成多个表来存放。如下图所示：

优点：不存在单表大数据造成的性能瓶颈；

缺点：逻辑复杂，通常查询时需要多个表名；

应用场景：单表数据量达百万级别甚至千万级别。

上图所示为库内分表，仅仅单纯的解决了单一表数据过大的问题，而没有把表的数据分布到不同的机器上，因此对于减轻 MySQL 服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的 IO、CPU、网络，这个就要通过分库来解决，即分库分表。

三、数据表水平拆分的两种方案

水平分表最主要的就是路由算法，即把路由的 key 按照指定的算法进行路由存放。常用的水平分表方案有两种：range 范围路由、hash 路由。

1、range 范围路由：按照数据范围进行拆分数据

range 方案比较简单，就是把一定范围内的订单，存放到一个表中；如上图 id=12 放到 0 表中，id=1300 万的放到 1 表中。设计这个方案时就是前期把表的范围设计好。通过 id 进行路由存放。

优点：数据扩容方便，不需要数据迁移；

缺点：有热点问题，由于 id 的值一般递增的，某段时间的数据会集中在某一张表中，就会导致该表压力过大，而其它表没有压力。（热点问题是指某段时间对数据的操作集中在一个表中，而其他表的操作很少。）

2、hash 路由：指定路由 key 对分表总数进行取模

在设计系统之前，假设未来几年的订单量为 4000 万。每张表我们可以容纳 1000 万，也我们可以设计 4 张表进行存储。

hash 路由的具体方法为：对指定的路由 key（如：id）对分表总数进行取模，上图中，id=12 的订单，对 4 进行取模，也就是会得到 0，那此订单会放到 0 表中。id=13 的订单，取模得到为 1，就会放到 1 表中。为什么对 4 取模，是因为分表总数是 4。

优点：数据可以均匀的放到每张表中，对数据进行操作时，就不会有热点问题。

缺点：若数据量继续增大，需要增加分表数，数据的迁移和扩容，很会麻烦。

四、分库分表方案

上述的两种水平分表方案中，hash 可以解决数据均匀问题，range 可以解决数据迁移问题，因此可以将两者结合在一起，实现分库分表的方案。

实现思路为：先用 range 路由方案让数据落地到一个范围内，这样需要扩容时以前的数据不需要迁移；再在这个范围内，使用 hash 路由方案让数据均匀分配在几个表中，这样就解决了数据热点问题，保证每个表压力一样；最后把这些表分配到几台数据库机器上，实现分库。以上就实现了分库分表。

具体实现，我们一起看一个例子：

假设数据量为 4000 万，定义一个 Group01 组，组内有三个 DB 库，DB_0 中有 4 张表，DB_1 中有 3 张表，DB_2 中有 3 张表。每张表内存储的路由 key（id）的范围如上图所示。这里假设 DB_0 的服务器性能更好，所以存储 4 张表，从而可以存储更多的数据。

存储路由 key 的具体流程为：

扩容的时候，只需要新增加一个 group02 组，而不需要迁移之前的数据。

实际设计的时候，我们只需要维护 group、db、table 的对应关系，就可以将数据存储在对应的表中。如下图所示，图中 table 表字段有些小错误，仅做示例。

实际开发的时候，这三张表可以保存在缓存，而不是 MySQL 中。

C++Linux 服务器/后端开发/架构师面试题、学习资料、教学视频和学习路线图（资料包括 C/C++，Linux，golang 技术，Nginx，ZeroMQ，MySQL，Redis，fastdfs，MongoDB，ZK，流媒体，CDN，P2P，K8S，Docker，TCP/IP，协程，DPDK，ffmpeg 等），免费分享有需要的可以自行添加学习交流群 960994558