添加条件后严重的MySQL查询性能问题

Question

我的问题是，我有一个mysql查询运行得非常快（0.3秒），尽管它有大量的左联接和联接列上的一些条件，但当我再添加一个条件时，查询需要花费180秒以上！我理解，条件意味着执行计划必须调整，首先拉出所有潜在的记录，然后在循环中应用条件，但对我来说奇怪的是，没有附加条件的快速查询只返回16行，即使只是用外部查询上的条件包装查询也要花费大量的时间，而您认为它只会添加一个通过16行的附加循环。

如果重要的话，这就是使用Amazon Aurora无服务器，它应该与MySQL5.7一致

下面是查询的样子。可以看到附加条件被注释掉了。（数据库本身的通用表结构目前无法更改，因此请不要建议进行完整的数据库重组）

select 
    e1.entityId as _id,
    v1.Value,
    v2.Value
    v3.Value,
    v4.Value,
    v5.Value,
    v6.Value,
    v7.Value,
    v8.Value,
    v9.Value,
    v10.Value,
    v11.Value,
    v12.Value 
from entity e1
left join val as v1 on (v1.entityId = e1.entityId and v1.attributeId = 1189) 
left join val as v2 on (v2.entityId = e1.entityId and v2.attributeId = 1190) 

left join entity as e2 on e2.entityId = (select entityId from entity where code = v1.Value and type = 88 limit 1) 
left join val as v3 on (v3.entityId = e2.entityId and v3.attributeId = 507) 
left join val as v4 on (v4.entityId = e2.entityId and v4.attributeId = 522) 
left join val as v5 on (v5.entityId = e2.entityId and v5.attributeId = 558)
left join val as v6 on (v6.entityId = e2.entityId and v6.attributeId = 516)
left join val as v7 on (v7.entityId = e2.entityId and v7.attributeId = 518)
left join val as v8 on (v8.entityId = e2.entityId and v8.attributeId = 1384) 
left join val as v9 on (v9.entityId = e2.entityId and v9.attributeId = 659) 
left join val as v10 on (v10.entityId = e2.entityId and v10.attributeId = 519) 
left join val as v11 on (v11.entityId = e2.entityId and v11.attributeId = 1614)

left join entity as e3 on e3.entityId = (select entityId from entity where code = v9.Value and type = 97 limit 1) 
left join val as v12 on (v12.entityId = e3.entityId and v12.attributeId = 661)

where e1.type = 154
and v2.Value = 'foo'
and v5.Value = 'bar'
and v10.Value = 'foo2'
-- and v11`.Value = 'bar2'

order by v3.Value asc;

把它包在这样的东西里还需要很长时间...

select * 
from (
    <query from above>
) sub
where sub.v11 = 'bar2';

我将在“实体”表上摆弄索引来改进执行计划，不管这可能会有什么帮助...但是，有人能解释一下这里发生了什么，我应该在执行计划中看到什么，这会显示出如此糟糕的表现吗？为什么将快速查询包装在子查询中，使外部查询只能循环16行需要很长的时间呢？

编辑：我注意到在慢速查询中，最左边的执行使用的是“68e9145e-43eb-4581-9727-4212be41bef5”(v11)的非唯一键查找（在val.entityId上），而不是其他执行使用的唯一键查找（在entityId和attributeId上的复合索引）。我想这可能是问题的一部分，但为什么它不能像其他地方一样使用综合指数呢？

PS：现在，由于我们知道结果集很小，我们将在nodeJS服务器中的结果集上使用过滤器实现最后一个条件服务器端。

下面是“show CREATE TABLE entity”和“show CREATE TABLE val”的结果。

CREATE TABLE `entity` (
  `entityId` int(11) NOT NULL AUTO_INCREMENT,
  `UID` varchar(64) NOT NULL,
  `type` int(11) NOT NULL,
  `code` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
  PRIMARY KEY (`entityId`),
  UNIQUE KEY `UID` (`UID`),
  KEY `IX_Entity_Type` (`type`),
  CONSTRAINT `FK_Entities_Types` FOREIGN KEY (`type`) REFERENCES `entityTypes` (`typeId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=296138 DEFAULT CHARSET=latin1


CREATE TABLE `val` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `UID` varchar(64) NOT NULL,
  `attributeId` int(11) NOT NULL,
  `entityId` int(11) NOT NULL,
  `Value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
  PRIMARY KEY (`id`),
  UNIQUE KEY `UID` (`UID`),
  UNIQUE KEY `idx_val_entityId_attributeId` (`entityId`,`attributeId`),
  KEY `IX_val_attributeId` (`attributeId`),
  KEY `IX_val_entityId` (`entityId`)
) ENGINE=InnoDB AUTO_INCREMENT=2325375 DEFAULT CHARSET=latin1

Answer 1

请提供show CREATE table。

我希望看到这些综合指数：

`val`: (entityId, attributeId)   -- order is not critical

唉，因为code是longtext，所以对于entity:index（type,code,entityId)是不可能的。因此,这将不是很有效：

        SELECT  entityId
            from  entity
            where  code = v9.Value
              and  type = 97
            limit  1

我看到limit有一个order by--您关心您得到的是哪个值吗？

也许这样写更好

    WHERE EXISTS ( SELECT 1 FROM entity
                WHERE entityID = e3.entityID
                  AND code     = v9.Value
                  AND type = 97 )

（您确定E3和V9的混合使用吗？）

正在包装...

这将强制左联接变为联接。并且它摆脱了当时内部的顺序。

那么优化器可能会决定最好从68E9145E-43EB-4581-9727-4212BE41BEF5开始，我将其称为VAL AS V11:

JOIN val AS v11 ON (v11.entityId = e2.id
             and  v11.attributeId = 1614)
             AND  v11.Value = 'bar2')

如果这是一个EAV表，那么它所做的就是验证[，1514]是否具有值'bar2'。这似乎不是一个明智的测试。

除了我以前的建议。

我更喜欢解释选择...。

EAV

假设val是一个传统的EAV表，这可能会好得多：

CREATE TABLE `val` (
  `attributeId` int(11) NOT NULL,
  `entityId` int(11) NOT NULL,
  `Value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
  PRIMARY KEY(`entityId`,`attributeId`),
  KEY `IX_val_attributeId` (`attributeId`),
) ENGINE=InnoDB AUTO_INCREMENT=2325375 DEFAULT CHARSET=latin1

这两个ID没有实际用途（除非我遗漏了什么）。如果你因为一个框架而被迫使用它们，那是不幸的。将（entityId,attributeId）提升为PK使获取value的速度更快。

没有任何有用的方法可以在任何索引中包含longtext,因此我前面的一些建议需要更改。