我的编程空间,编程开发者的网络收藏夹
学习永远不晚

五个提升SQL语句性能的小窍门,进一步提升查询性能

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

五个提升SQL语句性能的小窍门,进一步提升查询性能

本文介绍五个实用的优化SQL的技巧,帮助读者更好地利用索引、避免性能瓶颈,并提高数据库的整体性能。

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。

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

此外,您是否曾经遇到过这样的情况:明明是相同的SQL,只是输入参数不同。有时候索引a生效,有时候索引b生效?

有时候MySQL会选择错误的索引。

如果有必要,可以使用force index来强制查询SQL使用特定的索引。

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

五个提升SQL语句性能的小窍门,进一步提升查询性能

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

五个提升SQL语句性能的小窍门,进一步提升查询性能

在进行数据库操作时,优化SQL语句是提升性能和效率的关键步骤之一。无论是处理大规模数据还是简单的查询,优化SQL语句都可以明显改善系统的响应时间和资源利用率。
SQL语句性能2024-11-30

12个优化SQL语句的小技巧,提升查询性能

本文将介绍一些有效的技术,以提升SQL查询性能。下文是几种优化SQL查询以提高性能的方法。
SQL数据库2024-11-30

六个提升SQL查询性能的核心技巧

使用SELECT *​ 是一个常见的错误,这可能会严重影响查询性能。当使用SELECT * 时,数据库会返回所有列的数据,包括那些我们不需要使用的列。相反,我们应该明确列出真正需要的列,这样可以减少数据传输量和处理的工作量,从而提高查询的效
SQLSELECT查询2024-11-30

编程热搜

  • Python 学习之路 - Python
    一、安装Python34Windows在Python官网(https://www.python.org/downloads/)下载安装包并安装。Python的默认安装路径是:C:\Python34配置环境变量:【右键计算机】--》【属性】-
    Python 学习之路 - Python
  • chatgpt的中文全称是什么
    chatgpt的中文全称是生成型预训练变换模型。ChatGPT是什么ChatGPT是美国人工智能研究实验室OpenAI开发的一种全新聊天机器人模型,它能够通过学习和理解人类的语言来进行对话,还能根据聊天的上下文进行互动,并协助人类完成一系列
    chatgpt的中文全称是什么
  • C/C++中extern函数使用详解
  • C/C++可变参数的使用
    可变参数的使用方法远远不止以下几种,不过在C,C++中使用可变参数时要小心,在使用printf()等函数时传入的参数个数一定不能比前面的格式化字符串中的’%’符号个数少,否则会产生访问越界,运气不好的话还会导致程序崩溃
    C/C++可变参数的使用
  • css样式文件该放在哪里
  • php中数组下标必须是连续的吗
  • Python 3 教程
    Python 3 教程 Python 的 3.0 版本,常被称为 Python 3000,或简称 Py3k。相对于 Python 的早期版本,这是一个较大的升级。为了不带入过多的累赘,Python 3.0 在设计的时候没有考虑向下兼容。 Python
    Python 3 教程
  • Python pip包管理
    一、前言    在Python中, 安装第三方模块是通过 setuptools 这个工具完成的。 Python有两个封装了 setuptools的包管理工具: easy_install  和  pip , 目前官方推荐使用 pip。    
    Python pip包管理
  • ubuntu如何重新编译内核
  • 改善Java代码之慎用java动态编译

目录