共計 6945 個字符，預計需要花費 18 分鐘才能閱讀完成。
本篇內容主要講解“MySQL 億級數據數據庫優化的方法是什么”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“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 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` 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 以上的特性，進行不同場景應用的思考。
場景：銀行交易流水記錄的查詢
根據小總結六的特性，操作表和歷史查詢表一定要時間可以分開，由于帶索引的歷史表，插入會很慢，所以要插入到操作表內，操作表和歷史表的字段是一樣的。
根據小總結二特性，然后固定某個時間點，比如半夜 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 百萬的那張分出來的表，速度會很快的。
根據小總結一和小總結三的特性，把關鍵的字段加上索引，用戶，時間，這樣保證查詢的速度。
根據小總結四的特性，盡量限制查詢結果的數量范圍，比如，單個人查自己的交易明細，可以限制范圍，比如查詢時間范圍不超過三個月，或半年，或一年。
到此，相信大家對“MySQL 億級數據數據庫優化的方法是什么”有了更深的了解，不妨來實際操作一番吧！這里是丸趣 TV 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
