MYSQL 大資料效能優化

航空母艦發表於2016-03-21
MySql大資料優化

批量插入優化

在網上找了一些插入大量資料效能優化資料，提到了比較重要的一點是將

insert into tablename(f1,f2,...) values (d1,d2,...);
insert into tablename(f1,f2,...) values (d1,d2,...);
...

 這樣的單條單條的insert語句改造成

insert into tablename(f1,f2,...) values (d1,d2,...),(d1,d2,...),(d1,d2,...);

這種一次insert多條記錄，效能會提升比較明顯，所以我就開始試驗這種方法，將每條記錄在程式碼裡迴圈拼接成一條原生insert語句再進行插入（想想感覺可行性很高），拼接完成後依然繼續插入五萬條資料，拼接出來的sql語句就成了

insert into tablename(f1,f2,...) values (d1,d2,...),(d1,d2,...),(d1,d2,...)...;//此處省略了49997條記錄

瀏覽器執行插入資料的頁面，bong...，提示Mysql server has gone away!，mysql崩潰了。蛋疼~！然後尋思著將這五萬條資料分批次進行插入，這樣就不會產生資料庫崩潰的情況，所以我將這五萬條資料按照五千個一組分批插入，最後再執行這個頁面，bong...五萬條資料兩秒之內就給全部插入進去了，兩秒。。(這裡已經去掉了前面加上的 ini_set('memory_limit','1024M');)效率跟之前比提高了幾十倍，瞬間感覺整個人都變好了。又試了再插入三萬條資料，1秒 之內搞定。下面貼出部分參考程式碼

<?php
//下面是大於5000條資料拼接演算法
$chu = floor($count / 5000); //取整
for ($i = 0; $i < $chu; $i++) {
    //每5000條資料組成一個insert語句,$codeModel是存放記錄的一個陣列
    $values = '';
    for ($j = $i * 5000; $j < ($i + 1) * 5000; $j++) {
        //拼接values的值
        $values .= '(' . $codeModel[$j]['rid'] . ',' . $codeModel[$j]['cid'] . ',"' . $codeModel[$j]['regcode'] . '",0,' . $codeModel[$j]['status'] . ',0,' . time() . '),';
    }
    $values = "insert into w_code (rid,cid,regcode,used_times,status,reason_id,created_at) values" . substr($values, 0, -1) . ';';
    Yii::$app->db->createCommand($values)->execute();
}
//下面是小於5000條資料拼接演算法
$yu = $count % 5000; //取餘
for ($k = 0; $k < $yu; $k++) {
    //echo "k:" . $k . "<br/>";
}

另外，這些程式碼外層都放了事務回滾的！將多條insert放入事務中也會提升一點資料插入的效能！

 

將資料放到檔案中，使用load data infile

select * into outfile 'ddd.txt' fields terminated by ',' from dn_location;
load data infile 'ddd.txt' into table dn_location2  FIELDS TERMINATED BY ',';

通過該方法匯出的資料，是將各欄位（只有資料，不匯出表結構）資料存在一個檔案中，中間以逗號分隔，因為檔案中並不包含資料庫名或者表名，因此需要在匯入匯出的時候些明確。該方法在18分鐘內匯出1.6億條記錄，46min內匯入6472W條記錄，平均速度：8442W條/h。mysql官方文件也說明了，該方法比一次性插入一條資料效能快20倍。


刪除表大量資料

假設有一個表(syslogs)有1000萬條記錄，需要在業務不停止的情況下刪除其中statusid=1的所有記錄，差不多有600萬條，直接執行

DELETE FROM syslogs WHERE statusid=1

會發現刪除失敗，因為lock wait timeout exceed的錯誤。因為這條語句所涉及的記錄數太多，因此我們通過LIMIT引數分批刪除，比如每10000條進行一次刪除，那麼我們可以利用 MySQL這樣的語句來完成

DELETE FROM syslogs WHERE status=1 ORDER BY statusid LIMIT 10000;

然後分多次執行就可以把這些記錄成功刪除。

delete命令根本不會回收空間，也就是說之前假如這個檔案佔了100G ,delete後，檔案大小沒有改變。當全表掃描的時候，還是掃這麼多的資料塊。當執行完alter table 命令後，它會回收空間。假如刪了80G,它的物理檔案會只佔20G空間。

alter table table_name engine=innodb;

當您的庫中刪除了大量的資料後，您可能會發現資料檔案尺寸並沒有減小。這是因為刪除操作後在資料檔案中留下碎片所致。OPTIMIZE TABLE 是指對錶進行優化。如果已經刪除了表的一大部分資料，或者如果已經對含有可變長度行的表（含有 VARCHAR 、 BLOB 或 TEXT 列的表）進行了很多更改，就應該使用 OPTIMIZE TABLE 命令來進行表優化。這個命令可以將表中的空間碎片進行合併，並且可以消除由於刪除或者更新造成的空間浪費 。 OPTIMIZE TABLE 命令只對 MyISAM 、 BDB 和 InnoDB 表起作用 。表優化的工作可以每週或者每月定期執行，對提高表的訪問效率有一定的好處，但是需要注意的是，優化表期間會鎖定表，所以一定要安排在空閒時段進行。

一，原始資料

mysql> select count(*) as total from ad_visit_history;
+---------+
| total   |
+---------+
| 1187096 |                      //總共有118萬多條資料
+---------+
1 row in set (0.04 sec)

 2，存放在硬碟中的表檔案大小

[root@BlackGhost test1]# ls |grep visit |xargs -i du {}
382020    ad_visit_history.MYD                    //資料檔案佔了380M
127116    ad_visit_history.MYI                     //索引檔案佔了127M
12    ad_visit_history.frm                              //結構檔案佔了12K

 3，檢視一下索引資訊

mysql> show index from ad_visit_history from test1;     //檢視一下該表的索引資訊
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| ad_visit_history |          0 | PRIMARY           |            1 | id            | A         |     1187096 |     NULL | NULL   |      | BTREE      |         |
| ad_visit_history |          1 | ad_code           |            1 | ad_code       | A         |          46 |     NULL | NULL   | YES  | BTREE      |         |
| ad_visit_history |          1 | unique_id         |            1 | unique_id     | A         |     1187096 |     NULL | NULL   | YES  | BTREE      |         |
| ad_visit_history |          1 | ad_code_ind       |            1 | ad_code       | A         |          46 |     NULL | NULL   | YES  | BTREE      |         |
| ad_visit_history |          1 | from_page_url_ind |            1 | from_page_url | A         |       30438 |     NULL | NULL   | YES  | BTREE      |         |
| ad_visit_history |          1 | ip_ind            |            1 | ip            | A         |      593548 |     NULL | NULL   | YES  | BTREE      |         |
| ad_visit_history |          1 | port_ind          |            1 | port          | A         |       65949 |     NULL | NULL   | YES  | BTREE      |         |
| ad_visit_history |          1 | session_id_ind    |            1 | session_id    | A         |     1187096 |     NULL | NULL   | YES  | BTREE      |         |
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.28 sec)

索引資訊中的列的資訊說明。

Table :表的名稱。
Non_unique :如果索引不能包括重複詞，則為0。如果可以，則為1。
Key_name :索引的名稱。
Seq_in_index :索引中的列序列號，從1開始。
Column_name :列名稱。
Collation :列以什麼方式儲存在索引中。在MySQLSHOW INDEX語法中，有值’A’（升序）或NULL（無分類）。
Cardinality :索引中唯一值的數目的估計值。通過執行ANALYZE TABLE或myisamchk -a可以更新。基數根據被儲存為整數的統計資料來計數，所以即使對於小型表，該值也沒有必要是精確的。基數越大，當進行聯合時，MySQL使用該索引的機會就越大。
Sub_part :如果列只是被部分地編入索引，則為被編入索引的字元的數目。如果整列被編入索引，則為NULL。
Packed :指示關鍵字如何被壓縮。如果沒有被壓縮，則為NULL。
Null :如果列含有NULL，則含有YES。如果沒有，則為空。
Index_type ：儲存索引資料結構方法（BTREE, FULLTEXT, HASH, RTREE）

二，刪除一半資料

mysql> delete from ad_visit_history where id>598000;          //刪除一半資料
Query OK, 589096 rows affected (4 min 28.06 sec)

[root@BlackGhost test1]# ls |grep visit |xargs -i du {}              //相對應的MYD，MYI檔案大小沒有變化
382020    ad_visit_history.MYD 
127116    ad_visit_history.MYI
12    ad_visit_history.frm

按常規思想來說，如果在資料庫中刪除了一半資料後，相對應的.MYD,.MYI檔案也應當變為之前的一半。但是刪除一半資料後，.MYD.MYI盡然連1KB都沒有減少 ，這是多麼的可怕啊。

我們在來看一看，索引資訊

mysql> show index from ad_visit_history;  
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+  
| Table            | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |  
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+  
| ad_visit_history |          0 | PRIMARY           |            1 | id            | A         |      598000 |     NULL | NULL   |      | BTREE      |         |  
| ad_visit_history |          1 | ad_code           |            1 | ad_code       | A         |          23 |     NULL | NULL   | YES  | BTREE      |         |  
| ad_visit_history |          1 | unique_id         |            1 | unique_id     | A         |      598000 |     NULL | NULL   | YES  | BTREE      |         |  
| ad_visit_history |          1 | ad_code_ind       |            1 | ad_code       | A         |          23 |     NULL | NULL   | YES  | BTREE      |         |  
| ad_visit_history |          1 | from_page_url_ind |            1 | from_page_url | A         |       15333 |     NULL | NULL   | YES  | BTREE      |         |  
| ad_visit_history |          1 | ip_ind            |            1 | ip            | A         |      299000 |     NULL | NULL   | YES  | BTREE      |         |  
| ad_visit_history |          1 | port_ind          |            1 | port          | A         |       33222 |     NULL | NULL   | YES  | BTREE      |         |  
| ad_visit_history |          1 | session_id_ind    |            1 | session_id    | A         |      598000 |     NULL | NULL   | YES  | BTREE      |         |  
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+  
8 rows in set (0.00 sec)

對比一下，這次索引查詢和上次索引查詢，裡面的資料資訊基本上是上次一次的一本，這點還是合乎常理。

三，用optimize table來優化一下

mysql> optimize table ad_visit_history;                                             //刪除資料後的優化
+------------------------+----------+----------+----------+
| Table                  | Op       | Msg_type | Msg_text |
+------------------------+----------+----------+----------+
| test1.ad_visit_history | optimize | status   | OK       |
+------------------------+----------+----------+----------+
1 row in set (1 min 21.05 sec)

 1，檢視一下.MYD,.MYI檔案的大小

[root@BlackGhost test1]# ls |grep visit |xargs -i du {}
182080    ad_visit_history.MYD                                          //資料檔案差不多為優化前的一半
66024    ad_visit_history.MYI                                             //索引檔案也一樣，差不多是優化前的一半
12    ad_visit_history.frm

 2，檢視一下索引資訊

mysql> show index from ad_visit_history;  
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+  
| Table            | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |  
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+  
| ad_visit_history |          0 | PRIMARY           |            1 | id            | A         |      598000 |     NULL | NULL   |      | BTREE      |         |  
| ad_visit_history |          1 | ad_code           |            1 | ad_code       | A         |          42 |     NULL | NULL   | YES  | BTREE      |         |  
| ad_visit_history |          1 | unique_id         |            1 | unique_id     | A         |      598000 |     NULL | NULL   | YES  | BTREE      |         |  
| ad_visit_history |          1 | ad_code_ind       |            1 | ad_code       | A         |          42 |     NULL | NULL   | YES  | BTREE      |         |  
| ad_visit_history |          1 | from_page_url_ind |            1 | from_page_url | A         |       24916 |     NULL | NULL   | YES  | BTREE      |         |  
| ad_visit_history |          1 | ip_ind            |            1 | ip            | A         |      598000 |     NULL | NULL   | YES  | BTREE      |         |  
| ad_visit_history |          1 | port_ind          |            1 | port          | A         |       59800 |     NULL | NULL   | YES  | BTREE      |         |  
| ad_visit_history |          1 | session_id_ind    |            1 | session_id    | A         |      598000 |     NULL | NULL   | YES  | BTREE      |         |  
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+  
8 rows in set (0.00 sec)

從以上資料我們可以得出，ad_code，ad_code_ind，from_page_url_ind等索引機會差不多都提高了85％，這樣效率提高了好多。

四，小結

結合mysql官方網站的資訊，個人是這樣理解的。當你刪除資料 時，mysql並不會回收，被已刪除資料的佔據的儲存空間，以及索引位。而是空在那裡，而是等待新的資料來彌補這個空缺，這樣就有一個缺少，如果一時半 會，沒有資料來填補這個空缺，那這樣就太浪費資源了。所以對於寫比較頻煩的表，要定期進行optimize，一個月一次，看實際情況而定了。

舉個例子來說吧。有100個php程式設計師辭職了，但是呢只是人走了，php的職位還在那裡，這些職位不會撤銷，要等新的php程式來填補這些空位。招一個好的程式設計師，比較難。我想大部分時間會空在那裡。哈哈。

五，手冊中關於OPTIMIZE的一些用法和描述

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

如果您已經刪除了表的一大部分，或者如果您已經對含有可變長度行的表（含有VARCHAR, BLOB或TEXT列的表）進行了很多更改，則應使用OPTIMIZE TABLE。被刪除的記錄被保持在連結清單中，後續的INSERT操作會重新使用舊的記錄位置。您可以使用OPTIMIZE TABLE來重新利用未使用的空間，並整理資料檔案的碎片。在多數的設定中，您根本不需要執行OPTIMIZE TABLE。即使您對可變長度的行進行了大量的更新，您也不需要經常執行，每週一次或每月一次即可，只對特定的表執行。

OPTIMIZE TABLE只對MyISAM, BDB和InnoDB表起作用。注意，在OPTIMIZE TABLE執行過程中，MySQL會鎖定表。

 

 

相關文章