为什么你的SQL，跑不动千万级的数据？-技术圈

点击蓝色“有关SQL”关注我哟

加个“星标”，天天与10000人一起快乐成长

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

Select Max(AuditDate) As AuditDate From AuditTrail

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

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

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

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

主程大咖，我平时蛮看好的一个程序员，主攻前端UI， Angular 1,2,3,4 都难不倒他，手写倒排算法，c#, Node.js, React, Kendo 也都是随手捏来，毫不费劲，荣耀里的王者，大家眼中的技术大神，近 8 年一线外企工作经验。看到这条 SQL 运行了这么长时间，毫无头绪的跑到 DB 组来求救。

当然，也有很多朋友是不信的。我特意做了个视频，放在这里解释

视频版演绎《一条极简 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 #AuditTrail


DECLARE @INC INT;
SELECT @INC = 5000000;


; WITH L0
AS (
    SELECT *
    FROM (
        VALUES (1)
            ,(2)
            ,(3)
        ) AS T(C)
    )
    ,L1
AS (
    SELECT a.C
        ,b.C AS BC
    FROM L0 AS a
    CROSS JOIN L0 AS b
    )
    ,L2
AS (
    SELECT a.C
        ,b.C AS BC
    FROM L1 AS a
    CROSS JOIN L1 AS b
    )
    ,L3
AS (
    SELECT a.C
        ,b.C AS BC
    FROM L2 AS a
    CROSS JOIN L2 AS b
    )
    ,L4
AS (
    SELECT a.C
        ,b.C AS BC
    FROM L3 AS a
    CROSS JOIN L3 AS b
    )
    ,L5
AS (
    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.AuditDate
FROM (
    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
    ) TMP
CROSS JOIN #AuditTrail ATT

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

SELECT object_name(object_id) as objname,rows
FROM sys.partitions with(nolock)
WHERE object_id = object_id(N'dbo.AuditTrail')

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

Select Max(AuditDate) As AuditDate From AuditTrail
7 秒


CREATE INDEX IDX_AuditDate ON AuditTrail(AuditDate)

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

CHECKPOINT
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

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

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

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

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

Begin Transaction
UPDATE AuditTrail
SET AuditDate = GETUTCDATE()  + 1

再打开一个查询窗口，执行下面脚本：

Select Max(AuditDate) As AuditDate From AuditTrail

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

SELECT request_session_id
    ,resource_type
    ,request_mode
    ,request_status
FROM sys.dm_tran_locks
WHERE request_session_id IN (
        55
        ,54
        )
ORDER BY request_session_id


SELECT session_id
    ,STATUS
    ,command
    ,blocking_session_id
    ,last_wait_type
FROM sys.dm_exec_requests
WHERE session_id > 50
ORDER BY session_id DESC