-- 用戶表
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;

    -- 插入當前批次的記錄
    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í)行存儲過程
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;

    -- 插入當前批次的記錄
    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庫存
      );
      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;

    -- 插入當前批次的記錄
    WHILE i <= total AND i <= (batch_size * FLOOR((i-1)/batch_size) + batch_size) DO
      -- 優(yōu)化：預先計算隨機商品ID和價格，避免子查詢
      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,    -- 隨機用戶
        rand_product_id,                 -- 隨機商品
        DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY), -- 近1年訂單
        product_price * (FLOOR(RAND() * 5) + 1),            -- 1-5件數(shù)量
        FLOOR(RAND() * 3) + 1             -- 隨機狀態(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 |
+----------+

-- 對任意SELECT查詢執(zhí)行分析
EXPLAIN SELECT 列名 FROM 表名 WHERE 條件;
-- 支持復雜查詢（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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

-- 默認是關閉的
show global variables like 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name   | Value                    |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+

-- 開啟跟蹤（修改當前會話）
SET optimizer_trace = "enabled=on";

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

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

-- 查看跟蹤結果
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


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