一、插入數(shù)據(jù)優(yōu)化

普通插入：

在平時我們執(zhí)行insert語句的時候，可能都是一條一條數(shù)據(jù)插入進去的，就像下面這樣。

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, '財務(wù)部(RD)', '4層'),

現(xiàn)在我們考慮以下三個方面對insert操作進行優(yōu)化。

1、采用批量插入（一次插入的數(shù)據(jù)不建議超過1000條）,

執(zhí)行批量插入，一次性插入的數(shù)據(jù)不建議超過1000條，如果要插入上萬條數(shù)據(jù)的話，可以將其分割為多條insert語句進行插入。

INSERT INTO `department` (`id`, `deptName`, `address`)
VALUES
 (1, '研發(fā)部(RD)', '2層'),
 (2, '人事部(HR)', '3層'),
 (3, '市場部(MK)', '4層'),
 (4, '后勤部(MIS)', '5層'),
 (5, '財務(wù)部(FD)', '6層');

2、手動提交事務(wù)

因為一條一條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

注意事項：使用load的時候要按主鍵順序插入，主鍵順序插入的性能要高于亂序插入的性能。

二、主鍵優(yōu)化

在InnoDB存儲引擎中，表數(shù)據(jù)都是根據(jù)主鍵順序組織存放的，這種存儲方式的表稱為索引組織表。

我們的InnoDB存儲引擎的聚集索引結(jié)果中，B+Tree的葉子結(jié)點下存儲的是row，行數(shù)據(jù)，并且是根據(jù)主鍵順序存放。所有的數(shù)據(jù)都會出現(xiàn)在葉子結(jié)點，而非葉子結(jié)點僅僅起到了索引的作用。

圖片

主鍵設(shè)計原則：

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);
#查詢當前索引
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é)果集存儲在臨時文件中并對其進行排序。這并不一定意味著性能問題，但是可能會影響查詢的執(zhí)行時間，尤其是當處理大量數(shù)據(jù)時。

總結(jié)：

• 根據(jù)排序字段建立合適的索引，多字段排序時，也遵循最左前綴法則
• 盡量使用覆蓋索引
• 多字段排序，一個升序一個降序，此時需要注意聯(lián)合索引在創(chuàng)建時的規(guī)則（ASC/DESC）
• 如果不可避免出現(xiàn)filesort，大數(shù)據(jù)量排序時，可以適當增大排序緩沖區(qū)大小 sort_buffer_size（默認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的記錄，其他記錄丟棄，查詢排序的代價非常大。

優(yōu)化方案：一般分頁查詢時，通過創(chuàng)建覆蓋索引能夠比較好地提高性能，可以通過覆蓋索引加子查詢形式進行優(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ù)一行一行地從引擎里面讀出來，然后累計計數(shù)。

count的幾種用法：

count()是一個聚合函數(shù)，對于返回的結(jié)果集，一行行地判斷，如果count函數(shù)的參數(shù)不是NULL，累計值就加1，否則不加，最后返回累計值。

用法：count(*)、count(主鍵)、count(字段)、count(1)、count(0).

count(主鍵)：InnoDB會遍歷整張表，把每一行的主鍵id值都取出來，返回給服務(wù)層。服務(wù)層拿到主鍵后，直接按行進行累加（主鍵不可能為null）。

count(字段)：沒有not null約束的話，InnoDB引擎會遍歷整張表把每一行的字段值都取出來，返回給服務(wù)層，服務(wù)層判斷是否為null，不為null，計數(shù)累加；有not null約束的話，InnoDB引擎會遍歷整張表把每一行的字段值都取出來，返回給服務(wù)層，直接按行進行累加。

count(1)：InnoDB引擎遍歷整張表，但不取值。服務(wù)層對于返回的每一行，放一個數(shù)字“1”進去，直接按行進行累加。

count(*)：InnoDB引擎并不會把全部字段取出來，而是專門做了優(yōu)化，不取值，服務(wù)層直接按行進行累加。

按照效率排序: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,當然會立馬執(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字段添加索引