前言：
我們都知道，當(dāng)執(zhí)行 select 查詢語句時，用沒用到索引區(qū)別是很大的，若沒用到索引，一條 select 語句可能執(zhí)行好幾秒或更久，若使用到索引則可能瞬間完成。那么當(dāng)執(zhí)行 update 語句時，用沒用到索引有什么區(qū)別呢，執(zhí)行時間相差大嗎？本篇文章我們一起來探究下。
1. update SQL 測試
為了對比出差距，這里筆者創(chuàng)建兩張一樣數(shù)據(jù)的大表，一張有普通索引，一張無普通索引，我們來對比下二者的差別。
# tb_noidx 表無普通索引
MySQL> show create table tb_noidxG
*************************** 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_withidxG
*************************** 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 兩張表數(shù)據(jù)完全相同，表大概有 360W 條數(shù)據(jù)，約占用 840M 空間。其中 col1 字段區(qū)分度較高，del 字段區(qū)分度很低，下面我們分別以這兩個字段為篩選條件來執(zhí)行 update 語句：
# 以 col1 字段為篩選條件 來更新 col2 字段
mysql> explain update tb_withidx set col2 = '48348a10d7794d269ecf10f9e3f20b52' where col1 = '48348a10d7794d269ecf10f9e3f20b52';
+----+-------------+------------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | UPDATE | tb_withidx | NULL | range | idx_col1 | idx_col1 | 96 | const | 1 | 100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)
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> explain update tb_noidx set col2 = '48348a10d7794d269ecf10f9e3f20b52' where col1 = '48348a10d7794d269ecf10f9e3f20b52';
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | UPDATE | tb_noidx | NULL | index | NULL | PRIMARY | 4 | NULL | 3557131 | 100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.00 sec)
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
# 以 col1 字段為篩選條件 來更新 col1 字段
mysql> explain update tb_withidx set col1 = 'col1aac4c0f07449c688af42886465b76b' where col1 = '95aac4c0f07449c688af42886465b76b';
+----+-------------+------------+------------+-------+---------------+----------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+----------+---------+-------+------+----------+------------------------------+
| 1 | UPDATE | tb_withidx | NULL | range | idx_col1 | idx_col1 | 96 | const | 1 | 100.00 | Using where; Using temporary |
+----+-------------+------------+------------+-------+---------------+----------+---------+-------+------+----------+------------------------------+
1 row in set (0.01 sec)
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> explain update tb_noidx set col1 = 'col1aac4c0f07449c688af42886465b76b' where col1 = '95aac4c0f07449c688af42886465b76b';
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | UPDATE | tb_noidx | NULL | index | NULL | PRIMARY | 4 | NULL | 3557131 | 100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.01 sec)
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> explain update tb_withidx set col2 = 'col24c0f07449c68af42886465b76' where del = 0;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | UPDATE | tb_withidx | NULL | index | idx_del | PRIMARY | 4 | NULL | 3436842 | 100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.00 sec)
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> explain update tb_noidx set col2 = 'col24c0f07449c68af42886465b76' where del = 0;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | UPDATE | tb_noidx | NULL | index | NULL | PRIMARY | 4 | NULL | 3296548 | 100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.00 sec)
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
# 以 del 字段為篩選條件 來更新 del 字段
mysql> explain update tb_withidx set del = 2 where del = 0;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | UPDATE | tb_withidx | NULL | index | idx_del | PRIMARY | 4 | NULL | 3436842 | 100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.03 sec)
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> explain update tb_noidx set del = 2 where del = 0;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | UPDATE | tb_noidx | NULL | index | NULL | PRIMARY | 4 | NULL | 3296548 | 100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.00 sec)
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 語句執(zhí)行速度影響還是很大的，具體表現(xiàn)如下：
- 若在區(qū)分度較高的字段上添加索引，并以該字段為篩選條件進(jìn)行更新，則無論是更新該字段還是其他字段，用到索引的更新都要快好多。
- 若在區(qū)分度很低的字段上添加索引，并以該字段為篩選條件進(jìn)行更新，當(dāng)更新其他字段時，有無索引區(qū)別不大，當(dāng)更新這個區(qū)分度很低的字段時，用到索引的更新反而更慢。
2.一些經(jīng)驗總結(jié)
我們試著來解釋下以上實驗結(jié)果，首先來看下 update SQL 執(zhí)行流程，大致如下：
- 首先客戶端發(fā)送請求到服務(wù)端，建立連接。
- 服務(wù)端先看下查詢緩存，對于更新某張表的 SQL ，該表的所有查詢緩存都失效。
- 接著來到解析器，進(jìn)行語法分析，一些系統(tǒng)關(guān)鍵字校驗，校驗語法是否合規(guī)。
- 然后優(yōu)化器進(jìn)行 SQL 優(yōu)化，比如怎么選擇索引之類，然后生成執(zhí)行計劃。
- 執(zhí)行器去存儲引擎查詢需要更新的數(shù)據(jù)。
- 存儲引擎判斷當(dāng)前緩沖池中是否存在需要更新的數(shù)據(jù)，存在就直接返回，否則去從磁盤加載數(shù)據(jù)。
- 執(zhí)行器調(diào)用存儲引擎 API 去更新數(shù)據(jù)。
- 存儲器更新數(shù)據(jù)，同時寫入 undo log 、redo log 信息。
- 執(zhí)行器寫 binlog ，提交事務(wù)，流程結(jié)束。
也就是說，執(zhí)行更新語句首先需要將被更新的記錄查詢出來，這也就不難理解為啥以區(qū)分度較高的字段為篩選條件進(jìn)行更新，有索引的情況下執(zhí)行更快。
對于區(qū)分度很低的字段，用沒用到索引則區(qū)別不大，原因是查詢出將被更新的記錄所需時間差別不大，需要掃描的行數(shù)差別不大。當(dāng)更新區(qū)分度很低的字段的字段時，因為要維護索引 b+ 樹，所以會拖慢更新速度。
之前也有講過，雖然索引能加速查詢，但索引也是有缺點的，那就是索引需要動態(tài)的維護，當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除、修改時，會降低數(shù)據(jù)的維護速度。本次實驗結(jié)果也能論證這個結(jié)論。
通過本次實驗，我們也能得到一些索引相關(guān)經(jīng)驗：
- 只為用于搜索、排序、分組、連接的列創(chuàng)建索引。
- 索引盡量建在區(qū)分度高的字段上，避免在區(qū)分度低的字段上建索引。
- 對經(jīng)常更新的表避免創(chuàng)建過多的索引。
- 不要有冗余索引，會增加維護成本。
