MySQL中的全表扫描和索引树扫描

引言

在学习mysql时，我们经常会使用explain来查看sql查询的索引等优化手段的使用情况。在使用explain时，我们可以观察到，explain的输出有一个很关键的列，它就是 type 属性，type表示的是扫描方式，代表 MySQL 使用了哪种索引类型，不同的索引类型的查询效率是不一样的。

在type这一列，有如下一些可能的选项：

• system：系统表，少量数据，往往不需要进行磁盘IO
• const：常量连接
• eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
• ref：非主键非唯一索引等值扫描
• range：范围扫描
• index：索引树扫描
• ALL：全表扫描(full table scan)

在上面列出的7种选项中，前面五种我就不详细讲了，可以参考 Mysql Explain之type详解 这篇文章。我当时对于前五种属性是比较容易就理解了的，但是对于后面两种即索引树扫描和全表扫描我还是存在一些疑问。

索引树扫描我们是比较熟悉的，它就是会遍历聚簇索引树，底层是一颗B+树，叶子节点存储了所有的实际行数据。其实，全表扫描也是扫描的聚簇索引树，因为聚簇索引树的叶子节点中存储的就是实际数据，只要扫描遍历聚簇索引树就可以得到全表的数据了。

那索引树扫描和全表扫描究竟有什么区别呢？

以下将以一个实例来详细分析这两种扫描方式的区别。

实例

我们建立一张 t_article 表：

create table t_article(
    t_article_id int primary key auto_increment,
    t_title varchar(40),
);

在我们创建的 t_article 表中，只有两个字段，一个是主键 t_article_id ，另一个是普通字段 t_title 。

我们知道，InnoDB会将聚簇索引默认建立在主键上，而聚簇索引树中的叶子节点就存储了整张表的行数据。

接着，我们分别设计两个sql查询case：

1. 走主键索引

   explain SELECT t_article_id FROM t_article;

2. 走全表扫描：

   explain SELECT t_title FROM t_article;

以上两个查询都没有where查询，按理来说底层的sql执行情况应该是差不多的。

结果分析

我们可以来看看上面两种查询的结果，在查询时使用explain语句输出sql执行的详细信息。

1. 走索引扫描

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_article		index		PRIMARY	4		2	100	Using index

1. 走全表扫描

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_article		ALL					2	100

从以上两个查询结果中我们可以发现，走主键索引的查询和走全表的查询是不一样的。我们前面也提到了，InnoDB的索引是使用B+树来实现的，而主键索引中存储了整张表的数据，那全表扫描时其实也是扫描的主键索引。那为什么这两种查询会不一样呢？按理来说都是查询的主键索引，它们应该是一样的。

其实，它们两者是有一些细节区别的。

比如，第一个查询，它的优化手段是使用索引树扫描，也就是type中显示的index属性，而且它还使用了覆盖索引，即Extra列中的Using index属性。之所以第一个查询能够使用这两种优化手段，其实是因为select查询的结果列只包含主键，而主键的值是可以直接在遍历聚簇索引树时确定，也不需要回表查询了。

对于第二个查询，它也没有使用where进行过滤，而且它的select结果列包含的是普通列，并不是主键或者其他索引列，所以它会走全表扫描。而全表扫描其实底层也是扫描的聚簇索引树，也就是底层的B+树。这种全表扫描与索引树扫描有一个明显区别，那就是，全表扫描不仅仅需要扫描索引列，还需要扫描每个索引列中指向的实际数据，这里包含了所有的非索引列数据。

前面的分析可能还是有点生硬和难以理解，具体地，我们通过下面一张图来更直观地看一下：

​

图片源自: 

从上面的图我们可以看到，对于索引扫描来讲，它只需要读取叶子节点的所有key，也就是索引的键，而不需要读取具体的data行数据；而对于全表扫描来说，它无法仅仅通过读取索引列获得需要的数据，还需要读取具体的data数据才能获取select中指定的非索引列的具体值。所以，全表扫描的效率相比于索引树扫描相对较低一点，但是差距不是很大。

1. Mysql中的myisam与innodb的区别？
2. InnoDB存储引擎的四大特性？
3. 什么是事务？
4. 数据库事务的四大特性？
5. 不考虑事务的隔离性，会发生几种问题？
6. MySQL数据库提供的四种隔离级别？
7. 有多少种日志？
8. 事务是如何通过日志来实现的？
9. 数据库的乐观锁和悲观锁是什么？
10. 什么是存储过程？有哪些优缺点？
11. 存储过程与触发器的区别？
12. 索引是什么？有什么作用以及优缺点？
13. 说一说MySQL数据库几个基本的索引类型？
14. 使用索引查询一定能提高查询的性能吗？为什么？
15. 为数据表建立索引的原则有哪些？
16. 什么情况下应不建或少建索引？
17. 什么是mysql联合索引？
18. 说一说 B+树索引、哈希索引？
19. B树和B+树的区别？
20. 为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？
21. 聚集索引和非聚集索引区别?
22. 说一说drop、delete与truncate的区别？
23. drop、delete与truncate分别在什么场景之下使用？
24. 超键、候选键、主键、外键分别是什么？
25. mysql为什么用自增列作为主键？
26. MySQL中的varchar和char的区别以及varchar(50）中的50代表的涵义？
27. 什么是视图？视图的使用场景有哪些？
28. 数据库三大范式？
29. sql优化
30. 非关系型数据库和关系型数据库区别，优势比较?
31. 什么是 内连接、外连接、交叉连接、笛卡尔积等?
32. SQL语言分类
33. like %和-的区别
34. count(*)、count(1)、count(column)的区别
35. 你们数据库是否支持emoji表情，如果不支持，如何操作？
36. 你是如何监控你们的数据库的？你们的慢日志都是怎么查询的？

1、Mysql中的MyISAM与InnoDB的区别？

（1）InnoDB存储引擎支持事务，而MyISAM不支持事务；

（2）InnoDB支持行级锁，而MyISAM只支持表级锁；

（ InnoDB行锁是通过给索引加锁实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表级锁！行级锁在每次获取锁和释放锁的操作需要比表级锁消耗更多的资源。

MySQL表级锁有两种模式：表共享读锁和表独占写锁。就是说对MyIASM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；而对MyISAM表的写操作，会阻塞其他用户对同一表的读和写操作。）

（3）InnoDB支持外键，而MyISAM不支持外键；

（4）InnoDB不保存数据库表中表的具体行数，而MyISAM会保存；

（ 也就是说，执行 select count(*) from table 时，InnoDB要扫描一遍整个表来计算有多少行，而MyISAM只需要读出保存好的行数即可（内部维护了一个计算器，可以直接调取）。【注】：当count(*)语句包含where条件时，两种表的操作是一样的。也就是上述介绍到的InnoDB使用表锁的一种情况。）

对于select ,update ,insert ,delete 操作：

如果执行大量的SELECT，MyISAM是更好的选择（因为MyISAM不支持事务，使得MySQL可以提供高速存储和检索，以及全文搜索能力）；

如果执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表（因为InnoDB支持事务，在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了）。

2、InnoDB存储引擎的四大特性？

插入缓冲、二次写、自适应哈希索引、预读

（1）插入缓冲：

一般情况下，主键是行唯一的标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。因为，对于此类情况下的插入，速度还是非常快的。

如果索引是非聚集的且不唯一，在进行插入操作时，数据的存放对于非聚集索引叶子节点的插入不是顺序的，这时需要离散地访问非聚集索引页，由于随机读取的存在而导致了插入操作性能下降。（这是因为B+树的特性决定了非聚集索引插入的离散性。）

插入缓冲对于非聚集索引的插入和更新操作，不是每一次直接插入索引页中，而是先判断插入的非聚集索引页是否在缓存池中。如果在，则直接插入；如果不在，则先放入一个插入缓冲区中，好似欺骗数据库这个非聚集的索引已经插入到叶子结点了，然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对非聚集索引执行插入和修改操作的性能。

插入缓冲的使用要满足两个条件：

• 索引是辅助索引
• 索引不是唯一的
  • （辅助索引不能是唯一的，因为在把它插入到插入缓冲时，我们并不去查找索引页的情况。如果去查找肯定又会出现离散读的情况，插入缓冲就失去了意义。）

存在的问题：

在写密集的情况下，插入缓冲会过多的占用缓冲池内存，默认情况下最大可以占用1/2的缓冲池内存。

（2）二次写

当数据库宕机时，可能发生数据库正在写一个页面，而这个页只写了一部分的情况，我们称之为部分写失效。当写入失效发生时，先通过页的副本来还原该页，再重做日志，这就是两次写。

doublewrite步骤：

1. 当一系列机制（main函数触发、checkpoint等）触发数据缓冲池中的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页拷贝到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次、每次1MB顺序写入共享表空间的物理磁盘上。
2. 然后马上调用fsync函数，同步脏页进磁盘。在这个过程中，doublewrite页的存储是连续的，因此写入磁盘为顺序写，性能很高在完成doublewrite页的写入后，再将doublewrite buffer中的页写入到各个表空间文件中，此时的写入则是离散的。

如果操作系统在将页写入磁盘的过程中崩溃了，在恢复过程中，InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本，将其拷贝到表空间文件，再应用重做日志，就完成了恢复过程。因为有副本所以也不担心表空间中数据页是否损坏。

（3）自适应哈希索引

InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称为自适应的。自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快，而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

（4）预读

InnoDB 提供了两种预读的方式，一种是 Linear read ahead，由参数innodb_read_ahead_threshold控制，当你连续读取一个 extent 的 threshold 个 page 的时候，会触发下一个 extent 64个page的预读。另外一种是Random read-ahead，由参数innodb_random_read_ahead控制，当你连续读取设定的数量的page后，会触发读取这个extent的剩余page。

InnoDB 的预读功能是使用后台线程异步完成的。

3. InnoDB如何保证事务的四大特性？

MySQL的存储引擎InnoDB使用重做日志(redo log)保证一致性与持久性，回滚日志(undo log)保证原子性，使用各种锁来保证隔离性。

4. MySQL中的重做日志（redo log），回滚日志（undo log），以及二进制日志（binlog）？

MySQL中有六种日志文件，分别是：

复制代码

1 2 3 4 5 6 7 8 9

重做日志（redo log） 回滚日志（undo log） 二进制日志（binlog） 错误日志（errorlog） 慢查询日志（slow query log） 一般查询日志（general log） 中继日志（relay log） 其中重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系。

事务是如何通过日志来实现的？

Undo 记录某 数据 被修改 前 的值，可以用来在事务失败时进行 rollback；
Redo 记录某 数据块 被修改 后 的值，可以用来恢复未写入 data file 的已成功事务更新的数据。
即，

• Redo Log 保证事务的持久性
• Undo Log 保证事务的原子性（在 InnoDB 引擎中，还用 Undo Log 来实现 MVCC）

比如某一时刻数据库 DOWN 机了，有两个事务，一个事务已经提交，另一个事务正在处理。数据库重启的时候就要根据日志进行前滚及回滚，把已提交事务的更改写到数据文件，未提交事务的更改恢复到事务开始前的状态。即 通过 redo log 将所有已经在存储引擎内部提交的事务应用 redo log 恢复， 所有已经 prepared 但是没有 commit 的事务将会应用 undo log 做回滚。

重做日志（redo log）：

redo log在事务没有提交前，会记录每一个修改操作变更后的数据。主要是防止在发生故障的时间点，尚有脏页未写入磁盘。在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。（作用）

在事务提交前，只要将 Redo Log 持久化即可，不需要将数据持久化。当系统崩溃时，系统可以根据redo Log的内容，将所有数据恢复到最新的状态。（持久化：先将重做日志写入缓存，再刷新(fsync)到磁盘）

重做日志是物理日志，记录的是对于每个页的修改。事务开始后Innodb存储引擎先将重做日志写入缓存（innodb_log_buffer）中。然后会通过以下三种方式将innodb日志缓冲区的日志刷新到磁盘。

1. Master Thread每秒一次执行刷新Innodb_log_buffer到重做日志文件。
2. 每个事务提交时会将重做日志刷新到重做日志文件。
3. 当重做日志缓存可用空间少于一半时，重做日志缓存被刷新到重做日志文件

当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化。

1、内容：

      物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。

2、redo log是什么时候写盘的？

是在事物开始之后逐步写盘的。

事务开始之后就产生redo log，redo log的写盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。（先将重做日志写入缓存，将日志缓冲区的日志刷新到磁盘，写入磁盘的方式有上面3种）

【注】即使某个事务还没有提交，Innodb存储引擎仍然每秒会将重做日志缓存刷新到重做日志文件。这一点是必须要知道的，因为这可以很好地解释再大的事务的提交（commit）的时间也是很短暂的。

3、什么时候释放：

      当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的