mysql查询执行时间太长

本文介绍了mysql查询执行时间太长的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个查询执行时间太长(4 秒)，即使我查询的所有字段都已编入索引.下面是查询和解释结果.任何想法是什么问题?(执行查询时，mysql CPU 使用率高达 100%

I have a query that is taking way too long to execute (4 seconds) even though all the fields i am querying against are indexed. Below are the query and the explain results. Any ideas what the problem is? (mysql CPU usage shoots up to 100% when executing the query

EXPLAIN SELECT count(hd.did) as NumPo, `hd`.`sid`, `src`.`Name` FROM (`hd`) JOIN `result` ON `result`.`did` = `hd`.`did` JOIN `sf` ON `sf`.`fid` = `hd`.`fid` JOIN `src` ON `src`.`sid` = `hd`.`sid` WHERE `sf`.`tid` = 2 AND `result`.`set` = 'xxxxxxx' GROUP BY `hd`.`sid` ORDER BY `NumPo` DESC LIMIT 10; +----+-------------+--------------+--------+-------------------------+---------+---------+--------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+--------+-------------------------+---------+---------+--------------------------+------+----------------------------------------------+ | 1 | SIMPLE | sf | ref | PRIMARY,type | type | 2 | const | 4 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | hd | ref | PRIMARY,sid,fid | FeedID | 4 | f2.sf.fid | 3 | | | 1 | SIMPLE | result | ALL | resultset | NULL | NULL | NULL | 5322 | Using where; Using join buffer | | 1 | SIMPLE | src | eq_ref | PRIMARY | PRIMARY | 4 | f2.hd.sid | 1 | | +----+-------------+--------------+--------+-------------------------+---------+---------+--------------------------+------+----------------------------------------------+

推荐答案

| 1 | SIMPLE | result | ALL | resultset | NULL | NULL | NULL | 5322 | Using where; Using join buffer |

看起来它没有在最大的表上使用索引.我无法猜测这个查询应该做什么，但看起来你在 result.set 上有一个索引，所以我会尝试向 result.did，看看它是否有帮助.

It looks like it's not using an index on the biggest table. I'm having trouble guessing what this query is supposed to do, but it looks like you have an index on result.set, so I'd try adding one to result.did and see if it helps.