MYSQL 查询锁定服务器 [英] MYSQL Query locking up server
问题描述
尝试执行此查询时，我的 mysql 服务器 CPU 使用率达到 100%，并且页面停止.我在(Client_Code、Date_Time、Time_Stamp、Activity_Code、Employee_Name、ID_Transaction)上设置了一个索引，它似乎没有帮助.接下来我可以采取哪些步骤来解决这个问题?如果有关系的话，数据库上也已经有一个索引.谢谢
When trying to execute this query my mysql server cpu usage goes to 100% and the page just stalls. I setup an index on (Client_Code, Date_Time, Time_Stamp, Activity_Code, Employee_Name, ID_Transaction) it doesn't seem to help. What steps can I go about next to fix this issue? Also there is already one index on the database if that matters any. Thanks
这是这个查询的作用
数据库信息
ID_Transaction | Client_Code | Employee_Name | Date_Time |Time_Stamp| Activity_Code
1 | 00001 | Eric | 11/15/10| 7:30AM | 00023
2 | 00001 | Jerry | 11/15/10| 8:30AM | 00033
3 | 00002 | Amy | 11/15/10| 9:45AM | 00034
4 | 00003 | Jim | 11/15/10| 10:30AM | 00063
5 | 00003 | Ryan | 11/15/10 | 12:00PM | 00063
6 | 00003 | bill | 11/14/10 | 1:00pm | 00054
7 | 00004 | Jim | 11/15/10 | 1:00pm | 00045
8 | 00005 | Jim | 11/15/10| 10:00 AM| 00045
查询获取上面的信息并像这样计算它.通过每个 client_code 的最新条目.在这种情况下，查询将如下所示.php之后.
The query takes the info above and counts it like so. By the most recent entry for each client_code. In this case the query would look like this. After php.
Jerry = 1
2 | 00001 | Jerry | 11/15/10| 8:30AM | 00033
Amy = 1
3 | 00002 | Amy | 11/15/10| 9:45AM | 00034
Ryan = 1
5 | 00003 | Ryan | 11/15/10 | 12:00PM | 00063
Jim = 2
7 | 00004 | Jim | 11/15/10 | 1:00pm | 00045
8 | 00005 | Jim | 11/15/10| 10:00 AM| 00045
$sql = "SELECT m.Employee_Name, count(m.ID_Transaction)
FROM ( SELECT DISTINCT Client_Code FROM Transaction)
md JOIN Transaction m ON
m.ID_Transaction = ( SELECT
ID_Transaction FROM Transaction mi
WHERE mi.Client_Code = md.Client_Code AND Date_Time=CURdate() AND Time_Stamp!='' AND
Activity_Code!='000001'
ORDER BY m.Employee_Name DESC, mi.Client_Code DESC, mi.Date_Time DESC,
mi.ID_Transaction DESC LIMIT 1 )
group by m.Employee_Name";
有没有更好的方法来编写这个查询，这样它就不会让我的系统陷入困境?该查询对 10 个数据库条目运行良好，但当数据库有 300,000 个条目时它会锁定我的服务器.
Is there a better way to write this query so it doesnt bog down my system? The query works fine with 10 database entries but it locks my server up when the database has 300,000 entries.
谢谢埃里克
+----+--------------------+-------------+--------+------------------------+--------------+---------+----------------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------------+--------+------------------------+--------------+---------+----------------+------+----------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 8 | 100.00 | Using temporary; Using filesort |
| 1 | PRIMARY | m | index | [NULL] | search index | 924 | [NULL] | 21 | 100.00 | Using where; Using index; Using join buffer |
| 3 | DEPENDENT SUBQUERY | mi | ref | search index,secondary | search index | 18 | md.Client_Code | 3 | 100.00 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | Transaction | index | [NULL] | secondary | 918 | [NULL] | 21 | 38.10 | Using index |
+----+--------------------+-------------+--------+------------------------+--------------+---------+----------------+------+----------+----------------------------------------------+
推荐答案
您肯定会想要进行连接而不是子选择.
You'll definitely want to do a join instead of a sub select.
另外，您正在查看多少条记录?分页和使用限制是不可能的吗?
Also, how many records are you viewing? Is pagination and using limit out of the question?
如果您将使用内部/外部连接修改的初始查询设置为视图并且它不会崩溃，那么您将更近一步.设置好视图后，您将能够使用更简单的 select 语句 - 可能需要分页.
If you set up your initial query modified with inner/outer joins as a view and it doesn't crash, you'll be one step closer. Once the view is set up, you'll be able to use a much less complicated select statement - potentially paginated.
这篇关于MYSQL 查询锁定服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
