MySQL優(yōu)化---插入數(shù)據(jù)優(yōu)化和主鍵優(yōu)化

一、插入數(shù)優(yōu)化（insert）

平時(shí)我們插入數(shù)據(jù)的時(shí)候一般都是一個(gè)語句插一個(gè)數(shù)據(jù)，如下所示：

insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');

如果我們需要一次性往數(shù)據(jù)庫(kù)表中插入多條記錄，可以從以下三個(gè)方面進(jìn)行優(yōu)化。

1.insert插入的優(yōu)化方案

（1）優(yōu)化方案一

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

上面這個(gè)語句我們之前是使用過的，相較于一條語句插入一個(gè)數(shù)據(jù)，一次性插入批量數(shù)據(jù)效率必然是更高的，這就不需要多次開啟和提交事務(wù)了，節(jié)約時(shí)間。

（2）優(yōu)化方案二

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

手動(dòng)控制事務(wù)，其實(shí)這個(gè)語句的就是方法1的本質(zhì)，也就是通過一次事務(wù)去提交，避免多次開啟事務(wù)的情況。

（3）優(yōu)化方案三

主鍵順序插入，性能要高于亂序插入。這個(gè)應(yīng)該沒什么好多說了，排序肯定是需要耗時(shí)間的。

主鍵亂序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主鍵順序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

2 大批量插入數(shù)據(jù)

如果一次性需要插入大批量數(shù)據(jù) ( 比如 : 幾百萬的記錄 ) ，使用 insert 語句插入性能較低，此時(shí)可以使

用 MySQL 數(shù)據(jù)庫(kù)提供的load指令進(jìn)行插入。操作如下：

可以執(zhí)行如下指令，將數(shù)據(jù)腳本文件中的數(shù)據(jù)加載到表結(jié)構(gòu)中：

-- 客戶端連接服務(wù)端時(shí)，加上參數(shù) -–local-infile
mysql –-local-infile -u root -p
 
-- 設(shè)置全局參數(shù)local_infile為1，開啟從本地加載文件導(dǎo)入數(shù)據(jù)的開關(guān)
set global local_infile = 1;
 
-- 執(zhí)行l(wèi)oad指令將準(zhǔn)備好的數(shù)據(jù)，加載到表結(jié)構(gòu)中
load data local infile '/root/sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n' ;

補(bǔ)充說明：

對(duì)于load指令的語句中，fields terminated by意思是每一個(gè)字段之間間隔符號(hào)用什么

lines terminated by意思是每一行間距是用什么。

這里我們先去查看一下local infile開了沒有

select @@local_infile;

開啟成功后，下面就開始建表進(jìn)行數(shù)據(jù)的插入了。

CREATE TABLE `tb_user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL,
`password` VARCHAR(50) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`birthday` DATE DEFAULT NULL,
`sex` CHAR(1) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

在load時(shí)，主鍵順序插入性能高于亂序插入
二、主鍵優(yōu)化
在上面，我們提到，主鍵順序插入的性能是要高于亂序插入的。這一小節(jié)，就來介紹一下具體的原因，然后再分析一下主鍵又該如何設(shè)計(jì)。
1.數(shù)據(jù)組織方式
在 InnoDB 存儲(chǔ)引擎中，表數(shù)據(jù)都是根據(jù)主鍵順序組織存放的，這種存儲(chǔ)方式的表稱為索引組織表
(index organized table IOT)。
2. 頁(yè)分裂
頁(yè)可以為空，也可以填充一半，也可以填充 100% 。每個(gè)頁(yè)包含了 2-N 行數(shù)據(jù) ( 如果一行數(shù)據(jù)過大，會(huì)行溢出) ，根據(jù)主鍵排列。
A. 主鍵順序插入效果
① . 從磁盤中申請(qǐng)頁(yè)， 主鍵順序插入
上述的這種現(xiàn)象，稱之為 "頁(yè)分裂"，是比較耗費(fèi)性能的操作。
MERGE_THRESHOLD：合并頁(yè)的閾值，可以自己設(shè)置，在創(chuàng)建表或者創(chuàng)建索引時(shí)指定。
4. 索引設(shè)計(jì)原則
滿足業(yè)務(wù)需求的情況下，盡量降低主鍵的長(zhǎng)度。
插入數(shù)據(jù)時(shí)，盡量選擇順序插入，選擇使用AUTO_INCREMENT自增主鍵。
盡量不要使用UUID做主鍵或者是其他自然主鍵，如身份證號(hào)。
業(yè)務(wù)操作時(shí)，避免對(duì)主鍵的修改。
來源：https://blog.csdn.net/m0_73633088/article/details/137425511