MySql面试题

说明：这个文章只适用于我自己，针对自己的不足做的整理，并不适用大众，后期如果有时间整理一个完整的面试集。

1. 数据库索引失效了怎么办？

参考答案

可以采用以下7种方式，来避免索引失效：

• 使用组合索引时，需要遵循“最左前缀”原则；
• 不在索引列上做任何操作，例如计算、函数、类型转换，会导致索引失效而转向全表扫描；
• 尽量使用覆盖索引（之访问索引列的查询），减少 select * 覆盖索引能减少回表次数；
• MySQL在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描；
• LIKE以通配符开头（%abc）MySQL索引会失效变成全表扫描的操作；
• 字符串不加单引号会导致索引失效（可能发生了索引列的隐式转换）；
• 少用or，用它来连接时会索引失效。OR前后的两个条件中的列都是索引时，查询中才使用索引。

2. MySQL的索引为什么用B+树？

参考答案

B+树由B树和索引顺序访问方法演化而来，它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，各叶子节点通过指针进行链接。如下图：

B+树索引在数据库中的一个特点就是高扇出性，例如在InnoDB存储引擎中，每个页的大小为16KB。在数据库中，B+树的高度一般都在2～4层，这意味着查找某一键值最多只需要2到4次IO操作，这还不错。因为现在一般的磁盘每秒至少可以做100次IO操作，2～4次的IO操作意味着查询时间只需0.02～0.04秒。

3. 谈谈MySQL的事务隔离级别

参考答案

SQL 标准定义了四种隔离级别，这四种隔离级别分别是：

• 读未提交（READ UNCOMMITTED）；
• 读提交 （READ COMMITTED）；
• 可重复读 （REPEATABLE READ）；
• 串行化 （SERIALIZABLE）。

事务隔离是为了解决脏读、不可重复读、幻读问题，下表展示了 4 种隔离级别对这三个问题的解决程度：

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	可能	可能	可能
READ COMMITTED	不可能	可能	可能
REPEATABLE READ	不可能	不可能	可能
SERIALIZABLE	不可能	不可能	不可能

上述4种隔离级别MySQL都支持，并且InnoDB存储引擎默认的支持隔离级别是REPEATABLE READ，但是与标准SQL不同的是，InnoDB存储引擎在REPEATABLE READ事务隔离级别下，使用Next-Key Lock的锁算法，因此避免了幻读的产生。所以，InnoDB存储引擎在默认的事务隔离级别下已经能完全保证事务的隔离性要求，即达到SQL标准的SERIALIZABLE隔离级别。

扩展阅读

并发情况下，读操作可能存在的三类问题：
脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。
不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。
幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。

4 MySQL的事务隔离级别是怎么实现的？

参考答案

InnoDB支持四种隔离级别，每种级别解决掉的问题如下表：

脏读	不可重复读	幻读	幻读
READ UNCOMMITTED	Y	Y	Y
READ COMMITTED	N	Y	Y
REPEATABLE READ（默认）	N	N	N
SERIALIZABLE	N	N	N

这四种隔离级别的实现机制如下：

• READ UNCOMMITTED & READ COMMITTED：

通过Record Lock算法实现了行锁，但READ UNCOMMITTED允许读取未提交数据，所以存在脏读问题。而READ COMMITTED允许读取提交数据，所以不存在脏读问题，但存在不可重复读问题。

• REPEATABLE READ：

使用Next-Key Lock算法实现了行锁，并且不允许读取已提交的数据，所以解决了不可重复读的问题。另外，该算法包含了间隙锁，会锁定一个范围，因此也解决了幻读的问题。

• SERIALIZABLE：

对每个SELECT语句后自动加上LOCK IN SHARE MODE，即为每个读取操作加一个共享锁。因此在这个事务隔离级别下，读占用了锁，对一致性的非锁定读不再予以支持。

5. 如何解决幻读问题？

参考答案

MySQL的InnoDB引擎，在默认的REPEATABLE READ的隔离级别下，实现了可重复读，同时也解决了幻读问题。它使用Next-Key Lock算法实现了行锁，并且不允许读取已提交的数据，所以解决了不可重复读的问题。另外，该算法包含了间隙锁，会锁定一个范围，因此也解决了幻读的问题。

6. 介绍一下间隙锁

参考答案

InnoDB存储引擎有3种行锁的算法，间隙锁（Gap Lock）是其中之一。间隙锁用于锁定一个范围，但不包含记录本身。它的作用是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生。

7. MySQL的慢查询优化有了解吗？

参考答案

优化MySQL的慢查询，可以按照如下步骤进行

• 开启慢查询日志：
  MySQL中慢查询日志默认是关闭的，可以通过配置文件my.ini或者my.cnf中的log-slow-queries选项打开，也可以在MySQL服务启动的时候使用–log-slow-queries[=file_name]启动慢查询日志。
  启动慢查询日志时，需要在my.ini或者my.cnf文件中配置
  long_query_time选项指定记录阈值，如果某条查询语句的查询时间超过了这个值，这个查询过程将被记录到慢查询日志文件中。

• 分析慢查询日志：
  直接分析mysql慢查询日志，利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句。

• 常见慢查询优化：

1. 索引没起作用的情况
   1.1 在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置，索引才会起作用。

   1.2 MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中的第1个字段时索引才会被使用。

   1.3 查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。

2. 优化数据库结构
   2.1 对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

   2.2 对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

3. 分解关联查询
   很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效。

4. 优化LIMIT分页
   当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

8. 说一说你对explain的了解

参考答案

MySQL中提供了EXPLAIN语句和DESCRIBE语句，用来分析查询语句，EXPLAIN语句的基本语法如下：

EXPLAIN [EXTENDED] SELECT select_options
使用EXTENED关键字，EXPLAIN语句将产生附加信息。执行该语句，可以分析EXPLAIN后面SELECT语句的执行情况，并且能够分析出所查询表的一些特征。下面对查询结果进行解释：

• id：SELECT识别符。这是SELECT的查询序列号。
• select_type：表示SELECT语句的类型。
• table：表示查询的表。
• type：表示表的连接类型。
• possible_keys：给出了MySQL在搜索数据记录时可选用的各个索引。
• key：是MySQL实际选用的索引。
• key_len：给出索引按字节计算的长度，key_len数值越小，表示越快。
• ref：给出了关联关系中另一个数据表里的数据列名。
• rows：是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。
• Extra：提供了与关联操作有关的信息。

扩展阅读

DESCRIBE语句的使用方法与EXPLAIN语句是一样的，分析结果也是一样的，并且可以缩写成DESC。。DESCRIBE语句的语法形式如下：

DESCRIBE SELECT select_options

9. explain关注什么？

参考答案

重点要关注如下几列：

列名 备注

• type 本次查询表联接类型，从这里可以看到本次查询大概的效率。
• key 最终选择的索引，如果没有索引的话，本次查询效率通常很差。
• key_len 本次查询用于结果过滤的索引实际长度。
• rows 预计需要扫描的记录数，预计需要扫描的记录数越小越好。
• Extra 额外附加信息，主要确认是否出现 Using filesort、Using temporary 这两种情况。

其中，type包含以下几种结果，从上之下依次是最差到最好：
类型 备注

• ALL 执行full table scan，这是最差的一种方式。
• index 执行full index scan，并且可以通过索引完成结果扫描并且直接从索引中取的想要的结果数据，也就是可以避免回表，比ALL略好，因为索引文件通常比全部数据要来的小。
• range 利用索引进行范围查询，比index略好。
• index_subquery 子查询中可以用到索引。
• unique_subquery 子查询中可以用到唯一索引，效率比 index_subquery 更高些。
• index_merge 可以利用index merge特性用到多个索引，提高查询效率。
• ref_or_null 表连接类型是ref，但进行扫描的索引列中可能包含NULL值。
• fulltext 全文检索。
• ref 基于索引的等值查询，或者表间等值连接。
• eq_ref 表连接时基于主键或非NULL的唯一索引完成扫描，比ref略好。
• const 基于主键或唯一索引唯一值查询，最多返回一条结果，比eq_ref略好。
• system 查询对象表只有一行数据，这是最好的情况。

另外，Extra列需要注意以下的几种情况：
关键字 备注

• Using filesort 将用外部排序而不是按照索引顺序排列结果，数据较少时从内存排序，否则需要在磁盘完成排序，代价非常高，需要添加合适的索引。
• Using temporary 需要创建一个临时表来存储结果，这通常发生在对没有索引的列进行GROUP BY时，或者ORDER BY里的列不都在索引里，需要添加合适的索引。
• Using index 表示MySQL使用覆盖索引避免全表扫描，不需要再到表中进行二次查找数据，这是比较好的结果之一。注意不要和type中的index类型混淆。
• Using where 通常是进行了全表/全索引扫描后再用WHERE子句完成结果过滤，需要添加合适的索引。
• Impossible WHERE 对Where子句判断的结果总是false而不能选择任何数据，例如where 1=0，无需过多关注。
• Select tables optimized away 使用某些聚合函数来访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询，例如MIN()\MAX()，这种也是比较好的结果之一。