【MySQL】百万数据调优MySQL执行计划分析“三步曲” 1. 查看 SQL 执行计划：EXPLAIN SQL 2.

一、前言

在阿里云上申请 RDS，机器配置如下：

MySQL 8.0
CPU 核数：1
内存：1GB
最大连接数：2000
最大 IOPS ：10000
最大IO带宽：1024Mbps

MySQL 执行计划分析“三步曲” ：

查看 SQL 执行计划：EXPLAIN SQL
通过 Profile 定位 QUERY 代价消耗
通过 Optimizer Trace 表查看 SQL 执行计划树

基本 SQL 优化案例有：

根据 Extra 列优化
根据 Rows 列优化
group by 和 order by 优化
Limit 分页优化
count 优化

二、`SQL` 测试

（1）测试索引排序

在不加索引的情况下, EXPLAIN 如下:

EXPLAIN SELECT invite_id as inviteId, invite_code as inviteCode, create_time as createTime
FROM invite_code
WHERE owner_id = 'u_7bbf522985ff5936eb4909776874969f'
ORDER BY create_time DESC;

同时，单次执行查询语句耗时: 403 ms。

1. 加索引

加上索引： ALTER TABLEinvite_codeADD INDEX idx_owner_create_time (owner_id,create_time);

EXPLAIN SELECT invite_id as inviteId, invite_code as inviteCode, create_time as createTime
FROM invite_code
WHERE owner_id = 'u_7bbf522985ff5936eb4909776874969f'
ORDER BY create_time DESC;

同时，单次执行查询语句耗时: 22 ms。

Backward-index-scan 是什么？

Backward index scan 是 MySQL-8.0.x 针对上面场景的一个专用优化项，它可以从索引的后面往前面读。引入反向索引后，InnoDB 现在可以按照降序顺序存储数据行，优化器将在查询中请求降序时利用它。

2. 加索引排序

操作如下：

DROP INDEX idx_owner_create_time ON invite_code;

ALTER TABLE `invite_code` ADD INDEX idx_owner_create_time (`owner_id`, `create_time` DESC);

再执行 EXPLAIN：

EXPLAIN SELECT invite_id as inviteId, invite_code as inviteCode, create_time as createTime
FROM invite_code
WHERE owner_id = 'u_7bbf522985ff5936eb4909776874969f'
ORDER BY create_time DESC;

同时，单次执行查询语句耗时: 21 ms。

说明：在 MySQL 8.0 下，索引加不排序，效果差不多。

（2）测试大表驱动小表

从 invite_code 表中，选择 1000 条插入 user_invite_code 表中。

大表关联小表：

SELECT * 
FROM invite_code i JOIN user_invite_code u ON i.owner_id = u.account_id

耗时：70ms，10次平均耗时 64ms

小表关联大表：

SELECT * 
FROM user_invite_code u JOIN invite_code i ON i.owner_id = u.account_id

耗时 55ms，10次平均耗时 60ms

1. 加上条件筛选

次表筛选放 WHERE 中

EXPLAIN SELECT * 
FROM user_invite_code u JOIN invite_code i ON i.owner_id = u.account_id
WHERE i.is_expired = 0

次表筛选放 JOIN 中

EXPLAIN SELECT * 
FROM user_invite_code u JOIN invite_code i ON i.owner_id = u.account_id AND i.is_expired = 0

说明：在 MySQL 8.0 下，这两种方式基本没有差异。

三、生成百万数据量

思路一：

创建内存表：往内存表写数据（随机生成一批数据）
创建业务表：写入内存表数据
删除内存表里的数据
循环 1 ~ 3 的步骤

Tips：阿里云里禁止使用 ENGINE=MEMORY，所以思路一可以先放一放。

思路二：直接存储过程进行生成

# 业务数据库：
CREATE TABLE IF NOT EXISTS `invite_code` (
`invite_id` VARCHAR(64) NOT NULL COMMENT '邀请码Id',
`invite_code` VARCHAR(16) NOT NULL COMMENT '邀请码',
`owner_id` VARCHAR(64) NOT NULL COMMENT '账号Id',
`expire_time` DATETIME NOT NULL COMMENT '有效时间',
`is_permanent` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否永久, 0 否, 1 是',
`is_expired` BIGINT NOT NULL DEFAULT 0 COMMENT '是否过期',
`create_time` DATETIME NOT NULL COMMENT '创建时间',
`modify_time` DATETIME NOT NULL COMMENT '更新时间',
PRIMARY KEY (`invite_id`),
UNIQUE KEY `uni_invite_code` (`invite_code`, `is_expired`)
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '邀请码';

# 存储过程: 向业务表插入 n 条数据
DELIMITER $$
CREATE PROCEDURE `insertBatchMemory`(IN n int)
BEGIN
	DECLARE i INT DEFAULT 1;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SELECT 'error!!!';
    WHILE (i <= n) DO
    	INSERT INTO invite_code (invite_id, invite_code, owner_id, expire_time, 
                                 is_permanent, is_expired, create_time, modify_time)
    	VALUES ('i_' + MD5(UUID()), SUBSTRING(MD5(UUID()), 4, 8), 
                'u_' + MD5(UUID()), NOW(), 0, 0, NOW(), NOW());
    	SET i = i + 1;
    END WHILE;
END $$
DELIMITER ;

# 存储过程： 调用 n 次，每批次 count 个
DELIMITER $$
CREATE PROCEDURE `insertInviteCodeBatch`(IN n int, IN count int)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE (i <= n) DO
        CALL insertBatchMemory(count);
        SET i = i + 1;
    END WHILE;
END $$
DELIMITER ;


# 单次调用
CALL insertBatchMemory(10000);


# 批量调用
CALL insertInviteCodeBatch(100, 10000);

调用耗时结果如下：

- - -共(1)条语句执行- - -

[语句1]:
CALL insertInviteCodeBatch(100, 10000)
[Success] ，耗时：133,232(ms)
影响行数：1

执行结束,成功执行(1)条!

查询数据量如下：

【MySQL】百万数据调优