MySQL Workbench中SQL关联查询运行无响应问题排查求助

Let’s break down why your query is hanging indefinitely and walk through fixes to get it running smoothly:

1. You’re Generating a Massive Cartesian Product #

The biggest issue here is the one-to-many relationship between es_order and es_orderGoods—a single order can have dozens of line items. When you join these tables first, you’re creating duplicate rows for every order-goods pair, blowing up your dataset size exponentially. For example, 10,000 orders with 50 items each turns into 500,000 rows to process before grouping, which will grind MySQL to a halt.

2. Missing Indexes on Join Columns #

If OrderNo isn’t indexed on any of the three tables, MySQL has to run full table scans for each join. This is devastatingly slow, especially on large tables like es_orderGoods which likely has the most rows.

3. Inefficient Grouping #

Grouping on OrderName without an index forces MySQL to use temporary tables or sort large datasets to aggregate results, adding unnecessary overhead. Also, while count(OI.OrderNo) works, count(*) is often better optimized by MySQL (since it doesn’t need to check for non-null values).

Fixes to Implement #

Optimize the Query to Avoid Cartesian Products

Instead of joining all three tables first, aggregate es_orderGoods data upfront to get counts per order, then join to the other tables. This cuts down the dataset size early on:

SELECT OI.OrderName, SUM(OG.ItemCount) as TotalItems
FROM onns.es_orderInfo as OI 
JOIN onns.es_order as O ON OI.OrderNo = O.OrderNo
JOIN (
    -- Aggregate goods per order first to reduce rows
    SELECT OrderNo, COUNT(*) as ItemCount
    FROM onns.es_orderGoods
    GROUP BY OrderNo
) OG ON O.OrderNo = OG.OrderNo
GROUP BY OI.OrderName;

If es_orderInfo and es_order have a one-to-one relationship (which they likely do), you can simplify further by skipping the es_order table entirely:

SELECT OI.OrderName, COUNT(DISTINCT OG.OrderNo) as UniqueOrderCount
FROM onns.es_orderInfo as OI 
JOIN onns.es_orderGoods OG ON OI.OrderNo = OG.OrderNo
GROUP BY OI.OrderName;

Add Indexes to Speed Up Joins

First, check if OrderNo is indexed on all three tables:

SHOW INDEX FROM onns.es_orderInfo;
SHOW INDEX FROM onns.es_order;
SHOW INDEX FROM onns.es_orderGoods;

If no index exists for OrderNo, create them:

CREATE INDEX idx_orderinfo_orderno ON onns.es_orderInfo(OrderNo);
CREATE INDEX idx_order_orderno ON onns.es_order(OrderNo);
CREATE INDEX idx_ordergoods_orderno ON onns.es_orderGoods(OrderNo);

Analyze the Query Plan

Use EXPLAIN to pinpoint exactly where MySQL is struggling:

EXPLAIN
SELECT OI.OrderName, count(OI.OrderNo)
FROM onns.es_orderInfo as OI 
JOIN onns.es_order as O
ON  OI.OrderNo = O.OrderNo
JOIN onns.es_orderGoods OG
ON O.OrderNo = OG.OrderNo
GROUP BY OI.OrderName;

Look for red flags:

• type: ALL (indicates a full table scan—this is bad!)
• key: NULL (means no index is being used for the join)
• Large numbers in the rows column (estimates how many rows MySQL will scan)

内容的提问来源于stack exchange，提问作者beth_9