一. 数据库索引是什么
数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。
索引是建立在数据库表中的某些列的上面。在创建索引的时候,应该考虑在哪些列上可以创建索引,在哪些列上不能创建索引。一般来说,应该在这些列上创建索引:在经常需要搜索的列上,可以加快搜索的速度;在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点:
根据数据库的功能,可以在数据库设计器中创建三种索引:唯一索引、主键索引和聚集索引。
唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如,如果在employee表中职员的姓(lname)上创建了唯一索引,则任何两个员工都不能同姓。
数据库表经常有一列或列组合,其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问。
在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引,则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比,聚集索引通常提供更快的数据访问速度。
为表设置索引要付出代价的:一是增加了数据库的存储空间,二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。 优势:创建索引可以大大提高系统的性能。
也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?因为,增加索引也有许多不利的方面。索引的缺点
不等式 <> ,!=, >, in, or 等会导致索引实现
说明:一个数据库里面一个状态字段 status 是数字字符类型的,咱们查询的时候直接用数字类型,会导致索引失效,最终会对全表尽心扫描。
说明:例如使用 函数计算 x+1 、 x-1 、CHAR_LENGTH(x) 等会导致索引失效
说明:如 % aa 这样的查询条件
如果索引了多个列,要遵守最佳左前缀法则。指的是查询从索引的最左前列开始 并且 不跳过索引中的列。
单值索引在B+树的结构里,一个节点只存一个键值对
开局一张图,由数据库的a字段和b字段组成一个联合索引。
从本质上来说,联合索引也是一个B+树,和单值索引不同的是,联合索引的键值对不是1,而是大于1个。
a, b 排序分析
a顺序:1,1,2,2,3,3
b顺序:1,2,1,4,1,2
大家可以发现a字段是有序排列,b字段是无序排列(因为B+树只能选一个字段来构建有序的树)一不小心又会发现,在a相等的情况下,b字段是有序的。 大家想想平时编程中我们要对两个字段排序,是不是先按照第一个字段排序,如果第一个字段出现相等的情况,就用第二个字段排序。这个排序方式同样被用到了B+树里。
分析最佳左前缀原理 先举一个遵循最佳左前缀法则的例子
select * from testTable where a=1 and b=2
分析如下:
首先a字段在B+树上是有序的,所以我们可以通过二分查找法来定位到a=1的位置。其次在a确定的情况下,b是相对有序的,因为有序,所以同样可以通过二分查找法找到b=2的位置。 再来看看不遵循最佳左前缀的例子
select * from testTable where b=2
分析如下:
我们来回想一下b有顺序的前提:在a确定的情况下。现在你的a都飞了,那b肯定是不能确定顺序的,在一个无序的B+树上是无法用二分查找来定位到b字段的。所以这个时候,是用不上索引的。
范围查询右边失效原理
select * from testTable where a>1 and b=2
分析如下:
首先a字段在B+树上是有序的,所以可以用二分查找法定位到1,然后将所有大于1的数据取出来,a可以用到索引。b有序的前提是a是确定的值,那么现在a的值是取大于1的,可能有10个大于1的a,也可能有一百个a。大于1的a那部分的B+树里,b字段是无序的(开局一张图),所以b不能在无序的B+树里用二分查找来查询,b用不到索引。
like索引失效原理
where name like "a%"
where name like "%a%"
where name like "%a"
我们先来了解一下%的用途
为什么%放在右边有时候能用到索引
没错,这里依然是最佳左前缀法则这个概念
大家可以看到,上面的B+树是由字符串组成的。
字符串的排序方式:先按照第一个字母排序,如果第一个字母相同,就按照第二个字母排序。。。以此类推
开始分析
一、%号放右边(前缀)
由于B+树的索引顺序,是按照首字母的大小进行排序,前缀匹配又是匹配首字母。所以可以在B+树上进行有序的查找,查找首字母符合要求的数据。所以有些时候可以用到索引。
二、%号放左边
是匹配字符串尾部的数据,我们上面说了排序规则,尾部的字母是没有顺序的,所以不能按照索引顺序查询,就用不到索引。
三、两个%%号
这个是查询任意位置的字母满足条件即可,只有首字母是进行索引排序的,其他位置的字母都是相对无序的,所以查找任意位置的字母是用不上索引的。
适用场景: MyISAM适合: 插入不频繁,查询非常频繁,如果执行大量的SELECT,MyISAM是更好的选择, 没有事务。 InnoDB适合: 可靠性要求比较高,或者要求事务; 表更新和查询都相当的频繁, 大量的INSERT或UPDATE
文章来自问我学院(问我社区);原文链接:http://www.wenwoha.com/8/course_article?act_id=62
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。