mysql面试

简历写到的：熟练mysql,具有丰富sql优化实战经验

sql优化思路

业务层面上

控制查询条件：例如我们系统中，查询订单信息除了统计之外基本不会查询所有订单信息的这种需求，所以我们一系统的订单列表都是控制查询条件在一个月内。还有就是例如我们订单列表信息，之前是关联多表查询的，比较慢（pcb），当时我也是和产品沟通，让他和业务方去沟通，如果是把一些信息放到详情里行不行，这样列表就能单表只差订单信息，拓宽额信息放详情里去展示。
技术层面上比较多可以讲的了
先说下自己项目遇到的一些优化场景（佐证自己丰富的实战经验）

• 元器件库存信息列表优化，单表优化，频繁用于查询元器件编号，更新时间建索引
• 元器件信息列表关联了四张表数据量一百多万（基本信息，拓展信息，库存信息，分类信息），优化方式，组合索引，元器件编号，和上下架状态建复合索引。理由where条件后面，平时业务查询把元器件件编号，状态为上架状态的组合起来查询。表之间的关联的字段建立索引
• 还有一些sql语句查询不规范的一些调整，命名两张表之间可以通过int类型的keyId去关联的有些人偏偏用字符串类型的uuid去关联查询。
• 以前遇到的情况，索引列上做函数运算，物料，几百万条数据。新增字段，写存储过程转换成统一格式，存到新字段，查询的时候通过Java代码，把格式统一转小写，全角转半角。然后新加的字段加索引。

技术上的优化思路

分析sql语句，看下写法上有没有什么优化
多用exists少用in,表连接的时候是小表驱动大表还是大表驱动小表。
可以是索引没用上，也可能是索引失效。
explain查看执行计划
参考：https://blog.csdn.net/shenlf_bk/article/details/122598041
主要关注id type key extra 这几个字段
id：sql执行顺序，大先执行，一样按顺序执行
type:有没有用上索引是否全表扫描，从最差到最好：all<index<range<ref<system
key:
extra:using temp,using filesort（用非索引列进行排序）,using index,usingwhere
索引失效场景及原因
索引列上做了函数运算
like双百分号模糊查询
大于小于
null
多用exists少用in
不用select *
避免使用子查询,子查询会产生临时表，用join代替
索引建立原则及原因
频繁更新的字段不适合
频繁查询的字段应建索引
点表建议索引个数不超过5个，索引是真实的一个文件，建太多的话索引文件很大，也会影响查询效率
select where order by gorup by中的字段建立索引，推荐建立联合索引。
对复杂的大sql进行拆分

索引为什么是B+树而不是B树或AVL树或红黑树

参考：这个还很好的说明了hashmap为什么用红黑树：https://www.cnblogs.com/tiancai/p/9024351.html
很好的说明了为什么用红黑树：https://www.cnblogs.com/jiading/articles/12571642.html
思路：二叉查找树-》AVL平衡二叉树-》红黑树-》B树-》B+树
其他不说了，本身设计就不是为了磁盘查找而设计，树的层数太高，磁盘IO次数多。
索引的查询效率：cpu和磁盘io次数，cup太快了时间忽略不计，并且我们这是IO密集型。B树的总体特点，B-和B+的区别，B+都存放在叶子节点，B所有节点都有。在IO每次读取的数据量一定的情况下，节点大就以为着更多的IO，这是其中一点。还有一点，sql查找区间查找比较多，B树分散到各个节点，不适合区间。
使用B+树而不是B数的原因
B+树节点大小更小，一次IO读入的节点数更多
B+树的数据都在叶子节点中，遍历和区间访问性能大幅提高
B+树查询效率稳定
使用B+树而不是AVL树、红黑树的原因
B+树的树高比AVL树、红黑树低，IO次数少
补充知识点

1. 先序遍历：根左右
2. 中序遍历：左根右
3. 后序遍历：左右根

sql语句在mysql中的执行过程

组件：连接查询分析优化执行
4. 连接器： 身份认证和权限相关(登录 MySQL 的时候)
5. 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）
6. 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
7. 优化器： 按照 MySQL 认为最优的方案去执行。
8. 执行器： 执行语句，然后从存储引擎返回数据

面试回答技巧
只说更新过程，因为更新需要先把数据查询出来。
1、连接器：账户密码，权限等进行校验
2、查询器：查询缓存中是否有数据，有直接返回（8之后移除了，不实用）
3、分析器：

• 词法分析：提取出一些关键字select,from 表名，查询条件等等这些关键字
• 语法分析：是否有语法错误

4、优化器
优化器按它认为最优的方案去执行,例如多个索引走哪个，多表之间查询的时候如何去关联。
5、执行器
执行前会先去验证用户有没有权限，如果有权限就会按照优化器确认的执行计划去执行。如果是增删除改的操作的话这个时候还会记录redolog（重做日志）来做数据库持久性的保证，这是InnoDB独有和还会记录binlog(归档日志)日志用于主从同步。为了保证这两个日志的一致性，会有一个二阶段提交的工程。过程大概是这样的：我们在执行更新操作的时候会调用引擎API吧数据保存在内存中，同事记录redolog,此时redolog进入prepare状态，然后告诉执行器执行完成了，随时可以提交，执行器收到通知后记录binlog，然后调用引擎接口，提交redolog为提交状态。

ACID靠什么保证

A：原子性由undo log （重做日志）日志保证，它记录了需要回滚的日志信息，事物回滚时撤销已经执行成功的sql
C：一致性是由其他三大特性保证、程序代码要保证业务上的一致性。
I：隔离性由MVCC保证
D：持久性由内存+redolog来保证，mysql修改数据同事在内存额redo log记录这次操作，宕机的时候可以从redo log回复

InnoDB redo log写盘，InnoDB事物会进入prepare状态，如果前面prepare成功，binlog写盘，再继续将事物日志持久化到binlog，如果持久化成功，那么InnoDB事物则进入commit状态（在redolog里面写一个commit记录）

redolog的刷盘会在系统空闲时进行

binlog\redolog\undolog

先总说特点，再说数据库更新过程中的两阶段提交（解决两份日志之间逻辑上一致问题），说可能失败的场景，然后解释一些失败后保存两份数据一致的逻辑。

• binlog:记录sql语句逻辑上的执行顺序，用于主从同步
• redolog:InnoDB特有，是事物ACID特性中持久性的保证，记录的是在某个数据页（mysql读取数据按页读取每页16KB）上做了什么修改。
• undolog:保障事物的原子性，事物回滚时候通过undolog来撤销已经执行的sql
• 两阶段提交：redolog写入被拆分成两个步骤一个是prepare一个是commit。过程是这样的：server层中执行器在执行的时候会调innoDB存储引擎这个时候会去写redolog日志，会把redolog日志变为prepare状态，然后返回告诉执行器可以执行提交。时候会去写binlog日志然后再调存储引擎执行commit提交。这个时候就存在两个地方可以失败，第一个地方是写binlog的时候发生异常。在执行恢复的时候发现redolog还处于prepare状态，则执行回滚。还有一种情况就是binlog写成功了，执行commit的时候失败了。这个时候拿redolog做 恢复时会去判断事物id,如果binlog和redolog里都有就会继续执行commit提交

存储引擎对MVCC的实现

看视频的解释

多版本并发控制：读取数据时通过一种类似快照的方式将数据保存下来，这样读锁和写锁不冲突了，不同的事物session看到自己特定的数据，版本链。

MVCC只在Read Commited和Repeatable Read两个隔离级别下工作。其他两个级别都和MVCC不兼容。

聚簇索引记录中有两个必要的隐藏列：
trx_id:用来存储每次对某条聚簇索引记录进行修改的时候的事物ID
roll_pointer:每次对那条聚簇索引记录有修改的时候，都会把老版本写入undolog日志中。这个roll_pointer就是存一个指针，它指向这条聚簇索引记录的上一个版本为位置，通过它来获得上一个版本的记录信息。（插入操作的undo日志没有这个属性，因为它没有老版本）

读已提交和可重复读的区别在于他们生成readView的策略不同。
开启事物的时候创建readView,readView维护当前获得的事物id,即未提交的事物id,排序生成一个数组（根据事物id从小到达排列）。
访问数据，获取数据中的事物id（获取的是事物id最大的记录），对比readView:如果在readview的左边（比ReadView都小），可以访问（在左边以为着该事物已经提交），如果在ReadView右边（比ReadView都大）或者在readView中，不可以访问，获取roll_pointer,取上一版本重新对比（在右边意味着，该事物在readView生成之后出现，在readView中意味着改事物还未提交）

读已提交隔离级别下的事物在每次查询的开始都会生成一个独立的readView,而可重复读则在第一次读的时候生成readView，之后的读都复用之前的ReadView。

这就是MVCC通过版本链，实现多版本，可并发读-写，写-读。通过ReadView生成策略不通过实现不同隔离级别。

自己的理解总结

回答的时候通过一个查询的过程把这三个东西串连起来
1、MVCC的实现依赖于：Read View、隐藏字段、undolog。
2、在select查询的时候会创建ReadView,它里面维护了所有已开启未提交的事物列表，按事物id从小到大排序。
3、访问数据的时候会获取隐藏字段trx_id也就是事物id里的最大id和readView里的事物ID对比如果是小于里面最小的则表示可以访问。如果是在列表里面或者是比列表里的最大的还大就会通过另一个叫roll_porinter的隐藏字段在undolog里面找到它的上一条版本记录信息。然后再和readview对比直到小于readView的最小值，这表示这条数据可以访问

索引失效的场景以及原因

https://juejin.cn/post/7056640917016412197#heading-3

索引下推

从 MySQL5.6 之后，有一个索引下推功能，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
大概原理是：截断的字段会被下推到存储引擎层进行条件判断（因为 c 字段的值是在 (a, b, c) 联合索引里的），然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升了性能。

• 组合索引不符合最左匹配原则：mysql会从左到右去匹配where查询条件的列和索引列顺序是否匹配，如果匹配则会根据列的信息过滤掉一批数据，如果遇到><between等范围的时候就会停止匹配
• 索引列上做了函数运算
• 隐式的类型转换（字符串查询的，条件查询是整型）
• like左百分或者双百分：应为B+树是按照索引值有序排列存储的，只能根据前缀进行比较