執(zhí)行update語(yǔ)句，用沒(méi)用到索引，區(qū)別大嗎？
前言：
我們都知道，當(dāng)執(zhí)行 select 查詢語(yǔ)句時(shí)，用沒(méi)用到索引區(qū)別是很大的，若沒(méi)用到索引，一條 select 語(yǔ)句可能執(zhí)行好幾秒或更久，若使用到索引則可能瞬間完成。那么當(dāng)執(zhí)行 update 語(yǔ)句時(shí)，用沒(méi)用到索引有什么區(qū)別呢，執(zhí)行時(shí)間相差大嗎？本篇文章我們一起來(lái)探究下。
1. update SQL 測(cè)試
為了對(duì)比出差距，這里筆者創(chuàng)建兩張一樣數(shù)據(jù)的大表，一張有普通索引，一張無(wú)普通索引，我們來(lái)對(duì)比下二者的差別。
# tb_noidx 表無(wú)普通索引
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='無(wú)索引表'
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 |
+----------------+-----------------+這里說(shuō)明下，tb_noidx 和 tb_withidx 兩張表數(shù)據(jù)完全相同，表大概有 360W 條數(shù)據(jù)，約占用 840M 空間。其中 col1 字段區(qū)分度較高，del 字段區(qū)分度很低，下面我們分別以這兩個(gè)字段為篩選條件來(lái)執(zhí)行 update 語(yǔ)句：
# 以 col1 字段為篩選條件 來(lái)更新 col2 字段
mysql> explain update tb_withidx set col2 = '48348a10d7794d269ecf10f9e3f20b52' where col1 = '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 | Using where |
+----+-------------+------------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)
mysql> update tb_withidx set col2 = '48348a10d7794d269ecf10f9e3f20b52' where col1 = '48348a10d7794d269ecf10f9e3f20b52';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> explain update tb_noidx set col2 = '48348a10d7794d269ecf10f9e3f20b52' where col1 = '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 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.00 sec)
mysql> update tb_noidx set col2 = '48348a10d7794d269ecf10f9e3f20b52' where col1 = '48348a10d7794d269ecf10f9e3f20b52';
Query OK, 1 row affected (13.29 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 以 col1 字段為篩選條件 來(lái)更新 col1 字段
mysql> explain update tb_withidx set col1 = 'col1aac4c0f07449c688af42886465b76b' where col1 = '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 | Using where; Using temporary |
+----+-------------+------------+------------+-------+---------------+----------+---------+-------+------+----------+------------------------------+
1 row in set (0.01 sec)
mysql> update tb_withidx set col1 = 'col1aac4c0f07449c688af42886465b76b' where col1 = '95aac4c0f07449c688af42886465b76b';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> explain update tb_noidx set col1 = 'col1aac4c0f07449c688af42886465b76b' where col1 = '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 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.01 sec)
mysql> update tb_noidx set col1 = 'col1aac4c0f07449c688af42886465b76b' where col1 = '95aac4c0f07449c688af42886465b76b';
Query OK, 1 row affected, 1 warning (13.15 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 以 del 字段為篩選條件 來(lái)更新 col2 字段
# del為0的大概203W條 del為1的大概155W條
mysql> select del,count(*) from tb_withidx GROUP BY del;
+-----+----------+
| del | count(*) |
+-----+----------+
| 0 | 2033080 |
| 1 | 1557025 |
+-----+----------+
mysql> explain update tb_withidx set col2 = 'col24c0f07449c68af42886465b76' where del = 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 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.00 sec)
mysql> update tb_withidx set col2 = 'col24c0f07449c68af42886465b76' where del = 0;
Query OK, 2033080 rows affected (47.15 sec)
Rows matched: 2033080 Changed: 2033080 Warnings: 0
mysql> explain update tb_noidx set col2 = 'col24c0f07449c68af42886465b76' where del = 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 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.00 sec)
mysql> update tb_noidx set col2 = 'col24c0f07449c68af42886465b76' where del = 0;
Query OK, 2033080 rows affected (49.79 sec)
Rows matched: 2033080 Changed: 2033080 Warnings: 0
# 以 del 字段為篩選條件 來(lái)更新 del 字段
mysql> explain update tb_withidx set del = 2 where del = 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 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.03 sec)
mysql> update tb_withidx set del = 2 where del = 0;
Query OK, 2033080 rows affected (2 min 34.96 sec)
Rows matched: 2033080 Changed: 2033080 Warnings: 0
mysql> explain update tb_noidx set del = 2 where del = 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 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.00 sec)
mysql> update tb_noidx set del = 2 where del = 0;
Query OK, 2033080 rows affected (50.57 sec)
Rows matched: 2033080 Changed: 2033080 Warnings: 0從以上實(shí)驗(yàn)大致可以看出，是否用到索引，對(duì)于 update 語(yǔ)句執(zhí)行速度影響還是很大的，具體表現(xiàn)如下：
若在區(qū)分度較高的字段上添加索引，并以該字段為篩選條件進(jìn)行更新，則無(wú)論是更新該字段還是其他字段，用到索引的更新都要快好多。
若在區(qū)分度很低的字段上添加索引，并以該字段為篩選條件進(jìn)行更新，當(dāng)更新其他字段時(shí)，有無(wú)索引區(qū)別不大，當(dāng)更新這個(gè)區(qū)分度很低的字段時(shí)，用到索引的更新反而更慢。
2.一些經(jīng)驗(yàn)總結(jié)
我們?cè)囍鴣?lái)解釋下以上實(shí)驗(yàn)結(jié)果，首先來(lái)看下 update SQL 執(zhí)行流程，大致如下：
首先客戶端發(fā)送請(qǐng)求到服務(wù)端，建立連接。
服務(wù)端先看下查詢緩存，對(duì)于更新某張表的 SQL ，該表的所有查詢緩存都失效。
接著來(lái)到解析器，進(jìn)行語(yǔ)法分析，一些系統(tǒng)關(guān)鍵字校驗(yàn)，校驗(yàn)語(yǔ)法是否合規(guī)。
然后優(yōu)化器進(jìn)行 SQL 優(yōu)化，比如怎么選擇索引之類(lèi)，然后生成執(zhí)行計(jì)劃。
執(zhí)行器去存儲(chǔ)引擎查詢需要更新的數(shù)據(jù)。
存儲(chǔ)引擎判斷當(dāng)前緩沖池中是否存在需要更新的數(shù)據(jù)，存在就直接返回，否則去從磁盤(pán)加載數(shù)據(jù)。
執(zhí)行器調(diào)用存儲(chǔ)引擎 API 去更新數(shù)據(jù)。
存儲(chǔ)器更新數(shù)據(jù)，同時(shí)寫(xiě)入 undo log 、redo log 信息。
執(zhí)行器寫(xiě) binlog ，提交事務(wù)，流程結(jié)束。
也就是說(shuō)，執(zhí)行更新語(yǔ)句首先需要將被更新的記錄查詢出來(lái)，這也就不難理解為啥以區(qū)分度較高的字段為篩選條件進(jìn)行更新，有索引的情況下執(zhí)行更快。
對(duì)于區(qū)分度很低的字段，用沒(méi)用到索引則區(qū)別不大，原因是查詢出將被更新的記錄所需時(shí)間差別不大，需要掃描的行數(shù)差別不大。當(dāng)更新區(qū)分度很低的字段的字段時(shí)，因?yàn)橐S護(hù)索引 b+ 樹(shù)，所以會(huì)拖慢更新速度。
之前也有講過(guò)，雖然索引能加速查詢，但索引也是有缺點(diǎn)的，那就是索引需要?jiǎng)討B(tài)的維護(hù)，當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除、修改時(shí)，會(huì)降低數(shù)據(jù)的維護(hù)速度。本次實(shí)驗(yàn)結(jié)果也能論證這個(gè)結(jié)論。
通過(guò)本次實(shí)驗(yàn)，我們也能得到一些索引相關(guān)經(jīng)驗(yàn)：
只為用于搜索、排序、分組、連接的列創(chuàng)建索引。
索引盡量建在區(qū)分度高的字段上，避免在區(qū)分度低的字段上建索引。
對(duì)經(jīng)常更新的表避免創(chuàng)建過(guò)多的索引。
不要有冗余索引，會(huì)增加維護(hù)成本。
推薦閱讀
（點(diǎn)擊標(biāo)題可跳轉(zhuǎn)閱讀）
評(píng)論
圖片
表情