当前位置:Gxlcms > mysql > mysqlorderbylimitN快速查找特性的是与非

mysqlorderbylimitN快速查找特性的是与非

时间:2021-07-01 10:21:17 帮助过:45人阅读

orderbylimitN有个特性,如果找到N个符合需求的数据后,就返回结果,而不继续往下查找!这是mysql对orderbylimitn的一个优化,但是有的时候,因为这个特性,也会

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

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

本文出自 “原下” 博客,请务必保留此出处

人气教程排行