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

46次阅读

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

前言：

咱们都晓得，当执行 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+ 树，所以会拖慢更新速度。

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

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

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

正文完
mysql
发表至： mysql
2021-09-06
 0
关于mysql:MySQL-Server可执行注释
关于mysql:新特性解读-MySQL-80-新密码策略下
关于mysql:SQL是如何在数据库中执行的
关于mysql:CentOS7中Mysql的systemctl启动方式启动停止重启开机启动