基于營銷資源系統(tǒng)云化后的數(shù)據(jù)庫查詢性能優(yōu)化探究

黎剛

關(guān)鍵詞 數(shù)據(jù)庫 優(yōu)化 索引 分頁

1研究背景

在營銷資源系統(tǒng)云化后的情況下，分布式MySQL數(shù)據(jù)庫具備讀寫分離功能，其可以承載高可靠的數(shù)據(jù)集，即“ 一主兩備” 數(shù)據(jù)集。系統(tǒng)的開發(fā)框架是“SpingBoot+Ibatis+MySQL”。系統(tǒng)云化初期，在數(shù)據(jù)查詢方面出現(xiàn)了很多問題，最為典型的問題就是頁面查詢緩慢、客戶感知極差[1] 。通過對(duì)全量的慢SQL進(jìn)行分析可以發(fā)現(xiàn)，出現(xiàn)上述問題的原因主要有兩個(gè)：一是沒有建立合理的索引;二是很多需要查詢的語句沒有走上索引。為了解決系統(tǒng)查詢緩慢的問題，本文對(duì)全量的慢SQL 語句進(jìn)行了分析和優(yōu)化，最終使系統(tǒng)的查詢語句速度維持都在1 秒以內(nèi)，且CPU 和磁盤IO 的占比始終處于合理水平[2] 。

2優(yōu)化技術(shù)

測(cè)試的硬件環(huán)境和軟件環(huán)境為：邏輯CPU 核數(shù)為64 個(gè); 內(nèi)存總量為377GB;交換空間為31GB;單個(gè)數(shù)據(jù)集的存儲(chǔ)量為1.5TB;操作系統(tǒng)為CentOS?7?x86_64。

測(cè)試結(jié)構(gòu)如表1。整張表的數(shù)據(jù)量為：22987997。

為了保持文章簡潔，我們確定查詢條件如表2。為了使后文不再重復(fù)，統(tǒng)一用“查詢條件”代替表2 的查詢條件。

3營銷資源系統(tǒng)云化后索引的使用方法

索引就像是一幢大樓的房間編號(hào)。如果需要尋找某個(gè)房間的人，首先應(yīng)該查找房間編號(hào)，找到該房間后，就能快速找到對(duì)應(yīng)的人[3] 。

營銷資源系統(tǒng)云化后，為了提供高效的查詢性能，所有的庫表需要建立合理的索引，因此全量的查詢語句都需要走上索引。

本文采用EXPLAIN 對(duì)查詢語句進(jìn)行分析，對(duì)庫表為MKT_RES_INST。圖1 為重點(diǎn)關(guān)注數(shù)據(jù)。

根據(jù)MKT_RES_INST 表可知，在分布式數(shù)據(jù)庫的環(huán)境中，單個(gè)分片承接的上限為1000 萬條左右。根據(jù)業(yè)務(wù)量進(jìn)行綜合考慮，本文設(shè)計(jì)了16 個(gè)分片，數(shù)據(jù)量為22987997。

通過分析可以發(fā)現(xiàn)，一個(gè)查詢語句是否走上索引在消耗的時(shí)間方面有較大差異，如表3 所示。

4使用limit 對(duì)分頁進(jìn)行優(yōu)化

在本文模擬的云化的場(chǎng)景中，分頁業(yè)務(wù)場(chǎng)景需要返回某幾行數(shù)據(jù)，limit 則可以指定返回的記錄數(shù)。當(dāng)數(shù)據(jù)量達(dá)到2000 萬條以上時(shí)，如果僅需獲取部分?jǐn)?shù)據(jù)，一定要注意規(guī)避全表掃描的問題，否則查詢效率會(huì)很低[4] 。

使用合理的分頁方式可以提高分頁的效率，切忌利用limit offset 和row_count 進(jìn)行分頁。以語句select? from MKT_RES_INST limit 100000，1;為例，MySQL首先會(huì)掃描100000 行，再讀取一行，可見效率之差。而優(yōu)化思路則是：砍掉跳頁功能（直接跳到第X 頁，最后一頁等），每次讀取根據(jù)上一頁的最大ID 做范圍查詢。

優(yōu)化舉例：selectMKT_RES_INST_ID， MKT_RES_INST_NBR from MKT_RES_INST limit 866644， 10。用該語句做分頁查詢可知，當(dāng)數(shù)據(jù)量達(dá)到2000 萬時(shí)，需要消耗很多時(shí)間。用本文的測(cè)試數(shù)據(jù)對(duì)其進(jìn)行測(cè)試驗(yàn)證如表4 所示。

5營銷資源系統(tǒng)云化項(xiàng)目全量慢SQL優(yōu)化

營銷資源系統(tǒng)云化項(xiàng)目大約經(jīng)歷了一年半的上線過程。系統(tǒng)云化初期，在數(shù)據(jù)查詢方面出現(xiàn)了很多問題，如頁面查詢緩慢以及查詢時(shí)間超過1 秒的全量的慢SQL 超過100 條。通過對(duì)全量的慢SQL 進(jìn)行分析可以發(fā)現(xiàn)，其原因主要有兩個(gè)：一是沒有建立合理的索引;二是查詢的很多語句沒有走上索引。為了解決該問題，本文對(duì)系統(tǒng)進(jìn)行了全量的慢SQL 語句分析和優(yōu)化[5] 。

5.1查詢語句中禁止使用“?”

在分布式數(shù)據(jù)庫的DML 審計(jì)中，要求查詢語句不能跨片查詢。查詢語句在不跨片的前提下，會(huì)將語句中的“?” 按照表中數(shù)據(jù)字典對(duì)應(yīng)的全部列名進(jìn)行依次轉(zhuǎn)換，該過程耗時(shí)較長。優(yōu)化辦法是：僅列出所需的字段名，不查詢不需要展示的字段，并且項(xiàng)目中的全部語句禁止使用“?”[6] 。

在查詢語句中采用“?”，會(huì)消耗數(shù)據(jù)字典轉(zhuǎn)換為全量列名的時(shí)間，同時(shí)會(huì)增加CPU、磁盤IO、數(shù)據(jù)網(wǎng)絡(luò)傳輸?shù)臅r(shí)間，所以盡量不要使用“?”。字段提取按照“需多少、提多少”的原則。若用“?”，驗(yàn)證數(shù)據(jù)如表6 所示。

明確提取的字段去掉“?”后，取1000 條數(shù)據(jù)的結(jié)果如表7 所示。

5.2若限制條件中其他字段沒有索引，禁止用or

在對(duì)or 的兩邊進(jìn)行查詢時(shí)，若存在一個(gè)不是索引字段，而其他條件有索引字段，最后的查詢結(jié)果是：走不上索引。本文對(duì)該場(chǎng)景進(jìn)行了優(yōu)化，采用union all替代or，測(cè)試證明查詢的效果較好（見表8 和表9）。

原因分析： 由于“ or CREATE _ STAFF =500905505”沒有走上索引，造成整個(gè)查詢都沒有走上索引，查詢耗時(shí)較長。

原因分析：查詢語句的前半部分走上了索引，后半部分沒有走上索引，但整體的查詢性能得到提升。

5.3避免在where 中對(duì)字段進(jìn)行null 值判斷或者對(duì)字段進(jìn)行函數(shù)操作

5.3.1禁止在where 中使用null 值判斷

測(cè)試表明，如果where 中有null 的判斷，查詢不能走上索引，查詢是全表掃描的。本文利用explain 分析的結(jié)果如圖2 所示。

5.3.2 避免在where 中對(duì)字段進(jìn)行函數(shù)操作

如果在索引列上使用了函數(shù)運(yùn)算會(huì)導(dǎo)致索引失效，此時(shí)可將計(jì)算放到索引列外的表達(dá)式上。比如，在表10 中，TIMESTAMPDIFF（DAY， a. create_date，now（）） <= 1。

原因分析：在索引列上使用了函數(shù)運(yùn)算，導(dǎo)致索引失效。

優(yōu)化為：create_date> = DATE_FORMAT（DATE_SUB（NOW（ ），INTERVAL 1 DAY），'%Y?%m?%d %H：%i：%S'） ，查詢效率明顯提升（表11）。

原因分析：將計(jì)算放到索引列外的表達(dá)式上，確保查詢語句走上了索引，查詢性能才有大幅度的提升。

5.4聯(lián)合索引遵循“最左前綴”法則

本文使用的聯(lián)合索引為idx_STATUS_CD_LAN_ACPT，其基于STATUS_CD，LAN_ID，ACPT_STATE 三個(gè)字段而創(chuàng)建，如索引idx_STATUS_CD_LAN_ACPT（STATUS_CD，LAN_ID，ACPT_STATE），遵循“最左前綴”法則。

按最左邊第一個(gè)字段的查詢條件，符合“最左前綴”法則，是可以走上索引的（表12）。

耗時(shí)：21ms。

按最左邊前兩個(gè)字段的查詢條件，也是可以走上索引的（表13）。

按右邊第一個(gè)字段的查詢條件，無法走上索引（表14）。

5.5禁止使用%前綴進(jìn)行模糊查詢

使用LIKE“% REMARK”或者LIKE“% REMARK%”等查詢語句不能走上索引，因?yàn)榇藭r(shí)查詢語句會(huì)對(duì)全表進(jìn)行掃描。但是，使用LIKE “REMARK %”查詢語句可以走上索引。

使用LIKE“%name%”查詢語句，不能走上索引（表15）。

測(cè)試數(shù)據(jù)表明： LIKE 語句一定要使用LIKE“REMARK %”等后綴進(jìn)行模糊查詢，確保走上索引。

6結(jié)論