关于mysql:执行update语句用没用到索引区别大吗

9次阅读

共计 9860 个字符，预计需要花费 25 分钟才能阅读完成。

前言：

咱们都晓得，当执行 select 查问语句时，用没用到索引区别是很大的，若没用到索引，一条 select 语句可能执行好几秒或更久，若应用到索引则可能霎时实现。那么当执行 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: 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 语句执行速度影响还是很大的，具体表现如下：

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

咱们试着来解释下以上试验后果，首先来看下 update SQL 执行流程，大抵如下：

首先客户端发送申请到服务端，建设连贯。
服务端先看下查问缓存，对于更新某张表的 SQL，该表的所有查问缓存都生效。
接着来到解析器，进行语法分析，一些零碎关键字校验，校验语法是否合规。
而后优化器进行 SQL 优化，比方怎么抉择索引之类，而后生成执行打算。
执行器去存储引擎查问须要更新的数据。
存储引擎判断以后缓冲池中是否存在须要更新的数据，存在就间接返回，否则去从磁盘加载数据。
执行器调用存储引擎 API 去更新数据。
存储器更新数据，同时写入 undo log、redo log 信息。
执行器写 binlog，提交事务，流程完结。

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

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

之前也有讲过，尽管索引能减速查问，但索引也是有毛病的，那就是索引须要动静的保护，当对表中的数据进行减少、删除、批改时，会升高数据的保护速度。本次试验后果也能论证这个论断。

通过本次试验，咱们也能失去一些索引相干教训：

只为用于搜寻、排序、分组、连贯的列创立索引。
索引尽量建在区分度高的字段上，防止在区分度低的字段上建索引。
对常常更新的表防止创立过多的索引。
不要有冗余索引，会减少保护老本。

正文完

mysql

发表至： mysql

2021-09-06

0

关于mysql:mysql表的设计规范

关于mysql:Linux-Mysql集群搭建

关于mysql:分布式-DBLE-关联查询下压优化

关于mysql:MyCATMySQL搭建高可用企业级数据库集群内附资料文档

关于redis:redis-四redis之String

关于mysql:执行update语句用没用到索引区别大吗

1. update SQL 测试

2. 一些经验总结

站内搜索