MySQL专题,第四期—深入索引学习

一、背景

数据库查询在数据库学习与以后的应用中是非常重要的技能。上节我们讲了连接查询,接下来我们会讲述如何进行查询优化。查询优化主要是依赖索引完成的,所以本节我们就讲一下索引的一些知识。

二、索引是什么

1.定义:索引简单说就是一种数据结构。是一种排好序的快速查找数据的数据结构。很明显:建立索引的目的是为了提高查找效率。

2.索引主要影响sql的那一部分

索引主要影响sql where子句部分及order by子句部分的执行效率。还记得我上一篇文章讲的mysql真正执行sql的顺序吧。就是为了更好的应用索引,提高查询效率。

3.索引的分类

① 单值索引

单值索引即只有一列的索引。这种索引虽然看似简单,但是我觉得是很常用的,对查询优化也是很有帮助的。

② 唯一索引

索引列的值唯一,但允许有空值。单值索引有可能是唯一索引,即索引列值唯一。复合索引也可以是唯一索引,即索引列的组合唯一。

③ 复合索引

一个索引包含多个列,就是复合索引。复合索引的建立很有讲究,随后我们细谈。

4.mysql的索引结构

mysql中的索引结构主要是以下几种:B+Tree(B+树类型索引)、Hash(hash键值对类型索引)、full-text(全文索引)等。

① 对于Hash键值对索引。我们很好理解:它的索引结构就是key-value型的键值对。Hash索引查询效率很高的。因为它是直接根据key查找到数据值,不用想树结构那样总是从根节点开始查询到叶子节点。但是Hash索引应用场合不大。Hash 索引仅仅能满足"=","IN"和""查询,不能使用范围查询。所以Hash索引应用空间并不大。

② 对于全文索引,在关系型数据库中本身并不建议使用全文索引,大家有兴趣的自行学习,这里不再赘述。

③ 重点是我们的B+Tree类型的索引。这个索引是mysql中常用索引。它的结构实际上是这样的:它是一个多路搜索树,但是并不一定是二叉树。相对于BTree它的主要特点是:非叶子节点不存储真实的数据,只存储指引搜索方向的数据项。

BTree结构,如下图:

我这里已一个简单二叉树为例,可以看到每个节点(无论是叶子节点还是非叶子节点都是[key, data]这样的数据结构)。

B+Tree结构,如下图:

可以看到B+Tree的主要特点就是:非叶子节点不存储真实的数据,只存储指引搜索方向的数据项。

注:我是以二叉树为例,但是BTree与B+Tree不一定就是二叉树。准确的说叫:多路搜索树。因为这涉及到树的详细数据结构,我就不在这里细究了,想要了解的朋友可以自行去学习。

还有要注意的一点:有的mysql中选择索引方法的时候显示是BTree,这里澄清一下。mysql中的BTree指的是B+Tree而不是BTree。关于BTree与B+Tree究竟哪个效率更高,这涉及计算机更底层的一些知识,这里也不再陈述,感兴趣的朋友可以自行学习。

三、什么时候用索引

讲完索引的结构后,那么在什么情况下需要建立索引呢。

以下情况为通常建立索引的情况:

① 主键在数据库中会自动建立索引。

② 频繁作为查询条件的字段。

③ 查询中与其他表关联的字段,即:外键关系时常建立索引。

④ 查询中排序字段建立索引。(因为索引其实就是一种排好序的数据结构,如果排序能用上索引,速度真的是快的不要不要的。)

⑤ 查询中统计或分组字段。

以下为不适合建索引的情况:

① 表记录较少。

② 经常增、删、改的表。(会引起索引的重构,效率比较慢。同时从这一点我们亦可以看出,索引其实影响了增、删、改的性能。所以索引真的不是越多越好,一般一张表建立5个索引即是最多,更多则考虑优化索引。)

③ where条件用不到的字段不适合创建索引。

④ 某些数据列包含旭东重复内容,不适宜建索引。(这里涉及一个索引性能问题:选择建索引的选择性=索引列不同值/总列数。从这个公式看出主键索引即是必须建立的索引,所以数据库会自动给我们建立主键索引)

四、总结

在本文中,主要讲述了一些mysql索引的一些概念与其一些核心概念。主要是比较了BTree与B+Tree的不同,还有什么时候我们需要建立索引。

关于查询的优化,在数据库层面,其实基本上是从索引入手的。有一个好的索引结构,必能更好的优化慢查询问题。

下一节我会带给大家一些性能分析的相关知识,提前透露一下:关于explain关键字的使用,会是索引学习的重头戏哦。赶紧关注我吧。

小编原创不易,只求大家关注关注。拜托拜托。谢谢大家!我们明天见。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180517G0B6XJ00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券