如何优化MySQL日期范围查询及特定日期匹配查询的性能？

Alright, let's tackle that frustrating 90-second query delay you're dealing with. The root issue here is almost certainly that your individual indexes on date_added and name aren't optimized for how you're using them together—combining filtering by name and sorting by date_added needs a smarter indexing strategy. Here's a step-by-step breakdown of fixes:

1. Create a Composite Index (Critical Fix) #

The biggest win will be replacing your single-column indexes with a composite index ordered correctly. Since you're filtering on an exact name match first, then sorting by date_added, the index should prioritize the equality column first:

CREATE INDEX idx_name_dateadded ON your_table (name, date_added);

Why this works:

• MySQL can quickly narrow down all rows matching your target name using the first part of the index.
• Those matching rows are already sorted by date_added in the index itself—so the ORDER BY clause won't trigger a slow disk-based filesort anymore.

2. Validate with EXPLAIN #

Always check if your index is being used by running your query with EXPLAIN prefix:

EXPLAIN SELECT your_columns FROM your_table WHERE name = 'target_name' ORDER BY date_added;

Look for these signs of success:

• In the type column: ref or range (indicates the index is being used for filtering).
• In the Extra column: No Using filesort (means sorting is handled by the index) and ideally Using index if you're using a covering index (see next point).

3. Use a Covering Index (Skip Table Lookups) #

If your query pulls specific numeric columns (not SELECT *), add those columns to the composite index to create a covering index. This lets MySQL retrieve all needed data directly from the index, without having to jump back to the main table:

CREATE INDEX idx_name_dateadded_values ON your_table (name, date_added, value_col1, value_col2);

Pro tip: Never use SELECT * here—only include the columns you actually need. This reduces index size and speeds up data retrieval.

4. Optimize the Query Logic #

If you're looking for the "most matching" row (e.g., the most recent date_added for a given name), add LIMIT 1 to your query. This tells MySQL to stop searching once it finds the first matching row (which the composite index makes trivial):

SELECT value_col1, value_col2 
FROM your_table 
WHERE name = 'target_name' 
ORDER BY date_added DESC 
LIMIT 1;

5. Fix Data Type & Query Pitfalls #

• Ensure date_added is a DATE, DATETIME, or TIMESTAMP type—not a string. String-based dates break index efficiency.
• Never wrap date_added in functions in your WHERE clause (e.g., DATE(date_added) = '2024-01-01'). This forces MySQL to ignore the index and scan the entire table.

6. Consider Partitioning (For Massive Datasets) #

If your table has millions/billions of rows, partitioning by date_added (e.g., by year or month) can drastically reduce the amount of data MySQL scans:

ALTER TABLE your_table 
PARTITION BY RANGE (TO_DAYS(date_added)) (
    PARTITION p2023 VALUES LESS THAN (TO_DAYS('2024-01-01')),
    PARTITION p2024 VALUES LESS THAN (TO_DAYS('2025-01-01'))
);

This way, queries targeting a specific date range only scan the relevant partition(s).

7. Update Table Statistics #

Outdated table statistics can make MySQL choose a bad execution plan. Refresh them with:

ANALYZE TABLE your_table;

8. Tweak Server Configuration (If Needed) #

If you still see Using filesort in EXPLAIN, check your sort_buffer_size setting. A too-small buffer forces MySQL to sort data on disk instead of in memory. Adjust it in your my.cnf/my.ini (start with 8-16MB, based on your server's available memory):

sort_buffer_size = 16M

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