mysql的索引原理和数据结构能介绍下吗？
b+树和b树的区别？
mysql聚簇索引和非聚簇索引的区别？
使用mysql索引都有什么原则？
不同的存储引擎是如何进行实际存储的？
mysql的组合索引的结构是什么样的？
什么是回表，索引覆盖，最左匹配原则，索引下推？
mysql索引是如何进行优化的？
二叉树--->avl树--->红黑树--->b树--->b-树--->b+树
索引是帮助mysql高效获取数据的排好序的数据结构，要想弄清楚索引是什么，就必须了接他的底层结构 他的底层结构为b+树
b-树 1，b-树的每个非叶子节点的子节点个数都不会超过D(D就是b-树的阶) 2 所有的叶子节点都在同一层
3 所有节点关键字都是按照递增顺序排列
b+树 1 非叶子节点不存储数据，只进行数据索引。2 所有数据都存储在叶子节点上，3，每个叶子节点都存有相邻叶子节点的指针，4 叶子节点按照本身关键字从大到小排列
上面两个数的区别：b-叶子节点没有指针，所以范围查找慢，数的高度高，b+非叶子节点的分叉越多，总高度越低。
再来说一下索引的存放位置，说到这个，先了接一下计算机的局部性原理： 数据和程序都是由聚集成群的倾向，之前被查询过的数据很可能再次被查询。磁盘预读：在磁盘跟内存进行交互的时候，有一个最小的逻辑单元，称之为页，datapage 页跟操作系统相关，一般是4k或者8k,每次在进行数据交互的时候，一定读取的是页的整数倍。
索引的存储是在磁盘中，但是会先加载到内存中，而且在进行加载的时候是分页加载，
聚簇索引和非聚簇索引底层都是b+树，innodb 存储引擎数据和索引是放在一起的，数据在进行插入的时候必须跟某一个索引列存储在一起，此时的索引列叫做聚簇索引，其他的索引存储的是聚簇索引的key值，在进行数据查找的时候，先从其他索引中找到key值，再通过key去聚簇索引中找到数据。这个过程称之为回表。
innodb 既有聚簇索引也有非聚簇索引
而myisam数据跟索引是分开的，只有非聚簇索引。
回表 索引覆盖 最左匹配 索引下推 假设有一个表 id name ,age,gender id是主键, name是普通索引
回表 select * from table where id = 10; select * from table where name = zeze;
第一次查询name的b+树，根据name 获取到id ,再根据id 去id 的b+数上找到行记录，这个过程称之为回表，效率不高，io的次数会变多。
索引覆盖：select id,name from table where name = zeze;再进行检索的时候，直接根据name 去nameB+数上获取到了id ，name两个列的值，此时不需要回表，效率高，所以应该尽可能的使用索引覆盖来代替回表，所以有时候，在复杂的sql中，可以将不相关的列都设置为索引列。select id,name，age from table where name = zeze; 可以考虑将name，age作为联合索引
最左匹配：
select * from table where name = ?
select * from table where name = ?and age=?
select * from table where age=? and name = ? 有一个组件叫做优化器
select * from table where age = ?
最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。
在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。
就像上面的查询，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。
可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。、
组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。
索引下推： select * from table where name = ?and age=? 在没有索引下推之前，先根据name去存储引擎中拉取符合结果的数据，返回到server层，在server层中对age的条件进行过滤，有了索引下推之后，根据name ,age两个条件直接从存储引擎中拉取结果，不需要再server 层做条件过滤
索引一般都是主键自增且整形 自增是因为让叶子节点不会分裂达到平衡，整形是为了比的更快，如果没有主键自增列，innodb存储引擎就会找到一个不重复的列作为索引，如果找不到，会自己维护一个隐藏的索引列 （生成一个6字节的rowid）来作为索引，组织b+树。
分割的工作单位(构成单一逻辑工作单元的操作集合)。
如果某一事务成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。
如果事务遇到错误且必须取消或回滚，则所有更改均被清除
逻辑架构和存储引擎
MySQL服务器逻辑架构从上往下可以分为三层：
1. 第一层：处理客户端连接、授权认证等。
2. 第二层：服务器层，负责查询语句的解析、优化、缓存以及内置函数的实现、存储过程等。
3. 第三层：存储引擎，负责MySQL中数据的存储和提取。
事务的特性acid 原子性，一致性，隔离性，持久性
InnoDB存储引擎提供了两种事务日志：
redo logg(重做日志)：可以理解是当服务宿机时，重启后强制保持一致
undo log(回滚日志)：可以理解是如果回滚时回滚到之前某一个状态
原子性 是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个
sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。
实现原理：undo log(回滚日志) 在Mysql里数据每次修改前，都首先会把修改之前的数据作为历史保存一份到undo log里面的，数据里
面会记录操作该数据的事务ID。当事务执行失败或调用rollback，导致事务需要回滚，便可以利用undo
log中的信息将数据回滚到修改之前的状态。
undo log 主要分为两种：
1. insert undo log
代表事务在 insert 新记录时产生的 undo log, 只在事务回滚时需要，并且在事务提交后可以被立即
丢弃
2. update undo log
事务在进行 update 或 delete 时产生的 undo log ; 不仅在事务回滚时需要，在快照读时也需要；
START TRANSACTION; SAVEPOINT a1; UPDATE dept SET dept_name='行政部6' WHERE dept_id=60; SAVEPOINT a2; UPDATE dept SET dept_name='行政部7' WHERE dept_id=50; ROLLBACK TO a1;
所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一
清除
3.3 持久性 (Durability)
持久性 也称永久性（Permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性
的。接下来的其他操作或故障不应该对其执行结果有任何影响。
刷脏
InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓
冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放
入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷
新到磁盘中（这一过程称为刷脏）
刷脏引发的问题
Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer
Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。解决这个问题通
过redo log来解决。
实现原理：redo log
重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间纠正不完整事务写入的数据
redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志。
3.4 一致性（Consistency）
事务操作成功后，数据库所处的状态和它的业务规则是一致的，数据库的完整性约束没有被破坏，事务
执行的前后都是合法的数据状态。
数据库的完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段的类
型、大小、长度要符合要求）、外键约束、用户自定义完整性（如转账前后，两个账户余额的和应该不
变）。
3.5 隔离性（Isolation） 隔离性 是指事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。与原子性、
持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响。
隔离性主要考虑最简单的 读操作 和 写操作
隔离性的探讨，主要可以分为两个方面：
(一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性
(一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性
脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。
不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读
幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。
事务的隔离级别
MVCC
MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议，MVCC 的目的就是多版本
并发控制，在数据库中的实现，就是为了解决读写冲突。
当前读
像 select lock in share mode (共享锁), select for update; update; insert; delete (排他锁)这些操作都是
一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修
改当前记录，会对读取的记录进行加锁
快照读
像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串
行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的
实现是基于多版本并发控制，即 MVCC ,可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免
了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有
可能是之前的历史版本
MVCC 是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁
的操作，是悲观锁的实现。
4.2 MVCC实现原理
MVCC实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现.
4.2.1隐式字段:
InnoDB存储引擎在每行数据的后面添加了三个隐藏字段
DB_TRX_ID(6字节)：表示最近一次对本记录行作修改（insert | update）的事务ID。
DB_ROLL_PTR(7字节)：回滚指针，指向当前记录行的undo log信息
DB_ROW_ID(6字节)：随着新行插入而单调递增的行ID。
事务A（事务ID为2）对该记录做出了修改，将Honor列内容改为"fmvp"： 1. 事务A先对该行加排它锁
2. 然后把该行数据拷贝到undo log中，作为旧版本
3. 拷贝完毕后，修改该行的Honor为"fmvp"，并且修改DB_TRX_ID为2（事务A的ID）, 回滚指针指向
拷贝到undo log的旧版本。（然后还会将修改后的最新数据写入redo log） 4. 事务提交，释放排他锁
Read View 是事务进行快照读操作的时候生产的读视图 (Read View)，在该事务执行的快照读的那一
刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID 。
Read View 主要是用来做可见性判断的，把生成的读视图 (Read View)当作条件用来判断当前事务能够
看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数
据。
Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的 DB_TRX_ID（即当前事务 ID
）取出来，与系统当前其他活跃事务的 ID 去对比（由 Read View 维护）。
当每个事务开启时，都会被分配一个 ID , 这个 ID 是递增的，所以最新的事务，ID 值越大
把 Read View 简单的理解成有三个全局属性
m_ids：一个数值列表,用于维护 Read View 生成时刻系统 正活跃的事务 ID 列表
up_limit_id：lower water remark,是 trx_list 列表中事务 ID 最小的 ID
low_limit_id： hight water mark,ReadView 生成时刻系统尚未分配的下一个事务 ID ,也就是目前
已出现过的事务 ID 的最大值 + 1
为什么是 low_limit ? 因为它也是系统此刻可分配的事务 ID 的最小值
假设当前列表里的事务id为[80,100]。
1.如果你要访问的记录版本的事务id为50，比当前列表最小的id80小，那说明这个事务在之前就提交
了，所以对当前活动的事务来说是可访问的。
2.如果你要访问的记录版本的事务id为90,发现此事务在列表id最大值和最小值之间，那就再判断一下是
否在列表内，如果在那就说明此事务还未提交，所以版本不能被访问。如果不在那说明事务已经提交，
所以版本可以被访问。
3.如果你要访问的记录版本的事务id为110，那比事务列表最大id100都大，那说明这个版本是在
ReadView生成之后才发生的，所以不能被访问。
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
