如何加速SQLAlchemy查询？千万级传感器数据表查询优化求助

Hey there, let's tackle this sensor data query slowness you're dealing with—10M+ rows, 50+ columns, and 20+ seconds for a full day's data is way longer than it should be, even with some indexes already in place. Let's break down actionable steps to speed this up:

1. Diagnose the Query First with EXPLAIN #

Don't guess why it's slow—let the database tell you. Run your query prefixed with EXPLAIN to see exactly what's happening:

EXPLAIN SELECT * FROM sensor_data WHERE recordTimestamp BETWEEN 'YYYY-MM-DD 00:00:00' AND 'YYYY-MM-DD 23:59:59' AND deviceId = 'xxx' AND positionValid = 1;

Pay attention to these key columns in the output:

• type: Should be range or ref (good) instead of ALL (full table scan, bad)
• key: Shows which index the query is actually using—if it's not your composite index, that's a red flag
• Extra: Watch for Using filesort or Using temporary—these are major performance bottlenecks

A common mistake with composite indexes is order: if your query uses an equality filter (deviceId = ?) plus a range filter (recordTimestamp BETWEEN ...), put the equality columns first in the composite index. For example, (deviceId, recordTimestamp, positionValid) works better than (recordTimestamp, deviceId, positionValid)—once a range filter is used, any columns after it in the index can't be used for lookups.

2. Avoid SELECT *—Fetch Only What You Need #

You have 50+ columns, but do you really need all of them for this daily query? Cutting down to just the columns you use does two big things:

• Reduces the amount of data transferred from the database to your application
• Lets you use a covering index: add the required columns to your composite index so the database can retrieve everything it needs directly from the index, no need to hit the main table.

Example of a covering index for a query that needs temperature and humidity:

CREATE INDEX idx_device_ts_valid_temp_humidity ON sensor_data (deviceId, recordTimestamp, positionValid, temperature, humidity);

Then your query becomes:

SELECT recordTimestamp, temperature, humidity FROM sensor_data WHERE ...;

3. Partition or Shard the Table #

Since your data is time-series (sensor data tied to timestamps), time-based partitioning is a game-changer. Instead of scanning the entire 10M+ row table, the database only scans the partition for the day you're querying.

For MySQL, here's how to set up daily partitioning:

ALTER TABLE sensor_data PARTITION BY RANGE (TO_DAYS(recordTimestamp)) (
    PARTITION p20240520 VALUES LESS THAN (TO_DAYS('2024-05-21')),
    PARTITION p20240521 VALUES LESS THAN (TO_DAYS('2024-05-22')),
    -- Add more partitions as needed
);

If you have thousands of unique devices, you could also consider sharding by deviceId, but partitioning is easier to maintain for time-series use cases.

4. Tune Database and Hardware Settings #

Even with perfect indexes, poor configuration can kill performance:

• InnoDB Buffer Pool: Set innodb_buffer_pool_size to ~70-80% of your server's available RAM (e.g., 8GB on a 10GB RAM server). This lets the database cache most of your sensor data in memory, avoiding slow disk reads.
• SSD Storage: Swap out HDDs for SSDs—sensor data queries often involve range scans, which SSDs handle exponentially faster than spinning disks.
• Write Optimization: If you don't need strict ACID compliance for this data, set innodb_flush_log_at_trx_commit = 2 to reduce disk I/O during writes (this slightly increases crash risk but boosts performance).

5. Pre-Aggregate Data with a Summary Table #

If this daily query runs frequently (e.g., dashboards, reports), precompute the results ahead of time instead of querying the raw data every time. Create a summary table and populate it with a scheduled job (like a cron task or database event):

First, create the summary table:

CREATE TABLE sensor_daily_summary (
    deviceId VARCHAR(50) NOT NULL,
    recordDate DATE NOT NULL,
    avg_temperature DECIMAL(5,2),
    max_humidity DECIMAL(5,2),
    min_pressure DECIMAL(5,2),
    -- Add other aggregated metrics you need
    PRIMARY KEY (deviceId, recordDate)
);

Then run a daily job to populate it:

INSERT INTO sensor_daily_summary
SELECT 
    deviceId,
    DATE(recordTimestamp),
    AVG(temperature),
    MAX(humidity),
    MIN(pressure)
FROM sensor_data
WHERE recordTimestamp BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE()
GROUP BY deviceId, DATE(recordTimestamp);

Now your daily query is just a fast lookup on the summary table instead of a full scan of raw data.

6. Clean Up Redundant Indexes #

You mentioned adding individual indexes plus composite indexes—check for redundancy. For example, if you have a composite index (deviceId, recordTimestamp), the individual deviceId index is redundant because the composite index's prefix already covers equality lookups on deviceId. Redundant indexes slow down writes (since the database has to update multiple indexes) and waste disk space.

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