SQL 優化_ZenDei技術網路在線

1. 插入數據 1.1 insert 如果我們需要一次性往資料庫表中插入多條記錄，可以從以下三個方面進行優化。 insert into tb_test values(1,'tom'); insert into tb_test values(2,'cat'); insert into tb_test ...

1. 插入數據

1.1 insert

如果我們需要一次性往資料庫表中插入多條記錄，可以從以下三個方面進行優化。

insert into tb_test values(1,'tom');

insert into tb_test values(2,'cat');

insert into tb_test values(3,'jerry');
.....

優化方案一

批量插入數據

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

優化方案二

手動控制事務

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

1.2 大批量插入數據

如果一次性需要插入大批量數據(比如: 幾百萬的記錄)，使用 insert 語句插入性能較低，此時可以使用 MySQL 資料庫提供的 load 指令進行插入。操作如下：

可以執行如下指令，將數據腳本文件中的數據載入到表結構中：

-- 客戶端連接服務端時，加上參數 -–local-infile

mysql –-local-infile -u root -p

-- 設置全局參數local_infile為1，開啟從本地載入文件導入數據的開關

set global local_infile = 1;

-- 執行load指令將準備好的數據，載入到表結構中

load data local infile '/root/sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n' ;

主鍵順序插入性能高於亂序插入

2. 主鍵優化

數據組織方式

InnoDB 存儲引擎中，表數據都是根據主鍵順序組織存放的，這種存儲方式的表稱為索引組織表 (index organized table IOT) 。

行數據，都是存儲在聚集索引的葉子節點上的。 InnoDB 的邏輯結構圖：

在 InnoDB 引擎中，數據行是記錄在邏輯結構 page 頁中的，而每一個頁的大小是固定的，預設 16K。那也就意味著，一個頁中所存儲的行也是有限的，如果插入的數據行 row 在該頁存儲不小，將會存儲到下一個頁中，頁與頁之間會通過指針連接。

頁分裂

頁可以為空，也可以填充一半，也可以填充 100%。每個頁包含了 2-N 行數據(如果一行數據過大，會行溢出)，根據主鍵排列。

主鍵順序插入效果

①. 從磁碟中申請頁，主鍵順序插

②. 第一個頁沒有滿，繼續往第一頁插入

③. 當第一個也寫滿之後，再寫入第二個頁，頁與頁之間會通過指針連接

④. 當第二頁寫滿了，再往第三頁寫入

主鍵亂序插入效果

①. 加入1#,2#頁都已經寫滿了，存放瞭如圖所示的數據

②. 此時再插入id為50的記錄，我們來看看會發生什麼現象

會再次開啟一個頁，寫入新的頁中嗎？

不會。因為，索引結構的葉子節點是有順序的。按照順序，應該存儲在47之後。

但是47所在的1#頁，已經寫滿了，存儲不了50對應的數據了。那麼此時會開闢一個新的頁 3#。

但是並不會直接將50存入3#頁，而是會將1#頁後一半的數據，移動到3#頁，然後在3#頁，插入50。

移動數據，並插入id為50的數據之後，那麼此時，這三個頁之間的數據順序是有問題的。 1#的下一個頁，應該是3#， 3#的下一個頁是2#。所以，此時，需要重新設置鏈表指針。

上述的這種現象，稱之為 "頁分裂"，是比較耗費性能的操作.

頁合併

目前表中已有數據的索引結構(葉子節點)如下：

當我們對已有數據進行刪除時，具體的效果如下:

當刪除一行記錄時，實際上記錄並沒有被物理刪除，只是記錄被標記（flaged）為刪除並且它的空間變得允許被其他記錄聲明使用。

當我們繼續刪除2#的數據記錄

當頁中刪除的記錄達到 MERGE_THRESHOLD（預設為頁的50%），InnoDB會開始尋找最靠近的頁（前或後）看看是否可以將兩個頁合併以優化空間使用。

刪除數據，並將頁合併之後，再次插入新的數據21，則直接插入3#頁

這個裡面所發生的合併頁的這個現象，就稱之為 "頁合併"。

知識小貼士：

MERGE_THRESHOLD：合併頁的閾值，可以自己設置，在創建表或者創建索引時指定。

索引設計原則
- 滿足業務需求的情況下，儘量降低主鍵的長度。
- 插入數據時，儘量選擇順序插入，選擇使用 AUTO_INCREMENT 自增主鍵。
- 儘量不要使用 UUID 做主鍵或者是其他自然主鍵，如身份證號。
- 業務操作時，避免對主鍵的修改。

3. order by 優化

MySQL的排序，有兩種方式：

Using filesort : 通過表的索引或全表掃描，讀取滿足條件的數據行，然後在排序緩衝區 sortbuffer 中完成排序操作，所有不是通過索引直接返回排序結果的排序都叫 FileSort 排序。

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

對於以上的兩種排序方式，Using index 的性能高，而 Using filesort 的性能低，我們在優化排序操作時，儘量要優化為 Using index。

order by優化原則:

根據排序欄位建立合適的索引，多欄位排序時，也遵循最左首碼法則。
儘量使用覆蓋索引。
多欄位排序, 一個升序一個降序，此時需要註意聯合索引在創建時的規則（ASC / DESC）。
如果不可避免的出現 filesort ，大數據量排序時，可以適當增大排序緩衝區大小
sort_buffer_size (預設256k) 。

4. order by 優化

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

5. limit優化

優化思路: 一般分頁查詢時，通過創建覆蓋索引能夠比較好地提高性能，可以通過覆蓋索引加子查詢形式進行優化。

explain select * from tb_sku t , (select id from tb_sku order by id
limit 2000000,10) a where t.id = a.id;

6. count優化

6.1 概述

select count(*) from tb_user ;

測試中，我們發現，如果數據量很大，在執行count操作時，是非常耗時的。

MyISAM 引擎把一個表的總行數存在了磁碟上，因此執行 count(*) 的時候會直接返回這個數，效率很高；但是如果是帶條件的 count，MyISAM 也慢。
InnoDB 引擎就麻煩了，它執行 count(*) 的時候，需要把數據一行一行地從引擎裡面讀出來，然後累積計數。

如果說要大幅度提升 InnoDB 表的 count 效率，主要的優化思路：自己計數(可以藉助於redis 這樣的資料庫進行,但是如果是帶條件的 count 又比較麻煩了)。

6.2 count用法

count() 是一個聚合函數，對於返回的結果集，一行行地判斷，如果 count 函數的參數不是 NULL，累計值就加 1，否則不加，最後返回累計值。

按照效率排序的話，count(欄位) < count(主鍵 id) < count(1) ≈ count()，所以盡
量使用 count()。

7. update優化

我們主要需要註意一下update語句執行時的註意事項。

update course set name = 'javaEE' where id = 1 ;

當我們在執行刪除的SQL語句時，會鎖定id為1這一行的數據，然後事務提交之後，行鎖釋放。

但是當我們在執行如下SQL時。

update course set name = 'SpringBoot' where name = 'PHP' ;

當我們開啟多個事務，在執行上述的SQL時，我們發現行鎖升級為了表鎖。導致該 update 語句的性能大大降低。

InnoDB 的行鎖是針對索引加的鎖，不是針對記錄加的鎖 ,並且該索引不能失效，否則會從行鎖升級為表鎖。