MySQL 億級數據需求的優化思路(一),交易流水記錄的查詢

對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 doinsert 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`< 100 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

IDTableNameCreateTimeStartCreateTimeEnd
1yun_cashflow_12018-10-22 09:06:582018-10-26 09:06:58
2yun_cashflow_22018-10-26 09:06:582018-10-29 09:06:58
3yun_cashflow_32018-11-12 09:06:582018-11-22 09:06:58
4yun_cashflow_42018-11-22 09:06:582018-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百萬的那張分出來的表，速度會很快的。

?

3、根據小總結一和小總結三的特性，把關鍵的字段加上索引，用戶，時間，這樣保證查詢的速度。

4、根據小總結四的特性，盡量限制查詢結果的數量范圍，比如，單個人查自己的交易明細，可以限制范圍，比如查詢時間范圍不超過三個月，或半年，或一年。

?

?

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/386976.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/386976.shtml
英文地址，請注明出處：http://en.pswp.cn/news/386976.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

RUNOOB python練習題2

RUNOOB python練習題2

用來練手的python 練習題&#xff0c;原鏈接 : python練習實例2 題干 : 企業發放的獎金根據利潤提成。利潤(I)低于或等于10萬元時&#xff0c;獎金可提10%&#xff1b;利潤高于10萬元&#xff0c;低于20萬元時&#xff0c;低于10萬元的部分按10%提成&#xff0c;高于10萬元的…
閱讀更多...
dubbo負載均衡策略和集群容錯策略

dubbo負載均衡策略和集群容錯策略

dubbo負載均衡策略 random loadbalance 默認情況下&#xff0c;dubbo是random load balance隨機調用實現負載均衡&#xff0c;可以對provider不同實例設置不同的權重&#xff0c;會按照權重來負載均衡&#xff0c;權重越大分配流量越高&#xff0c;一般就用這個默認的就可以了。…
閱讀更多...
MySQL 億級數據需求的優化思路(二)，100億數據，1萬字段屬性的秒級檢索

MySQL 億級數據需求的優化思路(二)，100億數據，1萬字段屬性的秒級檢索

最近在研究億級數據的時候&#xff0c;無意中看到了一個關于寫58同城的文章 https://blog.csdn.net/admin1973/article/details/55251499?fromtimeline 其實上面講的versionext的方式以及壓縮json的思路&#xff0c;對于我來講都可以看得懂&#xff0c;想得通&#xff0c;其…
閱讀更多...
RUNOOB python練習題3

RUNOOB python練習題3

用來練手的python 練習題&#xff0c;原鏈接 : python練習實例3 拿到題目就寫了如下代碼&#xff0c;思路是因為使用**0.5進行開平方操作時&#xff0c;python會將數據類型自動轉換為float單精度浮點型。這里利用提取其整數部分&#xff0c;來判斷這個數是否是完全平方數。 z…
閱讀更多...
使用git將項目上傳到github（最簡單方法）

使用git將項目上傳到github（最簡單方法）

使用git將項目上傳到github&#xff08;最簡單方法&#xff09; 首先你需要一個github賬號&#xff0c;所有還沒有的話先去注冊吧&#xff01; https://github.com/ 我們使用git需要先安裝git工具&#xff0c;這里給出下載地址&#xff0c;下載后一路直接安裝即可&#xff1…
閱讀更多...
數據庫 概念詳解

數據庫 概念詳解

數據庫 概念詳解 一、MySQL MySQL 事務 MySQL 鎖 MySQL 二、Redis 三、MongoDB 四、Memcached 轉載于:https://www.cnblogs.com/guozepingboke/p/10743648.html
閱讀更多...
RUNOOB python練習題4

RUNOOB python練習題4

用來練手的python習題其四&#xff0c; 原題鏈接: python練習實例4 題干: 輸入某年某月某日&#xff0c;判斷這一天是這一年的第幾天&#xff1f; 這個題目比較簡單&#xff0c;只需要注意閏年和非閏年的區別就可以了。我這里使用numpy矩陣存儲每個月的天數&#xff0c;之后用…
閱讀更多...
GitHub入門：如何上傳與下載工程？

GitHub入門：如何上傳與下載工程？

由于經常要在家寫代碼&#xff0c;所以需要有個能夠方便訪問代碼管理工具。最近嘗試了一下GitHub。經過了一翻糾結之后&#xff0c;基本上掌握了他的使用方式。 要使用GitHub需要首先在其網站上進行注冊。其官方網站是https://github.com/。注冊的流程在這里就不多少了&#x…
閱讀更多...
如何解決PIP命令不可用

如何解決PIP命令不可用

今天想用PIP裝一個python包&#xff0c;發現PIP報錯&#xff0c;不是內部或外部命令。。。 遇事百度&#xff0c;有兩種說法&#xff0c;一&#xff0c;沒安裝包&#xff0c;不管那么多命令執行了再說 在命令行輸入&#xff1a;python -m ensurepip 將pip.exe文件下載下來 再pi…
閱讀更多...
RUNOOB python練習題5

RUNOOB python練習題5

用來練手的python 練習題其五&#xff0c;原鏈接 : python練習實例5 題干 : 輸入三個整數x,y,z&#xff0c;請把這三個數由小到大輸出。 又是非常簡單的排序算法&#xff0c;只要使用numpy矩陣的排序方法或者使用python list的排序算法就可以輕松解決。 源代碼如下 : import …
閱讀更多...
初步使用github,并上傳下載文件

初步使用github,并上傳下載文件

使用GitHub需要先注冊GitHub的賬號,登陸進去 然后開始創建項目 start a project 創建完成,開始生成公私鑰,可以不必每次都要輸密碼 ssh-keygen -t rsa -C "mghxy123163.com" //填寫email地址&#xff0c;然后一直“回車”ok 然后把公鑰導入GitHub中的key里面去,也…
閱讀更多...
NOIP2000提高組復賽C 單詞接龍

NOIP2000提高組復賽C 單詞接龍

題目鏈接&#xff1a;https://ac.nowcoder.com/acm/contest/248/C 題目大意&#xff1a; 略 分析&#xff1a; 注意點&#xff1a;1.前綴和后綴的公共部分應該選最短的。2.如果兩個字符串前綴和后綴的公共部分恰好是其中一個字符串&#xff0c;那么這兩個字符串不能合并。 代碼…
閱讀更多...
右鍵Git Bash Here不見了怎么辦，手把手教你還原！

右鍵Git Bash Here不見了怎么辦，手把手教你還原！

第一步&#xff0c;window R&#xff0c;輸入regedit回車進入注冊表 依次進入HKEY_CLASSES_ROOT —-》 Directory —-》Background —-》 shell 右鍵點擊shell&#xff0c;選擇新建&#xff0c;然后選擇項&#xff0c;命名為 Git Bash Here&#xff0c;成功后進入桌面右鍵發現…
閱讀更多...
RUNOOB python練習題6 斐波那契數列

RUNOOB python練習題6 斐波那契數列

用來練手的python 練習題其六&#xff0c;原鏈接 : python練習實例6 題干 : 斐波那契數列 斐波那契數列可以說是很好的遞歸理解工具了&#xff0c;這里就用遞歸實現一下斐波那契數列。 源代碼如下: # 返回fibonacci數列中某一項的數值 def Fibonacci(n):if n 1:return 1eli…
閱讀更多...
linux 單用戶密碼修改

linux 單用戶密碼修改

1.啟動系統&#xff0c;并在GRUB2啟動屏顯時&#xff0c;按下e鍵進入編輯模式。 2.在linux16/inux/linuxef所在參數行ro更改為init/sysroot/bin/sh 3.按Crlx啟動到shell. 4.掛載文件系統為可寫模式: mount -o remount &#xff0c;rw /sysroot 5換根chroot /sysroot 6.運行pass…
閱讀更多...
github windows客戶端

github windows客戶端

方法/步驟 1 1. 首先到官網下載Github客戶端 2 2. 點擊上圖紅框的按鈕開始下載客戶端。 3 3. 雙擊下載好的客戶端&#xff0c;開始安裝。 4 雙擊之后出現一個框 5 之后等待一段時間&#xff0c;出現一個在線下載界面 6 4. 在線下載完成之后開始進行安裝。安裝完成之后…
閱讀更多...
賦值語句 變量的地址相關 : RUNOOB python練習題7

賦值語句 變量的地址相關 : RUNOOB python練習題7

用來練手的python 練習題&#xff0c;原鏈接 : python練習實例7 練習實例7非常的簡單也有意思。題干 : 將一個列表的數據復制到另一個列表中。 完成這個操作的代碼非常簡單&#xff0c;即使是我這樣的初學者應該也是一語道破&#xff0c;賦值語句嘛。但這里我們就列舉出幾種不…
閱讀更多...
Web標準的概念及組成

Web標準的概念及組成

一周更新兩個或三個關于web前端的知識點&#xff0c;歡迎感興趣的小伙伴們一起學習討論1、WEB標準是網頁制作的標準&#xff0c;它不是一個標準&#xff0c;它是根據網頁的不同組成部分生成的一系列標準。這些標準大部分由W3C起草發布&#xff0c;也有部分標準由ECMA起草發布。…
閱讀更多...
Fiddler簡介及安裝和HTTPS的解決

Fiddler簡介及安裝和HTTPS的解決

Fiddler簡介&#xff1a; 一個很強大的抓包工具&#xff0c;類似Charles 1.安裝&#xff1a; Filddler官網&#xff1a;點擊打開鏈接 我安裝的是filddler4&#xff1a;點擊打開鏈接 直接下載文件&#xff0c;然后一路下一步就可以了 1.下載文件&#xff1a; 2.安裝文件一…
閱讀更多...
RUNOOB python練習題8 numpy矩陣的索引及遍歷

RUNOOB python練習題8 numpy矩陣的索引及遍歷

用來練手的python 練習題&#xff0c;原鏈接 : python練習實例8 題干: 輸出 9*9 乘法口訣表。 import numpy as nptable np.zeros((9,9)) for i in range(table.shape[0]):for j in range(table.shape[1]):table[i][j] (i1) * (j1)# 查詢九九乘法表 def affichage_table(a,…
閱讀更多...
最新文章

Copyright @ 2022~2023