MySQL计数子查询

Question

我有一个有三个表的数据库：

• 股票
• 股票分割
• 股利

股票和股票分割，股票和股息之间有一对多的关系。对于每一种股票，我想显示出股票分红和股息的数量：

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

该查询似乎运行良好，但我怀疑它效率低下。怎么才能把它重构成更快呢？没有数据库管理系统(通过.net到MySQL服务器的server)，假设每个表的主ID上存在索引。

Answer 1

计算PKs而不是*可能已经有帮助了：

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

Answer 2

这是您最初的查询

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

我只是在想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;

IFNULL涵盖任何没有股票分割的股票。

试一试，看看它跑得更快

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

• 如果左侧联接在右侧表中没有相应的条目，则IFNULL将将NULL转换为0。
• 如果左联接有一个右侧条目，则if函数返回1。
• 如果左联接没有右侧入口，则如果函数返回0
• 和将所有的1和0相加，模拟一个计数。

Answer 3

在我的经历中，MySQL的correlated subquery性能很差。

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 -联合

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