MySQL / 系统课时

搭建可复现的优化实验环境

搭建可复现的优化实验环境

查询优化实验只有在版本、表结构、数据规模和执行方式稳定时才有可比性。本课用 MySQL 8.4 搭建一个最小实验环境,创建固定结构和可重复生成的数据,并给出清理、重建与验证方法,保证后续计划和索引实验可以反复执行。

学习目标

  • 准备可验证版本的 MySQL 8.4 实验实例并确认关键环境信息
  • 创建固定结构的样例表,并生成稳定规模的数据集
  • 用可重复脚本完成重建与校验,避免数据漂移干扰执行计划比较

检查 MySQL 版本与基础状态

本课只依赖 MySQL 8.4 的命令行客户端和一个可写数据库。先确认服务可连接,并记录版本信息。实际主机、端口和用户名请替换为自己的值:

bash
mysql -h 127.0.0.1 -P <PORT> -u root -p -e "SELECT VERSION() AS version, @@version_comment AS edition;"

预期输出包含 8.4.x 版本号;补丁版本会随安装介质不同而变化:

text
+-------------+------------------------------+
| version     | edition                      |
+-------------+------------------------------+
| 8.4.<VALUE> | MySQL Community Server - GPL |
+-------------+------------------------------+

如果连接失败,先确认 MySQL 服务已启动、端口映射正确,并检查账号是否允许从当前主机连接。实验阶段不要一边修改服务参数、一边比较 SQL 性能,否则很难判断差异来自查询本身还是环境变化。

创建固定的样例结构

建立独立数据库,避免和已有业务表混用。样例采用订单表,后续可以直接用于选择性、联合索引、覆盖索引和排序实验。

sql
DROP DATABASE IF EXISTS query_lab;
CREATE DATABASE query_lab;
USE query_lab;

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id BIGINT UNSIGNED NOT NULL,
    status ENUM('NEW','PAID','SHIPPED','CANCELLED') NOT NULL,
    created_at DATETIME NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    note VARCHAR(200) NULL,
    PRIMARY KEY (id),
    KEY idx_customer_id (customer_id)
) ENGINE=InnoDB;

预期结果是数据库与表创建成功;CREATE TABLE 成功时客户端通常返回类似信息:

text
Query OK, 0 rows affected

这里暂时只保留主键和一个单列索引。后续实验新增或删除索引时,基线结构不会混入尚未讨论的优化策略。

生成稳定规模的数据

为了让执行计划具有观察价值,数据量不能太小。下面的存储过程固定生成 100000 行,并使用确定性表达式构造客户、状态、时间和金额分布;重复重建后数据规模与分布保持一致。

sql
USE query_lab;

DELIMITER //

CREATE PROCEDURE seed_orders(IN p_rows INT)
BEGIN
    DECLARE i INT DEFAULT 1;

    START TRANSACTION;
    WHILE i <= p_rows DO
        INSERT INTO orders(customer_id, status, created_at, amount, note)
        VALUES (
            1 + MOD(i, 10000),
            ELT(1 + MOD(i, 4), 'NEW', 'PAID', 'SHIPPED', 'CANCELLED'),
            TIMESTAMP('2025-01-01 00:00:00') + INTERVAL MOD(i, 31536000) SECOND,
            10.00 + MOD(i * 37, 50000) / 100,
            IF(MOD(i, 10) = 0, CONCAT('batch-', MOD(i, 100)), NULL)
        );

        SET i = i + 1;
    END WHILE;
    COMMIT;
END//

DELIMITER ;

CALL seed_orders(100000);

预期结果是过程创建成功,并插入 100000 行。过程调用完成后客户端会返回成功状态,耗时会随机器配置不同:

text
Query OK, 0 rows affected

逐行插入的目的不是模拟最高吞吐写入,而是让数据生成逻辑简单、确定且易复现。若机器资源有限,可以减少行数,但同一组对比实验必须保持相同规模。

校验数据与建立重置脚本

生成后先检查总行数、客户基数和时间范围,确认数据没有缺失或重复累加:

sql
SELECT
    COUNT(*) AS row_count,
    COUNT(DISTINCT customer_id) AS customers,
    MIN(created_at) AS min_created_at,
    MAX(created_at) AS max_created_at
FROM query_lab.orders;

预期结果中,行数应为 100000,客户基数应为 10000;时间范围由生成表达式决定:

text
+-----------+-----------+---------------------+---------------------+
| row_count | customers | min_created_at      | max_created_at      |
+-----------+-----------+---------------------+---------------------+
|    100000 |     10000 | 2025-01-01 00:00:01 | 2025-01-02 03:46:40 |
+-----------+-----------+---------------------+---------------------+

为了保证实验可以从同一状态重新开始,保存一份重置脚本,例如 <PATH>/reset-query-lab.sql。其中先清空数据,再重新播种:

sql
USE query_lab;

TRUNCATE TABLE orders;
CALL seed_orders(100000);
ANALYZE TABLE orders;

预期结果是旧数据被清空、固定的 100000 行重新生成,并刷新表统计信息。再次执行上一条校验 SQL 后,row_count 与 customers 应仍分别为 100000 和 10000。

固定后续实验条件

做执行计划对比时,每次只改变一个变量,例如新增一个索引或改写一个谓词,其余条件保持不变。至少固定 MySQL 主版本、表结构、数据规模、查询文本和会话级参数;比较前后都记录 EXPLAIN ANALYZE 结果。

如果一次优化看起来“突然快了很多”,还要考虑缓存热度带来的影响。需要更严谨的实验时,可重复执行多次并记录稳定区间,而不是只比较第一次和第二次运行。可复现环境的价值就在于:任何计划变化都能被重新构造、重新测量,并明确归因到你主动修改的那一个因素。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

搭建可复现的优化实验环境

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通