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选择；

如果能够充分了解到这个特性，自己在程序中分类别选择索引！

©著作权归作者所有：来自51CTO博客作者justforqdj的原创作品，如需转载，请注明出处，否则将追究法律责任