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

2023-03-15 00:00:00 索引 字段 更新 筛选 区分

前言:

我们都知道,当执行 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        |
+----------------+-----------------+

相关文章