mysql - 在mysql上优化查询

Question

我有一个查询，当不在内存中时，它运行得非常慢（15 20秒），而在内存时（2s-0.6s）时运行得很快

select count(distinct(concat(conexiones.tMacAdres,date_format(conexiones.fFecha,'%Y%m%d')))) as Conexiones,
                    sum(if(conexiones.tEvento='megusta',1,0)) as MeGusta,sum(if(conexiones.tEvento='megusta',conexiones.nAmigos,0)) as ImpactosMeGusta,
                    sum(if(conexiones.tEvento='checkin',1,0)) as CheckIn,sum(if(conexiones.tEvento='checkin',conexiones.nAmigos,0)) as ImpactosCheckIn,
                    min(conexiones.fFecha) Fecha_Inicio, now() Fecha_fin,datediff(now(),min(conexiones.fFecha)) as dias
                    from conexiones, instalaciones
                    where  conexiones.idInstalacion=instalaciones.idInstalacion and conexiones.idInstalacion=190
                        and (fFecha between '2014-01-01 00:00:00' and '2016-06-18 23:59:59')
                    group by instalaciones.tNombre
                    order by instalaciones.idCliente

这是表SCHEMAS：
安装了1332行：

CREATE TABLE `instalaciones` (
  `idInstalacion` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idCliente` int(10) unsigned DEFAULT NULL,
  `tRouterSerial` varchar(50) DEFAULT NULL,
  `tFacebookPage` varchar(256) DEFAULT NULL,
  `tidFacebook` varchar(64) DEFAULT NULL,
  `tNombre` varchar(128) DEFAULT NULL,
  `tMensaje` varchar(128) DEFAULT NULL,
  `tWebPage` varchar(128) DEFAULT NULL,
  `tDireccion` varchar(128) DEFAULT NULL,
  `tPoblacion` varchar(128) DEFAULT NULL,
  `tProvincia` varchar(64) DEFAULT NULL,
  `tCodigoPosta` varchar(8) DEFAULT NULL,
  `tLatitud` decimal(15,12) DEFAULT NULL,
  `tLongitud` decimal(15,12) DEFAULT NULL,
  `tSSID1` varchar(40) DEFAULT NULL,
  `tSSID2` varchar(40) DEFAULT NULL,
  `tSSID2_Pass` varchar(40) DEFAULT NULL,
  `fSincro` datetime DEFAULT NULL,
  `tEstado` varchar(10) DEFAULT NULL,
  `tHotspot` varchar(10) DEFAULT NULL,
  `fAlta` datetime DEFAULT NULL,
  PRIMARY KEY (`idInstalacion`),
  UNIQUE KEY `tRouterSerial` (`tRouterSerial`),
  KEY `idInstalacion` (`idInstalacion`)
) ENGINE=InnoDB AUTO_INCREMENT=1332 DEFAULT CHARSET=utf8;

有2370365行的Conexiones

CREATE TABLE `conexiones` (
  `idConexion` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idInstalacion` int(10) unsigned DEFAULT NULL,
  `idUsuario` int(11) DEFAULT NULL,
  `tMacAdres` varchar(64) DEFAULT NULL,
  `tUsuario` varchar(128) DEFAULT NULL,
  `tNombre` varchar(64) DEFAULT NULL,
  `tApellido` varchar(64) DEFAULT NULL,
  `tEmail` varchar(64) DEFAULT NULL,
  `tSexo` varchar(20) DEFAULT NULL,
  `fNacimiento` date DEFAULT NULL,
  `nAmigos` int(11) DEFAULT NULL,
  `tPoblacion` varchar(64) DEFAULT NULL,
  `fFecha` datetime DEFAULT NULL,
  `tEvento` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`idConexion`),
  KEY `idInstalacion` (`idInstalacion`),
  KEY `tMacAdress` (`tMacAdres`) USING BTREE,
  KEY `fFecha` (`fFecha`),
  KEY `idUsuario` (`idUsuario`),
  KEY `insta_fecha` (`idInstalacion`,`fFecha`)
) ENGINE=InnoDB AUTO_INCREMENT=2370365 DEFAULT CHARSET=utf8;

这是解释

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  instalaciones   const   PRIMARY,idInstalacion   PRIMARY 4   const   1   
1   SIMPLE  conexiones  ref idInstalacion,fFecha,insta_fecha    idInstalacion   5   const   110234  "Using where"

谢谢 ！

（已编辑）

显示表格状态，例如“ conexiones”

  Name  Engine  Version Row_format  Rows    Avg_row_length  Data_length Max_data_length Index_length    Data_free   Auto_increment  Create_time Update_time Check_time  Collation   Checksum    Create_options  Comment
 conexiones InnoDB  10  Compact 2305296 151 350060544   0   331661312   75497472    2433305 28/06/2016 22:26    NULL    NULL    utf8_general_ci NULL        

Answer 1

这就是为什么它这么慢的原因。我将以可能的加速结束。

首先请

SELECT COUNT(*) FROM conexiones
    WHERE idInstalacion=190
      and fFecha >= '2014-01-01'
      and fFecha  < '2016-06-19

为了查看我们正在处理多少行。解释建议110234，但这仅是粗略的估计。

假设查询中涉及到11万行 conexiones，并假设这些行（按时间顺序）按 fFecha插入（大约），则...


有很多行要处理，并且
它们分散在磁盘上的表周围，因此
该查询需要大量的I / O，除非将其缓存。


让我们进一步检查我最近的要求...您有多少RAM？ innodb_buffer_pool_size的值是什么？它应该大约是可用RAM的70％。如果您的RAM少于4GB，请使用较低的百分比。

假设 conexiones太大而无法在“ buffer_pool”中“缓存”，我们需要找到一种减少I / O的方法。

idInstalacion有1332个不同的值。也许您每隔几分钟/小时就在 conexiones中插入1332行？由于 PRIMARY KEY仅仅是 AUTO_INCREMENT，因此这些行将被“追加”到表的末尾。

现在让我们看一下 idInstalacion=190行的位置。每隔1332（约）行将出现一个新的。那意味着他们分散了。这意味着（可能）在同一块中没有两行（InnoDB中为16KB）。这意味着110234将位于110234不同的块中。大约2GB。如果buffer_pool小于该值，则将有I / O。即使比这更大，也需要处理大量数据。

但是该怎么办呢？如果我们可以将 =190行安排在表中连续，则2GB可能会降至20MB，这是一个更易于管理和缓存的大小。但是怎么办呢？通过更改 PRIMARY KEY。

PRIMARY KEY(idInstalacion, fFecha, idConexion),
INDEX(idConexion)

和 DROP以 idInstalacion或 idConexion开头的任何其他索引。解释：


由于PK与数据“聚集”，因此任何连续 idInstalacion=190范围内的所有 fFetcha行在数据中将是连续的。因此，获取一个块将获得约100行-更少的I / O。
PK必须是唯一的。假设 (idInstalacion, fFecha)不是唯一的，我加 idConexion使其唯一。
我添加了 INDEX(idConexion)使 AUTO_INCREMENT开心。


潜在的缺点...由于此更改会重新排列数据的顺序，因此其他查询（包括 INSERTs）可能会变慢。 INSERTs会分散，但不会真正变慢。 1332“热点”将接受新行；可以轻松地缓存许多块。

算术...如果您有旋转的驱动器，那么我希望现有的结构需要110234行大约1102秒（对于SSD而言可能不到110秒）。由于它花费的时间不到20秒，因此我怀疑存在一些缓存（或您有SSD）或110234被严重高估了。我建议的更改应显着减少“最差”时间，并稍微改善“记忆”时间。这种“轻微的改进”来自能够使用PK而不是辅助密钥。

注意事项：由于110234 * 1332距离2370365不远，因此我的大部分数值分析可能都不正确。例如，具有该架构的2370365行可能小于1GB。请提供 SHOW TABLE STATUS LIKE 'conexiones'。

附加物

“服务器的内存为2GB，innodb_buffer_pool_size为5368709120”-要么是错字，要么是可怕的。由于buffer_pool需要驻留在RAM中，因此请勿将buffer_pool设置为5GB。 500MB可能适合2GB的微型RAM。

SHOW TABLE STATUS确认它（数据+索引）不太适合500M，因此您可能会定期遇到500M的I / O绑定查询。

增加RAM和buffer_pool将暂时（直到数据变大）有助于提高性能。

在将其投入生产之前，请测试 ALTER并计时所使用的各种查询的时间：

ALTER TABLE conexiones
    DROP PRIMARY KEY,
    DROP INDEX insta_fecha,
    DROP INDEX idInstalacion,
    PRIMARY KEY(idInstalacion, fFecha, idConexion),
    INDEX(idConexion)

注意： ALTER将需要大约1GB的可用磁盘空间。

计时时，请关闭查询缓存，然后运行两次-第一个可能涉及I / O；第二个可能涉及I / O。第二个是您提到的“内存中”。

修订后的分析：由于较大的表具有300MB的数据和正在使用的一定数量的索引，并假设有500MB的缓冲池，我怀疑某些时候块会从缓冲池中溢出。这非常适合您对查询速度的初步评论。我建议的索引更改应有助于避免速度差异，但可能会损害其他查询的性能。