MySQL按天分组取每日最后一条温度值 求高效优化方案

Hey there! Let’s tackle that slow query for fetching the daily last temperature reading—this is a super common scenario, so I’ve got a few solid, battle-tested optimizations to share with you.

1. Use Window Functions (Modern SQL Databases) #

If your database supports window functions (like PostgreSQL, MySQL 8.0+, SQL Server, etc.), this is the cleanest and often most efficient approach. We’ll rank each day’s readings by datetime in descending order, then pick the top-ranked entry for each day.

Here’s the query:

SELECT dt_date, temperature
FROM (
    SELECT 
        DATE(datetime) AS dt_date,
        temperature,
        ROW_NUMBER() OVER (PARTITION BY DATE(datetime) ORDER BY datetime DESC) AS rn
    FROM temperature_readings
) ranked_readings
WHERE rn = 1;

Why this works: #

• The PARTITION BY DATE(datetime) groups all readings by day, and ORDER BY datetime DESC ensures the latest entry gets row number 1.
• With the right index (see below), the database can compute this ranking without scanning the entire table.

2. Index-First Approach (Max Datetime + Join) #

If you’re working with an older database that doesn’t support window functions, or you want to squeeze every bit of performance out, this method leverages indexes to quickly find the latest datetime per day, then joins back to get the temperature.

First, create a covering index to make this blazingly fast:

-- Adjust function syntax if needed for your database
CREATE INDEX idx_temp_date_datetime_temp ON temperature_readings (DATE(datetime), datetime DESC, temperature);

Then use this query:

SELECT 
    DATE(tr.datetime) AS dt_date,
    tr.temperature
FROM temperature_readings tr
INNER JOIN (
    SELECT DATE(datetime) AS day_date, MAX(datetime) AS latest_dt
    FROM temperature_readings
    GROUP BY DATE(datetime)
) latest_dates ON tr.datetime = latest_dates.latest_dt;

Why this works: #

• The index lets the subquery quickly find the max datetime per day without scanning all rows.
  -- The covering index includes temperature, so the database doesn’t need to jump back to the main table (this is called a "covering index scan" and avoids expensive table lookups).

3. Pre-Aggregate with a Summary Table #

If your temperature data is mostly read-only (i.e., historical readings don’t change after being inserted), pre-aggregating the daily last value into a smaller table will give you instant query results.

Step 1: Create a summary table #

CREATE TABLE daily_last_temperature (
    reading_date DATE PRIMARY KEY,
    last_temperature DECIMAL(5,2) NOT NULL
);

Step 2: Populate it (and set up a scheduled job) #

Run this once to backfill historical data:

INSERT INTO daily_last_temperature (reading_date, last_temperature)
SELECT 
    DATE(datetime) AS reading_date,
    temperature
FROM temperature_readings tr
WHERE tr.datetime = (
    SELECT MAX(datetime) 
    FROM temperature_readings 
    WHERE DATE(datetime) = DATE(tr.datetime)
);

Then set up a daily scheduled task (like a database event or cron job) to add the previous day’s value:

REPLACE INTO daily_last_temperature (reading_date, last_temperature)
SELECT 
    DATE(CURRENT_DATE - INTERVAL 1 DAY),
    temperature
FROM temperature_readings
WHERE datetime = (
    SELECT MAX(datetime) 
    FROM temperature_readings 
    WHERE DATE(datetime) = DATE(CURRENT_DATE - INTERVAL 1 DAY)
);

Why this works: #

• Querying the summary table is just a simple SELECT * FROM daily_last_temperature—no grouping or sorting needed, so it’s instant even for years of data.

Key Indexing Tips #

No matter which method you choose, indexes are make-or-break here:

• Avoid using DATE(datetime) in your query without an index on that expression (if your database supports functional indexes).
• Always include temperature in the index if you’re selecting it—this avoids "bookmark lookups" back to the main table.
• Run EXPLAIN on your query to verify the database is using the index you created. If you see "Full Table Scan" in the plan, your index isn’t being utilized (check for syntax issues or outdated stats).

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