MySQL面试重点-2

16. MySQL数据引擎：

引擎分类：

show engines命令查看数据库支持的存储引擎。

描述一下InnoDB和MyISAM的区别？**

InnoDB存储限制64TB，而MyISAM存储限制256TB；
InnoDB支持事物，而MyISAM不支持；
InnoDB支持外键，而MyISAM不支持；
InnoDB支持行级锁（默认）+表级锁，而MyISAM支持表级锁；
InnoDB支持MVCC（多版本并发控制技术）, 而MyISAM不支持；
InnoDB即支持聚簇索引又支持非聚簇索引，而MyISAM 只支持非聚簇索引；
InnoDB不支持全文索引（5.6版本之后支持），而MyISAM支持。

如何选择？

现在MySQL的默认存储引擎已经变成了InnoDB，推荐使用InnoDB：

1. 是否需要支持事务，如果需要选择InnoDB，如果不需要选择MyISAM；
2. 如果表的大部分请求都是读请求，可以考虑MyISAM，如果既有读也有写，使用InnoDB。

17. 描述一下MySQL主从复制的机制的原理？MySQL主从复制主要有几种模式？（没啥印象）

原理：

从库会生成两个线程：I/O线程和SQL线程；
- I/O线程会去请求主库的binlog，并将得到的binlog写到本地的relay log(中继日志)文件中；
- SQL线程会读取relay log文件中的日志，并解析成SQL语句，并逐一执行。
同步时，主库会生成一个dump线程，用来给从库I/O线程传binlog。

模式：

一主一从、主主复制、一主多从、多主一从、联级复制。

主从同步延迟问题：*

原因：
- DML和DDL的IO操作是随机的，不是顺序，成本很高；
- 主库在高并发时，从库的SQL线程处理不过来；
- Slave中有大型Query语句产生了锁等待。
解决：
- 提高机器性能；
- 业务分库，一主多从；
- 加缓存层。

18. 如何优化SQL，查询计划（Explain）的结果中看哪些些关键数据？

前提：**

做好表结构设计，相关字段提前加索引。
业务处理，减少数据库连接；增加缓存层等。

如何优化：

开启慢查询日志（不说，因为没有实战经验）。
查询的优化：
- 减少连接次数；
- 返回更少的数据；
- 加索引，并且避免全表扫描，注意查看索引是否生效，是否效率高。
合理的分库分表。
数据库访问优化（建立数据库连接池，建索引）。

Explain作用：

模拟MySQL优化器运行SQL语句，了解MySQL如何处理你的SQL语句。分析SQL查询语句或是表结构的性能瓶颈。

Explain解释：

id列（数据列的执行顺序）
select_type列（数据读取操作的操作类型）
table列（该行数据是关于哪张表）
type列（访问类型，重点关注）：
- 由好到差system > const > eq_ref > ref > range > index > ALL，一般来说，保证查询至少达到range级别，最好能达到ref。
- system：表只有一条记录(等于系统表)，这是const类型的特例，平时业务中不会出现。
- const：通过索引一次查到数据，该类型主要用于比较primary key 或者unique 索引，因为只匹配一行数据，所以很快。
- eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。
- ref：非唯一索引扫描，返回匹配某个单独值得所有行，本质上是一种索引访问，它返回所有匹配某个单独值的行。
- range：只检索给定范围的行，使用一个索引来选着行。key列显示使用了哪个索引。一般在你的WHERE 语句中出现between 、< 、> 、in 等查询，这种给定范围扫描比全表扫描要好。因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
- index：FUll Index Scan 扫描遍历索引树(扫描全表的索引，从索引中获取数据)。
- ALL：全表扫描从磁盘中获取数据百万级别的数据ALL类型的数据尽量优化。
possible_keys列（显示可能应用在这张表的索引）
keys列（实际使用到的索引，重点关注）
ken_len列（索引中使用的字节数，重点关注）
ref列（显示索引的哪一列被使用）
rows列（每张表有多少行被优化器查询，重点关注）
Extra列（扩展属性）:Using filesort 、Using temporary 、 Using index ....
- using filesort：排序的字段没有使用索引
- Using temporary : 使用了临时表保存中间结果

https://www.cnblogs.com/gdwkong/articles/8505125.html

19. 描述一下MySQL的乐观锁和悲观锁，锁的种类？

乐观锁：

乐观锁并不是数据库自带的，需要自己去实现，一般情况下，我们会在表中新增一个version字段，每次更新数据version+1，在进行提交之前会判断version是否一致。

悲观锁：

MySQL中的绝大部分锁都是悲观锁，按照粒度可以分为行锁和表锁：

行锁：***
- 共享锁：当读取一行记录的时候，为了防止别人修改，则需要添加S锁。
- 排它锁：当修改一行记录的时候，为了防止别人同时进行修改，则需要添加X锁。

- 记录锁（Record Lock）：添加在行索引上的锁。
- 间隙锁（Gap Lock）：它的锁定范围是索引记录之间的间隙，针对可重复读以上隔离级别。
- 临键锁（Next-key Lock）：记录锁 + 间隙锁。
Tips：
- 如果不需要解决幻读问题，不要加临键锁和间隙锁。
- 加锁方式：SELECT ... for update；