Shulou(Shulou.com)06/01 Report--

With regard to the use of MYSQL composite indexes, the official statement for the following example is that indexes can be used:

KEY (key_part1,key_part2,key_part3)

Select.... From table where key_part1='xxx' and key_part3='yyy'

Judging from MYSQL's execution plan, it is true that indexes are also used.

However, in the actual optimization process, it is not enough for us to simply pay attention to whether the index is used.

[@ more@]

What we need to focus on is:

Is the index used when filtering the keyword key_part3?

Let's create an example:

CREATE TABLE `im_message_201001_ 12` (

`msg_ id`bigint (20) NOT NULL default'0'

`time`datetime NOT NULL

`owner` varchar (64) collate latin1_bin NOT NULL

`other` varchar (64) collate latin1_bin NOT NULL

`content` varchar (8000) collate latin1_bin default NULL

PRIMARY KEY (`msg_ id`)

KEY `im_msg_own_oth_tim_ ind` (`owner`, `other`, `time`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin

Query statement:

Select count (distinct concat ('ab',content)) dis, count (*) all from im_message_201001_12

Where

Owner='huaniaoyuchong83'

And time between '2010-01-01 00 and' 2010-02-01 00

We see that the conditions of the query are leaping for the index.

This is not difficult for ORACLE. The SQL optimizer will filter the time field in the index.

Use HINT:/*+INDEX_SS (TABLE INDEX_NAME) * / to assist.

But for MYSQL, you may not know when the time field was filtered.

Of course, we want to filter the TIME field through the index. In this way, the final number of returns to the table will be less.

During the test, we speculated the results by observing the changes in the Innodb_buffer_pool_read_requests (logical read) variables of MYSQL.

Notice the change of the condition time and the variable Innodb_buffer_pool_read_requests during the following query

# Test environment:

OS:RHEL 4.7 X86_64

MYSQL 5.0.51a / 5.1.40

Before starting the following test, run:

Select count (distinct concat ('centering focus content)), count (*) from im_message_201001_11 where owner='huaniaoyuchong83'

So that all the results are in CACHE.

# start the first test

Show session status like 'Innodb_buffer_pool_read_requests'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_buffer_pool_read_requests | 136566076 | |

+-+ +

1 row in set (0.02 sec)

Select count (distinct concat), count (*) from im_message_201001_11 where owner='huaniaoyuchong83' and time between '2010-01-0100: 00 and' 2010-02-0100: 00 count

+-+ +

| | count (distinct concat ('cantilethal content)) | count (*) |

+-+ +

| | 35644 | 44397 | |

+-+ +

1 row in set (1.40 sec)

Show session status like 'Innodb_buffer_pool_read_requests'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_buffer_pool_read_requests | 136742193 | |

+-+ +

1 row in set (0.02 sec)

Select 136742193-136566076

+-+

| | 136742193-136566076 | |

+-+

| | 176117 |

+-+

1 row in set (0.00 sec)

# start the second test

Show session status like 'Innodb_buffer_pool_read_requests'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_buffer_pool_read_requests | 136742194 | |

+-+ +

1 row in set (0.02 sec)

Select count (distinct concat ('cymbidium content)), count (*) from im_message_201001_11 where owner='huaniaoyuchong83' and time between' 2010-01-0100: 00 and '2010-01-05 00 and'

+-+ +

| | count (distinct concat ('cantilethal content)) | count (*) |

+-+ +

| | 3679 | 4097 | |

+-+ +

1 row in set (0.74 sec)

Show session status like 'Innodb_buffer_pool_read_requests'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_buffer_pool_read_requests | 136916032 | |

+-+ +

1 row in set (0.01 sec)

Select 136916032-136742194

+-+

| | 136916032-136742194 | |

+-+

| | 173838 |

+-+

1 row in set (0.00 sec)

# start the third test

Show session status like 'Innodb_buffer_pool_read_requests'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_buffer_pool_read_requests | 136916033 | |

+-+ +

1 row in set (0.01 sec)

Select count (distinct concat), count (*) from im_message_201001_11 where owner='huaniaoyuchong83' and time between '2010-01-0100: 00 and' 2010-01-0100: 00 count

+-+ +

| | count (distinct concat ('cantilethal content)) | count (*) |

+-+ +

| | 0 | 0 |

+-+ +

1 row in set (0.85 sec)

Show session status like 'Innodb_buffer_pool_read_requests'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_buffer_pool_read_requests | 137086323 | |

+-+ +

1 row in set (0.01 sec)

Select 137086323-136916033

+-+

| | 137086323-136916033 | |

+-+

| | 170290 |

+-+

1 row in set (0.00 sec)

# start the fourth test

Show session status like 'Innodb_buffer_pool_read_requests'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_buffer_pool_read_requests | 137086324 | |

+-+ +

1 row in set (0.02 sec)

Select count (*) from im_message_201001_11 where owner='huaniaoyuchong83' and time between '2010-01-0100: 00 and' 2010-02-0100: 00

+-+

| | count (*) |

+-+

| | 44397 |

+-+

1 row in set (0.05sec)

Show session status like 'Innodb_buffer_pool_read_requests'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_buffer_pool_read_requests | 137092204 | |

+-+ +

1 row in set (0.01 sec)

Select 137092204-137086324

+-+

| | 137092204-137086324 | |

+-+

| | 5880 |

+-+

1 row in set (0.00 sec)

# start the fifth test

Show session status like 'Innodb_buffer_pool_read_requests'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_buffer_pool_read_requests | 137092205 | |

+-+ +

1 row in set (0.01 sec)

Select count (*) from im_message_201001_11 where owner='huaniaoyuchong83'

+-+

| | count (*) |

+-+

| | 44397 |

+-+

1 row in set (0.04 sec)

Show session status like 'Innodb_buffer_pool_read_requests'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_buffer_pool_read_requests | 137098085 | |

+-+ +

1 row in set (0.01 sec)

Select 137098085-137092205

+-+

| | 137098085-137092205 | |

+-+

| | 5880 |

+-+

1 row in set (0.00 sec)

# start the sixth test

Show session status like 'Innodb_buffer_pool_read_requests'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_buffer_pool_read_requests | 137098131 | |

+-+ +

1 row in set (0.02 sec)

Select count (*) from im_message_201001_11 where owner='huaniaoyuchong83' and time between '2010-01-0100: 00 and' 2010-01-05 00 and'

+-+

| | count (*) |

+-+

| | 4097 |

+-+

1 row in set (0.05sec)

Show session status like 'Innodb_buffer_pool_read_requests'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_buffer_pool_read_requests | 137104011 | |

+-+ +

1 row in set (0.01 sec)

Select 137104011-137098131

+-+

| | 137104011-137098131 | |

+-+

| | 5880 |

+-+

1 row in set (0.00 sec)

# Analysis results

For the first three queries, you need to return the table after retrieval from the index:

Logical reading of time result rows

30 days 44397 176117

5 days 4097 173838

1 day 0 170290

For the last three queries, there is no need to return to the table after retrieval from the index

Logical reading of time result rows

30 days 44397 5880

No time condition 44397 5880

5 days 4097 5880

Judging from the data,

For queries such as select count (*), with or without time conditions, logical reads are the same and do not have to return to the table.

It also shows that in this case, MYSQL uses indexes to filter time fields.

Select count (distinct concat ('cantilever content), count (*), such a query, which uses fields other than the index, must return to the table.

However, through logical reading, it is found that no matter how many rows the query result is, the logical reading is about 17W.

Especially when the result behavior is 0, if the time is filtered through the index, then the logical read should be close to 5900, not 17W.

This also shows that in this case, MYSQL does not use the index to filter the TIME field.

So MYSQL also uses different optimization procedures for querying the same WHERE conditions, but there is something wrong with the optimization of MS.

For such an index, it needs to be optimized. Adjust the index order (`owner`, `time`, `other`).

But this is just an optimization of an SQL.

You should also consider that there are many other similar SQL in the system that need to use this index. So when optimizing, you need to evaluate all SQL.

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.