1. 問題描述

業(yè)務(wù)執(zhí)行一個非常簡單的 SQL，結(jié)果導致 MySQL crash，并且每次都能復現(xiàn)。

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 條數(shù)據(jù)。

表結(jié)構(gòu)：

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?時產(chǎn)生的 core file，發(fā)現(xiàn)是在一個磁盤臨時表中發(fā)生了crash，/mysql/data/_tmp/#sql_28ed_0。

通過測試其他版本，發(fā)現(xiàn) 5.7.21?不會 crash。對比了 5.7.20?與 5.7.21?版本該 SQL?的執(zhí)行計劃，主要區(qū)別在于 5.7.21?對子查詢使用了 auto_key，而 5.7.20 版本沒有。懷疑可能與 SQL 未走索引有關(guān)。在 a 表的 name 字段增加索引，再次執(zhí)行 SQL，一切正常，未發(fā)生宕機。

alter table a add index idx_name(name);

另外，如果減少 a，b 兩張表的數(shù)據(jù)量，比如記錄數(shù)減少到 1000，執(zhí)行同樣的 SQL，也不會發(fā)生宕機。

總結(jié)一下：

a 表在 name 字段增加索引，不會宕機

a，b 表減少數(shù)據(jù)量，不會宕機

升級 MySQL 版本到 5.7.21，不會宕機

以上 3 個場景，都會導致執(zhí)行計劃發(fā)生變化，結(jié)合 gdb 調(diào)試 core 文件，crash 發(fā)生在磁盤臨時文件中，因此懷疑執(zhí)行計劃中使用的臨時表，具體來說是使用了磁盤臨時表，最終導致 MySQL crash。

通過搜索 MySQL 5.7.20?以上版本的 Release Note，發(fā)現(xiàn)在 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)

該 Bug?的修復代碼：

簡單來說，就是一個內(nèi)存臨時表由于占用了太大的空間，需要轉(zhuǎn)為磁盤臨時表，磁盤臨時表的索引沒有被正確的初始化，最終導致 crash。

將該提交的代碼合并到 5.7.20，編譯，測試，發(fā)現(xiàn) MySQL?還是 crash?了，只不過這次 crash?的地方不一樣。

繼續(xù)排查，發(fā)現(xiàn)另外一個提交：

將該提交合入 5.7.20?代碼，編譯，測試，問題解決。

總結(jié)：該 Bug 需要 2 個提交才能徹底解決，但即使修復之后，該SQL性能也不好，還是需要增加合適的索引，才能解決性能問題。

3. 解決方案

業(yè)務(wù)表增加合適的索引，避免產(chǎn)生臨時表，同時也能加快 SQL 執(zhí)行效率。

升級 MySQL 版本到 5.7.22 及以上，MySQL 會自動給臨時表增加 auto_key，能夠避免 crash。當然也可以既升級 MySQL 版本，也給表加上合適的索引。

總結(jié)

以上是生活随笔為你收集整理的mysql 临时索引_MySQL select in 语句未使用索引，产生磁盘临时表，导致 crash的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。