没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
MySQL常见⾯试题总结
MySQL常见⾯试题总结2---索引
概述
在数据之外,数据库系统还维护着满⾜特定查找算法的数据结构,这些数据结
构以某种⽅式引⽤(指向)数据,这样就可以在这些数据结构上实现⾼级查找
算法。这种数据结构,就是索引。
上图展⽰了⼀种可能的索引⽅式。左边是数据表,⼀共有两列七条记录,最左
边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也并不是⼀定物
理相邻的)。为了加快Col2的查找,可以维护⼀个右边所⽰的⼆叉查找树,每
个节点分别包含索引键值和⼀个指向对应数据记录物理地址的指针,这样就可
以运⽤⼆叉查找在O(log2n)的复杂度内获取到相应数据。
索引的优点(为什么要创建索引)
创建索引可以⼤⼤提⾼系统的性能。
⼤⼤减少了服务器需要扫描的数据⾏数。
帮助服务器避免进⾏排序和分组,以及避免创建临时表(B+Tree
索引是有序的,可以⽤于ORDER BY 和 GROUP BY
操作。临时表主要是在排序和分组过程中创建,不需要排序和分组,也就不需
要创建临时表)。
将随机 I/O 变为顺序 I/O。
过创建唯⼀性索引,可以保证数据库表中每⼀⾏数据的唯⼀性。
可以⼤⼤加快数据的检索速度,这也是创建索引的最主要的原因。
可以加速表和表之间的连接,在实现数据的参考完整性⽅⾯特别有意义。
在使⽤分组和排序⼦句进⾏数据检索时,同样可以显著减少查询中分组和排序
的时间。
通过使⽤索引,可以在查询的过程中,使⽤优化隐藏器,提⾼系统的性能。
建⽴索引的不利因素(缺点)
创建索引和维护索引要耗费时间,这种时间随着数据量的增加⽽增加。
索引需要占物理空间,除了数据表占数据空间之外,每⼀个索引还要占⼀定的
物理空间,如果要建⽴聚簇索引,那么需要的空间就会更⼤。
当对表中的数据进⾏增加、删除和修改的时候,索引也要动态的维护,这样就
降低了数据的维护速度。
创建索引的准则
在经常需要搜索的列上,可以加快搜索的速度;
在作为主键的列上,强制该列的唯⼀性和组织表中数据的排列结构;
在经常⽤在连接的列上,这些列主要是⼀些外键,可以加快连接的速度;
在经常需要根据范围进⾏搜索的列上创建索引,因为索引已经排序,其指定的
范围是连续的;
在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利⽤索引
的排序,加快排序查询时间;
在经常使⽤在WHERE⼦句中的列上⾯创建索引,加快条件的判断速度。
对于⾮常⼩的表、⼤部分情况下简单的全表扫描⽐建⽴索引更⾼效;对于中到
⼤型的表,索引就⾮常有效;但是对于特⼤型的表,建⽴和维护索引的代价将
会随之增长。这种情况下,需要⽤到⼀种技术可以直接区分出需要查询的⼀组
数据,⽽不是⼀条记录⼀条记录地匹配,例如可以使⽤分区技术。
哪些列不应该创建索引?
对于那些在查询中很少使⽤或者参考的列不应该创建索引。这是因为,既然这
些列很少使⽤到,因此有索引或者⽆索引,并不能提⾼查询速度。相反,由于
增加了索引,反⽽降低了系统的维护速度和增⼤了空间需求。
对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取
值很少,例如⼈事表的性别列,在查询的结果中,结果集的数据⾏占了表中数
据⾏的很⼤⽐例,即需要在表中搜索的数据⾏的⽐例很⼤。增加索引,并不能
明显加快检索速度。
对于那些定义为text,
image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当
⼤,要么取值很少。
当修改性能远远⼤于检索性能时,不应该创建索引。这是因为,修改性能和检
索性能是互相⽭盾的。当增加索引时,会提⾼检索性能,但是会降低修改性能
。当减少索引时,会提⾼修改性能,降低检索性能。因此,当修改性能远远⼤
于检索性能时,不应该创建索引。
聚集索引和⾮聚集索引的区别
聚集索引⼀个表只能有⼀个;⽽⾮聚集索引⼀个表可以存在多个。
聚集索引存储记录是物理上连续存在;⽽⾮聚集索引是逻辑上的连续,物理存
储并不连续。
聚集索引:物理存储按照索引排序(数据⾏的物理顺序与列值(⼀般是主键的
那⼀列)的逻辑顺序相同);聚集索引是⼀种索引组织形式,索引的键值逻辑
顺序决定了表数据⾏的物理存储顺序;
⾮聚集索引:物理存储不按照索引排序(索引的逻辑顺序与磁盘上⾏的物理存
储顺序不同);⾮聚集索引是普通索引,仅仅只是对数据列创建相应的索引,
不影响整个表的物理存储顺序。
聚集索引的叶节点就是数据节点。⽽⾮聚集索引的叶节点仍然是索引节点,只
不过有⼀个指针指向对应的数据块。
聚集索引的约束是唯⼀性,是否要求字段也是唯⼀的呢?
不要求唯⼀。⼀般我们指定⼀个表的主键,如果这个表之前没有聚集索引,同
时建⽴主键时候没有强制指定使⽤⾮聚集索引,SQL会默认在此字段上创建⼀个
聚集索引,⽽主键都是唯⼀的,所以理所当然的认为创建聚集索引的字段也需
要唯⼀。聚集索引可以创建在任何⼀列你想创建的字段上,这是从理论上讲,
实际情况并不能随便指定,否则在性能上会是恶梦。
为什么聚集索引可以创建在任何⼀列上,如果此表没有主键约束,即有可能存
在重复⾏数据呢?
如果未使⽤ UNIQUE
属性创建聚集索引,数据库引擎将向表⾃动添加⼀个四字节 uniqueifier
列。必要时,数据库引擎 将向⾏⾃动添加⼀个 uniqueifier
值,使每个键唯⼀。此列和列值供内部使⽤,⽤户不能查看或访问。
在主键上创建聚集索引的表在数据插⼊上为什么⽐主键上创建⾮聚集索引表速
度要慢?
在有主键的表中插⼊数据⾏,由于有主键唯⼀性的约束,所以需要保证插⼊的
数据没有重复。聚集索引由于索引叶节点就是数据页,所以如果想检查主键的
唯⼀性,需要遍历所有数据节点才⾏。但⾮聚集索引不同,由于⾮聚集索引上
已经包含了主键值,所以查找主键唯⼀性,只需要遍历所有的索引页就⾏(索
引的存储空间⽐实际数据要少),这⽐遍历所有数据⾏减少了不少IO消耗。
何时使⽤聚集索引,何时使⽤⾮聚集索引?
局部性原理与磁盘预读
由于存储介质的特性,磁盘本⾝存取就⽐主存慢很多,再加上机械运动耗费,
磁盘的存取速度往往是主存的⼏百分分之⼀,因此为了提⾼效率,要尽量减少
磁盘I/O。为了达到这个⽬的,磁盘往往不是严格按需读取,⽽是每次都会预读
,即使只需要⼀个字节,磁盘也会从这个位置开始,顺序向后读取⼀定长度的
数据放⼊内存。这样做的理论依据是计算机科学中著名的局部性原理:当⼀个
数据被⽤到时,其附近的数据也通常会马上被使⽤。程序运⾏期间所需要的数
据通常⽐较集中。
由于磁盘顺序读取的效率很⾼(不需要寻道时间,只需很少的旋转时间),因
此对于具有局部性的程序来说,预读可以提⾼I/O效率。
剩余11页未读,继续阅读
资源评论
sun7bear
- 粉丝: 2
- 资源: 121
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功