盤點(diǎn)開發(fā)中那些常用的MySQL優(yōu)化技巧

給SQL君加星標(biāo)★，每天好文不錯(cuò)過

SQL專欄

SQL數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)匯總SQL數(shù)據(jù)庫(kù)高級(jí)知識(shí)匯總

作者：jian

www.cnblogs.com/jian0110/p/9410981.html

Tips

最近公眾號(hào)改版，將信息流時(shí)間軸改成亂序，導(dǎo)致部分小伙伴找不到SQL君發(fā)布的的推文。如果覺得文章還可以，可以將我們加星標(biāo)，就不會(huì)找不到了。

1、大批量插入數(shù)據(jù)優(yōu)化

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

ALTER?TABLE?tbl_name?DISABLE?KEYS;
loading?the?data
ALTER?TABLE?tbl_name?ENABLE?KEYS;

（2）對(duì)于InnoDB引擎，有以下幾種優(yōu)化措施：

① 導(dǎo)入的數(shù)據(jù)按照主鍵的順序保存：這是因?yàn)镮nnoDB引擎表示按照主鍵順序保存的，如果能將插入的數(shù)據(jù)提前按照排序好自然能省去很多時(shí)間。

比如bulk_insert.txt文件是以表user主鍵的順序存儲(chǔ)的，導(dǎo)入的時(shí)間為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

沒有按照主鍵排序的話，時(shí)間為：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

② 導(dǎo)入數(shù)據(jù)前執(zhí)行SET UNIQUE_CHECKS=0，關(guān)閉唯一性校驗(yàn)，帶導(dǎo)入之后再打開設(shè)置為1：校驗(yàn)會(huì)消耗時(shí)間，在數(shù)據(jù)量大的情況下需要考慮。

③ 導(dǎo)入前設(shè)置SET AUTOCOMMIT=0，關(guān)閉自動(dòng)提交，導(dǎo)入后結(jié)束再設(shè)置為1：這是因?yàn)樽詣?dòng)提交會(huì)消耗部分時(shí)間與資源，雖然消耗不是很大，但是在數(shù)據(jù)量大的情況下還是得考慮。

2、INSERT的優(yōu)化

（1）盡量使用多個(gè)值表的 INSERT 語(yǔ)句，這種方式將大大縮減客戶端與數(shù)據(jù)庫(kù)之間的連接、關(guān)閉等消耗。（同一客戶的情況下），即：

INSERT?INTO?tablename?values(1,2),(1,3),(1,4)

實(shí)驗(yàn)：插入8條數(shù)據(jù)到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
時(shí)間:?0.033s
[SQL]?
insert?into?user?values(2,'test',replace(uuid(),'-',''));
受影響的行:?1
時(shí)間:?0.034s
[SQL]?
insert?into?user?values(3,'test',replace(uuid(),'-',''));
受影響的行:?1
時(shí)間:?0.056s
[SQL]?
insert?into?user?values(4,'test',replace(uuid(),'-',''));
受影響的行:?1
時(shí)間:?0.008s
[SQL]?
insert?into?user?values(5,'test',replace(uuid(),'-',''));
受影響的行:?1
時(shí)間:?0.008s
[SQL]?
insert?into?user?values(6,'test',replace(uuid(),'-',''));
受影響的行:?1
時(shí)間:?0.024s
[SQL]?
insert?into?user?values(7,'test',replace(uuid(),'-',''));
受影響的行:?1
時(shí)間:?0.004s
[SQL]?
insert?into?user?values(8,'test',replace(uuid(),'-',''));
受影響的行:?1
時(shí)間:?0.004s

總共的時(shí)間為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
時(shí)間:?0.038s

得到時(shí)間為0.038，這樣一來可以很明顯節(jié)約時(shí)間優(yōu)化SQL（2）如果在不同客戶端插入很多行，可使用INSERT DELAYED語(yǔ)句得到更高的速度，DELLAYED含義是讓INSERT語(yǔ)句馬上執(zhí)行，其實(shí)數(shù)據(jù)都被放在內(nèi)存的隊(duì)列中。并沒有真正寫入磁盤。LOW_PRIORITY剛好相反。（3）將索引文件和數(shù)據(jù)文件分在不同的磁盤上存放（InnoDB引擎是在同一個(gè)表空間的）。（4）如果批量插入，則可以增加bluk_insert_buffer_size變量值提供速度（只對(duì)MyISAM有用）（5）當(dāng)從一個(gè)文本文件裝載一個(gè)表時(shí)，使用LOAD DATA INFILE，通常比INSERT語(yǔ)句快20倍。

3、GROUP BY的優(yōu)化

在默認(rèn)情況下，MySQL中的GROUP BY語(yǔ)句會(huì)對(duì)其后出現(xiàn)的字段進(jìn)行默認(rèn)排序（非主鍵情況），就好比我們使用ORDER BY col1,col2,col3…所以我們?cè)诤竺娓暇哂邢嗤校ㄅcGROUP BY后出現(xiàn)的col1,col2,col3…相同）ORDER BY子句并沒有影響該SQL的實(shí)際執(zhí)行性能。那么就會(huì)有這樣的情況出現(xiàn)，我們對(duì)查詢到的結(jié)果是否已經(jīng)排序不在乎時(shí)，可以使用ORDER BY NULL禁止排序達(dá)到優(yōu)化目的。下面使用EXPLAIN命令分析SQL。Java知音公眾號(hào)內(nèi)回復(fù)“面試題聚合”，送你一份面試題寶典在user_1中執(zhí)行select id, sum(money) form user_1 group by name時(shí)，會(huì)默認(rèn)排序（注意group by后的column是非index才會(huì)體現(xiàn)group by的排序，如果是primary key，那之前說過了InnoDB默認(rèn)是按照主鍵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時(shí)：有明顯的Using filesort。

當(dāng)使用ORDER BY NULL禁止排序后，Using filesort不存在

4、ORDER BY 的優(yōu)化　　

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

（1）WHERE 條件和OREDR BY 使用相同的索引：即key_part1與key_part2是復(fù)合索引，where中使用復(fù)合索引中的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）查詢行的關(guān)鍵字與ORDER BY所使用的不相同，即WHERE 后的字段與ORDER BY 后的字段是不一樣的

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

（3）ORDER BY對(duì)不同的關(guān)鍵字使用，即ORDER BY后的關(guān)鍵字不相同

SELECT*FROM?user?ORDER?BY?key1,?key2;

5、OR的優(yōu)化

當(dāng)MySQL使用OR查詢時(shí)，如果要利用索引的話，必須每個(gè)條件列都使獨(dú)立索引，而不是復(fù)合索引（多列索引），才能保證使用到查詢的時(shí)候使用到索引。

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

mysql>?select*from?user_info;
+---------+--------+----------+-----------+
|?user_id?|?idcard?|?name?????|?address????|
+---------+--------+----------+-----------+
|???????1?|?111111?|?Zhangsan?|?Kunming???|
|???????2?|?222222?|?Lisi?????|?Beijing???|
|???????3?|?333333?|?Wangwu???|?Shanghai??|
|???????4?|?444444?|?Lijian???|?Guangzhou?|
+---------+--------+----------+-----------+
4?rows?in?set

之后創(chuàng)建ind_name_id(user_id, name)復(fù)合索引、id_index(id_index)獨(dú)立索引，idcard主鍵索引三個(gè)索引。

mysql>?show?index?from?user_info;
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|?Table?????|?Non_unique?|?Key_name????|?Seq_in_index?|?Column_name?|?Collation?|?Cardinality?|?Sub_part?|?Packed?|?Null?|?Index_type?|?Comment?|?Index_comment?|
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|?user_info?|??????????0?|?PRIMARY?????|????????????1?|?idcard??????|?A?????????|???????????4?|?NULL?????|?NULL???|??????|?BTREE??????|?????????|???????????????|
|?user_info?|??????????1?|?ind_name_id?|????????????1?|?user_id?????|?A?????????|???????????4?|?NULL?????|?NULL???|??????|?BTREE??????|?????????|???????????????|
|?user_info?|??????????1?|?ind_name_id?|????????????2?|?name????????|?A?????????|???????????4?|?NULL?????|?NULL???|?YES??|?BTREE??????|?????????|???????????????|
|?user_info?|??????????1?|?id_index????|????????????1?|?user_id?????|?A?????????|???????????4?|?NULL?????|?NULL???|??????|?BTREE??????|?????????|???????????????|
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4?rows?in?set

測(cè)試一：OR連接兩個(gè)有單獨(dú)索引的字段，整個(gè)SQL查詢才會(huì)用到索引(index_merge)，并且我們知道OR實(shí)際上是把每個(gè)結(jié)果最后UNION一起的。

mysql>?explain?select*from?user_info?where?user_id=1?or?idcard='222222';
+----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
|?id?|?select_type?|?table?????|?partitions?|?type????????|?possible_keys????????????????|?key?????????????????|?key_len?|?ref??|?rows?|?filtered?|?Extra??????????????????????????????????????????????|
+----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
|??1?|?SIMPLE??????|?user_info?|?NULL???????|?index_merge?|?PRIMARY,ind_name_id,id_index?|?ind_name_id,PRIMARY?|?4,62????|?NULL?|????2?|??????100?|?Using?sort_union(ind_name_id,PRIMARY);?Using?where?|
+----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
1?row?in?set

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

mysql>?explain?select*from?user_info?where?name='Zhangsan'?or?address='Beijing';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|?id?|?select_type?|?table?????|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?filtered?|?Extra???????|
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|??1?|?SIMPLE??????|?user_info?|?NULL???????|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|????4?|????43.75?|?Using?where?|
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1?row?in?set

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

mysql>?explain?select*from?user_info?where?address='Beijing'?or?user_id=1;
+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+
|?id?|?select_type?|?table?????|?partitions?|?type?|?possible_keys????????|?key??|?key_len?|?ref??|?rows?|?filtered?|?Extra???????|
+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+
|??1?|?SIMPLE??????|?user_info?|?NULL???????|?ALL??|?ind_name_id,id_index?|?NULL?|?NULL????|?NULL?|????4?|????43.75?|?Using?where?|
+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+
1?row?in?set

6、優(yōu)化嵌套查詢

使用嵌套查詢有時(shí)候可以使用更有效的JOIN連接代替，這是因?yàn)镸ySQL中不需要在內(nèi)存中創(chuàng)建臨時(shí)表完成SELECT子查詢與主查詢兩部分查詢工作。但是并不是所有的時(shí)候都成立，最好是在on關(guān)鍵字后面的列有索引的話，效果會(huì)更好！

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

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

而通過嵌套查詢時(shí)，在內(nèi)存中創(chuàng)建臨時(shí)表完成SELECT子查詢與主查詢兩部分查詢工作，會(huì)有一定的消耗

select?*?from?student?u?where?major_id?not?in?(select?major_id?from?major);

7、使用SQL提示

SQL提示（SQL HINT）是優(yōu)化數(shù)據(jù)庫(kù)的一個(gè)重要手段，就是往SQL語(yǔ)句中加入一些人為的提示來達(dá)到優(yōu)化目的。下面是一些常用的SQL提示：

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

mysql>?explain?select*?from?user_info?use?index(id_index,ind_name_id)?where?user_id>0;
+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+
|?id?|?select_type?|?table?????|?partitions?|?type?|?possible_keys????????|?key??|?key_len?|?ref??|?rows?|?filtered?|?Extra???????|
+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+
|??1?|?SIMPLE??????|?user_info?|?NULL???????|?ALL??|?ind_name_id,id_index?|?NULL?|?NULL????|?NULL?|????4?|??????100?|?Using?where?|
+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+
1?row?in?set

mysql>?explain?select*?from?user_info?use?index(id_index)?where?user_id>0;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|?id?|?select_type?|?table?????|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?filtered?|?Extra???????|
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|??1?|?SIMPLE??????|?user_info?|?NULL???????|?ALL??|?id_index??????|?NULL?|?NULL????|?NULL?|????4?|??????100?|?Using?where?|
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1?row?in?set

（2）IGNORE INDEX忽略索引

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

mysql>?explain?select*?from?user_info?ignore?index(primary,ind_name_id,id_index)?where?user_id>0;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|?id?|?select_type?|?table?????|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?filtered?|?Extra???????|
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|??1?|?SIMPLE??????|?user_info?|?NULL???????|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|????4?|????33.33?|?Using?where?|
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1?row?in?set

（3）FORCE INDEX強(qiáng)制索引

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

mysql>?explain?select*?from?user_info?where?user_id>0;
+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+
|?id?|?select_type?|?table?????|?partitions?|?type?|?possible_keys????????|?key??|?key_len?|?ref??|?rows?|?filtered?|?Extra???????|
+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+
|??1?|?SIMPLE??????|?user_info?|?NULL???????|?ALL??|?ind_name_id,id_index?|?NULL?|?NULL????|?NULL?|????4?|??????100?|?Using?where?|
+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+
1?row?in?set

之后強(qiáng)制使用獨(dú)立索引id_index(user_id)：

mysql>?explain?select*?from?user_info?force?index(id_index)?where?user_id>0;
+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|?id?|?select_type?|?table?????|?partitions?|?type??|?possible_keys?|?key??????|?key_len?|?ref??|?rows?|?filtered?|?Extra?????????????????|
+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|??1?|?SIMPLE??????|?user_info?|?NULL???????|?range?|?id_index??????|?id_index?|?4???????|?NULL?|????4?|??????100?|?Using?index?condition?|
+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1?row?in?set

總結(jié)

（1）很多時(shí)候數(shù)據(jù)庫(kù)的性能是由于不合適（是指效率不高，可能會(huì)導(dǎo)致鎖表等）的SQL語(yǔ)句造成，本篇博文只是介紹簡(jiǎn)單的SQL優(yōu)化

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

——End——

后臺(tái)回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨

后臺(tái)回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群。

推薦閱讀

• 來，教你寫一手好SQL！
  
• 你有一份經(jīng)典SQL語(yǔ)句大全，請(qǐng)注意查收！！！
  
• 一行SQL代碼能做什么？
  
• 危險(xiǎn)！請(qǐng)馬上停止使用第三方微信外掛
  
• 送你一份 SQL 進(jìn)階技巧（下）

這是一個(gè)能學(xué)到技術(shù)的公眾號(hào)，歡迎關(guān)注點(diǎn)擊「閱讀原文」了解SQL訓(xùn)練營(yíng)