mysql 8.0上的选择性能问题
伊恩
我遇到了以下sql的性能问题。
select a.id, a.name, a.chinese_name, turnover, group_concat(b.branch) as branch,
group_concat(b.type_of_service) as service
from kn_supplier a join kn_supplier_service b
on a.id = b.supplier_id
group by a.name
它在机器A上运行大约13秒钟，但是在机器B上运行几乎2秒钟。问题是，机器A和机器B的环境与MySQL 8.0相同。架构，表，索引都相同。为什么有这么大的差异？我忘了设置MySQL 8.0的技巧吗？注意，SQL在本地服务器上运行。
我尝试配置两台机器，即机器A 
mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=7;
+----------+-----+--------------------------------+-----------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+
| QUERY_ID | SEQ | STATE | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION | SOURCE_FILE | SOURCE_LINE |
+----------+-----+--------------------------------+-----------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+
| 7 | 2 | starting | 0.000260 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 7 | 3 | Executing hook on transaction | 0.000010 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | launch_hook_trans_begin | rpl_handler.cc | 1107 |
| 7 | 4 | starting | 0.000017 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | launch_hook_trans_begin | rpl_handler.cc | 1109 |
| 7 | 5 | checking permissions | 0.000009 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | check_access | sql_authorization.cc | 2203 |
| 7 | 6 | checking permissions | 0.000011 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | check_access | sql_authorization.cc | 2203 |
| 7 | 7 | Opening tables | 0.000944 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | open_tables | sql_base.cc | 5590 |
| 7 | 8 | init | 0.000018 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Sql_cmd_dml::execute | sql_select.cc | 662 |
| 7 | 9 | System lock | 0.000025 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_lock_tables | lock.cc | 332 |
| 7 | 10 | optimizing | 0.000022 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::optimize | sql_optimizer.cc | 217 |
| 7 | 11 | statistics | 0.000048 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::optimize | sql_optimizer.cc | 429 |
| 7 | 12 | preparing | 0.000069 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::optimize | sql_optimizer.cc | 504 |
| 7 | 13 | Creating tmp table | 0.000221 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::create_intermediate_tabl | sql_executor.cc | 325 |
| 7 | 14 | executing | 12.978405 | 12.953125 | 0.015625 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::exec | sql_executor.cc | 227 |
| 7 | 15 | end | 0.000024 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Sql_cmd_dml::execute | sql_select.cc | 715 |
| 7 | 16 | query end | 0.000007 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_execute_command | sql_parse.cc | 4547 |
| 7 | 17 | waiting for handler commit | 0.000313 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ha_commit_trans | handler.cc | 1570 |
| 7 | 18 | removing tmp table | 0.000257 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2279 |
| 7 | 19 | waiting for handler commit | 0.000020 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2319 |
| 7 | 20 | closing tables | 0.000032 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_execute_command | sql_parse.cc | 4593 |
| 7 | 21 | freeing items | 0.000020 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_parse | sql_parse.cc | 5264 |
| 7 | 22 | removing tmp table | 0.000010 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2279 |
| 7 | 23 | freeing items | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2319 |
| 7 | 24 | removing tmp table | 0.000013 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2279 |
| 7 | 25 | freeing items | 0.000111 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2319 |
| 7 | 26 | logging slow query | 0.000066 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | log_slow_do | log.cc | 1623 |
| 7 | 27 | cleaning up | 0.000032 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | dispatch_command | sql_parse.cc | 2159 |
+----------+-----+--------------------------------+-----------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+
26 rows in set, 1 warning (0.00 sec)
机器B
mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=3;
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+
| QUERY_ID | SEQ | STATE | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION | SOURCE_FILE | SOURCE_LINE |
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+
| 3 | 2 | starting | 0.000082 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | 3 | Executing hook on transaction | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | launch_hook_trans_begin | rpl_handler.cc | 1119 |
| 3 | 4 | starting | 0.000006 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | launch_hook_trans_begin | rpl_handler.cc | 1121 |
| 3 | 5 | checking permissions | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | check_access | sql_authorization.cc | 2218 |
| 3 | 6 | checking permissions | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | check_access | sql_authorization.cc | 2218 |
| 3 | 7 | Opening tables | 0.000344 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | open_tables | sql_base.cc | 5574 |
| 3 | 8 | init | 0.000007 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Sql_cmd_dml::execute | sql_select.cc | 666 |
| 3 | 9 | System lock | 0.000008 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_lock_tables | lock.cc | 331 |
| 3 | 10 | optimizing | 0.000011 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::optimize | sql_optimizer.cc | 219 |
| 3 | 11 | statistics | 0.000020 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::optimize | sql_optimizer.cc | 439 |
| 3 | 12 | preparing | 0.000016 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::optimize | sql_optimizer.cc | 520 |
| 3 | 13 | Creating tmp table | 0.000167 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::create_intermediate_tabl | sql_executor.cc | 336 |
| 3 | 14 | executing | 0.037210 | 0.000000 | 0.031250 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | SELECT_LEX_UNIT::ExecuteIterat | sql_union.cc | 1398 |
| 3 | 15 | end | 0.000007 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Sql_cmd_dml::execute | sql_select.cc | 719 |
| 3 | 16 | query end | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_execute_command | sql_parse.cc | 4579 |
| 3 | 17 | waiting for handler commit | 0.038665 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ha_commit_trans | handler.cc | 1569 |
| 3 | 18 | removing tmp table | 0.000176 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2305 |
| 3 | 19 | waiting for handler commit | 0.000011 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2345 |
| 3 | 20 | closing tables | 0.000012 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_execute_command | sql_parse.cc | 4626 |
| 3 | 21 | freeing items | 0.000005 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_parse | sql_parse.cc | 5299 |
| 3 | 22 | removing tmp table | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2305 |
| 3 | 23 | freeing items | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2345 |
| 3 | 24 | removing tmp table | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2305 |
| 3 | 25 | freeing items | 0.000061 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | free_tmp_table | sql_tmp_table.cc | 2345 |
| 3 | 26 | cleaning up | 0.000015 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | dispatch_command | sql_parse.cc | 2172 |
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+
25 rows in set, 1 warning (0.00 sec)
蒂姆·比格莱森（Tim Biegeleisen）
要求GROUP_CONCAT涉及该组中的每个记录，而没有机会减少那里的开销。此外，您的查询没有WHERE子句。您可以尝试将以下索引添加到kn_supplier_service表中：
CREATE INDEX idx on kn_supplier_service (supplier_id, branch, type_of_service);
这至少将允许MySQL快速针对该表查找表中的每个id值。kn_supplierkn_supplier_service
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
编辑于
相关文章
Related 相关文章
- 1
Mysql 计数与 wordpress 中的选择性能
- 2
MySQL选择JDBC的性能问题
- 3
8GB RAM VPS 上的 Magento - MySQL 性能
- 4
简单选择查询的MySQL性能问题
- 5
简单更新/选择上的MySQL表性能问题（大表，许多连接）
- 6
MySQL 8 窗口函数语法问题
- 7
mysql选择性能（许多具有索引的变量与较少的具有索引的变量）
- 8
Windows 8上的MySQL错误编号2003
- 9
在Debian 8上安装MySQL 5.7失败
- 10
在CentOS 8上安装MySQL 5.7
- 11
无法在 Ubuntu 19.04 上安装 MySQL 8
- 12
MySQL 8 上的基本 sql 查询失败
- 13
如何解决在MySQL 5.7上为MySQL8运行代码的问题？
- 14
Windows Phone 8上的Cocos2dx性能问题
- 15
使用选择，计数和分组依据的MySQL性能问题
- 16
性能问题MySQL 5.7
- 17
Ruby- Mysql2编码utf8mb4创建索引问题（因此影响性能）
- 18
在MySQL中用Utf-8选择返回错误结果
- 19
在MySQL中用Utf-8选择返回错误结果
- 20
mysql＆UTF8阿拉伯语问题
- 21
GlassFish + MySQL + XHTML + Java Beans + UTF-8问题
- 22
mysql＆UTF8阿拉伯语问题
- 23
修复MySQL UTF-8字符问题
- 24
MsSQL上的连接性能与预选择性能
- 25
MySQL选择性显示状态
- 26
带插入的大表上的mysql性能问题
- 27
Windows 8上的MySQL 5.6在哪里得到它的配置？
- 28
无法使MYSQL8在Ubuntu 20.04上运行
- 29
将MySQL UTF8迁移到UTF8MB4问题和疑问
我来说两句