【八股】MySQL

面试题

SQL语句在MySQL里的执行过程？

MySQL分为Server层和存储引擎层。
Server层里，首先经过1. 连接器，主要做用户身份认证的工作。
2. 查询缓存，缓存里的数据是kv类型的数据，Key是查询语句，Value是结果集。但由于应用场景比较少，官方在MySQL8.0版本后删除了这个缓存的功能。
3.分析器，做的工作是通过词法分析和语法分析来得到SQL语句是干嘛的。
4.优化器，里面决定他认为最优的方案去执行，比如有多个索引的情况下如何选择索引。
5.执行器，确定了执行方案后，就去调用存储引擎的接口，返回接口执行的结果。

知道什么是覆盖索引吗？

覆盖索引是指，查询使用的索引，需要返回的列，在该索引的叶子节点中已经能够全部找到。
简单的来说，覆盖索引就是查询索引后，已经得到了所需字段的信息，不需要回表查询。
引生出的两个小问题：

1. 用id查询，就能直接走聚簇索引，只需要一次索引扫描，就能得到需要的数据，性能高
2. 如果返回的列中没包含所有需要的列，就有可能会触发回表查询，所以尽量避免使用select *

MySQL超大分页怎么处理？

问题：
数据量比较大时，limit分页查询，需要对数据进行排序，效率低。
比如说limit 9000000, 10, 此时MySql需要排序前9000010记录，但只返回最后10条记录，其他记录丢弃，排序的代价很大。

优化思路：

select * from tb_sku limit 9000000, 10;

select * from tb_sku t, 
	(select id from tb_sku order by id limit 9000000, 10) a
where t.id = a.id;

索引创建的原则

一般来讲，就是表中的数据超过10万以上，才会创建索引。
1.数据量较大，且查询比较频繁的 表
2.常作为where，order by，limit的字段
3.尽量使用联合索引
4.如果一个字段的内容较长，也可以用前缀索引
5.要控制索引的数量，因为添加索引会导致增删改的速度变慢

什么情况下索引会失效？

1.用到复合索引（联合索引）时，违反最左前缀法则
2.范围查询右边的列，不能使用索引
3.在索引列上进行运算操作，索引将失效
4.字符串不加单引号，会类型转换，造成索引失效
5.以%开头的like模糊查询，会造成索引失效

谈一谈你对sql优化的经验

1.表的设计优化（参考阿里的开发手册）
2.索引优化，创建索引原则
3.sql语句优化，避免使用select *，避免索引失效…
4.主动复制，读写分离。目的是不让数据的写入影响读操作
5.分库分表

事务的特性是什么？可以详细说一下吗？

ACID
原子性(Atomicity)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)
举个例子，a向b转账100块，a扣除100元，b增加100元。
原子性体现在这两个操作要么都成功，要么都失败。
一致性体现在，a扣了100块钱，b必须增加100块钱。
隔离性体现在，a向b转账不受其他事务的影响。
持久性体现在事务提交后，要把数据持久化。

one more thing:
事务是在 MySQL 引擎层实现的,InnoDB 引擎是支持事务的, MyIslam 引擎不支持事务

并发事务的问题？解决方法？MySQL的默认隔离级别？

并发事务的问题：
脏读：一个事务读到另一个事务还没有提交的数据
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同
幻读：一个事务按条件查询数据时，没有对应的数据行，但在插入数据时，又发现这行数据已经存在了，好像出现了“幻影”

解决方法：
MySQL支持四种隔离级别。
第一个是读未提交（read uncommitted），他解决不了刚才提出的所有问题，项目里一般也不用这个。
第二个是读已提交（read committed），他能解决脏读，但不能解决不可重复读和幻读。
第三个是可重复读（repeatable read），他能解决脏读和不可重复读，但是解决不了幻读。
第四个是串行化（serializable），它可以解决刚才提出来的所有问题，但是事务是串行执行的，性能比较低。

MySQL的默认隔离级别：
可重复读(repeatable read)

redo log和undo log的区别？

redo log日志记录的是数据页的物理变化，若服务宕机可用来同步数据。
undo log日志记录的是逻辑日志，当事务回滚时，通过逆操作就可以恢复原来的数据。
redo log保证了事务的持久性，undo log保证了事务的原子性和一致性。

MVCC

MVCC机制是基于乐观锁思想的，通过聚簇索引里的两个隐藏字段，Undo版本链，ReadView来控制多个并发事务访问同一个记录时的行为。

对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同：
读提交隔离级别是在每次读取数据时，都会生成一个新的 Read View。前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。。
可重复读隔离级别是在开启事务时，生成一个新的 Read View， 这个 Read View 会一直沿用到事务提交。这样就保证了在事务期间读到的数据都是事务启动前的记录。

在可重复读隔离级别中，普通的 select 语句就是基于 MVCC 实现的快照读，也就是不会加锁的。而 select … for update 语句就不是快照读了，而是当前读了，也就是每次读都是拿到最新版本的数据，但是它会对读到的记录加上 next-key lock 锁。

MySQL InnoDB 引擎的可重复读隔离级别（默认隔离级），根据不同的查询方式，分别提出了避免幻读的方案：
针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读。
针对当前读（select … for update 等语句），是通过 next-key lock（记录锁（行锁？）+ 间隙锁）方式解决了幻读。行锁防止数据被别的事务修改或删除，间隙锁防止别的事务新增数据，行锁和间隙锁形成的Next-Key Lock共同解决的RR隔离级别下在写数据时的幻读问题。

所以，MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。
要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。