mysql limit性能太低_mysql order by + limit 性能问题如何解决？

主要做设计前端。。PHP是业余看看。。

没用过mysql语句 今天硬啃了一天了 发现加上 ORDER BY 就没走索引。。

百度了一天 还是没搞懂原理 和解决方案 求大神科普。。。

order by + limit 分页 越往后性能越低

SELECT * FROM yi_user_joke WHERE status = 2 ORDER BY audit_time desc LIMIT 499950,10;

//结果

10 rows in set (2.67 sec)

SELECT * FROM yi_user_joke WHERE status = 2 ORDER BY audit_time desc LIMIT 499950,10;

explain 结果

explain SELECT * FROM yi_user_joke WHERE status = 2 ORDER BY audit_time desc LIMIT 499950,10;

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

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

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

| 1 | SIMPLE | yi_user_joke | ALL | NULL | NULL | NULL | NULL | 499999 | Using where; Using filesort |

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

1 row in set (0.00 sec)

表结构

show columns from yi_user_joke

-> ;

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

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

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

| id | int(11) | NO | PRI | NULL | auto_increment |

| title | varchar(1000) | NO | | NULL | |

| image | varchar(200) | YES | | NULL | |

| content | text | NO | | NULL | |

| is_package | tinyint(1) | NO | | 0 | |

| package_fee | int(11) | NO | | 0 | |

| package_user_id | int(11) | NO | | 0 | |

| created_time | int(11) | NO | MUL | 0 | |

| audit_time | int(11) | NO | MUL | 0 | |

| type | tinyint(1) | NO | MUL | 0 | |

| status | tinyint(1) | NO | | 0 | |

| user_id | int(11) | NO | MUL | 0 | |

| audit_num | int(11) | NO | | 0 | |

| good_num | int(11) | NO | MUL | 0 | |

| bad_num | int(11) | NO | MUL | 0 | |

| review_num | int(11) | NO | | 0 | |

| share_num | int(11) | NO | | 0 | |

| award_num | int(11) | NO | | 0 | |

| tags_id | varchar(200) | YES | MUL | NULL | |

| god_reply | tinyint(1) | NO | MUL | 0 | |

| reason | varchar(200) | YES | | NULL | |

| commend | tinyint(1) | NO | | 0 | |

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

22 rows in set (0.01 sec)

索引

show index from yi_user_joke

-> ;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| yi_user_joke | 0 | PRIMARY | 1 | id | A | 499999 | NULL | NULL | | BTREE | | |

| yi_user_joke | 1 | type | 1 | type | A | 1 | NULL | NULL | | BTREE | | |

| yi_user_joke | 1 | created_time | 1 | created_time | A | 499999 | NULL | NULL | | BTREE | | |

| yi_user_joke | 1 | user_id | 1 | user_id | A | 1 | NULL | NULL | | BTREE | | |

| yi_user_joke | 1 | god_reply | 1 | god_reply | A | 1 | NULL | NULL | | BTREE | | |

| yi_user_joke | 1 | good_num | 1 | good_num | A | 1 | NULL | NULL | | BTREE | | |

| yi_user_joke | 1 | bad_num | 1 | bad_num | A | 1 | NULL | NULL | | BTREE | | |

| yi_user_joke | 1 | tags_id | 1 | tags_id | A | 1 | NULL | NULL | YES | BTREE | | |

| yi_user_joke | 1 | audit_time | 1 | audit_time | A | 499999 | NULL | NULL | | BTREE | | |

| yi_user_joke | 1 | order_time | 1 | audit_time | A | 499999 | NULL | NULL | | BTREE | | |

| yi_user_joke | 1 | order_time | 2 | status | A | 499999 | NULL | NULL | | BTREE | | |

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

11 rows in set (0.00 sec)