目录
前言
在日常工作中会经常接触到。比如某一个 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破解”即可获取！
1936
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
