MYSQL执行Update语句用没用到索引区别大吗?

我们都知道,当执行 select 查询语句时,用没用到索引区别是很大的,若没用到索引,一条 select 语句可能执行好几秒或更久,若使用到索引则可能瞬间完成。

MYSQL执行Update语句用没用到索引区别大吗?

前言

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

1. update SQL 测试

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

  • #tb_noidx表无普通索引
  • mysql>showcreatetabletb_noidx\\G
  • ***************************1.row***************************
  • Table:tb_noidx
  • CreateTable:CREATETABLE`tb_noidx`(
  • `increment_id`int(11)unsignedNOTNULLAUTO_INCREMENTCOMMENT'自增主键',
  • `col1`char(32)NOTNULLCOMMENT'字段1',
  • `col2`char(32)NOTNULLCOMMENT'字段2',
  • `del`tinyint(4)NOTNULLDEFAULT'0'COMMENT'是否删除',
  • )ENGINE=InnoDBAUTO_INCREMENT=3696887DEFAULTCHARSET=utf8COMMENT='无索引表'
  • mysql>selectcount(*)fromtb_noidx;
  • +———-+
  • |count(*)|
  • +———-+
  • |3590105|
  • +———-+
  • mysql>selectconcat(round(sum(data_length/1024/1024),2),'MB')asdata_length_MB,concat(round(sum(index_length/1024/1024),2),'MB')asindex_length_MB
  • ->frominformation_schema.tableswheretable_schema='testdb'andtable_name='tb_noidx';
  • +—————-+—————–+
  • |data_length_MB|index_length_MB|
  • +—————-+—————–+
  • |841.98MB|0.00MB|
  • +—————-+—————–+
  • #tb_withidx表有普通索引
  • mysql>showcreatetabletb_withidx\\G
  • ***************************1.row***************************
  • Table:tb_withidx
  • CreateTable:CREATETABLE`tb_withidx`(
  • `increment_id`int(11)unsignedNOTNULLAUTO_INCREMENTCOMMENT'自增主键',
  • `col1`char(32)NOTNULLCOMMENT'字段1',
  • `col2`char(32)NOTNULLCOMMENT'字段2',
  • `del`tinyint(4)NOTNULLDEFAULT'0'COMMENT'是否删除',
  • PRIMARYKEY(`increment_id`),
  • KEY`idx_col1`(`col1`),
  • KEY`idx_del`(`del`)
  • )ENGINE=InnoDBAUTO_INCREMENT=3696887DEFAULTCHARSET=utf8COMMENT='有索引表'
  • mysql>selectcount(*)fromtb_withidx;
  • +———-+
  • |count(*)|
  • +———-+
  • |3590105|
  • +———-+
  • mysql>selectconcat(round(sum(data_length/1024/1024),2),'MB')asdata_length_MB,concat(round(sum(index_length/1024/1024),2),'MB')asindex_length_MB
  • ->frominformation_schema.tableswheretable_schema='testdb'andtable_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>explainupdatetb_withidxsetcol2='48348a10d7794d269ecf10f9e3f20b52'wherecol1='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|Usingwhere|
  • +—-+————-+————+————+——-+—————+———-+———+——-+——+———-+————-+
  • 1rowinset(0.00sec)
  • mysql>updatetb_withidxsetcol2='48348a10d7794d269ecf10f9e3f20b52'wherecol1='48348a10d7794d269ecf10f9e3f20b52';
  • QueryOK,1rowaffected(0.01sec)
  • Rowsmatched:1Changed:1Warnings:0
  • mysql>explainupdatetb_noidxsetcol2='48348a10d7794d269ecf10f9e3f20b52'wherecol1='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|Usingwhere|
  • +—-+————-+———-+————+——-+—————+———+———+——+———+———-+————-+
  • 1rowinset(0.00sec)
  • mysql>updatetb_noidxsetcol2='48348a10d7794d269ecf10f9e3f20b52'wherecol1='48348a10d7794d269ecf10f9e3f20b52';
  • QueryOK,1rowaffected(13.29sec)
  • Rowsmatched:1Changed:1Warnings:0
  • #以col1字段为筛选条件来更新col1字段
  • mysql>explainupdatetb_withidxsetcol1='col1aac4c0f07449c688af42886465b76b'wherecol1='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|Usingwhere;Usingtemporary|
  • +—-+————-+————+————+——-+—————+———-+———+——-+——+———-+——————————+
  • 1rowinset(0.01sec)
  • mysql>updatetb_withidxsetcol1='col1aac4c0f07449c688af42886465b76b'wherecol1='95aac4c0f07449c688af42886465b76b';
  • QueryOK,1rowaffected,1warning(0.01sec)
  • Rowsmatched:1Changed:1Warnings:0
  • mysql>explainupdatetb_noidxsetcol1='col1aac4c0f07449c688af42886465b76b'wherecol1='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|Usingwhere|
  • +—-+————-+———-+————+——-+—————+———+———+——+———+———-+————-+
  • 1rowinset(0.01sec)
  • mysql>updatetb_noidxsetcol1='col1aac4c0f07449c688af42886465b76b'wherecol1='95aac4c0f07449c688af42886465b76b';
  • QueryOK,1rowaffected,1warning(13.15sec)
  • Rowsmatched:1Changed:1Warnings:0
  • #以del字段为筛选条件来更新col2字段
  • #del为0的大概203W条del为1的大概155W条
  • mysql>selectdel,count(*)fromtb_withidxGROUPBYdel;
  • +—–+———-+
  • |del|count(*)|
  • +—–+———-+
  • |0|2033080|
  • |1|1557025|
  • +—–+———-+
  • mysql>explainupdatetb_withidxsetcol2='col24c0f07449c68af42886465b76'wheredel=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|Usingwhere|
  • +—-+————-+————+————+——-+—————+———+———+——+———+———-+————-+
  • 1rowinset(0.00sec)
  • mysql>updatetb_withidxsetcol2='col24c0f07449c68af42886465b76'wheredel=0;
  • QueryOK,2033080rowsaffected(47.15sec)
  • Rowsmatched:2033080Changed:2033080Warnings:0
  • mysql>explainupdatetb_noidxsetcol2='col24c0f07449c68af42886465b76'wheredel=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|Usingwhere|
  • +—-+————-+———-+————+——-+—————+———+———+——+———+———-+————-+
  • 1rowinset(0.00sec)
  • mysql>updatetb_noidxsetcol2='col24c0f07449c68af42886465b76'wheredel=0;
  • QueryOK,2033080rowsaffected(49.79sec)
  • Rowsmatched:2033080Changed:2033080Warnings:0
  • #以del字段为筛选条件来更新del字段
  • mysql>explainupdatetb_withidxsetdel=2wheredel=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|Usingwhere|
  • +—-+————-+————+————+——-+—————+———+———+——+———+———-+————-+
  • 1rowinset(0.03sec)
  • mysql>updatetb_withidxsetdel=2wheredel=0;
  • QueryOK,2033080rowsaffected(2min34.96sec)
  • Rowsmatched:2033080Changed:2033080Warnings:0
  • mysql>explainupdatetb_noidxsetdel=2wheredel=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|Usingwhere|
  • +—-+————-+———-+————+——-+—————+———+———+——+———+———-+————-+
  • 1rowinset(0.00sec)
  • mysql>updatetb_noidxsetdel=2wheredel=0;
  • QueryOK,2033080rowsaffected(50.57sec)
  • Rowsmatched:2033080Changed:2033080Warnings:0
  • 从以上实验大致可以看出,是否用到索引,对于 update 语句执行速度影响还是很大的,具体表现如下:

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

    2.一些经验总结

    我们试着来解释下以上实验结果,首先来看下 update SQL 执行流程,大致如下:

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

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

    对于区分度很低的字段,用没用到索引则区别不大,原因是查询出将被更新的记录所需时间差别不大,需要扫描的行数差别不大。当更新区分度很低的字段的字段时,因为要维护索引 b+ 树,所以会拖慢更新速度。

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

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

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

    原文链接:https://mp.weixin.qq.com/s/97u-BmjT8zS6gG9evsEqYg

    版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/6261/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

    (0)
    上一篇 2022-07-28 12:09:04
    下一篇 2022-07-28 12:09:16

    软件定制开发公司

    相关阅读

    发表回复

    登录后才能评论
    通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!