将MySQL查询优化从190秒缩短至1秒：数千万条记录的处理
首先，必须明确一点，处理数千万条数据通常并不是MySQL的最佳用例。
优化MySQL以处理数千万条记录有几种策略：
在本讨论中，我们考虑的是一个单张MySQL表包含数千万条记录的场景。
该表的设计不佳，业务规则不允许将SQL查询拆分为多个子查询。
在这种情况下，开发者可以通过优化SQL来尝试实现他们的查询目标。
当一个MySQL表包含数千万条记录时，就需要特别注意了。
本讨论主要关注极端情况下的SQL优化策略。
生成数千万条记录
我们将使用存储过程来生成1000万条记录。
以下是表结构：
CREATE TABLE `orders` (
`order_id` int NOT NULL AUTO_INCREMENT,
`user_id` int DEFAULT NULL,
`order_date` date NOT NULL,
`total_amount` decimal(10,2) NOT NULL,
PRIMARY KEY (`order_id`),
KEY `idx_user_id` (`user_id`) USING BTREE,
KEY `idx_user_amount` (`user_id`,`total_amount`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `users` (
`user_id` int NOT NULL AUTO_INCREMENT,
`username` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
`email` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`),
KEY `idx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;生成数据的存储过程如下。
用户数据：
-- 存储过程生成1000个用户
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_users`()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE total_users INT DEFAULT 1000; -- 调整用户数量
DECLARE rnd_username VARCHAR(50);
DECLARE rnd_email VARCHAR(100);
WHILE i < total_users DO
-- 生成随机的用户名和电子邮件
SET rnd_username = CONCAT('User', FLOOR(1 + RAND() * 10000000)); -- 假设用户名唯一
SET rnd_email = CONCAT(rnd_username, '@example.com'); -- 假设电子邮件唯一
-- 将数据插入到users表中
INSERT INTO users (username, email) VALUES (rnd_username, rnd_email);
SET i = i + 1;
END WHILE;
END订单数据生成存储过程：
CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_orders`()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE total_users INT DEFAULT 1000; -- 用户数量
DECLARE total_orders_per_user INT DEFAULT 1000; -- 每个用户的订单数量
DECLARE rnd_user_id INT;
DECLARE rnd_order_date DATE;
DECLARE rnd_total_amount DECIMAL(10, 2);
DECLARE j INT DEFAULT 0;
WHILE i < total_users DO
-- 获取用户ID
SELECT user_id INTO rnd_user_id FROM users LIMIT i, 1;
WHILE j < total_orders_per_user DO
-- 生成订单日期和总金额
SET rnd_order_date = DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND() * 1096) DAY); -- 随机日期，介于2020-01-01和2022-12-31之间
SET rnd_total_amount = ROUND(RAND() * 1000, 2); -- 随机总金额，介于0和1000之间
-- 将数据插入到orders表中
INSERT INTO orders (user_id, order_date, total_amount) VALUES (rnd_user_id, rnd_order_date, rnd_total_amount);
SET j = j + 1;
END WHILE;
SET j = 0;
SET i = i + 1;
END WHILE;
END分离users和数据的生成过程，以便多次调用存储过程，利用多线程。
首先调用call ()，然后打开15个窗口调用订单生成存储过程call ()。
整个过程将生成1000个用户和15*1000*1000，即1500万条订单记录。
原始SQL
这是一个非常简单的SQL查询varchar(50)中50代表的含义，用于计算每个用户的总订单金额。
在没有默认创建索引的情况下，这个查询需要超过190秒才能执行。
-- 第一版
SELECT a.*, sum(b.total_amount) as total
FROM users a
LEFT JOIN orders b ON a.user_id = b.user_id
GROUP BY a.user_id;查询分析如下：
正如我们所见，没有使用任何索引，查询类型为 "ALL"，意味着全表扫描。
执行时间：191秒。
第一次优化：常规索引
在SQL条件中使用的所有列上创建索引，包括WHERE、JOIN和SUM。
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_total_amount ON orders (total_amount);
CREATE INDEX idx_users_user_id ON users (user_id);SQL查询保持不变：
-- 第一版
SELECT a.*, sum(b.total_amount) as total
FROM users a
LEFT JOIN orders b ON a.user_id = b.user_id
GROUP BY a.user_id;让我们检查一下执行计划：
类型变为 "index" 或 "ref"，所有查询都使用了索引。
然而，结果令人失望。执行时间增加到了超过460秒。
这表明查询变慢是因为MySQL使用主键定位相关字段的一种机制。
第二次优化：覆盖索引
覆盖索引是一个包含满足查询所需的所有列的索引，允许查询直接从索引中执行，而不需要访问实际的数据行。
-- 不要删除常规索引
-- drop INDEX idx_orders_user_id ON orders;
-- drop INDEX idx_orders_total_amount ON orders;
CREATE INDEX idx_orders_total_amount_user_id ON orders (total_amount, user_id);
CREATE INDEX idx_orders_user_id_total_amount ON orders (user_id, total_amount);在1500万条记录上创建索引需要超过300秒，因此索引应该谨慎创建。
SQL查询保持不变：
-- 第一版
SELECT a.*, sum(b.total_amount) as total
FROM users a
LEFT JOIN orders b ON a.user_id = b.user_id
GROUP BY a.user_id;让我们检查一下执行计划：
我们可以看到，表的类型已从 "index" 更改为 "ref"。
查询时间现在已从460多秒减少到10秒。
这个结果证明了覆盖索引可以提高查询性能。
关键观察点是，只有索引减少了查询时间，而没有做到。
这部分的解释与MySQL关键字的执行顺序（假设的varchar(50)中50代表的含义，因为没有找到确切的来源）有关。
MySQL执行顺序：
shell from
on
join
where
group by
having
select
distinct
union (all)
order by
limit覆盖索引在的SUM函数之前使用了WHERE子句，这与索引的创建顺序一致。
drop INDEX idx_orders_user_id ON orders;
drop INDEX idx_orders_total_amount ON orders;
drop INDEX idx_orders_total_amount_user_id ON orders;删除冗余索引显示执行时间保持不变，仍然是10秒。
因此，这里的优化策略是使用覆盖索引来有效地击中索引。
第三次优化：减少数据量
减少数据量涉及删除不必要的数据或进行架构调整。一种实现方式是分割表。
通过这种方式，数据量可以从数千万减少到数百万甚至数十万，从而显著提高查询速度。
-- 第三次优化：减少数据量
SELECT a.*, sum(b.total_amount) as total
FROM users a
LEFT JOIN orders b ON a.user_id = b.user_id
WHERE a.user_id > 1033
GROUP BY a.user_id;执行计划如下：
正如我们所见，users表的类型是 "range"，过滤了数据的一部分。
查询时间从10秒减少到7秒，证明了减少数据量是有效的。
第四次优化：小表驱动大表
在MySQL中，优化器通常会根据查询条件和表的大小选择最合适的驱动表。
“小表驱动大表”策略涉及在连接查询中选择较小的表作为驱动表，以减少内存使用和处理时间。
基于第三次优化的结果，让我们尝试这种策略。
-- 第三版，小表驱动大表，没有显著效果
SELECT a.*, sum(b.total_amount) as total
FROM users a
LEFT JOIN (SELECT user_id, total_amount FROM orders c WHERE c.user_id > 1033) b ON a.user_id = b.user_id
WHERE a.user_id > 1033
GROUP BY a.user_id;这个更改将LEFT JOIN表修改为子查询，预先过滤了一些数据。
执行计划如下：
正如我们所见，执行计划没有太大变化，实际的执行性能保持不变。
虽然“小表驱动大表”策略在这里没有效果，但它仍然可能是一个有效的优化策略，具体取决于特定的业务逻辑。
第五次优化：强制使用索引
当在涉及数千万条记录的查询中使用IN子句时，设计不佳的索引可能会导致索引效率低下，影响查询性能。
通常，MySQL优化器会选择最佳的执行计划，包括适当的索引。然而，对于大量数据的IN子句查询，MySQL可能无法有效地使用索引，导致全表扫描或索引效率低下。
以下SQL演示了这一点，由于IN数据不是稀疏的，强制索引和不强制索引产生了相似的结果：
-- 第五版，强制索引
SELECT a.*, sum(b.total_amount) as total
FROM users a
LEFT JOIN orders b FORCE INDEX (idx_orders_user_id_total_amount) ON a.user_id = b.user_id
WHERE b.user_id IN (1033, 1034, 1035, 1036, 1037, 1038)
GROUP BY a.user_id;
-- 第五版，不强制索引
SELECT a.*, sum(b.total_amount) as total
FROM users a
LEFT JOIN orders b ON a.user_id = b.user_id
WHERE b.user_id IN (1033, 1034, 1035, 1036, 1037, 1038)
GROUP BY a.user_id;在这两种情况下，查询时间都不到一秒。
在实际的商业场景中，可能会出现查询虽然根据执行计划命中了索引，但仍然很慢的情况。在这种情况下，可以尝试强制使用索引。
优化策略优化SQL Tips
命中索引的核心是覆盖索引。对于数千万条数据量的情况，可能需要使用强制索引。
小贴士结果中type的含义
在MySQL的查询结果中，type字段表示查询使用的访问类型，代表查询执行过程中的访问方法。
根据访问类型，MySQL的查询优化器将选择不同的执行计划。以下是type字段的可能值及其含义：
通常，type字段的值按从好到坏的顺序排列为、const、、ref、range、index、all。然而，实际情况取决于特定的查询、表结构和索引使用。更好的查询性能通常对应于更好的type值。
MySQL的表查找机制
在MySQL中，表查找（也称为“ref”或“ ”）指的是MySQL首先使用索引找到满足条件的行位置，然后查找主表中的实际数据行。
这个过程通常发生在覆盖索引无法满足所有查询要求的情况下。
当查询无法完全由索引满足时，MySQL需要查找主表以获取额外信息。这通常发生在以下情况：
当MySQL执行表查找时，由于需要从主表读取数据，因此会发生额外的磁盘访问，这可能会降低性能，尤其是在大型表或高并发环境中。
为了最小化表查找，请考虑以下建议：