摘要：最近在做項(xiàng)目的時(shí)候，遇到了一些大數(shù)據(jù)量的操作，有大批量的的操作，一開(kāi)始的實(shí)現(xiàn)的方案經(jīng)過(guò)性能測(cè)試，發(fā)現(xiàn)性能并不是很好，然后開(kāi)始審查代碼，對(duì)相關(guān)可以提升性能的操作進(jìn)行了優(yōu)化，這里分享給大家。另外也不推薦大量數(shù)據(jù)的批量更新，一次不要超過(guò)條為好。

最近在做項(xiàng)目的時(shí)候，遇到了一些大數(shù)據(jù)量的操作，有大批量的CRUD的操作，一開(kāi)始的實(shí)現(xiàn)的方案經(jīng)過(guò)性能測(cè)試，發(fā)現(xiàn)性能并不是很好，然后開(kāi)始審查代碼，對(duì)相關(guān)可以提升性能的操作進(jìn)行了優(yōu)化，這里分享給大家。

首先我這里不講索引相關(guān)的內(nèi)容以及數(shù)據(jù)庫(kù)相應(yīng)參數(shù)的優(yōu)化，這里假設(shè)你對(duì)索引已經(jīng)有了相關(guān)的了解了，我總結(jié)了下我這次的優(yōu)化，主要兩個(gè)原則：

一些特定的場(chǎng)景，盡量用批處理處理數(shù)據(jù)，比如批量添加數(shù)據(jù)，批量修改數(shù)據(jù)；

結(jié)合業(yè)務(wù)盡量減少SQL的執(zhí)行次數(shù)和查詢不必要的數(shù)據(jù)；

為模擬運(yùn)行場(chǎng)景，我這里建了一個(gè)表，并往里面添加了300w條數(shù)據(jù)，表結(jié)構(gòu)如下：

CREATE TABLE `tb_big_data` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `weixin_id` varchar(64) NOT NULL,
 `openid` varchar(64) NOT NULL,
 `status` int(3) NOT NULL,
 `gmt_create` datetime NOT NULL,
 `gmt_modified` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `weixin_id_gmt_create_openid` (`weixin_id`,`gmt_create`,`openid`)
) ENGINE=InnoDB AUTO_INCREMENT DEFAULT CHARSET=utf8

分頁(yè)查詢老生常談，網(wǎng)上各種優(yōu)化方法都很多，這里就不提及了，這里只是分享一個(gè)小技巧：

如何在使用最普通的limit的時(shí)候提高性能？

假設(shè)我們現(xiàn)在有一條這樣的SQL：

SELECT * FROM `tb_big_data` where weixin_id ="gh_266a30a8a1f6" and gmt_create > "2017-10-10 00:00:00" order by id asc limit 800000, 100;

執(zhí)行時(shí)間：100 rows in set (1.53 sec)

假如我們現(xiàn)在不能進(jìn)行其他優(yōu)化，比如傳入最小id，分表查詢等策略，以及不進(jìn)行SQL預(yù)熱，怎么提高這條SQL的速度呢？
其實(shí)很簡(jiǎn)單我們只需要一個(gè)in操作即可：

SELECT * FROM `tb_big_data` t1 where t1.id in ( 
    SELECT tt.id FROM ( 
        SELECT id FROM `tb_big_data` t2 where weixin_id = "gh_266a30a8a1f6" and gmt_create > "2017-10-10 00:00:00" order by t2.id asc limit 800100, 100
        ) as tt);

執(zhí)行時(shí)間：100 rows in set (1.17 sec)

可以看出只需稍加修改，SQL的效率可以提高30%~40%，而且在單條數(shù)據(jù)記錄越大的情況下效果越好，當(dāng)然這不是最好的分頁(yè)方法，這只是一個(gè)小技巧；

現(xiàn)在有一個(gè)需求我們現(xiàn)在有一個(gè)用戶的列表（用戶的唯一標(biāo)識(shí)為openid）然后我們需要判斷用戶在當(dāng)天是否有過(guò)相應(yīng)的記錄；

這是問(wèn)題其實(shí)很簡(jiǎn)單，我們首先一想到的操作就是循環(huán)這個(gè)列表一個(gè)一個(gè)判斷，很簡(jiǎn)單也很好實(shí)現(xiàn)，但是真正測(cè)試的時(shí)候發(fā)現(xiàn)性能卻很差，尤其在數(shù)據(jù)量大的情況下，倍數(shù)級(jí)增長(zhǎng)，這里有有網(wǎng)絡(luò)數(shù)據(jù)傳輸消耗的時(shí)間和SQL本身的執(zhí)行時(shí)間；

假設(shè)我們現(xiàn)在執(zhí)行一條以下的SQL：

SELECT * FROM `tb_big_data` WHERE weixin_id ="gh_266a30a8a1f6" and gmt_create > "2017-10-13 00:00:00" and openid="2n6bvynihm5bzgyx";

執(zhí)行時(shí)間：1 row in set (0.95 sec)

現(xiàn)在如果我們執(zhí)行100次，不敢想象會(huì)是什么情況，慶幸自己發(fā)現(xiàn)了這個(gè)問(wèn)題，因?yàn)樵跀?shù)據(jù)量少的情況下，這個(gè)問(wèn)題表現(xiàn)的并不是那么嚴(yán)重，其實(shí)我們稍加改變就能以另一種高效的方式解決這個(gè)問(wèn)題：

SELECT * FROM `tb_big_data` WHERE weixin_id ="gh_266a30a8a1f6" and gmt_create > "2017-10-13 00:00:00" and openid in ("2n6bvynihm5bzgyx","1stbvdnl63de2q37","3z8552gxzfi3wy27"...);

執(zhí)行時(shí)間：100 row in set (1.05 sec)

發(fā)現(xiàn)了沒(méi)有，還是用in，而且執(zhí)行時(shí)間幾乎與單條查詢的時(shí)間一樣，可見(jiàn)只是單一這一部分處理就可以提升了很大的性能。

這個(gè)跟上一點(diǎn)有一個(gè)相似點(diǎn)，那就是減少SQL執(zhí)行，上面只是查詢而已，而當(dāng)出現(xiàn)大批量的CUD的操作時(shí)，執(zhí)行每條SQL，數(shù)據(jù)庫(kù)都會(huì)進(jìn)行事務(wù)處理，這將會(huì)消耗大量的時(shí)間，而且極端情況下會(huì)引起大批量SQL等待無(wú)法執(zhí)行，導(dǎo)致業(yè)務(wù)出錯(cuò)，正是因?yàn)檫@些原因，我們?cè)谝恍┻m當(dāng)?shù)那闆r下可以使用批處理來(lái)解決這個(gè)問(wèn)題。

（1）批量插入

批量插入比較簡(jiǎn)單，也比較常用，這里就給一下基本語(yǔ)法：

INSERT INTO table_name (field1,filed2,...) values (value11,value12,...),(value21,value22,...),...

（2）批量更新

我先舉個(gè)簡(jiǎn)單的例子，我們現(xiàn)在來(lái)根據(jù)一些條件來(lái)更新數(shù)據(jù)，具體SQL如下：

update `tb_big_data` set status = 2 WHERE weixin_id ="gh_266a30a8a1f6" and gmt_create > "2017-10-13 00:00:00" and openid = "2n6bvynihm5bzgyx";

Query OK, 1 row affected (2.28 sec)
Rows matched: 1  Changed: 1  Warnings: 0

很驚訝，我們只是更新了一條記錄，而且更新條件上是有復(fù)合索引的，沒(méi)想到速度還那么慢，可以想象如果我們批量更新數(shù)據(jù)，那得耗時(shí)多少；

但是我們看一下另一條SQL：

update `tb_big_data` set status = 1 WHERE id = 900098;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

上面的id值為之前條件篩選出來(lái)的記錄的id，是不是很驚訝，怎么這條SQL執(zhí)行的時(shí)間幾乎不需要什么時(shí)間，所以我們可以利用這個(gè)特點(diǎn)和批量查詢簡(jiǎn)化批量更新，雖然這種方式不能讓性能到最優(yōu)，但是也能提升很大了，我進(jìn)行了一個(gè)測(cè)試，根據(jù)相應(yīng)條件批量更新100條數(shù)據(jù)：

可以看出這種方式相對(duì)對(duì)于普通方式來(lái)說(shuō)，性能提升巨大，具體執(zhí)行的時(shí)候我們也可以將這些SQL放在一個(gè)事務(wù)提交，減少數(shù)據(jù)庫(kù)事務(wù)次數(shù)，但只這是一種在代碼層面上的優(yōu)化；

另外我們可以利用MySQL提供的特殊語(yǔ)法進(jìn)行批量更新，具體語(yǔ)法為：

#語(yǔ)法
INSERT INTO table_name (id,field1,field2,...) VALUES  (id1,value11,value12,...),(id1,value11,value12,...),... on duplicate key update  field = VAULES(field);

#使用例子

INSERT INTO `tb_big_data` (id,weixin_id,openid,gmt_create,status) values  (1,"gh_266a30a8a1f6","w9q8fmodytjgppsr","2017-10-13 12:00:00",3),(2,"gh_266a30a8a1f6","bu1flmch4i8eegzf","2017-10-13 12:00:00",3) on duplicate key update status = VAULES(status);

經(jīng)過(guò)測(cè)試這種方式在數(shù)據(jù)量小的情況下與上述方式效率差不多，但是隨著數(shù)據(jù)量越來(lái)越大，性能也越來(lái)越好，缺點(diǎn)的話主要傳輸?shù)臄?shù)據(jù)量很大，不需要更新的字段也需要傳輸。

另外也不推薦大量數(shù)據(jù)的批量更新，一次不要超過(guò)1000條為好。

總的來(lái)說(shuō)，SQL優(yōu)化是一門(mén)細(xì)心的學(xué)問(wèn)，需要不斷去嘗試，測(cè)試，找到最優(yōu)方式，另外還有一點(diǎn)就是要結(jié)合實(shí)際情況，綜合考慮選擇合適的方式。