我有一个连接查询,它需要大量时间来处理。
SELECT
COUNT(c.id)
FROM `customers` AS `c`
LEFT JOIN `setting` AS `ssh` ON `c`.`shop_id` = `ssh`.`id`
LEFT JOIN `customer_extra` AS `cx` ON `c`.`id` = `cx`.`customer_id`
LEFT JOIN `customers_address` AS `ca` ON `ca`.`id` = `cx`.`customer_default_address_id`
LEFT JOIN `lytcustomer_tier` AS `ct` ON `cx`.`lyt_customer_tier_id` = `ct`.`id`
WHERE (c.shop_id = '12121') AND ((DATE(cx.last_email_open_date) > '2019-11-08'));
这主要是因为表"customers"有200万条记录。
我可以继续做索引等。但是,更重要的是,这250万可能在一天内变成10亿条记录。
我正在寻找能够提高性能的解决方案。我已经考虑过
a( 水平可扩展性。-:将mysql表分配到不同的部分,并独立查询计数。
b( 使用复合索引。
c( 我最喜欢的一个-:只需在mongodb或redis中创建一个单独的集合,它只包含计数(此查询的输出(,因为计数只有1个数字。这将不需要巨大的规模,也不需要更好的查询性能(唯一的问题是,有多少这样的查询,因为这将增加新集合的规模(
试试这个,看看它是否能提高性能:
SELECT
COUNT(c.id)
FROM `customers` AS `c`
INNER JOIN `customer_extra` AS `cx` ON `c`.`id` = `cx`.`customer_id`
LEFT JOIN `setting` AS `ssh` ON `c`.`shop_id` = `ssh`.`id`
LEFT JOIN `customers_address` AS `ca` ON `ca`.`id` = `cx`.`customer_default_address_id`
LEFT JOIN `lytcustomer_tier` AS `ct` ON `cx`.`lyt_customer_tier_id` = `ct`.`id`
WHERE (c.shop_id = '12121') AND ((DATE(cx.last_email_open_date) > '2019-11-08'));
正如我在评论中提到的,由于条件AND ((DATE(cx.last_email_open_date) > '2019-11-08'));
已经使customers
表与customer_extra
表一起变为INNER JOIN
,您可以将其更改为INNER JOIN customer_extra AS cx ON c.id = cx.customer_id
,然后再使用其他LEFT JOIN
。
INNER JOIN
至少会得到初始结果,只返回基于指定值的last_email_open_date值的任何客户。
- 说
COUNT(*)
,而不是COUNT(c.id)
-
移除这些;他们在不添加任何我能看到的东西的情况下减慢了查询速度:
LEFT JOIN `setting` AS `ssh` ON `c`.`shop_id` = `ssh`.`id` LEFT JOIN `customers_address` AS `ca` ON `ca`.`id` = `cx`.`customer_default_address_id` LEFT JOIN `lytcustomer_tier` AS `ct` ON `cx`.`lyt_customer_tier_id` = `ct`.`id`
-
DATE(...)
使得该测试不"可搜索"。这适用于DATE
或DATETIME
;这要快得多:cx.last_email_open_date > '2019-11-08'
-
考虑是否应该是
>=
而不是>
。 - 需要
shop_id
的索引。(请提供SHOW CREATE TABLE
。( - 当
JOIN
同样有效时,不要使用LEFT JOIN
-
如果
customer_extra
是本应在customer
中的列,那么现在是时候将它们移入了INDEX(shop_id, last_email_open_date) -- in this order
有了这些更改,MySQL中的10亿行可能不会成为问题。如果是的话,我还可以建议更多的修复方法。