MySQL查询变慢，并添加了另一个AND子句

Alexey 发表于 Dev

阿列克谢

我在Symfony2项目中使用Doctrine2，ORM产生以下查询：

SELECT COUNT(p0_.id) AS sclr0 
FROM Purchase p0_ 
LEFT JOIN USER u1_ ON p0_.user_id = u1_.id 
LEFT JOIN Config c2_ ON u1_.id = c2_.id AND (c2_.deletedAt IS NULL) 
WHERE (u1_.organization_id = 7) AND (p0_.deletedAt IS NULL);

它返回176336，并且服务器需要30秒钟才能启动它。

如果我删除AND (p0_.deletedAt IS NULL)条款，则需要0.1秒。为什么AND子句（而不是OR！）使resutset总是<=，如果没有子句，它会使速度减慢如此之快？为了证明这一点，这是SELECT PROFILES;使用AND查询后输出的屏幕截图：

探查器输出WITH AND

这是完全相同的查询的输出，但没有AND (p0_.deletedAt IS NULL)，请注意，下面附加了更新的查询（请参阅Query_ID = 5）：

探查器输出无与

为确保这不是缓存问题，我启动了另一个相同的查询AND (p0_.deletedAt IS NULL)，但未添加SQL_NO_CACHE（请参阅Query_ID = 8）

在Windowsmysql Ver 14.14 Distrib 5.6.14, for Win64 (x86_64)和Ubuntu上均可重现5.5.37-0ubuntu0.12.04.1-log。这是在功能更强大的Linux服务器上进行的相同实验：

+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                                                                                                                         |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.00025925 | SELECT SQL_NO_CACHE COUNT(p0_.id) AS sclr0
FROM Purchase p0_
LEFT JOIN USER u1_ ON p0_.user_id = u1_.id
LEFT JOIN Config c2_ ON u1_.id = c2_.id AND (c2_.deletedAt IS NULL)
WHERE (u1_.organization_id = 7)                               |
|        2 | 0.04896325 | SELECT SQL_NO_CACHE COUNT(p0_.id) AS sclr0
FROM Purchase p0_
LEFT JOIN `User` u1_ ON p0_.user_id = u1_.id
LEFT JOIN Config c2_ ON u1_.id = c2_.id AND (c2_.deletedAt IS NULL)
WHERE (u1_.organization_id = 7)                             |
|        3 | 8.35424850 | SELECT SQL_NO_CACHE COUNT(p0_.id) AS sclr0  FROM Purchase p0_  LEFT JOIN `User` u1_ ON p0_.user_id = u1_.id  LEFT JOIN Config c2_ ON u1_.id = c2_.id AND (c2_.deletedAt IS NULL)  WHERE (u1_.organization_id = 7) AND (p0_.deletedAt IS NULL) |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

与AND (p0_.deletedAt IS NULL)和的查询INNER JOIN大约需要相同的时间：带有INNER JOIN的Prilfer输出

这是EXPLAIN带有和不带有的输出AND：

用AND解释

不使用AND进行解释

表结构。

购买：

CREATE TABLE `purchase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `credit_card_user_id` int(11) DEFAULT NULL,
  `requestor_id` int(11) DEFAULT NULL,
  `organization_supplier_id` int(11) DEFAULT NULL,
  `organization_id` int(11) DEFAULT NULL,
  `deletedAt` datetime DEFAULT NULL,
  `status` int(11) NOT NULL,
  `number` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `amount` double NOT NULL,
  `xml` longtext COLLATE utf8_unicode_ci NOT NULL,
  `comments` longtext COLLATE utf8_unicode_ci,
  `payload_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `error` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shipping_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `shipping_email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `shipping_phone_name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shipping_phone` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shipping_fax` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shipping_deliver_to_1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `shipping_deliver_to_2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `is_payment_info_amount_percent` tinyint(1) DEFAULT NULL,
  `temporary_old_id` int(11) DEFAULT NULL,
  `user_address_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `number_organization` (`number`,`organization_id`),
  KEY `IDX_9861B36DA76ED395` (`user_id`),
  KEY `IDX_9861B36DD68143A9` (`credit_card_user_id`),
  KEY `IDX_9861B36DA7F43455` (`requestor_id`),
  KEY `IDX_9861B36D1A81C9F7` (`organization_supplier_id`),
  KEY `IDX_9861B36D32C8A3DE` (`organization_id`),
  KEY `IDX_9861B36D52D06999` (`user_address_id`),
  CONSTRAINT `FK_9861B36D1A81C9F7` FOREIGN KEY (`organization_supplier_id`) REFERENCES `organizationsupplier` (`id`),
  CONSTRAINT `FK_9861B36D32C8A3DE` FOREIGN KEY (`organization_id`) REFERENCES `organization` (`id`),
  CONSTRAINT `FK_9861B36D52D06999` FOREIGN KEY (`user_address_id`) REFERENCES `useraddress` (`id`),
  CONSTRAINT `FK_9861B36DA76ED395` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_9861B36DA7F43455` FOREIGN KEY (`requestor_id`) REFERENCES `requestor` (`id`),
  CONSTRAINT `FK_9861B36DD68143A9` FOREIGN KEY (`credit_card_user_id`) REFERENCES `creditcarduser` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=359199 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

用户

CREATE TABLE `User` (
  `id` int(11) NOT NULL,
  `organization_id` int(11) DEFAULT NULL,
  `username` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `salt` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `roles` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:json_array)',
  `firstname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `lastname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `middlename` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `is_enabled` tinyint(1) DEFAULT NULL,
  `login_attempts` int(11) NOT NULL,
  `employee_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `password_changed_at` datetime NOT NULL,
  `allow_all_suppliers` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_2DA1797732C8A3DE` (`organization_id`),
  CONSTRAINT `FK_2DA1797732C8A3DE` FOREIGN KEY (`organization_id`) REFERENCES `organization` (`id`),
  CONSTRAINT `FK_2DA17977BF396750` FOREIGN KEY (`id`) REFERENCES `config` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

设定档

CREATE TABLE `config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deletedAt` datetime DEFAULT NULL,
  `discr` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5041 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

马库斯·亚当斯（Marcus Adams）

对于带有AND子句的查询，MySQL必须查找实际的购买记录以检索该deletedAt值。根据EXPLAIN结果，对于每1968个用户记录，这种情况发生35次。

没有AND，MySQL可以使用IDX_9861B36DA76ED395索引来检索购买user_id和id（用于COUNT），因此它不必查找实际购买记录以获取任何其他信息。

添加以下多列索引可能会改善这种情况： purchase (user_id, deletedAt)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。

编辑于 2021-03-21

我来说两句

0 条评论

登录后参与评论

上一篇：getPaginationCount（）函数在Laravel的雄辩ORM中不起作用

MySQL查询变慢，并添加了另一个AND子句

MySQL查询变慢，并添加了另一个AND子句

Qt Creator Windows 10 - “使用 jom 而不是 nmake”不起作用

使用next.js时出现服务器错误，错误：找不到react-redux上下文值；请确保组件包装在<Provider>中

SQL Server中的非确定性数据类型

Swift 2.1-对单个单元格使用UITableView

如何避免每次重新编译所有文件？

在同一Pushwoosh应用程序上Pushwoosh多个捆绑ID

Hashchange事件侦听器在将事件处理程序附加到事件之前进行侦听

应用发明者仅从列表中选择一个随机项一次

在 Avalonia 中是否有带有柱子的 TreeView 或类似的东西？

HttpClient中的角度变化检测

在Wagtail管理员中，如何禁用图像和文档的摘要项？

如何了解DFT结果

Camunda-根据分配的组过滤任务列表

错误：找不到存根。请确保已调用spring-cloud-contract：convert

为什么此后台线程中未处理的异常不会终止我的进程？

构建类似于Jarvis的本地语言应用程序

使用分隔符将成对相邻的数组元素相互连接

您如何通过 Nativescript 中的 Fetch 发出发布请求？

通过iwd从Linux系统上的命令行连接到wifi（适用于Linux的无线守护程序）

使用React / Javascript在Wordpress API中通过ID获取选择的多个帖子/页面

使用 text() 獲取特定文本節點的 XPath