盤點(diǎn)開發(fā)中那些常用的MySQL優(yōu)化
點(diǎn)擊上方藍(lán)色“程序猿DD”，選擇“設(shè)為星標(biāo)”
回復(fù)“資源”獲取獨(dú)家整理的學(xué)習(xí)資料！
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 語句，這種方式將大大縮減客戶端與數(shù)據(jù)庫之間的連接、關(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語句得到更高的速度，DELLAYED含義是讓INSERT語句馬上執(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語句快20倍。
3、GROUP BY的優(yōu)化
在默認(rèn)情況下，MySQL中的GROUP BY語句會(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
測試一：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
測試二：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ù)庫的一個(gè)重要手段，就是往SQL語句中加入一些人為的提示來達(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ù)庫的性能是由于不合適（是指效率不高，可能會(huì)導(dǎo)致鎖表等）的SQL語句造成，本篇博文只是介紹簡單的SQL優(yōu)化
（2）其中有些優(yōu)化在真正開發(fā)中是用不到的，但是一旦出問題性能下降的時(shí)候需要去一一分析。
往期推薦
掃一掃，關(guān)注我
一起學(xué)習(xí)，一起進(jìn)步