背景
歡迎來到Java學院,我們學院學員眾多,每年都要招收新學員。但是,我們學院并沒有“畢業”這一機制,所以年復一年學員的數量就越來越多。
咱們學院每年都有一次大考,需要統計所有學員的成績,并按排名的先后順序公示給大家。
第一年
我們招收了1,000名學員。在一年過后,我們的公示欄分為10頁,第一頁展示了前100名的學員成績,第二頁展示了101至200名的學員成績,以此類推...
第二年
由于學院的反響不錯,我們擴招了99,000名學員,總人數達到了100,000!為了方便教學管理,我們新增了9名教師,并把學生們分布到10個班進行管理。
這一年的大考由于人數眾多,辛勤的園丁們苦不堪言,在經歷了漫長的閱卷后,他們把100,000份考卷按照從高到低的順序一份份排好序...
第三年
我們的學員已經來到了10,000,000,又翻了100倍,這下教師們要罷工了,
“我們和學生的時間可是很寶貴的,這樣改卷和排序,已經沒有辦法正常上課了!”
看著教師們群情激奮,院長只好出手想了個辦法,能不能把一部分的排序工作交給學生們進行呢?
說干就干,首先,每個班共有1,000,000名學生,將他們分為1,000個1,000人的小組,每組之間先排好序。這樣的確會快很多,因為1000個小組可以一起進行了!
但問題也隨之而來,小組內排好序了,整個班級的順序呢?
其實不難,我們只需要知道每一個同學在每個小組間的順序就好了。比如說,有一位同學成績優異,1000個組中只有三個組的同學成績超過他,這三個組他分別處于3,4,5名,其他小組他都可以排第一,那么他在整個班級的排名就是2+3+4+1=10,第10名!
可比忘了最后的+1,因為如果沒有人超過他的話,他就是第一名。
到這里,咱們學院問題可能已經迎刃而解了,但實際情況可能還要復雜一點,讓我來為你們詳細介紹。
回到正題
現在,讓我們結合實際情況,進行具體問題具體分析。
一個20,000,000+數據量的用戶表,使用sharding-jdbc分表后均勻拆成了10個表,也就是說,他們的注冊時間分布是均勻的,每個表的用戶注冊時間從早到晚均勻分布,同時,我們使用mysql5.7數據庫進行存儲,Java 8進行操作。
我們要做的,就是根據用戶的注冊時間范圍,進行一個范圍查詢,再按照時間的倒序,也就是離現在近的時間排在前面,進行一個分頁展示。
簡單介紹一下sharding-jdbc做了什么,假如你有一張表order,現在它被拆成三份,分別是order_1,order_2,order_3,你想要查詢的話只要訪問那個實際不存在的表,也就是order表就好了,sharding-jdbc會幫你去order_1,order_2,order_3中查詢數據,并匯總到一起,同時幫你做了一些速度上的優化。
那么我們直接使用sharding-jdbc邏輯表(order)進行分頁查詢,會發生什么呢?
結果可能出人意料,sharding-jdbc內部采用歸并排序的方式進行排序,我們得到的數據可能并不是我們想要的。
舉個栗子,現在給你一張簡單的表,我想要查找每頁大小為2,第一頁,并按從大到小的順序會發生什么?

很好,我們的sharding-jdbc取出了每張表的前兩個數據,這對應了每頁大小為2,隨后,再把每張表的最大值取出來,也就是100,99,99,現在再按從大到小取出兩個數100,99。
乍一看好像沒有問題,恭喜你成功取到了第一頁的數據!

但是,我為什么要說但是呢,當我們取第二頁的時候,事情就有點不對勁了。
現在,按照上面的步驟再來一遍,我們從三個表中取到的是98,97,96,最后的結果是98,97。
我們真正的順序是100,99,99,98,97,97,96,96,95,所以第二頁應該是99,98,而不是98,97。這真是令人大失所望,我們的分頁之路要變得復雜起來了。

既然使用邏輯表的分頁都已經出了問題,那么我們想要使用pagehelper偷懶肯定也是不行的,那么我們只能另謀出路。
我們的問題難點在于時間,為什么一碰到時間就會出問題呢,這就不得不提到mysql5.7的排序問題,只要碰到相同值(比如說時間)的行,那么它采用的堆排序就會出問題。
眾所周知,堆排序是個不穩定的排序,比如說,我們要對以下的數字排個序:
1 2(a) 2(b) 3 4
這不是已經有序了嗎?還能怎么排序
可實際上,排序后可能會出現這樣的結果,我們已經有序的兩個2竟然被調換了順序!在數據量更大,重復數據更多的情況下,這種場景只會越來越多。
1 2(b) 2(a) 3 4
不信你可以在mysql5.7上試試,使用order by + limit進行一個排序后的分頁操作,你會驚喜的發現第一頁和第二頁的數據竟然出現了重復!
這看起來似乎是不能忍受的,但我們也只能這樣安慰自己,好歹查詢速度快了不少對吧!
解決思路
言歸正傳,我們要開始著手解決這個問題了。
面對這種重復值較多的范圍查詢,還要排序,還要分頁,那我們只能使用大名鼎鼎的二次查詢法,也就是背景中交代的,Java學院最終采用的那種方式!
說到這里是不是感到醍醐灌頂了,原來我們的分頁問題從一開始就得到解決了!
不不不,我們解決的只是分頁和排序問題罷了,可你難道要把性能給丟掉嗎?
串行查詢多個表,并且還是二次查詢,對性能的考驗可是很大的,一不小心超時了那可要捅大婁子了!
sql優化
采用只讀索引法進行查詢,說人話,就是先從表里查出符合條件的主鍵,再通過主鍵去表里查出數據。
乍一聽是不是很像通過非聚簇索引查出數據,再回表查詢?確實如此,但是情況是這樣查詢會比直接在表上查詢所有字段快很多。
舉個栗子
`
select * from from 表名 where 條件;
select * from 表名
inner join(select primaryKey from 表名 where 條件) t
using (primaryKey);
`
parallelStream
Java 8中,并行流parallelStream或許可以為我們解決這個問題,我們把查詢數據庫的操作交給parallelStream來完成,它會使用forkjoin線程池,將你的工作進行分片完成。
那么我們要怎么利用它來進行二次查詢呢?
第一次查詢
我們需要計算出實際偏移量,比如說10張表,當前頁curPage為100,頁面大小pageSize為100,實際偏移量offset為(curPage-1)*pageSize=9900
那么我們分散到10張表中的偏移量就是9900/10=990,我們就要帶著這個990進行查詢,也就是
select ... limit 990,100
第一次查出的數據,我們要計算出最大的那個時間,并按表名和時間存在HashMap中,然后把所有的數據丟進ConcurrentLinkedQueue內。
ConcurrentLinkedQueue<Object> queue=new ConcurrentLinkedQueue<>();
有人就要問了,為什么不放在ArrayList中?
首先,我們使用了parallelStream,ArrayList是線程不安全的,我們可以使用CopyOnWriteArrayList或者Collections.synchronizedList(new ArrayList<>())的方式創建一個線程安全的List。
List<Object> list2 = new CopyOnWriteArrayList<>();
List<Object> list1 = Collections.synchronizedList(new ArrayList<>());
但前者每進行一次寫操作都會復制一次數組,對于我們把數據庫數據寫入是很不友好的,而后者對List集合的操作是阻塞的,這意味著如果一個線程正在對List集合進行操作,其他線程必須等待該操作完成才能繼續執行。所以二者都會帶來一定的性能開銷。
那么ConcurrentLinkedQueue好在哪里?ConcurrentLinkedQueue是一個無界非阻塞隊列,也就是說,我們可以自由向里面添加數據而不受束縛,同時還兼顧了線程安全!并且,由于我們會對分頁大小作出限制,數據庫的時間分布均勻,不用擔心無界隊列中數據過多的情況。
第二次查詢
在第一次查詢中,我們得到一個ConcurrentLinkedQueue用來存放用戶數據,一個HashMap用來存放每張表最大時間的數據,第二次查詢,我們就要利用這個HashMap,查出最大時間在各個表的“位置”,也就是相對偏移量。
我們把HashMap中的value叫做originMaxDate,originMaxDate中最大的叫做maxDate
我們的sql要改寫為
select ... between originMaxDate and maxDate
這樣,我們把查出少量的額外數據加入到ConcurrentLinkedQueue中,并統計出每個表的額外數據作為相對偏移量,這樣我們只需要使用Stream流的distinct()(mysql between and是閉區間,所以會有重復),sort(),skip(),limit(),collect()操作,就能得到最終的數據。
題外話
不能偷懶直接統計邏輯表的總數,這也會出問題的...
邏輯表查出的總數和分別去真實表查出的總數對不上哦~
還是得老老實實查出總數,并根據最大的那個總數計算出總頁數。
PS:第一次寫博客,只是給大家提供一個解題思路,部分代碼都沒有放上來,不過聰明的你,根據我提供的思路查查部分API的用法,問題應該就能迎刃而解了吧!
浙公網安備 33010602011771號