本文来源 | 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进行优化,所以分析执行计划很重要。
相关推荐
面对“只进结果集不支持请求的操作”的异常,通过调整查询类型、使用原生SQL或优化数据库连接配置,可以有效避免此类问题,确保应用程序的正常运行。在实际开发中,应根据项目需求和数据库特性灵活选择最合适的解决...
未能加载文件或程序集“Microsoft.SqlServer.Diagnostics.STrace, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”或它的某一个依赖项。系统找不到指定的文件。 (Microsoft.SqlServer.Dmf) ...
“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, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”或它的某一个依赖项。系统找不到指定的文件。 (Microsoft.SqlServer.Dmf) ...
在oracle里面运行一下,解决Exception java.sql.SQLException ORA-00600 内部错误代码
标题 "未能加载文件或程序集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版本,这是一个针对SQL Server的插件,旨在优化SQL编写体验。 此版本的SQLPrompt提供了以下几个主要功能: 1. **智能提示**:当用户在编写...
SQLPrompt-10.14.0.4064.exe 支持SSMS 19.1
Oracle SQL Developer 企业管理 Oracle12c连接工具,sqldeveloper18.2.0.183.1748-no-jre Oracle JDeveloper
"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.787这个版本中,用户可以享受到更加智能和精准的SQL编写体验。Keygen(密钥生成器)通常用于激活软件的全功能版本,但请注意,这里提到的"SQLPrompt_7.5.0.787Keygen各版本通用"可能涉及到未经...
这个驱动程序是Java Database Connectivity (JDBC)的一部分,允许Java开发者通过编写Java代码来访问和操作SQL Server数据库。 首先,我们需要理解JDBC。它是Java平台的一个标准接口,由Sun Microsystems(现为...
在IT行业中,数据库操作是核心任务之一,而Ibatis作为一款优秀的Java持久层框架,极大地简化了数据库操作。本文将深入探讨与“sql-map-2.dtd”和“sql-map-config-2.dtd”相关的知识点,以及它们在Ibatis中的作用。 ...
SQL Server 2005是微软推出的一款关系型数据库管理系统,它在Windows 8到Windows 10操作系统上运行,虽然这些系统版本较新,但SQL Server 2005仍可以通过特定步骤进行安装。`sqlservr.exe`和`sqlos.dll`是SQL Server...
copy "D:\SqlServer2008查询性能优化.pdf.File
本文将从 Oracle SQL 优化的基础知识开始,介绍 SQL 执行步骤、SQL 优化的基础知识、性能检测工具、性能问题 SQL 分析、执行计划分析、优化器参数调整等方面的知识点。 一、SQL 执行步骤 SQL 执行步骤是 Oracle ...
安装到SqlServer服务的时候提示启动服务失败,这里就是关键啦,下载本文的两个附件,里面是SP4(2005.90.5000.0)版本的sqlservr.exe和sqlos.dll。32位下载sqlservr32.rar,64位下载sqlservr64.rar。
但最主要的是一定要找一个NTWDBLIB.DLL文件放到system32目录下,还有也要放到PHP的安装目录下(可能这一步可以省,但没有试过),最主要的是要确定NTWDBLIB.DLL这个文件的版本号为2000.80.2039.0,2000.80.194.0的...