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