执行update语句,用没用到索引,区别大吗?
前言:
我们都知道,当执行 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 |
+----------------+-----------------+
相关文章