2.3　為MySQL填充億級(jí)數(shù)據(jù)實(shí)戰(zhàn)

這里只準(zhǔn)備了一臺(tái)服務(wù)器作為MySQL服務(wù)器。該服務(wù)器內(nèi)存1GB、硬盤20GB、CPU 1核、系統(tǒng)版本CentOS 6.5、MySQL版本5.1.73。

增加的測(cè)試數(shù)據(jù)的表結(jié)構(gòu)如下所示：

在創(chuàng)建表之后，可以通過(guò)如下命令查看創(chuàng)建的表語(yǔ)句：

注意：該表僅用來(lái)測(cè)試，無(wú)其他特殊含義。

2.3.1　INSERT INTO SELECT方案

INSERT INTO SELECT語(yǔ)句可以先從一個(gè)表中復(fù)制數(shù)據(jù)，再把復(fù)制的數(shù)據(jù)插到一個(gè)已存在的表（目標(biāo)表）中，并且目標(biāo)表中已存在的行完全不受影響。從一個(gè)表中復(fù)制所有的列插到目標(biāo)表中的命令如下所示：

也可以從一個(gè)表中只復(fù)制某些列插到目標(biāo)表中：

1. INSERT INTO SELECT語(yǔ)句的優(yōu)點(diǎn)和缺點(diǎn)

為數(shù)據(jù)庫(kù)填充測(cè)試數(shù)據(jù)最快且最容易的方案是使用INSERT INTO SELECT語(yǔ)句。該方案不涉及任何I/O方面的消耗，最大的缺點(diǎn)是在創(chuàng)建數(shù)據(jù)時(shí)數(shù)據(jù)自由度不高。

注意，INSERT INTO SELECT語(yǔ)句只能為數(shù)據(jù)庫(kù)填充數(shù)據(jù)，絕不能為數(shù)據(jù)庫(kù)遷移數(shù)據(jù)。例如，需要將表A的數(shù)據(jù)遷移到表B中，雖然貌似可以使用INSERT INTO SELECT語(yǔ)句完成需求，但是INSERT INTO SELECT語(yǔ)句采用全表掃描的方式讀取數(shù)據(jù)庫(kù)資源，在默認(rèn)的數(shù)據(jù)庫(kù)隔離級(jí)別下，表B會(huì)被逐步行鎖（掃一條鎖一條），表A則會(huì)被表鎖（全表加鎖）。由于鎖住的數(shù)據(jù)越來(lái)越多，進(jìn)而導(dǎo)致數(shù)據(jù)庫(kù)增刪改大量失敗，從而導(dǎo)致應(yīng)用程序崩潰。

2. INSERT INTO SELECT語(yǔ)句的實(shí)現(xiàn)過(guò)程

（1）插入初始化數(shù)據(jù)：

初始化結(jié)果如圖2-1所示。

（2）通過(guò)INSERT INTO SELECT語(yǔ)句創(chuàng)建數(shù)據(jù)：

在多次使用INSERT INTO SELECT語(yǔ)句之后，每次使用該語(yǔ)句都會(huì)使數(shù)據(jù)量翻倍。在硬盤與CPU足夠的情況下，幾秒即可填充億級(jí)數(shù)據(jù)，結(jié)果如圖2-2所示。

3. INSERT INTO SELECT語(yǔ)句可能出現(xiàn)的異常

當(dāng)復(fù)制400萬(wàn)條數(shù)據(jù)到表中時(shí)已經(jīng)出現(xiàn)了錯(cuò)誤，如下所示：

這是由于緩沖區(qū)不夠?qū)е碌模瑢儆贛ySQL緩沖區(qū)異常。

此時(shí)需要在InnoDB buffer Pool中處理緩存，處理的緩存內(nèi)容如下所示：

（1）數(shù)據(jù)緩存（InnoDB數(shù)據(jù)頁(yè)面）。

（2）索引緩存（索引數(shù)據(jù)）。

（3）緩存數(shù)據(jù)（在內(nèi)存中已修改但尚未寫入磁盤的數(shù)據(jù)）。

（4）內(nèi)部結(jié)構(gòu)（如自適應(yīng)哈希索引、行鎖等）。

.……

因此，當(dāng)MySQL大批量執(zhí)行INSERT INTO SELECT語(yǔ)句時(shí)，要求InnoDB Buffer Pool要足夠大，并且當(dāng)InnoDB Buffer Pool較大時(shí)，還會(huì)提高INSERT INTO SELECT語(yǔ)句的執(zhí)行效率。解決MySQL緩沖區(qū)異常的方式只有兩種：

（1）在INSERT INTO SELECT語(yǔ)句中增加LIMIT限制性語(yǔ)句，保證每次增加的數(shù)據(jù)量緩沖區(qū)都可以承載。

（2）增加innodb_buffer_pool_size的值。

4. 增加innodb_buffer_pool_size的值的步驟

（1）使用下面的命令可以查看當(dāng)前表使用了哪種數(shù)據(jù)庫(kù)引擎：

結(jié)果如圖2-3所示。

（2）使用下面的命令可以查看當(dāng)前數(shù)據(jù)庫(kù)引擎狀態(tài)中的參數(shù)：

運(yùn)行之后，截取部分參數(shù)，如表2-1所示。從表2-1中可以看出，innodb_buffer_pool_size的值為“8388608”，即為8MB。

（3）查看當(dāng)前數(shù)據(jù)庫(kù)引擎狀態(tài)中的參數(shù)。

查找配置文件，在Linux系統(tǒng)中，配置文件是my.cnf；在Windows系統(tǒng)中，配置文件是my.ini。設(shè)置innodb_buffer_pool_size=64MB。更改之后，重新運(yùn)行MySQL，再次查看數(shù)據(jù)庫(kù)引擎狀態(tài)中的參數(shù)可以發(fā)現(xiàn)，innodb_buffer_pool_size的值已經(jīng)修改了，如圖2-4所示。

2.3.2　存儲(chǔ)過(guò)程方案

存儲(chǔ)過(guò)程（Stored Procedure）是數(shù)據(jù)庫(kù)中可以完成某種特定功能的SQL語(yǔ)句集。用戶可以通過(guò)指定存儲(chǔ)過(guò)程的名稱并給定參數(shù)（需要時(shí)）來(lái)調(diào)用并執(zhí)行存儲(chǔ)過(guò)程。我們可以把存儲(chǔ)過(guò)程簡(jiǎn)單地理解為數(shù)據(jù)庫(kù)在SQL語(yǔ)言層面的代碼封裝與重用。MySQL是從5.0版本開始支持存儲(chǔ)過(guò)程的。

1. 存儲(chǔ)過(guò)程方案的優(yōu)點(diǎn)和缺點(diǎn)

優(yōu)點(diǎn)：

（1）存儲(chǔ)過(guò)程可封裝，并隱藏復(fù)雜的商業(yè)邏輯。

（2）存儲(chǔ)過(guò)程可以回傳值，并且可以接收參數(shù)。

（3）存儲(chǔ)過(guò)程無(wú)法使用SELECT指令來(lái)運(yùn)行，因?yàn)樗亲映绦颍c查看表、數(shù)據(jù)表或用戶定義函數(shù)等不同。

（4）存儲(chǔ)過(guò)程可以用在數(shù)據(jù)檢驗(yàn)上，強(qiáng)制執(zhí)行商業(yè)邏輯等。

缺點(diǎn)：

（1）存儲(chǔ)過(guò)程往往定制化于特定的數(shù)據(jù)庫(kù)上，當(dāng)切換到其他數(shù)據(jù)庫(kù)時(shí)，因?yàn)橹С值木幊陶Z(yǔ)言不同，需要重寫原有的存儲(chǔ)過(guò)程。

（2）存儲(chǔ)過(guò)程的性能調(diào)校與編寫通常受限于數(shù)據(jù)庫(kù)。

2. 存儲(chǔ)過(guò)程方案的實(shí)現(xiàn)過(guò)程

聲明存儲(chǔ)過(guò)程，如下所示：

注意：此處可以使用存儲(chǔ)方案的隨機(jī)數(shù)函數(shù)來(lái)創(chuàng)建數(shù)據(jù)。另外，如果要增加事務(wù)，則不要過(guò)于頻繁提交事務(wù)，否則會(huì)出現(xiàn)磁盤I/O異常。

調(diào)用存儲(chǔ)過(guò)程如表2-2所示。

2.3.3　Loadfile方案

Loadfile方案相當(dāng)于使用Java或Python等語(yǔ)言先創(chuàng)建CVS、txt等文件，再把數(shù)據(jù)存放在這些文件中，最后通過(guò)MySQL的Loadfile命令，把文件中的數(shù)據(jù)導(dǎo)入MySQL中。

1. Loadfile方案的優(yōu)點(diǎn)和缺點(diǎn)

Loadfile方案與INSERT INTO SELECT方案和存儲(chǔ)過(guò)程方案相比，自由度更高。但是從需要準(zhǔn)備的文件來(lái)看，Loadfile方案整體所需要的時(shí)間比INSERT INTO SELECT方案和存儲(chǔ)過(guò)程方案要多。

2. Loadfile方案的實(shí)現(xiàn)過(guò)程

（1）準(zhǔn)備文件。

通過(guò)Java或Python等語(yǔ)言編寫代碼，輸出相應(yīng)的CVS文件或txt文件，文件內(nèi)容如圖2-5所示。

（2）把文件導(dǎo)入MySQL中。

使用如下命令把文件上傳到服務(wù)端的/var/lib/mysql/目錄下：

Navicat和SQLYog等工具也有上傳文件的功能，但是數(shù)據(jù)庫(kù)在連接這類工具時(shí)速度會(huì)慢很多。

2.3.4　第三方解決方案

1. DataFactory

DataFactory是一個(gè)大數(shù)據(jù)生成工具，可以按照數(shù)據(jù)的某些規(guī)律大批量地生成數(shù)據(jù)。該工具的特點(diǎn)是簡(jiǎn)單易用。

2. Datafaker

Datafaker是一個(gè)大批量測(cè)試數(shù)據(jù)和流測(cè)試數(shù)據(jù)的生成工具，是一個(gè)多數(shù)據(jù)源測(cè)試數(shù)據(jù)構(gòu)造工具，可以模擬生成大部分常用數(shù)據(jù)類型的數(shù)據(jù)。