MySQL / 系统课时
搭建可复现的优化实验环境
搭建可复现的优化实验环境
查询优化实验只有在版本、表结构、数据规模和执行方式稳定时才有可比性。本课用 MySQL 8.4 搭建一个最小实验环境,创建固定结构和可重复生成的数据,并给出清理、重建与验证方法,保证后续计划和索引实验可以反复执行。
学习目标
- 准备可验证版本的 MySQL 8.4 实验实例并确认关键环境信息
- 创建固定结构的样例表,并生成稳定规模的数据集
- 用可重复脚本完成重建与校验,避免数据漂移干扰执行计划比较
检查 MySQL 版本与基础状态
本课只依赖 MySQL 8.4 的命令行客户端和一个可写数据库。先确认服务可连接,并记录版本信息。实际主机、端口和用户名请替换为自己的值:
mysql -h 127.0.0.1 -P <PORT> -u root -p -e "SELECT VERSION() AS version, @@version_comment AS edition;"预期输出包含 8.4.x 版本号;补丁版本会随安装介质不同而变化:
+-------------+------------------------------+
| version | edition |
+-------------+------------------------------+
| 8.4.<VALUE> | MySQL Community Server - GPL |
+-------------+------------------------------+如果连接失败,先确认 MySQL 服务已启动、端口映射正确,并检查账号是否允许从当前主机连接。实验阶段不要一边修改服务参数、一边比较 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 成功时客户端通常返回类似信息:
Query OK, 0 rows affected这里暂时只保留主键和一个单列索引。后续实验新增或删除索引时,基线结构不会混入尚未讨论的优化策略。
生成稳定规模的数据
为了让执行计划具有观察价值,数据量不能太小。下面的存储过程固定生成 100000 行,并使用确定性表达式构造客户、状态、时间和金额分布;重复重建后数据规模与分布保持一致。
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 行。过程调用完成后客户端会返回成功状态,耗时会随机器配置不同:
Query OK, 0 rows affected逐行插入的目的不是模拟最高吞吐写入,而是让数据生成逻辑简单、确定且易复现。若机器资源有限,可以减少行数,但同一组对比实验必须保持相同规模。
校验数据与建立重置脚本
生成后先检查总行数、客户基数和时间范围,确认数据没有缺失或重复累加:
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;时间范围由生成表达式决定:
+-----------+-----------+---------------------+---------------------+
| 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。其中先清空数据,再重新播种:
USE query_lab;
TRUNCATE TABLE orders;
CALL seed_orders(100000);
ANALYZE TABLE orders;预期结果是旧数据被清空、固定的 100000 行重新生成,并刷新表统计信息。再次执行上一条校验 SQL 后,row_count 与 customers 应仍分别为 100000 和 10000。
固定后续实验条件
做执行计划对比时,每次只改变一个变量,例如新增一个索引或改写一个谓词,其余条件保持不变。至少固定 MySQL 主版本、表结构、数据规模、查询文本和会话级参数;比较前后都记录 EXPLAIN ANALYZE 结果。
如果一次优化看起来“突然快了很多”,还要考虑缓存热度带来的影响。需要更严谨的实验时,可重复执行多次并记录稳定区间,而不是只比较第一次和第二次运行。可复现环境的价值就在于:任何计划变化都能被重新构造、重新测量,并明确归因到你主动修改的那一个因素。