专栏首页后端mysql 系列:搞定索引
原创

mysql 系列:搞定索引

摘要

索引是数据库里重要的组成部分,也是提高查询效率必备的知识点。本文将会介绍索引作用索引类型索引优化以及索引底层结构,也算是对索引知识的一次归纳

一、索引介绍

什么是索引?

数据库是用来存储与读取数据的,如何在这庞大的数据中查询我们想要的那一行呢?最简单的办法便是扫描整个数据表,一一对比。然而这样效率太低了。

如果我们有类似字典的功能,在查询某行数据前,先到字典里定位到行位置,再根据行位置找到具体数据,是否能更快呢?是的,索引就是这么设计的。

一般的,我们往表里插入某一行数据时,总会有额外的信息来定位到这一行。这个信息可能是一个指针地址,也可能是一个主键标识

在拿到这一行的定位信息后,就可以将列数据和定位信息做关联了。下次想查找这个字段列所对应的行数据时,就可以先到关联信息里搜索,拿到定位信息后直接查找即可。这就是索引,存储了列和定位信息,这定位信息也可以理解指向数据记录的引用指针

需要注意的是,索引是由存储引擎这个模块来实现的,不同的存储引擎有不同的实现方式。像 innodb 的主键就包含了行数据,找到了主键,也就找到了数据。

索引的分类

在数据库里,索引有好多种。我们可以从下面几方面来分类归纳。

从数据结构划分:B+ 树、hash 索引、全文索引

从物理结构划分:聚集索引、非聚集索引

从逻辑用户划分:主键、唯一索引、复合索引、普通单列索引

其中, B+ 树、 hash 索引、全文索引将会在后面具体介绍其底层结构,我们来看看其他的索引:

聚集索引:该索引除了存储索引信息还存储了行数据,像刚刚提到的主键就是。找到它也就意味找到数据了。并且它的排序直接对应了物理存储顺序。

非聚集索引:该索引除了存储索引信息还存储了定位到数据记录的信息,需要根据这个信息再做一次查询,才能获取到数据,并且它的排序是逻辑上,不是物理存储顺序。

主键:唯一地标识表中一条记录的索引,不能有 NULL 值。在 InnoDB 里,主键就是聚集索引。

唯一索引:索引所对应的列值里是不能有重复值的,允许有 NULL 值。像刚刚提到的主键是不允许有 NULL 值的。

复合索引:有多列组合在一起的索引,但只能按最左原则查找,即第一列字段才能被索引查找,后面只是作为附带信息存放着。主要是为了找到索引后,不需要再去行数据里捞数据,直接从索引里提取字段信息即可。

普通单列索引:没有什么限制条件的索引列。

索引的缺点

引入索引,并不总意味着高效,它是需要付出代价维护的。每当有数据需要添加更新时,都得更新对应的索引,这是额外的性能开销,甚至有可能有出现死锁。

另外,索引是需要占用磁盘空间的,不能无限制的添加索引,要有针对性的建索引。

二、索引的使用

使用原则

索引之所以那么快,是因为我们将平时查询频率较高的字段单独维护了起来。当我们有多个查询选项,多个查询条件就不一定能发挥作用了,所以索引的使用是有注意事项的,下面总结了一些:

  • where 里最经常用到的查询字段才建索引,能利用主键 id,就用主键 id 来增删改查
  • 按最左匹配原则,将多个单列索引改为复合索引,减少维护量
  • 尽量挑选择度高,也就是重复率低的列作为索引,像性别这种列就不适合了,会在 B+ 树里做多层次多范围的搜索,还不如全表扫描呢
  • 查找时,不对索引列做函数计算,否则不能使用到索引
  • 查询条件尽量用 union 来取代 or
  • like 用法: ‘列%’ 这样还是可以用到索引的,'%列%' 就不行了
  • IS NULL,IS NOT NULL 是用不到索引的
  • 在 order by,group by 里尽量使用索引字段
  • join 的 on 条件里尽量使用索引字段

性能分析

当我们使用了索引后,又如何知道它有没有使用到索引呢?我们可以借助执行计划来分析,执行计划是 mysql 根据我们的查询语句进行一系列的分析后得到的优化方案。我们可以通过执行计划来获取执行过程。

执行计划的获取:

explain select 语句

执行计划

涉及的字段含义如下:

  • id: 该 SELECT 标识符
  • select_type: 该 SELECT 类型
  • table: 输出行的表
  • partitions: 匹配的分区
  • type: 联接类型
  • possible_keys: 可供选择的可能索引
  • key: 实际选择的索引
  • key_len: 所选密钥的长度
  • ref: 与索引比较的列
  • rows: 估计要检查的行数
  • filtered: 按表条件过滤的行百分比
  • Extra: 附加信息

其中,有个 type 字段,它的含义大概如下:

  • eq_ref: 使用到了 UNIQUE 或 PRIMARY KEY 索引
  • ref: 显示索引的哪一列被使用了
  • ref_or_null: 对 Null 进行了索引优化
  • range: 索引范围检索
  • index: 索引扫描
  • unique_subquery: 使用了 in 子查询,里面涉及了主键字段
  • index_subquery: 使用了 in 子查询,里面涉及了非唯一索引
  • fulltext: 全文索引
  • all: 全表扫描数据

从上面大概就能分析出索引的使用情况了,如果是 all,那就是没有用到索引了。

索引的的底层

前面提到过索引的种类时,细分了 B+ 树、hash 索引、全文索引这三类。现在我们来具体看下对应的底层结构吧。

B 树

在 B+ 树之前还有二叉搜索树B 树,我们来一步一步演化,看看有什么不同,先来看二叉搜索树

二叉搜索树

当要进行查找时,会按小于往左搜索,大于往右搜索的规则去寻找。二叉搜索树只存了单个节点值,树的高度有可能会很高,如果用来存储索引数据,效率将会降低,不适用于 mysql 的索引,我们来看看 B 树吧:

B 树

一个节点可以存储多个数据值。当然,在插入删除时需要做对应的拆分或合并动作。

而且 B 树允许在非叶子节点也存储具体数据,这意味着在扫描搜索时也会将数据加载进来,这无疑增加了磁盘 IO。

对于磁盘 IO 要求高的 mysql 而言,B 树也很不划算,所以 B+ 树成了最好的选择,它长这样的:

B+ 树

B+ 树只在叶子节点存储具体的数据(注:数据可以是真正的行数据也可以是定位到行数据的指针地址),而非叶子节点值只存放索引数据,这样可以降低磁盘 IO,还能充分利用磁盘的预读功能,批量的加载索引数据。

hash 索引

hash 索引

hash 索引将列通过 hash 运算得到 hash code,然后将 hash code 跟数据行的指针地址关联在一起,下次查找时只需查找对应 hash code 的数据行地址即可。

hash 索引非常的紧凑,查找速度很快,适用于内存存储引擎的应用。不过它只能精确查询,不支持范围查找,也不能直接进行排序。限制还是挺多的。

全文索引

全文索引主要是用于文档查找,像我们可能会从多篇文章中查找包含某些词语的文章,这时就可以使用全文索引了。虽然 like 也可以使用,但是效率太低了。

全文索引在接收到文档时,会对它进行分词处理,以获取到关键词。然后会将关键词和属于这个文档的 id 关联起来。

下次查找,就会先到关键词列表里找到关联的文档 id ,最后利用文档 id 去查找到文档数据。

总结

索引所涉及的知识点还是挺多的,从了解索引用好索引再到优化索引,我想这应该是我们进行查询优化的必经之路吧。希望本文能为大家带来不一样的认识,也欢迎一起探讨!


感兴趣的朋友可以搜一搜公众号「 阅新技术 」,关注更多的推送文章。

可以的话,就顺便点个赞、留个言、分享下,感谢各位支持!

阅新技术,阅读更多的新知识。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

关注作者,阅读全部精彩内容

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL索引优化系列(1)—索引介绍

    MySQL官方对索引的定义为:索引(Index)是帮助MySQL高校获取数据的数据结构。

    公众号 IT老哥
  • MySQL调优系列——MySQL B+Tree索引和Hash索引的区别?

    https://blog.csdn.net/n88Lpo/article/details/78099337?depth_1-utm_source=distrib...

    须臾之余
  • 索引系列:2dsphere索引

    MongoDB Manual (Version 4.2)> Indexes > 2dsphere Indexes

    MongoDB中文社区
  • 彻底搞懂MySQL的索引

    MyISAM和InnoDB是MySQL最常用的两个存储引擎,本文将进行详尽的介绍和对比。对于MySQL其余几种存储引擎,请读者自行搜索学习。

    全菜工程师小辉
  • 彻底搞懂MySQL的索引

    MyISAM和InnoDB是MySQL最常用的两个存储引擎,本文将进行详尽的介绍和对比。对于MySQL其余几种存储引擎,请读者自行搜索学习。

    田维常
  • 「Mysql索引原理(四)」单列索引

    例如,select actor_id from actor where actor_id+1=5;

    源码之路
  • 「Mysql索引原理(五)」多列索引

    很多人对多列索引的理解都不够。一个常见的错误就是,为每个列创建独立的索引,或者按照错误的顺序创建多列索引。

    源码之路
  • MySQL 系列教程之(十)索引原理:B+ 树与索引

    优势:可以快速检索,减少I/O次数,加快检索速度;根据索引分组和排序,可以加快分组和排序;

    若尘_
  • MySQL系列 | 索引数据结构大全

    对于二叉树而言,每个节点只能有两个子节点,如果是一颗单边二叉树,查询某个节点的次数与节点所处的高度相同,时间复杂度为 O(n);如果是一颗平衡二叉树,查找效率高...

    Tinywan
  • 五分钟搞懂MySQL索引下推

    如果你在面试中,听到MySQL5.6”、“索引优化” 之类的词语,你就要立马get到,这个问的是“索引下推”。

    三分恶
  • mysql 创建 主键索引 唯一索引 全文索引 多列索引 添加索引

    ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

    bear_fish
  • Mysql系列第二十二讲 mysql索引管理详解

    每个表有且一定会有一个聚集索引,整个表的数据存储在聚集索引中,mysql索引是采用B+树结构保存在文件中,叶子节点存储主键的值以及对应记录的数据,非叶子节点不存...

    易兮科技
  • 玩转Mysql系列 - 第22篇:mysql索引原理详解

    Mysql系列的目标是:通过这个系列从入门到全面掌握一个高级开发所需要的全部技能。

    路人甲Java
  • 玩转Mysql系列 - 第23篇:mysql索引管理详解

    Mysql系列的目标是:通过这个系列从入门到全面掌握一个高级开发所需要的全部技能。

    路人甲Java
  • MongoDB系列四(索引).

    一、索引简介     再来老生常谈一番,什么是索引呢?数据库索引与书籍的索引类似。有了索引就不需要翻整本书,数据库可以直接在索引中查找,在索引中找到条目以后,就...

    JMCui
  • 玩转Mysql系列 - 第21篇:什么是索引?

    路人在搞计算机之前,是负责小区建设规划的,上级领导安排路人负责一个万人小区建设规划,并提了一个要求:可以快速通过户主姓名找到户主的房子;让路人出个好的解决方案。

    路人甲Java
  • 你真的了解索引吗(上)?|mysql 系列(6)

    索引(Index)是帮助数据库系统高效获取数据的数据结构,数据库索引本质上是以增加额外的写操作与用于维护索引数据结构的存储空间为代价的用于提升数据库中数据检索效...

    DougWang
  • mysql快速给列加索引

    1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ...

    用户1220053
  • MySQL调优系列——Mysql如何为表字段添加索引?

    须臾之余

扫码关注云+社区

领取腾讯云代金券