数据库索引

.

数据库索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是一种数据结构。

一 创建索引好处

1. 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

2. 将随机 I/O 变为顺序 I/O(B+Tree 索引是有序的，会将相邻的数据都存储在⼀一起)。

3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

二 索引缺点

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2. 索引需要占物理空间，增加了数据库的存储空间。
3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

三 索引失效

1. 如果MySQL估计使用全表扫秒比使用索引快，则不使用索引；
2. 如果条件中有or，即使其中有条件带索引也不会使用；
3. 复合索引，如果索引列不是复合索引的第一部分，则不使用索引（即不符合最左前缀）；
4. 如果like是以 % 开始的，则该列上的索引不会被使用；
5. 如果列为字符串，则where条件中必须将字符常量值加引号，否则即使该列上存在索引，也不会被使用；

四 在什么情况下适合建立索引

1. 为经常出现在关键字order by、group by、distinct后面的字段，建立索引；
2. 在union等集合操作的结果集字段上，建立索引
3. 为经常用作查询选择 where 后的字段，建立索引；
4. 在经常用作表连接 join 的属性上，建立索引；
5. 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度
6. 考虑使用索引覆盖。
7. 对数据很少被更新的表，在经常需要搜索的列上建立索引，从而将表的扫描改变为索引的扫描。

五 为何采用B+树作为索引

1. 更少的查找次数

平衡树查找操作的时间复杂度和树⾼高 h 相关，O(h)=O(logdN)，其中 d 为每个节点的出度。

红⿊树的出度为 2，⽽而 B+ Tree 的出度⼀一般都⾮非常⼤大，所以红⿊黑树的树⾼高 h 很明显⽐比 B+ Tree ⼤大⾮非常 多，查找的次数也就更更多。

2. 利用磁盘预读特性

为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，⽽是每次都会预读。预读过程中，磁盘进行顺序 读取，顺序读取不需要进行磁盘寻道，并且只需要很短的旋转时间，速度会⾮非常快。

六 三种索引

1. 唯一索引: 唯一索引是不允许其中任何两行具有相同索引值的索引。数据库还可能防止添加将在表中创建重复键值的新数据.
2. 主键索引，在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
3. 聚集索引，在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

七 索引查询数据的流程

非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。有一种例外可以不使用聚集索引就能查询出所需要的数据， 这种的方法 称之为「覆盖索引」查询， 也就是平时所说的复合索引或者多字段索引查询。

非聚集索引查找数据的步骤：

//建立索引
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字段上的索引改成birthday, user_name双字段的覆盖索引

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的值返回即可。 通过这种覆盖索引直接查找的方式， 可以省略不使用覆盖索引查找的后面两个步骤， 大大的提高了查询性能