非常有意思的SQL查询语句(自己认为)

最新推荐文章于 2023-07-21 10:16:31 发布
原创 最新推荐文章于 2023-07-21 10:16:31 发布 · 1.5k 阅读 · 2 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#sql #工作 #数据库 #c

本文讨论了数据库中scorenew表中的学生成绩管理，涉及补考规则。2006年不及格的学生在2007年有机会补考，通过则合格；即使2006年成绩刚好60分，也可补考，但补考不及格则视为不合格。关键在于确定每个学生每门课程最终以考试时间最晚的成绩为准。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

写本篇前　首先非常感谢的两位ＣＳＤＮ上的朋友是： echiynn(寶琲)和 zhazhaok(喳喳)
本人工作中遇到一张比较复杂的表，数据大约几百万条，由于涉及到工作机密，暂时以下面的数据来说明我要处理的问题。

数据库中scorenew表中有如下数据：记录了一个学校比较乱的学生成绩记录，每个学生对应不同的课程，一个课程又至少有两个老师，在２００６年里考试不及格的同学（result＝＇Ｆ＇），可在２００７年补考，若补考过了则该学生该门课程是认为合格的（如２００２１０１），也可以在２００６考试虽然是及格，但由于分数刚好６０，也允许同学补考，但补考成绩不及格则被认为该生课程是不合格的（如２００２１０２也就是对同一个学生ＩＤ，课程成绩以该门课程考试时间最大的那个为准）．

学号           　　课程名        　　　老师            考试结果     考试日期  班级
studentid      classname    classteacher     result      testdate     banji
2002100        数学          B2老师        P        2006-7-3     1
2002100        外语          C1老师        P        2006-7-5     1
2002100        语文          A1老师        P        2006-7-1     1
2002101        数学          B2老师        P        2006-7-3     1
2002101        语文          A1老师        F        2006-7-1     1
2002101        语文          A1老师        P        2007-7-1     1
2002102        数学          B1老师        P        2006-7-3     2
2002102        数学          B1老师        F        2007-7-3     2
2002102        外语          C1老师        P        2006-7-5     2
2002102        语文          A1老师        F        2006-7-1     2
2002102        语文          A1老师        F        2007-7-1     2
2002103        数学          B1老师        P        2006-7-3     2
2002103        外语          C2老师        P        2006-7-5     2
2002103        语文          A2老师        P        2007-7-1     2
2002104        数学          B2老师        P        2006-7-3      1
2002104        外语          C1老师        F        2006-7-5      1
2002104        外语          C1老师        P        2007-7-5      1
2002104        语文          A2老师        P        2006-7-1      1
．现在求下面几个ＳＱＬ语句：
１．求所有科目都及格的人数
1.1.先求出总人数
SELECT COUNT(DISTINCT studentid) AS 总人数 FROM scorenew
结果=5
1.2.再求出考试科目最终有不及格的个数。
 SELECT STUDENTID, Result
FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate
        FROM SCORENEW t
        WHERE NOT EXISTS
                  (SELECT 1
                 FROM SCORENEW
                 WHERE STUDENTID = t .STUDENTID AND
                       CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate)) a
GROUP BY STUDENTID, Result
HAVING (Result = 'F')
结果显示
2002102    F
改成如下语句
SELECT COUNT(DISTINCT STUDENTID) AS 不及格人数
FROM (SELECT STUDENTID, Result
        FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate
                FROM SCORENEW t
                WHERE NOT EXISTS
                          (SELECT 1
                         FROM SCORENEW
                         WHERE STUDENTID = t .STUDENTID AND
                               CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate))
              a
        GROUP BY STUDENTID, Result
        HAVING (Result = 'F'))
显示:
不及格人数
1
所以合格人数=5-1=4
 
２．求参加某门科目的人数和该门科目及格的人数．
以科目数学为例:
第一步:先求出参加该门课程的总人数
SELECT COUNT(DISTINCT studentid) AS 该科目总人数
FROM scorenew
WHERE (classname = '数学')
结果显示为5
第二步:
SELECT STUDENTID, Result
FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate
        FROM SCORENEW t
        WHERE NOT EXISTS
                  (SELECT 1
                 FROM SCORENEW
                 WHERE STUDENTID = t .STUDENTID AND
                       CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate) AND
              classname = '数学') a
GROUP BY STUDENTID, Result
HAVING (Result = 'F')
显示结果
2002102 F
改写成统计该科目不及格人数
SELECT COUNT(DISTINCT STUDENTID) AS 该科目不及格人数
FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate
        FROM SCORENEW t
        WHERE NOT EXISTS
                  (SELECT 1
                 FROM SCORENEW
                 WHERE STUDENTID = t .STUDENTID AND
                       CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate) AND
              classname = '数学') a
GROUP BY STUDENTID, Result
HAVING (Result = 'F')
该科目不及格人数
1
 
或者求科目是数学的所有成绩记录:
SELECT *
FROM scorenew
WHERE (classname = '数学')
ORDER BY studentid, testdate
 
 
同理求某个老师下面不及格的人数
SELECT COUNT(DISTINCT STUDENTID) AS 该老师下不及格人数
FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate, classteacher
        FROM SCORENEW t
        WHERE NOT EXISTS
                  (SELECT 1
                 FROM SCORENEW
                 WHERE STUDENTID = t .STUDENTID AND
                       CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate) AND
              classteacher = 'B2老师') a
GROUP BY STUDENTID, Result
HAVING (Result = 'F')
３．求２００６年度所有科目都及格的人数，即一次性考试通过的人数．
第一步:先求出2006的所有考试记录
SELECT * FROM scorenew WHERE (testdate < '2007')
(因为本数据中只有2006和2007年的数据)
为2006年的成绩创造个视图
CREATE VIEW v_score2006 AS SELECT studentid, classname, classteacher, result,
                                        testdate, banji
                                  FROM scorenew
                                  WHERE (testdate < '2007')
查看视图创建是否成功
SELECT * FROM v_score2006 ORDER BY studentid, testdate
再根据上面的视图进行查找
SELECT STUDENTID, Result
FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate
        FROM v_score2006 t
        WHERE NOT EXISTS
                  (SELECT 1
                 FROM v_score2006
                 WHERE STUDENTID = t .STUDENTID AND
                       CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate)) a
GROUP BY STUDENTID, Result
HAVING (Result = 'F')
查找结果:
Studentid result
2002101     F
2002102     P
2002104     F
改写成统计上面的个数
SELECT COUNT(DISTINCT STUDENTID) AS 该年不及格人数
FROM (SELECT STUDENTID, Result
        FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate
                FROM v_score2006 t
                WHERE NOT EXISTS
                          (SELECT 1
                         FROM v_score2006
                         WHERE STUDENTID = t .STUDENTID AND
                               CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate))
              a
        GROUP BY STUDENTID, Result
        HAVING (Result = 'F')) DERIVEDTBL
结果显示:
该年不及格人数:
3
统计该年具体科目不及格人数
以语文为例子 //绿色标记的要对称
SELECT STUDENTID, Result, CLASSNAME, TestDate
FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate
        FROM v_score2006 t
        WHERE NOT EXISTS
                  (SELECT 1
                 FROM v_score2006
                 WHERE STUDENTID = t .STUDENTID AND
                       CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate) AND
              classname = '语文') a
GROUP BY STUDENTID, Result, CLASSNAME, TestDate
HAVING (Result = 'F')
显示结果:
2002101 F    语文 2006-7-1
2002102 F    语文 2006-7-1
或者连班级也显示在内可用下面的语句
SELECT STUDENTID, Result, CLASSNAME, TestDate, banji
FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate, banji
        FROM v_score2006 t
        WHERE NOT EXISTS
                  (SELECT 1
                 FROM v_score2006
                 WHERE STUDENTID = t .STUDENTID AND
                       CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate) AND
              classname = '语文') a
GROUP BY STUDENTID, Result, CLASSNAME, TestDate, banji
HAVING (Result = 'F')
显示结果:
2002101 F    语文 2006-7-1    1
2002102 F    语文 2006-7-1    2
改写成统计个数的语句
SELECT COUNT(DISTINCT STUDENTID) AS 该年该科目不及格人数
FROM (SELECT STUDENTID, Result, CLASSNAME, TestDate, banji
        FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate, banji
                FROM v_score2006 t
                WHERE NOT EXISTS
                          (SELECT 1
                         FROM v_score2006
                         WHERE STUDENTID = t .STUDENTID AND
                               CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate) AND
                      classname = '语文') a
        GROUP BY STUDENTID, Result, CLASSNAME, TestDate, banji
        HAVING (Result = 'F'))
显示结果:
该年该科目不及格人数
2
求该年该班级不及格的人数
SELECT STUDENTID, Result, CLASSNAME, TestDate, banji
FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate, banji
        FROM v_score2006 t
        WHERE NOT EXISTS
                  (SELECT 1
                 FROM v_score2006
                 WHERE STUDENTID = t .STUDENTID AND
                       CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate) AND
              banji = '1') a
GROUP BY STUDENTID, Result, CLASSNAME, TestDate, banji
HAVING (Result = 'F')
显示结果:
2002101 F 语文    2006-7-1   1
2002104 F 外语    2006-7-5   1
改写成统计结果
: SELECT COUNT(DISTINCT STUDENTID) AS 该年该班级不及格人数
FROM (SELECT STUDENTID, Result, CLASSNAME, TestDate, banji
        FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate, banji
                FROM v_score2006 t
                WHERE NOT EXISTS
                          (SELECT 1
                         FROM v_score2006
                         WHERE STUDENTID = t .STUDENTID AND
                               CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate) AND
                      banji = '1') a
        GROUP BY STUDENTID, Result, CLASSNAME, TestDate, banji
        HAVING (Result = 'F'))
显示结果:
该年该班级不及格人数:
2
查找该年该班级某个科目不及格人数
以1班语文成绩为例
SELECT STUDENTID, Result, CLASSNAME, TestDate, banji
FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate, banji
        FROM v_score2006 t
        WHERE NOT EXISTS
                  (SELECT 1
                 FROM v_score2006
                 WHERE STUDENTID = t .STUDENTID AND
                       CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate) AND
              banji = '1' AND classname = '语文') a
GROUP BY STUDENTID, Result, CLASSNAME, TestDate, banji
HAVING (Result = 'F')
显示结果:
2002101    F      语文     2006-7-1    1
改写成统计结果:
SELECT COUNT(DISTINCT STUDENTID) AS 该年该班该科目不及格人数
FROM (SELECT STUDENTID, Result, CLASSNAME, TestDate, banji
        FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate, banji
                FROM v_score2006 t
                WHERE NOT EXISTS
                          (SELECT 1
                         FROM v_score2006
                         WHERE STUDENTID = t .STUDENTID AND
                               CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate) AND
                      banji = '1' AND classname = '语文') a
        GROUP BY STUDENTID, Result, CLASSNAME, TestDate, banji
        HAVING (Result = 'F'))
结果显示:
该年该班该科不及格人数
1
４．求某个时间范围内某个科目及格的人数．
 1.同上,先创建某个时间范围内的数据视图(在这不要再写了,因为前面已经创建了)
CREATE VIEW v_score2006 AS SELECT studentid, classname, classteacher, result,
                                        testdate, banji
                                  FROM scorenew
                                  WHERE (testdate < '2007')
或者创建某个时间范围内某个特定班级的视图,这样会更加方便处理
如创建2006年语文科目的视图.
CREATE VIEW v_score2006china AS
 SELECT studentid, classname, classteacher, result, testdate,banji FROM scorenew
 WHERE (testdate < '2007') AND classname = '语文'
查找下看所建的视图是否正确
Select * from v_score2006china
2.求这个时间段参加该科目考试的人数
Select count (distinct studentid) as 该年该科目的考试人数　from v_score2006china
显示结果
该年该科目的考试人数
５
３．统计该年该科目不及格人数
SELECT STUDENTID, Result, CLASSNAME, TestDate, banji
FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate, banji
        FROM v_score2006china t
        WHERE NOT EXISTS
                  (SELECT 1
                 FROM v_score2006china
                 WHERE STUDENTID = t .STUDENTID AND
                       CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate)) a
GROUP BY STUDENTID, Result, CLASSNAME, TestDate, banji
HAVING (Result = 'F')
显示结果：
2002101     F    　　语文　　　　2006-7-1      1
2002102     F        语文　　　　2006-7-1      2
改成统计个数
SELECT COUNT(DISTINCT STUDENTID) AS 该年该科目不及格人数
FROM (SELECT STUDENTID, Result, CLASSNAME, TestDate, banji
        FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate, banji
                FROM v_score2006china t
                WHERE NOT EXISTS
                          (SELECT 1
                         FROM v_score2006china
                         WHERE STUDENTID = t .STUDENTID AND
                               CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate))
              a
        GROUP BY STUDENTID, Result, CLASSNAME, TestDate, banji
        HAVING (Result = 'F')) DERIVEDTBL
显示结果为
２
换成统计该年该班该科目不及格人数
SELECT STUDENTID, Result, CLASSNAME, TestDate, banji
FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate, banji
        FROM v_score2006china t
        WHERE NOT EXISTS
                  (SELECT 1
                 FROM v_score2006china
                 WHERE STUDENTID = t .STUDENTID AND
                       CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate) AND
              banji = '1') a
GROUP BY STUDENTID, Result, CLASSNAME, TestDate, banji
HAVING (Result = 'F')
．本人不材，在此很多工作都是重复的工作．
最后再次感谢此次项目中对我很多帮助的朋友同事
深入剖析一条SQL更新语句是如何执行的？
yonggeit的博客
07-26 89
前面我们系统了解了一个查询语句的执行流程，并介绍了执行过程中涉及的处理模块。相信你还记得，一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎。那么，一条更新语句的执行流程又是怎样的呢？之前你可能经常听 DBA 同事说，MySQL 可以恢复到半个月内任意一秒的状态，惊叹的同时，你是不是心中也会不免会好奇，这是怎样做到的呢？前面我有跟你介绍过 SQL 语句基本的执行链路，这里我再把那张图拿过来，你也可以先简单看看这个图回顾下。
一些搞笑的sql语句
08-20
总结的一些高效的sql语句，希望对童鞋们有所帮助！
一条有意思的SQL语句分析
yzc的专栏
08-28 1431
                        一条有意思的SQL语句分析       这条语句，说实在的困扰了我很久了，以前由于工作忙及工作中很少用到此种写法，即使有碰到这种要求也用别的语句代替或用另外一种方案实现，所以一直没空理它。这次星期天闲着没事，认真地思考分析了一下这条语句，还真有点意思。学生表 S(SNO int pk,SN varchar(8))  --SNO为学号 ,SN为学生
个人总结的几条比较有意思的sql语句
Beirut的专栏
01-23 2738
 ---------------------------------------------------------------------- 作者：张爱国-- 日期：2010-01-23 15:00:17-- 描述: 一些比较有意思的SQL语句-- Version:--    Microsoft SQL Server 2005 - 9.00.3042.00 (In
一个很有意思的sql语句
Eric的JAVA之路
08-09 613
今天在更新的sql代码里面看到一个很有意思的sql语句 select 1 from 表名，经过查询，结果是返回有几个结果，结果都为1，而不是之前认为的返回第1列的数值。下面是一个具体的用途的解释：  select * from ta where exists (select 1
有趣的sql语句
jqh19881022的专栏
10-02 554
1.select top 2* from Course order by newid()查找任意两行的数据 2.从一个存储过程中获取另一个存储过程的结果集：建立临时表，INSERT INTO #tmp      EXEC procedure_name @parameters_var，对于动态的sql语句INSERT INTO #tmp EXEC  dbo.sp_executesql @que
.net core实用技巧——将EF Core生成的SQL语句显示在控制台中
12-17
如果你的项目中使用了EF Core, 且正在处于性能调优阶段，那么了解EF Core生成的SQL语句是非常关键的。那么除了使用第三方工具，如何查看EF Core生成的SQL语句呢？这里笔者将给出一个基于.NET Core内置日志组件的实现...
帆软SQL语句巩固测试题
qq_43700270的博客
05-22 6908
SQL语句学习欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 欢迎使用Markdown编辑器 你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdo
Oracle+sql+取年度,oracle SQL语句取本周本月本年的数据
weixin_39955233的博客
04-10 794
--国内从周一到周日 国外是周日到周六select to_char(sysdate-1,'D') from dual;--取国内的星期几 去掉减一取国外的星期--取本周时间内的数据select * from table where DTIME >=trunc(next_day(sysdate-,)+) and DTIME<=trunc(next_day(sysdate-,)+)+ ;...
一些有创意的SQL语句
weixin_30654419的博客
07-01 212
1.把某个字段重新生气序列(从1到n): DECLARE @i int Set @i = 0 Update Table1 Set @i = @i + 1,Field1 = @i 2.按成绩排名次 Update 成绩表 Set a.名次 = ( Select Count(*) + 1 From 成绩表 b Where a.总成绩 < b.总成绩 ) From 成绩表 a ...
你见过哪些有意思的SQL语句？
weixin_67852961的博客
07-21 208
避免了使用group by必须将所有分组字段逐个写到select中的烦恼，常用于多维度的排序筛选。从远程dblink创建本地物化视图，按需刷新数据，以提高访问速度。注意：这种复制表结构的方式，不会复制主键和索引。与上面的WITH AS一样，用于递归查询。例如：查询物料最新采购价格。常用于查询树状结果，例如。
特别实用的几种SQL语句送给大家，让你的SQL高大上
风水道人
08-10 274
在写SQL时，经常灵活运用一些SQL语句编写的技巧，可以大大简化程序逻辑。减少程序与数据库的交互次数，有利于数据库高可用性，同时也能显得你的SQL很牛B，让同事们眼前一亮。 1.插入或替换 如果我们想插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。 情景示例：这张表存的每个客户最近一次交易订单信息，要求保证单个用户数据不重复录入，且执行效率最高，与数据库交互最少，支撑数据库的高可用。 此时，可以使用"REPLACE INTO"语句，这样就不必先查...
一些有趣的SQL题目
我想我是海 冬天的大海 心情随风轻摆
09-20 1848
题目来自: 点击打开链接 下面是本人的答案： --一下语句中用到的表tb均指这个测试表 --1、你猜下面这条语句能得到什么结果，这个很常用 if object_id('tb') is not null drop table tb go select getdate() create table tb(id int identity ,name varchar(10)) insert into
比较有意思的Sql语句
甜心奶酪的专栏
12-26 722
1、分组取前N条数据select  * from 表 awhere 主键 in ( select top 3 主键 from 表 where 类别=a.类别order by 排序方式 )order by 类别 , 排序方式 --可以不要order by 类别，这样主要是为了把同样的类别放在一起注意：一定不能用group by 和 游标，这样就大错特错了。特别是group by ，如果想到了
一个有趣的SQL语句求助
jadilee的专栏
10-15 275
1.需要找到表1中有黄色标注的单品（B,C,E,F) 2.表2为基础数据，该表记录了每一个单品每天的销售数量 3.表3为每一个单品的销售基本阈值，如果连续销售为0的值大于或者等于KeyDay, 4.黄色部分标注条件（销售为0，且连续为0的天数大于或者等于条件表3中的keyDay值）  ...
有趣的sql(3)
cong870624的博客
05-07 176
转贴自Lunar2000的专栏http://blog.youkuaiyun.com/lunar2000/ 显示结果很有趣。几个显示结果很有趣的SQL。转贴自Lunar2000的专栏http://blog.youkuaiyun.com/lunar200...
好玩的SQL
weixin_33963189的博客
06-10 142
1. 做一个3*3的加法表 SQL&gt; select a||'+'||b||'='||(a+b) from (select rownum a from all_objects where rownum&lt;4), (select rownum b from all_objects where rownum&lt;4); A||'+'||B||'='||(A+B) -------...
同一条SQL语句第二次跑比第一次快
最新发布
08-01
<think>嗯，用户问的是为什么SQL语句第二次执行比第一次快。这个问题其实在数据库优化领域很经典，让我梳理一下背后的机制。 用户可能是个刚接触数据库的开发人员，或者遇到了性能问题的运维。ta的提问很精准，直接聚焦在"第二次比第一次快"这个现象上，说明ta已经观察到具体的性能差异了。不过ta没提到具体数据库类型，我该用通用原理来解释。 从引用内容看，核心原因确实是缓存机制。引用[1]说得很直白：第一次查询时SQL Server要把数据和索引从磁盘加载到内存。这里其实包含两层缓存——执行计划缓存和数据缓存。执行计划缓存好理解，就是数据库不用重复做查询解析和优化；但数据缓存更关键，特别是当查询涉及大量磁盘I/O时。 突然想到引用[2]提到MySQL逻辑架构，虽然没展开，但所有数据库基本都遵循这个原理：查询执行要经过解析器→优化器→执行引擎→存储引擎。第二次查询时前两个步骤可能直接跳过，执行引擎也可能直接从缓冲池(Buffer Pool)拿数据。 引用[3]展示的游标处理代码很有意思，它暗示了另一个角度：应用程序本身也可能有缓存设计。不过用户问题更偏向数据库层面，这个点暂时不用展开。 用户没问但可能关心的是：这种加速效果能持续多久？引用[1]提到"Sql Server会定期清除缓存"，所以如果间隔太久或者内存不足，第二次查询可能还是会慢。要不要补充这个呢？还是等ta追问吧。 优化建议方面，引用[4][5]提到的批量执行SQL倒是不相关，但可以引申出人工预热缓存的手段——比如系统启动时主动执行关键查询。不过这个对普通用户可能超纲了... 最后决定用分层结构解释：先讲执行计划缓存，再讲数据缓存，最后提缓存失效机制。用磁盘I/O和内存访问的速度对比来强化认知，毕竟这是千倍级的差异。示例用SELECT很稳妥，但得强调UPDATE/DELETE第一次也可能慢——虽然用户没问，但避免ta产生误解。</think>SQL语句第二次执行比第一次快的主要原因在于数据库系统内部的**缓存机制优化**，具体体现在以下两个核心层面： --- ### 一、执行计划缓存（减少解析与优化开销） 1. **首次执行流程** 当SQL语句首次执行时，数据库需完整执行以下步骤： - **语法解析**：检查SQL语法正确性 - **语义分析**：验证表/列是否存在、权限等 - **查询优化**：生成多个候选执行计划，选择成本最低的方案（如索引选择、连接顺序优化） - **编译执行计划**：将优化后的方案转为可执行代码 > ⏳ 此过程消耗大量CPU和计算资源。 2. **第二次执行优化** 数据库会将编译好的**执行计划缓存到内存**（如SQL Server的`Plan Cache`，MySQL的`Query Cache`）。后续相同SQL可直接复用缓存计划，跳过解析优化阶段，显著降低延迟[^1][^2]。 --- ### 二、数据缓存（减少磁盘I/O） 1. **首次数据加载** 第一次执行时，数据库需从磁盘读取： - 目标数据页 - 相关索引页 - 系统元数据（如统计信息） > 🐌 磁盘I/O（毫秒级）比内存访问（纳秒级）慢数千倍。 2. **缓存生效机制** 首次执行后，以下内容将加载至内存缓冲区： - **数据页缓存**（如SQL Server的`Buffer Pool`，MySQL的`InnoDB Buffer Pool`） - **索引缓存** - 频繁访问的系统表 > ✅ 第二次查询直接读取内存缓存，避免磁盘访问，速度提升显著[^1][^3]。 --- ### 三、缓存失效场景 即使第二次执行，以下情况仍可能导致性能下降： | 场景 | 原因 | |---------------------|----------------------------------------------------------------------| | 内存压力 | 缓存被其他查询挤出（如SQL Server定期清理缓存）[^1] | | 统计信息更新 | 数据分布变化触发优化器重新生成执行计划 | | 参数化差异 | 非参数化查询中WHERE条件值变化可能导致新执行计划（如`WHERE id=1` vs `id=1000`) | | 架构变更 | 表结构/索引修改后原有计划失效 | --- ### 示例对比（假设查询`SELECT * FROM users WHERE id=100`） | 执行次数 | 关键步骤 | 资源消耗 | |----------|-----------------------------------|----------------| | 第一次 | 解析+优化+磁盘读数据+返结果 | 高CPU+高I/O | | 第二次 | 内存读执行计划+内存读数据+返结果 | 低CPU+零磁盘I/O | > 💡 **性能差距**：首次执行若需100ms，第二次可能仅需1-5ms（取决于数据量和内存速度）。 --- ### 如何验证缓存效果？ 1. **SQL Server** 检查执行计划缓存： ```sql SELECT plan_handle, execution_count FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE text LIKE '%你的SQL语句%'; ``` 2. **MySQL** 查看查询缓存状态（若启用）： ```sql SHOW STATUS LIKE 'Qcache_hits'; -- 缓存命中次数 ``` --- ### 总结关键原因 | 优化层级 | 首次执行消耗 | 二次执行优化 | |----------------|--------------------|--------------------| | **执行计划** | 解析+优化+编译 | 内存复用编译后计划 | | **数据访问** | 磁盘读取数据/索引 | 内存直接访问缓存 | > 通过以上机制，数据库系统实现了「越查询越快」的自我优化效果[^1][^2]。 --- ### 相关问题 1. **如何强制清理数据库缓存以测试真实性能？** 2. **参数化查询与非参数化查询对缓存复用有何影响？** 3. **数据库内存缓冲区大小如何配置更合理？** 4. **除了缓存复用，还有哪些SQL性能优化手段？** 5. **为什么有时相同SQL的第二次执行反而变慢？（缓存失效场景分析）** [^1]: SQL Server 缓存机制解析 [^2]: MySQL 逻辑架构与查询流程 [^3]: 游标处理中的内存数据访问优化
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值