如何加速条件mysql查询

v8wbuo2f 于 2021-06-20 发布在 Mysql

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

查询是：

SELECT RP.pID
  , (IF(RI.p01 = 54, 1, 0)
     + IF(RI.p02 = 16, 1, 0)
     + IF(RI.p03 = 54, 1, 0)
     + IF(RI.p04 = 92, 1, 0)
     + IF(RI.p05 = 34, 1, 0)
     + IF(RI.p06 = 51, 1, 0)
     + IF(RI.p07 = 62, 1, 0)
     + IF(RI.p08 = 98, 1, 0)
     + IF(RI.p09 = 14, 1, 0)
     + IF(RI.p10 = 25, 1, 0)
     + IF(RI.p11 = 34, 1, 0)
     + IF(RI.p12 = 67, 1, 0)
     + IF(RI.p13 = 81, 1, 0)
     + IF(RI.p14 = 29, 1, 0)
     + IF(RI.p15 = 24, 1, 0)
     + IF(RI.p16 = 45, 1, 0)
     + IF(RI.p17 = 72, 1, 0)
     + IF(RI.p18 = 86, 1, 0)
     + IF(RI.p19 = 25, 1, 0)
     + IF(RI.p20 = 95, 1, 0)
     + IF(RI.p21 = 92, 1, 0)
     + IF(RI.p22 = 31, 1, 0)
     + IF(RI.p23 = 24, 1, 0)
     + IF(RI.p24 = 78, 1, 0)) AS ATTP
FROM RP
LEFT JOIN RI
    ON RP.rID = RI.rID
    AND RP.nID = '1'
WHERE RI.p25 > ((RI.p27 + RI.p26) * 8)
ORDER BY ATTP DESC LIMIT 1000000;

在实际情况中，有27个这样的ifs，查询需要25-35秒才能完成。
我在其中检查这些条件的表是相关的，每天增长约50000条记录。
为了加速查询的哪一部分，我在inr上有主索引，但是如何加速这些IF呢？
执行计划：

表结构：

CREATE TABLE `RI` (
  `rID` mediumint(5) unsigned NOT NULL AUTO_INCREMENT,
  `p01` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p02` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p03` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p04` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p05` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p06` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p07` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p08` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p09` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p10` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p11` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p12` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p13` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p14` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p15` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p16` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p17` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p18` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p19` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p20` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p21` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p22` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p23` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p24` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p25` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p26` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p27` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `ils` mediumblob NOT NULL,
  PRIMARY KEY (`rID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_lithuanian_ci;

CREATE TABLE `RP` (
  `pID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `rID` mediumint(8) unsigned NOT NULL,
  `nID` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`pID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_lithuanian_ci;

mysql

来源：https://stackoverflow.com/questions/52190807/how-to-speed-up-conditional-mysql-query

2条答案

按热度按时间

1cosmwyk1#

现在，当我们得到真正的查询和ddl时，它使帮助您变得更加容易了！：）
尝试创建以下索引：

CREATE INDEX IX_RP_nID_rID ON RP (nID, rID);

这将消除目前正在进行的完全rp表扫描。这应该会大大促进经济增长，但仍有改进的空间。
而且，我认为你的 LEFT JOIN 表现为 INNER JOIN 因为你还有一个过滤条件 WHERE 条款。
我看到innodb让你索引表达式。如果总是查询完全相同的列，可以尝试在ri表上创建索引，该索引将在select语句中包含rid、p25（（p27+p.26）*8）和完整表达式。从技术上讲，这应该是一种持续的计算。
但是这个解决方案将在mysql更新之后工作https://dev.mysql.com/doc/refman/8.0/en/create-index.html

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

fdbelqdn2#

解决方法1：索引所有4列a、b、c和d将有助于加快处理速度。
修正2：可能是添加一个触发器，在插入或更新值时更新同一表中的另一列将解决问题。
这两个修复都是临时的请提供更多关于问题陈述的信息，说明为什么需要if's in查询，希望能有所帮助

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