主要做设计前端。。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)
![[Web フロントエンド] Node.js クイック スタート](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
如果用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、各种原因造成的索引数据错误，造成查询索引不命中，这个时候可以尝试重建索引。