为什么你的SQL,跑不动千万级的数据?

数据大师
数据大师
数据大师
294
文章
0
评论
2021-06-1112:12:25 评论 241 4851字
摘要

看了很多朋友写的 SQL 后,发现他们认为 SQL 遇上千万级的数据,就束手无策,想动用分布式计算这样的屠龙刀。所以,我觉得有必要重提下两个 SQL 优化的基础要点。 

Select Max(AuditDate) As AuditDate From AuditTrail

这条语句看上去,是不是超级简单。假使你是刚入门的 SQL 学者,对你来说,唯一要去查查 MSDN 的也就是 Max 函数用法。

对于已经编写过很多存储过程的老鸟来说,一眼就望到头了,不就是求最大 AuditDate 值嘛。

但是,当年这条语句在向客户展示 Demo 的时候,竟然跑了 20 多分钟,着实让老板丢了一把脸。半夜老板打越洋电话过来,不管怎么样,明天上午 11 点,重新演示 Demo 的时候,需要秒开。

这一下,开发们都着急上了火,这条语句最近半年可没听说什么有延迟这么严重啊,主程也跑过来说,这段 SQL 是他写的,想不到这把一运行,确实花了这么长时间。

为什么我会记住这一条普通的不能再普通的 SQL 呢,起因都在这次事故上。对于软件开发人员来说,数据库的理论知识有多重要?

我觉得这次的事故,可以很形象的回答这个问题。假如你碰巧也不知道为什么这条 SQL 会这么慢,我觉得你有必要再重新回炉一下数据库理论了。

至少,这条语句能考查出你对表结构, 索引,事务的理解。

让我们先从表结构与索引的角度,来看下为什么会花那么长时间:

首先表结构是这样的:

CREATE TABLE dbo.AuditTrail (    ID BIGINT IDENTITY(1, 1) NOT NULL    ,[DATE] DATE NOT NULL    ,[TIME] TIME(7) NOT NULL    ,[C-IP] VARCHAR(100) NULL    ,[C-SITENAME] VARCHAR(100) NULL    ,[S-COMPUTERNAME] VARCHAR(100) NULL    ,[S-IP] VARCHAR(100) NULL    ,[S-PORT] VARCHAR(100) NULL    ,[CS-METHOD] VARCHAR(100) NULL    ,[CS-URI-STEM] VARCHAR(MAX) NULL    ,[CS-URI-QUERY] VARCHAR(MAX) NULL    ,[SC-STATUS] VARCHAR(100) NULL    ,[SC-WIN32-STATUS] VARCHAR(100) NULL    ,[CS-HOST] VARCHAR(100) NULL    ,[CS(USER-AGENT)] VARCHAR(MAX) NULL    ,[CS(COOKIE)] NVARCHAR(MAX) NULL    ,[CS(REFERER)] VARCHAR(MAX) NULL    ,[APPLICATION] VARCHAR(30) NULL    ,[AREA] VARCHAR(100) NULL    ,[FULL_NAME] VARCHAR(100) NULL    ,[EMAIL_ADDRESS] VARCHAR(100) NULL    ,[AuditDate] DATETIME NOT NULL    )INSERT INTO AuditTrail (    [DATE]    ,[TIME]    ,[C-IP]    ,[C-SITENAME]    ,[S-COMPUTERNAME]    ,[S-IP]    ,[S-PORT]    ,[CS-METHOD]    ,[AuditDate]    )VALUES (    "2017-07-07"    ,CONVERT(TIME, "12:30:30")    ,"192.168.1.103"    ,"W3SVC1"    ,"EQCWEB01"    ,"192.168.2.103"    ,"80"    ,"GET"    ,GETUTCDATE()    )SELECT *FROM AuditTrail

如何给这表灌测试数据

使用 Tally Table 生成 1000W 数据很多初入 SQL 编程的朋友,可能不会接触到 Tally Table.这我也是从 Ben 大神的书上看来的。简要的说,就是有技巧利用数字的幂运算,生成一张数字辅助表。发挥我们的想象,这张数字表几乎在生成序列操作上面,无所不能

CREATE TABLE #AuditTrail ([ID] BIGINT IDENTITY(1, 1) NOT NULL,[DATE] DATE NOT NULL,[TIME] TIME(7) NOT NULL,[C-IP] VARCHAR(100) NULL,[C-SITENAME] VARCHAR(100) NULL,[S-COMPUTERNAME] VARCHAR(100) NULL,[S-IP] VARCHAR(100) NULL,[S-PORT] VARCHAR(100) NULL,[CS-METHOD] VARCHAR(100) NULL,[CS-URI-STEM] VARCHAR(MAX) NULL,[CS-URI-QUERY] VARCHAR(MAX) NULL,[SC-STATUS] VARCHAR(100) NULL,[SC-WIN32-STATUS] VARCHAR(100) NULL,[CS-HOST] VARCHAR(100) NULL,[CS(USER-AGENT)] VARCHAR(MAX) NULL,[CS(COOKIE)] NVARCHAR(MAX) NULL,[CS(REFERER)] VARCHAR(MAX) NULL,[APPLICATION] VARCHAR(30) NULL,[AREA] VARCHAR(100) NULL,[FULL_NAME] VARCHAR(100) NULL,[EMAIL_ADDRESS] VARCHAR(100) NULL,[AuditDate] DATETIME NOT NULL)INSERT INTO #AuditTrail ([DATE],[TIME],[C-IP],[C-SITENAME],[S-COMPUTERNAME],[S-IP],[S-PORT],[CS-METHOD],[AuditDate])SELECT *FROM (VALUES ("2017-07-07",CONVERT(TIME, "12:30:30"),"192.168.1.103","W3SVC1","EQCWEB01","192.168.2.103","80","GET",GETUTCDATE()),("2017-07-07",CONVERT(TIME, "12:30:30"),"192.168.1.103","W3SVC1","EQCWEB01","192.168.2.103","80","GET",GETUTCDATE())) AS TMP([DATE], [TIME], [C-IP], [C-SITENAME], [S-COMPUTERNAME], [S-IP], [S-PORT], [CS-METHOD], [AuditDate])SELECT *FROM #AuditTrailDECLARE @INC INT;SELECT @INC = 5000000;; WITH L0AS (    SELECT *    FROM (        VALUES (1)            ,(2)            ,(3)        ) AS T(C)    )    ,L1AS (    SELECT a.C        ,b.C AS BC    FROM L0 AS a    CROSS JOIN L0 AS b    )    ,L2AS (    SELECT a.C        ,b.C AS BC    FROM L1 AS a    CROSS JOIN L1 AS b    )    ,L3AS (    SELECT a.C        ,b.C AS BC    FROM L2 AS a    CROSS JOIN L2 AS b    )    ,L4AS (    SELECT a.C        ,b.C AS BC    FROM L3 AS a    CROSS JOIN L3 AS b    )    ,L5AS (    SELECT a.C        ,b.C AS BC    FROM L4 AS a    CROSS JOIN L4 AS b    )INSERT INTO AuditTrail (    [DATE]    ,[TIME]    ,[C-IP]    ,[C-SITENAME]    ,[S-COMPUTERNAME]    ,[S-IP]    ,[S-PORT]    ,[CS-METHOD]    ,[AuditDate]    )SELECT TMP.AuditDate AS [DATE]    ,ATT.[TIME]    ,ATT.[C-IP]    ,ATT.[C-SITENAME]    ,ATT.[S-COMPUTERNAME]    ,ATT.[S-IP]    ,ATT.[S-PORT]    ,ATT.[CS-METHOD]    ,TMP.AuditDateFROM (    SELECT TOP (@INC) DATEADD(DAY, RNK, "1900-01-01") AS [AuditDate]    FROM (        SELECT ROW_NUMBER() OVER (                ORDER BY (                        SELECT NULL                        )                ) AS RNK        FROM L5        ) M    ORDER BY RNK    ) TMPCROSS JOIN #AuditTrail ATT

使用下面的 SQL来查看表里数据的行数:

SELECT object_name(object_id) as objname,rowsFROM sys.partitions with(nolock)WHERE object_id = object_id(N"dbo.AuditTrail")

然后用我们开头的语句,看看最大日期是多少

Select Max(AuditDate) As AuditDate From AuditTrail7 秒CREATE INDEX IDX_AuditDate ON AuditTrail(AuditDate)

为了保证不受数据缓存的影响,我们需要清除缓存:

CHECKPOINTDBCC FREEPROCCACHEDBCC DROPCLEANBUFFERS

加了索引,就对字段做了排序。按照 SQL Server 索引二叉组织,第一页存储的就是最大值。即扫描一页就找到了,速度极快!现场的开发们无一不喜出望外,重要的是,可以准时吃饭了嘛。

"慢!"我一句话泼过去 6 盘冷水。"这样还不妥"。我又重新执行了 10 遍 这条语句,发现有时候,还是有点顿卡,众人莫名。

其实我们的这张表是实时记录日志的日志表。可以认为一直是处于动态更新之中的。这样就会受到事务的干扰。对,就是很多人,排斥的事务。

我们演示下实验:重新打开一个查询窗口,执行下面的脚本:

Begin TransactionUPDATE AuditTrailSET AuditDate = GETUTCDATE()  + 1

再打开一个查询窗口,执行下面脚本:

Select Max(AuditDate) As AuditDate From AuditTrail

发现什么问题了没:虽然我们的表里只有 1 条数据,但是在这里就是选不出来了。站在 DBA 的角度来分析,这里肯定是有等待产生了,所以我们用下面的语句来查看,这个等待是什么:

SELECT request_session_id    ,resource_type    ,request_mode    ,request_statusFROM sys.dm_tran_locksWHERE request_session_id IN (        55        ,54        )ORDER BY request_session_idSELECT session_id    ,STATUS    ,command    ,blocking_session_id    ,last_wait_typeFROM sys.dm_exec_requestsWHERE session_id > 50ORDER BY session_id DESC

是不是看到有一条记录是 WAIT 的状态,而且第二个结果集还显示 session 54 被 55 给阻塞了。

那怎么破呢?很简单。大家先回顾下事务以及事务隔离级别,重点在于隔离级别。

每个隔离级别要处理的事情,只有两个:

  1. 1. 脏读
  2. 2. 幻影读

要处理好这两件事,用的工具是锁。如果你允许你的程序有脏读,可以使用不加锁去读,这样不会阻塞别人,也不受别人阻塞。将隔离级别设置为 read uncommitted 或者用hint 暗示表读为不加锁的读

综上,处理好这条简单的 SQL, 一要加索引,二要加 WITH(NOLOCK) 或者设置事务隔离级别。其实还有第三种考虑,就是资源配额,当然我们不做深入讨论,很少用这个功能。

希望能帮到数据库不满10T的朋友!SQL Server 在处理 50T 以下的数据时,还是有保障的。要知道,纳斯达克交易所,也在用 SQL Server.

End.

作者:黄赟

转载如果涉及作品问题请联们第一时间删除(微信lovedata0520

更多文章前往首页浏览http://www.itongji.cn/

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: