数据库索引原理

柔情只为你懂 2022-06-06 05:56 215阅读 0赞

#### **1、为什么需要数据库索引？** ####

我们来看一个简单的例子：假设有一张数据表Employee，有三列：Employee\_name，Employee\_age，Employee\_address。表中有几万条记录。现在。我们需要执行下面的这条查询语句：查出所有名字叫tom的员工的详细信息。

select * from Employee where Employee_name = "tom"

#### **2、假如没有索引会发生什么？** ####

当我们执行这条查询之后，数据库系统会逐行遍历整张表，对于每一行都要检查其Employee\_name是否等于tom。当我们发现有一条名为tom的记录之后，并不能停止查找，因为可能还有其它叫tom的员工。这就意味着，对于表中的几万条数据，数据库每一条都要进行检查，即全表扫描。这是我们就知道了，**索引的最大作用就是加快查询速度，能从根本上减少需要扫描的表的记录/行的数量**。

#### **3、什么是索引？** ####

**索引就是数据结构**。该数据结构中**存储了这张表中某一列的所有值**，就是说，索引是基于数据表中的某一列创建的。一个索引是由表中某一列上的数据组成，这些数据存储在某个数据结构中，索引就是这个数据机构。

#### **4、索引是怎么提高查询效率的？** ####

**索引本质上是一个存储列值的数据结构**。如果在某列上使用了B+树索引，那么**这些列值在索引中是被排过序的，有序的值是索引能提高查询性能的主要原因**。当我们在Employee\_name列上创建了B+树索引之后，再去执行前面提到的sql语句，数据库就不用再对Employee表做全表扫描了，而是直接从索引中查找名叫tom的员工。由于B+树索引会把所有员工的名字按照字母表的顺序进行排序，这样以t开头的名字都会相邻，查找起来就会很快。值得注意的是，索引除了保存员工姓名之外还会保存该员工在数据表中所在行的指针。这样就可以检索到行中其它列的数据了。

#### **5、索引中保存了什么？** ####

**索引是创建在表中的某列上，其中保存了该列的所有数据**。只保存该列的数据，并不保存其它列的数据。例如我们在Employee\_name列上创建了索引，同一张表的Employee\_age，Employee\_address列的数据不会被保存在索引中。如果我们在索引中保存了其它列的数据就相当于对整张表的复制了，浪费空间效率也低。

#### **6、索引中保存了行的指针？** ####

> 有一个问题：当我们在索引中查找到tom后，怎么获取到这一行中的其它列值呢？

很简单，因为**索引中保存了关联行在表中位置的指针**。也就是说，**索引除了保存列值之外，还保存了与该列值关联的行在表中的位置信息**。因此，Employee\_Name索引中的值(或节点)看起来大概是这个样子 (“tom”, 0x82829)，0x82829就是“tom”这一行在磁盘上的存储地址(指针)。如果没有这个指针，只有一个单独的列值，对我们是没意义的，因为我们无法获取这一行的其他信息。

#### **7、索引的原理？** ####

想要理解索引原理必须清楚一种数据结构**「平衡树」(非二叉)**，也就是b tree或者 b+ tree，重要的事情说三遍：“平衡树，平衡树，平衡树”。当然， 有的数据库也使用哈希桶作用索引的数据结构 ， 然而， 主流的RDBMS都是**把平衡树当做数据表默认的索引数据结构**的。

我们平时建表的时候都会为表加上主键， 在某些关系数据库中， 如果建表时不指定主键，数据库会拒绝建表的语句执行。 事实上， 一个加了主键的表，并不能被称之为「表」。**一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐**， 跟我认知中的「表」很接近。**如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构，换句话说，就是整个表就变成了一个索引**。没错， 再说一遍， 整个表变成了一个索引，也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。  
![这里写图片描述][SouthEast]  
上图就是带有主键的表（聚集索引）的结构图。其中树的所有结点（底部除外）的数据都是由主键字段中的数据构成，也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。 假如我们执行一个SQL语句：

select * from table where id = 1256

首先根据索引定位到1256这个值所在的叶结点，然后再通过叶结点取到id等于1256的数据行。 这里不讲解平衡树的运行细节， 但是从上图能看出，树一共有三层， 从根节点至叶节点只需要经过三次查找就能得到结果。如下图  
![这里写图片描述][SouthEast 1]  
假如一张表有一亿条数据 ，需要查找其中某一条数据，按照常规逻辑， 一条一条的去匹配的话， 最坏的情况下需要匹配一亿次才能得到结果，用大O标记法就是O(n)最坏时间复杂度，这是无法接受的，而且这一亿条数据显然不能一次性读入内存供程序使用， 因此， 这一亿次匹配在不经缓存优化的情况下就是一亿次IO开销，以现在磁盘的IO能力和CPU的运算能力， 有可能需要几个月才能得出结果 。如果把这张表转换成平衡树结构（一棵非常茂盛和节点非常多的树），假设这棵树有10层，那么只需要10次IO开销就能查找到所需要的数据， 速度以指数级别提升，用大O标记法就是O(log n)，n是记录总树，底数是树的分叉数，结果就是树的层次数。换言之，查找次数是以树的分叉数为底，记录总数的对数，用公式来表示就是：  
![这里写图片描述][SouthEast 2]  
用程序来表示就是Math.Log(100000000,10)，100000000是记录数，10是树的分叉数（真实环境下分叉数远不止10）， 结果就是查找次数，这里的结果从亿降到了个位数。因此，利用索引会使数据库查询有惊人的性能提升。然而， 事物都是有两面的， **索引能让数据库查询数据的速度上升， 而使写入数据的速度下降**，原因很简单的， 因为**平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构， 因此，在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因**。

#### **8、聚集索引和非聚集索引** ####

非聚集索引和聚集索引一样， 同样是**采用平衡树作为索引的数据结构**。索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ，那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。如下图：  
![这里写图片描述][SouthEast 3]  
**每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引**。 因此， **给表添加索引，会增加表的体积， 占用磁盘存储空间**。

非聚集索引和聚集索引的区别在于， **通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据**，如下图  
![这里写图片描述][SouthEast 4]  
不管以任何方式查询表， **最终都会利用主键通过聚集索引来定位到数据， 聚集索引（主键）是通往真实数据所在的唯一路径**。

然而， 有一种例外可以不使用聚集索引就能查询出所需要的数据， 这种非主流的方法 称之为「**覆盖索引**」查询， 也就是平时所说的复合索引或者多字段索引查询。 文章上面的内容已经指出， 当为字段建立索引以后， 字段中的内容会被同步到索引之中， 如果为一个索引指定两个字段， 那么这个两个字段的内容都会被同步至索引之中。

先看下面这个SQL语句

//建立索引
    create index index_birthday on user_info(birthday);
    
    //查询生日在1991年11月1日出生用户的用户名
    select user_name from user_info where birthday = '1991-11-1'

这句SQL语句的执行过程如下：

> 首先，通过非聚集索引index\_birthday查找birthday等于1991-11-1的所有记录的主键ID值
> 
> 然后，通过得到的主键ID值执行聚集索引查找，找到主键ID值对就的真实数据（数据行）存储的位置
> 
> 最后， 从得到的真实数据中取得user\_name字段的值返回， 也就是取得最终的结果

我们把birthday字段上的索引改成双字段的覆盖索引

create index index_birthday_and_user_name on user_info(birthday, user_name);

这句SQL语句的执行过程就会变为：

> 通过非聚集索引index\_birthday\_and\_user\_name查找birthday等于1991-11-1的叶节点的内容，然而， 叶节点中除了有user\_name表主键ID的值以外， user\_name字段的值也在里面， 因此不需要通过主键ID值的查找数据行的真实所在， 直接取得叶节点中user\_name的值返回即可。 通过这种覆盖索引直接查找的方式， 可以省略不使用覆盖索引查找的后面两个步骤， 大大的提高了查询性能，如下图：  
> ![这里写图片描述][SouthEast 5]

#### **9、如何创建索引？** ####

前面学习了很多索引的基本原理，下面我们来动手实践一下索引是怎么创建的。

//为Employee_name列创建索引
    CREATE INDEX name_index ON Employee(Employee_name)
    
    //为Employee_name和Employee_age两列创建索引：
    CREATE INDEX name_age_index ON Employee(Employee_name,Employee_age)

#### **10、索引的基本特点** ####

建立索引的目的是加快对表中记录的查找或排序。

为表设置索引要付出代价的：

> 一是增加了数据库的存储空间；  
> 二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录 。创建索引可以大大提高系统的性能。

> 第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。  
> 第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
> 
> 第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
> 
> 第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
> 
> 第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

因为，增加索引也有许多不利的方面：

> 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
> 
> 第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
> 
> 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

#### **11、索引的优点** ####

通过建立索引可以极大地提高在数据库中获取所需信息的速度，同时还能提高服务器处理相关搜索请求的效率，从这个方面来看它具有以下优点：

> 1）在设计数据库时，通过创建一个惟一的索引，能够在索引和信息之间形成一对一的映射式的对应关系，增加数据的惟一性特点。
> 
> 2）能提高数据的搜索及检索速度，符合数据库建立的初衷。
> 
> 3）能够加快表与表之间的连接速度，这对于提高数据的参考完整性方面具有重要作用。
> 
> 4）在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。
> 
> 5）建立索引之后，在信息查询过程中可以使用优化隐藏器，这对于提高整个信息检索系统的性能具有重要意义。

#### **12、索引的缺点** ####

虽然索引的建立在提高检索效率方面具有诸多积极的作用，但还是存在下列缺点 ：

> 1）在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。
> 
> 2）在数据库中创建的索引需要占用一定的物理存储空间，这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间，如果有必要建立起聚簇索引，所占用的空间还将进一步的增加。
> 
> 3）在对表中的数据进行修改时，例如对其进行增加、删除或者是修改操作时，索引还需要进行动态的维护，这给数据库的维护速度带来了一定的麻烦。

#### **13、数据库如何使用索引？** ####

通常，你不需要告诉数据库什么时候使用索引，这由数据库自己决定。但是，对于大多数数据库（例如Oracle和Mysql），你可以强制让数据库使用索引，例如Mysql可以通过“force index”来强制使用索引。

当对数据库中的某张表进行查询的时候，数据库首先会检查查询的列上有没有创建索引。如果有索引，数据库还要判断是否应该使用索引检索要查找的值，因为有些情况，数据库会进行全表扫描：

> 1）统计信息还没有更新
> 
> 通常，数据库的统计信息要与表数据和索引数据保持一致。但是，因为一些原因导致表或索引的统计信息没有及时更新，结果就有可能造成全表扫描。这是因为大多数RDBMS(关系型数据库)的查询优化器会根据这些统计信息来计算是否应该使用索引。如果没有这些统计信息或统计信息不准确，RDBMS可能会错误的认为执行全表扫描比使用索引更高效。
> 
> 2）没有WHRER子句
> 
> 如果查询语句没有过滤结果集的WHRER子句，会执行全表扫描。
> 
> 3）没有使用索引
> 
> 有些情况，即使创建了索引还会执行全表扫描。
> 
> 虽然查询语句有WHERE子句，但是WHERE子句中使用的列没有匹配索引的”领导列”(leading column)，就会执行全表扫描。领导列又称最左列(leftmost column)，见下一节“最左前缀匹配原则”。
> 
> 即使WHERE子句中使用了索引的最左列，仍有可能执行全表扫描。这一般是由于WHRE子句中使用了“比较”操作，而阻止了数据库使用索引！下面列举几个会造成这种情况的例子：
> 
> a、使用不等于操作(!= 或 <>)。
> 
> 例如： WHERE NAME <> ‘Jesus’
> 
> 因为索引只能用于查找表中有什么，而不能用于查找表中没有什么。
> 
> b、使用`NOT`操作符。
> 
> 例如：WHERE NOT NAME ‘Jesus’ 。原因同上。
> 
> c、通配符出现在字符串比较的开始位置。
> 
> 例如：WHERE NAME LIKE ‘%programmer%’ 。
> 
> 以哪个字母开始都不清楚，索引也无能为力了。
> 
> 4）最左前缀匹配原则
> 
> 对于多列的混合索引(composite index)，只有符合最左前缀(leftmost prefix)的索引才能被查询优化器使用。比如，某个3列的混合索引(col1,col2,col3)，只有下面三种情况可以使用到索引： (col1), (col1, col2),和 (col1, col2, col3)。
> 
> 对于下面的查询语句：

SELECT * FROM tbl_name WHERE col1=val1;
    SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
    
    SELECT * FROM tbl_name WHERE col2=val2;
    SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

> 只有前两个SELECT语句使用到了索引，第3个和第4个查询虽然使用了索引列，但是(col2)和(col2,col3)不是混合索引(col1, col2, col3)的最左前缀。

[SouthEast]: /images/20220606/ddbdb6ab147f4caea823f5f5fb28ffdb.png
[SouthEast 1]: /images/20220606/360027340cbe48e693e751f63e4457a7.png
[SouthEast 2]: /images/20220606/a986e8444f68474ca4bddd76d3eb6c88.png
[SouthEast 3]: /images/20220606/a26f14f13dd3421a8f0c9bcce4a85dee.png
[SouthEast 4]: /images/20220606/d0bb856758d5494fac3f3ef7e34ae494.png
[SouthEast 5]: /images/20220606/bb95a2ac128142039e445a08457ec0a5.png