外键上建立索引的好处

最新推荐文章于 2023-12-29 08:30:00 发布
最新推荐文章于 2023-12-29 08:30:00 发布
阅读量1.3w 收藏 6
点赞数 2
文章标签： sql server SQL SERVER SQL Server SQL server 外键 索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/misterliwei/article/details/8362893
版权

外键上建立索引的好处

 

在主从表设计中，常常使用外键在这两个表之间建立关联。当从主表中删除一行数据时，SQL Server便会检查从表中是否有相同外键的行存在。如果从表没有包含外键列的索引，SQL SERVER需要扫描整个从表。可以想象：从表越大，删除的时间越长。更新主表时的情况也是一样。

 

INSERT

DELETE

UPDATE

主表（被引用表）

不作检查

检查从表是否有该键值的引用

检查从表是否有旧键值的引用

 因为主表有唯一聚集或非聚集的索引，所以在从表中插入或修改时，能利用主表的索引快速定位。

 下面来举例说明：

先创建两个表：主表ta(col1, col2)，从表tb(col3, col4, col5),tb表的col4列引用ta表的col1列。先不要再col4上创建索引。

if object_id('tb')isnot null

drop tabletb

go

if object_id('ta')isnot null

drop tableta

go

create tableta(col1intprimary key,col2 char(2000))

go

 createtabletb(col3intprimary key,col4 int,col5 char(2000)

constraint fk1 foreign key (col4)referencesta(col1)ondelete cascade

)

go

declare @i int

set @i= 1

while @i<=30000

begin

  insert into ta

  values(@i,@i)

   set @i=@i + 1

end;

go

 declare@i int

set @i= 1

while @i<=30000

begin

  insert into tb

  values(@i,@i,@i)

   set @i=@i + 1

end; 

执行ta上的删除动作，看看计划与IO：

dbcc dropcleanbuffers

go

dbcc freeproccache

go

set statisticsioon

set statisticstimeon

go

delete ta where col1 = 20087 

计划如下：

 

可以看出，由于没有col4上的索引，所以SQL SERVER只得在tb表上进行聚集索引扫描。IO如下：

SQL Server 执行时间:

   CPU 时间= 0 毫秒，占用时间= 1 毫秒。

SQL Server 分析和编译时间:

   CPU 时间= 0 毫秒，占用时间= 21 毫秒。

SQL Server 分析和编译时间:

   CPU 时间= 0 毫秒，占用时间= 1 毫秒。

表'tb'。扫描计数1，逻辑读取7529 次，物理读取3 次，预读7521 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'Worktable'。扫描计数2，逻辑读取7 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'ta'。扫描计数0，逻辑读取3 次，物理读取3 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

  

下面在表tb的col4上创建一个非聚集索引，同样执行上面的删除动作，再看看结果：

create nonclusteredindexnc1 on tb(col4)

go

dbcc dropcleanbuffers

go

dbcc freeproccache

go 

set statisticsioon

set statisticstimeon

go 

delete ta where col1 = 20088


 

果然，SQL SERVER使用了索引查找。IO结果如下：

SQL Server 执行时间:

   CPU 时间= 0 毫秒，占用时间= 1 毫秒。

SQL Server 分析和编译时间:

   CPU 时间= 0 毫秒，占用时间= 14 毫秒。

SQL Server 分析和编译时间:

   CPU 时间= 0 毫秒，占用时间= 1 毫秒。

表'tb'。扫描计数1，逻辑读取9 次，物理读取4 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'Worktable'。扫描计数2，逻辑读取7 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'ta'。扫描计数0，逻辑读取3 次，物理读取2 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

 对比两个IO结果，会发现在外键上建立索引大大节省了时间。

 参考文献

1.       http://www.sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys

2.       http://msdn.microsoft.com/zh-cn/library/ms175464(v=sql.105).aspx

 

 

misterliwei
关注
  • 2
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL创建数据表并建立主外键关系详解
12-16
外键列和参照列必须创建索引，如果外键列不存在索引，mysql将自动创建索引。 一、SQL语句创建数据表并设置主外键关系 create table demo.ChineseCharInfo ( ID int not null auto_increment, Hanzi varchar(10) ...
建立索引的原则
Starqinsx
12-20 2134
1) 定义主键的数据列一定要建立索引。 2) 定义有外键的数据列一定要建立索引。 3) 对于经常查询的数据列最好建立索引。 4) 对于需要在指定范围内的快速或频繁查询的数据列; 5) 经常用在WHERE子句中的数据列。 6) 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面
(五)MySQL索引应用篇：建立索引的正确姿势与使用索引的最佳指南！
weixin_54542328的博客
12-29 1092
至此，MySQL索引应用篇，也就是索引中篇就结束了，相信大家认真看完本篇之后，对于索引的掌握性、熟练程度绝对会更上一层楼，因为本章中从索引的优劣分析，到建立索引的原则、索引失效的情景、使用索引的正确姿势、MySQL对于索引的优化机制等各方面，对索引进行了进一步阐述。经历中、上两篇的阐述后，对于MySQL。
案例分析1：为什么强烈建议所有的外键要加索引
njhart2003的专栏
07-23 987
  环境准备：建主从表，主表dept,子表emp及主外键约束======================================================================chennan@cwgl94>CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;表已创建。chennan@cwgl94>CREATE 
关于Oracle外键列上是否需要索引问题
赶路人儿
08-14 825
外键列上缺少索引会带来两个问题，限制并发性、影响性能。而这两个问题中的任意一个都可能会造成严重性能问题。   无论是Oracle的官方文档，还是在Tom的书中都说明了两种情况下可以忽略外键上的索引。其实我认为不需要那么麻烦，与增加一个   索引所带来的性能开销和磁盘空间开销相比，缺失索引可能引发的问题要严重得多。因此，我会选择在所有的外键列上添加索引，虽然  
MySql学习笔记----索引,外键
tshangshi的专栏
11-16 542
索引索引简介索引由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构。索引可以提高查询的速度。通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。例如，索引相当于新华字典的音序表。索引有两种存储类型，包括B型树（BTREE）索引和哈希（HASH）索引。Inn
外键为何要建索引？
知识分享
03-12 4312
项目中，我们要求凡是有主子关系的表都要使用外键约束，来保证主子表之间关系的正确，不推荐由应用自己控制这种关系。 但发现有时开发人员提交SQL语句时未必会注意外键列需要定义索引，或者不清楚为什么外键列需要建立索引，网上一些所谓的“宝典”也会将外键列建索引作为其中的一条，包括TOM大师，曾说过： 导致死锁的头号原因是外键未加索引（第二号原因是表上的位图索引遭到并发更新）。在以下两种情况下，Ora...
MySQL外键字段为什么必须加索引
黄佳俊的博客
12-26 2615
外键字段为什么需要索引？主要原因就在于查询性能了。父子表之间在进行外键检查时，需要一条一条每条必查地校验，而且在对父表的SELECT操作时，为了避免产出数据不一致，使用的是一致性锁定读（SELECT …… LOCK IN SHARE MODE），主动加一个阻塞其他修改操作的读锁。如果没有索引，对于大一点的表而言那效率就非常低下了。参考官方文档https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html，有一段具体的说明。 M
oracle如何创建外键列,为什么子表外键列需要建立索引?（上）
weixin_42628573的博客
04-09 523
外键是我们经常使用的数据库约束方式。它保证了子表上的特定索引列取值一定与父表列(主键列)相一致对应。而且保证在子表没有对应子记录的情况下，父表数据才能删除。应该说，外键是实现数据库完整性、消除潜在脏数据风险的重要手段。在使用外键的时候，我们经常会要求在子表外键列上建立索引。这个“江湖定则”有什么样的原因呢？本篇我们一起来进行探讨。还是使用组合实验的方式来进行研究。1、环境准备我们在Oracle 1...
为什么外键最好加上索引？
Edison Xu
09-05 3049
今天遇到一个数据库DeadLock的问题，与同事讨论，一个同事忽然问了一句：“为什么外键没有索引会导致deadlock“? 为了回答这个问题，我直接翻了Oracle文档，里面两张图画的非常直接。 文章地址：http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#sthref3108 Unindexe
数据库基本操作(创建主键、外键以及索引）
热门推荐
weixin_45859411的博客
05-30 1万+
​ 图书馆学习没有多大干劲了，数据库又快考试了根据考试内容写总结。老师回放已过期不得不自己来尝试 心酸！ 需要按照要求给表创建主键、创建索引，创建外键（并设置删除和更新规则） 一、创建主键 ​ 自己创建一个数据库和最少两个表（student表 和 course 表），然后上图：点击创建主键 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kEcUycta-1622373901728)(C:\Users\赵轶楠\AppData\Roaming\Typ
详解MySQL 外键约束
12-14
外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立； 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而...
MySQL外键使用详解
12-15
（2）外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作； （3）外键的作用： 保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值！ ...
MySQL外键使用及说明详解
12-16
外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立； 　3.外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以...
Mysql增加外键的方法
12-14
帮助您学会增加外键，在建表的时候没有加外键在之后加的方法
施耐德PLC例程源码twidopid控制实列
04-22
施耐德PLC例程源码twido pid 控制实列提取方式是百度网盘分享地址
node-v19.2.0-darwin-arm64.tar.xz
04-22
Node.js，简称Node，是一个开源且跨平台的JavaScript运行时环境，它允许在浏览器外运行JavaScript代码。Node.js于2009年由Ryan Dahl创立，旨在创建高性能的Web服务器和网络应用程序。它基于Google Chrome的V8 JavaScript引擎，可以在Windows、Linux、Unix、Mac OS X等操作系统上运行。 Node.js的特点之一是事件驱动和非阻塞I/O模型，这使得它非常适合处理大量并发连接，从而在构建实时应用程序如在线游戏、聊天应用以及实时通讯服务时表现卓越。此外，Node.js使用了模块化的架构，通过npm（Node package manager，Node包管理器）,社区成员可以共享和复用代码，极大地促进了Node.js生态系统的发展和扩张。 Node.js不仅用于服务器端开发。随着技术的发展，它也被用于构建工具链、开发桌面应用程序、物联网设备等。Node.js能够处理文件系统、操作数据库、处理网络请求等，因此，开发者可以用JavaScript编写全栈应用程序，这一点大大提高了开发效率和便捷性。 在实践中，许多大型企业和组织已经采用Node.js作为其Web应用程序的开发平台，如Netflix、PayPal和Walmart等。它们利用Node.js提高了应用性能，简化了开发流程，并且能更快地响应市场需求。
node-v15.12.0-darwin-x64.tar.xz
最新发布
04-22
Node.js，简称Node，是一个开源且跨平台的JavaScript运行时环境，它允许在浏览器外运行JavaScript代码。Node.js于2009年由Ryan Dahl创立，旨在创建高性能的Web服务器和网络应用程序。它基于Google Chrome的V8 JavaScript引擎，可以在Windows、Linux、Unix、Mac OS X等操作系统上运行。 Node.js的特点之一是事件驱动和非阻塞I/O模型，这使得它非常适合处理大量并发连接，从而在构建实时应用程序如在线游戏、聊天应用以及实时通讯服务时表现卓越。此外，Node.js使用了模块化的架构，通过npm（Node package manager，Node包管理器）,社区成员可以共享和复用代码，极大地促进了Node.js生态系统的发展和扩张。 Node.js不仅用于服务器端开发。随着技术的发展，它也被用于构建工具链、开发桌面应用程序、物联网设备等。Node.js能够处理文件系统、操作数据库、处理网络请求等，因此，开发者可以用JavaScript编写全栈应用程序，这一点大大提高了开发效率和便捷性。 在实践中，许多大型企业和组织已经采用Node.js作为其Web应用程序的开发平台，如Netflix、PayPal和Walmart等。它们利用Node.js提高了应用性能，简化了开发流程，并且能更快地响应市场需求。
毕业设计 一款家庭记账本.zip
04-22
毕业设计 一款家庭记账本
为何建议在外键上创建索引
07-15
在外键上创建索引有几个好处： 1. 提高查询性能：外键是用来建立表与表之间的关联关系的，经常会在联接查询中使用。如果在外键上创建索引，可以加快联接查询的速度，减少数据的扫描量，提高查询性能。 2. 维护数据完整性：外键是用来保持数据的完整性约束的，它确保了表之间的关联关系。当插入、更新或删除数据时，数据库会自动检查外键约束，确保数据的一致性。如果在外键上创建索引，可以加快这些操作的速度，减少锁定时间。 3. 提高数据一致性：外键的存在可以确保表之间的数据一致性。当插入、更新或删除数据时，数据库会自动检查外键约束，确保操作的有效性。如果在外键上创建索引，可以加快这些检查的速度，减少操作的延迟。 总之，通过在外键上创建索引，可以提高查询性能、维护数据完整性和提高数据一致性。这对于一个拥有复杂关联关系的数据库来说尤为重要。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值