自学内容网 自学内容网

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)


原文地址:https://blog.csdn.net/qq_45929019/article/details/140605709

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!