MySQL Count 子查询

MySQL Count subquery

提问by Jimmy

I have a database with 3 tables:

我有一个包含 3 个表的数据库：

• equities
• stocksplits
• dividends

• 股票
• 股票分割
• 股息

There is a one to many relationship between equities and stocksplits, and between equities and dividends. For each equity I would like to show the number of stocksplits and dividends:

股票和股票分割之间以及股票和股息之间存在一对多的关系。对于每个股票，我想显示股票拆分和股息的数量：

SELECT equities.Symbol, 
       (SELECT COUNT(*) 
          FROM stocksplits 
         WHERE stocksplits.EquityID = equities.InstrumentID) as `# Splits`,
       (SELECT COUNT(*) 
          FROM dividends 
         WHERE dividends.EquityID = equities.InstrumentID) as `# Dividends`
FROM equities

The query appears to run fine, though I suspect it is inefficient. How can it be refactored to be faster? No DBMS (SQL query via .net to MySQL server), assume indices exist on the primary ID of each table.

查询似乎运行良好，但我怀疑它效率低下。如何重构更快？没有 DBMS（通过 .net 到 MySQL 服务器的 SQL 查询），假设每个表的主 ID 上存在索引。

回答by Henrique Ordine

Counting the PKs instead of * might already help:

计算 PK 而不是 * 可能已经有所帮助：

SELECT equities.Symbol, 
? ? ?     ?(SELECT COUNT(stocksplitsID) 
? ? ?     ? ? FROM stocksplits 
? ? ?     ? ?WHERE stocksplits.EquityID =     equity.InstrumentID) as `# Splits`,
? ? ?     ?(SELECT COUNT(dividendsid) 
? ? ? ?     ? FROM dividends 
? ? ? ?     ?WHERE dividends.EquityID = equities.InstrumentID) as `# Dividends`
FROM equities

回答by RolandoMySQLDBA

Here is your original query

这是您的原始查询

SELECT equities.Symbol, 
       (SELECT COUNT(*) 
          FROM stocksplits 
         WHERE stocksplits.EquityID = equities.InstrumentID) as `# Splits`
FROM equities

I was just thinking that an LEFT JOINwould be cleaner

我只是想一个LEFT JOIN会更干净

SELECT equities.Symbol,
    SUM(IF(IFNULL(stocksplits.EquityID,0)=0,0,1)) StockSplits,
    SUM(IF(IFNULL(dividends.EquityID  ,0)=0,0,1)) Dividends
FROM
    equities
    LEFT JOIN stocksplits ON equities.InstrumentID = stocksplits.EquityID
    LEFT JOIN dividends   ON equities.InstrumentID = dividends.EquityID
GROUP BY equities.Symbol;

The IFNULL covers any stock that had no stock splits

IFNULL 涵盖任何没有股票拆分的股票

Give it a Try and see it it runs faster

试一试，看看它运行得更快

Let me explain the expression SUM(IF(IFNULL(stocksplits.EquityID,0)=0,0,1))

让我解释一下这个表达 SUM(IF(IFNULL(stocksplits.EquityID,0)=0,0,1))

• IFNULL will turn a NULL into a 0 if the LEFT JOIN does not have a corresponding entry on the right-side table.
• if LEFT JOIN had a right-side entry, IF function returns 1
• if LEFT JOIN has no right-side entry, IF function return 0
• SUM will add up all the ones and zeros, simulating a COUNT

• 如果 LEFT JOIN 在右侧表中没有相应的条目，则 IFNULL 会将 NULL 转换为 0。
• 如果 LEFT JOIN 有右侧条目，则 IF 函数返回 1
• 如果 LEFT JOIN 没有右侧入口，则 IF 函数返回 0
• SUM 将所有的 1 和 0 相加，模拟 COUNT

回答by Jason Heo

In my exprience, MySQL's correlated subqueryhas a poor performance.

根据我的经验，MySQL 的correlated subquery性能很差。

Q1 - JOIN

Q1 - 加入

SELECT t1.Symbol, t1.cnt_splits, t2.cnt_dividends
FROM (
    SELECT equities.Symbol AS Symbol, COUNT(*) AS cnt_splits
    FROM equities LEFT JOIN stocksplits
        ON stocksplits.EquityID = equities.InstrumentID
    GROUP BY equities.Symbol
) t1,
(
    SELECT equities.Symbol AS Symbol, COUNT(*) AS cnt_dividends
    FROM equities LEFT JOIN dividends
        dividends.EquityID = equities.InstrumentID
    GROUP BY equities.Symbol
) t2 ON t1.Symbol = t2.Symbol;

Q2 - UNION

Q2 - 联合

Q1has no correlate subqueryand produces as same result as yours. but an extra join is required which needs time. following UNIONpattern is faster but output should be converted in client side.

Q1没有correlate subquery并产生与您相同的结果。但是需要额外的连接，这需要时间。以下UNION模式更快，但应在客户端转换输出。

SELECT equities.Symbol AS Symbol, COUNT(*) AS cnt_splits
FROM equities LEFT JOIN stocksplits
    ON stocksplits.EquityID = equities.InstrumentID
GROUP BY equities.Symbol

UNION

SELECT equities.Symbol AS Symbol, COUNT(*) AS cnt_dividends
FROM equities LEFT JOIN dividends
    dividends.EquityID = equities.InstrumentID
GROUP BY equities.Symbol