索引的威力--记一次MySQL存储过程优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
简介: 在MySQL存储过程中,一个`INSERT INTO SELECT`语句起初执行超过130秒,优化后,执行时间降低到1秒内,实现了100倍的性能提升。问题在于`NOT IN`子查询导致的慢查询,最终通过创建单列索引获得了最佳效果。文章还介绍了索引创建的基本语法,并讨论了单列索引与组合索引的优缺点。作者强调,随着数据量增加,索引对于查询性能的重要性,计划未来采用读写分离来进一步优化处理大量插入和查询的场景。

一、背景:

最近接手一个老项目,在MySQL存储过程中采用【insert.... select ....】的语句,执行时发现超过130s(之后停止存储过程,没有继续执行),实际是从30多万条数据中查询后,真正要插入数据只有1800多条。我在创建了多个单列索引后,同样的语句在1s内(<0.5s)就执行完成了,速度提升了100倍,体会到了数据库索引带来的巨大威力。


这个存储过程去掉具体的业务后,出现问题的语句是这么写的:

INSERT INTO da_tb_market_index_commodity_detail(
`third_level_code`, `data_type`,
`data_date`, 
`commodity_images`,
`commodity_name`
)SELECT 
`third_level_code`, 
`data_type`,
`data_date`, 
`commodity_images`,
`commodity_name`
from da_tb_market_index_commodity_detail_temp 
where third_level_code=category_code and data_date=data_time and data_type=type_str and price_section=section
AND (commodity_ID, data_date,data_type,third_level_code,price_section) NOT IN 
(SELECT commodity_ID, data_date,data_type,third_level_code,price_section FROM da_tb_market_index_commodity_detail);

二、解决问题过程:

因为这个存储过程执行时间超过30s,不太正常,于是我在navicat中,输入 show processlist; 命令查看正在执行的任务,发现这个存储过程还在执行insert语句。因为时间太长,担心表锁死,所以我通过查看Info列中找到任务对应的Id,执行kill命令 ,停止正在执行的存储过程。

分析这段代码,首先是not in的查询条件执行会比较慢,可以使用left join来优化,这会提供一些性能,当时因为有时间要求,没有采取这个优化策略。另一个是在数据库表da_tb_market_index_commodity_detail中增加索引,来提高查询的性能,我首先建了组合索引 (commodity_ID, data_date,data_type,third_level_code,price_section)Index,效果有比较大的提升,之后我创建了多个单列索引,发现速度更快,在1s内完成了存储过程,大大超出我的想象。索引创建如下图:

image.png



三、涉及知识点:

1、索引的创建:建表时和建表后都可以创建索引, 注:索引方法默认使用B+TREE。以下是创建表时的存储过程:

CREATE TABLE 表名(
字段名 数据类型 [完整性约束条件], ……,
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]);

说明:
UNIQUE:可选。表示索引为唯一性索引。
FULLTEXT:可选。表示索引为全文索引。
SPATIAL:可选。表示索引为空间索引。
INDEX和KEY:用于指定字段为索引,两者选择其中之一就可以了,作用是    一样的。
索引名:可选。给创建的索引取一个新名称。
字段名1:指定索引对应的字段的名称,该字段必须是前面定义好的字段。
长度:可选。指索引的长度,必须是字符串类型才可以使用。
ASC:可选。表示升序排列。
DESC:可选。表示降序排列。

2、单列索引和组合索引的区别联系

对于只涉及单列的查询,单列索引可以快速定位到符合条件的记录,提高查询效率。相对于组合索引,单列索引的维护成本较低,因为每次数据更新时只需要更新一个索引。

组合索引可以同时考虑多个列的组合,对于涉及这些列的查询,可以更有效地定位到符合条件的记录,提高查询效率。组合索引的大小会随着索引列的增加而增加,占用更多的存储空间,特别是当索引包含大量的列时,可能会导致索引过大,影响性能。更新组合索引列的值时,需要同时更新索引的多个列,可能会增加更新的代价和时间。

本次性能优化,既有单列查询,也有组合列查询,综合比较后,采用多个单列索引性能更好。


四、总结

索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址,可以大大加快查询的速度,使用索引后可以不用扫描全表来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

数据库表中数据少的时候,加不加索引,对于查询性能没有影响。一旦数据库数据较大,比如本例中表的数据超过30多万,对于比较复杂的一些查询性能是有明显的优势的。

我现在做的电商系统,每天数据增长很快,后续如果插入数据影响性能(比如插入数据时,用户在界面上查询数据响应比较慢),我规划把目前的单库改成读写分离模式,把插入数据和查询数据分开,增加更好的用户体验。

技术不断发展,希望和大家一起进步,加油!




相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
2
分享
相关文章
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
java调用mysql存储过程
在 Java 中调用 MySQL 存储过程主要借助 JDBC(Java Database Connectivity)。其核心原理是通过 JDBC 与 MySQL 建立连接,调用存储过程并处理结果。具体步骤包括:加载 JDBC 驱动、建立数据库连接、创建 CallableStatement 对象、设置存储过程参数并执行调用。此过程实现了 Java 程序与 MySQL 数据库的高效交互。
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
MySQL底层概述—7.优化原则及慢查询
本文主要介绍了:Explain概述、Explain详解、索引优化数据准备、索引优化原则详解、慢查询设置与测试、慢查询SQL优化思路
MySQL底层概述—7.优化原则及慢查询
MySQL原理简介—9.MySQL索引原理
本文详细介绍了MySQL索引的设计与使用原则,涵盖磁盘数据页的存储结构、页分裂机制、主键索引设计及查询过程、聚簇索引和二级索引的原理、B+树索引的维护、联合索引的使用规则、SQL排序和分组时如何利用索引、回表查询对性能的影响以及索引覆盖的概念。此外还讨论了索引设计的案例,包括如何处理where筛选和order by排序之间的冲突、低基数字段的处理方式、范围查询字段的位置安排,以及通过辅助索引来优化特定查询场景。总结了设计索引的原则,如尽量包含where、order by、group by中的字段,选择离散度高的字段作为索引,限制索引数量,并针对频繁查询的低基数字段进行特殊处理等。
MySQL原理简介—9.MySQL索引原理
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
24 9
从MySQL优化到脑力健康:技术人与效率的双重提升
聊到效率这个事,大家应该都挺有感触的吧。 不管是技术优化还是个人状态调整,怎么能更快、更省力地完成事情,都是我们每天要琢磨的事。
58 23

数据库

+关注

相关产品

  • 云数据库 RDS MySQL 版
  • AI助理

    你好,我是AI助理

    可以解答问题、推荐解决方案等