MySQL部分从库上面因为大量的临时表tmp_table造成慢查询

背景描述

# time: 2019-01-24t00:08:14.705724+08:00
# user@host: **[**] @  [**]  id: **
# schema: sentrymeta  last_errno: 0  killed: 0
# query_time: 0.315758  lock_time: 0.001693  rows_sent: 9664  rows_examined: 36413  rows_affected: 0
# bytes_sent: 1616970  tmp_tables: 1  tmp_disk_tables: 1  tmp_table_sizes: 16384
# qc_hit: no  full_scan: no  full_join: no  tmp_table: yes  tmp_table_on_disk: yes
# filesort: no  filesort_on_disk: no  merge_passes: 0
#   innodb_io_r_ops: 0  innodb_io_r_bytes: 0  innodb_io_r_wait: 0.000000
#   innodb_rec_lock_wait: 0.000000  innodb_queue_wait: 0.000000
#   innodb_pages_distinct: 1085

             total       used       free     shared    buffers     cached
mem:           125         38         87          0          0         19
-/+ buffers/cache:         18        107
swap:           31          0         31

root@(none)04:33:02>select version();
+---------------+
| version()     |
+---------------+
| 5.7.19-17-log |
+---------------+
1 row in set (0.00 sec)

root@(none)04:33:07>show variables like '%table_size%';
+---------------------+-----------+
| variable_name       | value     |
+---------------------+-----------+
| max_heap_table_size | 134217728 |
| tmp_table_size      | 16777216  |
+---------------------+-----------+
2 rows in set (0.00 sec)

问题分析

q1：为什么会产生临时表？

这个不多说，sql写的惹不起，反正就是半个小时看不懂的那种，就是一眼就知道一定会产生临时表的