什么是索引?
索引就跟我们的字典目录一样,如果一本字典没有目录,那么你要找一个字,那就相当费劲,得从头一点一点的找;在数据库中也是一样,如果一张表没有索引,那查询一条数据也是相当费劲。
索引是啥样的?
我们都知道,在数据库中的表数据都是一行一行的,但是如果给它加上索引(如果有主键,就会自动生成主键索引),它会变成啥样呢?这里以最常见的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_字段名。
索引虽好,但是也不能随便建,如果建的不好,除了浪费空间外,还会降低性能。所以,如何建索引也是一门学问。
索引创建的原则
上面提到索引也不能随便建,那就需要遵循一些创建原则,下面说一些重要的原则
- 在经常用作过滤器的字段上建立索引,比如订单表的订单号;
- 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引;
- 在值的取值范围较少的字段上不要建立索引,比如性别,只有两种,或者最多三种;
- 对于经常修改的字段上不要创建索引;
- 经常用于多表join的字段上建立索引;
- 经常用在where子句中的字段建立索引;
- 对于一些大字段不要建立索引,比如text、blob等;
- 一张表最多不要建立超过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+树的原因。
<center>扫一扫,关注我</center>
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。