从 36 分钟到 0.7 秒：战损级硬件下的千万级深分页 SQL 调优纪实

import csv
import random
import uuid
from datetime import datetime, timedelta

# 配置数据量 (千万级建议：100W用户 -> 1000W订单 -> 3000W详情)
USER_COUNT = 1_000_000
ORDER_COUNT = 10_000_000
ITEMS_PER_ORDER_MAX = 3

def generate_csv():
    start_date = datetime(2023, 1, 1)
    
    # 1. 生成用户表 (users.csv)
    print("正在生成用户数据...")
    with open('users.csv', 'w', newline='') as f:
        writer = csv.writer(f)
        for i in range(1, USER_COUNT + 1):
            # id, username, level, created_at
            writer.writerow([i, f"user_{i}", random.randint(1, 5), start_date + timedelta(seconds=i)])

    # 2. 生成订单与明细 (orders.csv & order_items.csv)
    print("正在生成订单与详情数据 (流式处理中)...")
    with open('orders.csv', 'w', newline='') as f_o, open('order_items.csv', 'w', newline='') as f_i:
        o_writer = csv.writer(f_o)
        i_writer = csv.writer(f_i)
        
        item_id_counter = 1
        for o_id in range(1, ORDER_COUNT + 1):
            user_id = random.randint(1, USER_COUNT)
            order_no = uuid.uuid4().hex[:16]
            amount = round(random.uniform(10, 1000), 2)
            created_at = start_date + timedelta(seconds=random.randint(0, 31536000)) # 随机一年内
            
            # 写入订单: id, user_id, order_no, total_amount, status, created_at
            o_writer.writerow([o_id, user_id, order_no, amount, random.randint(0, 3), created_at])
            
            # 写入订单详情 (每个订单1-3个商品)
            for _ in range(random.randint(1, ITEMS_PER_ORDER_MAX)):
                # id, order_id, product_id, price, quantity
                i_writer.writerow([item_id_counter, o_id, random.randint(1, 5000), round(amount/2, 2), 1])
                item_id_counter += 1
            
            if o_id % 1000000 == 0:
                print(f"已完成 {o_id} 条订单数据...")

if __name__ == "__main__":
    generate_csv()
    print("所有CSV文件生成完毕！")

-- 1. 创建表结构
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    level INT,
    created_at DATETIME
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    order_no VARCHAR(32),
    total_amount DECIMAL(10,2),
    status INT,
    created_at DATETIME
);

CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    price DECIMAL(10,2),
    quantity INT
);

-- 2. 导入数据 (在MySQL命令行执行)
-- 注意修改路径为你的本地路径
SET FOREIGN_KEY_CHECKS = 0;

LOAD DATA INFILE '/home/testsql/users.csv' INTO TABLE users FIELDS TERMINATED BY ',';
LOAD DATA INFILE '/home/testsql/orders.csv' INTO TABLE orders FIELDS TERMINATED BY ',';
LOAD DATA INFILE '/home/testsql/order_items.csv' INTO TABLE order_items FIELDS TERMINATED BY ',';

SET FOREIGN_KEY_CHECKS = 1;

 SELECT 
	o.id,
	i.id,
    o.order_no,
    o.total_amount,
    o.status,
    o.created_at,
    u.username,
    u.level,
    i.product_id,
    i.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items i ON o.id = i.order_id
WHERE o.status = 1 
ORDER BY o.created_at DESC
LIMIT 800000, 20;

ALTER TABLE orders ADD INDEX idx_status_time (status, created_at);

SELECT 
	o.id,
	i.id,
    o.order_no,
    o.total_amount,
    o.status,
    o.created_at,
    u.username,
    u.level,
    i.product_id,
    i.price
FROM (
    -- 核心魔法：全覆盖索引 JOIN
    -- MySQL 在这里只需要扫描 idx_status_time 和 idx_order_id 这两棵极其轻量的索引树
    -- 就能在内存中算出那 80万个偏移量，最后精准锁定这 20 个主键组合
    SELECT o_idx.id AS order_id, i_idx.id AS item_id
    FROM orders o_idx
    JOIN order_items i_idx ON o_idx.id = i_idx.order_id
    WHERE o_idx.status = 1 
    ORDER BY o_idx.created_at DESC
    LIMIT 800000, 20
) AS temp
JOIN orders o ON temp.order_id = o.id
JOIN users u ON o.user_id = u.id
JOIN order_items i ON temp.item_id = i.id;

SELECT 
    o.order_no,
    o.total_amount,
    o.status,
    o.created_at,
    u.username,
    u.level,
    i.product_id,
    i.price
FROM (
    -- 核心：这个子查询只会扫描索引，不回表，不 JOIN
    SELECT id 
    FROM orders 
    WHERE status = 1 
    ORDER BY created_at DESC 
    LIMIT 800000, 20
) AS temp_o
JOIN orders o ON temp_o.id = o.id
JOIN users u ON o.user_id = u.id
JOIN order_items i ON o.id = i.order_id;

ALTER TABLE orders
ADD INDEX idx_status_created_id (status, created_at, id);
ALTER TABLE order_items
ADD INDEX idx_orderid_id (order_id, id);

 SELECT
    o.id,
    i.id,
    o.order_no,
    o.total_amount,
    o.status,
    o.created_at,
    u.username,
    u.level,
    i.product_id,
    i.price
FROM (
    (
        -- 1) created_at 更早的结果
        SELECT
            o_idx.id AS order_id,
            i_idx.id AS item_id,
            o_idx.created_at
        FROM orders o_idx
        STRAIGHT_JOIN order_items i_idx
            ON i_idx.order_id = o_idx.id
        WHERE o_idx.status = 1
          AND o_idx.created_at < '2023-11-03 18:25:14'
        ORDER BY o_idx.created_at DESC, o_idx.id DESC, i_idx.id ASC
        LIMIT 20
    )
    
    UNION ALL
    
    (
        -- 2) created_at 相同，但 order_id 更小
        SELECT
            o_idx.id AS order_id,
            i_idx.id AS item_id,
            o_idx.created_at
        FROM orders o_idx
        STRAIGHT_JOIN order_items i_idx
            ON i_idx.order_id = o_idx.id
        WHERE o_idx.status = 1
          AND o_idx.created_at = '2023-11-03 18:25:14'
          AND o_idx.id < 7081307
        ORDER BY o_idx.id DESC, i_idx.id ASC
        LIMIT 20
    )
    
    UNION ALL
    
    (
        -- 3) created_at、order_id 都相同，但 item_id 更大
        SELECT
            o_idx.id AS order_id,
            i_idx.id AS item_id,
            o_idx.created_at
        FROM orders o_idx
        STRAIGHT_JOIN order_items i_idx
            ON i_idx.order_id = o_idx.id
        WHERE o_idx.status = 1
          AND o_idx.created_at = '2023-11-03 18:25:14'
          AND o_idx.id = 7081307
          AND i_idx.id > 14161664
        ORDER BY i_idx.id ASC
        LIMIT 20
    )
    
    ORDER BY created_at DESC, order_id DESC, item_id ASC
    LIMIT 20
) AS temp
JOIN orders o      ON o.id = temp.order_id
JOIN users u       ON u.id = o.user_id
JOIN order_items i ON i.id = temp.item_id
ORDER BY o.created_at DESC, o.id DESC, i.id ASC;

SELECT
    o.id,
    i.id,
    o.order_no,
    o.total_amount,
    o.status,
    o.created_at,
    u.username,
    u.level,
    i.product_id,
    i.price
FROM (
    -- A. 当前订单剩余的 item（如果上一页最后一条正好落在订单内部）
    (
        SELECT
            o_cur.id AS order_id,
            i_cur.id AS item_id,
            o_cur.created_at
        FROM orders o_cur FORCE INDEX (PRIMARY)
        STRAIGHT_JOIN order_items i_cur FORCE INDEX (idx_order_id)
            ON i_cur.order_id = o_cur.id
        WHERE o_cur.id = 7081307
          AND o_cur.status = 1
          AND o_cur.created_at = '2023-11-03 18:25:14'
          AND i_cur.id > 14161664
        ORDER BY i_cur.id ASC
        LIMIT 20
    )

    UNION ALL

    -- B. 后续订单（先取订单，再展开 item）
    (
        SELECT
            o_next.id AS order_id,
            i_next.id AS item_id,
            o_next.created_at
        FROM (
            SELECT
                o1.id,
                o1.created_at
            FROM orders o1 FORCE INDEX (idx_status_created_id)
            WHERE o1.status = 1
              AND (
                    o1.created_at < '2023-11-03 18:25:14'
                 OR (o1.created_at = '2023-11-03 18:25:14' AND o1.id < 7081307)
              )
            ORDER BY o1.created_at DESC, o1.id DESC
            LIMIT 20
        ) AS o_next
        STRAIGHT_JOIN order_items i_next FORCE INDEX (idx_order_id)
            ON i_next.order_id = o_next.id
        ORDER BY o_next.created_at DESC, o_next.id DESC, i_next.id ASC
        LIMIT 20
    )

    ORDER BY created_at DESC, order_id DESC, item_id ASC
    LIMIT 20
) AS temp
JOIN orders o      ON o.id = temp.order_id
JOIN users u       ON u.id = o.user_id
JOIN order_items i ON i.id = temp.item_id
ORDER BY o.created_at DESC, o.id DESC, i.id ASC;