MySQL 面试题

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

什么是前缀索引？
  1、对于 blob、text 和 varchar 类型的列，必须使用前缀索引，只索引开始的部分字符。
  2、前缀长度的选取需要根据索引选择性来确定。

什么是覆盖索引？
  select 的数据列只用从索引中就能够取得，不必读取数据行，无需回表。换句话说查询列要被所建的索引覆盖。

索引：
KEY `index_name1_name2_name3` (`name1`,`name2`,`name3`)

因为查询的字段 name1，name2，name3 被索引 index_name1_name2_name3 覆盖，explain 工具分析返回的 extra 列中存在 using index 结果 ，所以以下 sql 使用了唯一索引。
mysql> explain select name1, name2, name3 from mytable;  
+----+-------------+-----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key                     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | mytable | NULL       | index | NULL          | index_name1_name2_name3 | 310     | NULL |   19 |   100.00 | Using index   |
+----+-------------+-----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+	

什么是全文索引？
  1、mysql 的全文索引类型是 fulltext。
  2、全文索引只支持 myisam 和 innodb（>= 5.6）存储引擎，并且只能用于创建 char，varchar，text 类型。.
  3、最开始只支持英文不支持中文，自 >= 5.7.6 开始，引入了一个 ngram 全文分析器解决了这个问题。
  参考：
  Full-Text Search Functions
  MySQL 之全文索引

有时候创建了索引，但 MySQL 却没有选择索引？
  1、联合索引没有遵循最左原则。
  2、like 查询以 % 开头。
  3、没有按照正确的数据类型查询，比如手机号存储是 int，你却将它作为字符串查询。
  4、where 条件中使用了算数运算符或者其它的表达式，也不会走索引。
  5、查询优化器认为全表扫描要比走索引成本更低。

MySQL 数据类型 int(4) 和 int(11) 有什么区别？
  存储的时候有点区别，其它没有什么区别，都是占 4 字节 32 位。
  比如存储 99 这个数字，int(4) 会存储 0099，而 int(11) 会存储 00000000099。不过需要在创建类型的时候增加 zerofill 这个值，表示用 0 填充，否则看不出效果的。

什么是事务？
  事务就是一条 sql 或者一组 sql 要么都执行成功，要么都执行失败。

数据库事务的四个特性？
  1、原子性
  2、一致性
  3、隔离性
  4、持久性

事务隔离性的四个隔离级别？
  1、读未提交
  2、读已提交
  3、可重复读（默认级别）
  4、串行化

什么是脏读、幻读、不可重复读？
  脏读：当前事务读取到其它事务未提交的数据。
  幻读：当前事务读取到其它事务 insert 的数据。
  不可重复读：当前事务读取到其它事务提交的数据。

事务的的可重复读是怎么实现的？
  依靠 mvcc（多并发版本控制）和 next-key locks（临键锁）实现的。

两段锁协议是什么？
  将事务分成连个阶段，加锁阶段和解锁阶段。

什么是死锁？
  死锁就是两个事物执行中，因争夺锁资源造成了互相等待的现象。

死锁怎么处理？
  1、超时等待。
  2、死锁检测。
  3、控制并发度。

悲观锁是什么？
  每次使用只让自己使用，其它人只能等待，等自己使用完成后才释放给其它人使用。

乐观锁是什么？
  每次拿数据不会上锁，每次在更新的时候会判断别人有没有修改，如果没有修改就就更新。如果有修改就不更新，认为是过期数据。

间隙锁是什么？
  在索引记录上加的锁，保证某个间隙的数据在锁定的情况下不会发生变化，解决幻读的问题。

MVCC 是什么？
  多版本并发控制，是 innodb 存储引擎实现事务隔离级别的一种方式，用于实现读已提交和可重复读两种隔离级别。

Next-key Locks 是什么？
  临键锁，是行锁和间隙锁的合并。

一张表，里面 id 自增，insert 17 条记录，然后删除了 15，16，17 条记录，然后 insert 一条记录，这条记录 id 是 18 还是 15？
  1、如果不重启的话是 18。如果重启的话，要看表的存储引擎是什么！
  2、如果是 myisam 存储引擎 id 就是 18。因为 myisam 会把自增主键的最大 id 记录在数据文件中，重启 mysql 自增主键的最大 id 也不会丢失。
  3、如果是 innodb 存储引擎 id 就是 15。因为 innodb 会把自增主键的最大 id 记录到内存中，所以重启数据库或者对表进行 optimize 操作，都会导致最大 id 丢失。

如果删除表了 10000 行数据你会怎么做？
  循环执行 20 次，每次删除 500 行数据。
  因为如果直接执行 10000 行数据，单句占用时间长，锁等待时间时间也会延长。而且大事务会导致主从复制的延迟。

一个查询语句中有一个不存在的字段，这条 SQL 会抛出一个报错，这个错误是在连接器、分析器、优化器、执行器那个阶段报出来的呢？
  优化器，优化器会进行优化分析，比如用先执行哪个条件，使用哪个索引。
  如果没有对应的字段就会报错的，因为表字段是事先定义好的，所以可以直接读取的。