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