Mysql高級5-SQL優化_ZenDei技術網路在線

一、插入數據優化 1.1 批量插入如果有多條數據需要同時插入，不要每次插入一條，然後分多次插入，因為每執行一次插入的操作，都要進行資料庫的連接，多個操作就會連接多次，而一次批量操作只需要連接1次 1.2 手動提交事務因為Mysql預設每執行一次操作，就會提交一次事務，這樣就會涉及到頻繁的事務的開 ...

一、插入數據優化

　　1.1 批量插入

　　　　如果有多條數據需要同時插入，不要每次插入一條，然後分多次插入，因為每執行一次插入的操作，都要進行資料庫的連接，多個操作就會連接多次，而一次批量操作只需要連接1次

　　1.2 手動提交事務

　　　　因為Mysql預設每執行一次操作，就會提交一次事務，這樣就會涉及到頻繁的事務的開啟與關閉

start transaction;
　　insert into 表名 values(),(),();
　　insert into 表名 values(),(),();
　　insert into 表名 values(),(),();
commit;

　　1.3 主鍵順序插入

　　　　主鍵一般是預設自增的，但是也可以手動增加，這裡不建議手動亂序增加，而是使用預設的順序增加，原因會在後面解釋。

　　1.4 大批量插入數據

　　　　如果一次性需要插入大批量數據，使用insert語句插入性能較低，此時可以使用Mysql資料庫提供的load指令進行插入，

　　　　首先在連接資料庫的時候需要加上 --local-infile 參數

mysql --local-infile -u root -p

　　　　在使用本地文件載入功能的時候，需要先查看本地載入文件選項是否開啟的

mysql> select @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

　　　　說明1：0表示本地載入文件並未開啟

　　　　開啟本地載入文件的語句

mysql> set global local_infile = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

　　　　創建一個空表tb_user，其表結構如下

mysql> desc tb_user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(50) | NO   | UNI | NULL    |                |
| password | varchar(50) | NO   |     | NULL    |                |
| name     | varchar(20) | NO   |     | NULL    |                |
| birthday | date        | YES  |     | NULL    |                |
| sex      | char(1)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

　　　　使用load載入本地文件 'tb_user_data.sql' 內容到新創建的表中,其中tb_user_data.sql中的測試數據如下

houlei@houleideMacBook-Pro Desktop % cat tb_user_data.sql
1,a,aa,aaa,2023-07-01,1
2,b,bb,bbb,2023-07-02,0
3,c,cc,ccc,2023-07-03,1
4,d,dd,ddd,2023-07-04,0
5,e,ee,eee,2023-07-05,1
6,f,ff,fff,2023-07-06,0
7,g,gg,ggg,2023-07-07,1
houlei@houleideMacBook-Pro Desktop %

　　　　使用load載入本地文件 'tb_user_data.sql' 內容到新創建的表中　　

mysql> load data local infile '/Users/houlei/Desktop/tb_user_data.sql' into table tb_user fields terminated by ',' lines terminated by '\n';
Query OK, 7 rows affected (0.01 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0

　　　　說明1： load data local infile 是載入本地文件的意思，

　　　　說明2：'/Users/houlei/Desktop/tb_user_data.sql'是文件路徑

　　　　說明3：into table tb_user 是將文件中的數據，插入到tb_user表中

　　　　說明4：fields terminated by ',' 是說每個欄位之間的數據是使用','分割的

　　　　說明5：lines terminated by '\n' 是說每一行之間的數據使用的是‘\n’分割的

　　　　說明6：本方法只是舉例，在實際運用大數據量插入時100萬條數據的插入至少要數分鐘，如果使用load方法只需要十幾秒

二、主鍵優化

　　2.1 數據組織方式

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

　　　　說明1：在索引的B+數中所有的數據保存在葉子節點上，非葉子節點只保存主鍵key的值

　　　　說明2：索引中的各個節點都是保存在邏輯結構頁上面的，一頁預設大小16K

　　2.2 頁分裂

　　　　頁可以為空，也可以填充一半，也可以填充100%，每個頁包含了2至N行數據，根據主鍵排列

　　　　情況1：主鍵順序插入

　　　　說明1：row是行數據，每一頁上可以存放多個行數據。　　　　

　　　　情況2：主鍵亂序插入

　　　　說明1：當我們想要在插入一個id=50的數據時，會發生頁分裂

　　　　說明2：這時會將 1#page 頁裡面的數據超過 50% 的數據，移動到新開闢的 3#page 頁中

　　　　說明3：然後將 id=50 的數據也拼接到 3#page 頁中

　　　　說明4：這時就會出現一個問題，3#page 中的索引比 2#page 頁中的索引小，所以還需要將 3#page 頁前置，這就叫頁分裂

　　2.3 頁合併

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

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

　　　　說明1：這時在 2#page 刪除了13,14,15,16數據後，該頁空餘空間超過50%時就會尋找前一頁或者後一頁，是否同樣有不滿足50%,可以合併的

　　　　說明2：這時 1#page 頁是滿的，不能合併，3#page 頁不滿可以合併，所以 3#page 頁遷移到 2#page 頁中

　　　　說明3：這時如果在有數據20插入就可以直接插入到3#page頁上了，這就是頁合併。

　　2.4 主鍵設計原則

- 滿足業務需求的情況下，儘量減低主鍵的長度。
- 插入數據時，儘量選擇順序插入，選擇使用auto_incerment自增主鍵，
- 儘量不要用uuid作主鍵或者其他自然主鍵如身份證號，因為這個值是無需的，會存在頁分裂情況。

三、order by優化

　　3.1 Using filesort

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

　　3.2 Using index

　　　　通過有序索引順序掃描直接返回有序數據，這種情況即為using index，不需要額外的排序，操作效率高，即排序的列表欄位符合覆蓋索引。

　　3.3 案例

　　　　emp表結構：

mysql> desc emp;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int         | NO   | PRI | NULL    | auto_increment |
| name      | varchar(20) | YES  |     | NULL    |                |
| age       | int         | YES  |     | NULL    |                |
| job       | varchar(20) | YES  |     | NULL    |                |
| salary    | int         | YES  |     | NULL    |                |
| entrydate | date        | YES  |     | NULL    |                |
| managerid | int         | YES  |     | NULL    |                |
| dept_id   | int         | YES  | MUL | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

　　　　emp表中索引情況

mysql> show index from emp;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | fk_dept  |            1 | dept_id     | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

　　　　案例1：對查詢結果進行按 salary 和 age 都進行升序排序

mysql> explain select salary,age from emp order by salary, age;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

　　　　說明1：Extra 中值為 Using filesort 說明是先查出來需要的數據，然後再排序的,效率不高。

　　　　說明2：為什麼會出現Using filesort呢？因為查詢的這些欄位在查詢之前是無須的，索引需要先將數據查詢出來，然後再做排序，這樣才能得到想要的排序好的數據。

　　　　案例2：給 salary 和 age 添加一個聯合排序

mysql> create index salary_age_idx on emp(salary,age);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from emp;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY        |            1 | id          | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | fk_dept        |            1 | dept_id     | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | salary_age_idx |            1 | salary      | A         |           7 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | salary_age_idx |            2 | age         | A         |           7 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)

　　　　說明1：聯合索引 salary_age_idx 中 salary 是第一索引欄位，age 是第二索引欄位

　　　　說明2：Collation 中A 代表升序，D 代表降序　　　　

　　　　案例3：再次使用 order by 對 salary 和 age 進行升序排序

mysql> explain select salary,age from emp order by salary,age;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | salary_age_idx | 10      | NULL |    7 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select salary,age from emp order by salary;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | salary_age_idx | 10      | NULL |    7 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

　　　　說明1：在做升序排列時，無論 order by 後面是組合索引的全部欄位，還是只有部分欄位，這時 Extra 的值都是Usind index，所以其查詢的結果直接就是排序好的結果

　　　　說明2：為什麼呢？因為這個時候 salary和age是一個聯合索引，索引在文件中是一個帶順序的b+數結構，所以將這個欄位建立一個聯合索引，就意味著使用索引查詢的時候，就已經是帶著順序的數據了，所以這個時候就不需要在將數據從新在排序了，這樣的查詢效率就會更高。

　　　　案例4: order by 中的欄位順序和索引順序不一致的情況

mysql> explain select salary,age from emp order by age,  salary;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | salary_age_idx | 10      | NULL |    7 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

　　　　說明1：這個時候order by 是age在前，salary在後，和索引的順序不一致，仍然會觸發索引，使用Using index，但是也會使用Using filesort,所以推薦大家使用正確的索引順序的欄位來進行排序

　　　　案例5：對salary和age做降序查詢

mysql> explain select salary,age from emp order by salary desc, age desc;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | salary_age_idx | 10      | NULL |    7 |   100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id,salary,age from emp order by salary desc;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | salary_age_idx | 10      | NULL |    7 |   100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

　　　　說明1：無論是對salary和age同時做降序還是對其中一個欄位做降序排列，都會出現 Backward index scan; Using index，其中 Backward index scan 是反向掃描索引

　　　　說明2：這是因為索引中預設的順序是升序的，而做降序排列，就需要反向掃描索引了

　　　　案例7：創建一個 salary 和 age 都是降序的索引

create index salary_age_desc_idx on emp(salary desc, age desc);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

　　　　查詢目前所有的索引

mysql> show index from emp;
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name