SQL慢查询优化方式

目录

一、SQL语句优化

1. 避免使用 SELECT * ，而是具体字段

2.避免使用 % 开头的 LIKE 的查询

3.避免使用子查询，使用JOIN

4.使用EXISTS代替IN

5.使用LIMIT 1优化查询

6.使用批量插入、优化INSERT操作

7.其他方式

二、SQL索引优化

1.在查询条件或者连接条件的列上建立索引

2.遵循最左前缀原则

3.避免在索引列上进行计算

4.使用覆盖索引

5.避免使用更新频繁的列作为索引

6.避免过多的列使用复合索引

7.定期维护索引

三、EXPLAIN分析查询

SQL优化一般从SQL语句开始优化，再分析索引，即使有了良好的索引，糟糕的查询语句也可能导致性能问题

优化查询语句可以帮助数据库更有效地利用现有的资源，减少不必要的开销。关于如何从查询语句开始优化的步骤：

  1. 识别慢查询
  2. SQL优化
  3. 索引使用
  4. 使用 EXPLAIN 分析查询

一、SQL语句优化

不合理的SQL语句会导致：

  • 索引失效，无法使用索引
  • 全表扫描，因为数据库必须检查每一行数据以确定是否匹配
  • 对于大型表，这会导致性能问题和资源消耗

下面的例子🌰以User表为例

CREATE TABLE User (user_id INT PRIMARY KEY,name VARCHAR(255),phone VARCHAR(20),email VARCHAR(255),role VARCHAR(50),address VARCHAR(255),birthday DATETIME
);

1. 避免使用 SELECT * ，而是具体字段

❌：索引失效，全表扫描

SELECT * FROM user

✅：节省资源、减少网络开销。可能用到覆盖索引，减少回表，提高查询效率

SELECT id,username,sex FROM user

📌原因：

  • 只取实际需要的字段，节省资源、减少网络开销。

  • 可能用到覆盖索引，减少回表，提高查询效率

覆盖索引和回表下文会详解，此处不做过多赘述

⚠️注意：为节省时间，下面的样例字段都用 SELECT * 代替了

2.避免使用 % 开头的 LIKE 的查询

❌：由于 % 表示任意数量的字符，数据库无法利用索引来加速查询，因为它不知道从索引树的哪个位置开始搜索，从而导致全表扫描，性能较差，特别是在大表上

SELECT * FROM User WHERE name LIKE '%echola';

✅：可以利用 name 列上的索引来快速定位以 'echola' 开始的所有用户名，也就是从索引树的特定节点开始搜索，而不是从根节点遍历整个树

SELECT * FROM User WHERE name LIKE 'echola%';

3.避免使用子查询，使用JOIN

SELECT * FROM User 
WHERE id IN (SELECT user_id FROM Orders WHERE status = 'completed');

✅：

SELECT u.* 
FROM User u JOIN Orders o ON u.id = o.user_id 
WHERE o.status = 'completed';

inner join 、left join、right join，优先使用inner join

   三种连接如果结果相同，优先使用inner join（返回行数少），如果使用left join左边表尽量小（小表在前，大表在后）

  • inner join 内连接，只保留两张表中完全匹配的结果集；

  • left join会返回左表所有的行，即使在右表中没有匹配的记录；

  • right join会返回右表所有的行，即使在左表中没有匹配的记录

⚠️：表连接不宜太多，一般5个以内

4.使用EXISTS代替IN

虽然需避免子查询，但是某些情况下还是需要使用子查询，使用EXISTS代替IN可以提高查询效率

❌：

SELECT * FROM User 
WHERE id IN (SELECT user_id FROM Orders WHERE status = 'completed');

✅：EXISTS 找到第一个匹配项后就会停止搜索，而 IN 会遍历整个子查询结果集

SELECT * FROM User 
WHERE id EXISTS (SELECT user_id FROM Orders WHERE status = 'completed');

5.使用LIMIT 1优化查询

在只需要一条结果的查询中使用 LIMIT 1 可以提高性能，

尤其是在使用Mybatis Plus中的selectOne()，若有多条数据符合，则会抛出异常，因此需要添加 LIMIT 1

SELECT * FROM users WHERE name = 'echola' LIMIT 1;

6.使用批量插入、优化INSERT操作

使用批量插入可以减少事务提交次数：在批量插入过程中，尽量减少事务提交的次数。可以在一个事务中插入多条记录，然后一次性提交。在开发中一般使用MybatisPlus的saveBatch()

START TRANSACTION;
INSERT INTO User (user_id, name, phone, email, role, address, birthday)
VALUES(1, 'Alice', '1234567890', 'alice@example.com', 'user', 'New York', '2000-01-01 00:00:00'),(2, 'Bob', '0987654321', 'bob@example.com', 'admin', 'Los Angeles', '1999-01-01 00:00:00'),(3, 'Charlie', '5555555555', 'charlie@example.com', 'user', 'Chicago', '2001-01-01 00:00:00');
COMMIT;

7.其他方式

避免在 WHERE 子句中使用 OR 来连接条件

❌：

SELECT * FROM User WHERE age = 25 OR name = 'echola';

✅：

SELECT * FROM User WHERE age = 25
UNION ALL
SELECT * FROM User WHERE name = 'echola';

避免使用 NOT IN、!=、<> 等负条件，因为这些条件不能有效使用索引 

避免在索引列上使用IS NULL或IS NOT NULL

避免使用HAVING代替WHERE，在可能的情况下，使用 WHERE 代替 HAVING 进行过滤，因为 HAVING 是在聚合之后进行过滤，性能较差

二、SQL索引优化

合理地使用索引是SQL最重要的

如果数据量小的表，可以不建立索引，但数据量大的表肯定是需要建立索引的

如果一张表上kw的数据量，果索引使用不当，也可能会导致索引失效，反而成为负担

以下关键词条解释：

复合索引：多个列组合成的索引

覆盖索引：索引中包含了查询所需的全部字段
最左前缀原则：在复合索引中，查询条件应尽可能地按照索引的顺序进行匹配

回表：当数据库使用索引来加速查询时，如果索引是非聚集索引（也称为辅助索引或者二级索引），那么索引项中存储的并不是完整的行数据，而是指向实际数据行的指针。当数据库通过索引查找到匹配的索引项后，还需要根据这个指针回到实际的数据页去获取完整的行数据

1.在查询条件或者连接条件的列上建立索引

⚠️：只要发现接口查询缓慢，优先检查 WHERE 后面的条件，有没有创建索引，如果已经建立索引，需要创建复合索引，调整现有索引

⚠️：索引不宜太多，一般5个以内

CREATE INDEX idx_userid ON User(user_id);
SELECT * FROM User WHERE user_id = 123 AND name = 'echola';

2.遵循最左前缀原则

对于复合索引(name, age, phone)，可以用于(name)，(name, age)，(name, age, phone)顺序匹配查询

CREATE INDEX idx_name_age_phone ON User(name, age, phone);

❌：此处是（age,phone）,没有按照索引的顺序进行匹配 ，导致索引无法完全利用，可能需要额外的回表操作

SELECT * FROM User WHERE age = 25 AND phone = '1234567890';

✅：按顺序匹配（name, age）

SELECT * FROM User WHERE phone = '1234567890' AND age = 25 ;

3.避免在索引列上进行计算

❌：使用 YEAR(date) 会导致索引失效

CREATE INDEX idx_birthday ON User(birthday);SELECT * FROM User WHERE YEAR(birthday) = 2024;

✅：

SELECT * FROM User WHERE birthday BETWEEN '2024-01-01' AND '2024-12-31';

4.使用覆盖索引

如果查询的所有列都在索引中，那么数据库引擎可以不访问表中的数据而直接从索引中获取所需信息，这样可以减少回表操作，从而提高查询效率

❌：不使用覆盖索引

CREATE INDEX idx_userid ON User(user_id);SELECT user_id, phone, role FROM User WHERE user_id = 123;

索引 idx_userid 只包含 user_id 列，但 user_id, phone, 和 role 列不在索引里，导致需要回表访问主键索引或其他索引

✅：索引中覆盖了所有查询的列，数据库可以直接从索引中获取数据

CREATE INDEX idx_userid_phone_role ON User(user_id, phone, role);SELECT user_id, phone, role FROM User WHERE user_id = 123;

5.避免使用更新频繁的列作为索引

更新频繁的列作为索引会导致较高的维护成本，降低查询性能

❌：phone 列更新频繁，每次更新都会导致索引的重建，更新成本高，影响性能

CREATE INDEX idx_phone ON User(phone);

6.避免过多的列使用复合索引

复合索引的列数不要太多，列数过多会增加索引的维护开销，并且可能导致索引文件过大。对此可以拆分为较少复合索引和单个索引

CREATE INDEX idx_userid_phone_role_address ON User(user_id, phone, role, address);

7.定期维护索引

使用 ANALYZE TABLE 更新统计信息，并使用 OPTIMIZE TABLE 来整理碎片化的索引 ，特别是在大量插入、更新和删除操作后，表可能会出现碎片化，导致性能下降

ANALYZE TABLE User;
OPTIMIZE TABLE User;

⚠️：这两个命令可能会导致锁定表，因此在高并发环境中使用时要谨慎，最好在低峰时段执行

对于非常大的表，执行这些命令可能需要较长时间，建议提前进行测试

三、EXPLAIN分析查询

使用 EXPLAIN 分析 SQL 执行计划可以帮助我们理解数据库如何执行查询，并找出潜在的性能瓶颈

SELECT user_id, phone, role FROM User WHERE user_id = 123 AND birthday > '2000-01-01

先看下没有建立索引的情况

EXPLAIN SELECT user_id, phone, role FROM User WHERE user_id = 123 AND birthday > '2000-01-01 00:00:00';

 执行结果：

具体解释：

EXPLAIN 输出表格包含多个列，每列提供不同的查询计划信息。常见列包括：

1、id：查询的标识符，表示查询的执行顺序
2、select_type：查询类型，如 SIMPLE（简单查询），PRIMARY（主查询），UNION（联合查询的一部分），SUBQUERY（子查询）
3、table：查询涉及的表
4、type：连接类型，表示MySQL如何查找行。常见类型按效率从高到低排列为：

  • system：表只有一行（常见于系统表）
  • const：表最多有一个匹配行（索引为主键或唯一索引）
  • eq_ref：对于每个来自前一个表的行，表中最多有一个匹配行。
  • ref：对于每个来自前一个表的行，表中可能有多个匹配行。
  • range：使用索引查找给定范围的行。
  • index：全表扫描索引
  • ALL：全表扫描。

5、possible_keys：查询中可能使用的索引（user_id主键索引PRIMARY KEY）
6、key：实际使用的索引。
7、key_len：使用的索引键长度
8、ref：使用的列或常量，与索引比较，此处表示常量引用，即 user_id = 123
9、rows：MySQL 估计的要读取的行数
10、filtered：经过表条件过滤后的行百分比
11、Extra：额外的信息，如 Using index（覆盖索引），Using where（使用 WHERE 子句过滤），Using filesort（文件排序），Using temporary（使用临时表）

建立复合索引

CREATE INDEX idx_userid_birthday ON User(user_id, birthday);

执行结果 ：

主键索引使用：

        默认的主键索引 user_id 被利用，使得查询可以快速定位到 user_id = 123 的记录。
复合索引优化：

        创建复合索引 idx_userid_birthday 可以进一步优化查询性能，减少不必要的扫描操作。

通过这些步骤，我们可以确保查询性能得到显著提升，并且索引能够充分利用

欢迎对SQL优化的方式，进行补充……

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/411489.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

商圣集团：数字创新，引领智慧生活新篇章

商圣集团：数字创新，引领智慧生活新篇章

在全球化经济不断演进的大潮中&#xff0c;数字经济已成为推动社会进步的关键引擎&#xff0c;重塑着我们的生产与生活模式。商圣集团&#xff0c;以服务社会、创新驱动为核心价值观&#xff0c;致力于利用数字化技术&#xff0c;为个人和企业带来高效、便捷的服务体验&#xf…
阅读更多...
自省式RAG与LangGraph：探索高效实践之路

自省式RAG与LangGraph：探索高效实践之路

研究背景 由于大多数大型语言模型&#xff08;LLMs&#xff09;通常只针对大量公共数据进行周期性训练&#xff0c;它们往往缺少最新信息或不能接触到无法用于训练的私有数据。检索增强生成&#xff08;RAG&#xff09;模式恰好解决了这个问题&#xff0c;它通过将大型语言模型…
阅读更多...
前端速通面经八股系列(五）—— Vue(上)

前端速通面经八股系列(五）—— Vue(上)

Vue系列目录 一、Vue 基础1. Vue的基本原理2. 双向数据绑定的原理3. 使用 Object.defineProperty() 来进行数据劫持有什么缺点&#xff1f;4. MVVM、MVC、MVP的区别5. Computed 和 Watch 的区别6. Computed 和 Methods 的区别7. slot是什么&#xff1f;有什么作用&#xff1f;原…
阅读更多...
计算机视觉编程 1（图片处理）

计算机视觉编程 1（图片处理）

目录 灰色度 缩略图 拷贝粘贴区域 调整图像尺寸 旋转图像45 画图线、描点 灰色度 灰度是指图像中每个像素的亮度值&#xff0c;用来描述图像中各个像素的明暗程度。在计算机视觉中&#xff0c;灰度可以通过以下方式来计算&#xff1a; 1. 平均值法&#xff1a;将图像中每…
阅读更多...
E:Failed to fetch的解决方案——Linux换源方法

E:Failed to fetch的解决方案——Linux换源方法

错误描述 在sudo apt-get时报错 E: Failed to fetch https://mirrors.bupt.edu.cn/ubuntu/pool/universe/libc/libcanberra/libcanberra-gtk0_0.30-7ubuntu1_amd64.deb 403 Forbidden 这种错通常是该源在当前网络下无法连接导致&#xff08;如笔者从教育网换回家里的网&#x…
阅读更多...
Kubernetes存储Volume

Kubernetes存储Volume

数据是一个企业的发展核心,他涉及到数据存储和数据交换的内容。在生产环境中尤为重要的一部分&#xff0c;在 Kubernetes 中另一个重要的概念就是数据持久化 Volume。 一、Volume的概念 对于大多数的项目而言&#xff0c;数据文件的存储是非常常见的需求&#xff0c;比如存储用…
阅读更多...
大模型低显存推理优化-Offload技术

大模型低显存推理优化-Offload技术

近两年大模型火出天际&#xff1b;同时&#xff0c;也诞生了大量针对大模型的优化技术。本系列将针对一些常见大模型优化技术进行讲解。 [大模型推理优化技术-KV Cache][大模型推理服务调度优化技术-Continuous batching]大模型显存优化技术-PagedAttention大模型低显存推理优…
阅读更多...
爬虫入门学习

爬虫入门学习

流程 获取网页内容 HTTP请求 Python Requests解析网页内容 HTML网页结构 Python Beautiful Soup储存或分析数据 HTTP (Hypertext Transfer Protocol) 客户端和服务器之间的请求-响应协议 Get方法&#xff1a;获得数据 POST方法&#xff1a;创建数据 HTTP请求 请求行 方法类型…
阅读更多...
零基础国产GD32单片机编程入门(二)GPIO输入中断含源码

零基础国产GD32单片机编程入门(二)GPIO输入中断含源码

文章目录 一.概要二.可嵌套的向量中断控制器 (NVIC)三.中断向量表四.中断优先级详解五.GD32外部中断控制器(EXTI)1.EXTI简介2.EXTI在中断向量表的位置3.EXTI外部中断产生的信号流向4.EXTI中断产生后的中断服务程序 六.GPIO输入中断的例程实验七.工程源代码下载八.小结 一.概要 …
阅读更多...
Django+vue自动化测试平台（29）--测试平台集成playwright录制pytest文件执行

Django+vue自动化测试平台（29）--测试平台集成playwright录制pytest文件执行

需求背景 一、 系统目标与功能概述 脚本管理: 系统需要能够组织和存储所有通过playwright官方插件录制的脚本。这包括脚本的上传、编辑、删除和版本控制功能。 脚本执行: 用户应该能够在后台界面上查看所有可用的脚本&#xff0c;并能够通过简单的点击操作来启动特定脚本的执…
阅读更多...
Visual Basic 6.0教程/Visual Basic从入门到实践/Visual Basic学习视频教程

Visual Basic 6.0教程/Visual Basic从入门到实践/Visual Basic学习视频教程

Visual Basic 6.0教程/Visual Basic从入门到实践/Visual Basic学习视频教程 李天生VB从入门到精通 第一章 VisualBasic6基本介绍 第二章 VisualBasic6的数据类型与运算符表达式 第三章 VisualBasic6的内部函数 第四章 VisualBasic6的基本语句 第五章 VisualBasic6的数组 第六章…
阅读更多...
RX 8000系显卡规格曝光，全系GDDR6纯过渡产品

RX 8000系显卡规格曝光，全系GDDR6纯过渡产品

原文转载修改自&#xff08;更多互联网新闻/搞机小知识&#xff09;&#xff1a; RX 8000系显卡规格首曝&#xff0c;GDDR6显存就很骨感 前天&#xff0c;我们刚刚聊过有过新一代RTX 50系消息&#xff0c;虽然是按部就班地升级&#xff0c;但好在也是在升级。50系换核心升级显…
阅读更多...
Sentinel熔断与限流

Sentinel熔断与限流

一、服务雪崩与解决方案 1.1、服务雪崩问题 一句话&#xff1a;微服务之间相互调用&#xff0c;因为调用链中的一个服务故障&#xff0c;引起整个链路都无法访问的情况。 微服务中&#xff0c;服务间调用关系错综复杂&#xff0c;一个微服务往往依赖于多个其它微服务。 如图…
阅读更多...
RabbitMQ 集群与高可用性

RabbitMQ 集群与高可用性

目录 单节点与集群部署 1.1. 单节点部署 1.2. 集群部署 镜像队列 1.定义与工作原理 2. 配置镜像队列 3.应用场景 4. 优缺点 5. Java 示例 分布式部署 1. 分布式部署的主要目标 2. 典型架构设计 3. RabbitMQ 分布式部署的关键技术 4. 部署策略和实践 5. 分布式部署…
阅读更多...
前端开发学习Docker记录01镜像操作

前端开发学习Docker记录01镜像操作

Docker相关命令 Demo安装nginx 先搜索然后拉取&#xff0c;然后查看images列表是不是拉取成功 docker search nginxdocker pull nginx特定某个版本&#xff0c;镜像名&#xff1a;版本号 docker images
阅读更多...
layui2.9 树组件默认无法修改节点图标，修改过程记录下

layui2.9 树组件默认无法修改节点图标，修改过程记录下

官方文档树组件 data 参数值&#xff0c;未提供icon属性配置 需要修改源码 layui.js, 搜索图片中标记部分 查找到之后&#xff0c;修改为 <i class“‘(i.icon || “layui-icon layui-icon-file”)’”> 如图&#xff1a; 修改完成后&#xff0c;即可在data中添加icon…
阅读更多...
redis学习笔记 ——redis中的四大特殊数据结构

redis学习笔记 ——redis中的四大特殊数据结构

一.前言 在之前的学习中&#xff0c;我们已经介绍了Redis中常见的五种基本的数据结构&#xff0c;而今天我们就要开始介绍Redis的四种特殊的数据结构&#xff0c;它们分别是bitmap(位图)&#xff0c; HyperLogLog(基数统计),Geospatial(地理信息),Stream。 二.位图(Bitmap) …
阅读更多...
Windows安装PostgreSQL数据库，保姆级教程

Windows安装PostgreSQL数据库，保姆级教程

PostgreSQL 是客户端/服务器关系数据库管理系统 (RDMS)。PostgreSQL是一个功能非常强大的、源代码开放的客户/服务器关系型数据库管理系统&#xff08;RDBMS&#xff09;。PostgreSQL 也有自己的查询语言&#xff0c;称为 pgsql。 此外&#xff0c;PostgreSQL 还支持过程语言&a…
阅读更多...
CS224W—07 Machine Learning with Heterogeneous Graphs

CS224W—07 Machine Learning with Heterogeneous Graphs

CS224W—07 Machine Learning with Heterogeneous Graphs 本节中&#xff0c;我们将学习如何在异构图中进行图神经网络学习。 Heterogeneous Graphs 图中的节点类型/边类型不同&#xff0c;就会形成一个异构图&#xff08;Heterogeneous Graph&#xff09;&#xff0c;例如下…
阅读更多...
基于SpringBoot的在线答疑管理系统

基于SpringBoot的在线答疑管理系统

基于SpringBootVue的在线答疑管理系统【附源码文档】、前后端分离 开发语言&#xff1a;Java数据库&#xff1a;MySQL技术&#xff1a;SpringBoot、Vue、Mybaits Plus、ELementUI工具&#xff1a;IDEA/Ecilpse、Navicat、Maven 系统展示 摘要 基于SpringBoot的在线答疑管理系…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发