寫在最前

隨著互聯(lián)網(wǎng)日漸發(fā)展击费，開發(fā)人員需要處理的數(shù)據(jù)量愈來愈大，一些不起眼的小問題可能就會引發(fā)燎原之火桦他，而其中一個例子就是海量數(shù)據(jù)的檢索蔫巩。

由于歷史原因及業(yè)務(wù)因素，某些數(shù)據(jù)我們不能轉(zhuǎn)移到非關(guān)系型數(shù)據(jù)庫如MongoDB上快压，這時我們就需要考慮如何在舊有的關(guān)系型數(shù)據(jù)庫上進(jìn)行快速檢索圆仔。

溫故知新

要調(diào)優(yōu)關(guān)系型數(shù)據(jù)庫上的檢索性能，我們首先得了解蔫劣，關(guān)系型數(shù)據(jù)庫上檢索的原理坪郭。

執(zhí)行計劃

我們先看看維基百科怎么描述一句Select查詢的語句的執(zhí)行：

As SQL is a declarative programming language, SELECT queries specify a result set, but do not specify how to calculate it. The database translates the query into a "query plan" which may vary between executions, database versions and database software. This functionality is called the "query optimizer" as it is responsible for finding the best possible execution plan for the query, within applicable constraints.【1】

這里說出了一個重要的信息，每次檢索語句執(zhí)行拦宣，數(shù)據(jù)庫系統(tǒng)都會根據(jù)自身的情況執(zhí)行“查詢優(yōu)化”截粗，最后生成一個“執(zhí)行計劃”，這個執(zhí)行計劃與我們的SQL定義可能不完全一致鸵隧，比如查詢條件運行的先后順序，函數(shù)的執(zhí)行順序等等意推。

討論對象

市面上流行的關(guān)系型數(shù)據(jù)庫一般有Oracle豆瘫，SQL Server和MySQL。Oracle和SQL Server是典型的商業(yè)軟件菊值，他的license通常都非常昂貴外驱，但是方案非常成熟可靠育灸，是過往許多大型企業(yè)、政務(wù)系統(tǒng)的優(yōu)先選擇昵宇。而MySQL憑借其免費且開源的優(yōu)勢磅崭，在互聯(lián)網(wǎng)企業(yè)創(chuàng)業(yè)潮中，被廣泛應(yīng)用瓦哎。

由于執(zhí)行計劃針對不同的數(shù)據(jù)庫系統(tǒng)有不同的表現(xiàn)砸喻，今天我們就限定討論的對象為MySQL這個被互聯(lián)網(wǎng)企業(yè)廣泛應(yīng)用的關(guān)系型數(shù)據(jù)庫。

獲取執(zhí)行計劃

如前所述蒋譬，每一句select語句都會被轉(zhuǎn)換成執(zhí)行計劃割岛，所以要提升select語句的性能，我們首先要知道如何獲取執(zhí)行計劃犯助，才能更好的分析癣漆。

在MySQL中，我們使用explain關(guān)鍵字獲取執(zhí)行計劃剂买。

The EXPLAIN statement provides information about how MySQL executes statements:

• EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

• When EXPLAIN is used with an explainable statement, MySQL displays information from the optimizer about the statement execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. For information about using EXPLAIN to obtain execution plan information.

• When EXPLAIN is used with FOR CONNECTION connection_id rather than an explainable statement, it displays the execution plan for the statement executing in the named connection.

• For SELECT statements, EXPLAIN produces additional execution plan information.

• EXPLAIN is useful for examining queries involving partitioned tables.

• The FORMAT option can be used to select the output format. TRADITIONAL presents the output in tabular format. This is the default if no FORMAT option is present. JSONformat displays the information in JSON format.【2】

數(shù)據(jù)庫索引

一般在檢索較慢的情況下惠爽，我們能立刻想到的就是是否有使用索引，連MySQL官方文檔也將這一條通用優(yōu)化方案記錄在案：

The best way to improve the performance of SELECT operations is to create indexes on one or more of the columns that are tested in the query. The index entries act like pointers to the table rows, allowing the query to quickly determine which rows match a condition in the WHERE clause, and retrieve the other column values for those rows. All MySQL data types can be indexed.【3】

索引能提升檢索的速度瞬哼，主要原因是索引的數(shù)據(jù)結(jié)構(gòu)疆股，不同類型的索引，效果也不一樣倒槐。那么讓我們再來溫習(xí)一下索引的類型旬痹。

主鍵索引

顧名思義，主鍵索引即建立在主鍵上的索引讨越，他必定是唯一索引两残，通過主鍵可以快速定位一條記錄主要就是主鍵索引的原因。

外鍵索引

外鍵索引是建立在外鍵上的索引把跨，需要關(guān)聯(lián)另一個表的字段人弓，這對表關(guān)聯(lián)的速度有著很大的提升。國內(nèi)開發(fā)的時候着逐，一般都喜歡降低數(shù)據(jù)庫范式以達(dá)到減少關(guān)聯(lián)查詢或者多次查詢崔赌，從而提升檢索效率。然而這其實也是一種無奈的選擇耸别，因為國內(nèi)開發(fā)人員的素質(zhì)問題健芭，架構(gòu)師們多數(shù)采取這種不過不失的方法防止開發(fā)人員出錯。實際上秀姐，如果我們能將關(guān)鍵數(shù)據(jù)保存在主表慈迈，次要數(shù)據(jù)分存，速度絕對不會慢省有，也是提升應(yīng)用速度的一個方法痒留。但這也要求開發(fā)要精準(zhǔn)理解需求谴麦，將數(shù)據(jù)劃分到相應(yīng)的表里面，進(jìn)而精準(zhǔn)控制程序的獲取數(shù)據(jù)的規(guī)則伸头。

單字段索引

對表中某一字段的索引匾效，可以選擇建立普通索引，唯一索引和全文索引恤磷，也可以選擇其數(shù)據(jù)結(jié)構(gòu)為B-TREE或hash面哼。文檔中還有index prefixes和spatial index兩種索引，index prefixes可以理解為前置索引碗殷，而普通索引則是全字段長度的前置索引精绎，所以當(dāng)我們使用右模糊匹配('key%')時仍能使用索引；而spatial index則是涉及到存儲的索引锌妻，一般開發(fā)比較少涉及代乃。

多字段索引

對表中多個字段進(jìn)行索引，使用時可以使用左邊最小匹配原則仿粹，比如說：索引(A,B,C,D)搁吓，查詢時單獨使用A作為查詢條件，不需要額外建立索引吭历；使用(A,B)和(A,B,C)也不需要建立額外的索引堕仔；但使用B,(B,C)和(B,C,D)查詢時，需要另外建立索引晌区。

索引的數(shù)據(jù)結(jié)構(gòu)

簡單地說摩骨，常用的B-TREE和HASH主要區(qū)別在于，B-TREE因其樹形結(jié)構(gòu)擅長區(qū)域查找朗若，而HASH擅長于快速定位恼五。

小結(jié)

一般數(shù)據(jù)庫查詢調(diào)優(yōu)的基本知識都準(zhǔn)備完成了，我們試試用這些知識來解決一下實際問題吧哭懈。

實際案例

這里是我曾經(jīng)遇到的一個案例：我們需要把B邏輯庫中的T2表同步到A庫的T1邏輯表中灾馒，數(shù)據(jù)總量在2億左右，數(shù)據(jù)經(jīng)過分庫分表遣总，每月數(shù)據(jù)量單庫單表在1000萬左右睬罗，累計數(shù)據(jù)一般在3000萬左右。檢索經(jīng)過優(yōu)化旭斥，查詢已經(jīng)確認(rèn)使用索引容达，數(shù)據(jù)在程序中按分頁獲取，分頁步長1000琉预，程序GC正常董饰，但仍然時常有同步較慢的查詢出現(xiàn)，查詢延遲達(dá)到8秒以上圆米。

案件重演

為了重現(xiàn)這個慢查詢卒暂，當(dāng)時在測試庫中的嘗試了同樣的查詢，結(jié)果同樣高達(dá)8秒的查詢耗時娄帖。今天也祠，我再度在自己的測試庫中創(chuàng)建了一張30個字段，差不多400萬數(shù)據(jù)的表（生產(chǎn)實際情況更加大近速，字段達(dá)到100多個诈嘿，數(shù)據(jù)量3000萬），使用類似的SQL查詢削葱，結(jié)果仍然需要2.273秒奖亚。

image.png

眼尖的同學(xué)大概已經(jīng)看出問題了，但我們還是一步步來看析砸。

首先昔字，我們查看一下執(zhí)行計劃。

image.png

從執(zhí)行計劃可以看出首繁，語句使用了索引t2作郭，但是還是掃描了150萬行數(shù)據(jù)。而t2=10的數(shù)據(jù)總量才80萬弦疮。

image.png

這是因為數(shù)據(jù)量太多夹攒，篩選條件不能限定足夠少的數(shù)據(jù)時，索引失效了胁塞。

其實這個情況應(yīng)該很多人都曾經(jīng)遇到過咏尝，通常我們可以使用一種替換方案來轉(zhuǎn)換這句SQL，比如說按某個字段為依據(jù)啸罢，比如primary key编检，在這里是t1，那么SQL變?yōu)椋?/p>

select * from t1 where t2=10 and t1>800000 limit 1000;

再試一下伺糠，我們發(fā)現(xiàn)的確快了很多蒙谓，大概縮短到原來的5%左右了。

image.png

image.png

可以看到差別在于Extra里多了一項Extra描述“Using where”训桶。查看官方文檔的解釋：

A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index. 【4】

所以問題明了了累驮，之前的where條件限定因為數(shù)據(jù)量太大，執(zhí)行計劃已經(jīng)無法用where來限定輸出的行舵揭，所以導(dǎo)致了全表掃描而導(dǎo)致慢谤专。優(yōu)化后，性能已經(jīng)達(dá)到可以接受的輕度午绳，但是掃描的行數(shù)還是居高不下置侍，這個時候，我們其實還應(yīng)該想辦法將其降低。比如說蜡坊，我現(xiàn)在增加了一行唯一索引杠输，數(shù)據(jù)和t1一樣，我們對這行數(shù)據(jù)按照其生成規(guī)則進(jìn)行限定：

image.png

可以看到秕衙，檢索的行數(shù)大幅減少蠢甲，我們再看看實際查詢時間：

image.png

時間再次縮短為原來的1/3。

坑從中來

滿足于速度感的我很快以為這樣就解決了問題了据忘，然而問題真的解決了嗎鹦牛？

第一坑——區(qū)段限定值獲取

然而問題不止于此，關(guān)鍵在于如何獲取正確的區(qū)間段呢勇吊？我們需要一個效率哋獲取區(qū)段限定值的方法曼追，如果這個區(qū)段值檢索性能消耗太大，我們將會得不償失汉规。

我們首先嘗試用max和min函數(shù)來獲取:

image.png

結(jié)果得到的是兩個NULL礼殊，因為Max返回的是不看limit情況下的最大值和最小值差导，如果結(jié)果集被limit限定且最大值或最小值不在結(jié)果集里蹂空，返回值為NULL。

其實根據(jù)前面我們發(fā)現(xiàn)的特性屹蚊，我們不難得出悟民，我們實際需要的值一個為前一次查詢遺留的起始值坝辫，另一個則為頁步長順位值，而最快的獲取方式為以前置值為限定條件射亏，通過limit限定最終獲取行近忙。

image.png

這時候，兩個查詢總體消耗為0.093+0.032=0.125s智润。（別在意具體值及舍，截圖麻煩，實際應(yīng)該按你SQL語句的起始結(jié)束值來使用>=或者>號來獲取實際值窟绷。）

第二坑——結(jié)果集的順序

優(yōu)化后锯玛，我們發(fā)現(xiàn)時不時，我們就有丟數(shù)的現(xiàn)象兼蜈。排查后攘残，發(fā)現(xiàn)我們的數(shù)據(jù)插入有一個特點：因為使用了spark，任務(wù)插入的順序不可控为狸，生成的ID在各個分庫也不可控歼郭，所以導(dǎo)致有可能邏輯ID在一定時間內(nèi)順序是不可控的。這個邏輯字段對應(yīng)的正式我們演示中的t31字段辐棒。（物理ID——系統(tǒng)自增的ID應(yīng)該是有序可控的病曾，如果數(shù)據(jù)沒有被分庫分表牍蜂，完全依賴物理ID看似是可以的，但是實際還有問題泰涂，后面會說到鲫竞。）

由于邏輯ID的順序不可控，所以我們使用ID區(qū)段的時候负敏，就有可能出現(xiàn)一個情況贡茅，在獲取區(qū)段限定值時獲取的剛好是跳值的邏輯ID秘蛇，比如說其做，數(shù)據(jù)庫存儲順序為【1,2,3,4,9,5,6,7,8,10】，分頁為5赁还，第一次取區(qū)段值時為【1,9】妖泄，第二次取值為【9,10】，這時艘策，如果我們沒有加limit限定取值個數(shù)蹈胡，我們不能控制每個取值區(qū)間處理個數(shù)是均勻的，程序處理時間不可控朋蔫；如果limit限定了取值個數(shù)罚渐，我們又回丟失第二區(qū)段中【5,6,7,8】這四個值。所以為了區(qū)段取值符合我們預(yù)設(shè)的均勻驯妄，穩(wěn)健的需求荷并，我們需要保證邏輯ID必須是有序的。

那么MySQL中青扔，如何保證數(shù)據(jù)時有序的呢源织？

答案就是使用Order By語句。

MySQL中沒有固定的默認(rèn)排序微猖，只有固定的默認(rèn)排序方式（ASC谈息，升序）。

image.png

結(jié)果你會發(fā)現(xiàn)凛剥，作為物理ID的t1字段竟然倒序了侠仇，而t32是正序的。

觀察他的執(zhí)行計劃：

image.png

我們發(fā)現(xiàn)犁珠，他會多了一個Extra的內(nèi)容“Using index”逻炊，這說明這句SQL使用了t32的索引了。并且盲憎，t32成為它默認(rèn)排序的字段嗅骄。

因此，MySQL如果硬說有默認(rèn)的排序字段饼疙，那就是他執(zhí)行計劃中所使用的索引正序溺森。但我們前面溫故知新的時候知道慕爬，執(zhí)行計劃會被數(shù)據(jù)庫系統(tǒng)自身優(yōu)化掉，所以你的SQL實際執(zhí)行的時候屏积，很可能執(zhí)行計劃已經(jīng)不是你預(yù)計的那樣了医窿。這就是為什么我們要求數(shù)據(jù)有序的話，必須加Order By的原因炊林。

第三坑——這種優(yōu)化必要嗎姥卢？

我們知道優(yōu)化是針對大數(shù)據(jù)量的，但是如果數(shù)據(jù)量少的話渣聚，還有必要嗎独榴？我們看看一下語句：

image.png

這個查詢時間已經(jīng)是可以接受的范圍了，如果轉(zhuǎn)換為兩部法奕枝，那么時間分別為：

image.png

image.png

總共時間是0.014+0.028=0.042s棺榔，還要另有IO開銷，因此完全是沒必要使用兩分法的隘道。

這時候症歇，我們需要人工確定一個閾值，當(dāng)數(shù)據(jù)量超過這個閾值的時候谭梗，我們就可以轉(zhuǎn)為使用兩分法來進(jìn)行數(shù)據(jù)檢索忘晤。這個閾值會根據(jù)生產(chǎn)環(huán)境，表結(jié)構(gòu)激捏，索引等因素變化设塔，所以需要人為測試確定，比如說缩幸，一個30個字段的表和一個100多字段的表壹置，在海量數(shù)據(jù)中處理時間是不一樣的，后者會大好幾倍表谊。

第四坑——JAVA的多線程

Java多線程前面已經(jīng)帶來了插入ID不連續(xù)的坑钞护，但實際生產(chǎn)上，多線程還會帶來更多的坑爆办，比如說难咕，我們的線程如何劃分。我們知道距辆，兩分法中余佃，我們是需要前一次起始ID來確定下一個限定值的ID的。但是由于多線程不是線性的跨算，我們一般不會精確地控制他們的執(zhí)行順序爆土。所以這就要求我們的任務(wù)劃分要做提前規(guī)劃，或者劃分具體區(qū)片進(jìn)行處理了诸蚕。關(guān)于程序這一塊步势，我們暫時不過多討論了氧猬。

總結(jié)

在關(guān)系型數(shù)據(jù)庫中進(jìn)行海量數(shù)據(jù)的檢索，我們可以按實際數(shù)據(jù)量的大少坏瘩，選擇使用兩部查詢法分頁查詢獲取盅抚。第一步，我們確立有序的唯一索引區(qū)間倔矾；第二步妄均，通過該區(qū)間過濾我們需要的數(shù)據(jù)。

參考SQL范例

第一步

select this_end_value from table_name where unique_index_field_name > last_end_value order by unique_index_field_name limit page_size, 1;

第二步

select select_expr from table_name where unique_index_field_name between last_end_value+1 and this_end_value order by unique_index_field_name limit page_size;

參考資料

【1】https://en.wikipedia.org/wiki/Select_(SQL)

【2】https://dev.mysql.com/doc/refman/5.7/en/explain.html

【3】https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html

【4】https://dev.mysql.com/doc/refman/5.7/en/explain-output.html