执行Update语句，用没用到索引，区别大吗？
复制# tb_noidx 表无普通索引
mysql> show createtable tb_noidx\G
*************************** 1. row ***************************
Table: tb_noidx
CreateTable: CREATETABLE `tb_noidx` (
`increment_id` int(11) unsigned NOTNULL AUTO_INCREMENT COMMENT 自增主键,
`col1` char(32) NOTNULL COMMENT 字段1,
`col2` char(32) NOTNULL COMMENT 字段2,
...
`del` tinyint(4) NOTNULLDEFAULT0 COMMENT 是否删除,
) ENGINE=InnoDB AUTO_INCREMENT=3696887 DEFAULT CHARSET=utf8 COMMENT=无索引表
mysql> selectcount(*) 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=testdband table_name = tb_noidx;
+----------------+-----------------+
| data_length_MB | index_length_MB |
+----------------+-----------------+
| 841.98MB | 0.00MB |
+----------------+-----------------+
# tb_withidx 表有普通索引
mysql> show createtable tb_withidx\G
*************************** 1. row ***************************
Table: tb_withidx
CreateTable: CREATETABLE `tb_withidx` (
`increment_id` int(11) unsigned NOTNULL AUTO_INCREMENT COMMENT 自增主键,
`col1` char(32) NOTNULL COMMENT 字段1,
`col2` char(32) NOTNULL COMMENT 字段2,
...
`del` tinyint(4) NOTNULLDEFAULT0 COMMENT 是否删除,
PRIMARYKEY (`increment_id`),
KEY `idx_col1` (`col1`),
KEY `idx_del` (`del`)
) ENGINE=InnoDB AUTO_INCREMENT=3696887 DEFAULT CHARSET=utf8 COMMENT=有索引表
mysql> selectcount(*) 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=testdband table_name = tb_withidx;
+----------------+-----------------+
| data_length_MB | index_length_MB |
+----------------+-----------------+
| 841.98MB | 210.50MB |
+----------------+-----------------+
1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.
THE END
