执行update语句，用没用到索引，区别大吗？#yyds干货盘点#

前言：

我们都知道，当执行 select 查询语句时，用没用到索引区别是很大的，若没用到索引，一条 select 语句可能执行好几秒或更久，若使用到索引则可能瞬间完成。那么当执行 update 语句时，用没用到索引有什么区别呢，执行时间相差大吗？本篇文章我们一起来探究下。

  1. update 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 语句：

# 以 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 语句执行速度影响还是很大的，具体表现如下：

• 若在区分度较高的字段上添加索引，并以该字段为筛选条件进行更新，则无论是更新该字段还是其他字段，用到索引的更新都要快好多。
• 若在区分度很低的字段上添加索引，并以该字段为筛选条件进行更新，当更新其他字段时，有无索引区别不大，当更新这个区分度很低的字段时，用到索引的更新反而更慢。

  2.一些经验总结

我们试着来解释下以上实验结果，首先来看下 update SQL 执行流程，大致如下：

1. 首先客户端发送请求到服务端，建立连接。
2. 服务端先看下查询缓存，对于更新某张表的 SQL ，该表的所有查询缓存都失效。
3. 接着来到解析器，进行语法分析，一些系统关键字校验，校验语法是否合规。
4. 然后优化器进行 SQL 优化，比如怎么选择索引之类，然后生成执行计划。
5. 执行器去存储引擎查询需要更新的数据。
6. 存储引擎判断当前缓冲池中是否存在需要更新的数据，存在就直接返回，否则去从磁盘加载数据。
7. 执行器调用存储引擎 API 去更新数据。
8. 存储器更新数据，同时写入 undo log 、redo log 信息。
9. 执行器写 binlog ，提交事务，流程结束。

也就是说，执行更新语句首先需要将被更新的记录查询出来，这也就不难理解为啥以区分度较高的字段为筛选条件进行更新，有索引的情况下执行更快。

对于区分度很低的字段，用没用到索引则区别不大，原因是查询出将被更新的记录所需时间差别不大，需要扫描的行数差别不大。当更新区分度很低的字段的字段时，因为要维护索引 b+ 树，所以会拖慢更新速度。

之前也有讲过，虽然索引能加速查询，但索引也是有缺点的，那就是索引需要动态的维护，当对表中的数据进行增加、删除、修改时，会降低数据的维护速度。本次实验结果也能论证这个结论。

通过本次实验，我们也能得到一些索引相关经验：

• 只为用于搜索、排序、分组、连接的列创建索引。
• 索引尽量建在区分度高的字段上，避免在区分度低的字段上建索引。
• 对经常更新的表避免创建过多的索引。
• 不要有冗余索引，会增加维护成本。