[MySQL5.7] 5.7版本针对临时表的优化

尽量临时表在实际在线场景中很少会去显式使用，但在某些运维场景还是需要到的，在MySQL5.7中，专门针对临时表做了些优化；大概扫了下代码，把一些要点及关键函数记录下。

#独立的tmp表空间，默认在data目录下建立一个命名为ibtmp1的临时表表空间;对于非压缩临时表，表空间内容存放到ibtmp1下面；对于压缩表，依然会为其在tmp目录下建立ibd文件；

#server启动时会删除之前的ibtmp文件并重建(函数srv_open_tmp_tablespace)

#临时表空间使用全局变量srv_tmp_space（5.7里所有对表空间的管理的代码被重构成类Tablespace）

#无需为临时表记录redo log，因为临时表不做crash recovery；因此在针对临时表的大量操作直接disable redo (dict_disable_redo_if_temporary)

#临时表专用的回滚段,回滚段同样被记录到临时表空间ibtmp1下面(trx_sys_create_noredo_rsegs)； 降低和其他实体表的undo log存储产生竞争

尽管临时表无需做crash recovery，但依然要为其创建undo log表空间，因为可能在事务执行的过程中，需要回滚到某个savepoint；

临时表的undo log无需写redo （实体表的undo是需要写redo的）

128个回滚段中的32个回滚段(srv_tmp_undo_logs)专门预留给临时表操作(回滚段trx_rsegs_t被分为两类：m_redo及m_noredo)

针对purge操作，也做了修改，采用一个优先队列来进行操作(具体见函数TrxUndoRsegsIterator::set_next()，  purge_sys->purge_queue, 在函数trx_purge_rseg_get_next_history_log中往队列加undo，创建临时表回滚段调用trx_sys_create_noredo_rsegs)

其他相关函数：trx_sysf_rseg_find_free，查找回滚段（get_next_redo_rseg及get_next_noredo_rseg, trx_assign_rseg_low)

对undo的修改详细见该patch: http://bazaar.launchpad.net/~mysql/mysql-server/5.7/revision/5832

#临时表的定义可以存放在内存，无需持久化到磁盘；

无需将临时表的元数据信息写入到系统表（对于临时表，create_table_def直接调用dict_build_tablespace && dict_table_add_to_cache

创建索引时同样区分对待(row_create_index_for_mysql)

其他相关函数：dict_build_index_def，dict_create_index_tree,dict_recreate_index_tree（include/dict0crea.h），这些函数用于对表元数据进行操作而无需更新系统表

#降低对临时表的锁约束，因为临时表只对当前client可见；

例如无需对更新临时表的二级索引页设置trx id， 不做可见性判断(lock_clust_rec_cons_read_sees, lock_sec_rec_cons_read_sees, lock_sec_rec_read_check_and_lock),不会去加innodb层表锁 (lock_table)

甚至对临时表而言记录锁也是多余的。

#避免对临时表使用change buffer.  另外在ibtmp表空间中的临时表也不为其分配Ibuf  bitmap page(fsp_fill_free_list)

#增加了一个新的information schema表INNODB_TEMP_TABLE_INFO来显示临时表信息

root@test 12:41:54>create temporary table t1 (a int);
Query OK, 0 rows affected (0.00 sec)

root@test 12:42:17>create temporary table t2 (a int) row_format=compressed;
Query OK, 0 rows affected (0.00 sec)

root@test 12:42:26>select * from INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;
+———-+————–+——–+——-+———————-+—————+
| TABLE_ID | NAME         | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+———-+————–+——–+——-+———————-+—————+
|       63 | #sql7e20_7_1 |      4 |    65 | TRUE                 | TRUE          |
|       62 | #sql7e20_7_0 |      4 |    62 | FALSE                | FALSE         |
+———-+————–+——–+——-+———————-+—————+
2 rows in set (0.00 sec)

代码的修改包含在以下几个patch中：

http://bazaar.launchpad.net/~mysql/mysql-server/5.7/revision/5150

http://bazaar.launchpad.net/~mysql/mysql-server/5.7/revision/5456

http://bazaar.launchpad.net/~mysql/mysql-server/5.7/revision/5832

创建ibtmp的diff包含在另外一个大diff中（尼玛完全没法看啊。。。）

http://bazaar.launchpad.net/~mysql/mysql-server/5.7/revision/5112

官方博客上的性能数据:
CREATE/DROP临时表的性能：

临时表的DML性能：

——————–

ref:

官方博客的介绍绍：https://blogs.oracle.com/mysqlinnodb/entry/https_blogs_oracle_com_mysqlinnodb
MySQL5.7.3源代码

 ############

updated @ 2014-11-27，补充下官方相关总结（当前版本：5.7.5）

InnoDB Temporary Table Performance

One of the goals of 5.7 is to optimize InnoDB temp tables for better performance (normal SQL temporary tables). First, we made temp table creation and removal a more light-weight operation by avoiding the unnecessary step of persisting temp table metadata to disk. We moved temp tables to a separate tablespace (WL#6560) so that the recovery process for temp tables becomes a single stateless step by simply re-creating it at start-up. We removed unnecessary persistence for temp tables (WL#6469). Temp tables are only visible within the connection/session in which they were created, and they are bound by the lifetime of the server. We optimized  DML for Temp Tables (WL#6470) by removing unnecessary UNDO and REDO logging, change buffering, and locking. We added anadditional type of UNDO log (WL#6915), one that is not REDO logged and resides in a new separate temp tablespace. These non-redo-logged UNDO logs are not required during recovery and are only used for rollback operations.

Second, we made a special type of temporary tables which we call “intrinsic temporary tables” (WL#7682, WL#6711). An intrinsic temporary table is like a normal temporary table but with relaxed ACID and MVCC semantics. The purpose is to support internal use cases where internal modules such as the optimizer demand light-weight and ultra-fast tables for quick intermediate operations. Finally, we made the optimizer capable of using InnoDB “intrinsic temporary tables” for internal storage (WL#6711). Historically the optimizer has been using MyISAM for storage of internal temporary tables created as part of query execution. Now InnoDB can be used instead, providing better performance in most use-cases. While MyISAM currently remains the default, our intention is to switch to InnoDB Temp Tables as the default