[MySQL--SQL優(yōu)化]

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

• 建議使用批量插入

  # 批量插入避免頻繁連接斷開數(shù)據(jù)庫（一次連接插入多條數(shù)據(jù)）
  	insert into 表名 values(數(shù)據(jù)1),(數(shù)據(jù)2),(數(shù)據(jù)3)......
  

• 建議手動提交事務(wù)插入（避免頻繁開啟關(guān)閉事務(wù)）

  # 即開啟一次事務(wù) 執(zhí)行多條sql
  
  start transaction;  # 開啟事務(wù) 
  # 執(zhí)行多條sql
  insert into 表名 values(數(shù)據(jù)1),(數(shù)據(jù)2),(數(shù)據(jù)3);
  insert into 表名 values(數(shù)據(jù)4),(數(shù)據(jù)5),(數(shù)據(jù)6);
  insert into 表名 values(數(shù)據(jù)7),(數(shù)據(jù)8),(數(shù)據(jù)9);
  commit;  # 提交事務(wù)
  
  

• 大批量插入數(shù)據(jù)（load指令）

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

  操作如下：

  # 客戶端連接服務(wù)器時，加上參數(shù) --local-infile
  	mysql --local-infile -u 用戶名 -p 密碼
  	
  # 設(shè)置全局參數(shù)local-infile為1，開啟從本地加載文件導(dǎo)入數(shù)據(jù)的開關(guān)
  	1、查看是否開啟命令 
  		select @@local_infile;
  	2、開啟命令
  		set global local_infile = 1;
  		
  # 執(zhí)行l(wèi)oad指令將準(zhǔn)備好的數(shù)據(jù)，加載到表格
  	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 '每一行的結(jié)束符';
  	
  

  注意：主鍵順序插入的性能高于亂序插入!

2、主鍵優(yōu)化

# 1）滿足業(yè)務(wù)需求的情況下、盡量降低主鍵的長度
	原因是我們二級索引的葉子節(jié)點(diǎn)存放的就是主鍵，如果主鍵過長且葉子節(jié)點(diǎn)過多會占用大量的磁盤空間！
	
# 2）插入數(shù)據(jù)時盡量選擇順序插入，選擇使用auto_increment主鍵自增
	如果不順序插入可能會存在頁分裂現(xiàn)象,如果按照順序插入則可避免
	
# 3）盡量不要使用uuid作為主鍵或者是其他自然主鍵，如身份證號！
	原因 uuid或者身份證號則無序且過長,影響效率！
	
# 4）業(yè)務(wù)操作時盡量避免對主鍵的修改。
	

3、order by優(yōu)化

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

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

注意：盡量優(yōu)化為Using index

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

4、group by優(yōu)化

# 在分組操作時，可以通過索引來提高效率。
# 分組操作時，索引的使用也是滿足最左前綴法則的。

5、limit優(yōu)化

• 針對于大數(shù)據(jù)量的情況下 分頁查詢時越往后就會變得越耗時。所以我們需要通過優(yōu)化limit來提升效率

  注意：官方建議通過覆蓋索引加子查詢來優(yōu)化

  例如：
  
  未優(yōu)化前：
  	select * from 表名 limit 9000000,10:
  	耗時 19s
  	
  	
  優(yōu)化后：
  	# 可以直接使用覆蓋聚集索引，不需要回表查詢 提升效率
  	select id from 表名 order by id limit 9000000,10;
  	# 我們可以將查詢出來的id看作為一張新表
  	# 之后我們可以使用子查詢來獲取具體數(shù)據(jù)
  	select A.* from 表名1 as A,(select id from 表名 order by id limit 9000000,10:) as B where A.id == B.id;
  	# 括號內(nèi)可以看作為一張新表 然后進(jìn)行連表查詢即可 大大提升效率
  	
  	耗時 12s
  	
  

6、count優(yōu)化

• MyISAM 引擎把一個表的總行數(shù)存在了磁盤上，因此執(zhí)行count(*)的時候就會直接返回這個數(shù)，效率很高（僅僅在沒有where條件有效）

• innoDB 引擎就麻煩了，它執(zhí)行count(*)的時候，需要把數(shù)據(jù)一行一行地從引擎里面讀出來。然后累計計數(shù)

優(yōu)化思路：自己計數(shù)。

比如：我們可以自己維護(hù)一張表來專門存儲我們所需要記錄的數(shù)據(jù)，利用redis內(nèi)存數(shù)據(jù)庫 加一條數(shù)據(jù) 就+1等

count的幾種用法

1.count(主鍵)

InnoDB引擎會遍歷整張表,把每一行的主鍵id值都取出來,返回給服務(wù)層。服務(wù)層拿到主鍵后,直接按行進(jìn)行累加(主鍵不可能為nut)

2.count(字段)

沒有 not nul約束:? ?Innodb引擎會遍歷整張表把每一行的字段值都取出來,返回給服務(wù)層,服務(wù)層判斷是否為nu不為nu,計數(shù)累加。

有 not nu約束:? ?Innodb引擎會遍歷整張表把每一行的字段值都取出來,返回給服務(wù)層,直接按行進(jìn)行累加。

3.count(1)

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

4.count(*)

Innodb引擎并不會把全部字段取出來,而是專門做了優(yōu)化,不取值,服務(wù)層直接按行進(jìn)行累加。

按照效率排序的話, count字段)< count(主鍵id< count(1) c count(·),所以盡量使用 count(*)

7、update優(yōu)化

update student set no='2000100100' where id=1;

update student set no='2000100105' where name='韋一笑';

# Innode的行鎖是針對索引加的鎖,不是針對記錄加的鎖,并且該索引不能失效,否則會從行鎖升級為表鎖。
# 升級為表鎖就會降低性能！

文章來源地址http://www.zghlxwxcb.cn/news/detail-467163.html

到了這里，關(guān)于[MySQL--SQL優(yōu)化]的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！