执行update语句，用没用到索引，区别大吗（） mysql

前言：
我们都知道，当执行 select 查询语句时，用没用到索引区别是很大的，若没用到索引，一条 select 语句可能执行好几秒或更久，若使用到索引则可能瞬间完成。那么当执行 update 语句时，用没用到索引有什么区别呢，执行时间相差大吗？本篇文章我们一起来探究下。
1. update SQL 测试 【执行update语句，用没用到索引，区别大吗（）】为了对比出差距，这里笔者创建两张一样数据的大表，一张有普通索引，一张无普通索引，我们来对比下二者的差别。

# 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: 1Changed: 1Warnings: 0mysql> 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: 1Changed: 1Warnings: 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: 1Changed: 1Warnings: 0mysql> 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: 1Changed: 1Warnings: 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: 2033080Changed: 2033080Warnings: 0mysql> 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: 2033080Changed: 2033080Warnings: 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: 2033080Changed: 2033080Warnings: 0mysql> 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: 2033080Changed: 2033080Warnings: 0

从以上实验大致可以看出，是否用到索引，对于 update 语句执行速度影响还是很大的，具体表现如下：

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

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

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

也就是说，执行更新语句首先需要将被更新的记录查询出来，这也就不难理解为啥以区分度较高的字段为筛选条件进行更新，有索引的情况下执行更快。
对于区分度很低的字段，用没用到索引则区别不大，原因是查询出将被更新的记录所需时间差别不大，需要扫描的行数差别不大。当更新区分度很低的字段的字段时，因为要维护索引 b+ 树，所以会拖慢更新速度。
之前也有讲过，虽然索引能加速查询，但索引也是有缺点的，那就是索引需要动态的维护，当对表中的数据进行增加、删除、修改时，会降低数据的维护速度。本次实验结果也能论证这个结论。
通过本次实验，我们也能得到一些索引相关经验：