想问下UPDATE和索引的关系

[bashen1101]

不带where条件然后update一张表的某字段A，执行计划是Table Scan
如果在该表某字段B上有非聚集索引，那么执行计划是Index Scan
想问一下这个是否对性能有影响？为什么？可否指定update时索引？

另外，一张表只有1个字段和另一张表有10多个字段，在做同样update tb set a=0的情况下，是否性能有不同？

[LongRui888]

1、肯定对性能有影响，一种就是你说的表扫描，一种是索引，就像你要修改一本书的里一个错别字， 全表扫描是把整本书都看一遍，把错字修改，但索引就直接翻到第100页，修改了，就可以了，所以索引的方式效率更高。

[發糞塗牆]

index scan稍微比table scan低开销一点。你可能需要对A字段加非聚集索引，如果出现index scan，也有可能是你的where条件中，选择度太低，无法使用seek

[發糞塗牆]

下面那个，要看数据量

[LongRui888]

2.一般，如果是这样： update tb set a = 0 where xx = 11 那么如果在xx字段上有索引，那么会自动使用xx字段上的索引的。 3.字段越多，update的效率肯定更差。

[發糞塗牆]

update除了要更新某列，还可能会引起索引的修改，特别是对聚集索引键修改，会引起其他非聚集索引的改动，列越多，更新聚集索引的时间越长

[bashen1101]

引用 1 楼 yupeigu 的回复:

1、肯定对性能有影响，一种就是你说的表扫描，一种是索引，就像你要修改一本书的里一个错别字， 全表扫描是把整本书都看一遍，把错字修改，但索引就直接翻到第100页，修改了，就可以了，所以索引的方式效率更高。

可是我是不带where条件的全表更新，其实也就不存在翻不翻书的问题了，总是每一页都翻一遍，走索引会否反而更慢？

[發糞塗牆]

引用 6 楼 bashen1101 的回复:

[quote=引用 1 楼 yupeigu 的回复:] 1、肯定对性能有影响，一种就是你说的表扫描，一种是索引，就像你要修改一本书的里一个错别字， 全表扫描是把整本书都看一遍，把错字修改，但索引就直接翻到第100页，修改了，就可以了，所以索引的方式效率更高。

可是我是不带where条件的全表更新，其实也就不存在翻不翻书的问题了，总是每一页都翻一遍，走索引会否反而更慢？[/quote]当需要查询的部分占据一定百分比的时候，优化器会选择全表扫描而不会走索引

[LongRui888]

引用 6 楼 bashen1101 的回复:

[quote=引用 1 楼 yupeigu 的回复:] 1、肯定对性能有影响，一种就是你说的表扫描，一种是索引，就像你要修改一本书的里一个错别字， 全表扫描是把整本书都看一遍，把错字修改，但索引就直接翻到第100页，修改了，就可以了，所以索引的方式效率更高。

可是我是不带where条件的全表更新，其实也就不存在翻不翻书的问题了，总是每一页都翻一遍，走索引会否反而更慢？[/quote] 嗯，确实，那这个时候，就是全表扫描的效率更高了。 如果是随机查找，却似是索引效率更高。 如果不是随机查找，那么扫描效率更高。

[bashen1101]

引用 3 楼 DBA_Huangzj 的回复:

下面那个，要看数据量

其他字段的数据量是多的。 其实我的意思是，2张表的都有A字段INT，都对A字段SET 0（不带WHERE，全表），1张表只有个ID字段，1张表还有各种其他字段，但都和A字段无关，索引也都是非聚集索引，都和A字段无关。 以上这种情况，是否还有性能的影响？

[發糞塗牆]

非要说性能的话，当然列越少越好，update有时候会引起数据页的分页，一列的表所需的页不多，但是多列的话页就很多，分页会引起很多额外的IO，但是一般来说两者的性能问题其实可以忽略，重要的是考虑如何提高where条件

[LongRui888]

引用 9 楼 bashen1101 的回复:

[quote=引用 3 楼 DBA_Huangzj 的回复:] 下面那个，要看数据量

其他字段的数据量是多的。 其实我的意思是，2张表的都有A字段INT，都对A字段SET 0（不带WHERE，全表），1张表只有个ID字段，1张表还有各种其他字段，但都和A字段无关，索引也都是非聚集索引，都和A字段无关。 以上这种情况，是否还有性能的影响？[/quote] 字段多了，数据页就多，因为虽然你只是更新a字段，但其实在更新之前，是需要把所有的字段，都加载到内存中的。 所以肯定字段多，效率就会差一点 不过，在更新这个A字段的同时，只要a字段上没有索引，那么对其他索引，应该是没什么影响

[bashen1101]

引用 11 楼 yupeigu 的回复:

[quote=引用 9 楼 bashen1101 的回复:] [quote=引用 3 楼 DBA_Huangzj 的回复:] 下面那个，要看数据量

其他字段的数据量是多的。 其实我的意思是，2张表的都有A字段INT，都对A字段SET 0（不带WHERE，全表），1张表只有个ID字段，1张表还有各种其他字段，但都和A字段无关，索引也都是非聚集索引，都和A字段无关。 以上这种情况，是否还有性能的影响？[/quote] 字段多了，数据页就多，因为虽然你只是更新a字段，但其实在更新之前，是需要把所有的字段，都加载到内存中的。 所以肯定字段多，效率就会差一点 不过，在更新这个A字段的同时，只要a字段上没有索引，那么对其他索引，应该是没什么影响[/quote] 哦，原来是这样，总感觉这么做很不聪明呢，如果一个表有很多字段都是NVARCHAR(MAX）的，那我更新一个INT型的FLAG字段，然后SQL在更新时将其他NVARCHAR(MAX)的也加载到数据页也会受牵连变得很慢是吧？有什么好办法么

[發糞塗牆]

因为是以页（一个或者多个）为单位加载到内存，所以会把其他列带上，如果列上有索引，更新的影响就相对较少

[LongRui888]

引用 12 楼 bashen1101 的回复:

[quote=引用 11 楼 yupeigu 的回复:] [quote=引用 9 楼 bashen1101 的回复:] [quote=引用 3 楼 DBA_Huangzj 的回复:] 下面那个，要看数据量

其他字段的数据量是多的。 其实我的意思是，2张表的都有A字段INT，都对A字段SET 0（不带WHERE，全表），1张表只有个ID字段，1张表还有各种其他字段，但都和A字段无关，索引也都是非聚集索引，都和A字段无关。 以上这种情况，是否还有性能的影响？[/quote] 字段多了，数据页就多，因为虽然你只是更新a字段，但其实在更新之前，是需要把所有的字段，都加载到内存中的。 所以肯定字段多，效率就会差一点 不过，在更新这个A字段的同时，只要a字段上没有索引，那么对其他索引，应该是没什么影响[/quote] 哦，原来是这样，总感觉这么做很不聪明呢，如果一个表有很多字段都是NVARCHAR(MAX）的，那我更新一个INT型的FLAG字段，然后SQL在更新时将其他NVARCHAR(MAX)的也加载到数据页也会受牵连变得很慢是吧？有什么好办法么[/quote] 这个确实没有什么太好的办法，因为一行数据，存储到一个数据页中，他每次都是不所有的数据页都加载到内存中，除非你把一个表进行拆分，比如你有10个字段，有一个主键id，那么可以考虑： 主键id，a字段 一个表， 另外一个表就是：主键id，其他字段。 这样在更新表的时候，就只会涉及到第一个表了，就不会把其他大量的字段都加载到内存，可以提高效率

[bashen1101]

谢谢2位的解答。