這是一篇純講SQL語句優化的文章！！！| 博學谷狂野架構師

語言: CN / TW / HK

時間 2023-03-15 22:03:46 博學谷狂野架構師

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

大批量插入數據

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

file

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

-- 客户端連接服務端時，加上參數 -–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' ;

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

實例演示：

創建表結構

CREATE TABLE `tb_user` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`username` VARCHAR(50) NOT NULL,
	`password` VARCHAR(50) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`birthday` DATE DEFAULT NULL,
	`sex` CHAR(1) DEFAULT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

設置參數

-- 客户端連接服務端時，加上參數 -–local-infile
mysql –-local-infile -u root -p

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

load加載數據

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

mysql> load data local infile '/root/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;
Query OK, 1000000 rows affected (15.47 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select count(*) from tb_user;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.31 sec)

我們看到，插入100w的記錄，15.47s就完成了，性能很好。

在load時，主鍵順序插入性能高於亂序插入

主鍵優化

主鍵順序插入的性能是要高於亂序插入的。我們來介紹一下具體的原因，然後再分析一下主鍵又該如何設計。

數據組織方式

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

file

行數據，都是存儲在聚集索引的葉子節點上的。而我們之前也講解過InnoDB的邏輯結構圖：

file

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

頁分裂

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

主鍵順序插入效果
- 從磁盤中申請頁，主鍵順序插入
- 第一個頁沒有滿，繼續往第一頁插入
- 當第一個也寫滿之後，再寫入第二個頁，頁與頁之間會通過指針連接

file

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

file

主鍵亂序插入效果
- 加入1#,2#頁都已經寫滿了，存放了如圖所示的數據

file

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

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

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

file

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

file

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

file

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

file

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

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

file

當我們對已有數據進行刪除時，具體的效果如下:
當刪除一行記錄時，實際上記錄並沒有被物理刪除，只是記錄被標記（flaged）為刪除並且它的空間變得允許被其他記錄聲明使用。

file

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

file

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

file

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

file

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

知識小貼士：

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

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

file

order by 優化

MySQL的排序，有兩種方式：

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

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

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

接下來，我們來做一個測試：

數據準備

把之前測試時，為tb_user表所建立的部分索引直接刪除掉

drop index idx_user_phone on tb_user;
drop index idx_user_phone_name on tb_user;
drop index idx_user_name on tb_user;

mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |          23 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_phone       |            1 | phone       | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_phone_name  |            1 | phone       | A         |      935064 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_phone_name  |            2 | name        | A         |      951995 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_name        |            1 | name        | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            2 | age         | A         |          22 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            3 | status      | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro         |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_email_5          |            1 | email       | A         |          23 |        5 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
10 rows in set (0.00 sec)

mysql> drop index idx_user_phone on tb_user;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index idx_user_phone_name on tb_user;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index idx_user_name on tb_user;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

執行排序SQL

explain select id,age,phone from tb_user order by age;

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

explain select id,age,phone from tb_user order by age, phone ;

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

由於 age, phone 都沒有索引，所以此時再排序時，出現Using filesort，排序性能較低。

創建索引

-- 創建索引
create index idx_user_age_phone_aa on tb_user(age,phone);

創建索引後，根據age, phone進行升序排序

explain select id,age,phone from tb_user order by age;

mysql> explain select id,age,phone from tb_user order by age;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_user_age_phone_aa | 48      | NULL | 971649 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

建立索引之後，再次進行排序查詢，就由原來的Using filesort，變為了 Using index，性能就是比較高的了。

創建索引後，根據age, phone進行降序排序

explain select id,age,phone from tb_user order by age desc , phone desc;

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

也出現 Using index，但是此時Extra中出現了 Backward index scan，這個代表反向掃描索引，因為在MySQL中我們創建的索引，默認索引的葉子節點是從小到大排序的，而此時我們查詢排序時，是從大到小，所以，在掃描時，就是反向掃描，就會出現 Backward index scan。在MySQL8版本中，支持降序索引，我們也可以創建降序索引。

根據phone，age進行升序排序，phone在前，age在後。

explain select id,age,phone from tb_user order by phone , age;

mysql> explain select id,age,phone from tb_user order by phone , age;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-----------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_user_age_phone_aa | 48      | NULL | 971649 |   100.00 | Using index; Using filesor |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

排序時,也需要滿足最左前綴法則,否則也會出現 filesort。因為在創建索引的時候， age是第一個字段，phone是第二個字段，所以排序時，也就該按照這個順序來，否則就會出現 Usingfilesort。

根據age, phone進行降序一個升序，一個降序

explain select id,age,phone from tb_user order by age asc , phone desc;

mysql> explain select id,age,phone from tb_user order by age asc , phone desc;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-----------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_user_age_phone_aa | 48      | NULL | 971649 |   100.00 | Using index; Using filesort |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

因為創建索引時，如果未指定順序，默認都是按照升序排序的，而查詢時，一個升序，一個降序，此時就會出現Using filesort。 file

為了解決上述的問題，我們可以創建一個索引，這個聯合索引中 age 升序排序，phone 倒序排序。

創建聯合索引(age 升序排序，phone 倒序排序)

create index idx_phone_age_ad on tb_user(age asc,phone desc);

然後再次執行如下SQL

explain select id,age,phone from tb_user order by age asc,phone desc;

mysql> explain select id,age,phone from tb_user order by age asc,phone desc;
+----+-------------+---------+------------+-------+---------------+------------------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key              | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_phone_age_ad | 48      | NULL | 971649 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

升序/降序聯合索引結構圖示:

file

由上述的測試,我們得出order by優化原則:

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

group by 優化

分組操作，我們主要來看看索引對於分組操作的影響。

首先我們先將 tb_user 表的索引全部刪除掉。

drop index idx_user_pro_age_sta on tb_user;
drop index idx_email_5 on tb_user;
drop index idx_user_age_phone_aa on tb_user;
drop index idx_user_age_phone_ad on tb_user;

mysql> show index from tb_user;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY  |            1 | id          | A         |          23 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

接下來，在沒有索引的情況下，執行如下SQL，查詢執行計劃：

explain select profession , count(*) from tb_user group by profession;

mysql> explain select profession , count(*) from tb_user group by profession ;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra           |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 971649 |   100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

然後，我們在針對於 profession ， age， status 創建一個聯合索引。

create index idx_pro_age_sta on tb_user(profession,age,status);

緊接着，再執行前面相同的SQL查看執行計劃。

mysql> explain select profession , count(*) from tb_user group by profession;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_pro_age_sta | idx_pro_age_sta | 54      | NULL | 971649 |   100.00 | Using index |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

再執行如下的分組查詢SQL，查看執行計劃：

mysql> explain select profession , count(*) from tb_user group by profession,age;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_pro_age_sta | idx_pro_age_sta | 54      | NULL | 971649 |   100.00 | Using index |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select age , count(*) from tb_user group by age;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+------------------------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows   | filtered | Extra                        |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_pro_age_sta | idx_pro_age_sta | 54      | NULL | 971649 |   100.00 | Using index; Using temporary |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

我們發現，如果僅僅根據age分組，就會出現 Using temporary ；而如果是根據profession,age兩個字段同時分組，則不會出現 Using temporary。原因是因為對於分組操作，在聯合索引中，也是符合最左前綴法則的。

所以，在分組操作中，我們需要通過以下兩點進行優化，以提升性能：

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

limit 優化

在數據量比較大時，如果進行limit分頁查詢，在查詢時，越往後，分頁查詢效率越低。

我們一起來看看執行limit分頁查詢耗時對比：

mysql> select * from tb_user limit 0,10;
10 rows in set (0.00 sec)

mysql> select * from tb_user limit 100,10;
10 rows in set (0.00 sec)

mysql> select * from tb_user limit 1000,10;
10 rows in set (0.00 sec)

mysql> select * from tb_user limit 50000,10;
10 rows in set (0.01 sec)

mysql> select * from tb_user limit 500000,10;
10 rows in set (0.16 sec)

mysql> select * from tb_user limit 900000,10;
10 rows in set (0.28 sec)

通過測試我們會看到，越往後，分頁查詢效率越低，這就是分頁查詢的問題所在。

因為，當在進行分頁查詢時，如果執行 limit 2000000,10 ，此時需要MySQL排序前2000010 記錄，僅僅返回 2000000 - 2000010 的記錄，其他記錄丟棄，查詢排序的代價非常大。

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

explain select u.* from tb_user u,(select id from tb_user order by id limit 900000,10) a where u.id = a.id;

mysql> explain select u.* from tb_user u,(select id from tb_user order by id limit 900000,10) a where u.id = a.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 900010 |   100.00 | NULL        |
|  1 | PRIMARY     | u          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | a.id |      1 |   100.00 | NULL        |
|  2 | DERIVED     | tb_user    | NULL       | index  | NULL          | PRIMARY | 4       | NULL | 900010 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

count 優化

 select count(*) from tb_user;

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

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

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

count 用法

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

用法：count（*）、count（主鍵）、count（字段）、count（數字）

count 用法	含義
count(主鍵)	InnoDB 引擎會遍歷整張表，把每一行的主鍵id 值都取出來，返回給服務層。服務層拿到主鍵後，直接按行進行累加(主鍵不可能為null)
count(字段)	沒有not null 約束 : InnoDB 引擎會遍歷整張表把每一行的字段值都取出來，返回給服務層，服務層判斷是否為null，不為null，計數累加。有not null 約束：InnoDB 引擎會遍歷整張表把每一行的字段值都取出來，返回給服務層，直接按行進行累加。
count(數字)	InnoDB 引擎遍歷整張表，但不取值。服務層對於返回的每一行，放一個數字“1”進去，直接按行進行累加。
count(*)	InnoDB引擎並不會把全部字段取出來，而是專門做了優化，不取值，服務層直接按行進行累加。

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

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的行鎖是針對索引加的鎖，不是針對記錄加的鎖，並且該索引不能失效，否則會從行鎖升級為表鎖。也就是説我這邊事務沒有提交的話，其他關於這個表的update都不會執行成功，導致該update語句的性能大大降低。本文由傳智教育博學谷狂野架構師教研團隊發佈。

如果本文對您有幫助，歡迎關注和點贊；如果您有任何建議也可留言評論或私信，您的支持是我堅持創作的動力。

轉載請註明出處！

「其他文章」