MySQL幾個簡單SQL的優化

三分青年發表於2017-10-18
MySql優化

最近在做專案的時候，遇到了一些大資料量的操作，有大批量的CRUD的操作，一開始的實現的方案經過效能測試，發現效能並不是很好，然後開始審查程式碼，對相關可以提升效能的操作進行了優化，這裡分享給大家。

原則

首先我這裡不講索引相關的內容以及資料庫相應引數的優化，這裡假設你對索引已經有了相關的瞭解了，我總結了下我這次的優化，主要兩個原則：

  • 一些特定的場景，儘量用批處理處理資料，比如批量新增資料，批量修改資料；
  • 結合業務儘量減少SQL的執行次數和查詢不必要的資料；

場景實踐

為模擬執行場景，我這裡建了一個表，並往裡面新增了300w條資料，表結構如下：

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

1.分頁查詢小優化

分頁查詢老生常談，網上各種優化方法都很多，這裡就不提及了，這裡只是分享一個小技巧：

如何在使用最普通的limit的時候提高效能？

假設我們現在有一條這樣的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;

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

假如我們現在不能進行其他優化，比如傳入最小id，分表查詢等策略，以及不進行SQL預熱，怎麼提高這條SQL的速度呢？
其實很簡單我們只需要一個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);

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

可以看出只需稍加修改，SQL的效率可以提高30%~40%，而且在單條資料記錄越大的情況下效果越好，當然這不是最好的分頁方法，這只是一個小技巧；

2.減少SQL查詢

現在有一個需求我們現在有一個使用者的列表（使用者的唯一標識為openid）然後我們需要判斷使用者在當天是否有過相應的記錄；

這是問題其實很簡單，我們首先一想到的操作就是迴圈這個列表一個一個判斷，很簡單也很好實現，但是真正測試的時候發現效能卻很差，尤其在資料量大的情況下，倍數級增長，這裡有有網路資料傳輸消耗的時間和SQL本身的執行時間；

假設我們現在執行一條以下的SQL：

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

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

現在如果我們執行100次，不敢想象會是什麼情況，慶幸自己發現了這個問題，因為在資料量少的情況下，這個問題表現的並不是那麼嚴重，其實我們稍加改變就能以另一種高效的方式解決這個問題：

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`...);

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

發現了沒有，還是用in，而且執行時間幾乎與單條查詢的時間一樣，可見只是單一這一部分處理就可以提升了很大的效能。

3.特定場景使用SQL的批處理

這個跟上一點有一個相似點，那就是減少SQL執行，上面只是查詢而已，而當出現大批量的CUD的操作時，執行每條SQL，資料庫都會進行事務處理，這將會消耗大量的時間，而且極端情況下會引起大批量SQL等待無法執行，導致業務出錯，正是因為這些原因，我們在一些適當的情況下可以使用批處理來解決這個問題。

（1）批量插入

批量插入比較簡單，也比較常用，這裡就給一下基本語法：

INSERT INTO table_name (field1,filed2,...) values (value11,value12,...),(value21,value22,...),...
（2）批量更新

我先舉個簡單的例子，我們現在來根據一些條件來更新資料，具體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

很驚訝，我們只是更新了一條記錄，而且更新條件上是有複合索引的，沒想到速度還那麼慢，可以想象如果我們批量更新資料，那得耗時多少；

但是我們看一下另一條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值為之前條件篩選出來的記錄的id，是不是很驚訝，怎麼這條SQL執行的時間幾乎不需要什麼時間，所以我們可以利用這個特點和批量查詢簡化批量更新，雖然這種方式不能讓效能到最優，但是也能提升很大了，我進行了一個測試，根據相應條件批量更新100條資料：

方式 直接批量更新 先批量查主鍵再批量更新
耗時 289.12s 1.342s

可以看出這種方式相對對於普通方式來說，效能提升巨大，具體執行的時候我們也可以將這些SQL放在一個事務提交，減少資料庫事務次數，但只這是一種在程式碼層面上的優化；

另外我們可以利用MySQL提供的特殊語法進行批量更新，具體語法為：

#語法
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);

經過測試這種方式在資料量小的情況下與上述方式效率差不多，但是隨著資料量越來越大，效能也越來越好，缺點的話主要傳輸的資料量很大，不需要更新的欄位也需要傳輸。

另外也不推薦大量資料的批量更新，一次不要超過1000條為好。

總結

總的來說，SQL優化是一門細心的學問，需要不斷去嘗試，測試，找到最優方式，另外還有一點就是要結合實際情況，綜合考慮選擇合適的方式。

相關文章