MSSQL－應用案例－SQL Server 2016基於內存優化表的列存儲索引分析Web Access Log

問題引入

在日常的網站運維工作中，我們需要對網站客戶端訪問情況做統計、匯總、分析和報表展示，以數據來全麵掌控網站運營和訪問情況。當不可預知的意外情況發生時，我們可以快速發現問題以及采取相應的措施。比如：當網站受到黑客攻擊時的流量陡增，又或者是網站某個資源發生意外拋異常等情況。
在提供Web服務的服務器上，比如IIS、Apache都存在訪問日誌記錄，這篇是文章是以SQL Server 2016基於內存優化表的列存儲索引來分析Apache Web Access Log為例，講解分析網站訪問情況，因此，我們需要解決以下幾個問題：
Apache Web Access Log格式介紹
列存儲索引表結構的設計
Apache Web Access Log導入到列存儲索引表
網站訪問流量統計
客戶端主機訪問的分布情況
客戶端主機訪問的資源統計
異常URI訪問統計
Response Code分布情況

日誌格式介紹

在設計基於內存優化表的列存儲索引表結構之前，我們首先必須要對Apache Web Access Log服務器普通日誌格式了解得非常清楚，以日誌結構來建立我們SQL Server 2016的列存儲索引表結構，在此，僅以一條日誌記錄格式來分析：

## 通用日誌格式
LogFormat "%h %l %u %t \"%r\" %>s %b" common

## 其中一條日誌舉例
64.242.88.10 - - [07/Mar/2004:16:47:12 -0800] "GET /robots.txt HTTP/1.1" 200 68
......

其中：
%h：發送請求到服務器的客戶端主機（IP或者是主機名），本例中的64.242.88.10；
%l：確定訪問用戶的標識（因為不可靠，通常不會使用，用中劃線來填充），本例中的第一個中劃線；
%u：由HTTP認證確定的用戶名稱，本例中的第二個中劃線；
%t：服務器端收到客戶端請求的時間點，格式為：[day/month/year:hour:minute:second zone]，本例中的[07/Mar/2004:16:47:12 -0800]；
%r：置於雙引號之間的請求詳細信息，包括三部分：請求方法、請求的資源和客戶端協議及版本。本例中的"GET /robots.txt HTTP/1.1"；
%>s：返回的Response Code，比如本例中200表示訪問成功；
%b：返回給客戶端的對象大小，不包含HTTP Header，單位為byte，本例中獲取了68 byte資源。

基於內存優化表的列存儲索引表結構設計

基於以上對Apache Web Access Log格式的分析，我們可以建立格式對等的基於內存優化表的列存儲索引表。這種類型的表數據會按列壓縮存放在內存中，可以大大減少OLAP查詢對IOPS的消耗，提高OLAP分析查詢的性能。其表結構如下所示：

USE CCSI2016
GO
DROP TABLE IF EXISTS dbo.WebAccessLOG
GO

CREATE TABLE dbo.WebAccessLOG (
[LogId] BIGINT IDENTITY(1,1) NOT NULL,
[RemoteHost] [varchar](100) NULL,
[UserIdentity] varchar(10) NULL,
[UserName] varchar(10) NULL,
[RequestTime] varchar(50) NULL,
[Timezone] varchar(10) NULL,
[Action] varchar(10) NULL,
[URI] VARCHAR(1000) NULL,
[Version] VARCHAR(20) NULL,
[StatusCode] varchar(5) NULL,
[Size_Byte] INT NULL,
[Indate] DATETIME NOT NULL CONSTRAINT DF_Indate DEFAULT(GETDATE()),
CONSTRAINT PK_WebAccessLOG PRIMARY KEY NONCLUSTERED HASH ([LogId]) WITH (BUCKET_COUNT = 10000000)
)WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) ;
GO

ALTER TABLE dbo.WebAccessLOG
    ADD INDEX CCSI_WebAccessLOG CLUSTERED COLUMNSTORE
;
GO

在建表過程中，我們設置MEMORY_OPTIMIZED = ON，表示該表為內存優化表，此類表數據會存放在內存中；DURABILITY = SCHEMA_AND_DATA表示，我們需要持久化表結構和數據到磁盤上，以防止服務意外終止而導致的數據丟失；最後一句ALTER TABLE ADD INDEX CLUSTERED COLUMNSTORE表示為該內存優化表建立聚集列存儲索引，此類型表數據會被壓縮存放在內存中。

導入日誌信息到列存儲索引表

我們完成了基於內存優化表的列存儲索引表設計以後，接下來，我們需要將Apache Web Access Log文件導入到該表中。由於Log文件不帶表頭，第一行就直接是數據；每行之間的信息以空格分割；行與行之間以換行分割，所以，我們可以使用BULK INSERT的方式將Log文件導入列存儲索引表。方法如下：

USE CCSI2016
GO
-- Create view base on log table
DROP VIEW IF EXISTS dbo.[UV_WebAccessLOG]
GO
CREATE VIEW [dbo].[UV_WebAccessLOG]
AS
SELECT [RemoteHost]
    ,[UserIdentity]
    ,[UserName]
    ,[RequestTime]
    ,[Timezone]
    ,[Action]
    ,[URI]
    ,[Version]
    ,[StatusCode]
    ,[Size_Byte]
FROM CCSI2016.dbo.WebAccessLOG WITH(NOLOCK)
GO

-- BULK INSERT Log into view
BULK INSERT dbo.[UV_WebAccessLOG]
FROM 'C:\Temp\access_log'
WITH (
 FIRSTROW = 1,
 FIELDTERMINATOR = ' ',
 ROWTERMINATOR = '\n'
)

-- Init data
;WITH DATA
AS(
    SELECT TOP (1545) LogId
    FROM CCSI2016.dbo.WebAccessLOG AS A
    ORDER BY Indate DESC
)
UPDATE TOP(1545) A
SET RequestTime = REPLACE(RequestTime, '[', '')
FROM CCSI2016.dbo.WebAccessLOG AS A
WHERE LogId IN(SELECT LogId FROM DATA)

代碼解釋：由於列存儲索引表增加了自增列LogId和時間字段Indate，我們無法直接將數據BULK INSERT到正式表，需要建立視圖dbo.[UV_WebAccessLOG]來作為中間橋梁；數據導入完畢後，由於RequestTime字段含有中括號左半部分，我們需要將中括號刷洗掉。至此，列存儲索引表創建完畢，訪問Log日誌也已經導入，接下來就是詳細的統計分析了。

網站流量統計分析

網站的流量統計是以時間為單位統計所有客戶端訪問網站的點擊數量和以此獲取到的資源總流量大小。時間單位可以小到每秒，大到每小時或者每天為單位來統計，這個統計值可以數據化網站的訪問流量，隨時監控網站是否有意外發生，或者是意外的突發訪問，比如：被黑客攻擊導致流量突然增大。在此，僅以天為時間單位，描述網站流量統計分析的方法。

USE CCSI2016
GO
DROP PROCEDURE IF EXISTS dbo.UP_LoadingAnalysis
GO
CREATE PROCEDURE dbo.UP_LoadingAnalysis 
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 
AS BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    SELECT
        Day = CONVERT(CHAR(10), RequestTime, 120) 
        , minSize = CAST(MIN(Size_Byte) / 1024. AS DECIMAL(8, 2))
        , maxSize = CAST(MAX(Size_Byte) / 1024. AS DECIMAL(8, 2))
        , avgSize = CAST(AVG(Size_Byte) / 1024. AS DECIMAL(8, 2))
        , sumSize = CAST(SUM(Size_Byte) / 1024. AS DECIMAL(8, 2))
        , NoOfHits = COUNT(1)
    FROM dbo.WebAccessLOG
    GROUP BY CONVERT(CHAR(10), RequestTime, 120)
    ORDER BY 1 ASC
END
GO

單獨執行該存儲過程，返回的結果如下圖所示：

將返回的結果，做成一個Chart圖表，如下圖所示：

從返回的數據結果集和做出的圖表展示，我們很容易發現2004年3月8號這一天無論是點擊率還是網站流量都是6天內最高的。那麼，對這些流量貢獻排名前十的是哪些客戶端機器呢？請看下一小節。

客戶端主機訪問分布情況

流量統計部分隻能回答“哪個時間段流量超標”的問題，如果我們需要知道流量超標時間段內，到底是哪個或者哪些客戶端主機訪問量過大，客戶端主機訪問流量分布情況如何？在此，我們以2004年3月8號為例，分析客戶端主機訪問分布情況，代碼如下所示：

USE CCSI2016
GO
DROP PROCEDURE IF EXISTS dbo.UP_FrequentAccessHosts
GO
CREATE PROCEDURE dbo.UP_FrequentAccessHosts 
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 
AS BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

    SELECT RemoteHost
        , NoOfAccess = COUNT(1)
        , Size = cast(SUM(Size_Byte)/ 1024. as decimal(8,2))
    FROM dbo.WebAccessLOG
    WHERE [RequestTime] >= '08/Mar/2004' 
        AND [RequestTime] <= '09/Mar/2004'
    GROUP BY RemoteHost
    HAVING COUNT(1) >= 10
    ORDER BY RemoteHost ASC

END 
GO

執行該存儲過程，返回如下的結果集：

將這個返回的結果集，做成圖表展示如下圖所示：

從返回的結果集和圖表展示，我們很容易得出，來自客戶端機器64.242.88.10的點擊率和訪問流量遠遠高於其他的客戶端。至此，我們已經成功的找到了訪問量最大的客戶端機器IP地址。我們可以針對這個客戶端主機進行分析，看看是否存在黑客攻擊行為，如果存在，可以考慮從網絡層禁止這個IP訪問網站資源。那麼，客戶端主機訪問的是哪些網站資源呢？請繼續查看下一節。

客戶端主機訪問的資源

根據客戶端主機訪問分布情況部分，我們已經找到訪問量最大的某個或者某幾個客戶端主機，接下來我們需要回答“客戶端主機訪問的Web資源是哪些？經常被頻繁訪問的資源集中在哪些URI上？”。如果能夠找出這兩個問題，我們可以考量將對應的資源放到緩存設備中，以此來增加緩存的命中率，提高客戶機訪問網站資源的速度。方法如下：

USE CCSI2016
GO
DROP PROCEDURE IF EXISTS dbo.UP_FrequentAccessResouceURI
GO
CREATE PROCEDURE dbo.UP_FrequentAccessResouceURI 
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 
AS BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    -- TOP 10 URI
    SELECT TOP 10
            URI
            , NoOfHits = COUNT(1)
            , Size = CAST(SUM(Size_Byte)/ 1024. as decimal(8,2))
    FROM dbo.WebAccessLOG
    GROUP BY URI
    ORDER BY 2 DESC
END
GO

執行該存儲過程，返回如下結果集：

依據該結果集，做成圖表，展示如下圖所示：

從結果集和圖表展示的統計結果來看，點擊率最高的是獲取/twiki/pub/TWiki/TWikiLogos/twikiRobot46x50.gif資源的時候，而流量最大集中在對資源/twiki/bin/view/Main/WebHome的訪問上。

Response Code分布情況

在另一個方麵，網站客戶端主機訪問成功率是衡量一個網站是否正常工作很重要的指標，我們可以統計客戶端訪問HTTP服務的Response Code分布情況，來獲取客戶端主機訪問成功率，以此來判斷HTTP服務工作情況是否良好。方法如下：

USE CCSI2016
GO
DROP PROCEDURE IF EXISTS dbo.UP_ResponseCodeAnalysis
GO
CREATE PROCEDURE dbo.UP_ResponseCodeAnalysis 
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 
AS BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    SELECT
         StatusCode
        , ResponseCodeCount = COUNT(1)
    FROM dbo.WebAccessLOG
    GROUP BY StatusCode
    ORDER BY 1 ASC
END
GO

執行該存儲過程，返回的結果集如下所示：

將該存儲過程返回的結果集，做成圖表如下所示：

從存儲過程執行的結果集和展示的圖表來看，資源訪問成功率（返回為200的概率）僅為82.46%，換句話說，100個客戶端訪問中，僅有82.46個是成功訪問，成功率過低，還有很大的提升空間。因此，我們需要深入調查到底是訪問哪些URI導致了錯誤發生？請看下一小節。

報錯排名前十的URI

有時候，訪問我們的Web服務資源的時候，會發生很多意外情況（返回值不是200），我們需要對這些錯誤的發生有全麵的掌控，比如：統計Web站點上發生錯誤次數排名前十的資源有哪些？分析出這個問題的答案以後，我們就可針對錯誤的資源，定向查找訪問失敗的原因。

USE CCSI2016
GO
DROP PROCEDURE IF EXISTS dbo.UP_FrequentExceptionURI
GO
CREATE PROCEDURE dbo.UP_FrequentExceptionURI
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 
AS BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    SELECT TOP 10
            URI
            , NoOfHits = COUNT(1)
            , Size = CAST(SUM(Size_Byte)/ 1024. as decimal(8,2))
    FROM dbo.WebAccessLOG
    WHERE StatusCode <> 200
    GROUP BY URI
    ORDER BY 2 DESC
END
GO

執行該存儲過程，返回如下結果集：

將該結果集，做成圖表，展示如下所示：

從存儲過程返回的結果集和圖表展示的統計結果來看，資源/twiki/pub/TWiki/TWikiLogos/twikiRobot46x50.gif點擊發生的錯誤最多，而資源/twiki/bin/edit/Main/PostConf?topicparent=Main.PostfixCommands發生的錯誤流量最大。所以最終，我們找到了經常報錯的一些URI資源，我們需要解決這些錯誤，最終達到提高客戶端訪問成功率的目的。

最後總結

本篇月報是SQL Server列存儲索引係列月報的最後一篇，介紹SQL Server 2016基於內存優化表的列存儲索引的應用案例，分析Apache Web Access Log，以此來幫助我們分析和掌控網站的運行情況。至此，我們總共分析了四篇關於SQL Server列存儲技術，跨度從SQL Server 2012到SQL Server 2014，最終到SQL Server 2016。
SQL Server · 特性分析 · 2012列存儲索引技術：介紹SQL Server 2012列存儲索引技術。
SQL Server · 特性介紹 · 聚集列存儲索引：介紹SQL Server 2014中的聚集列存儲索引技術。
MSSQL · 特性分析 · 列存儲技術做實時分析：介紹了SQL Server 2016列存儲索引技術在實時分析場景中應用。

參考文檔

Log Files：Apache Web Access Log的日誌格式介紹。
Import and analyze IIS Log files using SQL Server：基於內存優化表的列存儲索引表結構設計。
Apache (Unix) Log Samples：本篇文章分析的Apache Web Access Log樣例數據。

最後更新：2017-05-04 12:01:17

MSSQL－應用案例－SQL Server 2016基於內存優化表的列存儲索引分析Web Access Log

問題引入

日誌格式介紹

基於內存優化表的列存儲索引表結構設計

導入日誌信息到列存儲索引表

網站流量統計分析

客戶端主機訪問分布情況

客戶端主機訪問的資源

Response Code分布情況

報錯排名前十的URI

最後總結

參考文檔

上一篇： V8 Ignition：JS 引擎與字節碼的不解之緣

下一篇：上雲，讓業務盡情擁抱互聯網：阿裏雲在企業專有雲與混合雲最佳實踐

相關內容

熱門內容

最新內容

MSSQL－應用案例－SQL Server 2016基於內存優化表的列存儲索引分析Web Access Log

問題引入

日誌格式介紹

基於內存優化表的列存儲索引表結構設計

導入日誌信息到列存儲索引表

網站流量統計分析

客戶端主機訪問分布情況

客戶端主機訪問的資源

Response Code分布情況

報錯排名前十的URI

最後總結

參考文檔

上一篇： V8 Ignition：JS 引擎與字節碼的不解之緣

下一篇： 上雲，讓業務盡情擁抱互聯網：阿裏雲在企業專有雲與混合雲最佳實踐

相關內容

熱門內容

最新內容

下一篇：上雲，讓業務盡情擁抱互聯網：阿裏雲在企業專有雲與混合雲最佳實踐