您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

执行update语句,用没用到索引,区别大吗?

时间:2021-09-07 17:13:54  来源:  作者:MySQL技术

前言:

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

1. update SQL 测试

为了对比出差距,这里笔者创建两张一样数据的大表,一张有普通索引,一张无普通索引,我们来对比下二者的差别。

# tb_noidx 表无普通索引
MySQL> show create table tb_noidxG
*************************** 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_withidxG
*************************** 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+ 树,所以会拖慢更新速度。

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

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

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


Tags:update语句   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
前言:我们都知道,当执行 select 查询语句时,用没用到索引区别是很大的,若没用到索引,一条 select 语句可能执行好几秒或更久,若使用到索引则可能瞬间完成。那么当执行 update 语句...【详细内容】
2021-09-07  Tags: update语句  点击:(49)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(17)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(16)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(19)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(27)  评论:(0)  加入收藏
相关文章
    无相关信息
最新更新
栏目热门
栏目头条