关于模糊查询，排序的问题，特别奇怪

[寂小魔]

## 这条查询耗时31s  ProductID , ProductName 建立了索引
## EXPLAIN 里，使用了 ProductID 的索引
select * FROM products 
where ProductName like '%宝%' 
and ProductName like '%宝%' 
and ProductName like '%爬%' 
and ProductName like '%行%' 
and ProductName like '%垫%' 
and ProductName like '%可%' 
and ProductName like '%机%' 
and ProductName like '%洗%' 
ORDER BY ProductID DESC
limit 0,20;

## 去掉Order By 耗时3s
## 没用到索引
select * FROM products 
where ProductName like '%宝%' 
and ProductName like '%宝%' 
and ProductName like '%爬%' 
and ProductName like '%行%' 
and ProductName like '%垫%' 
and ProductName like '%可%' 
and ProductName like '%机%' 
and ProductName like '%洗%' 
limit 0,20;

## 使用主键ID  Order By 耗时3s
## EXPLAIN 里，使用了 PRIMARY 的索引
select * FROM products 
where ProductName like '%宝%' 
and ProductName like '%宝%' 
and ProductName like '%爬%' 
and ProductName like '%行%' 
and ProductName like '%垫%' 
and ProductName like '%可%' 
and ProductName like '%机%' 
and ProductName like '%洗%' 
ORDER BY ID
limit 0,20;

where + order by 查询，order by 主键(ID) 与 普通索引(ProductID) 查询速度相差太大，什么原因造成的？

[听雨停了]

主键(ID) 默认就是聚集索引，数据内容本身就是按照聚集索引存进物理文件的，所以你读出来的数据其实就是按照这个排序出来的。这也就是为什么你去掉order by和使用id作为order by条件查询出来的耗时是一样的原因（两者查询出来的结果也是一样的）。与而普通索引(ProductID)是没有经过排序的，因为你的数据量比较大，所以按照这个非聚集索引再去排序消耗很长的时间

[zjcxc]

说明 ORDER BY ProductID DESC 的顺序去找数据时，不太容易找到满足条件记录，所以需要扫描的数据多，耗时长 不走索引和主键排序通常都是用表扫描（查询2/3），所以两者效率相同

[zjcxc]

有 LIMIT 时，通常是找到满足条件的 LIMIT 的记录时，查询就终止了，并不会找完所有满足条件的 所以能尽快找齐满足条件的查找顺序效率通常会更高

[LongRui888]

第1个之所以慢，主要是排序，要把所有数据都排序后，在取出前N条数据。 后面2个之所以快，一个是不用排序，直接取出前N条，那么找到N条记录后，就不会继续在去找，速度就快了。 最后1个，是因为按照主键排序，所以找到前N个，就直接返回了

[寂小魔]

引用 1 楼 qq_37170555 的回复:

主键(ID) 默认就是聚集索引，数据内容本身就是按照聚集索引存进物理文件的，所以你读出来的数据其实就是按照这个排序出来的。这也就是为什么你去掉order by和使用id作为order by条件查询出来的耗时是一样的原因（两者查询出来的结果也是一样的）。与而普通索引(ProductID)是没有经过排序的，因为你的数据量比较大，所以按照这个非聚集索引再去排序消耗很长的时间

引用 2 楼 zjcxc 的回复:

说明 ORDER BY ProductID DESC 的顺序去找数据时，不太容易找到满足条件记录，所以需要扫描的数据多，耗时长 不走索引和主键排序通常都是用表扫描（查询2/3），所以两者效率相同

引用 4 楼 yupeigu 的回复:

第1个之所以慢，主要是排序，要把所有数据都排序后，在取出前N条数据。 后面2个之所以快，一个是不用排序，直接取出前N条，那么找到N条记录后，就不会继续在去找，速度就快了。 最后1个，是因为按照主键排序，所以找到前N个，就直接返回了

limit 0,20 limit 600,20 这个两个也会影响查询速度，前者耗时很短，后者就耗时很多。 这些有没有什么办法或者思路可以去解决呢？

[LongRui888]

引用 5 楼 mimrc7993 的回复:

[quote=引用 1 楼 qq_37170555 的回复:] 主键(ID) 默认就是聚集索引，数据内容本身就是按照聚集索引存进物理文件的，所以你读出来的数据其实就是按照这个排序出来的。这也就是为什么你去掉order by和使用id作为order by条件查询出来的耗时是一样的原因（两者查询出来的结果也是一样的）。与而普通索引(ProductID)是没有经过排序的，因为你的数据量比较大，所以按照这个非聚集索引再去排序消耗很长的时间

引用 2 楼 zjcxc 的回复:

说明 ORDER BY ProductID DESC 的顺序去找数据时，不太容易找到满足条件记录，所以需要扫描的数据多，耗时长 不走索引和主键排序通常都是用表扫描（查询2/3），所以两者效率相同

引用 4 楼 yupeigu 的回复:

第1个之所以慢，主要是排序，要把所有数据都排序后，在取出前N条数据。 后面2个之所以快，一个是不用排序，直接取出前N条，那么找到N条记录后，就不会继续在去找，速度就快了。 最后1个，是因为按照主键排序，所以找到前N个，就直接返回了

limit 0,20 limit 600,20 这个两个也会影响查询速度，前者耗时很短，后者就耗时很多。 这些有没有什么办法或者思路可以去解决呢？[/quote] 第2个之所以，是因为他要找到第600条数据后，然后再取出20条，这种相对第1个会慢一些，这个比较难解决。 另外，我看你的代码都是 %xx% 这种语句，一般也不会用索引，计算用上索引，效果也不是很好，如果你要做全文检索，可以参考一下 sphinx 引擎