面试官：讲讲mysql表设计要注意啥续！谈谈索引方面面试题

引言

大家好，我是渣渣烟。之前，我撰写了一篇名为《面试官：谈谈在进行MySQL表设计时需要注意哪些事项》的文章。完成之后，感觉反响还不错！

于是，我们决定开设一个新的MySQL数据库专题。在这个专题中，我们将探讨与索引相关的MySQL面试题目。按照惯例，我们将讨论的是在InnoDB存储引擎环境下的情况，因为实际上我并未使用过MyISAM等其他存储引擎。

ps:其实很早就想写了，一直偷懒!

实际上，针对每一个问题，我都能详细阐述成一篇文章。此外，这些问题并非我随意杜撰。正如图中所示（请留意第三题）。

所以我回忆了一下，索引常见考点有哪些，总结成了这篇文章！

主要题目有下面这些

正文

1、你一般怎么建索引的？

烟哥注:曾记得有一个粉丝来找我的时候，出现如下搞笑一幕

渣渣烟质疑道：“你简历上提到具备SQL优化技能，那么你是如何构建索引的呢？”

只见该粉丝嘿嘿一笑..说道:"就那样建啊…"

渣渣烟:"噢(第二声)，就哪样建啊…"

粉丝:"…就网上说的那些索引规则啊"

渣渣烟:"那你怎么知道那些SQL出问题，需要建索引呢？"

粉丝:"我….."

嗯，这道题其实很基础。但是有没有做过，这题是可以看出来的。

去my.cnf里配置三个配置

启用慢查询日志功能，将slow_query_log设置为1，并指定慢查询日志的存储位置为/var/log/mysql/log-slow-queries.log。当SQL语句的执行时间超过3秒时，系统将自动将其记录入日志中，long_query_time参数设置为3。

监控到慢SQL后，就马上开始建索引?

不，不，不……在这种情境下，我们首先应当思考是否能够对SQL语句进行优化处理。

例如，当只要一行数据时使用 limit 1

在查询过程中，若事先知晓只会获取一条数据，此时添加“limit 1”参数能提升查询效率。这是因为MySQL数据库引擎在发现一条符合条件的结果后便会终止搜索，而不会继续查找后续记录是否满足要求直至所有数据被检查完毕。

然而，在多数情形下，处理业务的SQL语句相当繁复，难以进行优化。因此，我们便需要创建索引。在此阶段，请依照以下准则来构建索引。

2、讲讲索引的分类？你知道哪些？

从物理存储角度：聚簇索引和非聚簇索引

从数据结构的角度来看，我们可以探讨B+树索引、哈希索引、全文索引以及R-Tree索引。

从逻辑角度:

3、如何避免回表查询?什么是索引覆盖?

若需深入了解，请查阅《Innodb索引机制详解》一文。

这里简单说一下。

若读取索引即可获取所需信息，便无需从表中直接读取数据。所谓索引覆盖，即一个索引中（或其覆盖范围内）已包含查询所需的所有数据。

例如此时有一张表table1,有一个联合索引(a,b)

执行如下SQL

select a,b from table1

在索引上就能找到结果，就不用回表去查询！

而你执行的是

select a,b,c from table2

c列在索引上不存在，就需要回表查询。

需要特别指出的是，在创建覆盖索引时，必须确保索引列的值被存储，然而，哈希索引、空间索引以及全文索引并不保留索引列的值，因此，MySQL系统中仅支持使用B+树索引来实现覆盖索引功能。

目前我手中有一列数据，这些数据均具备唯一性，我打算为其创建一个索引。在此过程中，我面临选择：是采用唯一索引，还是选择普通索引？

答唯一索引！

首先，在孤尽出的《阿里巴巴JAVA开发规范》中有这么一段话

业务字段若具备独特性，即便是由多个字段构成，也务必设立唯一索引。需注意，尽管唯一索引可能对插入操作的速度有所影响，但这种速度的损失是可以忽略不计的；同时，即便在应用层面实施了严格的校验与控制，若缺乏唯一索引，依据墨菲定律，脏数据的产生几乎是必然的。

那好，下一问出现了！

为何建立唯一索引时的数据录入速度不如采用常规索引那样迅速？同时，为何在执行查询操作时，使用唯一索引的检索效率却高于普通索引？

这个问题需从Insert Buffer的介绍说起，当执行非聚簇索引的插入操作时，首先需确认待插入的索引页是否已存在于内存之中。若已存在，便可直接进行插入；若不存在，则需要先将该页内容存入Insert Buffer，随后按照既定的频率和条件，将Insert Buffer与原始数据页进行合并处理。

此方法的优势在于，它可以将多个插入操作合并为单一步骤，从而显著提升了非聚簇索引的插入效率。

自1.0.x版本起，InnoDB数据库管理系统引入了Change Buffer机制，这实际上是对Insert Buffer功能的一种提升。

自本版本起，InnoDB 存储引擎实现了对 insert、delete、update 操作的缓存功能。

唯一速度的插入比普通索引慢的原因就是:

于是乎下一问又来了！

为什么唯一索引的更新不使用 Change Buffer？

由于唯一索引确保数据的唯一性，故需将数据页调入内存以确认是否违背了唯一性限制。然而，既然数据页已经存入内存，那么直接在内存中对数据页进行更新，就无需再借助Change Buffer这一机制。

最后回答一下，唯一索引的搜索速度比普通索引快的原因就是:

5、mysql索引是什么结构的？用红黑树可以么?

这个妥妥答最常见的B+ Tree。

AVL树与红黑树主要在内存中存储数据时被运用。面对大规模数据的存储需求，显然不可能将所有数据一次性加载至内存。若选用红黑树，将导致频繁的I/O操作，从而效率大打折扣。

那为啥不用B Tree,而选择B+ tree呢？

这就需要贴一下经典的两张图。B tree是长下面这样的

注意一下B tree的两个明显特点

而B+ tree长下面这样的

注意一下B+ tree的两个明显特点

开始编写时，我们会发现数据库索引之所以采用B+树，关键在于B树在提升磁盘I/O性能的同时，并未彻底解决元素遍历效率低下的难题。为了有效解决这一挑战，B+树便应运而生。在B+树中，只需遍历叶子节点，便能完成对整棵树的全面遍历。此外，在数据库中进行基于范围的检索操作相当频繁，若采用B树结构，就必须执行局部中序遍历，甚至可能跨越多个层级，导致效率低下。

提示，我下一问就是:

你方才提到B树存在诸多不足，那么你是否明白为何MongoDB在构建索引时选择B树而非B+树呢？

从探讨关系型数据库与非关系型数据库差异的视角出发，不妨深入思考，在MongoDB数据库中，我们该如何体现这两种数据库之间的联系。

在MySQL中，若某张表创建了多个单一索引，当进行涉及多个条件的查询时，索引的访问路径是怎样的呢？

当我瞥见这道题目时，心中不禁一颤。这道题竟然要求后端开发者来解答，这无疑是对技术功底的一次严峻考验！

这道题目旨在测试考生对优化器理解的程度！它主要考察的是优化器在索引选择上的决策过程！优化器会进行评估，以确定哪个索引最为理想，随后进行执行。

MySQL的优化器中包含一个名为Range优化器的组件，该组件主要负责对范围查询进行优化处理。

该优化器在计算执行成本方面，主要采用了两种不同的方法，分别是索引深入查询和索引统计信息。

这些方法代表了MySQL优化器对操作成本进行的评估，其中一种方法虽然计算速度较慢，但能够提供非常精确的结果；而另一种方法则计算速度较快，但所得数据可能并不完全准确。

坦白讲，当我写到此处，心中不禁泪如泉涌，要将index dive与index statistics阐释透彻，实属不易，在此仅能略作阐述。

对于index dive:

计算成本的方式为

COST = CPU COST + IO COST

CPU COST代表的是处理记录返回时所需付出的成本。同时，IO COST则是指读取页面所耗费的资源。

MySQL会针对每一种索引的运行效果，执行上述的成本估算，最终选择成本最低的执行方式。

然而，在特定情境中，MySQL执行索引跳跃的代价相当高昂。于是，优化器便会转而采用基于索引统计数据的成本估算方法。

具体如下：

展示索引信息，针对表名为tbl_name的表格，[如果需要，可指定数据库名为db_name]。

此刻输出的结果中，存在一列被命名为Cardinality，其数值代表索引列中不同值的数量。

简言之，这指的是索引列中不同值的数量，若为复合索引，则是指这些值组合的独特数目。

该数值将成为MySQL优化器在评估语句执行策略时的参考依据。若其唯一性指数偏低，优化器将判定该索引对语句的优化作用有限，从而可能选择不使用索引。

Cardinality数值越高，这表明，通过索引我们可以筛选掉更多的数据，从而使得执行过程更加高效。