SQL優(yōu)化的七個方法，你會哪個？
一、插入數(shù)據(jù)優(yōu)化
普通插入：
在平時我們執(zhí)行insert語句的時候，可能都是一條一條數(shù)據(jù)插入進(jìn)去的，就像下面這樣。
INSERT INTO `department` VALUES(1, '研發(fā)部(RD)', '2層'),
INSERT INTO `department` VALUES(2, '人事部(RD)', '1層'),
INSERT INTO `department` VALUES(3, '后勤部(RD)', '4層'),
INSERT INTO `department` VALUES(3, '財(cái)務(wù)部(RD)', '4層'),現(xiàn)在我們考慮以下三個方面對insert操作進(jìn)行優(yōu)化。
1、采用批量插入（一次插入的數(shù)據(jù)不建議超過1000條）,
執(zhí)行批量插入，一次性插入的數(shù)據(jù)不建議超過1000條，如果要插入上萬條數(shù)據(jù)的話，可以將其分割為多條insert語句進(jìn)行插入。
INSERT INTO `department` (`id`, `deptName`, `address`)
VALUES
(1, '研發(fā)部(RD)', '2層'),
(2, '人事部(HR)', '3層'),
(3, '市場部(MK)', '4層'),
(4, '后勤部(MIS)', '5層'),
(5, '財(cái)務(wù)部(FD)', '6層');2、手動提交事務(wù)
因?yàn)橐粭l一條insert插入的時候，如果是自動提交事務(wù)，我們的MySQL會頻繁的開啟、執(zhí)行事務(wù)；
所以我們可以考慮在在大段insert單條插入語句執(zhí)行的時候，用手動提交事務(wù)的方式來執(zhí)行。
begin;
INSERT INTO `department` (`deptName`, `address`)VALUES('研發(fā)部(RD)', '2層'),('人事部(HR)', '3層'),('市場部(MK)', '4層'),('后勤部(MIS)', '5層');
INSERT INTO `department` (`deptName`, `address`)VALUES('研發(fā)部(RD)', '2層'),('人事部(HR)', '3層'),('市場部(MK)', '4層'),('后勤部(MIS)', '5層');
INSERT INTO `department` (`deptName`, `address`)VALUES('研發(fā)部(RD)', '2層'),('人事部(HR)', '3層'),('市場部(MK)', '4層'),('后勤部(MIS)', '5層');
commit;
圖片
3、大批量插入
如果一次性需要插入大批量數(shù)據(jù)，使用insert語句插入性能較低，此時可以使用MySQL數(shù)據(jù)庫提供的load指令插入。
-- 1、首先，檢查一個全局系統(tǒng)變量 'local_infile' 的狀態(tài)， 如果得到如下顯示 Value=OFF，則說明這是不可用的
show global variables like 'local_infile';
-- 2、修改local_infile值為on，開啟local_infile
set global local_infile=1;
-- 3、加載數(shù)據(jù)
/*
腳本文件介紹 :
每一列數(shù)據(jù)用","分割",
每一行數(shù)據(jù)用 \n'回車分割
*/
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';經(jīng)過測試，導(dǎo)入100萬行數(shù)據(jù)，僅僅耗時16.84s
注意事項(xiàng)：使用load的時候要按主鍵順序插入，主鍵順序插入的性能要高于亂序插入的性能。
二、主鍵優(yōu)化
在InnoDB存儲引擎中，表數(shù)據(jù)都是根據(jù)主鍵順序組織存放的，這種存儲方式的表稱為索引組織表。
我們的InnoDB存儲引擎的聚集索引結(jié)果中，B+Tree的葉子結(jié)點(diǎn)下存儲的是row，行數(shù)據(jù)，并且是根據(jù)主鍵順序存放。所有的數(shù)據(jù)都會出現(xiàn)在葉子結(jié)點(diǎn)，而非葉子結(jié)點(diǎn)僅僅起到了索引的作用。
圖片
主鍵設(shè)計(jì)原則：
1、滿足業(yè)務(wù)需求的情況下，盡量降低主鍵的長度
2、插入數(shù)據(jù)時，盡量選擇順序插入，選擇使用AUTO_INCREMENT自增主鍵
3、盡量不要使用UUID做主鍵或者是其他自然主鍵，如身份證號
4、業(yè)務(wù)操作時，避免對主鍵的修改
三、order by優(yōu)化
我們先了解兩個概念，前面我們在Explatin詳解文章中提到過：SQL性能分析工具Explain詳解
Using filesort：通過表的索引或全表掃描，讀取滿足條件的數(shù)據(jù)行，然后在排序緩沖區(qū) sort buffer 中完成排序操作，所有不是通過索引直接返回排序結(jié)果的排序都叫 FileSort 排序。
Using index：通過有序索引順序掃描直接返回有序數(shù)據(jù)，這種情況即為 using index，不需要額外排序，操作效率高
我們對order by的優(yōu)化就是盡可能優(yōu)化為Using index。
新建表：employee
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`dep_id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`salary` decimal(10,2) DEFAULT NULL,
`cus_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name_dep_id_age` (`name`,`dep_id`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;不使用索引情況：
圖片
新建聯(lián)合索引：name,dep_id,age
#創(chuàng)建聯(lián)合索引
CREATE INDEX idx_name_dep_id_age ON employee (name, dep_id, age);
#查詢當(dāng)前索引
show INDEX from employee
#刪除索引
DROP INDEX idx_name_dep_id_age ON employee;如果order by字段全部使用升序排序或者降序排序，則都會走索引.
圖片
#有索引情況順序
Explain select name,dep_id,age from employee order by name,dep_id,age ;
#有索引情況倒序
Explain select name,dep_id,age from employee order by name desc,dep_id desc,age desc;但是如果一個字段升序排序，另一個字段降序排序，則不會走索引，explain的extra信息顯示的是Using index, Using filesort.
圖片
#有索引情況有的順序,有的倒序
Explain select name,dep_id,age from employee order by name desc,dep_id asc,age desc;如果要優(yōu)化掉Using filesort，此時我們可以再創(chuàng)建一個聯(lián)合索引，即name按倒序，dep_id按升序創(chuàng)建索引，就可以解決。
圖片
注意的是雖然我已經(jīng)創(chuàng)建了覆蓋這些列的聯(lián)合索引 idx_name_dep_id_age2，但 MySQL 優(yōu)化器仍然可能會決定使用文件排序（filesort）來執(zhí)行這個順序的排序操作。
在內(nèi)存中無法容納整個結(jié)果集時，MySQL 將結(jié)果集存儲在臨時文件中并對其進(jìn)行排序。這并不一定意味著性能問題，但是可能會影響查詢的執(zhí)行時間，尤其是當(dāng)處理大量數(shù)據(jù)時。
總結(jié)：
- 根據(jù)排序字段建立合適的索引，多字段排序時，也遵循最左前綴法則
- 盡量使用覆蓋索引
- 多字段排序，一個升序一個降序，此時需要注意聯(lián)合索引在創(chuàng)建時的規(guī)則（ASC/DESC）
- 如果不可避免出現(xiàn)filesort，大數(shù)據(jù)量排序時，可以適當(dāng)增大排序緩沖區(qū)大小 sort_buffer_size（默認(rèn)256k）
四、group by優(yōu)化
先刪除全部的索引（保留主鍵id）
#刪除索引
DROP INDEX idx_name_dep_id_age2 ON employee;
#無索引情況
Explain select name,dep_id,age ,count(*)from employee group by name,dep_id,age ;無索引情況下，分組,出現(xiàn)filesort,type為All出現(xiàn)了全表掃描。
圖片
新建聯(lián)合索引，name,dep_id，age再觀察。
#創(chuàng)建聯(lián)合索引
CREATE INDEX idx_name_dep_id_age ON employee (name, dep_id, age);
#有索引情況
Explain select name,dep_id,age,count(*)from employee group by name,dep_id,age ;可見用到了索引
圖片
總結(jié)：
- 在分組操作時，可以通過索引來提高效率
- 分組操作時，索引的使用也是滿足最左前綴法則的
如索引為idx_user_pro_age_stat，則句式可以是select ... where profession order by age，這樣也符合最左前綴法則
五、limit優(yōu)化
語法復(fù)習(xí)：
#0表示起始位置，10表示每一頁展示的數(shù)據(jù)。
select * from student_info limit 0,10;這條查詢執(zhí)行的速度非常快，但是如果我們將起始位置設(shè)置為100000呢？
limit分頁查詢在大數(shù)據(jù)量的時候，查詢效率同樣會非常的慢，例如一個常見又非常頭疼的問題就是limit 2000000,10 此時需要MySQL排序前200010條記錄，僅僅返回200000-2000010的記錄，其他記錄丟棄，查詢排序的代價(jià)非常大。
優(yōu)化方案：一般分頁查詢時，通過創(chuàng)建覆蓋索引能夠比較好地提高性能，可以通過覆蓋索引加子查詢形式進(jìn)行優(yōu)化
例如：利用主鍵id,先獲取要查詢的10是個id.
explain select *
from student_info t,(select id from student_info order by id limit 2000000,10) a
where t.id = a.id;
圖片
或者使用范圍查詢方式優(yōu)化：
圖片
explain select *
from student_info where id > 2000000 limit 10六、COUNT優(yōu)化
count是一個聚合函數(shù)，用于求取符合條件的總數(shù)據(jù)量。
MyISAM引擎把一個表的總行數(shù)存在了磁盤上，因此執(zhí)行count（*）的時候會直接返回這個數(shù)，效率很高。
InnoDB引擎就比較麻煩，它執(zhí)行count(*)的時候，需要把數(shù)據(jù)一行一行地從引擎里面讀出來，然后累計(jì)計(jì)數(shù)。
count的幾種用法：
count()是一個聚合函數(shù)，對于返回的結(jié)果集，一行行地判斷，如果count函數(shù)的參數(shù)不是NULL，累計(jì)值就加1，否則不加，最后返回累計(jì)值。
用法：count(*)、count(主鍵)、count(字段)、count(1)、count(0).
count(主鍵)：InnoDB會遍歷整張表，把每一行的主鍵id值都取出來，返回給服務(wù)層。服務(wù)層拿到主鍵后，直接按行進(jìn)行累加（主鍵不可能為null）。
count(字段)：沒有not null約束的話，InnoDB引擎會遍歷整張表把每一行的字段值都取出來，返回給服務(wù)層，服務(wù)層判斷是否為null，不為null，計(jì)數(shù)累加；有not null約束的話，InnoDB引擎會遍歷整張表把每一行的字段值都取出來，返回給服務(wù)層，直接按行進(jìn)行累加。
count(1)：InnoDB引擎遍歷整張表，但不取值。服務(wù)層對于返回的每一行，放一個數(shù)字“1”進(jìn)去，直接按行進(jìn)行累加。
count(*)：InnoDB引擎并不會把全部字段取出來，而是專門做了優(yōu)化，不取值，服務(wù)層直接按行進(jìn)行累加。
按照效率排序:count(字段)<count(主鍵)<count(1)<count(*)
count(*):
圖片
count(name):
圖片
count(1):
圖片
七、UPDATE優(yōu)化
InnoDB 的行鎖是針對索引加的鎖，不是針對記錄加的鎖，并且該索引不能失效，否則會從行鎖升級為表鎖。
測試1：
開啟兩個會話：更新student表的數(shù)據(jù)，會話1更新id為2的數(shù)據(jù)，會話2更新id為2的數(shù)據(jù)
圖片
會話1：
update student set name = '123' where id = 1;由于id有主鍵索引，所以只會鎖id = 1這一行；
會話2: id=2,當(dāng)然會立馬執(zhí)行結(jié)束，不用等待會話1提交事務(wù)
update student set name = '123' where id = 2;測試2：
開啟兩個會話：更新student表的數(shù)據(jù)，會話1更新name（name字段無索引）為2的數(shù)據(jù)，會話2更新id為2的數(shù)據(jù)
update student set name = '123' where name = 'test';
圖片
由于name沒有索引，所以會把整張表都鎖住，導(dǎo)致會話2等待會話1提交事務(wù)。
解決方法：給name字段添加索引
