本地mysql查询和生产服务器查询执行时间之间的差异

kkih6yb8 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(370)

我正在建立一个网站，使用大量的数据库查询，所以我有点担心这可能会发生。
所以，这里的问题是，我有几个查询使用了很多 JOIN s和一些表有几千个条目，而其他一些表有大约20-30万个条目。我有一个网站放缓的经验，我不得不优化一些查询。
问题是，在本例中，在我的本地计算机上，使用这些查询的特定部分需要大约2.5秒的时间来加载，并将网络限制作为常规wi-fi启用。使用良好的wi-fi，加载大约需要1.3秒。
在我的生产服务器上，它是digitalocean上的一个虚拟机，大约需要5分钟！使用完全相同的查询加载完全相同的内容。现在我不是Maven了，但我的电脑并没有比digitalocean上的生产服务器快120倍。
我的笔记本电脑有以下规格：英特尔酷睿i7-6700hq，16gb的ddr4ram，服务器运行在5400rpm硬盘上，它甚至不在我的ssd驱动器上，那只是mysql引擎的所在。
生产服务器最初是一个基本do示例，有1gb的ram和1个vcpu。我认为它可能需要一些增强，所以我临时升级到有2vcpus和2GB的ram，但没有什么区别。除了使用大量连接的部分外，其他部分的加载速度非常快。
现在，我不是Maven，但我的电脑速度不是服务器的120倍，而且它还运行一系列其他进程。我确实有一个geforce1070m，但我不认为这会影响mysql的性能。
我试着把问题一分为二 JOIN s，然后执行多个简单的查询，将附加信息添加到我的信息数组中，但是我遇到了另一个问题。即使在我的电脑上，这个逻辑也被卡住了4-5秒，然后它突然加载了内容。
下面是chrome的network标签的截图，显示了时差。正如你所看到的，除了初始加载，其他加载都非常快。我很肯定这是mysql的问题，但区别是惊人的。我想尝试加载在digitalocean上6vCPU的16gb内存示例上的站点，看看它是否与内存/cpu有关，但我不确定我的客户是否愿意为这种虚拟机每月支付80美元或更多。
我在想的一个可能的解决办法是把 Localidades 以及 Asentamientos 表（它们都有大约20-30万个条目）分为32个较小的表，每个墨西哥州一个表，每个州都有一个特殊的函数来引用另一个表，但我认为这既不是可伸缩的，也不是好的做法。
我还在下面添加了查询的计算成本。
我的本地计算机有：
windows 10 1803
apache/2.4.25（win64）
MySQL5.7.23版本
我的生产服务器有：
ubuntu 18.04.1 lts版
apache/2.4.29（ubuntu）
5.7.24-0ubuntu0.18.04.1条
你知道我能做些什么来解决这个问题吗？
生成的查询如下：

SELECT 
    `Propiedades`.*,
    `Propiedades`.`directorio` AS `main_dir`,
    DATEDIFF(Propiedades.fecha_finalizacion,
            '2018-12-02 11:11:49') AS quedan,
    `OperacionesPorPropiedad`.*,
    `Operaciones`.`nombre_operacion`,
    `Operaciones`.`nombre_operacion_slug`,
    `TiposDePropiedades`.*,
    `FotografiasPorPropiedad`.*,
    `Empresas`.`nombre_empresa`,
    `Estados`.*,
    `Municipios`.*,
    `Localidades`.*,
    `Asentamientos`.*,
    `Clientes`.`nombres`,
    `Clientes`.`apellidos`,
    `Clientes`.`email`,
    `TiposDeClientes`.*
FROM
    `Propiedades`
        JOIN
    `OperacionesPorPropiedad` ON `OperacionesPorPropiedad`.`id_propiedad` = `Propiedades`.`id_propiedad`
        JOIN
    `Operaciones` ON (`Operaciones`.`id_operacion` = `OperacionesPorPropiedad`.`id_operacion`
        AND `OperacionesPorPropiedad`.`id_propiedad` = Propiedades.id_propiedad)
        JOIN
    `TiposDePropiedades` ON `TiposDePropiedades`.`id_tipo` = `Propiedades`.`id_tipo`
        JOIN
    `FotografiasPorPropiedad` ON (`FotografiasPorPropiedad`.`id_propiedad` = `Propiedades`.`id_propiedad`
        AND `FotografiasPorPropiedad`.`orden` = 1)
        JOIN
    `Empresas` ON `Empresas`.`id_empresa` = `Propiedades`.`id_empresa`
        JOIN
    `Estados` ON `Estados`.`id_estado` = `Propiedades`.`id_estado`
        LEFT OUTER JOIN
    `Municipios` ON `Municipios`.`id_municipio` = `Propiedades`.`id_municipio`
        LEFT OUTER JOIN
    `Localidades` ON `Localidades`.`id_localidad` = `Propiedades`.`id_localidad`
        LEFT OUTER JOIN
    `Asentamientos` ON `Asentamientos`.`id_asentamiento` = `Propiedades`.`id_asentamiento`
        JOIN
    `Clientes` ON `Clientes`.`id_cliente` = `Empresas`.`id_cliente`
        JOIN
    `TiposDeClientes` ON (`Clientes`.`id_tipo_cliente` = `TiposDeClientes`.`id_tipo_cliente`
        AND `Clientes`.`id_cliente` = `Empresas`.`id_cliente`)
WHERE
    `Propiedades`.`id_estatus_propiedad` = 1
GROUP BY `Propiedades`.`id_propiedad`
ORDER BY FIELD(`Propiedades`.`destacada`, '1', '0') , FIELD(`Clientes`.`id_tipo_cliente`, 1, 2, 3) , RAND()
LIMIT 24

mysql query-performance query-optimization mysql-slow-query-log

来源：https://stackoverflow.com/questions/53582793/difference-between-local-mysql-query-and-production-server-query-execution-times

2条答案

按热度按时间

gv8xihay1#

这能给你一个合理的24排吗？还是依赖于从其他表中筛选？

WHERE  P.`id_estatus_propiedad` = 1
    ORDER BY  FIELD(P.`destacada`, '1', '0') ,
              FIELD(C.`id_tipo_cliente`, 1, 2, 3) ,
              RAND()
    LIMIT  24

如果是，则考虑以下事项：
您当前的查询是从许多表中拖出整行，然后洗牌，最后只传递24行。
一个更好的方法是计算出哪24个，然后去了解细节：

SELECT lots-of-stuff
    FROM ( SELECT id_propiedad
               FROM Propiedades AS P1
               JOIN ...   -- as few as needed to get to Clientes
               JOIN  `Clientes` AS C1  ON C1.`id_cliente` = Em.`id_cliente`
               WHERE  P1.`id_estatus_propiedad` = 1
               ORDER BY  FIELD(P1.`destacada`, '1', '0') ,
                         FIELD(C1.`id_tipo_cliente`, 1, 2, 3) ,
                         RAND()
               LIMIT  24
         ) AS x
    JOIN  `Propiedades` AS P  ON P.id_propiedad = x.id_propiedad
    JOIN  `OperacionesPorPropiedad` AS OP  ON OP.`id_propiedad` = P.`id_propiedad`
    JOIN  `Operaciones` AS O  ON (O.`id_operacion` = OP.`id_operacion` ...
    ...
    -- no WHERE, GROUP BY, or LIMIT, but repeat the ORDER BY:
    ORDER BY  FIELD(P.`destacada`, '1', '0') ,
              FIELD(C.`id_tipo_cliente`, 1, 2, 3) , RAND()

回到性能差异的问题上。。。
你的个人电脑对你来说有更大的价值 innodb_buffer_pool_size 而不是云中的小虚拟机？
您正在从十几个表的许多行中获取所有列。
您（当前）首先收集大量的潜在输出行，然后使用 GROUP BY 消除重复，最后 LIMITing 只有24岁。临时表的大小可能很大气胀泄气综合征 JOIN 加 GROUP BY .
你可能有 TEXT 其中的一些列 * 列列表；这加剧了临时表的问题。
这些结合起来会导致快速/慢速性能。我的建议，如果可行的话，就排除了大部分。
也 FotografiasPorPropiedad 需要 INDEX(id_propiedad, orden) （按任意顺序）。

赞(0）回复(0）举报 2021-06-18

py49o6xq2#

抱歉耽误了你们的时间伙计们。。。这是一个新手的错误，在导入数据库时我没有阅读错误消息。
当我生成mysqldump时，一些表名被错误地生成，只有小写字母，这导致导入时出错。
因为所有的索引都在错误的指令之后，所以它们从来没有被执行过，所以我基本上做了无索引的全表扫描，这就是为什么加载结果要花很长时间的原因。
我更正了我的sql文件，再次创建了数据库，它像一个魅力。抱歉浪费了你们的时间。
ps：实际上我把服务器的内存提高到了16gb和6vcpus，这一点都没有区别。

赞(0）回复(0）举报 2021-06-18

我来回答

本地mysql查询和生产服务器查询执行时间之间的差异

2条答案

相关问题

热门标签

最新问答