数据库索引

什么是索引？

索引就跟我们的字典目录一样，如果一本字典没有目录，那么你要找一个字，那就相当费劲，得从头一点一点的找；在数据库中也是一样，如果一张表没有索引，那查询一条数据也是相当费劲。

索引是啥样的？

我们都知道，在数据库中的表数据都是一行一行的，但是如果给它加上索引（如果有主键，就会自动生成主键索引），它会变成啥样呢？这里以最常见的B+树索引为例，表结构会变成树状，像下面这样：

索引的分类

索引一般分为两个大类：聚集索引和非聚集索引。我们平时使用的主键索引就是聚集索引，一张表最多只能有一个聚集索引；而平时我们手动创建的普通索引、唯一索引和联合索引等等都属于非聚集索引。

索引的用处

可能很多人在平时使用数据库查询的时候，都没有使用到索引，自己也不创建索引，发现也能很顺畅的查询数据，并没感觉出来有没有索引的区别。这是因为数据量没那么大，有没有索引确实没多大区别。如果数据量达到千万级别或者上亿，那区别就出来了。

索引除了提高查询性能外，还有一个用处：那就是应付面试了😏（面试问索引真的是巨多）。

怎么建索引？

• 创建主键索引

  CREATE PRIMARY KEY INDEX index_name ON table_name (column_name)
  

• 创建唯一索引

  CREATE UNIQUE INDEX index_name ON table_name (column_name)
  

• 创建普通索引

  CREATE INDEX index_name ON table_name(column_name)
  

• 创建联合索引

  CREATE INDEX index_name ON table_name(column1_name,column2_name)
  

上面只是提到了最基础的创建索引的语句，还有一些查询、删除和修改等语句，这里就不再详述。

在创建索引时，索引的命名也最好按规范来，起到望文生义的作用，比如：主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名。

索引虽好，但是也不能随便建，如果建的不好，除了浪费空间外，还会降低性能。所以，如何建索引也是一门学问。

索引创建的原则

上面提到索引也不能随便建，那就需要遵循一些创建原则，下面说一些重要的原则

1. 在经常用作过滤器的字段上建立索引，比如订单表的订单号；
2. 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引；
3. 在值的取值范围较少的字段上不要建立索引，比如性别，只有两种，或者最多三种；
4. 对于经常修改的字段上不要创建索引；
5. 经常用于多表join的字段上建立索引；
6. 经常用在where子句中的字段建立索引；
7. 对于一些大字段不要建立索引，比如text、blob等；
8. 一张表最多不要建立超过5个索引；

最左匹配？

当我们建好索引后，sql写的不好，也可能用不到索引。比如下面这张book表：

这张表有三个字段，假如我们在name字段上创建了一个普通索引，那下面这个sql就会使用到这个索引

select * from book where name like '三%';

但是如果把 三% 改成 %三，那索引就会派不上用场了，像这样：

select * from book where name like '%三';

这就是索引的最左匹配原则，也就是最左边优先，比如我建了下面这样的联合索引：

CREATE INDEX idx_name_author ON book(name,author);

(name,author)这样的联合索引相当于建了两个索引，一个(name)，一个(name,author)；查询条件的顺序最好是和联合索引的顺序一致。

查看是否使用到索引，可以使用Explain查看执行计划，只要在查询语句前面加上explain即可，查询出来的字段意思如下：

字段	描述
id	选择标识符
select_type	表示查询的类型。
table	输出结果集的表
partitions	匹配的分区
type	表的连接类型
possible_keys	查询时，可能使用的索引
key	实际使用的索引
key_len	索引字段的长度
ref	列与索引的比较
rows	扫描出的行数(估算的行数)
filtered	按表条件过滤的行百分比
Extra	执行情况的描述和说明

回表？

上面说到索引分为聚集索引和非聚集索引。那这两种在数据存储上有什么区别吗？

首先说聚集索引，就拿主键索引来说，它是一颗索引树，所有的数据都存在叶子节点中，当我们查询数据，根据id查询时，就会搜索这颗索引树，到达叶子节点时就可以直接获取到要查的那行数据。

再说非聚集索引，它也是一个索引树，但是它的叶子节点存储的不是行数据，而是对应的id，拿到id以后，再去查询主键索引树，最后拿到真实数据。看到没有，如果是非聚集索引，我们查询会查询两次，也就是回表。

那有什么办法可以避免回表吗，毕竟查询两次还是挺浪费效率的。有时候我们可以建立覆盖索引，也就是联合索引来避免回表。还以上面的book表为例，如果我要查询“三体1”的作者，那sql应该是这样：

select author from book where name = '三体1';

如果我们只在name字段上建立了索引，那这个查询就需要回表，因为叶子节点上指存储了id的值。这个时候，我们可以在(name,author)这两个字段上建立联合索引，这样叶子节点上不仅有id值，还会有author的值，再次查询时就不需要回表了。

B+树

上面的索引都是以B+树为例子的，其实索引的数据结构也有很多种，但是B+树是最常见的，也是面试时问的最多的。那我们最后来聊聊啥是B+树？

在说B+树之前，我们先来看看其他几种树。

首先是二叉查找树，这种最常见，常用于查找数据，但是有时候数据有序的时候，插入二叉树就会导致树很不平衡，甚至变成一个链表，这种查找效率肯定会大打折扣。

接着是平衡树（AVL树），它是二叉查找树的升级版，自带平衡，这种由于维护平衡影响效率太大，因此应用的地方不多。

然后就是红黑树，这种在前面讲解HashMap的文章中提到过，这里不再赘述。

最后就是B树和B+树，它们和上面的树最大区别在于，它们一个节点可能有多个子节点。B树的所有节点都可以存储真实数据，这就会导致非叶子存储子节点的指针较少，从而树的层级会变高，看起来又高又瘦；而B+树的真实数据都存储在叶子节点上，而且所有叶子节点形成了一个链表，这就很适合范围查找，且非叶子节点只存储子树的指针，这样树的层级会变矮，看起来又胖又矮；相对于B树，B+树的矮更适合文件系统，因为层级变少，IO次数也会变少，效率也会变高，这就是为啥索引很多的数据结构都是B+树的原因。

---

扫一扫，关注我