MySQL查询性能在次级索引上

发布于 2025-02-09 12:08:53 字数 6235 浏览 1 评论 0原文

我对选择查询性能有一个问题。我的表DDL（从show创建表测试中的gen）如下：

CREATE TABLE `test` (
  `id` bigint NOT NULL,
  `issue_code` varchar(10) DEFAULT NULL,
  `issue` char(12) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_01` (`issue_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

db版本为8.0.23

i触发下面的两个查询语句：

S1 : select issue_code from test WHERE issue_code = '1104' limit 200000 ;
S2 : select id from test WHERE issue_code = '1104' limit 200000 ;

S2比S1慢，但是ID列是主键，而在辅助索引IDX_01上的捆绑包。

所以我真的不知道为什么S2比S1慢？

执行计划都是相同的。

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  test    NULL    ref idx_01  idx_01  43  const   224941  100.00  Using index .

显示配置文件：

Query_ID    Duration    Query
1   0.10767725  select issue_code from test WHERE issue_code = '1104' limit 200000
2   0.10759700  select issue_code from test WHERE issue_code = '1104' limit 200000
3   0.10683650  select issue_code from test WHERE issue_code = '1104' limit 200000
4   1.03925400  select id from test WHERE issue_code = '1104' limit 200000
5   1.08807700  select id from test WHERE issue_code = '1104' limit 200000
6   1.30657375  select id from test WHERE issue_code = '1104' limit 200000

查询1详细信息：

Status  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
starting    0.000070    0.000067    0.000002    0   0   0   0   0   0   0   0   0   NULL    NULL    NULL
Executing hook on transaction   0.000005    0.000004    0.000000    0   0   0   0   0   0   0   0   0   launch_hook_trans_begin rpl_handler.cc  1378
starting    0.000009    0.000008    0.000001    0   0   0   0   0   0   0   0   0   launch_hook_trans_begin rpl_handler.cc  1380
checking permissions    0.000006    0.000005    0.000000    0   0   0   0   0   0   0   0   0   check_access    sql_authorization.cc    2272
Opening tables  0.000148    0.000144    0.000005    0   0   0   0   0   0   0   0   0   open_tables sql_base.cc 5780
init    0.000007    0.000006    0.000001    0   0   0   0   0   0   0   0   0   execute sql_select.cc   603
System lock 0.000008    0.000008    0.000000    0   0   0   0   0   0   0   0   0   mysql_lock_tables   lock.cc 332
optimizing  0.000013    0.000013    0.000000    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    334
statistics  0.000080    0.000077    0.000003    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    603
preparing   0.000037    0.000035    0.000001    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    687
executing   0.106980    0.328047    0.000000    54  2   0   0   0   0   0   0   0   ExecuteIteratorQuery    sql_union.cc    1128
end 0.000016    0.000015    0.000000    0   0   0   0   0   0   0   0   0   execute sql_select.cc   636
query end   0.000005    0.000006    0.000000    0   0   0   0   0   0   0   0   0   mysql_execute_command_internal  sql_parse.cc    6350
waiting for handler commit  0.000009    0.000009    0.000000    0   0   0   0   0   0   0   0   0   ha_commit_trans handler.cc  1700
closing tables  0.000014    0.000013    0.000000    0   0   0   0   0   0   0   0   0   mysql_execute_command_internal  sql_parse.cc    6421
freeing items   0.000261    0.000262    0.000000    0   0   0   0   0   0   0   0   0   finish_dispatch_sql_command sql_parse.cc    6947
cleaning up 0.000010    0.000010    0.000000    0   0   0   0   0   0   0   0   0   finish_dispatch_command sql_parse.cc    3378

查询4详细信息：

Status  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
starting    0.000076    0.000076    0.000000    0   0   0   0   0   0   0   0   0   NULL    NULL    NULL
Executing hook on transaction   0.000005    0.000005    0.000000    0   0   0   0   0   0   0   0   0   launch_hook_trans_begin rpl_handler.cc  1378
starting    0.000009    0.000009    0.000000    0   0   0   0   0   0   0   0   0   launch_hook_trans_begin rpl_handler.cc  1380
checking permissions    0.000007    0.000007    0.000000    0   0   0   0   0   0   0   0   0   check_access    sql_authorization.cc    2272
Opening tables  0.000041    0.000041    0.000000    0   0   0   0   0   0   0   0   0   open_tables sql_base.cc 5780
init    0.000012    0.000012    0.000000    0   0   0   0   0   0   0   0   0   execute sql_select.cc   603
System lock 0.000009    0.000008    0.000000    0   0   0   0   0   0   0   0   0   mysql_lock_tables   lock.cc 332
optimizing  0.000014    0.000014    0.000000    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    334
statistics  0.000090    0.000090    0.000000    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    603
preparing   0.000022    0.000022    0.000000    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    687
executing   1.038229    2.064186    0.020909    1644    50  0   0   0   0   0   19  0   ExecuteIteratorQuery    sql_union.cc    1128
end 0.000016    0.000015    0.000000    0   0   0   0   0   0   0   0   0   execute sql_select.cc   636
query end   0.000005    0.000005    0.000000    0   0   0   0   0   0   0   0   0   mysql_execute_command_internal  sql_parse.cc    6350
waiting for handler commit  0.000007    0.000007    0.000000    0   0   0   0   0   0   0   0   0   ha_commit_trans handler.cc  1700
closing tables  0.000009    0.000010    0.000000    0   0   0   0   0   0   0   0   0   mysql_execute_command_internal  sql_parse.cc    6421
freeing items   0.000695    0.000694    0.000000    0   0   0   0   0   0   0   0   0   finish_dispatch_sql_command sql_parse.cc    6947
cleaning up 0.000009    0.000009    0.000000    0   0   0   0   0   0   0   0   0   finish_dispatch_command sql_parse.cc    3378

为什么S2（查询4〜6）应该需要更多上下文开关？

原文

I have a issue about select query performance .
My table DDL(gen from show create table test) as below:

CREATE TABLE `test` (
  `id` bigint NOT NULL,
  `issue_code` varchar(10) DEFAULT NULL,
  `issue` char(12) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_01` (`issue_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

db version is 8.0.23

I trigger below two query statements :

S1 : select issue_code from test WHERE issue_code = '1104' limit 200000 ;
S2 : select id from test WHERE issue_code = '1104' limit 200000 ;

The S2 is more slow than S1 ,but the id column is primary key and bundle on secondary index idx_01.

So I really don't know why S2 is so slowly than S1 ?

The execution plan are both the same.

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  test    NULL    ref idx_01  idx_01  43  const   224941  100.00  Using index .

show profiles :

Query_ID    Duration    Query
1   0.10767725  select issue_code from test WHERE issue_code = '1104' limit 200000
2   0.10759700  select issue_code from test WHERE issue_code = '1104' limit 200000
3   0.10683650  select issue_code from test WHERE issue_code = '1104' limit 200000
4   1.03925400  select id from test WHERE issue_code = '1104' limit 200000
5   1.08807700  select id from test WHERE issue_code = '1104' limit 200000
6   1.30657375  select id from test WHERE issue_code = '1104' limit 200000

Query 1 detail:

Status  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
starting    0.000070    0.000067    0.000002    0   0   0   0   0   0   0   0   0   NULL    NULL    NULL
Executing hook on transaction   0.000005    0.000004    0.000000    0   0   0   0   0   0   0   0   0   launch_hook_trans_begin rpl_handler.cc  1378
starting    0.000009    0.000008    0.000001    0   0   0   0   0   0   0   0   0   launch_hook_trans_begin rpl_handler.cc  1380
checking permissions    0.000006    0.000005    0.000000    0   0   0   0   0   0   0   0   0   check_access    sql_authorization.cc    2272
Opening tables  0.000148    0.000144    0.000005    0   0   0   0   0   0   0   0   0   open_tables sql_base.cc 5780
init    0.000007    0.000006    0.000001    0   0   0   0   0   0   0   0   0   execute sql_select.cc   603
System lock 0.000008    0.000008    0.000000    0   0   0   0   0   0   0   0   0   mysql_lock_tables   lock.cc 332
optimizing  0.000013    0.000013    0.000000    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    334
statistics  0.000080    0.000077    0.000003    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    603
preparing   0.000037    0.000035    0.000001    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    687
executing   0.106980    0.328047    0.000000    54  2   0   0   0   0   0   0   0   ExecuteIteratorQuery    sql_union.cc    1128
end 0.000016    0.000015    0.000000    0   0   0   0   0   0   0   0   0   execute sql_select.cc   636
query end   0.000005    0.000006    0.000000    0   0   0   0   0   0   0   0   0   mysql_execute_command_internal  sql_parse.cc    6350
waiting for handler commit  0.000009    0.000009    0.000000    0   0   0   0   0   0   0   0   0   ha_commit_trans handler.cc  1700
closing tables  0.000014    0.000013    0.000000    0   0   0   0   0   0   0   0   0   mysql_execute_command_internal  sql_parse.cc    6421
freeing items   0.000261    0.000262    0.000000    0   0   0   0   0   0   0   0   0   finish_dispatch_sql_command sql_parse.cc    6947
cleaning up 0.000010    0.000010    0.000000    0   0   0   0   0   0   0   0   0   finish_dispatch_command sql_parse.cc    3378

Query 4 detail:

Status  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
starting    0.000076    0.000076    0.000000    0   0   0   0   0   0   0   0   0   NULL    NULL    NULL
Executing hook on transaction   0.000005    0.000005    0.000000    0   0   0   0   0   0   0   0   0   launch_hook_trans_begin rpl_handler.cc  1378
starting    0.000009    0.000009    0.000000    0   0   0   0   0   0   0   0   0   launch_hook_trans_begin rpl_handler.cc  1380
checking permissions    0.000007    0.000007    0.000000    0   0   0   0   0   0   0   0   0   check_access    sql_authorization.cc    2272
Opening tables  0.000041    0.000041    0.000000    0   0   0   0   0   0   0   0   0   open_tables sql_base.cc 5780
init    0.000012    0.000012    0.000000    0   0   0   0   0   0   0   0   0   execute sql_select.cc   603
System lock 0.000009    0.000008    0.000000    0   0   0   0   0   0   0   0   0   mysql_lock_tables   lock.cc 332
optimizing  0.000014    0.000014    0.000000    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    334
statistics  0.000090    0.000090    0.000000    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    603
preparing   0.000022    0.000022    0.000000    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    687
executing   1.038229    2.064186    0.020909    1644    50  0   0   0   0   0   19  0   ExecuteIteratorQuery    sql_union.cc    1128
end 0.000016    0.000015    0.000000    0   0   0   0   0   0   0   0   0   execute sql_select.cc   636
query end   0.000005    0.000005    0.000000    0   0   0   0   0   0   0   0   0   mysql_execute_command_internal  sql_parse.cc    6350
waiting for handler commit  0.000007    0.000007    0.000000    0   0   0   0   0   0   0   0   0   ha_commit_trans handler.cc  1700
closing tables  0.000009    0.000010    0.000000    0   0   0   0   0   0   0   0   0   mysql_execute_command_internal  sql_parse.cc    6421
freeing items   0.000695    0.000694    0.000000    0   0   0   0   0   0   0   0   0   finish_dispatch_sql_command sql_parse.cc    6947
cleaning up 0.000009    0.000009    0.000000    0   0   0   0   0   0   0   0   0   finish_dispatch_command sql_parse.cc    3378

Why S2(Query 4~6) should need more context switch ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

墨小沫ゞ 2025-02-16 12:08:53

您获得不同结果的原因主要是要返回。在您的查询中，您要在哪里询问ISSUECODE，其中ISSUECODE =某些值，所使用的索引已经具有所有需求，而无需返回到其他列数据的原始数据页面。由于索引索引，它只会抓住它的所作所为，并在达到限制后立即切断。因为您没有子句的订单，所以它不在乎任何ID序列基础，只需抓取问题代码（在这种情况下，这都是相同的值）。

在第二个，您要询问问题代码=某些值的ID的位置，它需要进行往返往返。它首先需要索引来找出哪些符合条件，然后需要返回到索引指向的原始数据页面，以获取上述合格记录的ID组件。

现在，尽管您的示例显然已经缩写了任何重要的私人数据，但似乎并不是最终使用的方法。您可能会更好的是根据您的实际需求获得复合或覆盖索引。在这种情况下，拥有覆盖索引符合其中的条件，但也沿着感兴趣的另一列携带，从而阻止了需要返回原始数据页面。

在这种情况下，在（issue_code，id）上有一个索引，而不是依靠两个单独的索引会为您提供更好的性能。首先要限定位置，但ID在其中进行返回列。现在，执行仅限于索引的4、5或更多列的覆盖索引是不切实际的，但如果您在标准保证该标准的情况下，则可能适用于3、4或5列的索引。过滤目的。

此外，将某个条款和顺序结合起来也是一个很好的考虑因素，即按列作为索引的一部分以及绩效的一部分。这样，您可以获得合格的位置，但它也根据最终预期输出顺序排列。

最终澄清。

查询＃2中的原因不用用作“覆盖”索引，是因为您的索引仅基于WHERE子句。您无法将其视为我单独的索引。这就是引擎工作的方式。它分析了标准并找到了最佳索引。加载该条目列表仅是问题代码的条目，并且没有关联的ID上下文。因此，它获取了这些索引条目，然后必须返回原始数据才能使实际ID返回。

覆盖索引是一个具有组件的单个索引，因此（issue_code，id）。因此，它可以在此问题上获取哪些标准，同时，可以返回ID而无需转到原始数据页面。

这就是为什么您仅返回essue_code更快的问题代码的第一个查询的原因。它具有代码，并且不需要原始数据中的其他任何内容。这有助于澄清吗？

The reason you are getting different results is mostly what is being returned. In your query where you are asking for the IssueCode where IssueCode = some value, the index being used already has all it needs without having to go back to the raw data pages for other column data. Since it is indexed it just grabs what it does and cuts off as soon as the limit is reached. Because you have no ORDER BY clause, it does not care about any ID ordinal basis, just grabs the issue code (which in this case is all the same value).

In the second, where you are asking for the ID where the issue code = some value, it needs to do a round-trip so-to-speak. It first needs the index to find out WHAT qualifies, THEN needs to go back to the raw data page pointed to by the index to get the ID component of said-qualified record.

Now, although your example has obviously been abbreviated of any significant private data, it also does not seem practical end-use. What you might be better is to have a composite or covering index based on your actual needs. In this case, having a COVERING index meets the WHERE condition but also carries along the other column(s) of interest thus preventing the need to go back to the raw data pages.

In this case, having an index on (issue_code, id) instead of relying on two individual indexes would give you better performance. Issue first to qualify the WHERE, but the ID is in there for the return column of interest. Now, it is not practical to do a covering index of say 4, 5 or more columns which would just bloat the index, but could be applicable to have a 3, 4 or 5 column-based index if you WHERE criteria warranted it for the filtering purposes.

Additionally, in combination of a WHERE and ORDER BY clause is also a good consideration to have the ORDER BY column as part of the index as well for performance. This way you get the WHERE qualified, but its also in order based on the final expected output.

FINAL CLARIFICATION.

The reason in query #2 does NOT utilize as a "covering" index is because your index is based only on the WHERE clause. You cant think of it as I have an index on each individually. That is now how the engine works. It analyzes the criteria and finds the best index for that. Loads that list of entries that qualify which is ONLY that of the issue code and does NOT have any context of the IDs associated. So it gets those INDEXED entries and THEN has to go back to the raw data to get the actual IDs to be returned.

A COVERING index is a SINGLE index that has BOTH components, hence (issue_code, id). So it can grab the WHERE criteria on the issue, AND at the same time, be able to return the ID without going to the raw data pages.

That is why the first query where you are only returning the issue code WHERE the issue_code is faster. It has the code and does not need anything else from the raw data. Does that help clarify?

回复收藏 0 原文

~没有更多了~