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 索引条件下推优化