Optimization of MySQL inverted row

Today the database load goes straight up and the number of database connections explodes. Take a look at the sentence, the culprit is a very simple statement: SELECT * from Eload_promotion_code WHERE 1 and exp_time<1478782591 and cishu=0 ORDER by id DESC Limit 454660, 20; Apart this statement first kill, and then slowly see how to optimize.

First look at the index of this table:

>show index from Eload_promotion_code\g

1. Row ***************************

Table:eload_promotion_code

non_unique:0

Key_name:primary

Seq_in_index:1

Column_name:id

Collation:a

cardinality:921642

Sub_part:null

Packed:null

Null:

Index_type:btree

Comment:

Index_comment:

2. Row ***************************

Table:eload_promotion_code

Non_unique:1

Key_name:idx_cishu_exp

Seq_in_index:1

Column_name:cishu

Collation:a

Cardinality:15

Sub_part:null

Packed:null

Null:

Index_type:btree

Comment:

Index_comment:

3. Row ***************************

Table:eload_promotion_code

Non_unique:1

Key_name:idx_cishu_exp

Seq_in_index:2

Column_name:exp_time

Collation:a

cardinality:921642

Sub_part:null

Packed:null

Null:

Index_type:btree

Comment:

Index_comment:

You can see the ID as the primary key, Idx_cishu_exp is the unique index (cishu,exp_time)

Take a look at the execution plan for this statement, and you can see that the sort is not used to index

Explain SELECT * from Eload_promotion_code WHERE 1 and exp_time<1478782591 and cishu=0 ORDER by id DESC limit 454660,20 \g

1. Row ***************************

Id:1

Select_type:simple

Table:eload_promotion_code

Type:ref

Possible_keys:idx_cishu_exp

Key:idx_cishu_exp

Key_len:4

Ref:const

rows:460854

Extra:using where; Using Filesort

1 row in Set (0.00 sec)

Replace SELECT * with a SELECT ID and then look at the execution plan, which can be overridden with an index

>explain Select ID from eload_promotion_code WHERE 1 and exp_time<1478782591 and cishu=0 ORDER by id DESC limit 4546 60,20 \g

1. Row ***************************

Id:1

Select_type:simple

Table:eload_promotion_code

Type:range

Possible_keys:idx_cishu_exp

Key:idx_cishu_exp

Key_len:8

Ref:null

rows:460862

Extra:using where; Using index; Using Filesort

1 row in Set (0.00 sec)

OK, this statement is saved, take the delay association first take out the ID, and then according to the ID to get the original table required rows, the rewritten statement came: SELECT * from Eload_promotion_code INNER JOIN (SELECT ID from Eload_ Promotion_code where exp_time<1478782591 and cishu=0 order by id DESC limit 454660,20) as X on eload_promotion_code.id= X.id;

Take a look at the 0.3s results.

It's all over, though.

This article is from the "Always on the Road" blog, please be sure to keep this source http://chenql.blog.51cto.com/8732050/1871575

Optimization of MySQL inverted row