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