MySQL高频面试题整理

1. MySQL常用存储引擎有哪些？有什么区别？

   常用的有InnoDB、MyISAM

   	InnoDB	MyISAM
   外键	支持	不支持
   事务	支持	不支持
   锁	行级锁、表锁	表锁
   是否支持崩溃后恢复	根据redo log恢复	不支持

2. 什么是索引？索引有什么用？索引的数据结构？

   索引是一种适用于快速查询和检索数据的数据结构。常见的索引结构有B树，B+树，Hash。

   1. 索引优缺点？

      优点：使用索引可以大大加快数据检索速度，不使用索引会进行全表搜索。可以使用唯一索引来保证数据表中每一行数据的唯一性。

      缺点：创建索引和维护索引需要耗费时间。如果对表中数据进行增删改，那么相应的也要修改索引，会降低SQL执行效率。索引存储在硬盘上，会耗费一定空间。

   2. 索引底层数据结构？为何不选用Hash，B树，红黑树，平衡二叉树？

      InnoDB选择B+树作为索引底层数据结构。B+树的特点是所有数据存储在叶子节点上，同时叶子节点是有序的单向链表，适合范围查询，当查找到一个叶节点之后，顺着链表，后面的节点都是比这个节点大的。

      Hash索引基于Hash表实现，对于每一行数据通过Hash函数计算得到Hash码，散列到Hash表中，查找复杂度是O（1），但是因为Hash值是无序的所以不支持范围查询和排序。如果要使用精准查询，可以考虑使用Hash索引。

      B树节点和叶节点均存放键和值，因为B+树非叶节点不存储数据，只存储索引，索引相比于B树可以存储更多索引，使树的高度变低，减少IO次数，加快检索速度。同时B+树叶子节点是连在一起的，如果B树想进行范围查询，有回查问题。

      平衡二叉树会出现回查问题，同时因为每个节点只能存放一个数据，导致树很高。

      红黑树是特殊的平衡二叉树，不合适的原因类似。

   3. 索引类型？

      索引类型可以分为主键索引和辅助索引。一张表只能有一个主键，并且主键不能为null，不能重复。当表中没有指定主键时，InnoDB会自动检查表中是否有唯一索引字段，如果有，则选择该字段为默认主键，否则将自动创建一个自增主键。

      辅助索引又包括唯一索引，普通索引，前缀索引，全文索引。唯一索引是建立列的唯一性（这个列不能出现重复数据，允许为null，一张表允许创建多个唯一索引）。普通索引就是前文提到的用于加快查询速度的索引。前缀索引只适用于字符串类型的数据，对字符串的前几个字符创建索引。全文索引，为检索大文本数据中的关键字信息，解决模糊查询效率低的问题（where name like “%张%”）

   4. 聚集索引和非聚集索引

      聚集索引指的是索引和数据放一起的索引（都放在叶节点上），如主键索引。非聚集索引则是索引和数据分开，辅助索引都是非聚集索引，可能会回表查询。回表查询指如对name建索引，但是查询语句中有age，因为name索引中只有name的值，没有age，所以还需要回主键索引再查一遍，也就是所谓的回表查询。

   5. 索引使用场景？

      • 中大型表建立索引，小表全表扫描更快
      • 超大型表索引建立和维护代价大，考虑水平或者垂直拆分表
      • 表经常增删改，不适合建索引
      • 多字段建立联合索引
      • 经常出现在where条件、order by条件里的字段适合建立索引

   6. 最左前缀法则

      对于联合索引，存在最左前缀法则，从最左边为起点开始连续匹配，遇到范围查询停止匹配。举个例子。对列a，b，c建立索引。

      以下两种索引都会生效，where字段后的顺序不影响结果，MySQL优化器会自动优化查询顺序。

      select * from table_name where a=1 and b=2 and c=3
      select * from table_name where b=1 and a=2 and c=3
      

      以下没有用到索引，因为a没有出现

      select * from table_name where b=2 and c=3
      

      以下只有a用到了索引，因为中间b没出现

      select * from table_name where a=2 and c=3
      

      以下只有a用到了索引，b和c因为使用了范围查询，索引失效

      select * from table_name where a=2 and b>3 and c<2
      

      此外对于字符串的情况，只有前缀匹配索引才生效。

      select * from table_name where a like 'ab%';
      select * from table_name where a like '%ab';
      select * from table_name where a like '%ab%';
      

      以上都可以用B+树的数据结构来解释。详见这个视频

   7. 索引失效情况

      • 条件中有or
      • 索引上进行计算
      • 索引中使用函数
      • 使用了！，=，<>
      • 索引使用is null

3. 什么是事务？事务四大特性？

   事务是逻辑上的一组操作，要么都执行，要么都不执行。

   四大特性：

   • 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用
   • 一致性： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的
   • 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的
   • 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响

4. 并发事务一致性问题

   多个事务并发访问，可能会带来一系列问题。

   • 脏读。 事务A更新了数据，但还没有提交，这时事务B读取到事务A更新后的数据，然后事务A回滚了，事务B读取到的数据就成为脏数据了 。
   • 不可重复读。 事务A对数据进行多次读取，事务B在事务A多次读取的过程中执行了更新操作并提交了，导致事务A多次读取到的数据并不一致 。
   • 幻读。 事务A在读取数据后，事务B向事务A读取的数据中插入了几条数据，事务A再次读取数据时发现多了几条数据，和之前读取的数据不一致 。
   • 丢失修改。 事务A和事务B都对同一个数据进行修改，事务A先修改，事务B随后修改，事务B的修改覆盖了事务A的修改

5. 数据库隔离级别

   MySQL采用隔离级别解决并发事务带来的问题。

   • 读取未提交 。 一个事务在提交前，它的修改对其他事务也是可见的

   • 读取已提交 。 一个事务提交之后，它的修改才能被其他事务看到 。

   • 可重复读（默认） 。 在同一个事务中多次读取到的数据是一致的 。

   • 串行化 。 强制事务串行执行 。

   隔离级别	脏读	幻读	不可重复读
   读取未提交	√	√	√
   读取已提交	×	√	√
   可重复读	×	√	×
   串行化	×	×	×

6. MVCC实现隔离级别

   MVCC（multiple version concurrent control）是一种控制并发的方法，主要用来提高数据库的并发性能。MVCC就是在不加锁的情况下，解决数据库读写冲突问题（快照读）。

   InooDB对MVCC的实现依赖于：隐藏字段、read view、undo log

   • 隐藏字段

   InnoDB为每行数据添加了三个隐藏字段，DB_TRX_ID（最后一次插入或更新改行事务的id），DB_ROLL_PTR（回滚指针，指向该行的undo log），DB_ROW_ID（如果没有设置主键且该表没有唯一非空索引时，InnoDB会使用该id来生成聚集索引）

   • read view

   read view里主要有如下几个字段，creator_trx_id（创建这个read view的事务id），min_trx_id（生成read view时当前系统中活跃的读写事务中最小的事务id，小于这个id的trx_id可以访问这个版本），max_trx_id（生成read view时系统中应该分配给下一个事务的id，大于这个的trx_id不可以访问），若trx_id位于min_trx_id与max_trx_id之间，则假如存在于活跃事务列表（m_ids）即不可见。m_ids（生成read view时当前系统中活跃的读写事务的事务id列表）

   • undo-log

   讲MySQL日志的时候再细说。

   举例：MVCC实现读取已提交隔离级别。

   读取已提交隔离级别下，每次select查询前都生成一个read view。

   假设现在表里有一条数据张三，开启两个事务，一个事务ID是20，执行两条update语句修改这条数据（先修改为李四，再修改为王五），另外一个事务ID是40，修改其他表。那么在事务开始前就会创建read view，内容如下

   m_ids	20,40
   min_trx_id	20
   max_trx_id	41
   creator_trx_id（事务只有增删改的时候ID才不为0，读事务默认为0）	0

   对于李四和王五数据来说，trx_id为20，根据read view的规则，无法访问。而张三由于是在事务开始前有的数据，其trx_id一定是小于20的，那么就可以访问，就实现了读取已提交。

   举例：MVCC实现可重复读隔离级别。

   可重复读隔离级别下，只在事务开始的第一次select生成一个read view。假设有两个事务，一个事务查询两次，另外一个事务做修改，但是因为只在第一次查询的时候生成read view，后续不更新了，所以实现了两次查询结果一致，即可重复读。

7. 乐观锁和悲观锁

   乐观锁：假设数据并发更新不产生冲突，不对数据表加锁。实现方式：版本号和CAS

   悲观锁：假设数据并发更新会有冲突，每次修改数据都要加锁。

8. 外连接、内连接、交叉连接有什么区别？

   外连接：又可以分为左连接、右连接。左连接显示左表所有数据以及右表符合条件的数据，右表不符合条件的数据为Null。右连接相反。

   内连接：只显示符合条件的数据。

   交叉连接：笛卡尔积的一种连接。

   举例：

   

   

   左连接查询结果如下，由于小刚的班级还未创建，所以右表数据为null

   

   右连接查询结果如下，坐标没有的数据显示为null

   

   内连接查询结果如下，只显示两表都有的结果

   

   笛卡尔积的结果如下