我遇到了以下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)