[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的行鎖是針對索引加的鎖,不是針對記錄加的鎖,並且該索引不能失效,否則會從行鎖升級為表鎖。
# 升級為表鎖就會降低性能！