In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-30 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Select * from table where id in (....)
For such a query, is it a range index or an equivalent index?
Select * from table where key_part1 in (....) And key_part2='XX'
For such a query, does the second part still go to the index?
The purpose of the test is to know how MYSQL chose the execution plan for IN LIST; in single-field indexes and composite indexes
[@ more@]
Mysql 5.1.40
Os:rhel 5.4
Engine=innodb
Innodb_file_per_table
# Let's first create a test environment:
Create table index_test (id int auto_increment, col1 int, col2 varchar, content varchar, primary key (id), key col1 (col1)) engine=innodb default charset=latin1
# repeat insert operation 12 times
Insert into index_test (col1,col2) select @ rownum:=@rownum+1,column_name from information_schema.COLUMNS c, (select @ rownum:=0) id limit 500
# Test 1: test the IN operation of the primary key first
# Test cases:
Reset query cache;-clear QUERY_CAHCE
Show status like 'Innodb_buffer_pool_read_requests';-- used to query logical reads
Select * from index_test where id in (2, 10, 10, 1, 000, 1, 000, 1, 000, 1, 000, 000, 7, 000, 000)
Show status like 'Innodb_buffer_pool_read_requests';-- subtract from the previous result to get the logical reading brought about by the execution of SQL
For the accuracy of logical reading, you should run the same SQL several times to get rid of physical reading.
Root@127.0.0.1: test 16:02:16 > explain select * from index_test where id in
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | index_test | range | PRIMARY | PRIMARY | 4 | NULL | 4 | Using where |
+-- +
1 row in set (0.00 sec)
# from the point of view of the implementation plan, we follow the scope conditions; but let's look at the actual situation:
# Note, in order to reduce the length, I have deleted the results of each query.
Select * from index_test where id in (2jue 10)
RESULTs: 2 rows
LIO: 4
Select * from index_test where id in (2pm 1000)
RESULTs: 2 rows
LIO: 4
Select * from index_test where id in (2Jing 10100)
RESULTs: 3 rows
LIO: 6
Select * from index_test where id in (2, 10, 10, 1000, 1, 000, 2, 000)
RESULTs: 4 rows
LIO: 8
Select * from index_test where id in (2, 10, 10, 1, 000, 1, 000, 1, 000, 1, 000, 000, 7, 000, 000)
RESULTs: 5 rows
LIO: 10
# see here that the logical read increases linearly according to the number of KEY in the IN LIST, but does not change according to the key value, so we judge that the IN operation on the primary key is actually converted to the OR operation.
# Test 2: IN operations on non-primary keys
# Test cases:
Reset query cache
Show status like 'Innodb_buffer_pool_read_requests'
Select * from index_test where col1 in (100500300400)
Show status like 'Innodb_buffer_pool_read_requests'
Root@127.0.0.1: test 16:06:33 > explain select * from index_test where col1 in (100200)
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | index_test | range | col1 | col1 | 5 | NULL | 24 | Using where |
+-- +
1 row in set (0.00 sec)
Select * from index_test where col1 in (100101)
RESULTs: 24 rows
LIO: 86
Select * from index_test where col1 in (100500)
RESULTs: 24 rows
LIO: 86
Select * from index_test where col1 in (100500300)
RESULTs: 36 rows
LIO: 139
Select * from index_test where col1 in (100500300400)
RESULTs: 48 rows
LIO: 172
Analysis: this result is the same as that of Test 1
# Test 3: front-column IN operations on composite indexes
Alter table index_test drop index col1, add index col1col2 (col1,col2)
Update index_test set content=concat (col2,col3,col1)
The main purpose is to test whether the optimizer will use the second field for index search after the first field of the index uses IN.
Root@127.0.0.1: test 18:41:38 > explain select content from index_test where col1 in (100500300400) and col2='aaaa'
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | index_test | range | col1col2 | col1col2 | 208 | NULL | 4 | Using where |
+-- +
1 row in set (0.00 sec)
Select count (*) from index_test where col1 in (100500300400) and col2='aaaa'
RESULTs: 0 rows
LIO: 24
Select content from index_test where col1 in (100500300400) and col2='aaaa'
RESULTs: 0 rows
LIO: 24
Analysis:
# We found that the logical reading of the two queries is the same, which actually shows that the optimizer uses the second field of the index and completes the filtering of COL2 in the index search part
Summary: the MYSQL optimizer handles "multiple equivalent" queries that convert in list to "or"; not to range queries
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 277
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.