我要评论首先要声明的就是，千万级数据对于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'); -- 假设邮箱唯一
-- 将数据插入用户表
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
将users和orders的数据生成分开，这样可以通过多次调用orders存储过程多线程参数数据。
调用一次call create_users()，然后开15个窗口调用orders存储过程call generate_orders()。
整个过程会产生1000个用户，15*1000*1000也就是1500万条订单数据。
原始sql
这是一个很简单的sql，统计每个用户的订单总额。
在默认情况下，什么索引都没有创建，需要花费190+s的时间。
-- 第一个版本 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;
explain分析如下：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | simple | a | all | primary | 1000 | 100.0 | using temporary | ||||
| 1 | simple | b | all | 13016086 | 100.0 | using where; using join buffer (hash join) |
可以看到什么索引也没使用，type为all，直接全表扫描。
用时191s。
第一次优化：普通索引
把查询条件用到的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;
先看看expalin的结果：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | simple | a | index | primary,idx_users_user_id | primary | 4 | 1 | 100.0 | |||
| 1 | simple | b | ref | idx_orders_user_id | idx_orders_user_id | 5 | test2.a.user_id | 13003 | 100.0 |
type为index或者ref，全部走的索引。
查询结果却让人失望，这次用的时间更多，用了460+s。也就是说查询变慢了。
推测是由于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+s。所以创建索引得适度。
查询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;
先看看expalin的结果：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | simple | a | index | primary,idx_users_user_id | primary | 4 | 1 | 100.0 | |||
| 1 | simple | b | ref | idx_orders_user_id,idx_orders_user_id_total_amount | idx_orders_user_id_total_amount | 5 | test2.a.user_id | 874 | 100.0 | using index |
可以看到orders表的type从index提升到了ref。
此时的查询时间为从460s+降低到10s了。
结果证明覆盖索引能提升查询速度。
问题就在于这次建的两个覆盖索引，只有 idx_orders_user_id_total_amount 降低了查询时间，而 idx_orders_total_amount_user_id没有。
这个和mysql的关键词执行顺序有一定关系（推测，没找到资料）。
mysql执行顺序如下：
from on join where group by having select distinct union （all） order by limit
可以看到在覆盖索引使用过程先是where,再是到select的sum函数。这也是 idx_orders_user_id_total_amount 索引的创建顺序。
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;
drop掉相关的多余索引可以发现执行查询时间没有变化，仍然为10s。
索引优化这块差不多就是通过覆盖索引来命中索引。
第三次优化：减少数据量
减少数据量在业务上来说就是移除不必要的数据，或者可以在架构设计这块做一些工作。
分表就是这个原则。
通过这个方式能把千万的数据量减少到百万甚至几十万的量。提升的查询速度是可以想象的。
-- 第三次优化：减少数据量 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;
expain结果如下：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | simple | a | range | primary,idx_users_user_id | primary | 4 | 685 | 100.0 | using where | ||
| 1 | simple | b | ref | idx_orders_user_id_total_amount | idx_orders_user_id_total_amount | 5 | test2.a.user_id | 874 | 100.0 | using index |
可以看到users表的type为range。能过滤一部分数据量。
查询时间从10s降低到7s，减少数据量证明有效。
第四次优化：小表驱动大表
在 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的表修改为子查询，能提前过滤一部分数据量。
expain结果如下：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | simple | a | range | primary,idx_users_user_id | primary | 4 | 685 | 100.0 | using where | ||
| 1 | simple | c | ref | idx_orders_user_id_total_amount | idx_orders_user_id_total_amount | 5 | test2.a.user_id | 874 | 100.0 | using where; using index |
可以看到explain没什么变化。实际执行效果也没啥变化。
小表驱动大表在这里无效，但是可以结合具体的业务进行优化sql。这个策略是没问题的。
第五次优化：强制索引
当 mysql 中的 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更加复杂。这里由于临时创建的订单用户表没复现。
当你发现explain都是命中索引的，但是查询依然很慢。这个强制索引可以试试。
优化策略
- 提前命中索引，小表驱动大表
- 千万级数据in索引失效，进行强制索引
- 使用覆盖索引解决回表问题
下次该怎么优化sql
- 数据接近千万级，需要分表，比如按照用户id取模分表。
- 用汇总表代替子查询来命中索引，比如把小时表生成日表、月表汇总数据。
- 关联字段冗余、直接放到一张表就是单表查询了。
- 命中索引，空间换时间，这也是本文分析的场景。
关于命中索引核心点就是覆盖索引，再者是千万数据产生的特有场景需要走强制索引。
tips
explain结果type的含义
在 mysql 的 explain 查询结果中，type 字段表示了查询使用的访问类型，即查询执行过程中的访问方法。
根据不同的访问类型，mysql 查询优化器将选择不同的执行计划。以下是 type 字段可能的取值及其含义：
- system： 这是最好的情况，表示查询只返回一行结果。这通常是通过直接访问表的
primary key或唯一索引来完成的。 - const： 表示 mysql 在查询中找到了常量值，这是在连接的第一个表中进行的。由于这是常量条件，mysql 只会读取一次表中的一行数据。例如，通过主键访问一行数据。
- eq_ref： 类似于
const，但在使用了索引的情况下。此类型的查询是通过某个唯一索引来访问表的，对于每个索引键值，表中只有一行匹配。常见于使用主键或唯一索引进行连接操作。 - ref： 表示此查询使用了非唯一索引来查找值。返回的是所有匹配某个单独值的行。该类型一般出现在联接操作中，使用了非唯一索引或者索引前缀。
- range： 表示查询使用了索引来进行范围检索，通常出现在带有范围条件的查询语句中，例如
between、in()、>、<等。 - index： 表示 mysql 将扫描整个索引来找到所需的行。这通常是在没有合适的索引的情况下，mysql 会选择使用这种访问类型。
- all： 表示 mysql 将扫描全表以找到所需的行，这是最差的情况。这种情况下，mysql 将对表中的每一行执行完整的扫描。
通常来说，type 字段的排序从最好到最差依次是 system、const、eq_ref、ref、range、index、all，当然，实际情况取决于查询的具体情况、表结构和索引的使用情况。更好的查询性能通常对应着更好的 type 类型。
mysql的回表机制
在 mysql 中，回表（"ref" or "bookmark lookup" in english）是指在使用索引进行查询时，mysql 首先通过索引找到满足条件的行的位置，然后再回到主表（或称为数据表）中查找完整的行数据的过程。
这个过程通常发生在某些查询中，特别是涉及到覆盖索引无法满足查询需求时。
当一个查询不能完全通过索引满足时，mysql 就需要回到主表中查找更多的信息。这种情况通常出现在以下几种情况下：
- 非覆盖索引查询： 如果查询需要返回主表中未包含在索引中的其他列的数据时，mysql 就需要回到主表中查找这些额外的列数据。
- 使用索引范围条件： 当查询中使用了范围条件（例如
between、>、<等），而索引只能定位到范围起始位置时，mysql 需要回到主表中检查满足范围条件的完整行。 - 使用了聚簇索引但需要查找的列不在索引中： 在使用了聚簇索引的表中，如果需要查询的列不在聚簇索引中，mysql 需要回到主表中查找这些列的数据。
当 mysql 需要执行回表操作时，会发生额外的磁盘访问，因为需要读取主表中的数据。这可能会导致性能下降，特别是在大型数据表中或者在高并发环境中。
为了尽量减少回表操作的发生，可以考虑以下几点：
- 创建覆盖索引：确保查询所需的所有列都包含在索引中，从而避免回表操作。
- 优化查询语句：尽量避免使用范围条件，或者确保所有的过滤条件都可以被索引完全匹配。
- 考虑表设计：在设计数据库表结构时，可以考虑将常用的查询字段都包含在索引中，以减少回表操作的发生。
以上就是mysql千万级数据从190秒优化到1秒的全过程的详细内容，更多关于mysql千万级数据优化的资料请关注代码网其它相关文章！
发表评论