Use useindex to optimize SQL queries _ MySQL

Use useindex to optimize SQL queries

Let's take a look at the table structure of arena_match_index. pay attention to the index structure of the table.

Create table 'Arena _ match_index '(
'Tid' int (10) unsigned not null default '0 ',
'Mid 'Int (10) unsigned not null default '0 ',
'Group' int (10) unsigned not null default '0 ',
'Round 'tinyint (3) unsigned not null default '0 ',
'Day' date not null default '2017-00-00 ',
'Begintime' datetime not null default '2017-00-00 00:00:00 ',
Unique key 'TT' ('tid', 'mid '),
KEY 'mid '('mid '),
KEY 'begintime' ('begintime '),
KEY 'dg '('day', 'Group '),
KEY 'TD '('tid', 'day ')
) ENGINE = MyISAM default charset = utf8

Next, let's look at the following SQL:

SELECT round FROM arena_match_index WHERE 'day' = '2017-12-31 'AND 'group' = 18 AND 'begintime' <'2017-12-31 12:14:28' order by begintime LIMIT 1;

The query conditions of this SQL statement show that the indexes that may be used include 'begintime' and 'dg '. However, because order by begintime is used to sort mysql, the 'begintime' index is selected, the explain result is:

Mysql> explain SELECT round FROM arena_match_index WHERE 'day' = '2017-12-31 'AND 'group' = 18 AND 'begintime' <'2017-12-31 12:14:28' order by begintime LIMIT 1;
+ ---- + ------------- + ------------------- + ------- + --------------- + ----------- + --------- + ------ + -------- + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------------------- + ------- + --------------- + ----------- + --------- + ------ + -------- + ------------- +
| 1 | SIMPLE | arena_match_index | range | begintime, dg | Begintime | 8 | NULL | 226480 | Using where |
+ ---- + ------------- + ------------------- + ------- + --------------- + ----------- + --------- + ------ + -------- + ------------- +

The explain results show that 22 million records need to be scanned using the 'begintime' index. the query performance is very poor, and the actual execution is also the first time (when no data is cached) it takes more than 30 seconds.

In fact, this query uses the 'dg 'Union index to provide better performance, because dozens of matches are played in the same group on the same day, therefore, the 'dg 'index should be used first to locate matched data sets and then sort them. how can we tell mysql to use the specified index? Use the use index statement:

Mysql> explain SELECT round FROM arena_match_index use index (dg) WHERE 'day' = '2017-12-31 'AND 'group' = 18 AND 'begintime' <'2017-12-31 12:14:28' order by begintime LIMIT 1;
+ ---- + ------------- + ------------------- + ------ + --------------- + ------ + --------- + ------------- + ------ + --------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------------------- + ------ + --------------- + ------ + --------- + ------------- + ------ + --------------------------- +
| 1 | SIMPLE | arena_match_index | ref | dg | 7 | const, const | 757 | Using where; Using filesort |
+ ---- + ------------- + ------------------- + ------ + --------------- + ------ + --------- + ------------- + ------ + --------------------------- +

The explain results show that you only need to scan 757 pieces of data using the 'dg 'joint index, and the performance has been improved by hundreds of times. The actual execution result is almost immediately returned.

In the initial query statement, if order by begintime is removed, mysql will use the 'dg 'index. once again, this confirms that order by will affect the index selection policy of mysql!

Mysql> explain SELECT round FROM arena_match_index WHERE 'day' = '2017-12-31 'AND 'group' = 18 AND 'begintime' <'2017-12-31 12:14:28' LIMIT 1;
+ ---- + ------------- + ------------------- + ------ + --------------- + ------ + --------- + ------------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------------------- + ------ + --------------- + ------ + --------- + ------------- + ------ + ------------- +
| 1 | SIMPLE | arena_match_index | ref | begintime, dg | 7 | const, const | 717 | Using where |
+ ---- + ------------- + ------------------- + ------ + --------------- + ------ + --------- + ------------- + ------ + ------------- +

Through the above example, mysql is sometimes not smart, and it is not always the best choice. we still need developers to "tune up" it "!

BitsCN.com