提升SQL语句性能

news/2024/4/17 16:46:25

1 内连接说明

当涉及到多个表的连接查询时,通常使用join关键字。

最常用的连接方式是左连接和内连接。

  • left join:找到两个表的交集,并包含左表中剩余的数据。

  • inner join:找到两个表的交集数据。

以下是使用inner join的示例:

select o.id,o.code,u.name 
from order o 
inner join user u on o.user_id = u.id
where u.status=1;

如果两个表使用inner join关联,MySQL会自动选择两个表中的小表驱动大表,因此在性能上不会出现太多问题。

以下是使用left join的示例:

select o.id,o.code,u.name 
from order o 
left join user u on o.user_id = u.id
where u.status=1;

如果两个表使用left join关联,MySQL默认使用左连接关键字驱动右侧的表。如果左表中存在大量数据,则可能会出现性能问题。

需要注意的是,在使用left join查询时,应该将小表放在左侧,将大表放在右侧。如果可以使用inner join,应尽量避免使用left join

2 限制索引的数量

众所周知,索引可以大幅提高SQL查询的性能,但索引的数量并不是越多越好。

因为当向表中添加新数据时,同时需要为其创建索引,而索引需要额外的存储空间和一定的性能消耗。

单个表中的索引数量应尽量控制在5个以内,单个索引中的字段数量也不应超过5个。

MySQL使用的B+树结构来保存索引,B+树索引在插入、更新和删除操作时需要进行更新。如果索引过多,将消耗大量的额外性能。

那么,如果表中的索引过多,超过了5个怎么办呢?

这个问题需要辩证地看待。如果你的系统并发性较低,表中的数据量也不是很大,实际上可以使用超过5个的索引,只要不过度即可。

但对于一些高并发的系统,务必遵守单个表上不超过5个索引的限制。

那么,高并发系统如何优化索引的数量呢?

如果可以建立联合索引,就不要建立单个索引,可以删除一些无用的单个索引。

将一些查询功能迁移到其他类型的数据库中,比如Elastic Seach、HBase等,只需在业务表中建立少量的关键索引即可。

3 选择适当的字段类型

char表示固定长度的字符串类型,该类型的字段存储空间是固定的,会浪费存储空间。

alter table order 
add column code char(20) NOT NULL;

varchar表示可变长度的字符串类型,该类型的字段存储空间会根据实际数据的长度进行调整,不会浪费存储空间。

alter table order 
add column code varchar(20) NOT NULL;

如果是固定长度的字段,比如用户的手机号码,一般是11位,可以定义为长度为11字节的char类型。

但如果是企业名称字段,如果定义为char类型,会存在问题。

如果长度定义得过长,例如定义为200字节,而实际企业名称只有50字节,将浪费150字节的存储空间。

如果长度定义得过短,例如定义为50字节,而实际企业名称有100字节,将无法存储,并抛出异常。

因此,建议将企业名称改为varchar类型。可变长度字段的存储空间较小,可以节省存储空间,对于查询来说,在相对较小的字段中搜索效率显然更高。

选择字段类型时,应遵循以下原则:

如果可以使用数字类型,就不要使用字符串,因为数字类型的存储空间更小,查询效率更高。

尽量使用小型类型,例如使用bit类型存储布尔值,tinyint类型存储枚举值等。

对于固定长度的字段,可以使用char类型。

对于可变长度的字段,可以使用varchar类型。

对于金额字段,使用decimal类型,避免精度丢失的问题。

4 提高group by的效率

在许多业务场景中,需要使用group by关键字。它的主要功能是进行去重和分组。

通常,与having一起使用,表示按照某些条件进行分组,然后再过滤数据。

错误示例

select user_id,user_name from order
group by user_id
having user_id <= 200;

这种写法性能较差。它首先根据用户ID对所有订单进行分组,然后筛选出用户ID大于或等于200的用户。

分组是一个相对耗时的操作,为什么不在分组之前缩小数据范围呢?

正确示例

select user_id,user_name from order
where user_id <= 200
group by user_id

使用where条件在分组之前过滤掉冗余数据,这样在分组时效率会更高。

实际上,这是一个思路,不仅仅适用于group by的优化。在SQL语句执行一些耗时操作之前,应尽量缩小数据范围,这样可以提高整体SQL的性能。

5 索引优化

在SQL优化中,索引优化是非常重要的内容。

在许多情况下,使用索引和不使用索引时,SQL语句的执行效率会有很大差异。因此,索引优化是SQL优化的首选。

索引优化的第一步是检查SQL语句是否已经使用了索引。

那么,如何检查SQL是否使用了索引呢?

可以使用explain命令查看MySQL的执行计划。

explain select * from `order` where code='002';

图片

以下是索引失效的一些常见原因:

  • 不满足最左前缀原则。

  • 范围索引列没有放在最后。

  • 使用了select *

  • 对索引列进行了计算。

  • 对索引列使用了函数。

  • 字符串类型没有加引号。

  • 使用了like %

  • 错误地使用了is null

  • 错误地使用了or

如果不是由于上述原因,就需要进一步调查其他原因。


https://www.xjx100.cn/news/3118887.html

相关文章

《异常检测——从经典算法到深度学习》24 用于单变量时间序列异常检测的端到端基准套件

《异常检测——从经典算法到深度学习》 0 概论1 基于隔离森林的异常检测算法 2 基于LOF的异常检测算法3 基于One-Class SVM的异常检测算法4 基于高斯概率密度异常检测算法5 Opprentice——异常检测经典算法最终篇6 基于重构概率的 VAE 异常检测7 基于条件VAE异常检测8 Donut: …

37.从0到上线三天搭建个人网站(第一天)

点赞收藏加关注&#xff0c;你也能住大别墅&#xff01; 挑战三天搭建个人网站 从0到上线 一、项目的主要功能 1.作为自己在网上的一个工作室。 2.发帖 3.展示个人项目连接 4.介绍自己&#xff08;没准儿还能接点活儿&#xff09; 二、UI风格参考 三、技术选型 1.前端&a…

typescript使用笔记

typescript已经成为前端日常开发中常用的工具之一&#xff0c;本文结合自己的使用做一下笔记&#xff0c;方便以后查阅。 Partial Partial可以将某个类型中定义的属性变成可选的. 下面的定义了Book类型&#xff0c;创建实力时如果不设置id属性就会提示错误。 type Book {id…

Linux搭建FTP并安装xrdp,实现Windows系统下利用FileZilla传输文件和远程桌面连接

一、实现FTP文件传输 1、搭建FTP服务器&#xff1a; sudo apt-get install vsftpd sudo service vsftpd status 2、查看vsftpd是否正在运行&#xff0c;并监听21端口 netstat -npl | grep :21 or sudo service vsftpd status # 查看 vsftpd 服务状态 3、配置vsftpd.conf sud…

android开发:用IDEA建立你的第一个APP

主要是记录一下各种小坑。 IDEA目前是第一流行的java开发工具&#xff0c;同时也支持android开发&#xff0c;可以替代安卓官方的andriod studio&#xff0c;不过仍然要依赖android sdk。 本例指导你完成第一个app&#xff0c;需要一台Windows PC和一部android手机。 目录 一…

【洛谷算法题】P5716-月份天数【入门2分支结构】

&#x1f468;‍&#x1f4bb;博客主页&#xff1a;花无缺 欢迎 点赞&#x1f44d; 收藏⭐ 留言&#x1f4dd; 加关注✅! 本文由 花无缺 原创 收录于专栏 【洛谷算法题】 文章目录 【洛谷算法题】P5716-月份天数【入门2分支结构】&#x1f30f;题目描述&#x1f30f;输入格式&a…

智能手表上的音频(四):语音通话

上篇讲了智能手表上音频文件播放。本篇开始讲语音通话。同音频播放一样有两种case&#xff1a;内置codec和BT。先看这两种case下audio data path&#xff0c;分别如下图&#xff1a; 内置codec下的语音通话audio data path 蓝牙下的语音通话audio data path 从上面两张图可以看…

c++——string字符串____迭代器.范围for.修改遍历容量操作

在成为大人的路上喘口气. 目录 &#x1f393;标准库类型string &#x1f393;定义和初始化string对象 &#x1f4bb;string类对象的常见构造 &#x1f4bb;string类对象的不常见构造 &#x1f4bb;读写string对象 &#x1f393; string类对象的修改操作 &#x1f4…