MySQL百万级数据倒序查询20条记录缓慢的技术咨询

Hey there! I totally get why you're frustrated with that slow query—dealing with million-row tables can be tricky when your ORDER BY ends up dragging everything down. Let's break down why this is happening and how to fix it quickly.

Why Your Current Query Is Slow #

Your guess is spot-on: MySQL is likely doing a full join of all three tables first, creating a massive intermediate result set, then sorting that entire set just to grab the top 10 rows. That's a huge waste of resources, especially with 1M+ records. The ORDER BY bookings.booking_id DESC is forcing a costly filesort operation on the joined data, which is what's eating up all the time.

The Fix: Narrow Down the Dataset First #

Instead of joining all tables first and then sorting, we'll first fetch the 10 most recent booking IDs from the bookings table (using its index for speed), then join only those 10 records with passengers and payments. This cuts down the data we're working with drastically.

Here's the optimized query:

SELECT 
    b.created_at, 
    b.total_amount, 
    p.name, 
    p.id_number, 
    pay.amount, 
    p.ticket_no,
    b.phone,
    b.source, 
    b.destination,
    b.date_of_travel 
FROM (
    -- First get the top 10 latest bookings using the booking_id index
    SELECT booking_id, created_at, total_amount, phone, source, destination, date_of_travel
    FROM bookings
    ORDER BY booking_id DESC
    LIMIT 10
) AS b
INNER JOIN passengers p ON b.booking_id = p.booking_id
INNER JOIN payments pay ON b.booking_id = pay.booking_id

Key Optimizations to Ensure Speed #

• Check Indexes on booking_id:

  • Make sure bookings.booking_id is the primary key (it should be, since it's the table's identifier)—primary keys in MySQL are automatically clustered indexes, so fetching the latest 10 rows is almost instant.
  • Ensure passengers.booking_id and payments.booking_id have secondary indexes. If they don't, create them with these commands:

    CREATE INDEX idx_passengers_booking_id ON passengers(booking_id);
    CREATE INDEX idx_payments_booking_id ON payments(booking_id);
    

  These indexes let MySQL quickly find the matching passenger and payment records for each booking ID, instead of scanning the entire tables.

• Verify with EXPLAIN:
  Run EXPLAIN on both your original query and the optimized one to see the difference. For the optimized query, you should see:

  • The subquery uses the primary key index on bookings.booking_id (look for type: range or ref in the EXPLAIN output).
  • No Using filesort in the main query, since we're no longer sorting a huge dataset.

Why This Works #

By limiting the bookings data to just 10 rows first, we're reducing the join operation to only 10 records per table. The sorting now happens on the tiny bookings subset (which is super fast thanks to the index) instead of a massive joined result set.

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