MySQLの问与答

①B树与B+树的区别

   MySQL中的B+树

②Innodb中的B+树是如何产生的

InnoDB

Innodb--->MySQL中最常用的存储引擎

段（segment）
表空间是由不同的段组成的，在 InnoDB 1.1 版本之前，一个 InnoDB 只支持一个回滚段，支持 1023 个并发修改事务同时进行，在 InnoDB 1.2 版本，将回滚段数量提高到了 128 个，也就是说可以同时进行128 * 1023个并发修改事务。

区（extent）
区是由连续页组成的空间，每个区的固定大小为 1MB，为保证区中页的连续性，InnoDB 会一次从磁盘中申请 4 ~ 5 个区，在默认不压缩的情况下，一个区可以容纳 64 个连续的页。但是在开始新建表的时候，空表的默认大小为 96KB，是由于为了高效的利用磁盘空间，在开始插入数据时表会先利用 32 个页大小的碎片页来存储数据，当这些碎片使用完后，表大小才会按照 MB 倍数来增加。

页（page）
页是 InnoDB 存储引擎的最小管理单位，每页大小默认是 16KB，从 InnoDB 1.2.x 版本开始，可以利用innodb_page_size来改变页大小，但是改变只能在初始化 InnoDB 实例前进行修改，之后便无法进行修改，除非mysqldump导出创建新库，常见的页类型有：数据页、undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页以及压缩的二进制大对象页等。

行（row）
行对应的是表中的行记录，每页存储最多的行记录也是有硬性规定的最多16KB/2-200，即 7992 行，其中 16KB 是页大小。

索引结构：聚簇索引/主键索引--索引数据与用户数据存在一起

③Innodb是如何支持范围查找能走索引

④为何要遵守最左前缀原则才能利用到索引

create index idx_t1_bcd on t1(b,c,d);

explain select * from t1 where c=1 and d=1 and b=1;   使用bcd索引，跟where后面字段顺序无关

⑤范围查找导致索引失效原理

select * from t1 where b>1;  无法走bcd索引【慢】，全表扫描

select * from t1 where b>5; 可以，在上图中，先找到b>5的bcd索引再回表比直接全表扫描快

⑥覆盖索引的底层原理

select b from t1 where b>1;  走bcd索引，不用回表【覆盖索引--->索引中bcd已经包含了所有所需读取的列b的查询方式】

select b,c,d,a from t1 where b>1;  同上，主键也在bcd索引里

⑦索引扫描底层原理

select b from t1; 走bcd索引，可以从主键索引的叶子节点遍历 or bcd索引的叶子节点遍历，bcd索引存储的数据小，遍历更快。

select c from t1;也是走bcd索引，虽不符合最左前缀原则，但走bcd叶子节点遍历也比主键索引遍历快

⑧order by导致索引失效

explain select * fron t1 order by b,c,d;走全表扫描【内存排序，不用回表】，如走bcd索引，取出的数据不需排序，但返回前得回表去取完整数据

explain select b fron t1 order by b,c,d; 走bcd索引，不需回表，不用排序，直接返回

⑨MySQL中数据类型转换

create index idx_ti_e on t1(e);   【e字段是varchar类型】
explain select * from tl where a=1; --1        explain select * from tl where e='1';--1
explain select * from tl where a= '1'； --1        explain select * from tl where e= 1; --0

--1 走e索引 相等；--0 不走 不等    MySQL字符转换，非数字字符均转换为0，只要涉及到隐式转换都不走索引

⑩对字段操作导致索引失效

where e= 1  对字段强制类型转换/+-后，e索引的B+树失效

11）MySQL锁

按锁粒度分类:
1.行锁:锁某行数据，锁粒度最小，并发度高

2.表锁:锁整张表，锁粒度最大，并发度低
3.间隙锁:锁的是一个区间
还可以分为:
1.共享锁:也就是读锁，一个事务给某行数据加了读锁，其他事务也可以读【加读锁】，但是不能写【加写锁】

2.排它锁:也就是写锁，一个事务给某行数据加了写锁，其他事务不能读【同上】，也不能写

还可以分为:
1.乐观锁:并不会真正的去锁某行记录，而是通过一个版本号来实现的

2.悲观锁:上面所的行锁、表锁等都是悲观锁
在事务的隔离级别实现中，就需要利用锁来解决幻读

12）MySQL慢查询该如何优化

1. 检查是否走了索引，如果没有则优化SQL利用索引
2. 检查所利用的索引，是否是最优索引
3. 检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据
4. 检查表中数据是否过多，是否应该进行分库分表了
5. 检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源

13)聚簇与非聚簇索引的区别

都是B+树的数据结构

• 聚簇索引:将数据行存储在索引结构中，即索引树的叶子节点上。这样，查找特定值或范围的查询非常高效，因为可以直接从索引结构中获取所需的数据行。在MySQL中，主键索引就是一种聚簇索引，因为主键的值是唯一的，可以作为聚簇索引的键值。
• 非聚簇索引:将索引结构和数据行分开存储。索引树的叶子节点只包含索引键值和指向对应数据行的指针，而数据行存储在表的数据页中。当使用非聚簇索引进行查询时，需要先通过索引树获取到对应数据行的指针，再通过指针访问实际的数据行。

区别

1. 存储方式：聚簇索引将数据行存储在索引结构中，而非聚簇索引将数据行和索引结构分开存储。
2. 查找效率：聚簇索引在查找特定值或范围的查询时非常高效，而非聚簇索引需要进行额外的物理读取，相对来说更慢。
3. 数据行修改的影响：聚簇索引在插入和删除数据时，可能会导致数据的移动，影响性能；而非聚簇索引只需要更新索引树和数据页即可。

优缺点

聚簇索引的优势：

1. 查找数据更快：由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问。
2. 适合排序查询：聚簇索引中，数据是按照索引键的顺序物理存储的，数据库不需要额外的排序操作。

聚簇索引的劣势：

1. 维护成本高：特别是插入新行或者主键被更新导致分页时。
2. 数据存储稀疏：使用随机ID作为主键时，数据存储稀疏，可能比全表扫描更慢。

非聚簇索引的优势：

1. 辅助索引使用主键作为指针，减少了行移动或数据页分裂时的维护工作。

非聚簇索引的劣势：

1. 查找效率较低：需要进行额外的物理读取。

14）分库分表

分库分表，一文讲透！ - 雨梦山人 - 博客园 (cnblogs.com)

面试官问我怎么做分库分表？这是一份全面的实战解答 - 技术栈 (jishuzhan.net)

15）存储拆分后如何解决唯一主键问题

25 存储拆分后，如何解决唯一主键问题？ (lianglianglee.com)

(8 封私信) 存储拆分后，如何解决唯一主键问题？ - 知乎 (zhihu.com)、

16）ACID靠什么保证的

A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql

C一致性由其他三大特性保证、程序代码要保证业务上的一致性

I隔离性由MVCC来保证

D持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log恢复

17）MVCC（多版本并发控制）

一种用于提高数据库并发性能的技术，特别是在处理高并发读写操作时非常有效。它通过维护数据的多个版本来避免读写冲突，使得读操作无需阻塞写操作，写操作也不会影响读操作。MVCC的主要优点包括：

• 提高并发性能。

• 解决读一致性问题。

• 支持事务隔离级别。

        在MySQL中，MVCC通过InnoDB存储引擎实现，使用版本链和读视图来管理数据的可见性和一致性。

(8 封私信) 全网最详细MVCC讲解，一篇看懂 - 知乎 (zhihu.com)

18）MySQL主从同步原则

MySQL 主从同步（Master-Slave Replication）是一种数据同步机制，允许主库（Master）上的数据自动复制到从库（Slave）上。其基本原理是：主库将所有变更记录到二进制日志（binlog）中，从库通过 I/O 线程读取主库的二进制日志并写入中继日志（Relay Log），然后从库的 SQL 线程读取中继日志并在从库执行操作语句，从而实现主从数据同步。

主从同步的基本流程

1. 主库写入数据：主库接收客户端请求，将写操作记录到二进制日志中。

2. 从库复制日志：从库通过 I/O 线程将主库的二进制日志传输到自己的中继日志。

3. 从库执行日志：从库读取中继日志，通过 SQL 线程执行日志中的操作。

轻松搞定MySQL主从同步！详解【一主一从】实现及案例演示-CSDN博客

(二十四)全解MySQL之主从篇：死磕主从复制中数据同步原理与优化-阿里云开发者社区 (aliyun.com)

19）事务的隔离级别

在数据库中，事务的隔离级别决定了一个事务在多大程度上与其他并发事务隔离，要解决的实际并发事务的问题：脏读、不可重复读、幻读。

• 脏读：一个事务读取了另一个未提交事务的数据。

• 不可重复读：一个事务在不同时间读取到的数据不一致。

• 幻读：一个事务在读取数据时，另一个事务插入了新的数据。

四种隔离级别从低到高：

1、读未提交（Read Uncommitted）

在这种隔离级别下，一个事务可以读取另一个未提交事务的数据。这种隔离级别最低，可能会导致脏读（Dirty Read），即一个事务读取了另一个事务未提交的数据。这种隔离级别虽然并发处理能力高，但很少用于实际应用。

2、读已提交（Read Committed）

在这种隔离级别下，一个事务只能读取另一个已提交事务的数据。这样可以避免脏读，但可能会导致不可重复读（Non-repeatable Read），即同一个事务在不同时间读取到的数据不一致。大多数数据库系统默认使用这种隔离级别。

3、可重复读（Repeatable Read）

这是MySQL的默认隔离级别。在这种隔离级别下，一个事务在开始读取数据时，不允许其他事务修改该数据，从而避免了不可重复读问题。但是，这种隔离级别可能会导致幻读（Phantom Read），即一个事务在读取数据时，另一个事务插入了新的数据。

4、串行化（Serializable）

这是最高的隔离级别，通过强制事务串行化顺序执行，避免了脏读、不可重复读和幻读问题。但是，这种隔离级别效率低下，可能导致大量的超时现象和锁竞争。

(8 封私信) MySQL事务隔离级别和实现原理（看这一篇文章就够了！） - 知乎 (zhihu.com)