MySQL：查询优化——如何更优化地编写指定SQL语句？

Nice question! Let's walk through how to optimize this query for better efficiency (especially on large datasets) and cleaner readability. Your original query works, but it does unnecessary full-table scans and can be simplified.

Key Issues with the Original Query #

Your current query runs two separate full-table scans:

1. One to filter and sum Wednesday's data
2. Another in the subquery to calculate the global total sum
   This doubles the IO load on your database, which adds up when dealing with large tables.

Optimized Version 1: Use CTE to Calculate Global Total Once #

This approach computes the global total sum once using a CTE (Common Table Expression), then joins it with the Wednesday data calculation. This cuts down to one full scan for the total, plus a filtered scan for Wednesday's data (even better if you have an index on week_day):

WITH global_total AS (
    SELECT SUM(min_0 + min_1 + min_2 + min_3) AS total_sum
    FROM Hotel.RegIn
)
SELECT 
    "Wednesday" AS week_day,
    SUM(min_0 + min_1 + min_2 + min_3) * 100 / global_total.total_sum AS MIN_PERCENTAGE
FROM Hotel.RegIn, global_total
WHERE week_day = "Wednesday";

Why this works better:

• The CTE runs once, computing the total sum without repeating work
• We don't need GROUP BY anymore because the WHERE clause already filters to only Wednesday rows (the result will always be a single row)

Optimized Version 2: Add a Calculated Column (For Repeated Use) #

If you frequently calculate the sum of min_0 to min_3, adding a stored calculated column eliminates the need to repeat that arithmetic in every query:

First, add the calculated column:

ALTER TABLE Hotel.RegIn 
ADD COLUMN total_min INT GENERATED ALWAYS AS (min_0 + min_1 + min_2 + min_3) STORED;

Then your query becomes even cleaner and faster:

WITH global_total AS (
    SELECT SUM(total_min) AS total_sum
    FROM Hotel.RegIn
)
SELECT 
    "Wednesday" AS week_day,
    SUM(total_min) * 100 / global_total.total_sum AS MIN_PERCENTAGE
FROM Hotel.RegIn, global_total
WHERE week_day = "Wednesday";

Bonus: Index Optimization #

To make the filtered scan for Wednesday's data even faster, create an index on the week_day column:

CREATE INDEX idx_regin_weekday ON Hotel.RegIn(week_day);

This lets the database jump directly to all Wednesday rows instead of scanning every row in the table.

Alternative: Window Function Approach #

If you prefer window functions (great for more complex grouping scenarios), you can compute the global total in the same scan as the Wednesday data:

SELECT DISTINCT
    week_day,
    SUM(min_0 + min_1 + min_2 + min_3) OVER (PARTITION BY week_day) * 100 /
    SUM(min_0 + min_1 + min_2 + min_3) OVER () AS MIN_PERCENTAGE
FROM Hotel.RegIn
WHERE week_day = "Wednesday";

The DISTINCT ensures we only get one row for Wednesday, since the window functions would otherwise repeat the value for every matching row.

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