使用子查询的MySQL查询花费的时间比应该长

问题描述：

我一直在试图找出查询中减速的原因。查询原本是DELETE查询，但我一直在使用一个SELECT * FROM使用子查询的MySQL查询花费的时间比应该长

这是有问题的查询

SELECT * FROM table1 
where table1.id IN (
#Per friends suggestion I wrapped the subquery in a subquery (yo dawg) to "cache" it, it works on other queries, but not on this time. 
SELECT id FROM (  
(
    SELECT id FROM (
     SELECT table1.id FROM table1 
     LEFT JOIN table2 ON table2.id = table1.salesperson_id 
     LEFT JOIN table3 ON table3.id = table2.user_id 
     LEFT JOIN table4 ON table3.office_id = table4.id 
     WHERE table1.type = "Snapshot" 
     AND table4.id = 25 OR table4.parent_id =25 
     LIMIT 500 
    ) AS ids) 
) AS moreIds 
)

问题的表是16场演出。
它正在运行的服务器足够强壮不会成为瓶颈。字段id，salesperson_id和类型都被索引。检查它5次。

子查询本身运行速度非常快。子查询：

SELECT id FROM (
     SELECT table1.id FROM table1 
     LEFT JOIN table2 ON table2.id = table1.salesperson_id 
     LEFT JOIN table3 ON table3.id = table2.user_id 
     LEFT JOIN table4 ON table3.office_id = table4.id 
     WHERE table1.type = "Snapshot" 
     AND table4.id = 25 OR table4.parent_id =25 
     LIMIT 500 
    )

在进程列表中，查询处于“发送数据”状态。但工作台表明查询仍在运行。

这里有一个解释查询的SELECT

'1', 'PRIMARY', 'table1', 'index', NULL, 'SALES_FK_ON_SALES_STATE', '5', NULL, '36688459', 'Using where; Using index' 
'2', 'DEPENDENT SUBQUERY', '<derived3>', 'ALL', NULL, NULL, NULL, NULL, '500', 'Using where' 
'3', 'DERIVED', '<derived4>', 'ALL', NULL, NULL, NULL, NULL, '500', '' 
'4', 'DERIVED', 'table4', 'index_merge', 'PRIMARY,IDX_9F61CEFC727ACA70', 'PRIMARY,IDX_9F61CEFC727ACA70', '4,5', NULL, '67', 'Using union(PRIMARY,IDX_9F61CEFC727ACA70); Using where; Using index' 
'4', 'DERIVED', 'table3', 'ref', 'PRIMARY,IDX_C077730FFFA0C224', 'IDX_C077730FFFA0C224', '5', 'hugeDb.table4.id', '381', 'Using where; Using index' 
'4', 'DERIVED', 'table2', 'ref', 'PRIMARY,UNIQ_36E3BDB1A76ED395', 'UNIQ_36E3BDB1A76ED395', '5', 'hugeDb.table3.id', '1', 'Using where; Using index' 
'4', 'DERIVED', 'table1', 'ref', 'SALESPERSON,SALES_FK_ON_SALES_STATE', 'SALES_FK_ON_SALES_STATE', '5', 'hugeDb.table2.id', '115', 'Using where'

这里有SHOW创建表

CREATE TABLE `table4` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `logo_file_id` int(11) DEFAULT NULL, 
    `contact_address_id` int(11) DEFAULT NULL, 
    `billing_address_id` int(11) DEFAULT NULL, 
    `parent_id` int(11) DEFAULT NULL, 
    `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, 
    `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL, 
    `fax` varchar(255) COLLATE utf8_unicode_ci NOT NULL, 
    `contact_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, 
    `active` tinyint(1) NOT NULL, 
    `date_modified` datetime DEFAULT NULL, 
    `date_created` datetime NOT NULL, 
    `license_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL, 
    `list_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, 
    `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL, 
    `routing_address_id` int(11) DEFAULT NULL, 
    `billed_separately` tinyint(1) DEFAULT '0', 
    PRIMARY KEY (`id`), 
    UNIQUE KEY `UNIQ_9F61CEFCA7E1931C` (`logo_file_id`), 
    KEY `IDX_9F61CEFC320EF6E2` (`contact_address_id`), 
    KEY `IDX_9F61CEFC79D0C0E4` (`billing_address_id`), 
    KEY `IDX_9F61CEFC727ACA70` (`parent_id`), 
    KEY `IDX_9F61CEFC40F0487C` (`routing_address_id`), 
    -- CONSTRAINT `FK_9F61CEFC320EF6E2` FOREIGN KEY (`contact_address_id`) REFERENCES `other_irrelevant_table` (`id`), 
    -- CONSTRAINT `FK_9F61CEFC79D0C0E4` FOREIGN KEY (`billing_address_id`) REFERENCES `other_irrelevant_table` (`id`), 
    -- CONSTRAINT `FK_9F61CEFCA7E1931C` FOREIGN KEY (`logo_file_id`) REFERENCES `other_irrelevant_table` (`id`), 
    -- CONSTRAINT `FK_9F61CEFCE346079F` FOREIGN KEY (`routing_address_id`) REFERENCES `other_irrelevant_table` (`id`), 
    CONSTRAINT `FK_9F61CEFC727ACA70` FOREIGN KEY (`parent_id`) REFERENCES `table4` (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=750 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

CREATE TABLE `table3` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `office_id` int(11) DEFAULT NULL, 
    `user_id` int(11) DEFAULT NULL, 
    `active` tinyint(1) NOT NULL, 
    `date_modified` datetime DEFAULT NULL, 
    `date_created` datetime NOT NULL, 
    `profile_id` int(11) DEFAULT NULL, 
    `deleted` tinyint(1) NOT NULL, 
    PRIMARY KEY (`id`), 
    KEY `IDX_C077730FFFA0C224` (`office_id`), 
    KEY `IDX_C077730FA76ED395` (`user_id`), 
    KEY `IDX_C077730FCCFA12B8` (`profile_id`), 
    -- CONSTRAINT `FK_C077730FA76ED395` FOREIGN KEY (`user_id`) REFERENCES `other_irrelevant_table` (`id`), 
    -- CONSTRAINT `FK_C077730FCCFA12B8` FOREIGN KEY (`profile_id`) REFERENCES `other_irrelevant_table` (`id`), 
    CONSTRAINT `FK_C077730FFFA0C224` FOREIGN KEY (`office_id`) REFERENCES `table4` (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=382425 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

CREATE TABLE `table2` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` int(11) DEFAULT NULL, 
    `active` tinyint(1) NOT NULL, 
    `date_modified` datetime DEFAULT NULL, 
    `date_created` datetime NOT NULL, 
    PRIMARY KEY (`id`), 
    UNIQUE KEY `UNIQ_36E3BDB1A76ED395` (`user_id`), 
    CONSTRAINT `FK_36E3BDB1A76ED395` FOREIGN KEY (`user_id`) REFERENCES `table3` (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=174049 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

CREATE TABLE `table1` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `salesperson_id` int(11) DEFAULT NULL, 
    `count_active_contracts` int(11) NOT NULL, 
    `average_initial_price` decimal(12,2) NOT NULL, 
    `average_contract_value` decimal(12,2) NOT NULL, 
    `total_sold` int(11) NOT NULL, 
    `total_active` int(11) NOT NULL, 
    `active` tinyint(1) NOT NULL, 
    `date_modified` datetime DEFAULT NULL, 
    `date_created` datetime NOT NULL, 
    `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
    `services_scheduled_today` int(11) NOT NULL, 
    `services_scheduled_week` int(11) NOT NULL, 
    `services_scheduled_month` int(11) NOT NULL, 
    `services_scheduled_summer` int(11) NOT NULL, 
    `serviced_today` int(11) NOT NULL, 
    `serviced_this_week` int(11) NOT NULL, 
    `serviced_this_month` int(11) NOT NULL, 
    `serviced_this_summer` int(11) NOT NULL, 
    `autopay_account_percentage` decimal(3,2) NOT NULL, 
    `value_per_door` decimal(12,2) NOT NULL, 
    `total_paid` int(11) NOT NULL, 
    `sales_status_summary` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
    `total_serviced` int(11) NOT NULL, 
    `services_scheduled_year` int(11) NOT NULL, 
    `serviced_this_year` int(11) NOT NULL, 
    `services_scheduled_yesterday` int(11) NOT NULL, 
    `serviced_yesterday` int(11) NOT NULL, 
    PRIMARY KEY (`id`), 
    KEY `SALESPERSON` (`type`), 
    KEY `SALES_FK_ON_SALES_STATE` (`salesperson_id`), 
    CONSTRAINT `SALES_FK_ON_SALES_STATE` FOREIGN KEY (`salesperson_id`) REFERENCES `table2` (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=181662521 DEFAULT CHARSET=utf8;

感谢您使用EXPLAIN。另外，在询问SQL优化问题时，还应该为查询中引用的每个表包含“SHOW CREATE TABLE”的输出，以便我们可以知道迄今为止创建的任何索引，以及数据类型和约束。帮助我们帮助你！ –

TIL。谢谢比尔。但我该如何解决这个问题？我会在一瞬间包含创建语句 –

添加了SHOW CREATE TABLES。 –

答

当你在解释见“养SUBQUERY”，它是不缓存的结果子查询。它多次重新执行子查询（最外层查询中的每个不同值都执行一次）。我在解释中看到，最外层的查询正在检查3600万行。所以这可能运行子查询很多，很多次。

这被记录在这里：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

供养SUBQUERY，子查询重新只计算一次针对每组从其外上下文变量的不同的值。对于UNCACHEABLE SUBQUERY，子查询会针对外部上下文的每一行重新评估。为了避免这种

的一种方式是使用一个子查询作为派生表代替作为参数的IN()谓词。这是一种更好的方式来进行像您一样的半连接。

SELECT ... FROM TableA 
WHERE TableA.id IN (SELECT id FROM ...)

应该等于：

SELECT ... FROM TableA 
JOIN (SELECT DISTINCT id FROM ...) AS TableB 
    ON TableA.id = TableB.id

在子查询中使用DISTINCT意味着有每个子查询返回的ID只有一行，所以加入将不会从乘行数表A如果有多个匹配。这使得它成为半连接。

下应该做的更好：

SELECT table1.* 
FROM table1 
JOIN (
    SELECT table1.id FROM table1 
    LEFT JOIN table2 ON table2.id = table1.salesperson_id 
    LEFT JOIN table3 ON table3.id = table2.user_id 
    LEFT JOIN table4 ON table3.office_id = table4.id 
    WHERE table1.type = 'Snapshot' 
    AND table4.id = 25 OR table4.parent_id =25 
    LIMIT 500 
) AS ids ON table1.id = ids.id;

您也可以尝试摆脱index_merge的。你得到的是因为你使用OR为表4中的两个不同的索引列。它使用两个索引，然后将它们联合起来。有时候†最好明确地使用两个子查询的UNION，而不是依赖index_merge。

SELECT table1.* 
FROM table1 
JOIN (
    SELECT table1.id FROM table1 
    JOIN table2 ON table2.id = table1.salesperson_id 
    JOIN table3 ON table3.id = table2.user_id 
    JOIN (
     SELECT id FROM table4 WHERE id=25 
     UNION 
     SELECT id FROM table4 WHERE parent_id=25 
    ) AS t4 ON table3.office_id = t4.id 
    WHERE table1.type = 'Snapshot' 
    LIMIT 500 
) AS ids ON table1.id = ids.id;

您也在不必要地使用了LEFT JOIN，所以我用JOIN替换了它。 MySQL优化器会默默地将其转换为内部连接，但我认为您应该研究LEFT JOIN的含义，并在需要时使用它。

†我说“有时”，因为哪种方法最好可能取决于您的数据，所以您应该测试它的两种方式。

哇，哇。谢谢比尔！这个答案比我希望的要多得多。我无法表达我对你的感谢！您的答案通过超过几个月的工作提升了我的MySQL技能。另外，我发现了另一个解决我的问题的方法。我将在几分钟内发布它 –

答

由于我需要限制删除查询与连接（这是不可能在MySQL中），还有一个选项。这绝不是最好的选择（无法击败比尔的答案）。

但它工作，查询速度非常快，尽管不是非常灵活。由于它具有行的最低金额也可以拉，这对于一个38M行表是575K（不知道为什么）

但在这里，它是：

SELECT COUNT(*) FROM table1 
    JOIN table2 ON table2.id = table1.salesperson_id 
    JOIN table3 ON table3.id = table2.user_id 
    JOIN table4 ON table3.office_id = table4.id 
WHERE table1.type = "Snapshot" 
    AND table4.id = 113 OR table4.parent_id =113 
    AND RAND()<=0.001;

但比尔的答案应该是绰绰有余大家。 P.S.我会在Where Where子句中询问有关RAND（）的问题，并在此处发布链接。也许这将有助于在2025年

FWIW，您*可以*在MySQL中使用多表DELETE语法。请参阅https://dev.mysql.com/doc/refman/5.7/en/delete.html –

我不能限制它，这就是问题所在 –

答

有些绝望开发你被冲昏头脑嵌套等

SELECT table1.* 
    FROM 
    (
     SELECT table1.id 
      FROM table1 
      JOIN table2 ON table2.id = table1.salesperson_id 
      JOIN table3 ON table3.id = table2.user_id 
      JOIN table4 ON table3.office_id = table4.id 
      WHERE table1.type = "Snapshot" 
       AND table4.id = 25 
       OR table4.parent_id =25 
      LIMIT 500 
    ) AS ids 
    JOIN table1 USING(id)

一些讨论：

这是更好地找到500个id和投他们进入tmp表格，而不是在table1.*的所有列周围拖拽。因此子查询为LIMIT 500。
比尔的UNION由于Optimizer决定使用“索引合并联合”似乎是不必要的。这可能只是我第二次看到该功能在使用！
IN (SELECT ...)可能永远不会比等效的JOIN或EXISTS更快，以较合适的为准。（JOIN是适合您的情况。）
对于table4，你有logo_file_id一个完美的“自然PK，为什么不摆脱id和促进去PK？（类似于table2。）
Aarrgghh ...按照我以前的建议，您可以绕过table2！
table1有181M行？ INT总是4个字节。你有很多像专柜一样的专栏;考虑使用TINYINT UNSIGNED（1字节;范围：0..255）或SMALLINT UNSIGNED。这应该会显着缩小表的大小，从而加快缓存和表的使用。

使用子查询的MySQL查询花费的时间比应该长

相关推荐