先创建测试数据表
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;
再创建存储过程生成千万测试数据
创建用户表的存储过程
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'); -- 假设邮箱唯一
-- 将数据插入用户表
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之间的随机总金额
-- 将数据插入订单表
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
进入测试阶段
1. 不加索引测试查询用户的订单总额
explain
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
执行结果时间如下，700多s还没结束
2. 加普通索引
把查询条件用到的sql条件都创建索引。也就是where和join、sum涉及到的知道。
-- 添加普通索引
create index idx_orders_user_id on orders (user_id);
create index idx_orders_total_amount on orders(total_amount);
再执行explain语句看看
type为index，ref，全部有走索引
但这里没显示使用，可能走回表扫描
看下执行速度，执行300多s，看来还是
3. 覆盖索引
覆盖索引就是一个索引包含了要查询的所有列
sql的执行顺序
所以按照select部分，那就是创建联合索引，
-- 删除原来索引
drop index idx_orders_user_id on orders;
drop index idx_orders_total_amount on orders;
-- 添加覆盖索引
create index idx_orders_user_id_total_amount on orders(user_id, total_amount);
这次加索引还挺快的
再执行explain看看，这次走索引了，extra列也有using index了
执行看看有没有变快，这次从几百s到10几s
证明了覆盖索引提升了查询速度，但还是不够快
4. 进行第三步优化，减少数据量
减少数据量就是减少查询不必要的数据量
比如你你要查询的是user_id大于50的用户总金额【具体情况再具体分析】
-- 查询user_id大于50的
explain
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 > 50
group by a.user_id
再执行explain看看，index变成了range
explain执行结果中，type类型执行效率排序
再执行下看下速度看看，15s比上次好一点点
5. 接下来就是小表驱动大表
-- 小表驱动大表
explain
select
a.*,
sum(b.total_amount) as total
from users a
left join (
select
user_id,
total_amount
from orders
where user_id > 60
) b
on a.user_id = b.user_id
where a.user_id > 60
group by a.user_id
执行结果
看下执行速度，14s多比刚才好一点点
6. 使用强制索引
-- 强制索引
explain
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 a.user_id in (10,20,30,40,50, 60, 70, 80, 90)
group by a.user_id
使用后和使用前在这里显示不是很大，一般都是有效果的
回表机制
回表就是在查询过程中，命中索引，会找到索引所在行位置，再回到主表去找行的完整数据的过程
总结
- 提前命中索引，小表驱动大表
- 数据接近千万级，要进行分表
- 如果是汇总数据，那平时，做汇总之前的数据，做汇总表会更快
- 使用覆盖索引解决回表问题
- 千万级in查询索引失效，那可以使用强制索引
- 查询select时尽量查询指定字段不要select * 所有字段
