一、插入數據優化

普通插入：

在平時我們執行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;

這條查詢執行的速度非?？欤侨绻覀儗⑵鹗嘉恢迷O置為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字段添加索引