背景描述
- # 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写的惹不起,反正就是半个小时看不懂的那种,就是一眼就知道一定会产生临时表的??~~~
Q2:登录到机器上去查看内存使用偏小?
因为这个物理机的内存是125G,但是mysql的总数据量不超过1G,所有实际并不需要多少内存就可以将所有数据都加载都内存中。
Q3:既然内存够用,为啥还要在磁盘上产生临时表?
后面可以看见数据库配置的临时表空间是16M,从慢查询日志上来看每一个临时表的大小是16K,在QPS达到一定量了之后,临时表空间就达到了上限,就会产生临时磁盘表,看图下面的产生的【临时磁盘表/临时表】的比例也是符合预期,现在大概就每3条SQL其中有一条会产生临时表。解决办法就是把tmp_table_size这个参数调大,按照当前的计算,调大一半8M可以解决问题。但是,我现在的机器配置很豪,就开心的调大10倍啦~~~~

Q4:磁盘上产生临时表真的是SQL慢的根本原因吗?
通常我们会认为产生了临时表,就更不用说临时磁盘表,大部分就能确定慢查询的原因了。但是这次我还是怀疑了一下,实在是机器性能太好,想着16K的临时表真的有这么大的影响吗,而且我的磁盘性能【SSD、PCIE】感觉也很棒,O(∩_∩)O哈哈~。所以我统计了一下各个阶段的执行时间,发现 converting HEAP to ondisk 从内存中拷贝数据到磁盘消耗的时间并不多,16K对于这种高配的机器还是小case,真正的时间消耗在sending data上,为啥会这样呢?看上面的慢查询日志发现 Bytes_sent: 1616970 这个是1.54M,消耗时间比较多的是从引擎层发送数据给server层,因为这个SQL最后访问的数据比较多。做个简单测试,右边是原来的SQL执行时间,左边是我limit 5的统计结果,可以很直观的看到sending data时间上的差异,时间上查了0.011001/0.000131 ~ 84倍。但是这个和数据行数并不是线性增长关系的,原因嘛就是磁盘的访问方式。
- show profile for query 8;
- +----------------------+----------+
- | Status | Duration |
- +----------------------+----------+
- | starting | 0.000082 |
- | checking permissions | 0.000003 |
- | checking permissions | 0.000001 |
- | checking permissions | 0.000003 |
- | Opening tables | 0.000015 |
- | init | 0.000024 |
- | System lock | 0.000010 |
- | optimizing | 0.000010 |
- | statistics | 0.000098 |
- | preparing | 0.000014 |
- | Creating tmp table | 0.000033 |
- | executing | 0.000002 |
- | Sending data | 0.000131 |
- | end | 0.000003 |
- | query end | 0.000005 |
- | removing tmp table | 0.000049 |
- | query end | 0.000002 |
- | closing tables | 0.000015 |
- | freeing items | 0.000030 |
- | cleaning up | 0.000017 |
- +----------------------+----------+
- 20 rows in set, 1 warning (0.00 sec)
|
- show profile for query 1;
- +---------------------------+----------+
- | Status | Duration |
- +---------------------------+----------+
- | starting | 0.000165 |
- | checking permissions | 0.000005 |
- | checking permissions | 0.000002 |
- | checking permissions | 0.000006 |
- | Opening tables | 0.000027 |
- | init | 0.000057 |
- | System lock | 0.000015 |
- | optimizing | 0.000025 |
- | statistics | 0.000235 |
- | preparing | 0.000031 |
- | Creating tmp table | 0.000066 |
- | executing | 0.000003 |
- | Sending data | 0.011001 |
- | converting HEAP to ondisk | 0.005307 |
- | Sending data | 0.059461 |
- | end | 0.000004 |
- | query end | 0.000011 |
- | removing tmp table | 0.000137 |
- | query end | 0.000004 |
- | closing tables | 0.000026 |
- | freeing items | 0.000026 |
- | cleaning up | 0.000022 |
- +---------------------------+----------+
- 22 rows in set, 1 warning (0.00 sec)
|