MySQL索引与架构详解：聚簇与非聚簇、事务隔离、锁机制-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_45971439/article/details/124430919

MySQL

1. MySQL整体架构
1. 索引的基本原理
2. 聚簇索引和非聚簇索引的区别
3. 索引结构以及各种的优势
4. 索引的设计原则
5. MySQL的锁
6. MySQL的执行计划
7. 事务的基本特性和隔离级别
8. MVCC
9. 主从同步
10. MyISAM和InnoDB的区别
11. 回表，索引覆盖，最左匹配

1. MySQL整体架构

MySQL的整体架构
请添加图片描述 MySQL的日志系统

binlog：运行主从复制（Server层）
redolog：预写日志（InnoDB）
undolog：事务回滚和MVCC（InnoDB）
relaylog：中继日志（Server）
slowlog：慢日志（Server）
errorlog：错误日志（Server）

1. 索引的基本原理

索引用来快速的寻找那些具有特定值的记录，如果没有索引，一般来说执行查询时遍历整张表。常用的查询算法有顺序查找，二分查找，二叉排序树查找，Hash散列法，分块查找，B树

将无序的数据查询变成有序的查询

索引查询快的原理：通过B+树缩小扫描访问范围，底层索引进行了排序和分区（并且是双向链表），索引会携带数据在表中的物理地址，最终通过索引检索到数据之后，获取相关联的物理地址，通过物理地址直接定位到表中的数据，查询效率高

2. 聚簇索引和非聚簇索引的区别

都是B+树的数据结构

MySQL的索引类型与存储引擎是相关的，InnoDB存储数据和索引文件全部放在ibd文件中，而MyISAM的数据文件存放在myd文件中，索引文件存放在myi文件中

数据和索引是否是存储在一起的？是则为聚簇索引，一个索引对应一棵B+树，当一个表存在多个索引时，数据存储只有一份

聚簇索引：叶子节点存储的是数据和索引，并且按照一定的顺序组织的，找到了索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的。只要索引是相邻的，那么对应的数据一定也是相邻存放在磁盘上的（适合用来进行范围查询）
非聚簇索引：叶子结点不存放数据只存放索引，存储的是数据的行地址，根据索引查找到数据行的位置再去磁盘查找数据，类似书的目录，在目录中找到所需要的页码（索引）再去对应的页码查看文章（数据）

聚簇索引的优点：

查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下，只需要查索引不需要查数据比如查找某个表的id，对应主码索引）效率要高
聚簇索引对象范围查询的效率很高，由于其数据是按照大小排列的
聚簇索引适用于排序的场合，非聚簇索引不适合

聚簇索引的缺点：

维护索引代价很高，特别是插入行或者主码被更新导致需要分页，建议在大量插入新行后，选择在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片，使用独享表空间可以弱化碎片
表因为使用UUID作为主码，使得数据存储比较稀疏，这就会出现聚簇索引有可能比全表扫描更慢，所以建议使用int的AUTO_INCREMENT自增作为主码
如果主码比较大的话，那么辅助索引将会变得更大，因为辅助索引的叶子存储的是主码值；过长的主码值会导致非叶子节点占用更多的物理空间

数据库引擎中索引的区别：

InnoDB中一定有主码，主码一定是聚簇索引，不手动设置则默认会使用UNIQUE索引，没有UNIQUE索引则会使用数据库内部的一个行的隐藏id来作为主码索引。在聚簇索引基础上创建的索引即为辅助索引，辅助索引访问数据都是需要二次查找，非聚簇索引都是辅助索引，比如复合索引，前缀索引，唯一索引，辅助索引的叶子节点存储的不再是行的物理地址而是主码值
MyISAM：使用的是非聚簇索引没有聚簇索引，非聚簇索引的两个B+树看上去没什么区别，节点的结构完全一致但是存储的内容不同，主码索引B+树的叶节点存储了行地址，而辅助索引B+树的叶子结点存储的是主键值。两个B+树的叶子结点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有区别，由于索引树是独立的，通过辅助键检索不需要访问主键的索引树
InnoDB适用场景：innoDB存储引擎在进行数据插入时，数据值必须要跟某一个索引字段绑定在一起，如果表中有主键则使用主键。如果没有主键，那么选择唯一建，如果还是没有唯一键那么系统会自动生成一个6字节的rowId供数据使用（既有聚簇索引也有非聚簇索引）

如果创建索引的键是其他字段，那么在叶子结点中存储的是该记录的主键，然后再通过主键索引找到对应的记录，称之为回表

1）经常更新的表，适合处理多重并发的更新请求。

2）支持事务。

3）可以从灾难中恢复（通过 bin-log 日志等）。

4）外键约束，只有他支持外键。

5）支持自动增加列属性 auto_increment
MyISAM使用场景：MyISAM存储引擎的索引存储的是行地址（只有非聚簇索引，索引的叶子结点存储的是聚簇索引的key值）

1）MyISAM 执行读取操作的速度很快，而且不占用大量的内存和存储资源

2）不支持事务和行级锁，也不支持外键

3）涉及到大量数据的排序，全表扫描，count之类的操作还是MyISAM有优势，由于索引占用空间小

3. 索引结构以及各种的优势

B+树索引：B+树是一个平衡的多叉树，从根节点到每个叶子结点的高度差不超过1，而且同级别的节点之间有指针相互连接，在B+树上的常规检索，从根节点到叶子结点的搜索效率基本相当。并且叶子节点存储的索引是有序排列的并且是一个双向链表结构，因此对于范围性的查询非常适用，InnoDb默认使用的就是B+树索引

Hash索引：hash索引适使用hash算法，将key值映射成新的hash值，检索时不需要像B+树一样从根节点一直查询到叶子节点，对于单条记录的查询或者等值查询较快，但不适用于范围性的查询，没法利用索引进行排序，也不能完成模糊查询，不支持联合索引的最左匹配原则，Memory默认使用的是Hash索引

MySQL索引体系如何应对海量数据存储?

索引其实是存放在硬盘中的，防止断点或者系统宕机之后出现数据丢失。当查询需要读取索引时，从硬盘中加载索引到内存，由于索引可能很大，不能一次性全部加载到内存中，需要进行分块读取，这就涉及到磁盘预读

磁盘预读？

磁盘跟内存进行交互的时候有一个最基本的逻辑单位，叫做dataPage页，大小与OS有关一般是4k或者8k。每次进行数据读取时读取的是页的整数倍大小，使用命令==show variables like ‘innodb_page_size’;==查看innoDB默认读取的页大小是16Kb数据

为什么选择B+树作为索引的数据结构？

可选的数据结构有：Hash表，二叉搜索树，二叉树，红黑树，AVL数，B+树

使用Hash表作为索引的优缺点：可以将数据散列，查找速度快；容易出现hash碰撞（HashMap中计算hash值的扰动函数(h = key.hashCode()) ^ (h >>> 16))，由于低位参与运算的重合度是比较高的，所以要进行左移，让更多的高位参与运算，得出的hash值更加散列）；无法进行范围性的查询

Hash索引适用于Memory存储引擎，InnoDB存储引擎支持自适应Hash
不使用二叉树，AVL树，红黑树的原因：树中的每个节点只能存储一个数据，当数据增加时，不得不扩充二叉树的层，这样从磁盘加载索引的IO次数会增多，访问磁盘的次数增多，查询也就慢了
B树（degree=3）索引：每个节点占用一个磁盘块，一个节点上有两个升序排序的关键字和三个指向子节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键字可以划分成三个范围域对应三个指针指向的子树数据的范围。每个节点都会存储数据和地址，造成数据冗余，存储的索引数据较少，读取IO次数多
B+树索引：每个节点可以包含更多的节点，主要是为了降低树的高度，将数据范围变成多个区间，区间越多，数据检索越快。

非叶子节点只存储key，叶子节点存储key和数据；叶子节点之间存在指针形成一个双向链表（符合磁盘的预读性），顺序查询性能更高

这里还是以度=3作为例子，B+树上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有的叶子结点（数据节点）之间还是一种链式环结构，因此可以对B+树进行两种查找运算：一种是对于主键的范围查询和分页查找；另一种是从根节点开始进行随机查找

一般情况下，3-4层的B+树索引可以支持千万级别的数据（key值占用的空间越小越好）

ID在满足业务场景的需要下，需要自增吗？

索引维护，在数据迁移时，先把索引关闭掉，等待数据迁移完毕，再重新建立索引。不使用ID自增，插入新节点时可能会导致叶分裂过程，需要进行指针的维护工作，进行自增后只需要在尾部直接添加即可，不会影响之前的索引结构

4. 索引的设计原则

目标：查询更快，占用空间小

适合索引的列是出现在where子句中的列
表较小时，没必要建立索引
使用短索引，对于长字符串的列进行索引可以指定一个前缀长度，节省磁盘空间
定义有外键的数据列一定要建立索引
不要过度建立索引。在修改表的内容时，索引会进行更新甚至重构，索引列越多，时间越长
更新频繁的字段不适合创建索引，尽量选择区分度高的列作为索引
不能有效区分数据的列不适合建立索引，如性别
尽量的扩展索引，不要新建索引
查询很少的列，重复值比较多的列不建立索引

索引类型：

唯一索引：保证数据记录的一致性，主键是一种特殊的唯一索引，一个表中只能定一个主键索引
联合索引：索引可以覆盖多个数据列
全文索引：用于进行模糊查询
普通索引：允许被索引的数据列包含重复的值

5. MySQL的锁

基于锁的属性划分：共享锁S锁和排他锁X锁
基于锁的粒度划分：行级锁（InnoDB），表级锁（InnoDB，MyISAM），页级锁，记录锁，间隙锁，临键锁
基于锁的状态划分：意向共享锁，意向排他锁

共享锁S锁：当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能加写锁，为了支持并发读取数据，读取数据不支持修改，避免出现重复读和读脏数据问题
排它锁X锁：当一个事务为数据加上X锁之后，其他事务都不能对该数据加锁。避免出现读脏数据问题
表锁：上锁的时候是锁的整个表，粒度大容易冲突
行锁：上锁的是表中的某一行或者多行记录，只有被锁住的记录不能访问，其他的可以正常访问。粒度小不容器冲突，相比表锁的并发性高
记录锁：也是行锁的一种，锁的仅仅是一条记录，并且命中的字段是唯一索引。避免数据在查询时被修改的重复读问题，避免在修改的事务未提交之前被其他事务读取到引发脏读问题
页锁：介于表锁和行锁之间，页锁与行锁一样，有可能出现死锁
间隙锁：属于行锁的一种，锁住的是表记录的某一个区间，当表的相邻id之间出现空隙形成一个区间，遵循左开右闭原则。查询条件必须命中索引，间隙锁只会出现在REPEATABLE_READ(重复读)的事务级别中，用来解决幻读
意向排他和意向共享锁：当一个事务试图对整个表进行加锁之前，首先需要获取这个表的意向X锁或者意向S锁

6. MySQL的执行计划

请添加图片描述 id：查询的序列号，表示查询中select子句或者操作表的顺序。id号分为三种情况：

如果id相同，那么执行顺序从上到下
如果id不同，子查询中id值越大优先级越高
id相同和不同的同时存在，相同的id认为是一组，从上往下顺序执行。在所有组中，id值越大，优先级越高，越先执行

select_type：查询的类型，普通查询还是联合查询或者子查询

table：正在访问的是哪一个表，为具体的表名或者表的别名，从实际的物理表中获取数据。derivedN，使用id为N的查询产生的衍生表。

type：system，const，ref，range，index，all（从左到右扫描的范围逐渐变大，执行效率依次降低）

possible_keys：可能用到的字段是哪一个

key：实际用到的索引是哪一个，如果为null则没有使用索引，如果使用了覆盖索引，则该索引和查询的select字段重叠

key_len：索引使用的字节数，可计算查询中使用的索引长度，在不损失精度的前提下长度越短越好

ref：显示索引的哪一列被使用了，一般为常数

rows：查询预估的数据量，需要读取的行数，直接反映了sql查询的查找数据，完成目的的前提下越少越好

7. 事务的基本特性和隔离级别

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行事务是一个不可分割的工作逻辑单元

事务必须具备以下四个属性，简称 ACID 属性：

原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行
一致性（Consistency）：当事务完成时，数据必须处于一致状态
隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性

隔离性有4个隔离级别：

read uncommit：读未提交。可能会读取到其他事务未提交的数据，也就是脏读
read commit：读已提交。是Oracle的默认级别，两次读取到的结果不一致，也就是不可重复读，解决了脏读的问题，只会读取已经提交的事务
repeatable read：可重复读。是MySQL的默认级别，每次读取到的结果都是一致的，但是有可能产生幻读（对于新增操作，ReadView可能会读取到新增加的数据，可以使用间隙锁来解决）
serializable：串行化。给每一行读取的数据进行加锁，可以解决幻读问题，但是会导致大量超时时间和锁竞争的问题，并发性较差

ACID靠什么来保证？

原子性由undo log（InnoDB）日志来保证，记录了需要回滚的日志信息，事务撤销已经执行成功的sql
一致性由其他三个特性来保证，程序代码要保证业务上的一致性
隔离性由MVCC来保证
持久性由内存+redo log来保证，MySQL修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log中恢复

怎么处理慢查询？

产生原因：查询条件没有命中索引；load了不需要的数据列；数据量太大
解决方法：

开启慢查询日志，准确定位到哪个sql语句出现了问题
分析语句，分析是否加载了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行重写
分析语句的执行计划，然后获得使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引
对语句优化无法进行，可以考虑表中的数据量是否太大，可以进行横向或者纵向的分表

8. MVCC

多版本并发控制：读取数据时通过一种类似快照的方式将数据保存下来，读锁和写锁不产生冲突。MVCC只会在read committed和repeatable read两个隔离级别工作，由于read uncommitted总是读取到最新的数据行，不符合当前事务版本的数据行；而serializable会对所有的数据行进行加锁，也就不存在并发的意义了

已提交读和可重复读的区别在于他们生成的ReadView的策略不同

开始事务时创建readView，readview维护当前活动的事务（未提交的事务）id，排序生成一个数组
访问数据时，获取数据中的事务id（获取事务id最大的记录也就是最新的这条记录），去对比readview
如果事务id小于readview的最小值，可以访问，表示当前事务是已经提交的
如果事务id大于readview的最大值，表示当前事务是没有提交的，不可以访问。取该条数据的上一个版本（由于聚簇索引存在两个隐藏列，一个是事务id，一个是版本链指向上一个版本的该数据）再一次进行对比
对于read commited级别的事务每次查询都会产生一个独立的readview，因此每次比对的结果都不一样，这就是为什么不可重复读的原因；对于repeatable read级别的事务只是在第一次读取的时候生成一个readview，之后的读取都是复用之前的readview，因此可以解决不可重复读的问题。

9. 主从同步

主从同步：数据可以从一个MySQL数据库服务器的主节点复制到一个或多个从节点，默认使用异步同步复制，从节点不需要一直访问主节点来更新自己的数据，数据的更新可以在远程连接上进行。

主要由三个线程：Master主节点binlog dump线程，Salve包括IO线程和SQL线程。

binlog日志：当数据库启动时就会创建，保存所有对数据库修改的一个文件
log dump线程：当主库的binlog日志发生变化，会开启log dump线程读取其中的内容并发送给slave节点
slave IO线程：从库的IO线程接受binlog内容，并且写到relay log中继日志文件中
slave SQL线程：从库的SQL线程读取relay log文件，并解析成sql语句逐一执行，保证主从节点的数据一致性

MySQL默认是异步同步复制：主数据库将日志发送从库，不关心从库是否已近处理完成，当主库宕机，从库升级为主库时，之前的日志就丢失了

全同步复制：主库写入binlog后强制同步日志到从库，并且从库全部执行完成后才返回给客户端

半同步复制：从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就可以认为从库的写操作完成

10. MyISAM和InnoDB的区别

MyISAM

不支持事务，每次查询都是原子的
支持表级锁，每次操作都是对整个表进行加锁
一个MyISAM有三个文件：索引文件，数据文件，表结构文件
采用的是非聚簇索引，索引文件存储的是指向数据文件的指针
没有计数器

InnoDB

支持事务，以及四种隔离级别
支持行级锁，可以支持写并发
主键索引采用聚簇索引，索引数据存放的就是数据本身，辅助索引存储的是主键值；从辅助索引中查找到主键值，再从主键索引中查找数据
支持外键索引
有计数器

11. 回表，索引覆盖，最左匹配

回表：表中有字段id，name，age，gender和address。id为主键，name是普通索引，查询语句

select * from table where name='***';

查询过程：

先根据name字段查找匹配的数据，name上的B+树存储的是id的值，需要再根据id去查询id所在的B+树，至少需要两次查找索引才能找到全部的数据，这个过程叫做回表。发生回表时查询效率低，尽量避免回表

索引覆盖：例子与上述相同，查询语句为

select id,name from tabel where name='***';

查询过程：

直接根据name的值去查询name对应的索引，通过该B+树上的叶子结点可以直接获取到id和name，不需要回表，这个过程叫做索引覆盖，查询效率较高。

索引下推：对于下面的sql语句

select t1.name, t2.name from t1 join t2 on t1.id = t2.id;

两种执行方式：

先根据id进表的关联，然后获取到所有的关联数据之后再去查询name的值
先根据sql语句中需要查询的字段和主键筛选出来，再根据id进行表的关联，返回结果（谓词下推，查询效率高）

下面sql中，id为主键，name和age为组合索引

select * from table where name='***' and age = **;

在没有索引下推之前，先根据name从存储引擎中拉取所有的数据到server层，再从server层中根据age去做数据过滤

引入索引下推之后，直接根据name，age两个值从存储引擎中做数据筛选，返回结果（在server层的数据筛选放到存储引擎中执行，这一过程是索引下推，在MySQL5.7版本之后默认是开启的index_condition_pushdown）

最左匹配：针对于组合索引name和age，下面4条sql语句中

select * from table where name =? and age=?;
select * from table where name =?;
select * from table where age =?;
select * from table where age =? and name=?;