MySQL select in 语句未使用索引，产生磁盘临时表，导致 crash

1. 问题描述

业务执行一个非常简单的 SQL，结果导致 MySQL crash，并且每次都能复现。

MySQL 版本：
Percona Server for MySQL 5.7.20

SQL 语句：
select * from a where a.name in (select distinct name from b) limit 1000;

a 表 和 b 表 各包含 150w 条数据。

表结构：

CREATE TABLE `a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1500001 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `b` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1500001 DEFAULT CHARSET=utf8mb4;


MySQL 错误日志：

stack_bottom = 7f6b0423ec08 thread_stack 0x80000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x2c)[0xec198c]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x461)[0x799e11]
/lib64/libpthread.so.0(+0xf370)[0x7f74d2669370]
/usr/local/mysql/bin/mysqld(_Z16actual_key_partsPK6st_key+0xb)[0xcde51b]
/usr/local/mysql/bin/mysqld(_Z17calculate_key_lenP5TABLEjm+0x23)[0x800c63]
/usr/local/mysql/bin/mysqld(_ZN7handler14index_read_mapEPhPKhm16ha_rkey_function+0x2c)[0x8044cc]
/usr/local/mysql/bin/mysqld(_ZN7handler17ha_index_read_mapEPhPKhm16ha_rkey_function+0x218)[0x8025c8]
/usr/local/mysql/bin/mysqld(_Z23check_unique_constraintP5TABLE+0x78)[0xc6add8]
/usr/local/mysql/bin/mysqld[0xc6b58d]
/usr/local/mysql/bin/mysqld[0xc6b225]
/usr/local/mysql/bin/mysqld(_Z10sub_selectP4JOINP7QEP_TABb+0x18c)[0xc7071c]
/usr/local/mysql/bin/mysqld(_Z25join_materialize_semijoinP7QEP_TAB+0x7c)[0xc70d1c]
/usr/local/mysql/bin/mysqld(_ZN7QEP_TAB12prepare_scanEv+0x42)[0xc699b2]
/usr/local/mysql/bin/mysqld(_Z10sub_selectP4JOINP7QEP_TABb+0x40)[0xc705d0]
/usr/local/mysql/bin/mysqld(_ZN4JOIN4execEv+0x237)[0xc69497]
/usr/local/mysql/bin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x17d)[0xcd920d]
/usr/local/mysql/bin/mysqld[0x75826b]
/usr/local/mysql/bin/mysqld(_Z21mysql_execute_commandP3THDb+0x4816)[0xc9c7f6]
/usr/local/mysql/bin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x5d5)[0xc9fa25]
/usr/local/mysql/bin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0xaca)[0xca057a]
/usr/local/mysql/bin/mysqld(_Z10do_commandP3THD+0x1b7)[0xca1fc7]
/usr/local/mysql/bin/mysqld(_Z26threadpool_process_requestP3THD+0xc7)[0xd46fe7]
/usr/local/mysql/bin/mysqld[0xd5654e]
/usr/local/mysql/bin/mysqld(pfs_spawn_thread+0x1b4)[0xeda334]
/lib64/libpthread.so.0(+0x7dc5)[0x7f74d2661dc5]
/lib64/libc.so.6(clone+0x6d)[0x7f74d083573d]


2. 原因分析

通过分析 mysql 进程 crash 时产生的 core file，发现是在一个磁盘临时表中发生了crash，/mysql/data/_tmp/#sql_28ed_0。

通过测试其他版本，发现 5.7.21 不会 crash。对比了 5.7.20 与 5.7.21 版本该 SQL 的执行计划，主要区别在于 5.7.21 对子查询使用了 auto_key，而 5.7.20 版本没有。怀疑可能与 SQL 未走索引有关。在 a 表的 name 字段增加索引，再次执行 SQL，一切正常，未发生宕机。

alter table a add index idx_name(name);

另外，如果减少 a，b 两张表的数据量，比如记录数减少到 1000，执行同样的 SQL，也不会发生宕机。

总结一下：

1. a 表在 name 字段增加索引，不会宕机
2. a，b 表减少数据量，不会宕机
3. 升级 MySQL 版本到 5.7.21，不会宕机

以上 3 个场景，都会导致执行计划发生变化，结合 gdb 调试 core 文件，crash 发生在磁盘临时文件中，因此怀疑执行计划中使用的临时表，具体来说是使用了磁盘临时表，最终导致 MySQL crash。

通过搜索 MySQL 5.7.20 以上版本的 Release Note，发现在 5.7.22 版本修复的一个 Bug 与本次问题非常类似，如下：

When an on-disk temporary table was created from an in-memory temporary table, the indexes remained uninitialized for the new on-disk table. (Bug #88601, Bug #27214153)

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-22.html

该 Bug 的修复代码：

https://github.com/mysql/mysql-server/commit/ba01814af79b24f7bd313923f769f0a21eff6378

简单来说，就是一个内存临时表由于占用了太大的空间，需要转为磁盘临时表，磁盘临时表的索引没有被正确的初始化，最终导致 crash。

将该提交的代码合并到 5.7.20，编译，测试，发现 MySQL 还是 crash 了，只不过这次 crash 的地方不一样。

继续排查，发现另外一个提交：

https://github.com/mysql/mysql-server/commit/7f95ec4dd61232af7afa72f3becac91d94fb8468

将该提交合入 5.7.20 代码，编译，测试，问题解决。

总结：该 Bug 需要 2 个提交才能彻底解决，但即使修复之后，该SQL性能也不好，还是需要增加合适的索引，才能解决性能问题。

3. 解决方案

1. 业务表增加合适的索引，避免产生临时表，同时也能加快 SQL 执行效率。
2. 升级 MySQL 版本到 5.7.22 及以上，MySQL 会自动给临时表增加 auto_key，能够避免 crash。当然也可以既升级 MySQL 版本，也给表加上合适的索引。