持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第11天，点击查看活动详情

mysql面试题

1.事务隔离级别

• 读未提交：隔离级别最低，允许读取其他事务已修改而未提交的数据，会产生脏读、幻读、不可重复读问题。
• 读已提交：只能读到已提交的数据，解决脏读问题，存在幻读、不可重复读问题。
• 可重复读：mysql默认隔离级别，同一字段数据多次读取的数据都是一致的，除非是当前事务修改数据，解决了不可重复读问题，但依然存在幻读问题（可采用间隙锁解决）。
• 可串行化：各事务串行执行，互不干扰，即必须等一个事务完成下一个事务才允许执行，不存在脏读、幻读、不可重复读问题。

2.MySql性能优化

• 使用explain执行计划检查where条件是否命中索引列
• 检查是否有不必要的查询列
• 索引设计是否合理，不要设置区分度不大的索引列（如：性别）
• 数据量小的表可以不建立索引
• 检查是否出现索引失效，索引失效的几种情况：
  • 查询条件索引列使用函数
  • 查询条件没有用到索引列
  • 最左前缀原则，使用左like、<>、!=、not in、not exists、is null（表设计尽可能不要存在空列）
  • 索引列字段类型不一致（如：age int类型，where age='20'）
  • 查询条件索引列使用了运算符号
  • 使用or条件查询，部分情况导致索引失效(or连接的是不同字段索引失效)
  • 数据库本身不支持索引

3.MySql为什么使用InnoDB作为默认存储引擎

• InnoDB使用的是聚簇索引，每个表都只会有一个聚簇索引，数据行与键值存放数据行相同，聚簇索引叶子节点存放了索引值又存放行数据，即索引值与数据行是存放在一起的。
• InnoDB创建表后生成的文件：
  • frm：创建表语句
  • idb：表数据与索引文件

4.MySql索引底层为何使用B+Tree

• hash结构：能提供单个数据行的操作性能，但是无法很好的支持范围查询和排序的情况，最终会导致全表扫描。
• 二叉树：如果数据分布不均匀，查询所要遍历的树节点数量不稳定，甚至可能存在与链表相同的树结构，最终导致跟全表扫描没有太大区别。
• 平衡二叉树：解决了二叉树数据分布不均匀的缺点，但由于每个节点保存的数据太小了，这会导致树的深度增加，直接就会影响I/O次数，从而影响到查询效率。
• 多路平衡查找树（BTree）：BTree节点存储的是数据，一个节点就存储不了太多的数据，同时在进行连续数据查找时也会产生更多的随机I/O。
• B+Tree非叶子节点存储的是索引，索引占用空间小，每个非叶子节点可以存储很多的索引值，并且非叶子节点之间都是通过指针相连的，在顺序查找时也能减少随机IO的产生；B+Tree叶子节点的数据是顺序排列的，便于范围查找。

5.慢SQL产生的原因

• 分析执行计划，索引命中率等。
• 没有索引或没有用到索引（索引失效），设计缺陷。
• 内存不足、网络速度慢。
• 是否查询的数据量过大，没有采用多次分页查询或其他方法降低查询数据量。
• 查询是否返回了不必要的列。
• 检查是否存在锁或者死锁问题。
• I/O吞吐量小。
• 活动的用户查看，读写竞争资源。

6.MySql事务特性 ACID

• 原子性：一个事务操作不可被分割，要么全部执行，要么全部回滚不执行。
• 一致性：事务使数据从一种状态转变为另一种状态。
• 隔离性：在事务提交之前，不允许把该事务对数据的改变提供给其他事务。
• 持久性：事务提交后，数据最终的结果将会持久化到磁盘。

7.MySql主从复制

• 主库会将数据变更写入binlog日志，从库连接到主库后，会有一个IO线程，将主库的binlog日志拷贝到自己本地，写入一个relay中继日志，接着从库会有一个SQL线程从中继日志读取binlog，然后执行binlog中的内容，也就是在本地执行了一遍SQL。
• 主从延迟：
  • 主库的从库太多
  • 从库硬件配置比主库差
  • 慢SQl语句过多
  • 主从库之间的网络延迟
  • 主库读写压力大

8.MySql的binlog有几种格式

• statement模式：binlog记录的就是sql语句。优点是不需要记录每一行数据的变化，减少了binlog的日志量，节约IO，提高性能。缺点是在某些情况下会导致主从库数据不一致（可能存在一些函数的无法被记录复制）。
• row模式：不记录每条sql语句的上下文信息，仅需记录哪条数据被修改，修改成什么样子，解决了statement模式下主从数据不一致的问题，但会产生大量日志，特别是alter table的时候会使日志量暴涨。
• mixed模式：以上两种模式的混合式，一般的操作使用statement的模式保存binlog，对于statement模式无法复制的操作使用row模式保存binlog，MySql会根据执行的sql选择日志保存方式。

9.MVCC多版本并发控制

• 读取数据时通过一种类似快照读的方式将数据保存下来，这样读锁就和写锁不冲突了，不同的事务session会看到自己特定版本的数据，即版本链
• MVCC只在RC（读已提交）和RR（可重复读）两个隔离级别下工作。RU（读未提交）总是读取最新的数据行，而不是符合当前事务版本的数据行，串行化则会对所有读取的行加锁，不存在事务交互。
• 聚簇索引有两个隐藏列
  • trx_id：用来存储每次对某条聚簇索引记录进行修改时的事务id。
  • roll_pointer：每次对哪条聚簇索引有修改记录的时候，都会把老版本写入undolog日志中，roll_pointer指针存的就是记录上一个版本的地址。（插入操作没有该属性，因为新数据没有老版本数据）。
• 开启事务时创建readview，readview维护当前活动事务id，即未提交的事务，排序生成一个数组，访问数据时，获取数据中的隐藏字段事务id，对比readview：
  • 如果在readview左边（比readview都小），可以访问（即该事务已提交）
  • 如果在readview右边或中间，不可访问，获取roll_pointer取上一个版本重新比较（在右边意味着该事务在readview之后生成的，在readview中间说明该事务还未提交）
• 区别： RC隔离级别下的事务在每次查询都会生成一个独立的readview，而RR隔离级别则在第一次读的时候生成readview，之后读都复用之前的readview。

10.MyCat分库分表

• mycat通过定义路由规则（分片算法：hash、取模、范围），通过传递的sql语句做路由处理，将判断sql传递到哪些节点去执行。

• 分片查询mycat会根据分片算法计算出sql需要发送到哪些节点执行。非分片字段mycat无法计算路由，便会发送到所有节点执行。

• 分页查询：

  • 对于不指定排序的分页查询，mycat会将分页sql发送到各节点执行，最终分页结果取决于哪个DB节点最先返回结果给mycat。

• 指定排序的分页查询，mycat会将分页sql发送到各节点执行，最终会进行最小堆运算，计算出结果集中最小的记录返回。

• 带偏移量的排序分页查询，mycat会进行sql改写，将limit m, n改写成limit 0, m+n，再发送到各节点执行，最终进行最小堆运算，计算出最小的结果集，在进行limit m,n。

  例：select * from table order by id limit 5,2;改写成select * from table order by id limit 0,7;

• join操作，如有有两个表进行分库后，相关数据可能分布在不同的DB节点上，mycat在执行各个节点单独执行分片sql时，查询不出正确的结果集。如果需要进行表join操作，必须保证所有DB节点两个表关联字段具有相同的数据分布。