mysql 相关

合集 - 面试(7)

1.java 基础相关2024-08-112.spring 相关2024-08-113.分布式微服务2024-08-11

4.mysql 相关2024-08-11

5.线程与多线程2024-08-116.队列mq 相关2024-08-117.缓存redis 相关2024-08-13

收起

1. mysql 索引

1.1 mysql 为什么使用b+ 树做索引

mysql 的索引主要有hash 索引以及B+树索引
对于索引，一般就是为了加快数据访问而存在的，所以索引要求的就是效率
而mysql 索引数据一般是存储在磁盘的，磁盘与内存的交互存在IO, 而IO是硬件问题，没法解决，剩下的能解决的也就是IO次数以及IO量
所以对于索引的设计，最好就是采用分块读取的方式，例如innodb 每次读取都是16K
确定了索引的读取方式之后，就是索引的类型，对于索引，其就是为了根据一部分数据来确定其他数据，所以最适合的结构就是kv
而kv的话，又包含了hash和各种树。
所以mysql 存在hash 索引, 但是hash 的话一般是通过key 计算Hash 值，这样没法做范围查询，因为对key 做了二次计算。
然后是树，树的结构主要分为二叉树和多叉树等。对于二叉树，其只有两个分支，所以当数据量过多的话，就容易特别深，而对于 mysql ，
其存储的数据量是没有上限的，所以二叉树等树结构首先会被pass掉。
最后也就是多叉树，对于多叉树，有B树，B+树，N叉树等，对于N叉树，其节点虽然可以存储更多的键值对，但是其的平衡维护起来比较复杂，不像B树和B+树，
可以很轻松的维护平衡，对于mysql查询数据来说，会存在范围查询，而N叉树的范围查询比起B和B+树来说也是一大弱项，所以N叉树被pass
然后是B树和B+树，对于B树，其非叶子节点不只存储索引信息，还会存储数据信息，而B+树，非叶子节点只存储索引信息，同时对于其叶子节点还有左右指针，方便范围查询
所以B树存储索引来说，存储的索引数据会少，就会增加IO次数，同时减少IO量，并且B树没有B+树的叶子节点的左右指针，范围查询的效率会更低一点
所以基于此，mysql选择了B+树作为索引

1.2 mysql 索引类型

mysql 的索引主要分为普通索引、唯一索引、主键索引、组合索引、全文索引等
又或者可以分为单列索引和组合索引，普通，唯一和主键都是属于单列索引的
普通索引没有任何限制、唯一索引要求唯一，但是允许空值，主键索引要求唯一不允许空值
组合索引就是多个字段创建的索引，遵循最左匹配原则

从存储角度来说，索引又分为聚簇索引和非聚簇索引, innodb 的索引就是聚簇索引，myisam 就是非聚簇索引
聚簇索引，索引和数据存放在一起，非聚簇索引，索引和数据分开存放，索引执向数据地址
聚簇索引的叶子节点包含有主键、事务id、回滚指针、以及余下的列，聚簇索引的查询比非聚簇索引快，
非聚簇索引查询的时候，需要先查询一遍索引文件，得到索引后在根据索引查找对应的数据，二聚簇索引的叶子节点直接指向需要查找的数据行

1.3 myisam 为什么比innodb 树、查询快

myisam 的查询比innodb 快的原因是因为myisam 没有事务以及并发控制，同时myisam 的索引存放的是数据对应的物理偏移量
可以直接定位到数据，而innodb 的话，存在一个mvcc + 事务，同时如果查询时没有索引覆盖，则还需要进行回表查询，所以相对来说myisam 的查询快于innodb 

1.4 索引失效

索引失效的情况有多种，比如组合索引没有遵循最左匹配原则，关联查询时，数据类型不一致，模糊匹配使用了通配符，where 使用了函数操作，
in 的数据量过多，表数据过少，is not null 等都会导致索引失效

1.5 为什么排序和分组建议索引

当数据库执行分组操作时，需要对数据进行排序和分组，如果分组列有索引，数据库可以之间利用索引的有序性快速完成排序和分组，而无序对整张表进行排序
并且通过索引可以快速定位到满足条件的数据范围，因为索引通常都是经过排序的数据结构，减少了需要扫描的数量，在数据量较大时，可以显著降低磁盘IO操作

1.6 为什么要选择合适的数据类型

1. 可以节省磁盘空间
2. 提高查询性能，较小的数据类型在处理时通常较快，因为需要操作的数据量更少，可以减少数据传输时间，内存使用和CPU计算时间，从而提高查询和更新速度
3. 较小的数据类型可以使索引更加紧凑，在索引结构中能存储更多的键值，从而提高索引性能和减少索引的存储空间
4. 更小的数据类型能在数据库缓冲池中存储更多的行数据，增加缓存命中率，减少磁盘IO操作

1.7 执行计划

执行计划就是通过mysql提供的一个关键字， explain + sql 来查看这个sql 的具体执行计划
对查询结果主要关注几个重要字段，如 type、key、rows、等
key 指的是预期索引名称
rows 预期扫描行数
type 是预期访问方式, system 最好，只有一条数据, all 最差，进行全表扫描. 
  1. system 只有一条数据
  2. const 通过主键或者唯一进行等值匹配
  3. eq_ref 通过主键或者唯一进行匹配
  4. range 使用索引进行范围查询
  5. index 全索引扫描
  6. all 全表扫描 
此外还有key_len 预期索引字段长度
filtered 返回结果行数占扫描行数百分比
extra 关于查询的额外信息，是否覆盖，是否使用了临时表存储中间结果等

2. mysql 锁

2.1 mysql 乐观锁和悲观锁

乐观锁: lock share in mode
悲观锁: for update

2.2 mysql 锁都有哪些

mysql 中的锁包含有共享锁，排他锁，意向共享锁，意向排他锁，记录锁，间隙锁，临键锁等
共享锁允许其他事务读取但不允许修改，排他锁不允许其他事务获取任何类型的锁
共享锁常用语读操作，排他锁用于写操作
意向锁是表级锁，用于快速判断表中的是否有被锁定的记录，可以提高加锁的效率，即当事务需要对表中的某些行加共享锁或者排他锁时，先在表级别上添加相应的意向锁，
在处理行级锁时，其他事务可以通过查看表级的意向锁，快速判断是否有行被锁定，而无序检查表中的每一行

间隙锁和临键锁，这两个主要时在可重复读的隔离级别防止幻读产生，而对范围查询进行锁定，临键锁就是间隙锁+记录锁

2.3 间隙锁和临键锁

在RR隔离级别下，为了解决幻读问题，会存在间隙锁和临键锁，在删改之后会产生，如果在并发情况下，存在两个事务都执行了删改操作，
就会持有间隙锁，当insert 时，又要先插入间隙获取意向锁，就会导致冲突死锁.

2.4 mvcc

多版本并发控制，通过读视图的不同产生时机以及事务id+undolog 来进行多版本并发控制，
在mvcc 中存在3个隐式字段, row_id, tax_id 和 undo_id , 主键id, 当前事务id, 回滚id,
读视图, read view 就是在查询时生成的视图，不同的隔离级别其生成时机不同，例如RC每次查询都会生成，RR是事务的第一次查询才会生成
通过读视图的三个隐式字段，最小事务ID，活跃事务id列表以及下一个待分配的事务id，和当前事务id进行匹配，以达到可见性算法，做到多版本并发控制
同时 通过临键锁+间隙锁，解决了RR级别下的幻读问题

2.5 读视图可见性算法

当前事务id 首先和读视图最小事务id 比较, 当起小于时，说明当前读视图生成时，当前事务已经提交，对其可见
否则判断当前事务id是否大于下一个待分配的事务id, 当其大于时，说明当前读视图生成时，当前事务未提交，对其不可见
否则判断是否在活跃事务列表中，在的话，说明读视图生成时，当前事务尚未提交，对其不可见，否则就是对其可见
当不可见时，如果此条记录存在回滚ID，则会迭代此回滚id，直到找到对其可见的记录信息

3. mysql 主从复制

3.1 mysql 主从复制

mysql 的主从复制，都是通过binlog  完成的
每次在写入数据的时候，都会生成binlog 日志存储到本地磁盘中
从机通过IOThread 去定时访问binlog 日志，如果binlog 有变化了，将其拿到从机，放到从机的中继日志中
之后开启一个sqlThread 进行解析执行，保证数据一致

3.2 mysql主从复制延时

主从复制出现延时的情况，主要可能有几个方面的问题，首先是主库的写入压力过大，或者从库执行的事务过大，又或者是从库的硬件性能问题，线程配置问题
又或者是主从之间的网络问题
避免这种情况，就要首先对住哭的查询和写入进行优化，减少大事务的使用，合理创建索引，提升从库的硬件配置，确保主从之间的网络连接稳定，将大事务拆分为多个较小的事务，
减少从库复制时间，合理分配从库的复制线程等

3.3 binlog 和redo log 的两阶段提交

确保了在binlog 出现异常后，事务也能通过redo log 恢复对应数据
当发送一条更新Sql 后，由执行其去执行此sql, 
执行其找到存储引擎去获取此sql 对应的行，搜索此数据，内存中存在，之间返回执行器，否则磁盘读取到内存，在返回
执行器拿到数据，根据sql进行改变，得到新数据，调用引擎写入数据。
引擎更新到内存中，同时会写入到redolog 文件，此时redolog 处于prepare 状态，即告知执行器可以提交事务
执行器生成操作的binlog, 将其写入磁盘，调用引擎的事务提交接口，同时将redolog 的prepare 改为commit 状态

4. mysql 事务

4.1 事务的特性

ACID 原则，原子性，隔离性，一致性和持久性
事务的所有操作要么成功要么失败回滚
多个事务之间互不干扰
事务执行前后，其状态不变
事务提交，修改永久保存