mysql全方位解析

​ 写这篇文章缘起于对mysql相关的知识一直反复的看、反复的忘，不总结下来写篇文章真不行。

1 当前主流数据库的储存方式

数据的存储方式是数据库的核心，以下四种数据库加起来可以至少占一多半的使用场景（本篇文章重点讲解Mysql，列出其他数据库是为了进行对比）。

  1. Mysql

    Mysql为开源的关系型数据库。我们一般常用的都是innodb存储引擎。

    在innodb存储引擎中，表数据储存方式为B+树（聚簇索引），每张表都必须有一个聚簇索引（非聚簇索引是我们平时在表中添加的普通索引，也称二级索引），在创建表时，如果表有主键则选择主键，如果没有主键则选择一个唯一索引，如果都没有，使用行号当做聚簇索引。

  2. Hive

    Hive的数据是存放于HDFS上的（就是普通文件），对于分区的概念则是用目录进行区分。

    其基本的数据存放格式类似于CSV之类的，可以在创建表的时候指定列、行分隔符进行区分。

    同时也提供了几种复杂存储方式如TextFile，RCFile，SequenceFile，AVRO，ORC和Parquet格式

    Hive本身不提供索引（可以使用Impala的MPP方式进行加速查询）。

  3. MangoDB

    Mango由于使用了OS的虚拟内存，所以与Mysql相比，其读写速度更快。

    MangoDB使用B树作为索引结构，因为MangoDB不是传统的关系数据库，其本身存储的就是文档，对于范围查询和关联查询的需求近乎等于无，所以使用B树作为索引能够提升查询速度。

  4. Hbase

    Hbase是分布式的列式数据库，适合存储半结构化的数据，因其面向于大型数据集，所以底层储存依赖了HDFS。既然依赖HDFS，那不用问，在物理上数据肯定是以文件形式储存在HDFS上的，在HBase中叫做HFile。

    因其是个分布式的数据库，由HMaster管理众多的RegionServer(典型的中心化应用)，所以就用到了ZK帮忙实现选举和HA。

    其内存存储上，使用了LSM树（Log-Structured-Merge-Tree），LSM相对于B树最大的不同是损耗了读性能而极大的提升了写性能。因此Hbase自身只提供了对于rowKey的索引，没有CF之类的索引，使用phoenix或者kylin可以对Hbase预先创建索引加速查询。

2 常用索引类型（B树、B+树）

除了内存数据库以外的所有数据库，都是将数据保存到外存上的（一般指硬盘），大家都知道，磁盘寻址时间和内存寻址比不是一个量级的，所以为了提升查询数据速度，减少磁盘IO，数据库都提供了索引方式来加快数据访问。

基本上学过数据结构的都会学习到二叉平衡树，其查询的时间复杂度为O(log2N)，但在数据量大的情况下，磁盘IO次数依旧不是一个小数目，所以一般数据库都使用B树及其变种，因为B树是多路平衡树，能够显著降低磁盘IO次数（比较次数没有减少）。

2.1 B-树

B树肯定也是树形结构的，首先放定义（百度百科过来的）

  1. 每一个节点最多有 m 个子节点（m 表示为B树的阶）

  2. 每一个非叶子节点（除根节点）最少有 ⌈m/2⌉ 个子节点

  3. 如果根节点不是叶子节点，那么它至少有两个子节点

  4. 有 k 个子节点的非叶子节点拥有 k − 1 个键

  5. 所有的叶子节点都在同一层

    从网上拷的一张图，以字母的顺序为例构造的B树

    b-tree

2.2 B+树

一开始同样先放定义

  1. 有n棵子树的非叶子结点中含有n个关键字（b树是n-1个），这些关键字不保存数据，只用来索引，所有数据都保存在叶子节点（b树是每个关键字都保存数据）。

  2. 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

  3. 所有的非叶子结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

  4. 通常在b+树上有两个头指针，一个指向根结点，一个指向关键字最小的叶子结点。

  5. 同一个数字会在不同节点中重复出现，根节点的最大元素就是b+树的最大元素。

    同样从网上拷个B+树的图看一看

    b+tree

从定义可以看出与B树明显的几个区别：1.数据只存放在叶子节点上（在一个磁盘块中就能容纳更多的子节点，这也是数据库不常用B树的重要原因）2.叶子节点有指针相连，方便范围查询。3.必须要查询到叶子节点才能得到数据地址（这也是查询稳定的由来）

2.3 LSM树

对于磁盘来说，随机读写和顺序读写性能差别极大（甚至能达到三个数量级），因为磁盘的读和写操作，都需要磁头和磁盘臂的移动来实现，顺序读写无疑移动的距离最小。因此LSM树使用了这个优点，数据写入磁盘上时为顺序写入，大幅提升了写入速度，但是因此也付出了代价，其读性能相对偏弱。

以Hbase为例，Hbase对于数据的插入、删除、修改操作都先保存在内存中，这是也称作memtable，同时为了防止数据丢失，在写数据时会通过预写日志（WAL）先写入磁盘。随着对数据操作的增多，memtable也越来越庞大，就会触发合并批量保存到磁盘，从而大幅度提升写性能。

但是读取就相当于麻烦了，首先查询内存中的LSM树，如果内存中没有命中，那么很悲剧，就要遍历查询磁盘上的所有文件了，当然如果是根据rowKey查询，可以得知磁盘上存储的offset（与KafaKa的思想很类似），从而直接取得数据。

3 Mysql的索引结构

innodb存储引擎中，有聚簇索引和非聚簇索引之分（都是B+树），一开始也提到过，这里细讲一下。

  1. 聚簇索引

    如果以主键为聚簇索引，则索引中每个节点值为主键值（这也是为啥建议使用有序值当主键，因为有序情况下，插入数据不会导致索引大更新），叶子节点上为具体的主键所在行数据。

  2. 非聚簇索引

    非聚簇索引与聚簇索引只有一个区别，非聚簇索引叶子节点上的数据为聚簇索引的索引值，简单来讲，如果根据聚簇索引条件查询，能够直接得到行数据，而根据非聚簇索引条件查询，要先查出主键值，再拿主键值从聚簇索引中查出相应行数据（也就是回表查询），但是要注意，如果你只根据非聚簇索引查询主键值或者索引过的值，就不用回表了。

MyisAm存储引擎中则只存在聚簇索引，叶子节点存的是具体的物理地址。

索引使用注意事项：

  1. 一条sql语句只会使用一个索引。

  2. 对于联合索引，满足最左匹配原则。

  3. 不要创建过多索引，索引会占用磁盘空间并影响写入性能。

  4. 如果索引列参与了函数计算，索引会失效。

  5. 全模糊查询无法使用索引。

常问考点:

  1. 为什么在关系数据库中，常常使用B+树呢？因为其所有的叶子节点有指针相连，我们翻阅一下自己平时的业务代码也能看出，有很多情况都是范围查询，比如 where status > 10，这时候直接就能查询到相邻数据，而不用再返回上层节点再查询。
  2. 为什么Mysql使用聚簇索引+非聚簇索引？当发生数据行移动或者页分裂时，辅助索引树不需要更新，因为辅助索引树存储的是主索引的主键关键字，而不是数据具体的物理地址。

4 Mysql的查询流程

先放一张图

sql-flow-chat

该图反映了一条sql具体的执行流程

  1. mysql服务端收到sql后，根据sql的hash值，查询有无对应缓存，如果有缓存则直接返回（但是对于线上应用一般都会把缓存给禁掉）。

  2. 根据sql构造解析树，进行语法解析、验证。

  3. 将解析树转变为查询计划，可能一条sql会转变为多条查询计划，通过查询优化器选取并优化出最优的一条，最优一条称为执行计划。

  4. 调用存储引擎查询相应的数据返回。

5 Mysql的事务相关

关于事务隔离级别可以说面试已经问烂了。。

mysql提供的事务隔离级别有四种分别为，默认为第三种可重复读

  1. ru 未提交读

    当事务A没有提交时，其他事务仍能感知到事务A的修改。

    该隔离级别会产生脏读，脏读就是本事务中读取了其他事务尚未提交的数据。

  2. rc 提交读

    当事务A提交后，其他事务才能感知到事务A的变化

    该隔离级别会产生不可重复读，不可重复读就是本事务中读取了其他事务已提交的数据。

  3. rr 可重复读

    当事务A提交后，其他事务只有在提交事务后才能感知到事务A的变化

    该隔离级别会产生幻读，幻读就是本事务中对满足条件的数据进行修改了，但是由于其他事务插入了同样满足的数据，在本事务中由于无法感知，所以没有修改掉其他事务新增的数据。

  4. 串行化

    最严格的级别，事务不允许并行执行，也就不存在任何并发问题了，但是会急剧影响读写性能。

mysql的事务传播机制有三种

  1. propagation_required

    默认的传播范围，如果当前存在事务，就沿用当前事务，否则新建一个事务运行方法。

  2. propagation_required_new

    无论当前事务是否存在，都会创建新事务运行方法（完全独立的事务）

  3. propagation_nested

    创建一个嵌套事务，如果有外部事务存在，本事务为外部事务的子事务，当外部事务提交时，本事务才被提交。当本事务失败回滚，外部事务可以选择捕获异常不回滚外部事务，也可以选择不捕获一起回滚。

    如果外部事务不存在，则就是新建的独立事务。

mysql为了尽可能的提高性能，使用MVCC（多版本并发控制，主要解决读写冲突），可以让多个事务之间实现读写无锁。

通常在我们业务代码中，对于并发状况，多数情况下采用加锁强行串行化处理，但是串行化同样会阻塞读的执行，所以mysql为了提高性能，让每个事务在同一时刻，看到都是数据库的一份快照。每个事务处理的数据都是在自己的快照中处理的，这样就不会影响到别的事务，当事务提交后，mysql会将该数据的其他快照置为无效状态。这样就实现了读写并行且不用加锁目的。

6 Mysql锁机制

按照锁的大类型分，mysql中，锁可分为两种

  1. 共享锁 s锁，读锁，其他事务只能读

  2. 排他锁 x锁 ，写锁，加锁事务可读可写，阻塞其他读写

InnoDB对于UPDATE、DELETE、INSERT语句自动加写锁，对于读取数据不加锁，但也可使用下列语句明确加锁

1
2
3
4
# 共享锁（S）
SELECT * FROM table_name WHERE .... LOCK IN SHARE MODE
# 排他锁（X）
SELECT * FROM table_name WHERE .... FOR UPDATE.

MyisAm在写时自动加写锁，在读时加读锁

按照加锁的粒度分，可分为三种（在MyisAm引擎中只有表锁）

  1. 行锁 在InnoDB中行锁是对索引加的，如果表中不存在任何索引，行锁升级为表锁
  2. 表锁 在InnoDB中AUTOCOMMIT设为0，否则ＭySQL不会给表加锁，同时释放表锁时，会连同事务一起提交。
  3. 页锁

业务中使用Mysql的乐观锁

个人是非常不建议在mysql使用乐观锁的（如果只有小概率的高并发场景下还可以接受），先讲一下什么是乐观锁。

在我们平时的业务中，对于增减余额、库存等修改操作时，对表增加version字段，在更改语句中增加对version的判断（有点类似于CAS，增加条件version = 查询的version），如果update语句返回0，说明有并发修改，需要进行重试。

使用乐观锁时有以下几个缺点

  1. 当并发操作比较多的时候，就比如说秒杀减库存这种场景，因为竞争比较激烈，你的sql有可能要重试非常非常多的次数才能够成功执行，严重影响程序性能和增加mysql的压力，当然对于减库存的这种操作，可以直接使用 update set number = number + #{number} where number > 0，使用数据库提供的事务隔离来保证并发写。
  2. 无法保证请求的顺序性，有可能前几个更新操作都检测到冲突了，正好被后面的更新请求捡漏子了，如果强行保证顺序性，只能在应用层面上去保证请求的请求顺序。

数据库扩展知识1

在数据库中，对于不同表的连接方式可分为三种

对于表A和表B两张表，join的字段为Key

  1. Hash join

    选取表数据较小的一个放到内存中，对所有行的字段key进行hash计算，将算出的结果放到hash桶中（如果内存够的话就在内存中），接着对另一张表的数据字段key进行同样的hash计算，将每行的hash结果，查找相应桶中的数据进行精确比较，如果相同，则进行链接。

    mysql中in查询就使用的hash join，所以in的数据范围不能太大，否则会影响性能（内存不够放就要转移到磁盘上），并且因为底层是hash算法，不能保证有序性，因此查询结果会乱序。

  2. Nested loop join

    跟其名称一样，嵌套循环，就是一个双重循环，第一层循环遍历表A，第二层循环遍历表B（如果有索引会走索引），判断key字段是否相等。

    mysql中exist查询使用的是Nested loop join。

  3. Merge join

    有一定的限制，会要求表A和表B的数据的key是有序的，如果强制使用，会先排序。

    他其实跟一个很经典的两个有序数组的排序问题相同，从表A和表B的key字段的第一行数据开始分别遍历，如果表A的key<表B的key，则表A移动到下一行，否则表B移动到下一行。

数据库扩展知识2–join优化

对于sql中的join查询，在mysql中使用的Nested loop join方式进行连接左右表，并且，mysql的查询优化器做了很多工作来判断选用那张表当做驱动表（外层循环），对于两张表的所有情况可分为以下三种

前提条件：A表和B表进行链接查询，条件为A.a1 = B.b1

  1. A表的a1字段有索引，B表的b1字段无索引，则会选用无索引的B表当驱动表
  2. 两张表的a1字段、b1字段都没有索引，则选取行数少的那一张表当驱动表
  3. 两张表的a1字段、b1字段都有索引，则会选取总循环次数较少的一种情况

为什么会出现这种优化，因为如果当一张表有索引，那么Nested loop join则可升级为Index Nested loop join，在循环中可以使用索引树来进行快速查询，那么总查询次数就不再是N*M了，而是变成N*logM了（M和N为两张表的行数，下同），但是作为驱动表，则无法享受索引带来的便利，必须要全部循环，所以使用有索引的表当做被驱动表可以显著减少总循环次数，因此，对join字段加索引，会提升join的查询效率。

如果两张表都没有索引，那么根据Nested loop join算法，就是双重循环，总循环次数为N*M，这会是个很庞大的数字，假如两张表分别有1W条数据，总循环次数就是1亿次！显然要做优化，mysql使用了join_buffer的策略来对这种情况进行优化，也就是先将驱动表的一部分放到内存中，再让被驱动表和join_buffer中的数据进行比较，由于是内存操作和顺序读，能够显著提升整个算法的性能。但是毕竟内存空间是有限的，join_buffer也会受到空间限制，默认的大小是256K，但是可以对该值进行修改，也可以提升join查询的性能。