MySQL怎样优化千万级数据

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
简介: MySQL在处理千万级数据时可能存在性能挑战。本文介绍了几个优化策略来改善查询效率

首先要声明的就是,千万级数据对于MySQL来说就是不太合理的一个存在。

优化MySQL千万级数据策略还是比较多的。

  • 分表分库
  • 创建中间表,汇总表
  • 修改为多个子查询

这里讨论的情况是在MySQL一张表的数据达到千万级别。表设计很烂,业务统计规则又不允许把sql拆成多个子查询。

在这样的情况下,开发者可以尝试通过优化SQL来达到查询的目的。

当MySQL一张表的数据达到千万级别,会出现一些特殊的情况。这里主要是讨论在比较极端的情况下SQL的优化策略。

先来个千万级数据

通过存储过程传递函数制造1000万条数据。

表结构如下:

sql

复制代码

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;

造数据的存储过程如下。

用户数据:

sql

复制代码

-- 产生用户存储过程,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

订单数据生成存储过程如下:

sql

复制代码

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的时间。

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;

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涉及到的知道。

sql

复制代码

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仍然是第一个版本。

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的回表机制导致查询变得更慢了。所以接下来继续优化索引。

第二次优化:覆盖索引

覆盖索引是指一个索引包含了查询所需的所有列,从而可以满足查询的要求,而不需要访问实际的数据行。

通常情况下,数据库查询需要根据索引定位到对应的数据行,然后再从数据行中获取所需的列值。

而当索引中包含了查询所需的所有列时,数据库引擎可以直接通过索引就能够满足查询的要求,无需访问实际的数据行,这样就可以提高查询性能。

这也是普通索引添加了还是查询慢的原因,因为普通索引命中了还是会去找主键,通过主键找到关联字段的值做过滤。

sql

复制代码

-- 先不删除普通索引
-- 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还是第一个版本。

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执行顺序如下:

shell

复制代码

from
on
join
where
group by
having
select
distinct
union (all)
order by
limit

可以看到在覆盖索引使用过程先是where,再是到select的sum函数。这也是 idx_orders_user_id_total_amount 索引的创建顺序。

sql

复制代码

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。

索引优化这块差不多就是通过覆盖索引来命中索引。

第三次优化:减少数据量

减少数据量在业务上来说就是移除不必要的数据,或者可以在架构设计这块做一些工作。

分表就是这个原则。

通过这个方式能把千万的数据量减少到百万甚至几十万的量。提升的查询速度是可以想象的。

sql

复制代码

-- 第三次优化:减少数据量
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 中,通常情况下,优化器会根据查询条件和表的大小选择合适的驱动表(即主导表)。

小表驱动大表是一种优化策略,它指的是在连接查询中,优先选择小表作为驱动表,以减少连接操作所需的内存和处理时间。

在第三次优化的结果上,可以尝试使用小表驱动大表优化策略。

sql

复制代码

-- 第三个版本,小标驱动大表  没啥效果
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的数据量不是很稀疏,实际查询强制索引和普通索引效果一致

sql

复制代码

-- 第五个版本,强制索引 
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: 表示查询使用了索引来进行范围检索,通常出现在带有范围条件的查询语句中,例如 BETWEENIN()>、<等。
  • index: 表示 MySQL 将扫描整个索引来找到所需的行。这通常是在没有合适的索引的情况下,MySQL 会选择使用这种访问类型。
  • all: 表示 MySQL 将扫描全表以找到所需的行,这是最差的情况。这种情况下,MySQL 将对表中的每一行执行完整的扫描。

通常来说,type 字段的排序从最好到最差依次是 systemconsteq_refrefrangeindexall,当然,实际情况取决于查询的具体情况、表结构和索引的使用情况。更好的查询性能通常对应着更好的 type 类型。

mysql的回表机制

在 MySQL 中,回表("ref" or "Bookmark Lookup" in English)是指在使用索引进行查询时,MySQL 首先通过索引找到满足条件的行的位置,然后再回到主表(或称为数据表)中查找完整的行数据的过程。

这个过程通常发生在某些查询中,特别是涉及到覆盖索引无法满足查询需求时。

当一个查询不能完全通过索引满足时,MySQL 就需要回到主表中查找更多的信息。这种情况通常出现在以下几种情况下:

  • 非覆盖索引查询: 如果查询需要返回主表中未包含在索引中的其他列的数据时,MySQL 就需要回到主表中查找这些额外的列数据。
  • 使用索引范围条件: 当查询中使用了范围条件(例如 BETWEEN>< 等),而索引只能定位到范围起始位置时,MySQL 需要回到主表中检查满足范围条件的完整行。
  • 使用了聚簇索引但需要查找的列不在索引中: 在使用了聚簇索引的表中,如果需要查询的列不在聚簇索引中,MySQL 需要回到主表中查找这些列的数据。

当 MySQL 需要执行回表操作时,会发生额外的磁盘访问,因为需要读取主表中的数据。这可能会导致性能下降,特别是在大型数据表中或者在高并发环境中。

为了尽量减少回表操作的发生,可以考虑以下几点:

  • 创建覆盖索引:确保查询所需的所有列都包含在索引中,从而避免回表操作。
  • 优化查询语句:尽量避免使用范围条件,或者确保所有的过滤条件都可以被索引完全匹配。
  • 考虑表设计:在设计数据库表结构时,可以考虑将常用的查询字段都包含在索引中,以减少回表操作的发生。



转载来源:https://juejin.cn/post/7360984753463803930


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4天前
|
SQL Java 关系型数据库
MYSQL--JDBC优化
MYSQL--JDBC优化
|
7天前
|
DataWorks 关系型数据库 MySQL
DataWorks产品使用合集之用脚本的方式同步数据到MySQL,怎么指定列作为目标表为唯一行
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
7天前
|
分布式计算 DataWorks 关系型数据库
实时数仓 Hologres产品使用合集之如何将MySQL数据初始化到分区表中
实时数仓Hologres的基本概念和特点:1.一站式实时数仓引擎:Hologres集成了数据仓库、在线分析处理(OLAP)和在线服务(Serving)能力于一体,适合实时数据分析和决策支持场景。2.兼容PostgreSQL协议:Hologres支持标准SQL(兼容PostgreSQL协议和语法),使得迁移和集成变得简单。3.海量数据处理能力:能够处理PB级数据的多维分析和即席查询,支持高并发低延迟查询。4.实时性:支持数据的实时写入、实时更新和实时分析,满足对数据新鲜度要求高的业务场景。5.与大数据生态集成:与MaxCompute、Flink、DataWorks等阿里云产品深度融合,提供离在线
|
23小时前
|
关系型数据库 MySQL Java
实时计算 Flink版产品使用问题之如何提高Flink从MySQL读取数据的速度并减少延迟
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
23小时前
|
JSON 关系型数据库 MySQL
实时计算 Flink版产品使用问题之在使用CDAS语法同步MySQL数据到Hologres时,如果开启了字段类型宽容模式,MySQL中的JSON类型会被转换为什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1天前
|
canal 关系型数据库 MySQL
实时计算 Flink版产品使用问题之在进行整库同步MySQL数据到StarRocks时,遇到全量数据可以同步,但增量数据无法同步,是什么导致的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1天前
|
关系型数据库 MySQL 数据库
mysql索引优化
【6月更文挑战第16天】mysql索引优化
5 2
|
1天前
|
关系型数据库 MySQL 数据处理
实时计算 Flink版产品使用问题之第一次启动全库同步了, 然后增删改的mysql数据没有及时同步,是什么导致的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1天前
|
关系型数据库 MySQL Serverless
实时计算 Flink版产品使用问题之使用cdas语法同步mysql数据到sr serverless是否支持动态加表
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2天前
|
SQL 关系型数据库 MySQL
MySQL数据库子查询练习——单个数据的子查询
MySQL数据库子查询练习——单个数据的子查询
8 1

相关产品

  • 云数据库 RDS MySQL 版
  • 推荐镜像

    更多