大批量插入數據，卻不會優化怎么辦

1、大批量插入數據優化

（1）對于MyISAM存儲引擎的表，可以使用：DISABLE KEYS 和 ENABLE KEYS 用來打開或者關閉 MyISAM 表非唯一索引的更新。

ALTER TABLE tbl_name DISABLE KEYS;

loading the data

ALTER TABLE tbl_name ENABLE KEYS;

（2）對于InnoDB引擎，有以下幾種優化措施：

① 導入的數據按照主鍵的順序保存：這是因為InnoDB引擎表示按照主鍵順序保存的，如果能將插入的數據提前按照排序好自然能省去很多時間。

比如bulk_insert.txt文件是以表user主鍵的順序存儲的，導入的時間為15.23秒

mysql》 load data infile ‘mysql/bulk_insert.txt’ into table user;

Query OK， 126732 rows affected （15.23 sec）

Records： 126732 Deleted： 0 Skipped： 0 Warnings： 0

沒有按照主鍵排序的話，時間為：26.54秒

mysql》 load data infile ‘mysql/bulk_insert.txt’ into table user;

Query OK， 126732 rows affected （26.54 sec）

Records： 126732 Deleted： 0 Skipped： 0 Warnings： 0

② 導入數據前執行SET UNIQUE_CHECKS=0，關閉唯一性校驗，帶導入之后再打開設置為1：校驗會消耗時間，在數據量大的情況下需要考慮。

③ 導入前設置SET AUTOCOMMIT=0，關閉自動提交，導入后結束再設置為1：這是因為自動提交會消耗部分時間與資源，雖然消耗不是很大，但是在數據量大的情況下還是得考慮。

2、INSERT的優化

（1）盡量使用多個值表的 INSERT 語句，這種方式將大大縮減客戶端與數據庫之間的連接、關閉等消耗。（同一客戶的情況下），即：

INSERT INTO tablename values（1，2），（1，3），（1，4）

實驗：插入8條數據到user表中（使用navicat客戶端工具）

insert into user values（1，‘test’，replace（uuid（），‘-’，‘’））;

insert into user values（2，‘test’，replace（uuid（），‘-’，‘’））;

insert into user values（3，‘test’，replace（uuid（），‘-’，‘’））;

insert into user values（4，‘test’，replace（uuid（），‘-’，‘’））;

insert into user values（5，‘test’，replace（uuid（），‘-’，‘’））;

insert into user values（6，‘test’，replace（uuid（），‘-’，‘’））;

insert into user values（7，‘test’，replace（uuid（），‘-’，‘’））;

insert into user values（8，‘test’，replace（uuid（），‘-’，‘’））;

得到反饋：

［SQL］ insert into user values（1，‘test’，replace（uuid（），‘-’，‘’））;

受影響的行： 1

時間： 0.033s

［SQL］

insert into user values（2，‘test’，replace（uuid（），‘-’，‘’））;

受影響的行： 1

時間： 0.034s

［SQL］

insert into user values（3，‘test’，replace（uuid（），‘-’，‘’））;

受影響的行： 1

時間： 0.056s

［SQL］

insert into user values（4，‘test’，replace（uuid（），‘-’，‘’））;

受影響的行： 1

時間： 0.008s

［SQL］

insert into user values（5，‘test’，replace（uuid（），‘-’，‘’））;

受影響的行： 1

時間： 0.008s

［SQL］

insert into user values（6，‘test’，replace（uuid（），‘-’，‘’））;

受影響的行： 1

時間： 0.024s

［SQL］

insert into user values（7，‘test’，replace（uuid（），‘-’，‘’））;

受影響的行： 1

時間： 0.004s

［SQL］

insert into user values（8，‘test’，replace（uuid（），‘-’，‘’））;

受影響的行： 1

時間： 0.004s

總共的時間為0.171秒，接下來使用多值表形式：

insert into user values

（9，‘test’，replace（uuid（），‘-’，‘’）），

（10，‘test’，replace（uuid（），‘-’，‘’）），

（11，‘test’，replace（uuid（），‘-’，‘’）），

（12，‘test’，replace（uuid（），‘-’，‘’）），

（13，‘test’，replace（uuid（），‘-’，‘’）），

（14，‘test’，replace（uuid（），‘-’，‘’）），

（15，‘test’，replace（uuid（），‘-’，‘’）），

（16，‘test’，replace（uuid（），‘-’，‘’））;

得到反饋：

［SQL］ insert into user values

（9，‘test’，replace（uuid（），‘-’，‘’）），

（10，‘test’，replace（uuid（），‘-’，‘’）），

（11，‘test’，replace（uuid（），‘-’，‘’）），

（12，‘test’，replace（uuid（），‘-’，‘’）），

（13，‘test’，replace（uuid（），‘-’，‘’）），

（14，‘test’，replace（uuid（），‘-’，‘’）），

（15，‘test’，replace（uuid（），‘-’，‘’）），

（16，‘test’，replace（uuid（），‘-’，‘’））;

受影響的行： 8

時間： 0.038s

得到時間為0.038，這樣一來可以很明顯節約時間優化SQL

（2）如果在不同客戶端插入很多行，可使用INSERT DELAYED語句得到更高的速度，DELLAYED含義是讓INSERT語句馬上執行，其實數據都被放在內存的隊列中。并沒有真正寫入磁盤。LOW_PRIORITY剛好相反。

（3）將索引文件和數據文件分在不同的磁盤上存放（InnoDB引擎是在同一個表空間的）。

（4）如果批量插入，則可以增加bluk_insert_buffer_size變量值提供速度（只對MyISAM有用）

（5）當從一個文本文件裝載一個表時，使用LOAD DATA INFILE，通常比INSERT語句快20倍。

3、GROUP BY的優化

在默認情況下，MySQL中的GROUP BY語句會對其后出現的字段進行默認排序（非主鍵情況），就好比我們使用ORDER BY col1，col2，col3…所以我們在后面跟上具有相同列（與GROUP BY后出現的col1，col2，col3…相同）ORDER BY子句并沒有影響該SQL的實際執行性能。

那么就會有這樣的情況出現，我們對查詢到的結果是否已經排序不在乎時，可以使用ORDER BY NULL禁止排序達到優化目的。下面使用EXPLAIN命令分析SQL。Java知音公眾號內回復“面試題聚合”，送你一份面試題寶典

在user_1中執行select id， sum（money） form user_1 group by name時，會默認排序（注意group by后的column是非index才會體現group by的排序，如果是primary key，那之前說過了InnoDB默認是按照主鍵index排好序的）

mysql》 select*from user_1;

+----+----------+-------+

| id | name | money |

+----+----------+-------+

| 1 | Zhangsan | 32 |

| 2 | Lisi | 65 |

| 3 | Wangwu | 44 |

| 4 | Lijian | 100 |

+----+----------+-------+

4 rows in set

不禁止排序，即不使用ORDER BY NULL時：有明顯的Using filesort。

當使用ORDER BY NULL禁止排序后，Using filesort不存在

4、ORDER BY 的優化

MySQL可以使用一個索引來滿足ORDER BY 子句的排序，而不需要額外的排序，但是需要滿足以下幾個條件：

（1）WHERE 條件和OREDR BY 使用相同的索引：即key_part1與key_part2是復合索引，where中使用復合索引中的key_part1

SELECT*FROM user WHERE key_part1=1 ORDER BY key_part1 DESC， key_part2 DESC;

（2）而且ORDER BY順序和索引順序相同：

SELECT*FROM user ORDER BY key_part1， key_part2;

（3）并且要么都是升序要么都是降序：

SELECT*FROM user ORDER BY key_part1 DESC， key_part2 DESC;

但以下幾種情況則不使用索引：

（1）ORDER BY中混合ASC和DESC：

SELECT*FROM user ORDER BY key_part1 DESC， key_part2 ASC;

（2）查詢行的關鍵字與ORDER BY所使用的不相同，即WHERE 后的字段與ORDER BY 后的字段是不一樣的

SELECT*FROM user WHERE key2 = ‘xxx’ ORDER BY key1;

（3）ORDER BY對不同的關鍵字使用，即ORDER BY后的關鍵字不相同

SELECT*FROM user ORDER BY key1， key2;

5、OR的優化

當MySQL使用OR查詢時，如果要利用索引的話，必須每個條件列都使獨立索引，而不是復合索引（多列索引），才能保證使用到查詢的時候使用到索引。

比如我們新建一張用戶信息表user_info

mysql》 select*from user_info;

+---------+--------+----------+-----------+

+---------+--------+----------+-----------+

| 1 | 111111 | Zhangsan | Kunming |

| 2 | 222222 | Lisi | Beijing |

| 3 | 333333 | Wangwu | Shanghai |

| 4 | 444444 | Lijian | Guangzhou |

+---------+--------+----------+-----------+

4 rows in set

之后創建ind_name_id（user_id， name）復合索引、id_index（id_index）獨立索引，idcard主鍵索引三個索引。

mysql》 show index from user_info;

+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| user_info | 1 | ind_name_id | 2 | name | A | 4 | NULL | NULL | YES | BTREE | | |

+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

4 rows in set

測試一：OR連接兩個有單獨索引的字段，整個SQL查詢才會用到索引（index_merge），并且我們知道OR實際上是把每個結果最后UNION一起的。

mysql》 explain select*from user_info where user_id=1 or idcard=‘222222’;

+----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+

1 row in set

測試二：OR使用復合索引的字段name，與沒有索引的address，整個SQL都是ALL全表掃描的

mysql》 explain select*from user_info where name=‘Zhangsan’ or address=‘Beijing’;

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set

交換OR位置并且使用另外的復合索引的列，也是ALL全表掃描：

mysql》 explain select*from user_info where address=‘Beijing’ or user_id=1;

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

1 row in set

6、優化嵌套查詢

使用嵌套查詢有時候可以使用更有效的JOIN連接代替，這是因為MySQL中不需要在內存中創建臨時表完成SELECT子查詢與主查詢兩部分查詢工作。但是并不是所有的時候都成立，最好是在on關鍵字后面的列有索引的話，效果會更好！

比如在表major中major_id是有索引的：

select * from student u left join major m on u.major_id=m.major_id where m.major_id is null;

而通過嵌套查詢時，在內存中創建臨時表完成SELECT子查詢與主查詢兩部分查詢工作，會有一定的消耗

select * from student u where major_id not in （select major_id from major）;

7、使用SQL提示

SQL提示（SQL HINT）是優化數據庫的一個重要手段，就是往SQL語句中加入一些人為的提示來達到優化目的。下面是一些常用的SQL提示：

（1）USE INDEX：使用USE INDEX是希望MySQL去參考索引列表，就可以讓MySQL不需要考慮其他可用索引，其實也就是possible_keys屬性下參考的索引值

mysql》 explain select* from user_info use index（id_index，ind_name_id） where user_id》0;

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

1 row in set

mysql》 explain select* from user_info use index（id_index） where user_id》0;

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set

（2）IGNORE INDEX忽略索引

我們使用user_id判斷，用不到其他索引時，可以忽略索引。即與USE INDEX相反，從possible_keys中減去不需要的索引，但是實際環境中很少使用。

mysql》 explain select* from user_info ignore index（primary，ind_name_id，id_index） where user_id》0;

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set

（3）FORCE INDEX強制索引

比如where user_id 》 0，但是user_id在表中都是大于0的，自然就會進行ALL全表搜索，但是使用FORCE INDEX雖然執行效率不是最高（where user_id 》 0條件決定的）但MySQL還是使用索引。

mysql》 explain select* from user_info where user_id》0;

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

1 row in set

之后強制使用獨立索引id_index（user_id）：

mysql》 explain select* from user_info force index（id_index） where user_id》0;

+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

1 row in set

總結

（1）很多時候數據庫的性能是由于不合適（是指效率不高，可能會導致鎖表等）的SQL語句造成，本篇博文只是介紹簡單的SQL優化

（2）其中有些優化在真正開發中是用不到的，但是一旦出問題性能下降的時候需要去一一分析。

閱讀全文

SQL(46480) SQL(46480)
數據庫(68096) 數據庫(68096)

搜索歷史

大批量插入數據，卻不會優化怎么辦

評論