**前言：**

我們都知道，當執行 select 查詢語句時，用沒用到索引區別是很大的，若沒用到索引，一條 select 語句可能執行好幾秒或更久，若使用到索引則可能瞬間完成。那麼當執行 update 語句時，用沒用到索引有什麼區別呢，執行時間相差大嗎？本篇文章我們一起來探究下。

#### 1. update SQL 測試

為了對比出差距，這裡筆者建立兩張一樣資料的大表，一張有普通索引，一張無普通索引，我們來對比下二者的差別。

```sql
# tb_noidx 表無普通索引
mysql> show create table tb_noidx\G
*************************** 1. row ***************************
       Table: tb_noidx
Create Table: CREATE TABLE `tb_noidx` (
  `increment_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  `col1` char(32) NOT NULL COMMENT '欄位1',
  `col2` char(32) NOT NULL COMMENT '欄位2',
  ...
  `del` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否刪除',
) ENGINE=InnoDB AUTO_INCREMENT=3696887 DEFAULT CHARSET=utf8 COMMENT='無索引表'

mysql> select count(*) from tb_noidx;
+----------+
| count(*) |
+----------+
|  3590105 |
+----------+

mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB
    -> from information_schema.tables where table_schema='testdb' and table_name = 'tb_noidx'; 
+----------------+-----------------+
| data_length_MB | index_length_MB |
+----------------+-----------------+
| 841.98MB       | 0.00MB          |
+----------------+-----------------+

# tb_withidx 表有普通索引
mysql> show create table tb_withidx\G
*************************** 1. row ***************************
       Table: tb_withidx
Create Table: CREATE TABLE `tb_withidx` (
  `increment_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  `col1` char(32) NOT NULL COMMENT '欄位1',
  `col2` char(32) NOT NULL COMMENT '欄位2',
  ...
  `del` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否刪除',
  PRIMARY KEY (`increment_id`),
  KEY `idx_col1` (`col1`),
  KEY `idx_del` (`del`)
) ENGINE=InnoDB AUTO_INCREMENT=3696887 DEFAULT CHARSET=utf8 COMMENT='有索引表'

mysql> select count(*) from tb_withidx;
+----------+
| count(*) |
+----------+
|  3590105 |
+----------+

mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB
    -> from information_schema.tables where table_schema='testdb' and table_name = 'tb_withidx'; 
+----------------+-----------------+
| data_length_MB | index_length_MB |
+----------------+-----------------+
| 841.98MB       | 210.50MB        |
+----------------+-----------------+
```

這裡說明下，tb_noidx 和 tb_withidx 兩張表資料完全相同，表大概有 360W 條資料，約佔用 840M 空間。其中 col1 欄位區分度較高，del 欄位區分度很低，下面我們分別以這兩個欄位為篩選條件來執行 update 語句：

mysql> update tb_withidx set col2 = '48348a10d7794d269ecf10f9e3f20b52' where col1 = '48348a10d7794d269ecf10f9e3f20b52';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update tb_noidx set col2 = '48348a10d7794d269ecf10f9e3f20b52' where col1 = '48348a10d7794d269ecf10f9e3f20b52';
Query OK, 1 row affected (13.29 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update tb_withidx set col1 = 'col1aac4c0f07449c688af42886465b76b' where col1 = '95aac4c0f07449c688af42886465b76b';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update tb_noidx set col1 = 'col1aac4c0f07449c688af42886465b76b' where col1 = '95aac4c0f07449c688af42886465b76b';
Query OK, 1 row affected, 1 warning (13.15 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 以 del 欄位為篩選條件 來更新 col2 欄位
# del為0的大概203W條 del為1的大概155W條
mysql> select del,count(*) from tb_withidx GROUP BY del;
+-----+----------+
| del | count(*) |
+-----+----------+
| 0   |  2033080 |
| 1   |  1557025 |
+-----+----------+

mysql> update tb_withidx set col2 = 'col24c0f07449c68af42886465b76' where del = 0;
Query OK, 2033080 rows affected (47.15 sec)
Rows matched: 2033080  Changed: 2033080  Warnings: 0

mysql> update tb_noidx set col2 = 'col24c0f07449c68af42886465b76' where del = 0;
Query OK, 2033080 rows affected (49.79 sec)
Rows matched: 2033080  Changed: 2033080  Warnings: 0

mysql> update tb_withidx set del = 2 where del = 0;
Query OK, 2033080 rows affected (2 min 34.96 sec)
Rows matched: 2033080  Changed: 2033080  Warnings: 0

mysql>  update tb_noidx set del = 2 where del = 0; 
Query OK, 2033080 rows affected (50.57 sec)
Rows matched: 2033080  Changed: 2033080  Warnings: 0
```

從以上實驗大致可以看出，是否用到索引，對於 update 語句執行速度影響還是很大的，具體表現如下：

- 若在區分度較高的欄位上新增索引，並以該欄位為篩選條件進行更新，則無論是更新該欄位還是其他欄位，用到索引的更新都要快好多。
- 若在區分度很低的欄位上新增索引，並以該欄位為篩選條件進行更新，當更新其他欄位時，有無索引區別不大，當更新這個區分度很低的欄位時，用到索引的更新反而更慢。

#### 2.一些經驗總結

我們試著來解釋下以上實驗結果，首先來看下 update SQL 執行流程，大致如下：

1. 首先客戶端傳送請求到服務端，建立連線。
1. 服務端先看下查詢快取，對於更新某張表的 SQL ，該表的所有查詢快取都失效。
1. 接著來到解析器，進行語法分析，一些系統關鍵字校驗，校驗語法是否合規。
1. 然後最佳化器進行 SQL 最佳化，比如怎麼選擇索引之類，然後生成執行計劃。
1. 執行器去儲存引擎查詢需要更新的資料。
1. 儲存引擎判斷當前緩衝池中是否存在需要更新的資料，存在就直接返回，否則去從磁碟載入資料。
1. 執行器呼叫儲存引擎 API 去更新資料。
1. 儲存器更新資料，同時寫入 undo log 、redo log 資訊。
1. 執行器寫 binlog ，提交事務，流程結束。

也就是說，執行更新語句首先需要將被更新的記錄查詢出來，這也就不難理解為啥以區分度較高的欄位為篩選條件進行更新，有索引的情況下執行更快。

對於區分度很低的欄位，用沒用到索引則區別不大，原因是查詢出將被更新的記錄所需時間差別不大，需要掃描的行數差別不大。當更新區分度很低的欄位的欄位時，因為要維護索引 b+ 樹，所以會拖慢更新速度。

之前也有講過，雖然索引能加速查詢，但索引也是有缺點的，那就是索引需要動態的維護，當對錶中的資料進行增加、刪除、修改時，會降低資料的維護速度。本次實驗結果也能論證這個結論。

透過本次實驗，我們也能得到一些索引相關經驗：

- 只為用於搜尋、排序、分組、連線的列建立索引。
- 索引儘量建在區分度高的欄位上，避免在區分度低的欄位上建索引。
- 對經常更新的表避免建立過多的索引。
- 不要有冗餘索引，會增加維護成本。

執行update語句，用沒用到索引，區別大嗎？

相關文章