`
壹佰案例
  • 浏览: 34701 次
  • 性别: Icon_minigender_2
  • 来自: 北京
社区版块
存档分类
最新评论

一次非常有意思的SQL优化经历:从30248.271s到0.001s

阅读更多

本文来源 | toutiao.com/i6668275333034148356

作者  | Java技术架构

 

背景介绍

我用的数据库是mysql5.6,下面简单的介绍下场景:

 

课程表:

数据100条

学生表:

数据70000条

学生成绩表SC:

数据70w条

查询目的:

查找语文考100分的考生

查询语句:

执行时间:30248.271s

为什么这么慢?先来查看下查询计划:

发现没有用到索引,type全是ALL,那么首先想到的就是建立一个索引,建立索引的字段当然是在where条件的字段。

先给sc表的c_id和score建个索引

再次执行上述查询语句,时间为: 1.054s

快了3w多倍,大大缩短了查询时间,看来索引能极大程度的提高查询效率,看来建索引很有必要,很多时候都忘记建索引了,数据量小的的时候压根没感觉,这优化感觉挺爽。

但是1s的时间还是太长了,还能进行优化吗,仔细看执行计划:

查看优化后的sql:

补充:这里有网友问怎么查看优化后的语句

方法如下:

在命令窗口执行

有type=all

按照我之前的想法,该sql的执行的顺序应该是先执行子查询

耗时:0.001s

得到如下结果:

然后再执行

耗时:0.001s

这样就是相当快了啊,Mysql竟然不是先执行里层的查询,而是将sql优化成了exists子句,并出现了EPENDENT SUBQUERY,

mysql是先执行外层查询,再执行里层的查询,这样就要循环70007*11=770077次。

 

那么改用连接查询呢?

这里为了重新分析连接查询的情况,先暂时删除索引sc_c_id_index,sc_score_index

执行时间是:0.057s

效率有所提高,看看执行计划:

这里有连表的情况出现,我猜想是不是要给sc表的s_id建立个索引

CREATE index sc_s_id_index on SC(s_id);

show index from SC

在执行连接查询

时间: 1.076s,竟然时间还变长了,什么原因?查看执行计划:

优化后的查询语句为:

貌似是先做的连接查询,再执行的where过滤

回到前面的执行计划:

这里是先做的where过滤,再做连表,执行计划还不是固定的,那么我们先看下标准的sql执行顺序:

苦逼的码农注:知道执行顺序是非常重要的,建议通过这张表熟悉一下,最后不要死记硬背,而是理解他们。

正常情况下是先join再where过滤,但是我们这里的情况,如果先join,将会有70w条数据发送join做操,因此先执行where过滤是明智方案,现在为了排除mysql的查询优化,我自己写一条优化后的sql

即先执行sc表的过滤,再进行表连接,执行时间为:0.054s

和之前没有建s_id索引的时间差不多

查看执行计划:

先提取sc再连表,这样效率就高多了,现在的问题是提取sc的时候出现了扫描表,那么现在可以明确需要建立相关索引

再执行查询:

执行时间为:0.001s,这个时间相当靠谱,快了50倍

执行计划:

我们会看到,先提取sc,再连表,都用到了索引。

那么再来执行下sql

执行时间0.001s

执行计划:

这里是mysql进行了查询语句优化,先执行了where过滤,再执行连接操作,且都用到了索引。

总结

1、mysql嵌套子查询效率确实比较低

2、可以将其优化成连接查询

3、建立合适的索引

4、学会分析sql执行计划,mysql会对sql进行优化,所以分析执行计划很重要。

 

分享到:
评论

相关推荐

    com.microsoft.sqlserver.jdbc.SQLServerException: 只进结果集不支持请求的操作 解决方案

    面对“只进结果集不支持请求的操作”的异常,通过调整查询类型、使用原生SQL或优化数据库连接配置,可以有效避免此类问题,确保应用程序的正常运行。在实际开发中,应根据项目需求和数据库特性灵活选择最合适的解决...

    Microsoft.SqlServer.Diagnostics.STrace.dll

    未能加载文件或程序集“Microsoft.SqlServer.Diagnostics.STrace, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”或它的某一个依赖项。系统找不到指定的文件。 (Microsoft.SqlServer.Dmf) ...

    SqlServer.Diagnostics.STrace.dll

    “Microsoft.SqlServer.Management.Dmf.PolicyStore”的...找对版本将Microsoft.SqlServer.Diagnostics.STrace.dll复制到C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE这个目录 即可。

    Microsoft.SqlServer.Diagnostics.STrace

    未能加载文件或程序集“Microsoft.SqlServer.Diagnostics.STrace, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”或它的某一个依赖项。系统找不到指定的文件。 (Microsoft.SqlServer.Dmf) ...

    解决Exception java.sql.SQLException ORA-00600 内部错误代码

    在oracle里面运行一下,解决Exception java.sql.SQLException ORA-00600 内部错误代码

    未能加载文件或程序集microsoft.sqlserver.management.sdk.sfc,Version=11.0

    标题 "未能加载文件或程序集microsoft.sqlserver.management.sdk.sfc,Version=11.0" 描述了一个在使用ADO.NET数据实体时遇到的问题,即系统无法找到特定版本的Microsoft SQL Server Management SDK组件。这个问题...

    SQLPrompt_6.3.0.344.zip

    在SQLPrompt_6.3.0.344.zip这个压缩包中,包含的是SQLPrompt的6.3.0.344版本,这是一个针对SQL Server的插件,旨在优化SQL编写体验。 此版本的SQLPrompt提供了以下几个主要功能: 1. **智能提示**:当用户在编写...

    SQLPrompt-10.14.0.4064.exe 支持SSMS 19.1

    SQLPrompt-10.14.0.4064.exe 支持SSMS 19.1

    Oracle SQL Developer-18.2.0.183.1748

    Oracle SQL Developer 企业管理 Oracle12c连接工具,sqldeveloper18.2.0.183.1748-no-jre Oracle JDeveloper

    数据库驱动常见错误"java.lang.ClassNotFoundException:解决了jsp连接Error establishing socket.

    "java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver" 解决方案 [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket. 解决了jsp连接 sql server 2000的问题

    SQLPrompt_7.5.0.787Keygen各版本通用.7z

    在SQLPrompt 7.5.0.787这个版本中,用户可以享受到更加智能和精准的SQL编写体验。Keygen(密钥生成器)通常用于激活软件的全功能版本,但请注意,这里提到的"SQLPrompt_7.5.0.787Keygen各版本通用"可能涉及到未经...

    com.microsoft.sqlserver.jdbc.SQLServerDriver

    这个驱动程序是Java Database Connectivity (JDBC)的一部分,允许Java开发者通过编写Java代码来访问和操作SQL Server数据库。 首先,我们需要理解JDBC。它是Java平台的一个标准接口,由Sun Microsystems(现为...

    sql-map-2.dtd和sql-map-config-2.dtd

    在IT行业中,数据库操作是核心任务之一,而Ibatis作为一款优秀的Java持久层框架,极大地简化了数据库操作。本文将深入探讨与“sql-map-2.dtd”和“sql-map-config-2.dtd”相关的知识点,以及它们在Ibatis中的作用。 ...

    sqlservr.exe和sqlos.dll win8-win10下安装sqlserver2005 亲测成功

    SQL Server 2005是微软推出的一款关系型数据库管理系统,它在Windows 8到Windows 10操作系统上运行,虽然这些系统版本较新,但SQL Server 2005仍可以通过特定步骤进行安装。`sqlservr.exe`和`sqlos.dll`是SQL Server...

    SqlServer2008查询性能优化.pdf.File2

    copy "D:\SqlServer2008查询性能优化.pdf.File

    Oracle sql优化.pdf

    本文将从 Oracle SQL 优化的基础知识开始,介绍 SQL 执行步骤、SQL 优化的基础知识、性能检测工具、性能问题 SQL 分析、执行计划分析、优化器参数调整等方面的知识点。 一、SQL 执行步骤 SQL 执行步骤是 Oracle ...

    sqlservr.exe和sqlos.dll 64和32位

    安装到SqlServer服务的时候提示启动服务失败,这里就是关键啦,下载本文的两个附件,里面是SP4(2005.90.5000.0)版本的sqlservr.exe和sqlos.dll。32位下载sqlservr32.rar,64位下载sqlservr64.rar。

    NTWDBLIB.DLL这个文件的版本号为2000.80.2039.0

    但最主要的是一定要找一个NTWDBLIB.DLL文件放到system32目录下,还有也要放到PHP的安装目录下(可能这一步可以省,但没有试过),最主要的是要确定NTWDBLIB.DLL这个文件的版本号为2000.80.2039.0,2000.80.194.0的...

Global site tag (gtag.js) - Google Analytics