主頁 > 後端開發 > 這是一篇純講SQL陳述句優化的文章！！！| 博學谷狂野架構師

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

2023-03-16 07:32:00 後端開發

  • insert

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

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

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

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

.....
  1. 優化方案一:

批量插入資料

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
  1. 優化方案二

手動控制事務

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;
  1. 優化方案三

主鍵順序插入，性能要高于亂序插入，

主鍵亂序插入 : 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' ;

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

實體演示：

  1. 創建表結構
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 ;
  1. 設定引數
-- 客戶端連接服務端時，加上引數 -–local-infile
mysql –-local-infile -u root -p

-- 設定全域引數local_infile為1，開啟從本地加載檔案匯入資料的開關
set global local_infile = 1;
  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時，主鍵順序插入性能高于亂序插入

主鍵優化

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

  1. 資料組織方式

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

file

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

file

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

  1. 頁分裂

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

  • 主鍵順序插入效果

    • 從磁盤中申請頁， 主鍵順序插入
      file

    • 第一個頁沒有滿，繼續往第一頁插入
      file

    • 當第一個也寫滿之后，再寫入第二個頁，頁與頁之間會通過指標連接

file

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

file

  • 主鍵亂序插入效果

    • 加入1#,2#頁都已經寫滿了，存放了如圖所示的資料

file

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

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

    file

    不會，因為，索引結構的葉子節點是有順序的，按照順序，應該存盤在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. 索引設計原則
    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，

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

  1. 資料準備

把之前測驗時，為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
  1. 執行排序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， 排序性能較低，

  1. 創建索引
-- 創建索引
create index idx_user_age_phone_aa on tb_user(age,phone);
  1. 創建索引后，根據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，性能就是比較高的了，

  1. 創建索引后，根據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版本中，支持降序索引，我們也可以創建降序索引，

  1. 根據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，

  1. 根據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 倒序排序，

  1. 創建聯合索引(age 升序排序，phone 倒序排序)
create index idx_phone_age_ad on tb_user(age asc,phone desc);

image

  1. 然后再次執行如下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優化原則:

  1. 根據排序欄位建立合適的索引，多欄位排序時，也遵循最左前綴法則，
  2. 盡量使用覆寫索引，
  3. 多欄位排序, 一個升序一個降序，此時需要注意聯合索引在創建時的規則（ASC/DESC），
  4. 如果不可避免的出現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，原因是因為對于分組操作，在聯合索引中，也是符合最左前綴法則的，

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

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

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陳述句的性能大大降低，

本文由傳智教育博學谷狂野架構師教研團隊發布，

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

轉載請注明出處！

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/546942.html

標籤：Java

上一篇：SpringCloud微服務實戰——搭建企業級開發框架（五十一）：微服務安全加固—自定義Gateway攔截器實作防止SQL注入/XSS攻擊

下一篇：Mysql基礎知識

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • 【C++】Microsoft C++、C 和匯編程式檔案

    ......

    uj5u.com 2020-09-10 00:57:23 more
  • 例外宣告

    相比于斷言適用于排除邏輯上不可能存在的狀態，例外通常是用于邏輯上可能發生的錯誤。 例外宣告 Item 1：當函式不可能拋出例外或不能接受拋出例外時，使用noexcept 理由 如果不打算拋出例外的話，程式就會認為無法處理這種錯誤，并且應當盡早終止，如此可以有效地阻止例外的傳播與擴散。 示例 //不可 ......

    uj5u.com 2020-09-10 00:57:27 more
  • Codeforces 1400E Clear the Multiset(貪心 + 分治)

    鏈接：https://codeforces.com/problemset/problem/1400/E 來源：Codeforces 思路：給你一個陣列，現在你可以進行兩種操作，操作1：將一段沒有 0 的區間進行減一的操作，操作2：將 i 位置上的元素歸零。最終問：將這個陣列的全部元素歸零后操作的最少 ......

    uj5u.com 2020-09-10 00:57:30 more
  • UVA11610 【Reverse Prime】

    本人看到此題沒有翻譯，就附帶了一個自己的翻譯版本 思考 這一題，它的第一個要求是找出所有 $7$ 位反向質數及其質因數的個數。 我們應該需要質數篩篩選1~$10^{7}$的所有數，這里就不慢慢介紹了。但是，重讀題，我們突然發現反向質數都是 $7$ 位，而將它反過來后的數字卻是 $6$ 位數，這就說明 ......

    uj5u.com 2020-09-10 00:57:36 more
  • 統計區間素數數量

    1 #pragma GCC optimize(2) 2 #include <bits/stdc++.h> 3 using namespace std; 4 bool isprime[1000000010]; 5 vector<int> prime; 6 inline int getlist(int ......

    uj5u.com 2020-09-10 00:57:47 more
  • C/C++編程筆記：C++中的 const 變數詳解，教你正確認識const用法

    1、C中的const 1、區域const變數存放在堆疊區中，會分配記憶體（也就是說可以通過地址間接修改變數的值）。測驗代碼如下： 運行結果： 2、全域const變數存放在只讀資料段（不能通過地址修改，會發生寫入錯誤）， 默認為外部聯編，可以給其他源檔案使用（需要用extern關鍵字修飾） 運行結果： ......

    uj5u.com 2020-09-10 00:58:04 more
  • 【C++犯錯記錄】VS2019 MFC添加資源不懂如何修改資源宏ID

    1. 首先在資源視圖中，添加資源 2. 點擊新添加的資源，復制自動生成的ID 3. 在解決方案資源管理器中找到Resource.h檔案，編輯，使用整個專案搜索和替換的方式快速替換 宏宣告 4. Ctrl+Shift+F 全域搜索，點擊查找全部，然后逐個替換 5. 為什么使用搜索替換而不使用屬性視窗直 ......

    uj5u.com 2020-09-10 00:59:11 more
  • 【C++犯錯記錄】VS2019 MFC不懂的批量添加資源

    1. 打開資源頭檔案Resource.h，在其中預先定義好宏 ID（不清楚其實ID值應該設定多少，可以先新建一個相同的資源項，再在這個資源的ID值的基礎上遞增即可） 2. 在資源視圖中選中專案資源，按F7編輯資源檔案，按 ID 型別 相對路徑的形式添加 資源。（別忘了先把檔案拷貝到專案中的res檔案 ......

    uj5u.com 2020-09-10 01:00:19 more
  • C/C++編程筆記：關于C++的參考型別，專供新手入門使用

    今天要講的是C++中我最喜歡的一個用法——參考，也叫別名。 參考就是給一個變數名取一個變數名，方便我們間接地使用這個變數。我們可以給一個變數創建N個參考，這N + 1個變數共享了同一塊記憶體區域。(參考型別的變數會占用記憶體空間，占用的記憶體空間的大小和指標型別的大小是相同的。雖然參考是一個物件的別名，但 ......

    uj5u.com 2020-09-10 01:00:22 more
  • 【C/C++編程筆記】從頭開始學習C ++：初學者完整指南

    眾所周知，C ++的學習曲線陡峭，但是花時間學習這種語言將為您的職業帶來奇跡，并使您與其他開發人員區分開。您會更輕松地學習新語言，形成真正的解決問題的技能，并在編程的基礎上打下堅實的基礎。 C ++將幫助您養成良好的編程習慣（即清晰一致的編碼風格，在撰寫代碼時注釋代碼，并限制類內部的可見性），并且由 ......

    uj5u.com 2020-09-10 01:00:41 more
最新发布
  • Rust中的智能指標:Box<T> Rc<T> Arc<T> Cell<T> RefCell<T> Weak

    Rust中的智能指標是什么 智能指標（smart pointers）是一類資料結構，是擁有資料所有權和額外功能的指標。是指標的進一步發展 指標（pointer）是一個包含記憶體地址的變數的通用概念。這個地址參考，或 ” 指向”（points at）一些其 他資料 。參考以 & 符號為標志并借用了他們所 ......

    uj5u.com 2023-04-20 07:24:10 more
  • Java的值傳遞和參考傳遞

    值傳遞不會改變本身，參考傳遞（如果傳遞的值需要實體化到堆里）如果發生修改了會改變本身。 1.基本資料型別都是值傳遞 package com.example.basic; public class Test { public static void main(String[] args) { int ......

    uj5u.com 2023-04-20 07:24:04 more
  • [2]SpinalHDL教程——Scala簡單入門

    第一個 Scala 程式 shell里面輸入 $ scala scala> 1 + 1 res0: Int = 2 scala> println("Hello World!") Hello World! 檔案形式 object HelloWorld { /* 這是我的第一個 Scala 程式 * 以 ......

    uj5u.com 2023-04-20 07:23:58 more
  • 理解函式指標和回呼函式

    理解 函式指標 指向函式的指標。比如： 理解函式指標的偽代碼 void (*p)(int type, char *data); // 定義一個函式指標p void func(int type, char *data); // 宣告一個函式func p = func; // 將指標p指向函式func ......

    uj5u.com 2023-04-20 07:23:52 more
  • Django筆記二十五之資料庫函式之日期函式

    本文首發于公眾號：Hunter后端 原文鏈接：Django筆記二十五之資料庫函式之日期函式 日期函式主要介紹兩個大類，Extract() 和 Trunc() Extract() 函式作用是提取日期，比如我們可以提取一個日期欄位的年份，月份，日等資料 Trunc() 的作用則是截取，比如 2022-0 ......

    uj5u.com 2023-04-20 07:23:45 more
  • 一天吃透JVM面試八股文

    什么是JVM？ JVM，全稱Java Virtual Machine（Java虛擬機），是通過在實際的計算機上仿真模擬各種計算機功能來實作的。由一套位元組碼指令集、一組暫存器、一個堆疊、一個垃圾回收堆和一個存盤方法域等組成。JVM屏蔽了與作業系統平臺相關的資訊，使得Java程式只需要生成在Java虛擬機 ......

    uj5u.com 2023-04-20 07:23:31 more
  • 使用Java接入小程式訂閱訊息！

    更新完微信服務號的模板訊息之后，我又趕緊把微信小程式的訂閱訊息給實作了！之前我一直以為微信小程式也是要企業才能申請，沒想到小程式個人就能申請。 訊息推送平臺🔥推送下發【郵件】【短信】【微信服務號】【微信小程式】【企業微信】【釘釘】等訊息型別。 https://gitee.com/zhongfuch ......

    uj5u.com 2023-04-20 07:22:59 more
  • java -- 緩沖流、轉換流、序列化流

    緩沖流 緩沖流, 也叫高效流, 按照資料型別分類： 位元組緩沖流：BufferedInputStream，BufferedOutputStream 字符緩沖流：BufferedReader，BufferedWriter 緩沖流的基本原理，是在創建流物件時，會創建一個內置的默認大小的緩沖區陣列，通過緩沖 ......

    uj5u.com 2023-04-20 07:22:49 more
  • Java-SpringBoot-Range請求頭設定實作視頻分段傳輸

    老實說，人太懶了，現在基本都不喜歡寫筆記了，但是網上有關Range請求頭的文章都太水了 下面是抄的一段StackOverflow的代碼...自己大修改過的，寫的注釋挺全的，應該直接看得懂，就不解釋了 寫的不好...只是希望能給視頻網站開發的新手一點點幫助吧. 業務場景：視頻分段傳輸、視頻多段傳輸（理 ......

    uj5u.com 2023-04-20 07:22:42 more
  • Windows 10開發教程_編程入門自學教程_菜鳥教程-免費教程分享

    教程簡介 Windows 10開發入門教程 - 從簡單的步驟了解Windows 10開發，從基本到高級概念，包括簡介，UWP，第一個應用程式，商店，XAML控制元件，資料系結，XAML性能，自適應設計，自適應UI，自適應代碼，檔案管理，SQLite資料庫，應用程式到應用程式通信，應用程式本地化，應用程式 ......

    uj5u.com 2023-04-20 07:22:35 more

Copyright © 2010-2020 有解無憂