order by limit N有个特性，如果找到N个符合需求的数据后，就返回结果，而不继续往下查找！
这是mysql对order by limit n的一个优化，但是有的时候，因为这个特性，也会害死人！特别是条件一直查找到最后一个才满足所有需求，而且，需要扫描的结果集非常大，mysql错误的选择了可以快速查找的索引，此时会更加痛苦！
看几个sql语句
EXPLAIN
SELECT orders_id,sale_record_id,delivery_name,track_number FROM orders
INNER JOIN orders_status ON orders_status=orders_status_id WHERE is_delete = 0 AND customers_id=71794 AND language_id=1 AND (specialOperate = 0 OR isSpecialParent=1 OR specialOperate=3) AND delivery_name LIKE '%hosek%'
ORDER BY orders_id DESC LIMIT 0,10;
+----+-------------+---------------+--------+----------------------------+---------+---------+-------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+--------+----------------------------+---------+---------+-------------------------------------+------+-------------+
| 1 | SIMPLE | orders | index | customers_id,orders_status | PRIMARY | 4 | NULL | 922 | Using where |
| 1 | SIMPLE | orders_status | eq_ref | PRIMARY | PRIMARY | 8 | banggood.orders.orders_status,const | 1 | Using index |
+----+-------------+---------------+--------+----------------------------+---------+---------+-------------------------------------+------+-------------+
这条语句执行时间为6s
EXPLAIN
SELECT orders_id,sale_record_id,delivery_name,track_number FROM orders FORCE INDEX(customers_id)
INNER JOIN orders_status ON orders_status=orders_status_id WHERE is_delete = 0 AND customers_id=71794 AND language_id=1 AND (specialOperate = 0 OR isSpecialParent=1 OR specialOperate=3) AND delivery_name LIKE '%hosek%'
ORDER BY orders_id DESC LIMIT 0,10;
+----+-------------+---------------+--------+---------------+--------------+---------+-------------------------------------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+--------+---------------+--------------+---------+-------------------------------------+-------+-----------------------------+
| 1 | SIMPLE | orders | ref | customers_id | customers_id | 4 | const | 17566 | Using where; Using filesort |
| 1 | SIMPLE | orders_status | eq_ref | PRIMARY | PRIMARY | 8 | banggood.orders.orders_status,const | 1 | Using index |
+----+-------------+---------------+--------+---------------+--------------+---------+-------------------------------------+-------+-----------------------------+
2 rows in set (0.00 sec)
数据库
发布于：2021-07-04 10:22
|
阅读数：273
|
评论：0
QQ好友和群
QQ空间
