mysql order by limit N快速查找特性的是与非

唐伯虎

　　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)

　　第二条执行时间为0.2s

　　第一条语句，按照常理，这条语句应该会使用customers_id索引，但是正是因为有了order by orders_id desc limit 0,10符合上面说的特性，mysql使用了快速查找的方法，自作主张，将索引变成orders_id(为主键)，以为可以快速查找！
　　但是也碰到了特性的瓶颈，有了LIKE '%hosek%' 条件 ，且整个结果集只有 两条数据，所以mysql一直查找到最后一条，以满足limit 0,10这个条件。导致执行非常慢！等于一个全索引扫描！
　　

　　看第二条语句，稍稍优化一下，我们不想使用这种特性，强制使用customers_id作为索引，于是使用了customers_id，发现explain的rows扫描范围为1.7w行，而第一条语句的rows为900多行，mysql会确定选择使用这个特性。但是因为使用了customers_id，并没有进行全索引扫描！
　　

　　如果我们再对整个表稍作整改，将LIKE '%hosek%' 去掉(去掉改条件之后有大量数据)，而且我们不强制使用索引customers_id。如下

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)  
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 |
+----+-------------+---------------+--------+----------------------------+---------+---------+-------------------------------------+------+-------------+

　　这条语句的执行时间为0.1！
　　可以看到仍然是使用了orders_id索引，又选择了上面的这个特但是，这个结果集非常大，不需要通过orders_id查询几次就能选择到满足条件的10条，然后就返回了结果！
　　

　　注意：1.注意上面所说的特性
　　2. 如果碰到这种需求，视情况而定，若like '%%'类似能够把结果集缩小的条件多，可以强制使用索引；
　　若不多，则直接让mysql选择；
　　如果能够充分了解到这个特性，自己在程序中分类别选择索引！