面试题 MySQL

一、MyISAM和InnoDB存储引擎的区别

1、MyISAM

不支持事务，不支持外键约束，索引文件和数据文件分开，这样在内存里可以缓存更多的索引，对查询的性能更好，适用于少量的插入，大量查询的场景，支持表级锁。报表系统是比较适合MyISAM存储引擎的

2、Innodb

支持事务，支持外键约束，必须有主键，数据文件和（主键）索引是在一起的，支持表、行级锁。有成熟的高并发、高可用、分库分表、读写分离、主从切换的方案

二、MySQL的索引实现原理

1、为什么要用b+树

mysql的索引底层是用到了b+树，而b+树是b-树的变种。至于为什么要使用b+树，而不是二叉树和红黑树呢，如果只是select * from table where id = 15，那么二叉树、红黑树等都能实现，但如果是select * from table where id < 15，那红黑树这种就不好使了，类似于全表扫描，因为他们的高度是不可控的，而b+树是可控的，mysql通常是3到5层

2、b-树

 比如这样一条sql，select * from table where id = 20，那么就先将id跟56比较，比56小，再跟15比较，比15大，就进入15跟56之间的节点，以此类推

3、b+树

 与b-树最大的不同，就在于数据只存储在叶子节点上

一般数据库的索引都对b+树进行了优化，加入了顺序访问的指针，如下图所示 

4、MyISAM存储引擎的索引

myIsam最大的特点是索引和数据文件分开，所以按照上面的原理，在叶子节点找到的data是数据地址，再根据地址从数据文件里找到数据

5、InnoDB存储引擎的索引

InnoDB必须要有主键，所以会默认根据主键建立一个索引，叫做聚簇索引。当我们给别的字段建立索引后，会根据上面的逻辑，在叶子节点找到的data是主键id，然后再根据主键id从聚簇索引中找到数据。所以为什么不建议用UUID这种很长的字段来作主键呢？因为按照上面的逻辑，最终在叶子节点找到的data都是uuid，name索引找到的是uuid，dataTime索引找到的也是uuid，每种自己建立的索引找到的都是uuid，这样每个叶子节点的data都很长，那整个索引就会占用很大的空间。建里主键用自增主键，因为自增主键是顺序的，对于b+树而言，往一个方向加节点就行，不会让之前的节点重新组合

6、索引要如何建

1）如果查询涉及到3个字段，那么针对这3个字段建立联合索引，不要单独建索引

2）针对联合索引，sql中的where要遵守最左前缀匹配原则

三、MySQL事务隔离级别

1、ACID

1）Atomic：原子性，一堆SQL，要么一起成功，要么都别执行

Consistency：一致性，针对数据一致性来说的，一组SQL执行前，数据必须是准确的，执行之后，数据也必须是准确的

Isolation：隔离性，多个事务在跑的时候互不干扰

Durability：持久性，事务成功后，就要保证对数据的修改是永久有效的

2、事务隔离级别

1）读未提交：事务B读了事务A未提交事务的数据。事务A将name从张三改成李四，还未提交呢，事务B就读到name等于李四，结果读完后事务A又回滚了，变回张三了。这种现象被称为脏读

2）读已提交：事务B每次都要等事务A提交完事务后才能读到数据，不会产生脏读，但可能一个事务内的2次读的结果不一样。这种现象被称为不可重复读

3）可重复读：相比读已提交，对于select name from table where id = 10这样的sql，不会产生一个事务内的2次读的结果不一样的场景，但对于select count(*)这样的查询，结果还是会不一样。这种现象被称为幻读，不可重复读是update场景，幻读是insert、delete场景

4）序列化：所有事务只能串行化执行，必须前一个事务执行完毕（排他锁释放），下一个事务才能执行

3、MVCC

MySQL默认的隔离级别是可重复读，但不仅不会出现不可重复读，还不会出现幻读，这一切都是通过MVCC实现的，它是由3个隐式字段、undo日志、read view来实现的

1）每条数据都有3个隐藏的列

• DB_TRX_ID，用来记录最近修改（插入/删除）的事务id
• DB_ROLL_PTR，指向这条记录的上一个版本
• DB_ROW_ID，隐藏主键，如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

2）undo日志主要分2种

• insert undo log，代表事务在 insert 新记录时产生的 undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
• update undo log，事务在进行 update 或 delete 时产生的 undo log ; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除

3）首先我们往person表里插入一条数据，如下所示（这里假设事务id是null）

 4）事务1修改数据，数据库对行加排他锁，把该行数据拷贝到undo日志中，作为旧记录，修改事务id为当前id，回滚指针指向undo日志中的地址

5）事务2更新表，又把事务1的记录拷贝到undo日志中，事务id改为当前事务id

6）Read View读视图

Read View 就是事务进行快照读操作的时候生产的读视图，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID

Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的 DB_TRX_ID（即当前事务 ID ）取出来，与系统当前其他活跃事务的 ID 去对比。如果 DB_TRX_ID 跟 Read View 的属性做了某些比较，不符合可见性，那就通过 DB_ROLL_PTR 回滚指针去取出 Undo Log 中的 DB_TRX_ID 再比较，即遍历链表的 DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的 DB_TRX_ID , 那么这个 DB_TRX_ID 所在的旧记录就是当前事务能看见的最新老版本

4、排他锁和共享锁

1）排他锁

排他锁锁住行后，别的排他锁和共享锁就无法再操作这行。insert、update、delete、select XXX for update，会给相关的行加上排他锁

2）共享锁

共享锁锁住行后，别的共享锁可以继续获取这行，排他锁不可以。MySQL的共享锁是select XXX lock in share mode

3）不加锁

最普通的select查询不会加任何锁，即使该行有别的事务在update也可以读取update前的值，是通过快照读实现的，lock in share mode和for update的读都是当前读

四、MySQL调优

explain加sql语句，有如下字段

1、id

复杂SQL中具体每个SQL的执行顺序，id相同，执行顺序从上之下，id不同，执行顺序从大到小

2、select_type

查询类型，是简单查询还是子查询还是union查询

3、type

索引扫描类型，性能由好到差依次为 system>const>eq_ref>ref>range>index>all

4、key

实际使用到的索引