羅希意， 霍曉陽(yáng)， 傅洛伊

(上海交通大學(xué) 電子信息與電氣工程學(xué)院， 上海 200240)

在全世界范圍內(nèi)，每年自然科學(xué)和社會(huì)科學(xué)領(lǐng)域都會(huì)產(chǎn)出數(shù)以百萬(wàn)計(jì)的知識(shí)文獻(xiàn)，包括學(xué)術(shù)論文、科技報(bào)告和書(shū)籍等，并且呈爆發(fā)式速度增長(zhǎng).面對(duì)學(xué)術(shù)大數(shù)據(jù)[1]時(shí)代浩如煙海的學(xué)術(shù)資源，有效地檢索或查詢是一項(xiàng)極為重要的工作，因此，美國(guó)計(jì)算機(jī)協(xié)會(huì)(ACM)和美國(guó)電氣與電子工程師協(xié)會(huì)(IEEE)分別構(gòu)建了論文的電子數(shù)據(jù)庫(kù)，一些互聯(lián)網(wǎng)科技企業(yè)如谷歌、微軟和百度等分別推出了各自的學(xué)術(shù)搜索系統(tǒng)，以幫助科技工作者查詢學(xué)術(shù)文獻(xiàn).

作為國(guó)內(nèi)學(xué)術(shù)搜索研究領(lǐng)域的代表，可視化學(xué)術(shù)搜索系統(tǒng)AceMap以可視化的方式呈現(xiàn)了學(xué)術(shù)大數(shù)據(jù)中各學(xué)科學(xué)術(shù)領(lǐng)域論文之間的引用關(guān)系、論文作者之間的合作關(guān)系和師從關(guān)系、世界范圍內(nèi)各學(xué)術(shù)機(jī)構(gòu)和個(gè)人發(fā)表論文的統(tǒng)計(jì)信息等.AceMap系統(tǒng)已經(jīng)收錄約 1.2 億篇學(xué)術(shù)論文的相關(guān)信息，包括論文、作者、學(xué)術(shù)機(jī)構(gòu)、學(xué)術(shù)期刊和會(huì)議等學(xué)術(shù)實(shí)體及其關(guān)系，其數(shù)據(jù)主要來(lái)源于微軟學(xué)術(shù)圖譜[2]、ACM和IEEE的公開(kāi)數(shù)據(jù)庫(kù).所用方法：利用實(shí)體關(guān)系模型對(duì)這些學(xué)術(shù)實(shí)體及其關(guān)系進(jìn)行抽象分析，并通過(guò)關(guān)系型數(shù)據(jù)庫(kù)MySQL[3]來(lái)實(shí)現(xiàn)，生成的主要數(shù)據(jù)表為論文(Papers)、作者(Authors)、機(jī)構(gòu)(Affiliations)、期刊(Journals)、會(huì)議(Conferences)、學(xué)術(shù)領(lǐng)域(Field of Study)、論文與作者及其機(jī)構(gòu)的關(guān)系(Paper Author Affiliations)和論文引用關(guān)系(Paper References)等.這些數(shù)據(jù)表包含上億條的數(shù)據(jù)，依靠傳統(tǒng)的MySQL數(shù)據(jù)庫(kù)難以應(yīng)對(duì)高并發(fā)的應(yīng)用場(chǎng)景，而且需要滿足AceMap搜索系統(tǒng)的可視化學(xué)術(shù)關(guān)系分析和實(shí)時(shí)統(tǒng)計(jì)功能的要求，僅憑借基于全文檢索的搜索引擎技術(shù)還不夠，需要基于實(shí)體關(guān)系模型的結(jié)構(gòu)化查詢語(yǔ)言(SQL)的查詢技術(shù)，因而需要尋求合適的查詢優(yōu)化方法.

本文結(jié)合實(shí)際應(yīng)用場(chǎng)景提出了基于窗口函數(shù)(Window Functions)[4]的查詢優(yōu)化方法和基于分布式集群(SQL-on-Hadoop: SparkSQL)計(jì)算引擎[5-6]的查詢優(yōu)化方法，通過(guò)提取一些典型、引起系統(tǒng)性能瓶頸的分析型SQL查詢實(shí)例，以傳統(tǒng)的關(guān)系型數(shù)據(jù)庫(kù)MySQL上的查詢執(zhí)行時(shí)間為基準(zhǔn)進(jìn)行性能對(duì)比實(shí)驗(yàn)，從而驗(yàn)證所提出的查詢優(yōu)化方法的有效性和正確性.

1 基于窗口函數(shù)的查詢優(yōu)化方法

窗口函數(shù)查詢優(yōu)化是在實(shí)體關(guān)系模型中尋求解決方案，旨在單節(jié)點(diǎn)關(guān)系型數(shù)據(jù)庫(kù)的框架下優(yōu)化分析型SQL查詢.

1.1 窗口函數(shù)

窗口函數(shù)又稱(chēng)為OLAP[7]函數(shù)或分析函數(shù)，屬于SQL:2003標(biāo)準(zhǔn)[8]的新增部分.它通過(guò)提取數(shù)據(jù)集合中指定分組的行并用于聚合、排名或分析，所涉及的SQL查詢主要面向各類(lèi)聚合操作，常用的聚合函數(shù)包括數(shù)值求和函數(shù)(sum)、計(jì)數(shù)函數(shù)(count)、最小值函數(shù)(min)和最大值函數(shù)(max)等.窗口函數(shù)使用分區(qū)來(lái)代替?zhèn)鹘y(tǒng)SQL查詢的分組操作，使其能夠在各個(gè)分組中進(jìn)行多次聚合.其輸出包括基礎(chǔ)數(shù)據(jù)行和聚合結(jié)果，而分組操作只返回聚合結(jié)果.分區(qū)(Partitioning)、排序(Ordering)和分幀(Framing)是窗口函數(shù)的3個(gè)核心部分，它們構(gòu)成了窗口函數(shù)在SQL查詢中的語(yǔ)義和語(yǔ)法，三者之間的邏輯關(guān)系見(jiàn)圖1[4].

圖1 窗口函數(shù)的3個(gè)概念Fig.1 Three concepts of window function

(1) 分區(qū).分區(qū)是通過(guò)分區(qū)操作子句(Partition by)而實(shí)現(xiàn)的，是窗口函數(shù)的基礎(chǔ).根據(jù)分區(qū)操作子句指定的數(shù)據(jù)表字段，將其中數(shù)值相同的數(shù)據(jù)行劃分到同一個(gè)分區(qū)，以便于后續(xù)的聚合計(jì)算，但分區(qū)是執(zhí)行邏輯分區(qū)，這與分組操作的物理分區(qū)不同.例如，以AuthorID分區(qū)是將所有具有相同AuthorID的數(shù)據(jù)行劃分到同一個(gè)分區(qū).

(2) 排序.排序是通過(guò)排序子句(Order by)而實(shí)現(xiàn)的，即將每一個(gè)分區(qū)中的數(shù)據(jù)行按照指定的數(shù)據(jù)表字段進(jìn)行排序.

(3) 分幀.分幀建立在分區(qū)的基礎(chǔ)上，依賴于排序所確定的分區(qū)內(nèi)數(shù)據(jù)行之間的順序，具有行數(shù)(Rows)和數(shù)值范圍(Range)兩種限定模式，用于在數(shù)據(jù)分區(qū)內(nèi)部劃分局部的數(shù)據(jù)幀以供后續(xù)計(jì)算.

1.2 基于窗口函數(shù)的SQL查詢優(yōu)化

傳統(tǒng)的MySQL數(shù)據(jù)庫(kù)不支持窗口函數(shù)，因而需要將存儲(chǔ)學(xué)術(shù)大數(shù)據(jù)的數(shù)據(jù)庫(kù)遷移至支持SQL:2003標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)PostgreSQL 9.4[9]上.

本文以AceMap系統(tǒng)中常見(jiàn)的分析型SQL查詢(查詢作者論文的SCI引用數(shù))為例來(lái)驗(yàn)證基于窗口函數(shù)的查詢優(yōu)化方法.該實(shí)例查詢涉及了一個(gè)大型的數(shù)據(jù)表PaperAuthorAffiliations，以及存儲(chǔ)論文、作者和機(jī)構(gòu)的映射關(guān)系，包含 338 222 414 行數(shù)據(jù)記錄.傳統(tǒng)的SQL的查詢程序?yàn)閇4]

select count(*), sum(SCICitation) as sum

from PaperSciReferencesCount as tb1

inner join (select PaperID from PaperAuthor

Affiliations where AuthorID=@AuthorID) as tb2

on tb1.PaperReferenceID=tb2.PaperID

group by AuthorID

采用基于窗口函數(shù)的查詢優(yōu)化方法的SQL查詢程序?yàn)?/p>

select distinct count(*), sum(SCICitation)

over(partition by AuthorID) as sum

from PaperSciReferencesCount as tb1

inner join PaperAuthorAffiliations as tb2

on tb1.PaperReferenceID=tb2.PaperID

where AuthorID=@AuthorID

1.3 性能對(duì)比實(shí)驗(yàn)

采用一臺(tái)配置為兩個(gè)中央處理器(因特爾至強(qiáng)系列，型號(hào)E5-2630)、內(nèi)存128 GB的服務(wù)器對(duì)3個(gè)學(xué)術(shù)系統(tǒng)中的典型SQL查詢進(jìn)行優(yōu)化，并將查詢執(zhí)行時(shí)間作為性能評(píng)價(jià)指標(biāo).以原SQL查詢?cè)趥鹘y(tǒng)的MySQL數(shù)據(jù)庫(kù)上的查詢執(zhí)行時(shí)間作為基準(zhǔn)，對(duì)比采用基于窗口函數(shù)的查詢優(yōu)化方法的查詢執(zhí)行時(shí)間.表1列出了3個(gè)學(xué)術(shù)系統(tǒng)性能對(duì)比實(shí)驗(yàn)所涉及的SQL查詢的具體內(nèi)容，其查詢執(zhí)行時(shí)間的對(duì)比如圖2所示.可以看出，采用基于窗口函數(shù)的查詢優(yōu)化方法能夠在一定程度上提升查詢性能，使其查詢執(zhí)行時(shí)間減少 18.6%(SQL-3).

表1 窗口函數(shù)查詢優(yōu)化的SQL查詢列表

Tab.1 The SQL queries of window function query optimization

SQL查詢編號(hào)SQL查詢描述SQL-1統(tǒng)計(jì)某一學(xué)者所有論文的SCI引用次數(shù)SQL-2統(tǒng)計(jì)學(xué)術(shù)合作次數(shù),查找與某兩名學(xué)者合作次數(shù)最多的學(xué)者SQL-3查找引用了某一篇論文的所有論文,并輸出其中出現(xiàn)頻次最多的關(guān)鍵詞

圖2 窗口函數(shù)的查詢優(yōu)化方法的結(jié)果Fig.2 Contrast results of window function query optimization

2 基于SparkSQL的查詢優(yōu)化方法

與基于窗口函數(shù)的查詢優(yōu)化方法的單節(jié)點(diǎn)計(jì)算模式不同，基于SparkSQL計(jì)算引擎的查詢優(yōu)化旨在分布式計(jì)算的框架下，借助于其多節(jié)點(diǎn)和多核的優(yōu)勢(shì)進(jìn)行SQL查詢，適用于大規(guī)模海量數(shù)據(jù)的應(yīng)用場(chǎng)景.

2.1 Spark與SparkSQL

Spark[10]是類(lèi)似于Hadoop的分布式計(jì)算系統(tǒng) MapReduce 的計(jì)算引擎[11]，通過(guò)分布式計(jì)算框架對(duì)大規(guī)模數(shù)據(jù)進(jìn)行快速處理和計(jì)算.SparkSQL計(jì)算引擎在Spark的生態(tài)系統(tǒng)中作為SQL-on-Hadoop[11-12]系統(tǒng)存在，它是在分布式集群上進(jìn)行SQL查詢的技術(shù)平臺(tái).但是，SQL查詢不依賴于關(guān)系型引擎，它通過(guò)SparkSQL自帶的編譯器對(duì)SQL語(yǔ)言進(jìn)行解析和編譯并將其轉(zhuǎn)化為Spark作業(yè)，再通過(guò)Spark計(jì)算引擎在集群上實(shí)現(xiàn)的.

2.2 SparkSQL的文件系統(tǒng)和數(shù)據(jù)格式

SparkSQL計(jì)算引擎需要分布式存儲(chǔ)系統(tǒng)對(duì)其進(jìn)行支撐.本文選擇HDFS(Hadoop Distributed File System)[11]作為Spark的文件系統(tǒng).將數(shù)據(jù)由MySQL數(shù)據(jù)庫(kù)遷移到HDFS上，即SparkSQL采用此分布式文件系統(tǒng)存取數(shù)據(jù).

基于HDFS的文件系統(tǒng)中Spark支持多種數(shù)據(jù)格式，包括列式存儲(chǔ)(Parquet)[10]和文本等.依據(jù)Spark計(jì)算引擎的官方技術(shù)文檔[10]，SparkSQL在列式存儲(chǔ)中的查詢執(zhí)行速度(文本格式查詢執(zhí)行速度的約10倍)最快，而且平均可以節(jié)省約75%的存儲(chǔ)空間，因此，本文選擇列式存儲(chǔ)作為SparkSQL的數(shù)據(jù)格式.

2.3 基于SparkSQL的查詢優(yōu)化

采用SparkSQL自帶Catalyst優(yōu)化器[10]能夠?qū)崿F(xiàn)對(duì)SQL查詢的優(yōu)化，并最終生成能夠在Spark集群上執(zhí)行的作業(yè).

SparkSQL采用內(nèi)存列存儲(chǔ)優(yōu)化(In-Memory Columnar Storage)技術(shù)[13]，能夠查詢一些頻繁出現(xiàn)的聚合，將其中包含的數(shù)據(jù)列存儲(chǔ)到集群的同一個(gè)節(jié)點(diǎn)上，使其能夠快速地被讀入內(nèi)存，從而提高查詢速度.

參數(shù)優(yōu)化是充分發(fā)揮Spark集群計(jì)算優(yōu)勢(shì)以高效執(zhí)行作業(yè)的關(guān)鍵.一個(gè)作業(yè)在Spark集群中的執(zhí)行需要由位于Spark集群主節(jié)點(diǎn)中的Driver進(jìn)程與若干個(gè)從節(jié)點(diǎn)中的Executor進(jìn)程協(xié)同完成[10]，兩者之間的關(guān)系如圖3所示.其中，Driver負(fù)責(zé)集群資源的分配，Executor負(fù)責(zé)具體任務(wù)的執(zhí)行.

圖3 Driver進(jìn)程與Executor進(jìn)程的關(guān)系Fig.3 Relationship between driver and executor processes

本文對(duì)以下參數(shù)[10]進(jìn)行優(yōu)化：

(1) 參數(shù)SPARK_EXECUTOR_INSTANCES.表示Spark集群能夠同時(shí)啟動(dòng)的Executor實(shí)例個(gè)數(shù)的上限值，其對(duì)硬件資源的利用效率具有影響.結(jié)合實(shí)驗(yàn)條件，本文最終選擇其值為20，經(jīng)測(cè)試達(dá)到較優(yōu)的查詢性能.

(2) 參數(shù)SPARK_EXECUTOR_CORES.表示每個(gè)Executor能夠使用的中央處理器(CPU)核的數(shù)量，它影響Spark執(zhí)行任務(wù)時(shí)的并行度.本文將其值設(shè)置為10，相應(yīng)的能夠同時(shí)并行執(zhí)行的任務(wù)數(shù)為20×10=200.

(3) 參數(shù)SPARK_EXECUTOR_MEMORY.表示分配給每一個(gè)Executor的內(nèi)存數(shù)量，同一個(gè) Executor 的所有CPU核共用.經(jīng)實(shí)際測(cè)試，將其值設(shè)置為10 GB時(shí)的查詢性能較優(yōu).

2.4 性能對(duì)比實(shí)驗(yàn)

實(shí)驗(yàn)在一個(gè)含4個(gè)節(jié)點(diǎn)的Spark集群上進(jìn)行，包括一個(gè)Master主節(jié)點(diǎn)，配置一個(gè)中央處理器(因特爾酷睿系列，型號(hào)i5-4590，主頻率 3.3 GHz)，內(nèi)存12 GB；3個(gè)Slave從節(jié)點(diǎn)，配置均為兩個(gè)中央處理器(因特爾至強(qiáng)系列，型號(hào)E5-2630)，內(nèi)存128 GB.

本文對(duì)3個(gè)學(xué)術(shù)系統(tǒng)中涉及大規(guī)模數(shù)據(jù)的復(fù)雜SQL查詢進(jìn)行優(yōu)化，并以查詢執(zhí)行時(shí)間作為性能評(píng)價(jià)指標(biāo).以在傳統(tǒng)的MySQL上的SQL查詢執(zhí)行時(shí)間為基準(zhǔn)，對(duì)比通過(guò)Spark集群優(yōu)化后的查詢執(zhí)行時(shí)間.表2列出了實(shí)驗(yàn)所涉及的SQL查詢的具體內(nèi)容，其查詢執(zhí)行時(shí)間如圖4所示.可以看出，基于SparkSQL的查詢優(yōu)化方法能夠大幅提升查詢性能，使其查詢執(zhí)行時(shí)間降低 93.9%(SQL-2)，相當(dāng)于查詢速度加快16倍.

表2 SparkSQL查詢優(yōu)化的SQL查詢列表Tab.2 The SQL queries of SparkSQL query optimization

圖4 基于SparkSQL查詢優(yōu)化方法的查詢執(zhí)行時(shí)間Fig.4 Contrast experimental results of SparkSQL query optimization

3 結(jié)語(yǔ)

本文針對(duì)傳統(tǒng)的關(guān)系型數(shù)據(jù)庫(kù)MySQL的查詢速度較低的問(wèn)題，利用基于窗口函數(shù)的查詢優(yōu)化方法和基于SparkSQL的查詢優(yōu)化技術(shù)分別對(duì)各自適宜的應(yīng)用場(chǎng)景進(jìn)行優(yōu)化.結(jié)果表明：在MySQL的框架下，對(duì)于分析型SQL的查詢，利用基于窗口函數(shù)的查詢優(yōu)化方法能夠在一定程度上提高查詢速度；在分布式集群計(jì)算的框架下，針對(duì)海量數(shù)據(jù)和更為復(fù)雜的查詢操作，采用基于SparkSQL的查詢優(yōu)化方法能夠大幅提高系統(tǒng)的查詢性能.