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.