[MySQL--SQL優化]
1、insert優化（插入資料優化）
-
建議使用批量插入
# 批量插入避免頻繁連接斷開資料庫（一次連接插入多條資料） insert into 表名 values(資料1),(資料2),(資料3)...... -
建議手動提交事務插入（避免頻繁開啟關閉事務）
# 即開啟一次事務 執行多條sql start transaction; # 開啟事務 # 執行多條sql insert into 表名 values(資料1),(資料2),(資料3); insert into 表名 values(資料4),(資料5),(資料6); insert into 表名 values(資料7),(資料8),(資料9); commit; # 提交事務 -
大批量插入資料（load指令）
如果一次性需要插入大批量資料，使用insert陳述句插入性能較低，此時可以使用MySQL資料庫提供的load指令進行插入，
操作如下：
# 客戶端連接服務器時，加上引數 --local-infile mysql --local-infile -u 用戶名 -p 密碼 # 設定全域引數local-infile為1，開啟從本地加載檔案匯入資料的開關 1、查看是否開啟命令 select @@local_infile; 2、開啟命令 set global local_infile = 1; # 執行load指令將準備好的資料，加載到表格 load data local infile '/root/sql.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n'; load data local infile '檔案路徑' into table '表名' fields terminated by '什么作為分割符' lines terminated by '每一行的結束符';注意：主鍵順序插入的性能高于亂序插入!
2、主鍵優化
# 1）滿足業務需求的情況下、盡量降低主鍵的長度
原因是我們二級索引的葉子節點存放的就是主鍵，如果主鍵過長且葉子節點過多會占用大量的磁盤空間！
# 2）插入資料時盡量選擇順序插入，選擇使用auto_increment主鍵自增
如果不順序插入可能會存在頁分裂現象,如果按照順序插入則可避免
# 3）盡量不要使用uuid作為主鍵或者是其他自然主鍵，如身份證號！
原因 uuid或者身份證號則無序且過長,影響效率！
# 4）業務操作時盡量避免對主鍵的修改，
3、order by優化
①. Using filesort：通過表的索引或全表掃描，讀取滿足條件的資料行，然后在排序緩沖區sort buffer中完成排序操作，所有不是通過索引直接回傳排序結果的排序都叫file sort排序，
②. Using index：通過有序索引順序掃描直接回傳有序資料，這種情況即為using index，不需要額外排序，操作效率高，
注意：盡量優化為Using index
# 1、根據排序欄位建立合適的索引，多欄位排序時，也遵循最左前綴法則
# 2、盡量使用覆寫索引
# 3、多欄位排序，一個升序一個降序，此時需要注意聯合索引在創建時的規則（ASC/DESC）
# 4、如果不可避免的出現filesort，大資料量排序時，可以適當增大排序緩沖區大小sort_buffer_size(默認256k)，
查看默認緩沖區大小命令
show variables like 'sort_buffer_size';
4、group by優化
# 在分組操作時，可以通過索引來提高效率，
# 分組操作時，索引的使用也是滿足最左前綴法則的，
5、limit優化
-
針對于大資料量的情況下 分頁查詢時越往后就會變得越耗時，所以我們需要通過優化limit來提升效率
注意：官方建議通過覆寫索引加子查詢來優化
例如： 未優化前： select * from 表名 limit 9000000,10: 耗時 19s 優化后： # 可以直接使用覆寫聚集索引，不需要回表查詢 提升效率 select id from 表名 order by id limit 9000000,10; # 我們可以將查詢出來的id看作為一張新表 # 之后我們可以使用子查詢來獲取具體資料 select A.* from 表名1 as A,(select id from 表名 order by id limit 9000000,10:) as B where A.id == B.id; # 括號內可以看作為一張新表 然后進行連表查詢即可 大大提升效率 耗時 12s
6、count優化
-
MyISAM 引擎把一個表的總行數存在了磁盤上，因此執行count(*)的時候就會直接回傳這個數，效率很高（僅僅在沒有where條件有效）
-
innoDB 引擎就麻煩了，它執行count(*)的時候，需要把資料一行一行地從引擎里面讀出來，然后累計計數
優化思路：自己計數，
比如：我們可以自己維護一張表來專門存盤我們所需要記錄的資料，利用redis記憶體資料庫 加一條資料 就+1等
count的幾種用法
1.count(主鍵)
InnoDB引擎會遍歷整張表,把每一行的主鍵id值都取出來,回傳給服務層，服務層拿到主鍵后,直接按行進行累加(主鍵不可能為nut)
2.count(欄位)
沒有 not nul約束: Innodb引擎會遍歷整張表把每一行的欄位值都取出來,回傳給服務層,服務層判斷是否為nu不為nu,計數累加，
有 not nu約束: Innodb引擎會遍歷整張表把每一行的欄位值都取出來,回傳給服務層,直接按行進行累加，
3.count(1)
Innodb引擎遍歷整張表,但不取值，服務層對于回傳的每一行,放一個數字“1”進去,直接按行進行累加，
4.count(*)
Innodb引擎并不會把全部欄位取出來,而是專門做了優化,不取值,服務層直接按行進行累加，
按照效率排序的話, count欄位)< count(主鍵id< count(1) c count(·),所以盡量使用 count(*)
7、update優化
update student set no='2000100100' where id=1;
update student set no='2000100105' where name='韋一笑';
# Innode的行鎖是針對索引加的鎖,不是針對記錄加的鎖,并且該索引不能失效,否則會從行鎖升級為表鎖，
# 升級為表鎖就會降低性能！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/554058.html
標籤：其他
上一篇：海量資料運維要給力，GaussDB(for Cassandra)來助力
下一篇：返回列表