mysql 查询速度慢80%的数据库时间花费在此处

w80xi6nr 于 2022-12-03 发布在 Mysql

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

你好stackoverflow我这里有一个sql语句，它是相当缓慢的执行，我认为这是由于在下面的sql中看到的子查询。我的问题是简单地给出了这个子查询和事实，它必须设置'exists'为0或1给定的逻辑可以改善这一点。

SELECT
    p.id,
    p.name,
    (
        SELECT
            COUNT(*) > 0
        FROM
            product_log AS pl
        WHERE
            pl.product_id = p.id
            AND
            pl.state_name in ("Creation", "Auction", ...)
    ) AS 'has_log'
    from product as p;

为日志和产品创建表

CREATE TABLE `product_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `state_name` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `create_datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `state_name` (`state_name`,`create_datetime`)
(`id`,`product_id`,`state_name`,`create_datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=8132540 DEFAULT CHARSET=utf8;

在查询上运行explain有点不同，因为与实际运行的查询相比，省略了许多详细信息，但输出如下

|id |select_type       |table|partitions|type  |possible_keys                                                    |key                |key_len|ref                          |rows  |filtered|Extra                                       |
|---|------------------|-----|----------|------|-----------------------------------------------------------------|-------------------|-------|-----------------------------|------|--------|--------------------------------------------|
|1  |PRIMARY           |p    |          |eq_ref|PRIMARY,guarantee_list_index                                     |PRIMARY            |4      |ppd.product_id               |1     |100     |                                            |
|2  |DEPENDENT SUBQUERY|pl   |          |ref   |product_id,state_name                                            |product_id         |5      |p.id                         |3     |51.5    |Using where                                 |

mysql

来源：https://stackoverflow.com/questions/74612185/slow-query-80-of-db-time-spent-here

2条答案

按热度按时间

8yoxcaq71#

像什么？

select p.id, p.name, CASE WHEN pl.id IS NULL THEN 0 ELSE 1 END AS has_log
from product as p
left join product_log AS pl on pl.product_id = p.id AND pl.state_name in ("Creation", "Auction", ...)

消除了每行执行的子选择，并且避免使用count（），如果只需要存在，则必须遍历所有记录

赞(0）回复(0）举报 2022-12-03

hmtdttj42#

您不必计算所有匹配项，只需检查是否至少存在一个与EXISTS或IN匹配的项。

SELECT
  p.id,
  p.name,
  EXISTS
  (
    SELECT null
    FROM product_log AS pl
    WHERE pl.product_id = p.id
    AND pl.state_name in ('Creation', 'Auction', ...)
  ) AS has_log
FROM product AS p;

或

SELECT
  p.id,
  p.name,
  p.id IN
  (
    SELECT pl.product_id
    FROM product_log AS pl
    WHERE pl.state_name in ('Creation', 'Auction', ...)
  ) AS has_log
FROM product AS p;

这两个查询做的是相同的事情，因此DBMS可以为这两个查询提供相同的执行计划。但是查询显示DBMS可以通过两种方式来完成任务：
1.遍历所有产品并查找每个产品的日志。
1.查找具有请求日志的所有产品，并使用此列表进行查找。
这些方法需要不同的索引。如果要查找产品的日志，我需要：

CREATE INDEX idx1 ON product_log (product_id, state_name);

如果我想先建立查阅清单，我想：

CREATE INDEX idx2 ON product_log (state_name, product_id);

我建议您创建两个索引。然后查看查询的解释计划，查看使用了哪一个，并删除另一个。

赞(0）回复(0）举报 2022-12-03

我来回答

mysql 查询速度慢80%的数据库时间花费在此处

2条答案

相关问题

热门标签

最新问答