One Composite index adjustment _ MySQL

BitsCN.com

Adjustment of a composite index

The table test is as follows:

| Field | Type | Null | Key | Default | Extra |

+ ------------- + --------------------- + ------ + ----- + --------- + ------------------ +

| Pid | int (10) unsigned | NO | PRI | NULL | auto_increment |

| Tid | mediumint (8) unsigned | NO | MUL | 0 |

| Showtime | int (11) | YES | 0 |

Data Volume: over 200 million

Originally, a composite index was created on tid and showtime.

Existing SQL

SELECT * FROM test where and tid = '20180101' AND pid> = 47992 and showtime> 0 order by p. showtime, p. pid LIMIT 0, 40;

Modify the index as follows (tid cannot be changed as the leftmost prefix, and tid + showtime is used for other SQL statements ):

Alter table test add index idx_tid_showtime_pid (tid, showtime, pid );

SQL execution efficiency is very low. please refer to explain

+ ---- + ------------- + ---------------- + ------- + ---------------------- + -------------------- + --------- + ------ + -------- + ------------- +

| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+ ---- + ------------- + ---------------- + ------- + ---------------------- + -------------------- + --------- + ------ + -------- + ------------- +

| 1 | SIMPLE | test | range | idx_tid_showtime_pid | 8 | NULL | 123444 | Using where |

+ ---- + ------------- + ---------------- + ------- + ---------------------- + -------------------- + --------- + ------ + -------- + ------------- +

It seems that the efficiency is still very low. we can see that the execution time is more than 0.7 seconds through the slow-log record.

After the showtime condition is removed, the efficiency of using only the tid and pid Composite Indexes is very high, so the index is modified again:

Alter table test add index idx_tid_pid_showtime (tid, pid, showtime );

This greatly improves the efficiency and reduces the execution time to less than 0.1.

+ ---- + ------------- + ------- + Certificate + ---------------------- + --------- + ------ + ------------------------------- +

| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+ ---- + ------------- + ------- + Certificate + ---------------------- + --------- + ------ + ------------------------------- +

| 1 | SIMPLE | test | range | idx_tid_showtime_pid, idx_tid_pid_showtime | 12 | NULL | 3290 | Using where; Using filesort |

+ ---- + ------------- + ------- + Certificate + ---------------------- + --------- + ------

As mentioned above, I personally understand this. the idx_tid_showtime_pid (tid, showtime, pid) index satisfies the conditions for using the index order by, so 123444 rows of data need to be scanned.

The column order of the index idx_tid_pid_showtime (tid, pid, showtime) is different from that of the order by. The index cannot be used for sorting, but the efficiency of using the index in the where condition is good, only 3290 rows of data are scanned, even when sorting is added.

It can be seen that the addition of indexes cannot simply adapt to the sorting order by field order. although the time spent in sorting is omitted, the other overhead is far greater than the effect of sorting a small amount of data.

BitsCN.com