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

普通插入：

在平時(shí)我們執(zhí)行insert語(yǔ)句的時(shí)候，可能都是一條一條數(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)在我們考慮以下三個(gè)方面對(duì)insert操作進(jìn)行優(yōu)化。

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

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

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

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

因?yàn)橐粭l一條insert插入的時(shí)候，如果是自動(dòng)提交事務(wù)，我們的MySQL會(huì)頻繁的開啟、執(zhí)行事務(wù)；

所以我們可以考慮在在大段insert單條插入語(yǔ)句執(zhí)行的時(shí)候，用手動(dòng)提交事務(wù)的方式來(lái)執(zhí)行。

begin;
INSERT INTO `department` (`deptName`, `address`)VALUES('研發(fā)部(RD)', '2層'),('人事部(HR)', '3層'),('市場(chǎng)部(MK)', '4層'),('后勤部(MIS)', '5層');
INSERT INTO `department` (`deptName`, `address`)VALUES('研發(fā)部(RD)', '2層'),('人事部(HR)', '3層'),('市場(chǎng)部(MK)', '4層'),('后勤部(MIS)', '5層');
INSERT INTO `department` (`deptName`, `address`)VALUES('研發(fā)部(RD)', '2層'),('人事部(HR)', '3層'),('市場(chǎng)部(MK)', '4層'),('后勤部(MIS)', '5層');
commit;

圖片

3、大批量插入

如果一次性需要插入大批量數(shù)據(jù)，使用insert語(yǔ)句插入性能較低，此時(shí)可以使用MySQL數(shù)據(jù)庫(kù)提供的load指令插入。

-- 1、首先，檢查一個(gè)全局系統(tǒng)變量 'local_infile' 的狀態(tài)， 如果得到如下顯示 Value=OFF，則說(shuō)明這是不可用的
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)過(guò)測(cè)試，導(dǎo)入100萬(wàn)行數(shù)據(jù)，僅僅耗時(shí)16.84s

注意事項(xiàng)：使用load的時(shí)候要按主鍵順序插入，主鍵順序插入的性能要高于亂序插入的性能。

二、主鍵優(yōu)化

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

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

圖片

主鍵設(shè)計(jì)原則：

1、滿足業(yè)務(wù)需求的情況下，盡量降低主鍵的長(zhǎng)度

2、插入數(shù)據(jù)時(shí)，盡量選擇順序插入，選擇使用AUTO_INCREMENT自增主鍵

3、盡量不要使用UUID做主鍵或者是其他自然主鍵，如身份證號(hào)

4、業(yè)務(wù)操作時(shí)，避免對(duì)主鍵的修改

三、order by優(yōu)化

我們先了解兩個(gè)概念，前面我們?cè)贓xplatin詳解文章中提到過(guò)：SQL性能分析工具Explain詳解

Using filesort：通過(guò)表的索引或全表掃描，讀取滿足條件的數(shù)據(jù)行，然后在排序緩沖區(qū) sort buffer 中完成排序操作，所有不是通過(guò)索引直接返回排序結(jié)果的排序都叫 FileSort 排序。

Using index：通過(guò)有序索引順序掃描直接返回有序數(shù)據(jù)，這種情況即為 using index，不需要額外排序，操作效率高

我們對(duì)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字段全部使用升序排序或者降序排序，則都會(huì)走索引.

圖片

#有索引情況順序
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;

但是如果一個(gè)字段升序排序，另一個(gè)字段降序排序，則不會(huì)走索引，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，此時(shí)我們可以再創(chuàng)建一個(gè)聯(lián)合索引，即name按倒序，dep_id按升序創(chuàng)建索引，就可以解決。

圖片

注意的是雖然我已經(jīng)創(chuàng)建了覆蓋這些列的聯(lián)合索引 idx_name_dep_id_age2，但 MySQL 優(yōu)化器仍然可能會(huì)決定使用文件排序（filesort）來(lái)執(zhí)行這個(gè)順序的排序操作。

在內(nèi)存中無(wú)法容納整個(gè)結(jié)果集時(shí)，MySQL 將結(jié)果集存儲(chǔ)在臨時(shí)文件中并對(duì)其進(jìn)行排序。這并不一定意味著性能問(wèn)題，但是可能會(huì)影響查詢的執(zhí)行時(shí)間，尤其是當(dāng)處理大量數(shù)據(jù)時(shí)。

總結(jié)：

• 根據(jù)排序字段建立合適的索引，多字段排序時(shí)，也遵循最左前綴法則
• 盡量使用覆蓋索引
• 多字段排序，一個(gè)升序一個(gè)降序，此時(shí)需要注意聯(lián)合索引在創(chuàng)建時(shí)的規(guī)則（ASC/DESC）
• 如果不可避免出現(xiàn)filesort，大數(shù)據(jù)量排序時(shí)，可以適當(dāng)增大排序緩沖區(qū)大小 sort_buffer_size（默認(rèn)256k）

四、group by優(yōu)化

先刪除全部的索引（保留主鍵id）

#刪除索引
DROP INDEX idx_name_dep_id_age2 ON employee;

#無(wú)索引情況
Explain select name,dep_id,age ,count(*)from employee group  by name,dep_id,age ;

無(wú)索引情況下，分組,出現(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àn)用到了索引

圖片

總結(jié)：

• 在分組操作時(shí)，可以通過(guò)索引來(lái)提高效率
• 分組操作時(shí)，索引的使用也是滿足最左前綴法則的

如索引為idx_user_pro_age_stat，則句式可以是select ... where profession order by age，這樣也符合最左前綴法則

五、limit優(yōu)化

語(yǔ)法復(fù)習(xí)：

#0表示起始位置，10表示每一頁(yè)展示的數(shù)據(jù)。
select * from student_info limit 0,10;

這條查詢執(zhí)行的速度非?？?，但是如果我們將起始位置設(shè)置為100000呢？

limit分頁(yè)查詢?cè)诖髷?shù)據(jù)量的時(shí)候，查詢效率同樣會(huì)非常的慢，例如一個(gè)常見(jiàn)又非常頭疼的問(wèn)題就是limit 2000000,10 此時(shí)需要MySQL排序前200010條記錄，僅僅返回200000-2000010的記錄，其他記錄丟棄，查詢排序的代價(jià)非常大。

優(yōu)化方案：一般分頁(yè)查詢時(shí)，通過(guò)創(chuàng)建覆蓋索引能夠比較好地提高性能，可以通過(guò)覆蓋索引加子查詢形式進(jìn)行優(yōu)化

例如：利用主鍵id,先獲取要查詢的10是個(gè)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是一個(gè)聚合函數(shù)，用于求取符合條件的總數(shù)據(jù)量。

MyISAM引擎把一個(gè)表的總行數(shù)存在了磁盤上，因此執(zhí)行count（*）的時(shí)候會(huì)直接返回這個(gè)數(shù)，效率很高。

InnoDB引擎就比較麻煩，它執(zhí)行count(*)的時(shí)候，需要把數(shù)據(jù)一行一行地從引擎里面讀出來(lái)，然后累計(jì)計(jì)數(shù)。

count的幾種用法：

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

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

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

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

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

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

按照效率排序:count(字段)<count(主鍵)<count(1)<count(*)

count(*):

圖片

count(name):

圖片

count(1):

圖片

七、UPDATE優(yōu)化

InnoDB 的行鎖是針對(duì)索引加的鎖，不是針對(duì)記錄加的鎖，并且該索引不能失效，否則會(huì)從行鎖升級(jí)為表鎖。

測(cè)試1：

開啟兩個(gè)會(huì)話：更新student表的數(shù)據(jù)，會(huì)話1更新id為2的數(shù)據(jù)，會(huì)話2更新id為2的數(shù)據(jù)

圖片

會(huì)話1：

update student set name = '123' where id = 1;

由于id有主鍵索引，所以只會(huì)鎖id = 1這一行；

會(huì)話2: id=2,當(dāng)然會(huì)立馬執(zhí)行結(jié)束，不用等待會(huì)話1提交事務(wù)

update student set name = '123' where id = 2;

測(cè)試2：

開啟兩個(gè)會(huì)話：更新student表的數(shù)據(jù)，會(huì)話1更新name（name字段無(wú)索引）為2的數(shù)據(jù)，會(huì)話2更新id為2的數(shù)據(jù)

update student set name = '123' where name = 'test';

圖片

由于name沒(méi)有索引，所以會(huì)把整張表都鎖住，導(dǎo)致會(huì)話2等待會(huì)話1提交事務(wù)。

解決方法：給name字段添加索引