风也温柔

计算机科学知识库

数据库索引 数据结构 如何优雅地回答面试官关于MySQL索引的拷问

  在面试时,面试官一般不会让你直接描述查询索引的过程,但是会通过考察你对索引优化方法的理解,来评估你对索引原理的掌握程度,比如为什么 MySQL 选择 B+Tree 作为默认的索引数据结构?MySQL 常见的优化索引的方法有哪些?

  所以接下来,我们就详细了解一下在面试中如何回答索引优化的问题。

  B+Tree 索引的优势

  如果你被问到“为什么 MySQL 会选择 B+Tree 当索引数据结构?”其实在考察你两个方面:B+Tree 的索引原理;B+Tree 索引相比于其他索引类型的优势。

  我们刚刚已经讲了 B+Tree 的索引原理,现在就来回答一下 B+Tree 相比于其他常见索引结构,如 B 树、二叉树或 Hash 索引结构的优势在哪儿?

  B+Tree 相对于 B 树 索引结构的优势

  B+Tree 只在叶子节点存储数据,而 B 树 的非叶子节点也要存储数据,所以 B+Tree 的单个节点的数据量更小,在相同的磁盘 I/O 次数下,就能查询更多的节点。

  另外,B+Tree 叶子节点采用的是双链表连接,适合 MySQL 中常见的基于范围的顺序查找,而 B 树无法做到这一点。

  B+Tree 相对于二叉树索引结构的优势对于有 N 个叶子节点的 B+Tree,其搜索复杂度为O(logdN),其中 d 表示节点允许的最大子节点个数为 d 个。

  在实际的应用当中, d 值是大于100的,这样就保证了,即使数据达到千万级别时,B+Tree 的高度依然维持在 3~4 层左右,也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据(这里的查询参考上面 B+Tree 的聚簇索引的查询过程)。

  而二叉树的每个父节点的儿子节点个数只能是 2 个,意味着其搜索复杂度为 O(logN),这已经比 B+Tree 高出不少,因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

  B+Tree 相对于 Hash 表存储结构的优势

  我们知道范围查询是 MySQL 中常见的场景,但是 Hash 表不适合做范围查询,它更适合做等值的查询,这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

  至此,你就知道“为什么 MySQL 会选择 B+Tree 来做索引”了。在回答时,你要着眼于 B+Tree 的优势,然后再引入索引原理的查询过程(掌握这些知识点,这个问题其实比较容易回答)。

  接下来,我们进入下一个问题:在实际工作中如何查看索引的执行计划。

  通过执行计划查看索引使用详情 我这里有一张存储商品信息的演示表 :

  <pre>CREATE TABLE product  (   id int(11) NOT NULL,   product_no varchar(20)  DEFAULT NULL,   name varchar(255) DEFAULT NULL,   price decimal(10, 2) DEFAULT NULL,   PRIMARY KEY (id) USING BTREE,   KEY 'index_name' ('name').   KEY 'index_id_name' ('id', 'name') ) CHARACTER SET = utf8 COLLATE = utf8_general_ci </pre>

  表中包含了主键索引、name 字段上的普通索引,以及 id 和 name 两个字段的联合索引。现在我们来看一条简单查询语句的执行计划:

  执行计划

  对于执行计划,参数有 字段表示可能用到的索引,key 字段表示实际用的索引, 表示索引的长度,rows 表示扫描的数据行数。

  这其中需要你重点关注 type 字段, 表示数据扫描类型,也就是描述了找到所需数据时使用的扫描方式是什么数据库索引 数据结构,常见扫描类型的执行效率从低到高的顺序为(考虑到查询效率问题,全表扫描和全索引扫描要尽量避免):

  ALL(全表扫描);

  index(全索引扫描);

  range(索引范围扫描);

  ref(非唯一索引扫描);

  (唯一索引扫描);

  const(结果只有一条的主键或唯一索引扫描)。

  总的来说,执行计划是研发工程师分析索引详情必会的技能(很多大厂公司招聘 JD 上写着“SQL 语句调优” ),所以你在面试时也要知道执行计划核心参数的含义,如 type。在回答时,也要以重点参数为切入点,再扩展到其他参数,然后再说自己是怎么做 SQL 优化工作的。

  索引失效的常见情况

  在工作中,我们经常会碰到 SQL 语句不适用已有索引的情况,来看一个索引失效的例子:

  这条带有 like 查询的 SQL 语句,没有用到 表中的 索引。

  我们结合普通索引的 B+Tree 结构看一下索引失效的原因:当 MySQL 优化器根据 name like ‘%路由器’ 这个条件,到索引 的 B+Tree 结构上进行查询评估时,发现当前节点的左右子节点上的值都有可能符合 '%路由器' 这个条件,于是优化器判定当前索引需要扫描整个索引,并且还要回表查询数据库索引 数据结构 如何优雅地回答面试官关于MySQL索引的拷问,不如直接全表扫描。

  当然,还有其他类似的索引失效的情况:

  索引列上做了计算、函数、类型转换操作,这些情况下索引失效是因为查询过程需要扫描整个索引并回表,代价高于直接全表扫描;

  like 匹配使用了前缀匹配符 '%abc';

  字符串不加引号导致类型转换;

  我给你的建议是, 如果 MySQL 查询优化器预估走索引的代价比全表扫描的代价还要大,则不走对应的索引,直接全表扫描,如果走索引比全表扫描代价小,则使用索引。

  常见优化索引的方法前缀索引优化

  前缀索引就是用某个字段中,字符串的前几个字符建立索引,比如我们可以在订单表上对商品名称字段的前 5 个字符建立索引。使用前缀索引是为了减小索引字段大小数据库索引 数据结构,可以增加一个索引页中存储的索引值,有效提高索引的查询速度。在一些大字符串的字段作为索引时,使用前缀索引可以帮助我们减小索引项的大小。

  但是,前缀索引有一定的局限性,例如 order by 就无法使用前缀索引,无法把前缀索引用作覆盖索引。

  覆盖索引优化覆盖索引是指 SQL 中 query 的所有字段,在索引 B+tree 的叶子节点上都能找得到的那些索引,从辅助索引中查询得到记录,而不需要通过聚簇索引查询获得。

  假设我们只需要查询商品的名称、价格,有什么方式可以避免回表呢?

  我们可以建立一个组合索引,即商品ID、名称、价格作为一个组合索引。如果索引中存在这些数据,查询将不会再次检索主键索引,从而避免回表。所以,使用覆盖索引的好处很明显,即不需要查询出包含整行记录的所有信息,也就减少了大量的 I/O 操作。

  联合索引联合索引时,存在最左匹配原则,也就是按照最左优先的方式进行索引的匹配。

  比如联合索引 (, ),如果查询条件是 WHERE =1 AND =2,就可以匹配上联合索引;或者查询条件是 WHERE =1,也能匹配上联合索引,但是如果查询条件是 WHERE =2,就无法匹配上联合索引。

  另外,建立联合索引时的字段顺序,对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高,实际开发工作中建立联合索引时,要把区分度大的字段排在前面,这样区分度大的字段越有可能被更多的 SQL 使用到。

  区分度就是某个字段 不同值的个数除以表的总行数,比如性别的区分度就很小,不适合建立索引或不适合排在联合索引列的靠前的位置,而 uuid 这类字段就比较适合做索引或排在联合索引列的靠前的位置。

  总结

  主要讲了 MySQL 的索引原理,介绍了 为什么会采用 B+Tree 结构。因为 B+Tree 能够减少单次查询的磁盘访问次数,做到查询效率最大化。另外,我们还讲了如何查看 SQL 的执行计划,从而找到索引失效的问题,并有针对性的做索引优化。

  java创建solr索引库_数据库索引 数据结构_百度索引量 暂无数据

  文章来源:https://www.51cto.com/article/651669.html