查询优化中更喜欢什么：使用文件排序或检查更多行
我正在尝试使用 EXPLAIN 来优化这个 mysql 查询。有人可以帮我一下吗？
EXPLAIN SELECT * FROM keyword
WHERE keyword LIKE "panasonic%"
AND keyword != "panasonic"
AND price < 3230 AND price > 3370
ORDER BY price DESC
LIMIT 99
基本上我想找出以“某些关键字”开头但不完全匹配的关键字，并且其价格不在某个特定范围内。哦，我必须按价格降序排列它们（这导致了这里的问题）。
解释输出：
id: 1
select_type: SIMPLE
table: keyword
type: range
possible_keys: PRIMARY, keyword_price, price_keyword
key: keyword_price
key_len: 765
ref: NULL
rows: 24
Extra: Using where; Using filesort
索引 Key_name : column_names
PRIMARY: keyword
keyword_price: keyword, price
price_keyword: price, keyword
现在，如果我给出使用索引的提示，并将查询更改为
EXPLAIN SELECT * FROM keyword USE INDEX (price_keyword)
WHERE keyword LIKE "panasonic%"
AND keyword != "panasonic"
AND price < 3230 AND price > 3370
ORDER BY price DESC
LIMIT 99
“解释输出”更改为
id: 1
select_type: SIMPLE
table: keyword
type: index
possible_keys: price_keyword
key: price_keyword
key_len: 790
ref: NULL
rows: 1043044 (WHAT THE ????)
Extra: Using where
“解释输出”显示行数呈指数增长，但“使用文件排序”已消失。
在这种情况下哪个查询更好？ “检查的行数”栏可能具有欺骗性吗？
问候
I'm trying to optimize this mysql query using EXPLAIN. Can somebody please help me out over here?
EXPLAIN SELECT * FROM keyword
WHERE keyword LIKE "panasonic%"
AND keyword != "panasonic"
AND price < 3230 AND price > 3370
ORDER BY price DESC
LIMIT 99
Basically I want to find out the keywords which start with "some keyword" but don't exactly match it, and whose price is not in some particular range. And oh, I have to get them in descending order of price (which is causing the problem here).
Explain Output:
id: 1
select_type: SIMPLE
table: keyword
type: range
possible_keys: PRIMARY, keyword_price, price_keyword
key: keyword_price
key_len: 765
ref: NULL
rows: 24
Extra: Using where; Using filesort
Indexes
Key_name : column_names
PRIMARY: keyword
keyword_price: keyword, price
price_keyword: price, keyword
Now if I give the hint to use index, and change the query to
EXPLAIN SELECT * FROM keyword USE INDEX (price_keyword)
WHERE keyword LIKE "panasonic%"
AND keyword != "panasonic"
AND price < 3230 AND price > 3370
ORDER BY price DESC
LIMIT 99
Explain Output changes to
id: 1
select_type: SIMPLE
table: keyword
type: index
possible_keys: price_keyword
key: price_keyword
key_len: 790
ref: NULL
rows: 1043044 (WHAT THE ????)
Extra: Using where
The explain output shows the number of rows has increased exponentially but the "Using filesort" has gone.
Which query is better in this case? Can that "rows examined" column be deceptive?
Regards
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在这种特定情况下，我希望第一个查询更好。
选项 A) 提取以指定关键字开头的约 24 行，并按价格对它们进行排序（请注意，文件排序是一种算法，并不表示排序是在磁盘上完成的），
选项 B) 按价格顺序提取约 100 万行，并检查所有行以查看它们是否满足关键字约束（直到获得 99 行）。
话虽如此，如果您的关键字是
's'，或者您的数据不统一，则更好的选择可能相反。无论如何，我认为对于大多数应用来说，选项 A) 是获胜者。确实。
EXPLAIN中检查的行是估计值。 之类的输出最好检查诸如
SHOW SESSION STATUS LIKE 'handler_%'，以了解您实际提取了多少行。
In this specific case, I expect the first query to be better.
Option A) Pull the ~24 rows that start with the indicated keyword and sorting them by price (note that filesort is an algorithm, and doesn't indicate that the sort is done on disk),
Option B) Pull ~1 million rows in order of price and checking them all to see if they meet the keyword constraint (until you get 99 of them).
Having said that, if your keyword was
's', or your data is not uniform, the better option could reverse. At any rate, I imagine that for most applications, Option A) is the winner.Definitely. The rows examined in the
EXPLAINis an estimate. Best to check the output of something likeSHOW SESSION STATUS LIKE 'handler_%'to see how many rows you are actually pulling.