MySQL面试索引篇

1、什么是索引？

作为一个数据库，首要任务就是把数据存储好，并快速查询出用户需要的数据，而索引就相当于图书的目录一样，是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。

2、为什么要使用索引？

索引能帮助数据库高效获取数据的数据结构，提高数据查询的效率！

3、什么是B树？

B-树，也称为B树，是一种平衡的多叉树（可以对比一下平衡二叉查找树），它比较适用于对外查找。一颗m阶（阶数：一个节点最多有多少个孩子节点）的B树，有以下特征：

1. 根结点至少有两个子女；

2. 每个非根节点所包含的关键字个数 j 满足：⌈m/2⌉ - 1 <= j <= m - 1.(⌈⌉表示向上取整)

3. 有k个关键字(关键字按递增次序排列)的非叶结点恰好有k+1个孩子。

4. 所有的叶子结点都位于同一层

一颗简单的B树

4、什么是B+树？

B+树是B-树的变体，也是一颗多路搜索树。一棵m阶的B+树主要有这些特点：

1. 每个结点至多有m个子女;

2. 非根节点关键值个数范围：⌈m/2⌉ - 1 <= k <= m-1

3. 相邻叶子节点是通过指针连起来的，并且是关键字大小排序的。

一颗3阶的B+树

5、B树和B+树的区别？

1. B-树内部节点是保存数据的;而B+树内部节点是不保存数据的，只作索引作用，它的叶子节点才保存数据。

2. B+树相邻的叶子节点之间是通过链表指针连起来的，B-树却不是。

3. 查找过程中，B-树在找到具体的数值以后就结束，而B+树则需要通过索引找到叶子结点中的数据才结束

4. B-树中任何一个关键字出现且只出现在一个结点中，而B+树可以出现多次。

6、为什么索引结构默认使用B+树，而不是B树，Hash，二叉树，红黑树？

• Hash哈希，只适合等值查询，不适合范围查询。

• 一般二叉树，可能会特殊化为一个链表，相当于全表扫描。

• 红黑树，是一种特化的平衡二叉树，MySQL 数据量很大的时候，索引的体积也会很大，内存放不下的而从磁盘读取，树的层次太高的话，读取磁盘的次数就多了。

• B-Tree，叶子节点和非叶子节点都保存数据，相同的数据量，B+树更矮壮，也是就说，相同的数据量，B+树数据结构，查询磁盘的次数会更少。

7、为什么MySQL使用的B+树索引？

MySQL实现的B+树索引结构

MySQL实现的B+树简单好用，稳定可靠！

8、正确使用索引的一些建议？

1. 选择合适的字段创建索引

   • 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
   • 被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
   • 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
   • 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。
   • 不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。

2. 被频繁更新的字段应该慎重建立索引

   •  虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了

3. 尽可能的考虑建立联合索引而不是单列索引

   • 因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4. 注意避免冗余索引

   • 冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

   5. 字符串类型的自动使用前缀索引代替普通索引

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引

9、索引失效的场景？

1. 在联合索引场景下，查询条件不满足最左匹配原则！
2. 使用了select *；

【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。说明:1)增加查询分析器解析成本。2)增减字段容易与 resultMap 配置不一致。3)无用字段增加网络 消耗，尤其是 text 类型的字段。

        3、索引列参与运算；

        4、索引列使用了函数；

        5、错误的like使用，匹配占位符位于条件的首部；

        6、参数类型与字段类型不匹配，导致类型发生了隐式转换，索引失效；

        7、使用or操作，查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效；or两边为“>”和“<”范围查询时，索引失效；

        8、两列数据做比较，即便两列都创建了索引，索引也会失效；

        9、查询条件使用不等进行比较时，需要慎重，普通索引会查询结果集占比较大时索引会失效；

        10、查询条件使用is null时正常走索引，使用is not null时，不走索引；

        11、查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效；

        12、当查询条件涉及到order by、limit等条件时，是否走索引情况比较复杂，而且与Mysql版本有关，通常普通索引，如果未使用limit，则不会走索引。order by多个索引字段时，可能不会走索引。其他情况，建议在使用时进行expain验证

10、知道如何分析语句是否走索引查询

我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 ，这样就知道语句是否命中索引了。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。

根据type列判断：表的访问方法

EXPLAIN 的输出格式如下：

mysql> EXPLAIN SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | cus_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997572 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)