MySQL 面试题

MySQL 的一些面试题，有的是平时聊到的，有的是听别人说的，有的是工作中遇到的，特此汇总一下，有问有答。

一条 SQL 查询语句执行流程？
  1、连接器。客户端和服务器 tcp 握手后，验证用户名密码。用户名密码通过后，连接器会到权限表中查询你拥有的权限。之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。
  2、查询缓存。如果缓存开启，则查询缓存，key 是查询的语句，value 是查询的结果。（如果找到结果直接返回客户端）
  3、分析器。MySQL 首先识别关键字确定你要做什么，然后会对 sql 语法分析，然后将 SQL 解析成 认识的语法，如果有问题，MySQL 会抛出语法错误。
  4、优化器。比如：在表里面有多个索引的时候，决定使用哪个索引。或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
  5、执行器。首先判断对操作表有没有权限，如果有权限就执行调用存储引擎的 api 接口获取数据，如果没有权限就报错。
     参考：
  【MySQL实战45讲】01 一条SQL查询语句是如何执行的（一）

MySQL 有必要开启查询缓存吗？
  1、查询缓存需要每次将查询查询结果以查询语句（key）和查询结果（value）的方式存储起来，这些都是一些资源消耗。
  2、对于操作频繁的表没有必要开启缓存，因为简单的更新操作就会使缓存失效，这样会导致有些缓存还未使用就已经失效了。
  3、如果是一张静态表，很长时间才会更新一次，可以开启缓存，这样缓存的命中率会高一些。
  4、需要注意的是 MySQL 8.0 已经把查询缓存功能放弃了。

查询优化器工作流程？
  一条 sql 有很多条查询方案，在一条 sql 真正执行之前，查询优化器会找出 sql 所有执行的方案进行分析，然后对比找出成本最低的方案，但是成本最低的方案并不是响应时间最短的方案。
    优化过程大致如下：
  1、根据搜索条件，找出所有可能使用的索引。
  2、计算全表扫描的代价。
  3、计算使用不同索引执行查询的代价。
  4、对比各种执行方案的代价，找出成本最低的那一个。

MySQL 常见的几种存储引擎？
  常见的存储引擎有 MyISAM、InnoDB、Memory。

MyISAM 存储引擎和 InnoDB 存储引擎有什么区别？
  1、innodb 在 mysql 5.5.5 版本后是 mysql 的默认存储引擎。
  2、innodb 支持事物，支持行锁，支持外键，支持在线热备份，崩溃恢复也比 MyISAM 要快的多。
  3、myisam 支持表锁，不支持行锁，不支持事物，数据以紧密格式存储，支持压缩表和空间数据索引。

MyISAM 和 InnoDB 存储引擎的数据结构是什么？
  1、两种存储引擎都是 b+tree。
  2、mysql 叶节点存储的是实际数据的地址值，它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。
  3、innodb 叶节点存储的都是实际的数据，这种索引有被称为聚集索引。

什么是索引？
  1、索引是存储引擎用于快速找到记录的一种数据结构。
  2、通俗点讲，相当于一本书的目录部分。

MySQL 有哪几种索引？
  B+Tree、哈希、全文索引、RTree（空间数据索引）

索引的优点？
  1、大大减少了服务器需要扫描的数据行数。
  2、帮助服务器避免进行排序和分组。
  3、以及避免创建临时表（b+tree 索引是有序的，可以用于 order by 和 group by 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
  4、将随机 i/o 变为顺序 i/o（b+tree 索引是有序的，会将相邻的数据都存储在一起）。
  5、是最有效的查询优化手段。

为什么要重建索引？
  1、索引可能因为删除，或者页分裂等原因，导致数据页有空洞。
  2、重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

索引具体采取的那种数据结构？
  1、mysql 常见的索引数据结构有 b+tree 和哈希。
  2、mysql 和 innodb 默认索引结构是 B+Tree。
  3、memory 默认索引结构是哈希。

B+Tree 和哈希的区别吗？
  哈希索引只支持等值比较查询，包括 =、in()、<=>(注意 <> 和 <=> 是不同的操作)。也不支持任何范围查询，例如 where price > 100。因为是一次定位，所以哈希索引查找的速度非常快。由于哈希索引并不是按照索引值顺序存储的，所以无法用于排序和分组。
  b+tree 是由 b+树为存储结构实现的。b+tree 索引是从根节点到枝节点，最后才能到叶节点这样多次 i/o 访问，所以速度要慢于哈希索引。因为 b+tree 是按照顺序来建立索引树的（左子节点小于父节点，父节点小于右子节点），所以支持排序和分组操作。

聚簇索引和非聚簇索引的区别？
  1、聚簇索引的叶子节点中存储的是行的全部数据，查询的时候直接返回结果，并不需要回表。
  2、非聚簇索引的叶子节点中存储的是主键的值，需要找到主键的值，才能找到数据，需要查找两次 b+tree。
  3、索引覆盖也是只查找一次。

MySQL 组合索引使用为什么要遵循最左原则？
  1、因为 b+tree 是按照从左到右的顺序来建立搜索树的。
  2、比如索引 (name, age, sex)，索引会先比较 name，如果 name 一样，会再依次比较 age，sex，最后获取检索的数据。

MySQL 索引如果组合索引都使用上，用遵循最左原则吗？
  不用。
  因为查询优化器会为我们选择合适的索引。比如 (name, age, sex) 组合索引中，where 条件中只要出现了最左边的 name 字段就会匹配上。

MySQL 组合索引多个字段之间的顺序怎么选择？
  将选择性最高的列放在最左边，这样能够更快的过滤出需要的行。
  有一个计算公式：

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;

什么是索引下推？
  1、mysql 5.6 引入索引下推（ICP）。
  2、explain 分析结果中 extra 会显示 Using index condition。
  参考：
  Index Condition Pushdown Optimization
  MySQL ICP 索引条件下推优化

未完待续……

本作品采用《CC 协议》，转载必须注明作者和本文链接