关于数据库的索引问题以及面试常考点

如果面试问到数据库，一般都会被问到这样一个问题，如果数据库现在数据量特别大，普通查询比较慢，现在怎么办？或者直接问你知道数据库的索引吗？反正提到数据库或者用到数据库的项目，索引是跑不了的。而且在我们实际做项目的时候，写数据库的时候，索引也是必不可少的。今天就谈谈我对索引的认识，如果有错误，欢迎大家指出！

准备整理的时候，去网上看到很多说明都一样，资料很混乱，一方面是粘贴复制的风气，一方面是因为版本更新太快，而每个版本的变化都挺大的，所以存在一些混乱。下面我对我要讲到的地方尽可能的说明，如有错误，欢迎指出！

1. 索引的数据结构

现在开始讲，提到索引，必不可少的是mysql的默认索引结构是什么？毫无疑问是B+树。那为什么不用哈希表、二叉树、红黑树或者普通的B树呢。

我们一个个说，先讲哈希表，其实也是可以用哈希表索引的，不过大家想想，我们平时查询的时候，什么样的查询方式更多一些，是范围查找。它的优点是可以准确的进行查找，但是它不支持范围查找，而范围查找是我们常常要用的。

为什么不用二叉树呢？因为正常来说，数据库是要存储相当多的数据的，而二叉树一个节点只能存储一个索引，当存储大量数据的时候，二叉树的高度会过高，这样严重影响了查询效率，极端情况下会呈链表状。红黑树也有这样的问题，虽然可以平衡左右节点高度，但是它需要通过不断的旋转和平衡来保持节点高度，这极大的消耗了资源，所以也不会采取。

最后说说和B树的区别。首先他们都是可以在一个节点上存储多个节点的，并且索引是按顺序插入的。B+树相对于B树的区别主要是两个，第一个是它将data数据统一下放到叶子节点上，在非叶子节点上只保存索引，这样就可以在节点上保存更多的索引，有效的减少了树的高度。第二是将叶子节点变成链表结构，及上一个节点指向下一个节点，这样更有利于范围查找。一般来说索引占用8个自己，指针占用6字节，默认给一个节点分配16kb，也就是说一个节点可以存储1170个节点，这样千万级别的数据索引只需要三行，这几大的加快了查询的速度。当然这里只是一个大概，实际来说还要看具体分配多少大小和索引是什么类型的节点。（这里卖一个关子，那么说索引一个节点的大小是怎么给的呢）

2.索引的类型

在MySQL中，索引分为两大类：聚簇索引和非聚簇索引。聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引则不同；聚簇索引能够提高多行检索的速度，而非聚簇索引则对单行的检索速度很快。

一般来说，如果表里设置了主键，那么主键就是聚簇索引。

在这两大类的索引类型下，还可以将索引分成四个小类：

1，普通索引：最基本的索引，也叫二级索引，没有任何限制，是我们大多数情况下使用到的索引。

2，唯一索引：与普通索引类型，不同的是唯一索引的列值必须唯一，但允许为空值。

3，全文索引：全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。

4，组合索引：将几个列作为一条索引进行检索，使用最左匹配原则。

3.存储结构

重头戏来了，假如现在有一个user表，字段为id,name,age,id是主键。现在查询语句是select * from user where name="张一"。（这一段参考了三太子敖丙的公众号，在此贴出，大家如果看到这边博客的话，可以去关注一下他，他写的东西很通俗易懂。）

你觉得这个查询语句好不好？有没有什么优化的地方？

这个问题也是我们一提数据库优化大概率会说的尽量少用*。再说如何优化之前，我们先说一下这句式如何运行的，在说如何运行之前，我们先说数据是如何存储的。刚刚我们说了你知道索引一个节点的大小是怎么给的吗？正常来说是页的倍数。页是数据库的基本存储结构。也就是说所有的记录实际是存储在页里的。每次插入一个记录，都是在页的特定位置插入。其他位置记录一些其他信息，这个我们可以不用管。而且每个数据页会为他存储的记录生成一个页目录。

不同数据页构成一个双向链表。而一个数据页内不同的记录用构成一个单向链表。

回到这句是怎么运行的。如果用主键查找的话，首先遍历数据页，定位到对应的数据页，然后再页目录里使用二分查找法查找对应的槽，然后再遍历槽对应分组中的记录即可找到。

但是name不是主键，也不是索引，那么它就不能到页目录里找，者能够遍历所有的数据进行查找，再数据量特别大的时候，这样毫无疑问是非常慢的。

4 .回表和覆盖索引这个也是如何优化数据库会问到的。

下面还是通过上面的user表来说明：

create table user(
	id int(10) auto_increment,
    name varchar(30),
    age int(10),
    primary key (id),
    index idx_age (age)
)engine=innoDB charset=utf8

上面就是一个典型的创建表和定义索引的方式，这里定义了一个主键索引id，和一个普通索引age。

id是主键，因此是聚簇索引，它的叶子节点存储是对应的记录数据；

age是非聚簇索引，它的叶子节点存储的是它对应的主键。

那什么是回表呢，如果我们以主键为查询条件，比如 select * from user where id =2,它只需要扫描一次B+树就可以找到要查找的行录数据。但是如果以普通索引为查找条件，比如select *from user where age=24.它就要扫描两次B+树，第一次是通过索引age找到它对应的id，然后通过id再找到相应的记录数据。这就是回表查询。

显然，回表查询想能肯定更低，因为它需要两次遍历，优化就是针对它的，可以采用覆盖索引的方法。如何实现呢？

最常见的就是避免使用*，这里如果是select id,age from user where age=24

这样就只需要扫描一次了，因为一次已经拿到了所需要的值，但是如果是拿select id,age，name from user where age=24，就需要其他办法了。因为一次遍历拿不到name。

常见的就是组合索引，比如这里就可以建立组合索引idx_age_name(age,name).注意如果之前已经存在索引，比如这里的age，最好不好删除age，再重新建立组合索引，可以直接选择更新索引。

5.最左前缀匹配原则

这个网上就有很多了，大家搜一下就行了。

接下来会更新数据库调优的知识。