SQL索引结构、使用、优化-深入浅出—看不懂你来锤鹅

派大星的无情铁锤

已于 2024-01-13 09:48:58 修改

阅读量2k

点赞数 3

分类专栏： Oracle 数据库文章标签：数据库搜索引擎 mysql 面试 oracle

于 2023-08-14 23:30:00 首次发布

本文链接：https://blog.csdn.net/weixin_44430514/article/details/132218390

版权

Oracle 同时被 2 个专栏收录

4 篇文章 2 订阅

订阅专栏

数据库

2 篇文章 0 订阅

订阅专栏

前言

在日常工作中会经常接触到。比如某一个 SQL 查询比较慢，分析完SQL得语句结构之后，可能就会说“给作为条件的这些字段加个索引吧”之类的解决方案。但到底什么是索引，怎么加索引才会保证SQL就会走索引，索引又是如何工作的，也不会去多想，就算不走索引，也懒得管，反正加了，这次就温故一下这些话题。

什么是索引？

在开始学习索引就知道了，就类似于书的目录，比如小明要找第六章的内容，小明不知道在第几页，那小明直接找书的目录就能一目了然看到在第几页，然后直接翻到页数即可。

这里我们就是对书的内容做了索引，索引是目录中的第六章，小明就是数据库的执行器兼优化器；到这儿，索引是什么就说完了，道理很简单。

为什么索引失效？

还是上面书的例子，让SQL走索引，就是让文中的小明去找书的目录。

那么，为什么有时候加了索引但是SQL语句就是不走索引呢？在这里就是小明为什么就是不去找书的目录？

1、查询条件中有or

让小明去找第六章或者第四章的内容，但是目录中只有第六章的索引，这时候索引就失效了，因为小明不知道怎么通过索引找到两个章节内容。

这里也举个SQL例子：这里只对six做了索引，four没有索引

SELECT * FROM `Book` where six ='5' or four= '5';

这时，如果你对six和four都做索引，他就会一个一个按照索引查出来！

2、模糊查询以%开头

这时候你让小明去找第六章%五节的内容，我们知道第六章是有索引的，小明这时找的是第六章但是他不知道是第五节还是第五十五节，这时候小明懵了，直接全书开始找，所以索引失效了！

Select * From Book Where six like '%5';

3、用数字与查询字符串

这时你又闲了，想让小明去找一下书的第六章的第5节。

这时候你要注意了，我们的索引是对第六章第五节，这里的节数是字符串，就是说第六章里面只有汉字第一二三四五节，这就是上面我的SQL为什么都要带上引号了。（这里我把错误的索引和正确的索引颜色用的很接近，就是平时这种小问题会容易忽视）

小明是个小笨蛋他哪里知道第五节和第5节的区别，就当没看见，这时候他又懵了，直接全书开始找，所以索引失效了！

Select * From Book Where six = 5;

但是如果你是字符串查询数字，这是会走索引的，这应该是数据库的隐式转换！

4、在索引列上进行运算

吃完饭了，没事干，叫小明过来，找一下第六章的第四节内容，但是你非要让小明先找第五节，然后再减一找到第四节，这不是脱裤子放屁的操作么。小明就收到一个“第六章第五节-1”这么一个命令，小明左手六右手七，左脚画圈右脚踢，是的，他又懵了，想着哪里有这样的节呀！又懵了，直接全书开始找，所以索引失效了！

Select * From Book Where six-1 = '4';

那如果你非要对索引列进行计算，那就创建函数索引来用。

5、违背了最左匹配

这时候我先在书的目录中新增一个字段叫做关键词，对章节和关键词做一个复合索引；比如：“第六章第五节，关键词为神话”，下面这样的索引。

Select * From Book Where six = '5' and keywords = '神话'

你散步回来了，看到小明还在左脚画圈，右脚踢，你就让小明去找一下关键词为神话的内容，哇靠，差点没把小明cpu烧了，他只知道先找到第六章第五节再去找关键词为神话，你让他直接找关键词为神话的内容，他办不到，他又懵了，直接全书开始找，所以索引失效了！

Select * From Book Where keywords = '神话';

6、走索引还不如直接找

这句话意思就是我去找目录不如直接翻开书从头开始找来的快。

为什么会出现这二种情况？原因有以下几点：

查询的数量是大表的大部分，比如要查到的数据占全表的30％以上；
对小表查询，比如，表里就两条数据，数据库的优化器就出手了，直接选择全表扫描；
字段里面的值太过简单，比如，表里虽然有20万条数据，但是索引列中的值无非就是1，2这两种，就算你只查这个字段，他也不走索引；

7、一些其他的原因

索引本身失效；
使用<>；
使用not in /not exist；
当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况；
B-tree索引 is null不会走,is not null会走；
看到这里，相信已经得到了很多情况的索引失效的原因，如果你还是对索引的底层运行逻辑感兴趣那就继续往下瞅瞅。

索引的遍历逻辑

说到索引的遍历逻辑，不能不提到索引的数据结构，不同结构的索引，遍历逻辑也不一样。

1、索引数据结构

1、B+Tree

这里先说使用最多的B+Tree索引，这个索引结构就是一个N叉树形结构。

N叉树是二叉树的一种增强树形结构，二叉树中红黑树是一种平衡二叉树，遍历速度很快，但是如果数据很多，则树的层级就很深，则需要更多的IO次数，对于数据库来说很消耗资源，所以N叉树就是一个非叶子结点上有N和子节点，这个数量可以达到1200个，如果层级达到3层，则叶子节点可以存储17亿多的数据，这可以满足99%的数据表存储要求了。

B+树和B-树在树形结构是很相似的，它们的非叶子节点存储数据结构不一样，B+树的非叶子节点上只存储索引指针数据，不会存储实际数据，但是B-树的非叶子节点会存储实际数据。

B+树结构图如下：

从上面可以看出，B+树的数据结构。图中还可以看出B+树和B-树的区别还有一点就是叶子节点，虽然都存储实际数据，但是B+树的实际数据会形成一个有序链表，这样的话，查找遍历速度会更快。这里先不说具体的遍历顺序了，后面说。

2、B-Tree

这个索引类型和B+Tree类型很相似，可以看上一节B+Tree来学习B-Tree。这里不细说了

3、哈希索引

哈希表是一种以键 – 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

假设，你现在维护着一个身份证信息和姓名的表，需要根据身份证号查找对应的名字，这时对应的哈希索引的示意图如下所示：

图中，User2 和 User1 根据身份证号算出来的值都是 4，但没关系，后面还跟了一个链表。假设，这时候你要查 ID_Card_1 对应的名字是什么，处理步骤就是：首先，将 ID_Card_1 通过哈希函数算出 4；然后，按顺序遍历，找到 User1。

需要注意的是，图中三个 ID_Card 的值并不是递增的，这样做的好处是增加新的 User 时速度会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。

你可以设想下，如果你现在要找身份证号在[ID_Card_X,ID_Card_Y]这个区间的所有用户，就必须全部扫描一遍了。

所以，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。

2、B+Tree和B-Tree的遍历顺序

1、B-Tree

B树的等值查找，从根节点开始，根据键值的比较结果，沿着合适的分支向下遍历。在每个节点中，都会根据键值的大小判断应该继续向左子节点还是右子节点遍历，直到找到匹配的键值或者遍历到叶子节点为止。也就是B树可能不会遍历到叶子结点就找到了这个值。

B树的范围查找，也是从根节点开始，根据范围的上下界进行比较，选择适当的分支向下遍历。需要注意的是，B树在找到下界值后，会回到自己的父节点重新找下一个值，所以B树的范围查找可能需要在每个节点都进行遍历，因为不同的子树可能涵盖了不同的范围。

2、B+Tree

B+数的等值查找，同样从根节点开始，根据键值的比较结果，向下遍历子节点，直到达到叶子节点。B+树的叶子节点存储了所有的数据，因此等值查找在叶子节点上结束。

B+数的范围查找更为高效，由于叶子节点之间通过链表连接，从一个叶子节点可以直接跳到下一个叶子节点，以快速实现范围查找。在进行范围查找时，只需要找到下界的起始叶子节点开始，顺着链表依次遍历叶子节点，直到达到范围的上界。

3、主键索引、二级索引

1、主键索引

主键索引为每张表中对主键形成的一个唯一索引，也叫聚簇索引，它的叶子结点中存储的数据是表中整行数据，结构图如下：

2、二级索引

二级索引是普通索引的一个叫法，它的叶子结点存储的是主键值，结构图如下：

3、回表

回表操作就是发生在二级索引往主键索引之间跳转的一个操作，看图：

如果我现在要通过二级索引值为3，去找到这一整行数据，那么我就必须走二级索引先找到主键的值是16，再回到主键索引找到值为16的主键，所保存的整行数据。这个操作就叫做回表。

4、区别

所以在上面所说的看来，如果我要找R5整行数据，可以有以下两种方式：

Select * From Table Where PrimaryKey = '20';
Select * From Table Where k = '6';

第一种方式，我直接查询主键索引就找到了R5，但是第二种方式，就先走二级，再走主键，会多遍历一颗索引树，会降低效率。所以查询中多使用主键索引。

索引优化

1、覆盖索引

如果我现在要执行一个SQL：Select PrimaryKey From Where k = '6'；那么对于我上面所建立的索引来说，就不用去在进行回表操作了，因为我的二级索引就满足了我的查询需求，这个操作就叫做覆盖索引。

基于上面覆盖索引的说明，我们来讨论一个问题：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？

假设这个市民表的定义是这样的：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

我现在有个需求就是根据身份证号查询市民的姓名，

Select name From tuser Where id_card = ‘A’；

那我现在对身份证号建立索引的话，首先用二级索引找到身份证号上叶子结点保存的id，再进行回表，才能找到name；

但是我新增一个索引，以身份证和姓名作为索引，下图为例，那么现在我只用根据二级索引找到身份证号上叶子结点保存的name即可，这就不再需要回表查整行记录，减少语句的执行时间。

当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这正是业务 DBA工作。

2、最左前缀原则

看到这里你一定有一个疑问，如果为每一种查询都设计一个索引，索引是不是太多了。如果我现在要按照市民的身份证号去查他的家庭地址呢？虽然这个查询需求在业务中出现的概率不高，但总不能让它走全表扫描吧？反过来说，单独为一个不频繁的请求创建一个（身份证号，地址）的索引又感觉有点浪费。应该怎么做呢？

这里，我先和你说结论吧。B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

为了直观地说明这个概念，用这个索引（name，age）为例，

Select * From Where name = ‘张三’ and age = '20'；
Select * From Where name like ‘张%’；

这里可以看到，开头是姓名，后面跟着年龄，如果你先使用年龄作为条件，则不能走这个索引，索引就会失效。

3、索引下推

上一段我们说到满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。这时，你可能要问，那些不符合最左前缀的部分，会怎么样呢？

我们还是以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

Select * From tuer Where name like '张%' and age=10 and ismale=1;

你已经知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录主键3。当然，这还不错，总比全表扫描要好。

然后呢？

当然是判断其他条件是否满足。

在 MySQL 5.6 之前，只能从主键3 开始一个个回表。到主键索引上找出数据行，再对比字段值。而 MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

就是说，我直接在二级索引把两个条件都进行筛选，再去回表主键索引。

这里得出我们在建立二级索引时候，是为了更快地更有效率的对主键索引进行回表，所以二级索引必须可以筛选掉大部分数据才是有意义的，不然只会徒增资源消耗！！

总结

文中分析了数据库引擎可用的数据结构，介绍了 InnoDB 采用的 B+ 树结构，以及为什么 InnoDB 要这么选择。B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。

由于 InnoDB 是索引组织表，一般情况下我会建议你创建一个自增主键，这样非主键索引占用的空间最小。但事无绝对，我也跟你讨论了使用业务逻辑字段做主键的应用场景。

数据库索引的概念，优化包括了覆盖索引、前缀索引、索引下推。你可以看到，在满足语句需求的情况下，尽量少地访问资源是数据库设计的重要原则之一。我们在使用数据库的时候，尤其是在设计表结构时，也要以减少资源消耗作为目标。

如果对索引还想了解的更深一些，建议参考，蔡泽胤, 《MySQL核心原理与性能优化》。

最后我还整理汇总了⼀些 Java ⾯试相关的⾼质量 PDF 资料和免费Idea账号

公众号：Java小白，回复“⾯试” 和“idea破解”即可获取！

派大星的无情铁锤

关注

3
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录