本文介绍了MySQL，查询太慢，如何改进?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用 Workbench 5.2.35 和 MySQL 服务器 5.5 运行查询，并且在 600.516 秒后出现错误错误代码:2013.在查询过程中丢失与 MySQL 服务器的连接"，并且在查询几乎没有变化之后.查询有两个作用:

I'm running a query using Workbench 5.2.35 and a MySQL server 5.5 and I have the error "Error Code: 2013. Lost connection to MySQL server during query" after 600.516 seconds also after little changes in the query. The query has two roles:

选择一种特定类型的记录，其特征是在col1"中具有value1"(从阶段 A 到阶段 B)
删除'col2'中的值与下一个结果'col2'中的值相同的记录(从阶段B到阶段C)

select a particular type of records characterised by having 'value1' in 'col1' (pass from Stage A to Stage B)
remove the records where the value in 'col2' is the same as the value in 'col2' of the next result (pass from Stage B to Stage C)

Stage A             Stage B             Stage C
***************     ***************     ***************
*ID *col1*col2*     *ID *col1*col2*     *ID *col1*col2*
***************     ***************     ***************
*1  * A  * a  *     *3  * C  * a  *     *3  * C  * a  *
*2  * B  * a  *     *7  * C  * f  *     *7  * C  * f  *
*3  * C  * a  *     *8  * C  * f  *     *16 * C  * b  *
*4  * S  * a  *     *9  * C  * f  *     *18 * C  * c  *
*5  * B  * a  *     *16 * C  * b  *
*6  * A  * g  *     *17 * C  * b  *
*7  * C  * f  *     *18 * C  * c  *
*8  * C  * f  *
*9  * C  * f  *
*10 * A  * f  *
*11 * B  * f  *
*12 * D  * f  *
*13 * S  * f  *
*14 * F  * f  *
*15 * F  * f  *
*16 * C  * b  *
*17 * C  * b  *
*18 * C  * c  *

并且是对以下内容的概括:MySQL，选择参数值取决于它在不同行中的值的行

and is a generalisation of:MySQL, select rows where a parameter value depends on the value that it has in a different row

查询是:

SELECT t.id, t.col2, t.col3, t.col4, t.col5 FROM tablename t
WHERE t.id < 1000000
    AND t.col1 = 'value1'
    AND t.col2 <>
    (SELECT col2 FROM tablename
        WHERE col1 = 'value1'
        AND id > t.id
        LIMIT 1);

错误原因

现在，从这篇文章https://serverfault.com/questions/29597/what-does-mysql-error-2013-平均此错误的原因可能是:

有人取消了查询
网络问题导致连接中断
服务器崩溃/死亡
您的连接因 wait_timeout 处于空闲状态并被终止
客户端没有足够快地为 net_wait_timeout 提取数据并被终止

但是由于查询在 600.516 秒处停止，我想在这种情况下的问题是数字 4(超时).

but since the query stops at 600.516 seconds, I guess that the problem in this case is number 4 (timeout).

第一个想法是增加 wait_timeout 时间，但我认为这是由之前的错误触发的:查询不返回任何内容但继续运行.限制 t.id 正好用于在合理有限的子集中测试查询(数据库大约有 2 亿个条目)所以，我认为查询中存在一些问题，特别是在阶段 B 和阶段 C 之间的通道中(上一步是微不足道的)

The first idea would be to increase the wait_timeout time, but I think that this is triggered by a previous error: the query doesn't return anything but continues to run. The limit t.id < 1000000 is used exactly to test the query in a reasonably limited subset (the database has about 200 millions of entries)So, I'm thinking that there is some problem in the query, in particular in the passage between Stage B and Stage C (the previous step is trivial)

对于错误或查询的任何想法将不胜感激.

Any idea for the error or for the query will be much appreciated.

谢谢

这是工作代码，灵感来自最佳答案.命令 DISTINCT 有效，但最后我使用了 GROUP BY 和 ORDER BY 以便以更好的方式呈现结果.

this is the working code, inspired by the best answer. The command DISTINCT work, but finally I used GROUP BY and ORDER BY in order to present the results in a better way.

SELECT id, col1, col2, ..., coln FROM tablename
    WHERE col1 = 'value1'
    AND col2 = 'value2'
    ...
    AND coln = 'valuen'
    GROUP BY col2
    ORDER BY id;

查询太慢

MySQL，查询太慢，如何改进?

问题描述

错误原因

推荐答案