歡迎光臨
每天分享高質量文章

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

作者:逸宸a

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

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

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

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

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

— 建立一張 現金流量表

 

DROP TABLE IF EXISTS `yun_cashflow`;

CREATE TABLE `yun_cashflow` (

  `id` bigint(20NOT NULL AUTO_INCREMENT,

  `userid` int(11DEFAULT NULL,

  `type` int(11DEFAULT NULL COMMENT '1、入賬,2提現',

  `operatoruserid` int(11DEFAULT NULL COMMENT '操作員ID',

  `withdrawdepositid` bigint(20DEFAULT NULL COMMENT '提現ID',

  `money` double DEFAULT NULL COMMENT '錢數',

  `runid` bigint(20DEFAULT 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 intset num=0;
        while num 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, namevalue, 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`DO                
                            SET @createSql = 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`)
                                )'
                            ); 
                            prepare stmt from @createSql; 
                            execute stmt;          

— 創建索引

      set @createIndexSql1  = CONCAT('create index `t_money` on yun_cashflow_',`@i`,'(`money`);');
                            prepare stmt1 from @createIndexSql1; 
                            execute stmt1; 
                            set @createIndexSql2  = CONCAT('create index `t_userid` on yun_cashflow_',`@i`,'(`userid`);');
                            prepare stmt2 from @createIndexSql2; 
                            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萬左右條資料一張表,另外在做一張時間範圍的索引表,如下:

CreateTimeIndexTable
ID  TableName   CreateTimeStart CreateTimeEnd
1   yun_cashflow_1  2018-10-22 09:06:58 2018-10-26 09:06:58
2   yun_cashflow_2  2018-10-26 09:06:58 2018-10-29 09:06:58
3   yun_cashflow_3  2018-11-12 09:06:58 2018-11-22 09:06:58
4   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. 根據小總結四的特性,儘量限制查詢結果的數量範圍,比如,單個人查自己的交易明細,可以限制範圍,比如查詢時間範圍不超過三個月,或半年,或一年。

已同步到看一看
赞(0)

分享創造快樂