批量插入優化
在網上找了一些插入大量資料性能優化資料，提到了比較重要的一點是将
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條記錄
<?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。如果沒有，則為空。
二，删除一半資料
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檔案的大小
182080 ad_visit_history.myd //資料檔案差不多為優化前的一半
66024 ad_visit_history.myi //索引檔案也一樣，差不多是優化前的一半
2，檢視一下索引資訊
| ad_visit_history | 1 | ad_code | 1 | ad_code | a | 42 | 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_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會鎖定表。
![Java String.format方法的簡單使用[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)