php - mysql order by + limit 性能问题如何解决？

Question

主要做设计前端。。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)

Answer 1

如果用id排序和audit_time排序效果一致
第一页
$sql=SELECT * FROM yi_user_joke WHERE status = 2 ORDER BY id desc LIMIT 10;
第二页
$sql2=SELECT * FROM yi_user_joke WHERE status = 2 and id < 第一条sql查出的id ORDER BY id desc LIMIT 10;

Answer 2

这是典型的分页优化问题，尤其是偏移量大的时候。mysql会扫描大量不需要的行然后抛弃，只取limit的数量。所以一般最好不要用offset。解决方法有
1.可以先使用索引覆盖扫描，而不是查询所有的列，然后做关联操作返回相关的列。这个方法可以叫做“延迟关联”
2.可以把limit查询转换成已知位置的查询，变成between XXX and XXX 。
3.可以记录上次查询的数据的位置，下一次查询直接从该位置开始扫描

Answer 3

offset以及size来分页

select * from table where column < offset order by column desc limit size

需要记住column的最后一个值，这里column的值就是offset

大概是这样，不过不建议使用时间字段来作为offset，而是使用主键

SELECT * FROM yi_user_joke WHERE status = 2 and audit_time < '{上次查询最小的值}' ORDER BY audit_time desc limit size

因为audit_time这个字段，不能保证唯一值，所以这种方法，可能会丢失数据。

Answer 4

索引应该是(status,audit_time)吧，而且你这个索引太多了

Answer 5

大概看了下别人帮你的分析，(status,audit_time) 组合索引是正解。
但是需要你把建好这个索引的explain贴出来分析一下。
有两个问题需要注意：
1、单表索引数量不宜太多，太多的话会造成查询索引不命中；
2、各种原因造成的索引数据错误，造成查询索引不命中，这个时候可以尝试重建索引。