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

时间:2022-09-12 15:51:26

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

前言

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

1. update SQL 测试

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

  1. # tb_noidx 表无普通索引 
  2. mysql> show create table tb_noidx\G 
  3. *************************** 1. row *************************** 
  4.        Table: tb_noidx 
  5. Create TableCREATE TABLE `tb_noidx` ( 
  6.   `increment_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键'
  7.   `col1` char(32) NOT NULL COMMENT '字段1'
  8.   `col2` char(32) NOT NULL COMMENT '字段2'
  9.   ... 
  10.   `del` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除'
  11. ) ENGINE=InnoDB AUTO_INCREMENT=3696887 DEFAULT CHARSET=utf8 COMMENT='无索引表' 
  12.  
  13. mysql> select count(*) from tb_noidx; 
  14. +----------+ 
  15. count(*) | 
  16. +----------+ 
  17. |  3590105 | 
  18. +----------+ 
  19.  
  20. 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 
  21.     -> from information_schema.tables where table_schema='testdb' and table_name = 'tb_noidx';  
  22. +----------------+-----------------+ 
  23. | data_length_MB | index_length_MB | 
  24. +----------------+-----------------+ 
  25. | 841.98MB       | 0.00MB          | 
  26. +----------------+-----------------+ 
  27.  
  28. # tb_withidx 表有普通索引 
  29. mysql> show create table tb_withidx\G 
  30. *************************** 1. row *************************** 
  31.        Table: tb_withidx 
  32. Create TableCREATE TABLE `tb_withidx` ( 
  33.   `increment_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键'
  34.   `col1` char(32) NOT NULL COMMENT '字段1'
  35.   `col2` char(32) NOT NULL COMMENT '字段2'
  36.   ... 
  37.   `del` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除'
  38.   PRIMARY KEY (`increment_id`), 
  39.   KEY `idx_col1` (`col1`), 
  40.   KEY `idx_del` (`del`) 
  41. ) ENGINE=InnoDB AUTO_INCREMENT=3696887 DEFAULT CHARSET=utf8 COMMENT='有索引表' 
  42.  
  43. mysql> select count(*) from tb_withidx; 
  44. +----------+ 
  45. count(*) | 
  46. +----------+ 
  47. |  3590105 | 
  48. +----------+ 
  49.  
  50. 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 
  51.     -> from information_schema.tables where table_schema='testdb' and table_name = 'tb_withidx';  
  52. +----------------+-----------------+ 
  53. | data_length_MB | index_length_MB | 
  54. +----------------+-----------------+ 
  55. | 841.98MB       | 210.50MB        | 
  56. +----------------+-----------------+ 

这里说明下,tb_noidx 和 tb_withidx 两张表数据完全相同,表大概有 360W 条数据,约占用 840M 空间。其中 col1 字段区分度较高,del 字段区分度很低,下面我们分别以这两个字段为筛选条件来执行 update 语句:

  1. # 以 col1 字段为筛选条件 来更新 col2 字段 
  2. mysql> explain update tb_withidx set col2 = '48348a10d7794d269ecf10f9e3f20b52' where col1 = '48348a10d7794d269ecf10f9e3f20b52'
  3. +----+-------------+------------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+ 
  4. | id | select_type | table      | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra       | 
  5. +----+-------------+------------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+ 
  6. |  1 | UPDATE      | tb_withidx | NULL       | range | idx_col1      | idx_col1 | 96      | const |    1 |   100.00 | Using where | 
  7. +----+-------------+------------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+ 
  8. 1 row in set (0.00 sec) 
  9.  
  10. mysql> update tb_withidx set col2 = '48348a10d7794d269ecf10f9e3f20b52' where col1 = '48348a10d7794d269ecf10f9e3f20b52'
  11. Query OK, 1 row affected (0.01 sec) 
  12. Rows matched: 1  Changed: 1  Warnings: 0 
  13.  
  14. mysql> explain update tb_noidx set col2 = '48348a10d7794d269ecf10f9e3f20b52' where col1 = '48348a10d7794d269ecf10f9e3f20b52'
  15. +----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  16. | id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       | 
  17. +----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  18. |  1 | UPDATE      | tb_noidx | NULL       | index | NULL          | PRIMARY | 4       | NULL | 3557131 |   100.00 | Using where | 
  19. +----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  20. 1 row in set (0.00 sec) 
  21.  
  22. mysql> update tb_noidx set col2 = '48348a10d7794d269ecf10f9e3f20b52' where col1 = '48348a10d7794d269ecf10f9e3f20b52'
  23. Query OK, 1 row affected (13.29 sec) 
  24. Rows matched: 1  Changed: 1  Warnings: 0 
  25.  
  26. # 以 col1 字段为筛选条件 来更新 col1 字段 
  27. mysql> explain update tb_withidx set col1 = 'col1aac4c0f07449c688af42886465b76b' where col1 = '95aac4c0f07449c688af42886465b76b'
  28. +----+-------------+------------+------------+-------+---------------+----------+---------+-------+------+----------+------------------------------+ 
  29. | id | select_type | table      | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra                        | 
  30. +----+-------------+------------+------------+-------+---------------+----------+---------+-------+------+----------+------------------------------+ 
  31. |  1 | UPDATE      | tb_withidx | NULL       | range | idx_col1      | idx_col1 | 96      | const |    1 |   100.00 | Using where; Using temporary | 
  32. +----+-------------+------------+------------+-------+---------------+----------+---------+-------+------+----------+------------------------------+ 
  33. 1 row in set (0.01 sec) 
  34.  
  35. mysql> update tb_withidx set col1 = 'col1aac4c0f07449c688af42886465b76b' where col1 = '95aac4c0f07449c688af42886465b76b'
  36. Query OK, 1 row affected, 1 warning (0.01 sec) 
  37. Rows matched: 1  Changed: 1  Warnings: 0 
  38.  
  39. mysql> explain update tb_noidx set col1 = 'col1aac4c0f07449c688af42886465b76b' where col1 = '95aac4c0f07449c688af42886465b76b'
  40. +----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  41. | id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       | 
  42. +----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  43. |  1 | UPDATE      | tb_noidx | NULL       | index | NULL          | PRIMARY | 4       | NULL | 3557131 |   100.00 | Using where | 
  44. +----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  45. 1 row in set (0.01 sec) 
  46.  
  47. mysql> update tb_noidx set col1 = 'col1aac4c0f07449c688af42886465b76b' where col1 = '95aac4c0f07449c688af42886465b76b'
  48. Query OK, 1 row affected, 1 warning (13.15 sec) 
  49. Rows matched: 1  Changed: 1  Warnings: 0 
  50.  
  51. # 以 del 字段为筛选条件 来更新 col2 字段 
  52. # del为0的大概203W条 del为1的大概155W条 
  53. mysql> select del,count(*) from tb_withidx GROUP BY del; 
  54. +-----+----------+ 
  55. | del | count(*) | 
  56. +-----+----------+ 
  57. | 0   |  2033080 | 
  58. | 1   |  1557025 | 
  59. +-----+----------+ 
  60.  
  61. mysql> explain update tb_withidx set col2 = 'col24c0f07449c68af42886465b76' where del = 0; 
  62. +----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  63. | id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       | 
  64. +----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  65. |  1 | UPDATE      | tb_withidx | NULL       | index | idx_del       | PRIMARY | 4       | NULL | 3436842 |   100.00 | Using where | 
  66. +----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  67. 1 row in set (0.00 sec) 
  68.  
  69. mysql> update tb_withidx set col2 = 'col24c0f07449c68af42886465b76' where del = 0; 
  70. Query OK, 2033080 rows affected (47.15 sec) 
  71. Rows matched: 2033080  Changed: 2033080  Warnings: 0 
  72.  
  73. mysql> explain update tb_noidx set col2 = 'col24c0f07449c68af42886465b76' where del = 0; 
  74. +----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  75. | id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       | 
  76. +----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  77. |  1 | UPDATE      | tb_noidx | NULL       | index | NULL          | PRIMARY | 4       | NULL | 3296548 |   100.00 | Using where | 
  78. +----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  79. 1 row in set (0.00 sec) 
  80.  
  81. mysql> update tb_noidx set col2 = 'col24c0f07449c68af42886465b76' where del = 0; 
  82. Query OK, 2033080 rows affected (49.79 sec) 
  83. Rows matched: 2033080  Changed: 2033080  Warnings: 0 
  84.  
  85. # 以 del 字段为筛选条件 来更新 del 字段 
  86. mysql> explain update tb_withidx set del = 2 where del = 0;                                       
  87. +----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  88. | id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       | 
  89. +----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  90. |  1 | UPDATE      | tb_withidx | NULL       | index | idx_del       | PRIMARY | 4       | NULL | 3436842 |   100.00 | Using where | 
  91. +----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  92. 1 row in set (0.03 sec) 
  93.  
  94. mysql> update tb_withidx set del = 2 where del = 0; 
  95. Query OK, 2033080 rows affected (2 min 34.96 sec) 
  96. Rows matched: 2033080  Changed: 2033080  Warnings: 0 
  97.  
  98. mysql> explain update tb_noidx set del = 2 where del = 0;   
  99. +----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  100. | id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       | 
  101. +----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  102. |  1 | UPDATE      | tb_noidx | NULL       | index | NULL          | PRIMARY | 4       | NULL | 3296548 |   100.00 | Using where | 
  103. +----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  104. 1 row in set (0.00 sec) 
  105.  
  106. mysql>  update tb_noidx set del = 2 where del = 0;  
  107. Query OK, 2033080 rows affected (50.57 sec) 
  108. Rows matched: 2033080  Changed: 2033080  Warnings: 0 

从以上实验大致可以看出,是否用到索引,对于 update 语句执行速度影响还是很大的,具体表现如下:

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

2.一些经验总结

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

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

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

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

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

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

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

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