索引分类

我们可以按照四个角度来分类索引。

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引） 。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。

什么是回表（主键索引，二级索引）

主键索引和二级索引的b+tree区别:主键的叶子节点放了实际数据，二级索引的树存了主键值

所谓回表就是用二级索引查询，会先找到主键值，再用主键去主键的b+tree去找数据

不过，当查询的数据是主键值时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作「索引覆盖」，也就是只需要查一个 B+ 树就能找到数据

联合索引-最左匹配原则

通过将多个字段组合成一个索引，该索引就被称为联合索引

联合索引查询的 B+Tree 是先按 product_no 进行排序，然后再 product_no 相同的情况再按 name 字段排序。因此，使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

加入我建立一个联合索引（a，b）

a是全局有序的，b在a相等的情况下是局部有序的。所以在查询的时候一定要带上a，进行联合索引才能查到想要的值。

联合索引的最左匹配原则，在遇到范围查询（>、<、between、like 包括like '林%'这种模糊查询）的时候，就会停止匹配

mysql架构分层

• server层：负责建立连接，分析和执行sql
• 存储引擎层 ：负责数据储存和提取

执行一条 select 语句，期间发生了什么？ | 小林coding (xiaolincoding.com)

mysql执行流程（select）？

1. 连接器：mysql基于tcp传输协议，首先进行tcp三次握手，密码登录，连接器获取用户权限
2. 查询缓存：收到sql语句先去解析，如果是select字段，mysql会先去查询缓存，缓存key为sql语句，val为查询结果 ----但是对于更新频繁的表查询缓存很鸡肋，8.0之后删掉
3. 解析sql： 对sql做解析
4. 执行sql：
   • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
   • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
   • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

索引 --- 数据的目录

• 索引的定义就是帮助存储引擎InnoDB快速获取数据的一种数据结构

防止索引失效

用上了索引并不意味着查询的时候会使用到索引，所以我们心里要清楚有哪些情况会导致索引失效，从而避免写出索引失效的查询语句，否则这样的查询效率是很低的。

我之前写过索引失效的文章，想详细了解的可以去看这篇文章：谁还没碰过索引失效呢?(opens new window)

这里简单说一下，发生索引失效的情况：

• 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
• 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

失效查看--explain

• possible_keys 字段表示可能用到的索引；
• key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
• key_len 表示索引的长度；
• rows 表示扫描的数据行数。
• Extra
• type 表示数据扫描类型，我们需要重点看这个。

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

• All（全表扫描--最坏）；
• index（全索引扫描--最坏）；
• range（索引范围扫描）；
• ref（非唯一索引扫描）；
• eq_ref（唯一索引扫描）；
• const（结果只有一条的主键或唯一索引扫描）。

b+树

• B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的

查3

B+树的优势：

\

1.单一节点存储更多的元素，使得查询的IO次数更少。

\

2.所有查询都要查找到叶子节点，查询性能稳定。

\

3.所有叶子节点形成有序链表，便于范围查询。

mysql为什么用b+树作为索引

由于数据库的索引是保存到磁盘上的，因此当我们通过索引查找某行数据的时候，就需要先从磁盘读取索引到内存，再通过索引从磁盘中找到某行数据，然后读入到内存，也就是说查询过程中会发生多次磁盘 I/O，而磁盘 I/O 次数越多，所消耗的时间也就越大。

所以，我们希望索引的数据结构能在尽可能少的磁盘的 I/O 操作中完成查询工作，因为磁盘 I/O 操作越少，所消耗的时间也就越小。mysql支持范围查找

所以，要设计一个适合 MySQL 索引的数据结构，至少满足以下要求：

• 能在尽可能少的磁盘的 I/O 操作中完成查询工作；
• 要能高效地查询某一个记录，也要能高效地执行范围查找；

InnoDB储存引擎

InnoDB 的数据是按「数据页」为单位来读写的，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。数据页默认16 kb，在创建表时，InnoDB 存储引擎默认会创建一个主键索引，也就是聚簇索引，其它索引都属于二级索引。 数据页包括：

count

• 性能比较：count(*) = count(1) > count(主键字段)>count（字段）

• count() 是一个聚合函数,是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。

• count(1)表示不为nil的有几个

• 举例，有无二级索引的 select count(id) from t_order;

• count(*) == count(0)

• 如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些。 就是不要使用 count(字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数，建议给这个字段建立一个二级索引

• 优化 ：

1.近似值

2.维护一个额外表保存计数值

事务

事务的特点

• 原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，而且事务在执行过程中发生错误，会被回滚到事务开始前的状态

• 一致性： 事务操作前和操作后，数据满足完整性约束（比如，用户 A 和用户 B 在银行分别有 800 元和 600 元，总共 1400 元，用户 A 给用户 B 转账 200 元，分为两个步骤，从 A 的账户扣除 200 元和对 B 的账户增加 200 元。一致性就是要求上述步骤操作后，最后的结果是用户 A 还有 600 元，用户 B 有 800 元，总共 1400 元，而不会出现用户 A 扣除了 200 元，但用户 B 未增加的情况（该情况，用户 A 和 B 均为 600 元，总共 1200 元）

• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力

• 持久性：事务结束后，修改永久

并行事务引发什么？

可能出现脏读，不可重复读，幻读

脏读

• 如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。

（假设有 A 和 B 这两个事务同时在处理，事务 A 先开始从数据库中读取小林的余额数据，然后再执行更新操作，如果此时事务 A 还没有提交事务，而此时正好事务 B 也从数据库中读取小林的余额数据，那么事务 B 读取到的余额数据是刚才事务 A 更新后的数据，即使没有提交事务。因为事务 A 是还没提交事务的，也就是它随时可能发生回滚操作，如果在上面这种情况事务 A 发生了回滚，那么事务 B 刚才得到的数据就是过期的数据，这种现象就被称为脏读。）

不可重复读

• 在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。

幻读

• 在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。

总结

• 脏读：读到其他事务未提交的数据；
• 不可重复读：前后读取的数据不一致；
• 幻读：前后读取的记录数量不一致。

对一致性产生影响

严重性

• 脏读 > 不可重复读 > 幻读

事务的隔离级别--4个

• 读未提交（倒一）（read uncommitted） ，指一个事务还没提交时，它做的变更就能被其他事务看到；
• 读提交（倒二）（read committed） ，指一个事务提交之后，它做的变更才能被其他事务看到；
• 可重复读（倒三）（RR） ，指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
• 串行化（最高）（serializable ） ；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；（但是性能差）

InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它通过next-key lock 锁（行锁和间隙锁的组合）来锁住记录之间的“间隙”和记录本身，防止其他事务在这个记录之间插入新的记录，这样就避免了幻读现象。

如何实现隔离级别

• 对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
• 对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
• 对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。 「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View**。

mvcc

多版本并发控制，通过「版本链」来控制并发事务访问同一个记录时的行为

• 允许多个版本同时存在，并发执行。
• 不依赖锁机制，性能高。
• 只在读已提交和可重复读的事务隔离级别下工作。

MVCCC是“维持一个数据的多个版本，使读写操作没有冲突”的一个抽象概念。

解决问题如下：

• 并发读-写时：可以做到读操作不阻塞写操作，同时写操作也不会阻塞读操作。
• 解决脏读、幻读、不可重复读等事务隔离问题，但不能解决上面的写-写 更新丢失问题。

因此有了下面提高并发性能的组合拳：

• MVCC + 悲观锁：MVCC解决读写冲突，悲观锁解决写写冲突
• MVCC + 乐观锁：MVCC解决读写冲突，乐观锁解决写写冲突

可重复读如何工作

• 可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View
• read view 四个字段：当前事务id，rv创建时活跃未提交的id，list种最小id，创建rv时当前数据库应该给下一个事物的id

• 事务A创建时生成一个rv。此时存在一系列活跃未提交事务。当事务A去读取这条数据时，会看他的隐藏列的事务id，如果这个值小于rv中的最小事务id，说明这个数据在rv创建前已经提交，所以对A可见
• 如果在rv的list中的事务对数据进行修改但是还没有提交，这个时候mysql会记录相应的undo log，并用链表串起来，，形成版本链，事务A再去读取的时候，发现这个数据的事务id在min-max之间，并且在活跃list中，所以事务A不会读取，沿着版本链往下找，一直到事务id小于rv的最小值
• 如果list中的事务提交，，由于可重复读，事务a读取的时候，基于创建时刻的rv来判断当前版本是否可见。所以哪怕是你提交了还是不能读取。。
• 如果在min-max之间，但是不在list里，就可见

读提交怎么实现

• 事务在每次读数据的时候，会重新创建 Read View，
• 假设事务A读取数据创建一个rv，先读取有一个数据，然后list中一个事务把数据修改但是没有提交，A再去读取的时候，创建新的rv，发现这条事物的id已经更新，并且在rv list中，说明这个事务是活跃事务还没有提交，所以不读，沿着版本链往下找
• 在这个修改事务提交了之后，a在读取的时候创建rv，rv的list里就不包含这个修改事务了，斌并且版本链上的第一个事务id小于新rv的最小事务id，所以这条数据可见。

锁

全局锁

• 使用后，整个数据库处于只读，备份数据库数据的时候用
• 为了避免影响业务，可以采用可重复读的隔离级别，在备份是数据库之前开启事务，创建rv，整个事务期间使用这个rv，由于mvcc的支持，备份期间业务还可以对数据更新。

表级锁

表锁

• 表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。
• 颗粒度大，影响并发

元数据锁（MDL）

• 对表操作的时候，自动加MDL

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁；

• 对一张表做结构变更操作的时候，加的是 MDL 写锁；

• 申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

• 防止堵塞，可以考虑kill掉之前的加了读锁的长事务

意向锁

• 意向锁的目的是为了快速判断表里是否有记录被加锁。

• 当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

• 而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

AUTO-INC锁

• 在为某个字段声明 AUTO_INCREMENT 属性时，之后可以在插入数据时，可以不指定该字段的值，数据库会自动给该字段赋值递增的值，这主要是通过 AUTO-INC 锁实现的。
• 锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。

行级锁

• innodb支持
• 记录所：把一条记录锁上
• 间隙锁：锁定一个范围，但不包含记录本身
• next-key lock：记录+间隙，锁定一个范围，并且锁定记录本身

如何加锁 - 行级锁

死锁

• 可重复读隔离级别下，是存在幻读的问题。

• Innodb 引擎为了解决「可重复读」隔离级别下的幻读问题，就引出了 next-key 锁，它是记录锁和间隙锁的组合。

日志

• undo log（回滚日志） ：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
• redo log（重做日志） ：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
• binlog （归档日志） ：是 Server 层生成的日志，主要用于数据备份和主从复制；

为什么需要buffer pool

• MySQL 的数据都是存在磁盘中的，那么我们要更新一条记录的时候，得先要从磁盘读取该记录，然后在内存中修改这条记录，为了下次查询方便，先写到缓存中----innodb设计了缓冲池（Buffer Pool） ，来提高数据库的读写性能。

• 读：如果缓存池中有需要的数据，直接读，没有再去磁盘
• 写：如果数据在缓存池，修改缓存中大的页。因为了磁盘数据不一样了，所以把他当成脏页。为了减少此怕啊io，会找一个合适的时机，把脏页写入磁盘

buffer pool缓存什么

Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。

为什么需要redo log

• 防止掉电，缓存池里面的数据丢失

• WAL （Write-Ahead Logging）技术，指的是 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上。

• 在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

• redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；

• undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

redo log什么时候刷盘

• MySQL 正常关闭时；
• 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
• InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
• 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制，下面会说）。

binglog

• MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写 入 binlog 文件。

• binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；

• redo log 是 Innodb 存储引擎实现的日志；