-- 用戶表
CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  age INT,
  register_time DATETIME,
  INDEX idx_age (age),
  INDEX idx_register_time (register_time)
) ENGINE=InnoDB;

-- 商品表
CREATE TABLE products (
  product_id INT AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2),
  category_id INT,
  stock INT,
  INDEX idx_category (category_id),
  INDEX idx_name_price (product_name, price)
) ENGINE=InnoDB;

-- 訂單表
CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  order_time DATETIME,
  amount DECIMAL(10,2),
  status TINYINT, -- 1:待支付 2:已支付 3:已取消
  INDEX idx_user_time (user_id, order_time),
  INDEX idx_product_id (product_id)
) ENGINE=InnoDB;

DELIMITER //
CREATE PROCEDURE prod_generate_users()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE batch_size INT DEFAULT 1000; -- 每批處理1000條記錄
  DECLARE total INT DEFAULT 100000;   -- 總記錄數(shù)

  WHILE i <= total DO
    START TRANSACTION;

    -- 插入當(dāng)前批次的記錄
    WHILE i <= total AND i <= (batch_size * FLOOR((i-1)/batch_size) + batch_size) DO
      INSERT INTO users (username, email, age, register_time)
      VALUES (
        CONCAT('user_', i),
        CONCAT('user_', i, '@example.com'),
        FLOOR(RAND() * 40) + 18,
        DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY)
      );
      SET i = i + 1;
    END WHILE;

    COMMIT;
  END WHILE;
END //
DELIMITER ;

-- 執(zhí)行存儲(chǔ)過(guò)程
CALL prod_generate_users();

DELIMITER //
CREATE PROCEDURE prod_generate_products()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE batch_size INT DEFAULT 1000; -- 每批處理1000條記錄
  DECLARE total INT DEFAULT 10000;     -- 總記錄數(shù)

  WHILE i <= total DO
    START TRANSACTION;

    -- 插入當(dāng)前批次的記錄
    WHILE i <= total AND i <= (batch_size * FLOOR((i-1)/batch_size) + batch_size) DO
      INSERT INTO products (product_name, price, category_id, stock)
      VALUES (
        CONCAT('product_', i),
        ROUND(RAND() * 999 + 1, 2),      -- 1-1000元
        FLOOR(RAND() * 20) + 1,          -- 1-20類分類
        FLOOR(RAND() * 1000) + 10        -- 10-1009庫(kù)存
      );
      SET i = i + 1;
    END WHILE;

    COMMIT;
  END WHILE;
END //
DELIMITER ;

CALL prod_generate_products();

DELIMITER //
CREATE PROCEDURE prod_generate_orders()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE batch_size INT DEFAULT 500;  -- 每批處理500條記錄（訂單數(shù)據(jù)量大，批次更小）
  DECLARE total INT DEFAULT 1000000;    -- 總記錄數(shù)
  DECLARE max_user INT;
  DECLARE max_product INT;
  DECLARE rand_product_id INT;
  DECLARE product_price DECIMAL(10,2);

  -- 獲取用戶和商品的最大ID
  SELECT MAX(user_id) INTO max_user FROM users;
  SELECT MAX(product_id) INTO max_product FROM products;

  WHILE i <= total DO
    START TRANSACTION;

    -- 插入當(dāng)前批次的記錄
    WHILE i <= total AND i <= (batch_size * FLOOR((i-1)/batch_size) + batch_size) DO
      -- 優(yōu)化：預(yù)先計(jì)算隨機(jī)商品ID和價(jià)格，避免子查詢
      SET rand_product_id = FLOOR(RAND() * max_product) + 1;
      SELECT price INTO product_price FROM products WHERE product_id = rand_product_id LIMIT 1;

      INSERT INTO orders (user_id, product_id, order_time, amount, status)
      VALUES (
        FLOOR(RAND() * max_user) + 1,    -- 隨機(jī)用戶
        rand_product_id,                 -- 隨機(jī)商品
        DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY), -- 近1年訂單
        product_price * (FLOOR(RAND() * 5) + 1),            -- 1-5件數(shù)量
        FLOOR(RAND() * 3) + 1             -- 隨機(jī)狀態(tài)
      );
      SET i = i + 1;
    END WHILE;

    COMMIT;
  END WHILE;
END //
DELIMITER ;

CALL prod_generate_orders();

select count(1) from users
union all
select count(1) from products
union all
select count(1) from orders;

+----------+
| count(1) |
+----------+
|   100000 |
|    10000 |
|  1000000 |
+----------+

-- 對(duì)任意SELECT查詢執(zhí)行分析
EXPLAIN SELECT 列名 FROM 表名 WHERE 條件;
-- 支持復(fù)雜查詢（JOIN、子查詢等）
EXPLAIN SELECT u.username, o.order_id FROM users u JOIN orders o ON u.user_id = o.user_id WHERE u.age > 30;
+----+-------------+-------+------------+------+----------------------+----------------------+---------+------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys        | key                  | key_len | ref              | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+------------------+-------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | ALL  | PRIMARY,idx_age      | NULL                 | NULL    | NULL             | 99864 |    50.00 | Using where |
|  1 | SIMPLE      | o     | NULL       | ref  | idx_user_time_amount | idx_user_time_amount | 4       | testdb.u.user_id |     9 |   100.00 | Using index |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+------------------+-------+----------+-------------+
2 rows in set, 1 warning (0.14 sec)

EXPLAIN SELECT username, email FROM users WHERE age BETWEEN 30 AND 40;

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | idx_age       | NULL | NULL    | NULL | 99776 |    47.03 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

-- 默認(rèn)是關(guān)閉的
show global variables like 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name   | Value                    |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+

-- 開(kāi)啟跟蹤（修改當(dāng)前會(huì)話）
SET optimizer_trace = "enabled=on";

-- 僅影響當(dāng)前會(huì)話（看global全局還是off的）
show session variables like 'optimizer_trace';
+-----------------+-------------------------+
| Variable_name   | Value                   |
+-----------------+-------------------------+
| optimizer_trace | enabled=on,one_line=off |
+-----------------+-------------------------+

-- 執(zhí)行目標(biāo)查詢
SELECT * FROM orders WHERE user_id = 100 AND order_time > '2024-01-01';

-- 查看跟蹤結(jié)果
SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: SELECT * FROM orders WHERE user_id = 100 AND order_time > '2024-01-01'
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `orders`.`order_id` AS `order_id`,`orders`.`user_id` AS `user_id`,`orders`.`product_id` AS `product_id`,`orders`.`order_time` AS `order_time`,`orders`.`amount` AS `amount`,`orders`.`status` AS `status` from `orders` where ((`orders`.`user_id` = 100) and (`orders`.`order_time` > '2024-01-01'))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > '2024-01-01'))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`orders`.`order_time` > '2024-01-01') and multiple equal(100, `orders`.`user_id`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`orders`.`order_time` > '2024-01-01') and multiple equal(100, `orders`.`user_id`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`orders`.`order_time` > TIMESTAMP'2024-01-01 00:00:00') and multiple equal(100, `orders`.`user_id`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`orders`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`orders`",
                "field": "user_id",
                "equals": "100",
                "null_rejecting": true
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`orders`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 925560,
                    "cost": 93207.1
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_user_time",
                      "usable": true,
                      "key_parts": [
                        "user_id",
                        "order_time",
                        "order_id"
                      ]
                    },
                    {
                      "index": "idx_product_id",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "skip_scan_range": {
                    "potential_skip_scan_indexes": [
                      {
                        "index": "idx_user_time",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      }
                    ]
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_user_time",
                        "ranges": [
                          "user_id = 100 AND '2024-01-01 00:00:00' < order_time"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "in_memory": 1,
                        "rows": 10,
                        "cost": 3.76,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_user_time",
                      "rows": 10,
                      "ranges": [
                        "user_id = 100 AND '2024-01-01 00:00:00' < order_time"
                      ]
                    },
                    "rows_for_plan": 10,
                    "cost_for_plan": 3.76,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`orders`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "idx_user_time",
                      "chosen": false,
                      "cause": "range_uses_more_keyparts"
                    },
                    {
                      "rows_to_scan": 10,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_user_time"
                      },
                      "resulting_rows": 10,
                      "cost": 4.76,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 10,
                "cost_for_plan": 4.76,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > TIMESTAMP'2024-01-01 00:00:00'))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`orders`",
                  "attached": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > TIMESTAMP'2024-01-01 00:00:00'))"
                }
              ]
            }
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`orders`",
                "original_table_condition": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > TIMESTAMP'2024-01-01 00:00:00'))",
                "final_table_condition   ": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > TIMESTAMP'2024-01-01 00:00:00'))"
              }
            ]
          },
          {
            "refine_plan": [
              {
                "table": "`orders`",
                "pushed_index_condition": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > TIMESTAMP'2024-01-01 00:00:00'))",
                "table_condition_attached": null
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0


-- 關(guān)閉跟蹤（避免性能消耗）
SET optimizer_trace = "enabled=off";

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 2
ORDER BY order_time
LIMIT 10000, 20\G

EXPLAIN: -> Limit/Offset: 20/10000 row(s)  (cost=93205 rows=20) (actual time=744..744 rows=20 loops=1)
    -> Sort: orders.order_time, limit input to 10020 row(s) per chunk  (cost=93205 rows=925560) (actual time=742..743 rows=10020 loops=1)
        -> Filter: (orders.`status` = 2)  (cost=93205 rows=925560) (actual time=3.26..591 rows=333807 loops=1)
            -> Table scan on orders  (cost=93205 rows=925560) (actual time=3.25..505 rows=1e+6 loops=1)

CREATE INDEX idx_status_time ON orders(status, order_time);

EXPLAIN: -> Limit/Offset: 20/10000 row(s)  (cost=48225 rows=20) (actual time=48.4..48.4 rows=20 loops=1)
    -> Index lookup on orders using idx_status_time (status=2)  (cost=48225 rows=462780) (actual time=25.3..47.9 rows=10020 loops=1)

EXPLAIN SELECT COUNT(1) FROM users WHERE YEAR(register_time) = 2025;
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key               | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | users | NULL       | index | NULL          | idx_register_time | 6       | NULL | 99776 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN SELECT COUNT(1) FROM users WHERE register_time >= '2025-01-01' AND register_time < DATE_ADD('2025-01-01', INTERVAL 1 YEAR);
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | users | NULL       | range | idx_register_time | idx_register_time | 6       | NULL | 5300 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN select count(1) from products where product_name=12345;
+----+-------------+----------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | products | NULL       | index | idx_name_price | idx_name_price | 408     | NULL | 9642 |    10.00 | Using where; Using index |
+----+-------------+----------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 3 warnings (0.00 sec)

EXPLAIN select count(1) from products where product_name='12345';
+----+-------------+----------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | ref  | idx_name_price | idx_name_price | 402     | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 刪掉原本的 idx_category 方便驗(yàn)證該案例
alter table products drop index idx_category,add index idx_price_category(price,category_id);

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price > 900;
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | products | NULL       | range | idx_price_category | idx_price_category | 6       | NULL | 1003 |    10.00 | Using index condition |
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

-- 優(yōu)化索引順序
alter table products drop index idx_price_category,add index idx_category_price(category_id,price);

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price > 900;
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | products | NULL       | range | idx_category_price | idx_category_price | 11      | NULL |   45 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

-- 改回去
alter table products drop index idx_category_price,add index idx_category(category_id);

-- 查詢特定分類的商品，products表有idx_category索引
EXPLAIN SELECT * FROM products WHERE category_id = 15;

ANALYZE TABLE products; -- 更新統(tǒng)計(jì)信息后，執(zhí)行計(jì)劃會(huì)選擇idx_category索引

-- 查詢用戶的訂單記錄，使用SELECT * 導(dǎo)致讀取不必要字段
SELECT * FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE u.user_id = 100;


SELECT u.username, o.order_id, o.order_time, o.amount
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE u.user_id = 100;

-- 統(tǒng)計(jì)2024 年每個(gè)用戶的訂單總金額（包含所有用戶，即使無(wú)訂單也顯示 0）
EXPLAIN ANALYZE SELECT
  u.user_id,
  u.username,
  -- 子查詢：統(tǒng)計(jì)該用戶2024年的訂單總金額（無(wú)訂單則返回NULL，用IFNULL轉(zhuǎn)為0）
  IFNULL((SELECT SUM(o.amount)
          FROM orders o
          WHERE o.user_id = u.user_id
            AND o.order_time >= '2024-01-01'
            AND o.order_time < '2025-01-01'), 0) AS total_2024_amount
FROM users u \G;
*************************** 1. row ***************************
EXPLAIN: -> Table scan on u  (cost=10098 rows=99776) (actual time=0.147..44.7 rows=100000 loops=1)
-> Select #2 (subquery in projection; dependent)
    -> Aggregate: sum(o.amount)  (cost=3.26 rows=1) (actual time=0.0155..0.0155 rows=1 loops=100000)
        -> Index lookup on o using idx_user_time (user_id=u.user_id), with index condition: ((o.order_time >= TIMESTAMP'2024-01-01 00:00:00') and (o.order_time < TIMESTAMP'2025-01-01 00:00:00'))  (cost=2.36 rows=9.02) (actual time=0.0133..0.0143 rows=4.74 loops=100000)

1 row in set, 1 warning (1.87 sec)


-- 覆蓋索引：包含所有查詢需要的字段
alter table orders drop index idx_user_time,add index idx_user_time_amount(user_id, order_time, amount);

EXPLAIN ANALYZE SELECT
  u.user_id,
  u.username,
  COALESCE(s.total_amount, 0) AS total_2024_amount
FROM users u
LEFT JOIN (
  SELECT
    user_id,
    SUM(amount) AS total_amount
  FROM orders
  WHERE order_time >= '2024-01-01'
    AND order_time < '2025-01-01'
  GROUP BY user_id  -- 預(yù)聚合訂單數(shù)據(jù)
) s ON u.user_id = s.user_id \G;
*************************** 1. row ***************************
EXPLAIN: -> Nested loop left join  (cost=1.01e+9 rows=10e+9) (actual time=706..892 rows=100000 loops=1)
    -> Table scan on u  (cost=10098 rows=99776) (actual time=0.175..39.2 rows=100000 loops=1)
    -> Index lookup on s using <auto_key0> (user_id=u.user_id)  (cost=113560..113562 rows=10) (actual time=0.00809..0.00831 rows=0.988 loops=100000)
        -> Materialize  (cost=113559..113559 rows=100725) (actual time=706..706 rows=98795 loops=1)
            -> Group aggregate: sum(orders.amount)  (cost=103487 rows=100725) (actual time=1.24..571 rows=98795 loops=1)
                -> Filter: ((orders.order_time >= TIMESTAMP'2024-01-01 00:00:00') and (orders.order_time < TIMESTAMP'2025-01-01 00:00:00'))  (cost=93205 rows=102819) (actual time=1.23..487 rows=473886 loops=1)
                    -> Covering index scan on orders using idx_user_time_amount  (cost=93205 rows=925560) (actual time=1.23..340 rows=1e+6 loops=1)

1 row in set (0.92 sec)

-- 查詢第100001-100020條訂單（偏移量10萬(wàn)）
EXPLAIN ANALYZE
SELECT * FROM orders
ORDER BY order_id  -- 按主鍵排序（默認(rèn)也可能按此排序）
LIMIT 100000, 20 \G;
*************************** 1. row ***************************
EXPLAIN: -> Limit/Offset: 20/100000 row(s)  (cost=1074 rows=20) (actual time=50.3..50.3 rows=20 loops=1)
    -> Index scan on orders using PRIMARY  (cost=1074 rows=100020) (actual time=2.84..46.1 rows=100020 loops=1)

1 row in set (0.05 sec)

-- 假設(shè)第100000行的order_id為100000（可從上次查詢獲?。?
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_id > 100000  -- 直接定位到偏移量位置
ORDER BY order_id
LIMIT 20 \G;  -- 僅取20行
*************************** 1. row ***************************
EXPLAIN: -> Limit: 20 row(s)  (cost=99909 rows=20) (actual time=2.08..2.09 rows=20 loops=1)
    -> Filter: (orders.order_id > 100000)  (cost=99909 rows=498729) (actual time=2.08..2.08 rows=20 loops=1)
        -> Index range scan on orders using PRIMARY over (100000 < order_id)  (cost=99909 rows=498729) (actual time=2.07..2.08 rows=20 loops=1)

1 row in set (0.01 sec)

-- 按訂單時(shí)間排序，查詢第1001-1020條已支付訂單（偏移量1000）
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 2
ORDER BY order_time
LIMIT 10000, 20 \G;
*************************** 1. row ***************************
EXPLAIN: -> Limit/Offset: 20/10000 row(s)  (cost=52012 rows=20) (actual time=23.9..23.9 rows=20 loops=1)
    -> Index lookup on orders using idx_status_time (status=2)  (cost=52012 rows=498729) (actual time=11.6..23.6 rows=10020 loops=1)

1 row in set (0.02 sec)

-- 假設(shè)上一頁(yè)最后一條記錄的order_time為'2025-05-01 10:00:00'，order_id為50000
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 2
  AND order_time >= '2025-05-01 10:00:00'  -- 用上一頁(yè)時(shí)間作為起點(diǎn)
  AND NOT (order_time = '2025-05-01 10:00:00' AND order_id <= 50000)  -- 排除同時(shí)間的前序記錄
ORDER BY order_time, order_id  -- 時(shí)間+ID聯(lián)合排序，避免重復(fù)/遺漏
LIMIT 20 \G;
*************************** 1. row ***************************
EXPLAIN: -> Limit: 20 row(s)  (cost=58539 rows=20) (actual time=12.3..12.3 rows=20 loops=1)
    -> Index range scan on orders using idx_status_time over (status = 2 AND '2025-05-01 10:00:00' <= order_time), with index condition: ((orders.`status` = 2) and (orders.order_time >= TIMESTAMP'2025-05-01 10:00:00') and ((orders.order_time <> TIMESTAMP'2025-05-01 10:00:00') or (orders.order_id > 50000)))  (cost=58539 rows=130086) (actual time=12.3..12.3 rows=20 loops=1)

1 row in set (0.02 sec)

-- 子查詢先獲取目標(biāo)頁(yè)的order_id，再關(guān)聯(lián)回表
EXPLAIN ANALYZE
SELECT o.*
FROM orders o
JOIN (
  -- 子查詢僅查ID，數(shù)據(jù)量小，排序/偏移高效
  SELECT order_id
  FROM orders
  WHERE status = 2
  ORDER BY order_time
  LIMIT 10000, 20  -- 大偏移量?jī)H處理ID，而非全字段
) tmp ON o.order_id = tmp.order_id \G;
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=52567 rows=20) (actual time=7.07..7.15 rows=20 loops=1)
    -> Table scan on tmp  (cost=50058..50060 rows=20) (actual time=7.04..7.04 rows=20 loops=1)
        -> Materialize  (cost=50058..50058 rows=20) (actual time=7.04..7.04 rows=20 loops=1)
            -> Limit/Offset: 20/10000 row(s)  (cost=50056 rows=20) (actual time=7.01..7.01 rows=20 loops=1)
                -> Covering index lookup on orders using idx_status_time (status=2)  (cost=50056 rows=498729) (actual time=2.36..6.37 rows=10020 loops=1)
    -> Single-row index lookup on o using PRIMARY (order_id=tmp.order_id)  (cost=0.25 rows=1) (actual time=0.00447..0.00453 rows=1 loops=20)

1 row in set (0.01 sec)