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

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

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

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

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

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

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

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

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

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

2.1 并行查詢

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

圖1 并行查詢原理圖

workers的數(shù)量可以動態(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)程啟動失敗，則會切換成單線程執(zhí)行查詢?nèi)蝿?wù)，查詢執(zhí)行器會依據(jù)表的大小，適時地調(diào)整 worker進(jìn)程的數(shù)量，而調(diào)整worker的數(shù)量又與參數(shù) min_parallel_table_scan_size、min_parallel_index_scan_size有關(guān)。根據(jù)表大小計算并行度（parallelism）的公式如下：

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

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

圖2 測試結(jié)果

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

圖3 測試結(jié)果

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

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

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

圖4 Btree工作原理圖

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

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

raise notice 'query4 is: %', query4;

execute query4;

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

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

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

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

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

分區(qū)之前先使用 CREATE TABLE構(gòu)建表的結(jié)構(gòu)，使用PARTITION BY LIST（）語句添加分區(qū)，括號中填入設(shè)計的分區(qū)字段。在分區(qū)測試中發(fā)現(xiàn)，終端上報的車輛行駛數(shù)據(jù)會存在一些測試或者無意義數(shù)據(jù)，這些數(shù)據(jù)未定義 value_id或者status_id（數(shù)據(jù)類型編號），如果對這些測試數(shù)據(jù)不設(shè)置一個固定存放區(qū)，則發(fā)現(xiàn)導(dǎo)入的車輛數(shù)據(jù)會出現(xiàn)丟失或者時間格式的跳變。PostgreSQL新版本支持創(chuàng)建默認(rèn)分區(qū)即為default區(qū)，default區(qū)可以存放未匹配到數(shù)據(jù)類型編號的車輛數(shù)據(jù)。定義分區(qū)表需要對每張分區(qū)表分配特定的表格名稱，這里采用的命名格式是表名+車型號+value_id或者status_id（數(shù)據(jù)類型編號）。使用如下代碼對車輛行車數(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ù)類型編號）都存放在定義好的分區(qū)中，查詢車輛的車速數(shù)據(jù)只需遍歷車速的分區(qū)表，無需對所有表格進(jìn)行遍歷。使用select*from pg_tables where tablename LIKE '%d123456%'，就可以查看分區(qū)表的建立是否完成。查看d123456車輛分區(qū)后的部分結(jié)果如表2所示。

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

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

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

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

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

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

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

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

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

public postgresUtils(){

DataSource.setUrl(url);

DataSource.setDriverClassName(driver);

DataSource.setUsername(username);

DataSource.setPassword(password);

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

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

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

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

}

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

3 結(jié)論

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