數據庫

MySQL 億級數據數據庫優化方案測試-銀行交易流水記錄的查詢

廣告
廣告

微信掃一掃,分享到朋友圈

MySQL 億級數據數據庫優化方案測試-銀行交易流水記錄的查詢
0 0

作者:逸宸a

鏈接:https://www.jianshu.com/p/cbdef47fb837

對MySQL的性能和億級數據的處理方法思考,以及分庫分表到底該如何做,在什么場景比較合適?

比如銀行交易流水記錄的查詢

限鹽少許,上實際實驗過程,以下是在實驗的過程中做一些操作,以及踩過的一些坑,我覺得坑對于讀者來講是非常有用的。

首先:建立一個現金流量表,交易歷史是各個金融體系下使用率最高,歷史存留數據量最大的數據類型。現金流量表的數據搜索,可以根據時間范圍,和個人,以及金額進行搜索。

— 建立一張 現金流量表

DROP?TABLE?IF?EXISTS?`yun_cashflow`;CREATE?TABLE?`yun_cashflow`?(??`id`?bigint(20)?NOT?NULL?AUTO_INCREMENT,??`userid`?int(11)?DEFAULT?NULL,??`type`?int(11)?DEFAULT?NULL?COMMENT?'1、入賬,2提現',??`operatoruserid`?int(11)?DEFAULT?NULL?COMMENT?'操作員ID',??`withdrawdepositid`?bigint(20)?DEFAULT?NULL?COMMENT?'提現ID',??`money`?double?DEFAULT?NULL?COMMENT?'錢數',??`runid`?bigint(20)?DEFAULT?NULL?COMMENT?'工單ID',??`createtime`?timestamp?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP,??PRIMARY?KEY?(`id`))?ENGINE=InnoDB?AUTO_INCREMENT=63?DEFAULT?CHARSET=utf8;

然后開始造1個億的數據進去。

— 循環插入

drop?PROCEDURE?test_insert;DELIMITER;;CREATE?PROCEDURE?test_insert()begin?declare?num?int;?set?num=0;????????while?num?<?10000?do????????????insert?into?yun_cashflow(userid,type,operatoruserid,withdrawdepositid,money)?values(FLOOR(7?+?(RAND()?*?6))+FLOOR(22?+?(RAND()?*?9)),1,FLOOR(97?+?(RAND()?*?6))+FLOOR(2?+?(RAND()?*?9)),FLOOR(17?+?(RAND()?*?6))+FLOOR(2?+?(RAND()?*?9)),FLOOR(5?+?(RAND()?*?6))+FLOOR(2?+?(RAND()?*?9)));????????????set?num=num+1;????????end?while;??END;;call?test_insert();

坑一:

這個存儲過程建立好了之后,發現插入數據特別的慢,一天一晚上也插入不到100萬條數據,平均每秒40~60條數據,中間我停過幾次,以為是隨機函數的問題,都變成常數,但效果一樣,還是很慢,當時讓我對這個MySQL數據庫感覺到悲觀,畢竟Oracle用慣了,那插速是真的很快,不過功夫不負有心人,原來可以用另外一種寫法造數據,速度很快,上代碼。

INSERT?INTO?example(example_id,?name,?value,?other_value)VALUES(100,?'Name?1',?'Value?1',?'Other?1'),(101,?'Name?2',?'Value?2',?'Other?2'),(102,?'Name?3',?'Value?3',?'Other?3'),(103,?'Name?4',?'Value?4',?'Other?4');

就是在循環里,用這種格式造很多數據,VALUES后面以,隔開,然后把數據寫上去,我用Excel造了1萬條數據,按照語句格式粘貼了出來,就變成每循環一次,就1萬條數據,這樣沒多久1億數據就造好了。

select?count(*)?from?yun_cashflow

我還比較好奇,8個字段1億條數據,到底占了多大的地方,通過以下語句找到數據的路徑。

show?global?variables?like?"%datadir%";

通過查看文件,是7.78GB,看來如果字段不是很多,數據量大的話,其實不是什么問題,這其實作為架構師來講,在估算機器配置硬盤冗余的時候,這是最簡單直接粗暴的換算思路。
行了,表建完了,各種實驗開始

首先,啥條件不加看看咋樣。

呵呵了,Out of memory,看來這個查詢是真往內存里整,內存整冒煙了,看來7.8G的數據是往內存里放,我內存沒那么大導致的。

資金流水一般會按照時間進行查詢,看看這速度到底怎樣。

select?*?from?yun_cashflow??where?createtime?between?'2018-10-23?09:06:58'?and?'2018-10-23?09:06:59'

我去,腦補一下,當你拿這支付寶查歷史資金明細的時候,56條信息,103.489秒,也就是將近2分鐘的查詢速度,你會是怎樣的體驗。哦 哦,不對,這個還沒加用條件,那下面單獨試試某個用戶不限時間范圍的條件是怎樣的。

select?count(*)?from?yun_cashflow?where?userid=21

也是將近1分半的速度,那在試試金額的條件。

select?count(*)?from?yun_cashflow?where?money<62?and?userid=32

同樣都是將近一分半的時間。
那把兩個條件做下級聯,看看效果會是怎樣。
一樣,也是將近1分半的時間。

小總結一:在不加索引的情況下,無論單獨,還是聯合條件查詢,結果都是1分多鐘不到2分鐘。

好吧,那就加上索引試試,看看到底會有啥樣奇跡發生。
給用戶加索引

ALTER?TABLE?yun_cashflow?ADD?INDEX?index_userid?(userid)

`
給金額加索引

ALTER?TABLE?yun_cashflow?ADD?INDEX?index_money?(money)

給時間加索引

ALTER?TABLE?yun_cashflow?ADD?INDEX?index_createtime?(createtime)

小總結二: 建立索引的時間平均在1400秒左右,大概在23分鐘左右。
索引都建立完了,在開始以前的條件查詢,看看效果。

1、時間范圍查詢

select?*?from?yun_cashflow??where?createtime?between?'2018-10-23?09:06:58'?and?'2018-10-23?09:06:59'

2、用戶查詢與錢的聯合查詢

3、用戶查詢與錢與時間三個條件的聯合查詢

select?*?from?yun_cashflow?where?money<62?and?userid=32?and??createtime?between?'2018-10-22?09:06:58'?and?'2018-10-23?09:06:59'

小總結三:建立完索引后,這種級聯性質的查詢,速度基本都很快,數據量不大的情況下,基本不會超過一秒。

由于時間的范圍返回是56條數據,數據量比較小,所以速度快可能與這個有關,那實驗下條件多的數據效果會是什么樣。
先試試加完索引, 金額條件的效果。
2千5百萬的數據,返回時間為11.460秒。
加一個用戶數量比較多的條件 UserID=21
返回1000多萬的數據,用了6秒
在找一個用戶數量比較少的userid=34
返回4000多條,用不到1秒。

小總結四:條件返回的數據統計量越多,速度就越慢,超過1000萬就慢的離譜,1秒左右就是100萬的量才行。

那。。。。。。。。。。。。咱們程序猿都知道,我們在做數據的時候,都要用到分頁。分頁一般會用到LIMIT,比如每頁10行,第二頁就是LIMIT 10,10,得試試在分頁的時候,哪些頁的情況下,會是什么樣的效果呢?

  • limit在1千時候速度
  • limit在1百萬時候速度
  • limit在1千萬時候速度

小總結五:LIMIT 參數1,參數2  在隨著參數1(開始索引)增大時候,這個速度就會越來越慢,如果要求1秒左右返回時候的速度是100萬數據,在多在大就慢了,也就是,如果10條一頁,當你到第10萬頁之后,就會越來越慢。如果到30萬頁之后,可能就會到不到一般系統的3秒要求了。

數據庫都建上索引了,那我插數據速度有沒有影響呢,那試試
也就是說100條數據插了將近5秒,平均每秒插20條。

小總結六:也就是說,按照這樣的速度插入,并發量一但大的情況下,操作起來會很慢。所以在有索引的條件下插入數據,要么索引失效,要么插入會特別慢。
分庫分表的思維,一個大表返回那么多數據慢,那我把它變成若干張表,然后每張表count(*)后,我統計累加一下,一合計,就是所有數據的查詢結果的條數,然后就是到第多少頁,我先算一下這頁在哪個庫,哪張表,在從那張表讀不就完了。通過之前 的總結,100萬數據返回為1秒,所以就一張表里放100萬個數據,1億的數據就100張表。

BEGIN?????????DECLARE?`@i`?int(11);????????????DECLARE?`@createSql`?VARCHAR(2560);?????????DECLARE?`@createIndexSql1`?VARCHAR(2560);????????????DECLARE?`@createIndexSql2`?VARCHAR(2560);????????DECLARE?`@createIndexSql3`?VARCHAR(2560);????????set?`@i`=0;?????????WHILE??`@i`<[email protected]l?=?CONCAT('CREATE?TABLE?IF?NOT?EXISTS?yun_cashflow_',`@i`,'(`id`?bigint(20)?NOT?NULL?AUTO_INCREMENT,????????????????????????????????`userid`?int(11)?DEFAULT?NULL,????????????????????????????????`type`?int(11)?DEFAULT?NULL??,????????????????????????????????`operatoruserid`?int(11)?DEFAULT?NULL??,????????????????????????????????`withdrawdepositid`?bigint(20)?DEFAULT?NULL??,????????????????????????????????`money`?double?DEFAULT?NULL??,????????????????????????????????`runid`?bigint(20)?DEFAULT?NULL??,????????????????????????????????`createtime`?timestamp?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP,????????????????????????????????PRIMARY?KEY?(`id`)????????????????????????????????)'????????????????????????????);[email protected];?????????????????????????????execute?stmt;??????????

— 創建索引

[email protected]??=?CONCAT('create?index?`t_money`?on?yun_cashflow_',`@i`,'(`money`);');[email protected];?????????????????????????????execute?stmt1;[email protected]??=?CONCAT('create?index?`t_userid`?on?yun_cashflow_',`@i`,'(`userid`);');[email protected];?????????????????????????????execute?stmt2;?SET?`@i`=?`@i`+1;?????????????END?WHILE;END

表建完了,庫里的效果是醬樣的。

是不是很酷,這表分的,絕了,滿庫全是表。那還得往每張表里整100萬的數據。這部分代碼就不寫了,可以參考前面的改,相信能把文章看到這的都是懂行的人,也是對這方面有一腚追求的人。

坑二:我高估了我的計算機的并行計算能力,當我啟用100個線程同時玩我自己電腦的數據庫連接的時候,到后期給我反饋的結果是這樣的。

說白了,連接滿了,超時,數據庫都不給我返回值了,所以這種實驗,不找100臺機器,也別可一臺機器去霍霍,因為如果能快,那個1個億的大表,返回的也不會慢。這時候拼的就是計算能力了,都在一臺機器上去做實驗,會讓你懷疑人生的。

那咋辦, 這地方我就假裝返回都是1000毫秒,也就1秒,然后每個線程都在1秒的時候都給我返回值,這個值我寫死,可以看看多線程分布式統計count的效果。

最后總體耗時,就是最后那個返回時間最長的線程返回的時間,所以理論上100個線程同時啟動,應該在1秒完成,但線程這玩意有快有慢,所以1秒多一點,也是可以接受的。如果碰上都是機器性能好的時候,所有數據庫返回都在1秒以內,那么也就是1秒了。

這個多線程編程可以試試類似Java的countDownLatch/AKKA 將異步多線程結果同步返回。

最后是在數據庫數據量比較大的時候,通過MySQL以上的特性,進行不同場景應用的思考。

場景:銀行交易流水記錄的查詢

  1. 根據小總結六的特性,操作表和歷史查詢表一定要時間可以分開,由于帶索引的歷史表,插入會很慢,所以要插入到操作表內,操作表和歷史表的字段是一樣的。
  2. 根據小總結二特性,然后固定某個時間點,比如半夜12點,或者固定日期,或者選擇非交易查詢活躍的時間,把操作表里的數據往歷史表里插一下,由于重建索引也用不了太久,一樣半個小時左右。讓兩種表并存。還有另外一種策略,由于流水主要以時間做為排序對象,可以按照時間順序,也就是ID自增長的順序進行分庫分表,就像試驗的那樣,100萬左右條數據一張表,另外在做一張時間范圍的索引表,如下:
CreateTimeIndexTableID??TableName???CreateTimeStart?CreateTimeEnd1???yun_cashflow_1??2018-10-22?09:06:58?2018-10-26?09:06:582???yun_cashflow_2??2018-10-26?09:06:58?2018-10-29?09:06:583???yun_cashflow_3??2018-11-12?09:06:58?2018-11-22?09:06:584???yun_cashflow_4??2018-11-22?09:06:58?2018-11-26?09:06:58

當遇見這樣語句需求的時候:

select?*?from?yun_cashflow?where?money<62?and?userid=32?and??createtime?between?'2018-10-27?09:06:58'?and?'2018-10-28?09:06:59'

1)、就改寫成這樣的順序

select?TableName?from?CreateTimeIndexTable?where?CreateTimeStart>??'2018-10-27?09:06:58'?and?CreateTimeEnd?<?'2018-10-28?09:06:59'

2)、當得到TableName的時候,結果是yun_cashflow_2,在進行語句的查詢

select?*?from?yun_cashflow_2?where?money<62?and?userid=32?and??createtime?between?'2018-10-27?09:06:58'?and?'2018-10-28?09:06:59'

這樣,兩遍就可以查詢到結果。
不過也有可能查詢的結果是多個,比如

select?TableName?from?CreateTimeIndexTable?where?CreateTimeStart>??'2018-10-27?09:06:58'?and?CreateTimeEnd?<?'2018-11-13?09:06:59'

yun_cashflow_2,和yun_cashflow_3,這個時候,就需要把兩個表的結果都查詢出來,進行merge。相信程序員們對兩個表的結果集合并邏輯都不是什么難事,這地方不多解釋。

這樣做的好處,主要是每次重建索引的時候,就不用整個1個億的大表進行重建,而是只重建最近的1百萬的那張分出來的表,速度會很快的。

  1. 根據小總結一和小總結三的特性,把關鍵的字段加上索引,用戶,時間,這樣保證查詢的速度。
  2. 根據小總結四的特性,盡量限制查詢結果的數量范圍,比如,單個人查自己的交易明細,可以限制范圍,比如查詢時間范圍不超過三個月,或半年,或一年。
我還沒有學會寫個人說明!

深度復盤GitHub發展史:如何在短短10年內改變了人們的編程方式?

上一篇

老程序員都去哪了?

下一篇

你也可能喜歡

MySQL 億級數據數據庫優化方案測試-銀行交易流水記錄的查詢

長按儲存圖像,分享給朋友

ITPUB 每周精要將以郵件的形式發放至您的郵箱


微信掃一掃

微信掃一掃
双色球常规走势图 2018长沙怎么跑滴滴赚钱 苏州和杭州哪个更好赚钱 开服装设计公司赚钱 黑水路卖啥小吃赚钱 美团业务经理怎么赚钱 本地信息平台赚钱吗 德语培训老师赚钱多吗 赚钱是一件很难的事吗 歌手节目怎么赚钱 什么类别的软件赚钱 孕妇在家做什么可以赚钱 农资业务员赚钱吗