从mysql执行顺序讲到存储引擎再讲到临时表

从mysql执行顺序讲到存储引擎再讲到临时表

学习mysql的系统设计思想和性能特点

参考：

https://zhuanlan.zhihu.com/p/126144198 MySQL临时表
https://www.cnblogs.com/duanxz/p/3724120.html MySQL中的两种临时表
https://www.jianshu.com/p/d4cc0ea9d097 MySQL InnoDB存储引擎大观
https://www.jianshu.com/p/134f548720bd 【译】从草稿开始写一个 MySQL 存储引擎(一)
https://github.com/mysql/mysql-server/blob/8.0/sql/handler.h MySQL的github库

sql的执行顺序

FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT
→ ORDER BY→ LIMIT

这个查询执行顺序和算法有关

from读取原数据

Join则是对源数据进行关联输出（如果是非笛卡尔链接还会伴随着ON条件关联判断）

输入和输出完成后，下一步就是进行修饰了。

涉及WHERE条件过滤，GROUP BY的分组索引（类似字典树聚合）,和HAVING过滤.

ORDER BY排序（如果无法用上索引，排序算法必然用上临时表，为什么？因为数据通常大于内存，用不上快排这种无内存开销的算法）

LIMIT 仅仅只对返回的ResultSet行数进行了限制而已。

再讲讲JOIN，JOIN是否产生临时表呢？

答案是不会的，因为他JOIN后生成表和输出表是同一张，JOIN中生成表最大的是cross，也就是笛卡尔积，其他表都是一遍关联一遍作判断，不会输出到生成表，除非采用where这种条件关联，他会先生成大的生成表，之后再进行过滤，这种明显更费内存空间。

Mysql的临时表，其实跟是否在内存是无关的?

临时表存放的位置是在内存还是在硬盘取决于你使用的存储引擎，而存储引起的根本差别在于IO性能。其功能和结构对于SQL管理系统来说没有本质差别，毕竟他们都遵守了存储引擎规范。

再说说为什么需要临时表？

• 就拿 Union 和 Union all 说起，Union all 不会生成临时表，而union生成临时表。

• 为什么？

• 首先，明确，临时表是一种存储结构，它只能为算法服务，查询是一种存储算法。它为了实现某一输出结果而不得不用上的一种存储结构。

• union all，我们知道它输入的是n张表，可以理解为n个集合，输出的是一个集合，中间的过程只是将n个集合的按照抽象集合定义（列数一样，列类型一样，列顺序一样，列名一样）进行合并输出。
  这个过程非常简单，简单到不需要进行聚合，去重，排序。

• 而union,它对输出结构多了一个约束，就是不允许出现重复值。这就等于你不能直接合并输出，你需要进行去重，关于对集合去重的算法，你找找看哪个算法可以做到对大表去重不用申请额外内存开销，没有！没有！没有！所以特定的需求，造就了特定的算法，最终造就了必要的内存开销----临时表。

• union的做法和我们大多数带主键数据表一样，就是默认对临时表创建主键索引，然后将输入表的行依次插入，每次插入都会进行索引查重，最后将结果输出。你可以理解为这里的主键索引表也是一种临时表。

• 根据经验，Mysql中有哪些操作会产生索引表呢?

  如果不确定可以通过explain，查看看到use temporary便是了。

从临时表的生命周期思考临时表类型。

• 临时表，在执行某一些特定查询时便会产生，之后随着会话结束（客户端断开应用连接）后被存储引擎回收。

• 说到这里得出一个结论，临时表和会话线程捆绑，决定了它正常情况下的scope是线程级别的。即对线程本地可见。

• 其次就是它是既然寄托于存储引擎，也必将意味着它是存储引擎实现的一个组件。

• 有几个疑问？
  1.临时表有多少种类型呢？
  2.他们各自的生命周期是怎样的？
  3.为什么而服务？
  4.存储引擎的架构是怎样的呢？
  5.临时表在其中是怎么进行规划的呢？

为了解答上述问题，我们先假定最初只有一种临时表，其生命周期从查询时创建直到会话结束删除，用于优化查询。

然后先搞懂Mysql的架构及其存储引擎的架构。再慢慢回归到临时表的问题上。

Mysql存储引擎架构规范与Innodb的实现。

• 存储引擎规范实际上来源一个堆mysql系统保留的扩展口handler。
  源码来源：
  https://github.com/mysql/mysql-server/blob/8.0/sql/handler.h

比如下面就是我们熟悉的DML接口

 -------------------------------------------------------------------------
  MODULE change record
  -------------------------------------------------------------------------
  This part of the handler interface is used to change the records
  after INSERT, DELETE, UPDATE, REPLACE method calls but also other
  special meta-data operations as ALTER TABLE, LOAD DATA, TRUNCATE.
  These methods are used for insert (write_row), update (update_row)
  and delete (delete_row). All methods to change data always work on
  one row at a time. update_row and delete_row also contains the old
  row.
  delete_all_rows will delete all rows in the table in one call as a
  special optimization for DELETE from table;
  Bulk inserts are supported if all underlying handlers support it.
  start_bulk_insert and end_bulk_insert is called before and after a
  number of calls to write_row.
  Methods:
    write_row()
    update_row()
    delete_row()
    delete_all_rows()
    start_bulk_insert()
    end_bulk_insert()

当然还有DDL以及相关索引优化接口。

具体都在这个handler.h文件下定义。

• Innodb的实现

从总体上，innodb分为两大部分，内存架构和磁盘架构。
在内存架构上，innodb将内存划分为两个部分：bufferpool和log buffer.
其中bufferpool主要是用来缓存磁盘里的索引和热点的表数据，以此减少堆磁盘进行低效IO。

The buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed.

其次就是这个bufferpool是以页为单元从磁盘读取缓存，这也符合操作系统按页置换的习惯。

实际上bufferpool为了实现高效的磁盘缓存算法，所有操作会缓存完成再异步同步到磁盘上。为此，就有了一个用于存储[change log]的changebuffer,所有的change请求会先记录到changebuffer,等磁盘异步load页数据到缓存bufferpool后，再将changelog的记录merge到bufferpool。然后再由purge操作落盘。

这个算法有两个特点，一个是异步，另一个是全程在内存完成。其根本起因是索引缺页，而这种事情通常发生在非主键的二级索引，主键索引是有序而且聚簇的。

这里顺便提一下，聚簇索引可以等价认为是数据存储的分布的规则。

另外，对于bufferpool这个大容器，也有辅助搜索用的索引，对这种随机性分布的数据，当然是用Adaptive Hash Index这种哈希索引最为高效了。由于它的热度足够高，值得我们为其建立昂贵的哈希索引。

• log buffer 是什么？
  我们知道，innodb有三个核心的模型，log（redolog和undolog）、索引数据、表数据。其中索引数据和表数据放在bufferpool,那么log自然就是放在logbuffer了，你可以认为logbuffer就是磁盘中innodblog的内存映射。

The log buffer is the memory area that holds data to be written to the log files on disk.

• 再讲讲索引，总所周知，innodb的数据是聚簇在主键索引上的，而主键索引采用的是B+树，那么我们是否可以认为innodb里一切皆是Index索引呢？
  自信点说，答案是肯定的！如果应用在区分一下的话，聚簇索引可以认为就是表数据，而二级索引才是我们常说的表索引。

同理，索引的本质就是表table,那我们也一样可以自信的说，mysql一切皆是表table,也是可以成立的。
如果按照表来划分，我们可以分为： 系统表\File-Per-Table(独占文件的表)\普通表（非独占文件业务表）\undo表（历史记录表）\临时表。这些表我们会划分到各个不同的表空间中，所以他们不是逻辑上的分类，而是按照存储方式和生命周期划分的分类概念。

• 说说undo log和redolog,在开启事务后，innodb就会将回滚点写入到undolog,当需要回滚的时候就会执行undolog.而redolog.则是为了提高数据写入的原子性，将数据在redolog完成读写操作后在一次性同步到表中。

• 说说脏页恢复，我们知道在不考虑事务的情况下， 系统落盘页数据的时候奔溃就会造成脏页，希望重启后恢复脏页就不得不在写入页之前对磁盘页进行临时备份了，这个临时备份存放在doublewrite buffer。

总结

上面讲了这么多核心就三个，理解Mysql的2个数据模型，log, index与表
理解innodb的缓存策略，merge和purge异步缓存策略，和undolog、redolog、doublewritelog日志读写策略。

---

继续讲讲临时表以及它的生命周期

我们现在知道了，临时表其实就是一种索引表。
它是为了实现特点查询时产生，比如子查询，groupby ， orderby, union。
它有自己的表空间在磁盘里，对于内存的bufferpool来说，他只是普通的表。

用户可以自己创建临时表用来加速查询，这种临时表依然是临时表，它会在会话结束后被系统回收，跟其他临时表一样的宿命。用户创建的临时表叫外部临时表，叫用户临时表会比较亲切点吧。

我们可以像创建普通表一样为他指定存储引擎,innodb?myisam? 或者是memory?

其中memory引擎也脚heap引擎。

提这个heap存储引擎的目的是为了讲讲，
系统内部自动创建用于辅助查询的临时表默认用memory引擎，如果memory引擎的存储总量超过了MYSQL预设的MAX_HEAP_TABLE_SIZE值，后续创建的临时表将会转而采用INTERNAL_TMP_DISK_STORAGE_ENGINE指定的存储引擎，要么是innodb，要么是myisam。

不管怎么临时表仍然是普通表，只不过它的默认引起是memory罢了、并且它的生命周期和用户的会话周期一样罢了。

至于什么时候被创建？我们可以认为需要辅助查询的时候自然会用到，而且大多是系统默认会创建。很多CRUD Boy很少会意识到去创建临时表的，因为大多数场景都很少会想到用它。