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 ?
CodePudding user response：
For S1 the result is retrieved directly from the Index: Query-> Index -> return value from Index
For S2 MySQL needs the index to point to the primary key to return the ID: Query -> Index -> point to Primary Key to return ID -> return value for ID
CodePudding user response：
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.