基于車輛的關(guān)系型數(shù)據(jù)庫(kù)性能提升

劉 路，孫代青，王 瑞，王光福，張文成

（陜西汽車集團(tuán)股份有限公司技術(shù)中心，陜西 西安 710200）

現(xiàn)在主流的數(shù)據(jù)庫(kù)包括 MySQL、Oracle、 PostgreSQL等，對(duì)于這些數(shù)據(jù)庫(kù)各有利弊。MySql有很靈活的口令系統(tǒng)，但是在數(shù)據(jù)庫(kù)壓力達(dá)到極限臨界點(diǎn)的時(shí)候，服務(wù)器會(huì)出現(xiàn)丟失數(shù)據(jù)的情況。Oracle和PostgreSQL在很多方面存在相似之處，Oracle能在所有主流平臺(tái)上運(yùn)行，但是安裝環(huán)境及安裝包都占用很大的空間，且操作比較復(fù)雜。PostgreSQL運(yùn)行采用的是搶占資源方式，可能會(huì)堵塞其他進(jìn)程，但是可以進(jìn)行優(yōu)先級(jí)設(shè)置，可規(guī)避某些阻塞進(jìn)程的發(fā)生。PostgreSQL是一種關(guān)系型數(shù)據(jù)庫(kù)，客戶和服務(wù)器軟件既可以運(yùn)行在同一臺(tái)機(jī)器也可運(yùn)行在不同的機(jī)器上[1]，其性能穩(wěn)定，即使數(shù)據(jù)庫(kù)壓力達(dá)到極限值臨界點(diǎn)，它也可以保持穩(wěn)定的狀態(tài)，并且在內(nèi)存很小的機(jī)器上依舊能夠運(yùn)行。PostgreSQL支持更多的互聯(lián)網(wǎng)特征的功能，比如擁有更多的數(shù)據(jù)類型，支持更多的正則表達(dá)式[2]。本文描述了運(yùn)用PostgreSQL對(duì)某車的行車數(shù)據(jù)進(jìn)行表的結(jié)構(gòu)設(shè)計(jì)和數(shù)據(jù)庫(kù)的優(yōu)化方法，極大程度地提升數(shù)據(jù)庫(kù)的響應(yīng)效率，提升了用戶的體驗(yàn)度。

1 數(shù)據(jù)庫(kù)設(shè)計(jì)

某車數(shù)據(jù)上報(bào)采用狀態(tài)量及模擬量的方式。比如四狀態(tài)量：當(dāng)某一秒內(nèi)有四狀態(tài)數(shù)據(jù)上報(bào)時(shí)，無論是一條數(shù)據(jù)還是多條數(shù)據(jù)，都可以用一個(gè)四狀態(tài)枚舉量數(shù)據(jù)包進(jìn)行傳輸，由于它們都是在同一秒內(nèi)上傳的，因此，采樣時(shí)間的天、時(shí)、分、秒都是一樣的，把不同信號(hào)以value_id或者status_id（數(shù)據(jù)類型編號(hào)）進(jìn)行區(qū)別。gps（車輛定位信號(hào)）和 diagnosis（診斷信號(hào)）數(shù)據(jù)上報(bào)方式與狀態(tài)量及模擬量數(shù)據(jù)的上報(bào)方式不同，所以單獨(dú)設(shè)計(jì)表的字段。

同一輛車按照數(shù)據(jù)上報(bào)方式的狀態(tài)量各自建表：表名采用狀態(tài)量+VIN（車輛識(shí)別代號(hào)），表1是型號(hào)為d123456的某車車輛的數(shù)據(jù)庫(kù)表名。

表1 d123456車輛數(shù)據(jù)庫(kù)設(shè)計(jì)表名

2 數(shù)據(jù)庫(kù)性能提升

數(shù)據(jù)庫(kù)優(yōu)化可以從硬件優(yōu)化、存儲(chǔ)系統(tǒng)、存儲(chǔ)結(jié)構(gòu)、SQL四個(gè)維度著手。層與層之間是相互關(guān)聯(lián)的，硬件優(yōu)化和存儲(chǔ)系統(tǒng)的優(yōu)化成本較高。數(shù)據(jù)庫(kù)中的數(shù)據(jù)最終是落在物理磁盤上的，對(duì)物理存儲(chǔ)結(jié)構(gòu)的優(yōu)化雖然不能減少對(duì)物理存儲(chǔ)的訪問次數(shù)，但是可以使讀寫并行化發(fā)生，減少對(duì)磁盤讀寫的競(jìng)爭(zhēng)壓力，減少不必要的物理存儲(chǔ)系統(tǒng)的擴(kuò)充，所以對(duì)于優(yōu)化效果來說，存儲(chǔ)結(jié)構(gòu)和SQL會(huì)更佳。本文重點(diǎn)就存儲(chǔ)結(jié)構(gòu)和SQL優(yōu)化進(jìn)行數(shù)據(jù)庫(kù)效率提升的分析。

2.1 并行查詢

PostgreSQL的并行查詢包含三個(gè)組件：leader、gather、workers。并行化關(guān)閉的時(shí)候，進(jìn)程的工作流程是由進(jìn)程本身處理所有的數(shù)據(jù)；并行化開啟后，系統(tǒng)本身會(huì)在并行化節(jié)點(diǎn)上增加一個(gè)gather節(jié)點(diǎn)和多個(gè)workers線程，gather節(jié)點(diǎn)類似于查詢樹的子節(jié)點(diǎn)，workers線程的數(shù)量是由PostgreSQL的配置參數(shù)決定，workers線程之間相互配合，完成查詢?nèi)蝿?wù)后將結(jié)果反饋給 leader進(jìn)程。并行查詢的原理如圖1所示。

圖1 并行查詢?cè)韴D

workers的數(shù)量可以動(dòng)態(tài)調(diào)整，max_parallel_workers_per_gather參數(shù)定義了 workers的最小數(shù)量，查詢執(zhí)行器從池子中獲取 max_parallel_workers的數(shù)值，接著獲取 max_worker_processes的數(shù)值，該參數(shù)定義了workers的上限。查詢過程中，如果分配的worker進(jìn)程啟動(dòng)失敗，則會(huì)切換成單線程執(zhí)行查詢?nèi)蝿?wù)，查詢執(zhí)行器會(huì)依據(jù)表的大小，適時(shí)地調(diào)整 worker進(jìn)程的數(shù)量，而調(diào)整worker的數(shù)量又與參數(shù) min_parallel_table_scan_size、min_parallel_index_scan_size有關(guān)。根據(jù)表大小計(jì)算并行度（parallelism）的公式如下：

本次測(cè)試min_parallel_table_scan_size設(shè)置的數(shù)值為8 MB，min_parallel_index_scan_size設(shè)置的數(shù)值為 512 KB，根據(jù)式（1）計(jì)算出來最合適的并行度數(shù)值為4。

未開啟并行模式，采用單線程查詢模式，將dmax_parallel_workers_per_gather參數(shù)設(shè)置的數(shù)值0，查詢d123456車輛發(fā)動(dòng)機(jī)轉(zhuǎn)速一年的數(shù)據(jù)，PostgreSQL執(zhí)行過程如圖2所示。

圖2 測(cè)試結(jié)果

開啟并行查詢模式，將dmax_parallel_workers_per_gather參數(shù)設(shè)置的數(shù)值4，查詢d123456車輛發(fā)動(dòng)機(jī)轉(zhuǎn)速一年的數(shù)據(jù)，PostgreSQL執(zhí)行過程如圖3所示。

圖3 測(cè)試結(jié)果

結(jié)論：通過數(shù)據(jù)表大小計(jì)算得到合適的并行度，開啟并行化之前數(shù)據(jù)訪問的時(shí)間是18 730.130 ms，開啟并行度后數(shù)據(jù)訪問的時(shí)間是5 748.069 ms，數(shù)據(jù)請(qǐng)求的效率提升約69%。

2.2 數(shù)據(jù)表索引

索引是對(duì)指定數(shù)據(jù)表中的某種字段進(jìn)行特定排序，排序后的數(shù)據(jù)表就會(huì)形成存儲(chǔ)記憶，可以迅速地提取所需數(shù)據(jù)，減少人工等待時(shí)間[3]。數(shù)據(jù)庫(kù)的索引字段包含很多種方式，包含普通索引和唯一索引。普通索引常見的有 where條件查詢、order by排序條件查詢。唯一索引用關(guān)鍵字UNIQUE把某數(shù)據(jù)列定義成唯一項(xiàng)[4]。事實(shí)上，創(chuàng)建唯一索引不單單是為了提升數(shù)據(jù)庫(kù)訪問速度，而且能夠避免重復(fù)數(shù)據(jù)出現(xiàn)，保證數(shù)據(jù)的有效性。PostgreSQL索引類型常用的索引hash、btree、gin。Btree索引適用的范圍很大，它支持所有類型的數(shù)據(jù)查詢。B樹是平衡且多分支類型的，第一頁(yè)是索引根，內(nèi)部節(jié)點(diǎn)位于根下方，最低行是葉子頁(yè)面，每個(gè)頁(yè)面與根部都由相同數(shù)量的內(nèi)部頁(yè)面分隔。Btree工作原理如圖4所示。

圖4 Btree工作原理圖

對(duì)某車的車輛數(shù)據(jù)表添加btree類型的復(fù)合索引，該索引字段的任何子集均可用于查詢條件。使用如下代碼對(duì)車輛行車數(shù)據(jù)表添加索引：

query4:= 'CREATE INDEX "' || name || '_brin"ON "public"."'|| name ||'" USING brin("sample_time","value_id")';

raise notice 'query4 is: %', query4;

execute query4;

根據(jù)上文對(duì)數(shù)據(jù)庫(kù)添加btree類型的復(fù)合索引的優(yōu)化策略，系統(tǒng)對(duì)優(yōu)化前后的數(shù)據(jù)庫(kù)進(jìn)行請(qǐng)求的性能測(cè)試，測(cè)試條件為WIN7、RAM 16 GB、CPU 2.7 GHZ，對(duì)車輛里程數(shù)據(jù)進(jìn)行查詢，測(cè)試結(jié)果如圖5所示。

圖5 索引測(cè)試結(jié)果

結(jié)論：從上述測(cè)試對(duì)比可看出，相對(duì)比優(yōu)化之前的請(qǐng)求數(shù)據(jù)時(shí)間，添加btree復(fù)合索引后，數(shù)據(jù)請(qǐng)求的效率提升約30%。

2.3 數(shù)據(jù)庫(kù)分區(qū)

數(shù)據(jù)庫(kù)分區(qū)是指將龐大的數(shù)據(jù)分段劃分多個(gè)位置存放，分區(qū)后數(shù)據(jù)表仍然是一張表，但數(shù)據(jù)根據(jù)設(shè)置的分區(qū)條件存貯在多個(gè)分區(qū)塊中。分區(qū)可分為水平分區(qū)和垂直分區(qū)兩種方式。垂直分區(qū)是對(duì)表格中存在的列進(jìn)行劃分，對(duì)表格的寬度進(jìn)行縮減。水平分區(qū)指的是對(duì)數(shù)據(jù)表內(nèi)容橫向劃分，每張分區(qū)表中的初始結(jié)構(gòu)相同，本文采用的是水平分區(qū)，由于每種行車數(shù)據(jù)都有獨(dú)特的 value_id或者status_id（數(shù)據(jù)類型編號(hào)），提取數(shù)據(jù)一定會(huì)定義數(shù)據(jù)類型編號(hào)，所以對(duì)某車設(shè)置的分區(qū)條件為value_id或者status_id（數(shù)據(jù)類型編號(hào)）。

分區(qū)之前先使用 CREATE TABLE構(gòu)建表的結(jié)構(gòu)，使用PARTITION BY LIST（）語(yǔ)句添加分區(qū)，括號(hào)中填入設(shè)計(jì)的分區(qū)字段。在分區(qū)測(cè)試中發(fā)現(xiàn)，終端上報(bào)的車輛行駛數(shù)據(jù)會(huì)存在一些測(cè)試或者無意義數(shù)據(jù)，這些數(shù)據(jù)未定義 value_id或者status_id（數(shù)據(jù)類型編號(hào)），如果對(duì)這些測(cè)試數(shù)據(jù)不設(shè)置一個(gè)固定存放區(qū)，則發(fā)現(xiàn)導(dǎo)入的車輛數(shù)據(jù)會(huì)出現(xiàn)丟失或者時(shí)間格式的跳變。PostgreSQL新版本支持創(chuàng)建默認(rèn)分區(qū)即為default區(qū)，default區(qū)可以存放未匹配到數(shù)據(jù)類型編號(hào)的車輛數(shù)據(jù)。定義分區(qū)表需要對(duì)每張分區(qū)表分配特定的表格名稱，這里采用的命名格式是表名+車型號(hào)+value_id或者status_id（數(shù)據(jù)類型編號(hào)）。使用如下代碼對(duì)車輛行車數(shù)據(jù)表添加分區(qū)：

query2:='CREATE TABLE'|| name||' ("value_id" int4 NOT NULL, "value" float8 NOT NULL,"sample_time" TIMESTAMP (6) NOT NULL, "flag"int4 NOT NULL ) PARTITION BY list ( value_id )';

query_default:='CREATE TABLE'||name||'_def ault PARTITION of ' ||name || ' DEFAULT';

raise notice 'query2 is: %', query2;

execute query2;

raise notice 'query_default is:%', query_ default;

execute query_default;

分區(qū)劃分完成后，每張車輛數(shù)據(jù)表中的value_id或者 status_id（數(shù)據(jù)類型編號(hào)）都存放在定義好的分區(qū)中，查詢車輛的車速數(shù)據(jù)只需遍歷車速的分區(qū)表，無需對(duì)所有表格進(jìn)行遍歷。使用select*from pg_tables where tablename LIKE '%d123456%'，就可以查看分區(qū)表的建立是否完成。查看d123456車輛分區(qū)后的部分結(jié)果如表2所示。

表2 d123456分區(qū)后的部分結(jié)果

車輛數(shù)據(jù)分區(qū)后，不僅能提升遍歷表格的效率，還能減少后期對(duì)數(shù)據(jù)庫(kù)的維護(hù)。車輛分區(qū)表創(chuàng)建后，車輛數(shù)據(jù)發(fā)送頻率是1 s，數(shù)據(jù)量非常龐大，如果某張分區(qū)表發(fā)生故障或者某種信號(hào)的數(shù)據(jù)類型編號(hào)作出調(diào)整，就可以單獨(dú)修改特定數(shù)據(jù)類型編號(hào)的數(shù)據(jù)表，而且不會(huì)影響其他車輛數(shù)據(jù)的正常使用，降低了數(shù)據(jù)庫(kù)的運(yùn)維難度和工作量。

數(shù)據(jù)庫(kù)添加分區(qū)的優(yōu)化策略，對(duì)優(yōu)化前后的數(shù)據(jù)庫(kù)進(jìn)行請(qǐng)求的性能測(cè)試，測(cè)試條件為WIN7、RAM 16GB、CPU 2.7GHZ，對(duì)車輛水溫?cái)?shù)據(jù)進(jìn)行查詢，測(cè)試結(jié)果如圖6所示。

圖6 分區(qū)測(cè)試結(jié)果

結(jié)論：從上述測(cè)試對(duì)比可看出，相對(duì)比優(yōu)化之前的請(qǐng)求數(shù)據(jù)時(shí)間，對(duì)數(shù)據(jù)類型編號(hào)添加水平分區(qū)后，數(shù)據(jù)請(qǐng)求的效率提升約95%。

2.4 應(yīng)用程序接口設(shè)計(jì)優(yōu)化

應(yīng)用程序在訪問數(shù)據(jù)庫(kù)時(shí)，需要避免重復(fù)連接數(shù)據(jù)庫(kù)的行為，這將嚴(yán)重影響程序的響應(yīng)速度[5]。普通的應(yīng)用程序接口訪問數(shù)據(jù)庫(kù)，需要頻繁地創(chuàng)建連接和關(guān)閉連接，產(chǎn)生多次網(wǎng)絡(luò)交互，影響服務(wù)器性能。本文運(yùn)用數(shù)據(jù)庫(kù)連接池的方式訪問數(shù)據(jù)，連接池的基本思想是將數(shù)據(jù)庫(kù)連接作為對(duì)象存儲(chǔ)在內(nèi)存中，當(dāng)用戶需要訪問數(shù)據(jù)庫(kù)時(shí)，從連接池中取出一個(gè)空閑且已建立的連接對(duì)象，不需要建立新的連接機(jī)制。當(dāng)用戶使用完數(shù)據(jù)庫(kù)連接后，將該連接放回池中，供下一個(gè)連接請(qǐng)求使用。數(shù)據(jù)庫(kù)連接池的工作流程如圖7所示。

圖7 數(shù)據(jù)庫(kù)連接池流程圖

使用如下代碼對(duì)PostgreSQL進(jìn)行數(shù)據(jù)池接口設(shè)計(jì)：

public postgresUtils(){

DataSource.setUrl(url);

DataSource.setDriverClassName(driver);

DataSource.setUsername(username);

DataSource.setPassword(password);

DataSource.setInitialSize(10);//初始化時(shí)創(chuàng)建鏈接個(gè)數(shù)

DataSource.setMaxTotal(50);//設(shè)置最大連接數(shù)

DataSource.setMaxIdle(5);//這只最大的空閑連接數(shù)

DataSource.setMinIdle(1);//設(shè)置最小空閑連接數(shù)字

}

總結(jié)：數(shù)據(jù)庫(kù)連接池允許應(yīng)用程序復(fù)用資源，統(tǒng)一連接管理，避免了數(shù)據(jù)庫(kù)連接泄露和多個(gè)線程同時(shí)使用同一個(gè)連接。

3 結(jié)論

為了滿足車輛數(shù)據(jù)庫(kù)系統(tǒng)的大數(shù)據(jù)處理能力和混合負(fù)載能力的更高要求，本文提出和設(shè)計(jì)了提升車輛數(shù)據(jù)庫(kù)性能的方法，融合了并行系統(tǒng)查詢優(yōu)化、數(shù)據(jù)表索引、數(shù)據(jù)表分區(qū)、應(yīng)用程序連接池等技術(shù)。通過測(cè)試驗(yàn)證，與傳統(tǒng)的單線程數(shù)據(jù)庫(kù)相比，極大地提升了數(shù)據(jù)庫(kù)性能。