??鍵盤敲爛，年薪30萬(wàn)??
目錄
一、索引優(yōu)化 回顧：
??索引分類：
??索引失效：
??設(shè)計(jì)原則：
??SQL性能分析
二、SQL優(yōu)化 語(yǔ)句優(yōu)化
?? insert語(yǔ)句：
?? 主鍵優(yōu)化：
?? order by優(yōu)化：
?? group by優(yōu)化：
?? limit 優(yōu)化
?? count 優(yōu)化
?? update 優(yōu)化?
一、索引優(yōu)化 回顧：
??索引分類：
- 一般分類：主鍵索引、唯一索引、常規(guī)索引、全文索引
- 按存儲(chǔ)分類：聚集索引、二級(jí)索引
注意：
主鍵索引只能有一個(gè)且必須有一個(gè)，二級(jí)索引可以有多個(gè)，如果沒(méi)有主鍵，選唯一索引作為主鍵索引，如果沒(méi)有唯一索引，那么mysql會(huì)創(chuàng)建一個(gè)隱藏字段rowid作為索引。
?
??索引失效：
- 不滿足最左前綴法則
- 索引列計(jì)算
- 字符串類型不加'' 導(dǎo)致類型轉(zhuǎn)化
- 使用or連接了非索引的列
- %在最左邊，>或<號(hào)
- 數(shù)據(jù)分布影響
??設(shè)計(jì)原則：
? ? 盡量建立聯(lián)合索引，針對(duì)于數(shù)據(jù)量大(超百萬(wàn))，查詢多的表建索引，針對(duì)于where order by group by后的字段創(chuàng)建索引，如果字段很長(zhǎng)，考慮前綴索引，如果索引列不能為NULL，須在數(shù)據(jù)庫(kù)字段加上not null約束，這樣優(yōu)化器可以更好的選擇更有效的索引。
??SQL性能分析
- 執(zhí)行頻次
- 慢查詢?nèi)罩?/li>
- profile
- expplain執(zhí)行計(jì)劃?
二、SQL優(yōu)化 語(yǔ)句優(yōu)化
?? insert語(yǔ)句：
批量插入優(yōu)化：
- 一次性插入多條數(shù)據(jù)，但是不建議超過(guò)1000條。
insert into user values(1, 'zhangsan'), (2,'lisi');手動(dòng)提交事務(wù)優(yōu)化：
- 超過(guò)1000條，手動(dòng)開(kāi)啟提交事務(wù)，減少與數(shù)據(jù)庫(kù)的交互。
start transaction
insert into user values(1, 'zhagnsan'), (2, 'lisi'), ……
insert into user values(1000, 'wangwu'), (1001, 'zhaoliu') ……
……
commit主鍵順序插入優(yōu)化：
![[MySQL]SQL優(yōu)化之sql語(yǔ)句優(yōu)化,mysql,mysql,sql,數(shù)據(jù)庫(kù)](https://imgs.yssmx.com/Uploads/2023/12/764856-1.png)
大批量插入數(shù)據(jù)優(yōu)化：
- load：插入百萬(wàn)數(shù)據(jù)到數(shù)據(jù)庫(kù)
![[MySQL]SQL優(yōu)化之sql語(yǔ)句優(yōu)化,mysql,mysql,sql,數(shù)據(jù)庫(kù)](https://imgs.yssmx.com/Uploads/2023/12/764856-2.png)
- load使用三步走：
1.連接數(shù)據(jù)庫(kù)時(shí)加上：
--local-infile2.打開(kāi)全局參數(shù)：
set global local infile = 1;3.插入數(shù)據(jù)的腳本：
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' line terminated by '\n';
?? 主鍵優(yōu)化：
前面提到了主鍵按順序插入可提高性能，這里講解原理。
（這里我不是很明白，摘自GPT的回答）
頁(yè)分裂：
- 當(dāng)在一個(gè)已滿的頁(yè)（節(jié)點(diǎn)）中插入一個(gè)新的鍵時(shí)，可能會(huì)導(dǎo)致該頁(yè)不足以容納新鍵，因此需要進(jìn)行頁(yè)分裂。
- 頁(yè)分裂的過(guò)程涉及將原有的頁(yè)分成兩半，并將其中一半的部分移動(dòng)到一個(gè)新的頁(yè)中。這樣就在原有頁(yè)和新頁(yè)之間創(chuàng)建了一個(gè)新的分隔鍵，用于指示兩個(gè)頁(yè)之間的分割。
- 頁(yè)分裂的目的是確保樹(shù)的平衡，并維護(hù)索引的有序性。它通常發(fā)生在B樹(shù)或B+樹(shù)中。
頁(yè)合并：
- 與頁(yè)分裂相反，頁(yè)合并發(fā)生在刪除操作后。當(dāng)一個(gè)頁(yè)的鍵減少到一個(gè)臨界點(diǎn)以下時(shí)，可以考慮將其與相鄰的頁(yè)合并，從而減少索引樹(shù)的高度。
- 頁(yè)合并的過(guò)程涉及將兩個(gè)相鄰的頁(yè)合并成一個(gè)，并且刪除在合并過(guò)程中用于分隔的鍵。這有助于保持樹(shù)的平衡，并且減少了樹(shù)的高度，提高了檢索效率。
- 頁(yè)合并通常也發(fā)生在B樹(shù)或B+樹(shù)這樣的平衡樹(shù)結(jié)構(gòu)中。
小結(jié)：
? ? 索引的設(shè)計(jì)原則：長(zhǎng)度盡量短，盡量有序插入。
?? order by優(yōu)化：
優(yōu)化準(zhǔn)則：
- 如果創(chuàng)建索引的排序規(guī)則和要查詢語(yǔ)句的排序規(guī)則相同，那么直接返回?cái)?shù)據(jù)，效率高，如果不同，需要在緩沖區(qū)對(duì)相應(yīng)的字段進(jìn)行排序，效率不高。
注意：
創(chuàng)建索引默認(rèn)是升序排序，asc
創(chuàng)建索引是指定排序規(guī)則
create index id_na_ty on tb_book(name asc, type asc);例如：
一張tb_book表的索引![[MySQL]SQL優(yōu)化之sql語(yǔ)句優(yōu)化,mysql,mysql,sql,數(shù)據(jù)庫(kù)](https://imgs.yssmx.com/Uploads/2023/12/764856-3.png)
- 執(zhí)行語(yǔ)句1（升序排序查詢）：
select id, name, type from tb_book order by name asc, type asc;
-- 直接返回索引下面掛的數(shù)據(jù)，效率高查看執(zhí)行過(guò)程：
![[MySQL]SQL優(yōu)化之sql語(yǔ)句優(yōu)化,mysql,mysql,sql,數(shù)據(jù)庫(kù)](https://imgs.yssmx.com/Uploads/2023/12/764856-4.png)
- ?執(zhí)行語(yǔ)句2（name 升序 type 降序）
select id, name, type from tb_book order by name asc, type desc;
-- 會(huì)在緩沖區(qū)進(jìn)行排序，效率不高。查看執(zhí)行過(guò)程：
![[MySQL]SQL優(yōu)化之sql語(yǔ)句優(yōu)化,mysql,mysql,sql,數(shù)據(jù)庫(kù)](https://imgs.yssmx.com/Uploads/2023/12/764856-5.png)
小總結(jié)：
order by 查詢的字段要與建立索引時(shí)字段的排序規(guī)則相同，若不同，會(huì)在緩沖區(qū)排序然后返回?cái)?shù)據(jù)，可以在創(chuàng)建索引時(shí)指定排序規(guī)則
?? group by優(yōu)化：
跟order by類似，建立好相應(yīng)的索引，并且保證索引正確的使用規(guī)則，比如最左前綴法則。
?? limit 優(yōu)化
記?。?/span>覆蓋索引加子查詢：
原理：原本要對(duì)數(shù)據(jù)進(jìn)行排序，在挑選50條數(shù)據(jù)，現(xiàn)在使用索引覆蓋 + 子查詢 先根據(jù)id排序，排完之后直接子查詢就可以啦。
select * from user where limit 10000, 50;
-- 回表查詢性能低select t.* from user t, (select if from user where order by id limit 10000, 50) s where t.id = s.id;
-- 覆蓋索引 + 子查詢 性能略好?? count 優(yōu)化
count統(tǒng)計(jì)非空字段數(shù)量，count無(wú)法優(yōu)化，但是我們要區(qū)分count()括號(hào)里的字段的含義
- count(*)：不取值，直接累加。
- count(主鍵)：取出主鍵id，累加
- count(某個(gè)字段:有非空約束)：取值，返回給服務(wù)層，服務(wù)層直接累加
- coutn(某個(gè)字段：無(wú)非空約束)：取值，返回給服務(wù)層，服務(wù)層判斷后累加。
- count(1)：每行放一個(gè)1 并且累加，只要不是null都可以累加
小結(jié)：
盡量使用count(*)
?? update 優(yōu)化?
- 更新的條件一定要有索引，否則行鎖會(huì)標(biāo)為表鎖。
例如：user表 name字段帶有索引
一個(gè)客戶端執(zhí)行：update user set name = 'Zhangsan' where name = 'Lisi';
一個(gè)客戶端執(zhí)行：update user set name = 'wangwu' where name = 'zhaoliu';
分析：文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-764856.html
此時(shí)可以并發(fā)執(zhí)行，因?yàn)樗饕龑?duì)應(yīng)的是行級(jí)鎖，不會(huì)鎖整張表，相反如果沒(méi)有索引，或者索引失效，行級(jí)鎖就會(huì)變?yōu)楸礞i，無(wú)法高并發(fā)。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-764856.html
到了這里，關(guān)于[MySQL]SQL優(yōu)化之sql語(yǔ)句優(yōu)化的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
