一、插入數(shù)據(jù)優(yōu)化
(1) insert 語句
① 批量插入數(shù)據(jù)
insert into tb_test values(1, 'Tom'), (2, 'Cat'), (3, 'Jerry');
② 手動(dòng)控制事務(wù)
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;
③ 主鍵順序插入，性能要高于亂序插入
主鍵亂序插入 : 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) load 大批量插入數(shù)據(jù)【☆?
】
① 如果需要一次性插入大批量數(shù)據(jù)（百萬級(jí)別），使用 insert 語句插入性能很低
② 可使用 MySQL 數(shù)據(jù)庫提供的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' ;
二、主鍵優(yōu)化
主鍵順序插入的性能是要高于亂序插入的, 為什么？
(1) 數(shù)據(jù)組織形式
- 在 InnoDB 存儲(chǔ)引擎中，表數(shù)據(jù)都是根據(jù)主鍵順序組織存放的
- 這種存儲(chǔ)方式的表稱為索引組織表 (index organized table IOT)
在 InnoDB 存儲(chǔ)引擎中，數(shù)據(jù)行是記錄在邏輯結(jié)構(gòu) page 頁中的，而每一個(gè)頁的大小是固定的，默認(rèn)16K。那也就意味著， 一個(gè)頁中所存儲(chǔ)的行也是有限的，如果插入的數(shù)據(jù)行 row 在該頁存儲(chǔ)不小，將會(huì)存儲(chǔ)到下一個(gè)頁中，頁與頁之間會(huì)通過指針連接。
(2) 頁分裂
- 頁可以為空，也可以填充一半，也可以填充100%
- 每個(gè)頁包含了
2-N行數(shù)據(jù) - 如果一行數(shù)據(jù)過大，會(huì)行溢出，根據(jù)主鍵排列
(3) 頁合并
(4) 主鍵設(shè)計(jì)原則
- 滿足業(yè)務(wù)需求的情況下，盡量降低主鍵的長度
- 插入數(shù)據(jù)時(shí)，盡量選擇順序插入，選擇使用
AUTO_INCREMENT自增主鍵 - 盡量不要使用UUID做主鍵或者是其他自然主鍵，如身份證號(hào)
- 業(yè)務(wù)操作時(shí)，避免對(duì)主鍵的修改
三、orber by 優(yōu)化
- MySQL的排序，有兩種方式：
- Using filesort : 全表掃描，讀取滿足條件的數(shù)據(jù)行，然后在排序緩沖區(qū) sort buffer 中完成排序操作。所有不是通過索引直接返回排序結(jié)果的排序都叫 FileSort 排序。
- Using index : 通過有序索引順序掃描直接返回有序數(shù)據(jù)，這種情況即為 using index，不需要額外排序，操作效率高。
- Using index 的性能高，而 Using filesort 性能低
- 根據(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)化
- 在分組操作時(shí)，可以通過索引來提高效率。
- 分組操作時(shí)，索引的使用也是滿足最左前綴法則的
五、limit 優(yōu)化（分頁）
-
在數(shù)據(jù)量比較大時(shí)，如果進(jìn)行l(wèi)imit分頁查詢，在查詢時(shí)，越往后，分頁查詢效率越低。
-
在進(jìn)行分頁查詢時(shí)，如果執(zhí)行
limit 2000000,10，此時(shí)需要MySQL排序前2000010 條記錄，僅僅返回2000000 - 2000010的記錄，其他記錄丟棄，查詢排序的代價(jià)非常大
優(yōu)化思路: 一般分頁查詢時(shí)，通過創(chuàng)建 覆蓋索引 能夠比較好地提高性能，可以通過覆蓋索引加子查詢（多表聯(lián)查）形式進(jìn)行優(yōu)化
六、count 優(yōu)化
-
count()是一個(gè)聚合函數(shù)：對(duì)于返回的結(jié)果集，一行行地判斷，如果count 函數(shù)的參數(shù)不是 NULL，累計(jì)值就加 1，否則不加，最后返回累計(jì)值。
用法：
- count（*）
- count（主鍵）
- count（字段）
- count（數(shù)字）
七、update 優(yōu)化
文章來源：http://www.zghlxwxcb.cn/news/detail-477829.html
InnoDB 的行鎖是針對(duì)索引加的鎖，不是針對(duì)記錄加的鎖。并且該索引不能失效，否則會(huì)從行鎖升級(jí)為表鎖 文章來源地址http://www.zghlxwxcb.cn/news/detail-477829.html
到了這里，關(guān)于【MySQL 數(shù)據(jù)庫】7、SQL 優(yōu)化的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
