這是一篇純講SQL語句優化的文章!!!| 博學谷狂野架構師
- 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指令進行插入。操作如下:
可以執行如下指令,將數據腳本文件中的數據加載到表結構中:
-- 客户端連接服務端時,加上參數 -–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)。
行數據,都是存儲在聚集索引的葉子節點上的。而我們之前也講解過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做主鍵
或者是其他自然主鍵
,如身份證號。 - 業務操作時,
避免對主鍵的修改
。
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。
為了解決上述的問題,我們可以創建一個索引,這個聯合索引中 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)
升序/降序聯合索引結構圖示:
由上述的測試,我們得出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語句的性能大大降低。 本文由
傳智教育博學谷狂野架構師
教研團隊發佈。如果本文對您有幫助,歡迎
關注
和點贊
;如果您有任何建議也可留言評論
或私信
,您的支持是我堅持創作的動力。轉載請註明出處!
- ElasticSearch還能性能調優,漲見識、漲見識了!!!
- 【必須收藏】別再亂找TiDB 集羣部署教程了,這篇保姆級教程來幫你!!| 博學谷狂野架構師
- 【建議收藏】7000 字的TIDB保姆級簡介,你見過嗎
- Tomcat架構設計剖析 | 博學谷狂野架構師
- 你可能不那麼知道的Tomcat生命週期管理 | 博學谷狂野架構師
- 大哥,這是併發不是並行,Are You Ok?
- 為啥要重學Tomcat?| 博學谷狂野架構師
- 這是一篇純講SQL語句優化的文章!!!| 博學谷狂野架構師
- 捲起來!!!看了這篇文章我才知道MySQL事務&MVCC到底是啥?
- 為什麼99%的程序員都做不好SQL優化?
- 如何搞定MySQL鎖(全局鎖、表級鎖、行級鎖)?這篇文章告訴你答案!太TMD詳細了!!!
- 【建議收藏】超詳細的Canal入門,看這篇就夠了!!!
- 從菜鳥程序員到高級架構師,竟然是因為這個字final
- 為什麼95%的Java程序員,都是用不好Synchronized?
- 99%的Java程序員者,都敗給這一個字!
- 8000 字,就説一個字Volatile
- 98%的程序員,都沒有研究過JVM重排序和順序一致性
- 來一波騷操作,Java內存模型
- 時隔多年,這次我終於把動態代理的源碼翻了個地兒朝天
- 再有人問你分佈式事務,把這篇文章砸過去給他