mysql+limit+0+20_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)

如果用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;

这是典型的分页优化问题，尤其是偏移量大的时候。mysql会扫描大量不需要的行然后抛弃，只取limit的数量。所以一般最好不要用offset。解决方法有

1.可以先使用索引覆盖扫描，而不是查询所有的列，然后做关联操作返回相关的列。这个方法可以叫做“延迟关联”

2.可以把limit查询转换成已知位置的查询，变成between XXX and XXX 。

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这个字段，不能保证唯一值，所以这种方法，可能会丢失数据。

索引应该是

(status,audit_time)

吧，而且你这个索引太多了

大概看了下别人帮你的分析，

(status,audit_time)

组合索引是正解。

但是需要你把建好这个索引的explain贴出来分析一下。

有两个问题需要注意：

1、单表索引数量不宜太多，太多的话会造成查询索引不命中；

2、各种原因造成的索引数据错误，造成查询索引不命中，这个时候可以尝试重建索引。