mysql面试专题

mysql总体执行流程

1.服务器通过socket，接到一条查询sql，先从bufferPool查询，若有直接返回结果
2.若没有，服务器会使用sql解析器编译，生成可执行的解析树
3.生成的解析树进入预处理器，预处理器会校验解析树的语法规则以及执行权限
4.校验完成，解析树进入优化器，优化器会确定执行索引和替换等价操作，最终生成执行计划
5.最后服务器会调用存储引擎的api并把执行计划作为参数传递过去

explain关键字

1.explain可查看优化器生成的执行计划（计划上展示了执行逻辑和索引信息）
2.查询计划有id，select_type，table，type，key，extra字段
3.id字段标识每一次selcet的编号，select带有子查询的话，就有多个id，id大小标识select查询顺序
4.select_type字段标识查询类型（普通查询，联合查询，子查询）
1）SIMPLE：普通查询，不使用UNION或子查询
2）SUBQUERY：子查询，在select 或 where列表中包含子查询
3）PRIMARY：子查询，在from列表中包含子查询
4）UNION：联合查询，两条select使用union关键字连接
5.table字段可以展示出查询的是那个表
6.type字段标识查询时使用了什么索引规则
1）const和eq_ref：查询使用主键索引或唯一索引
2）ref：查询使用普通索引或者唯一性索引的部分前缀
3）range：使用索引，范围扫描，例如in(), between ,> ,<, >= 等操作
4）index：全表扫描
5）all：全表扫描
7.key字段标识查询时使用了具体的索引
8.extra字段标识额外说明信息

普通索引和唯一索引区别

1.辅助索引不具备唯一性,更新时，索引页在内存则直接更新，否则延迟更新到磁盘，临时把sql存到change buffer
2.唯一索引更新数据天然有高效性：数据有序，唯一命中。辅助索引也能保证部分有序，却不能保证更新数据量
3.辅助索引用异步延迟更新的方式大大提升了批量数据的写性能，由于多个写入操作在操作同一个索引页时还能合并
4.change buffer缺点：写操作密集会过度占用缓存池的内存空间，最大默认值为25%，上限配置为50%
5.change buffer合并刷盘：1）select查导致辅助索引页加载内存2）辅助索引页可用标记内存空间不足3）masterThread

checkPoint技术

1.修改数据时每次内存页数据发生变化都实时同步到磁盘，数据库的写性能面对并发场景会十分慢
2.当数据库写入时发生宕机事件，写入操作会丢失，数据并不可以被恢复
3.为了解决上述的问题，innodb采用的是writeAheadLog（redo日志）思路，事务提交时，先写入重做日志后修改页
4.为了控制脏页的数据合理存在缓冲池中和重做日志文件中，checkPoint技术的目的为：
1）缓冲池内存页空间不够时，需要将脏页及时刷入到磁盘空间
2）重做日志不够用时，需要将脏页及时刷入到磁盘空间
3）缩短宕机时从重做日志恢复数据的时间
5.checkPoint技术分两种模式：一种是关闭时全部脏页刷到磁盘，一种是运行时挑选部分脏页刷到磁盘
1）masterThread老版本定时任务会刷盘，新版本在pageCleaner线程
2）保证空闲内存页在一定数量，少了也会对脏页刷盘回收内存页
3）重做日志文件空间不足时，也会强制刷盘
4）脏页内存占比太大页会强制刷盘，可配置脏页数量限制

两次写

1.两次写能保证写入可靠性，防止部分写入失效（写操作写入一半到磁盘宕机），磁盘是脏数据，原始数据丢失
2.写入前，先写入原始页到doublewriteBuffer磁盘，再把修改后的页写入数据表的物理磁盘（类似可重试写入）
3.双写针对的是连续页写操作（例如binLog），单页写操作不需要双写（例如redoLog）

异步IO和刷新邻接页

1.异步IO能合并相邻页IO请求、能无需同步等待一个IO请求即可访问下一个IO请求页，但是异步IO需要操作系统接口支持
2.innodb支持检测更新内存页的所在分区是否有其他内存页也需要更新数据，有则一起同步更新，达到减少IO次数

日志类型

1.错误日志：格式-主机名.err,通过日志能查看启动时异常的打印，以及运行时异常的错误信息
2.慢日志查询：老版文件，新版表slowLog存储,可以通过long_query_time或是否走索引来界定慢查询sql，默认并不启动配置
3.查询日志：老版文件，新版表general_log存储，日志记录了所有的数据库请求，不管连接命令是否正确
4.二进制日志：binLog（格式主机名）,记录了数据库所有修改操作，默认不启动，主要用于恢复、复制、审计（sql注入）
1）binLog有三种数据格式：sql格式，表行数据，混合模式（自选判断）
2）老版只有sql格式，新版出现是因为主从模式出现问题（随机函数导致数据不一致）、事务可重复读需要快照内存数据
3）表行数据易于恢复和复制数据，但是可能因为更新数据量过大，会过度占用日志硬盘空间，因此建议使用混合模式
4）binLog只有事务提交之后才会刷到磁盘，事务不提交不刷盘，更新数据超过缓存池时，会写入到临时文件
5.重做日志：针对的是事务操作，事务提交之前也会把写操作同步到redoLog内
1）redoLog由一个文件组组成，至少有两个文件，存储引擎会先写文件1，满了会写到文件2，满了再切会文件1
2）redoLog日志记录的是内存页的数据，不需要等事务提交再写入，因此重做日志内存页写操作不需要双写
3）数据库要保证事务一致性，事务提交时，必须要同步强制刷盘到redoLog文件（事务提交操作不能存在异步）
4）redoLog三个LSN：log总偏移量、刷新到日志缓存偏移量、刷新到硬盘偏移量，启动恢复只恢复未刷盘的数据
6.undo日志：undo日志是逻辑日志，对于事务insert操作，undo日志会记录一条delete操作，以便回滚
1）undoLog分为两种，insertUndoLog和updateUndoLog，insert类型事务提交可直接删除，update则不行
2）事务提交，updateUndoLog不能直接删除，因为页可重用需要、以及其他事务可能要反推历史版本数据
3）updateUndoLog类型操作，尽管事务已提交，但还是先标记再更新，真正更新操作是由pure线程决定

mysql锁

1.不加锁场景，可以让用户所有事务操作保持串行执行，则不会存在任何业务问题，但是这样性能就会很差
2.加锁是为了提高并发度，以及做出一定取舍之后，应对并发下用户修改数据和保持一致性读（幻读、不可重复读）
3.文件系统是无锁机制，这也是数据库系统区别于文件系统的原因，锁机制只存于程序之中，文件系统可理解为共享资源
4.数据库锁三大类：表锁、间隙锁、行锁；行锁分四种：共享锁、排他锁、意向共享锁、意向排他锁，互斥锁间需竞争等待
5.innodb意向锁实现比较简单，特指表锁，页级别的意向锁没有实现，意向锁释放时机不是事务提交之后
6.mysql新版提供了锁监控，由事务表，锁表，锁等待表共同实现，用来观察锁等待以及死锁发现
7.一致性非锁定读指不读加锁的数据，读取多版本快照的数据，读取那个版本需要根据事务的隔离级别确定
8.数据库排他锁抢占会阻塞，超时未能抢到锁可抛异常回滚数据，锁超时配置参数能防止死锁

行多版本并发控制（事务隔离级别）

1.行数据可被多个事务读写操作，某时刻，行数据在内存有多个版本，由此带来的并发控制就是多版本并发控制
2.读未提交-脏读、不可重复读、幻读；读已提交-不可重复读、幻读；可重复读-幻读
3.更新丢失：事务之间发生ABA写操作或者AB-AC写操作，会存在丢失更新操作的风险

innodb事务实现

1.四大特性：原子性、一致性、隔离性、持久性
2.redoLog日志保证原子性和持久性、undoLog保证一致性和一定的隔离性

MYISAM和INNODB

1.MYISAM只有表锁
2.MYISAM不支持外键
3.MYISAM不支持事务，没有行锁和MVCC，强调性能
4.MYISAM非聚集索引，需要二次回表才能定位数据

主从复制

1.主服务器有BinLog日志和从服务器有BinLog和RelayLog日志
2.外部sql更新操作会被BinLog线程记录到BinLog
3.IO线程会把主的BinLog日志复制到从的RelayLog日志
4.SQL线程会把从的RelayLog日志中的sql信息重新执行一遍

三大范式

1.第一范式：表的每一列都不可再拆分，例如（中国广东，能拆成国家和省份）
2.第二范式：表可以包含除了外键外，还可以包含外键关联的额外信息（冗余）
3.第三范式：表之间的信息通过外键关联，不能包含除外键外的其他信息

Innodb下的sql更新操作

1.mysql面临更新操作来临，需要先查询更新的数据进内存，然后修改内存的数据，再写回硬盘
2.Innodb更新操作一定开启事务，若没有手动开启则会自动开启和提交事务
3.Innodb为了性能考虑和事务控制引出了两个文件redo日志和undo日志
4.undo日志记录事务来临时，数据修改前的状态，为了应对异常修改事件做事务回滚
5.redo日志关联一个redoBuffer缓存，更新sql可先缓存进redoBuffer，事务提交可持久化到redo日志
6.因为redo日志存在，Innodb更新操作就可以被延迟执行，这样就可以提高了更新的并发请求
7.sql更新操作会被其他查询操作触发，因为查询可使目标数据加载进内存
8.由于上述操作，更新sql时减少了必要的先查数据操作，减少了IO磁盘次数
9.当然因为redoBuffer的关系，如果事务没提交时，更新sql没有持久化到redo日志，所以sql会丢失，但没关系，这个是脏sql，毕竟没提交事务