数据引擎
分为innoDB，MyISAM等多种，常用的是InnoDB
innodb：支持事务、支持外键、支持行级锁
最小表空间略小于10M，最大表空间取决于页大小(page size)
myisam：不支持事务，不支持外键、支持表级锁
| 功能 | innodb | myisam |
|---|---|---|
| 事务 | √ | × |
| 外键 | √ | × |
| 哈希索引 | √ | × |
| b+树索引 | √ | √ |
| 全文索引 | √ | √ |
| 行级锁 | √ | × |
| 表级锁 | √ | √ |
区别：
1、存储结构
innodb：两种格式，.frm文件存储表的定义；.idb存储数据和索引。
myisam：三种格式，.frm文件存储表的定义；.MYD存储数据；.MYI存储索引。
2、最小锁粒度
innodb：行级锁，并发写入性能高。
myisam：表级锁，高并发种写操作存在性能瓶颈。
3、索引类型
innodb：聚簇索引，索引和数据不分开。
myisam：非聚簇索引，索引和数据分开存储，索引保存的是数据的指针。
4、主键必须
innodb：必须有主键。
myisam：可以没有主键。
5、表的行数
innodb：需要扫描整张表才能返回。
myisam：具体行数在表的属性中，查询时直接返回。
数据库架构
基础架构
分为三层：连接层、服务层、存储引擎层。
连接层：负责客户端连接的管理，包括验证身份、权限校验、连接管理等。可以通过数据库连接池来提升连接的处理效率。
服务层：负责查询解析、优化、执行等操作。这一层包含查询解析器、优化器、执行计划生成器、缓存(如查询缓存)、日志模块等。
在这一层，sql语句会经过解析、优化器优化，然后转发到存储引擎执行并返回结果。
存储引擎层：负责数据的实际存储和提取。
mysql的数据存储形式
mysql是以表的形式存储数据的，表空间的结构由段、区、页、行组成。
1、段： 表空间由多个段组成，常见的有数据段、索引段、回滚段等。
创建索引时会创建两个段，数据段和索引段，
数据段用来存储叶子节点中的数据；
索引段用来存储非叶子节点的数据；
回滚段包含了事务执行过程中用于数据回滚的旧数据。
2、区： 段由一个或多个区组成，区是一个连续的页，通常包含64个连续的页，就是1M的数据。
使用区 (而非单独的页) 进行数据分配可以优化磁盘操作，减少磁盘寻道时间，特别是在大量数据进行读写时。
3、页： 页是innodb存储数据的基本单元，标准大小为16KB，索引树上的一个节点就是一个页。
也就意味着数据库每次读写都是以16KB为单位的，一次最少从磁盘中读取16KB的数据到内存中，一次最少写16KB的数据到磁盘。
4、行： innodb采用行存储方式，意味着数据按照行进行组织和管理，行数据可能有多个格式，比如COMPACT、REDUNDANT、DYNAMIC等。
mysql8默认的行格式是DYNAMIC，有COMPACT演变而来，意味着这些数据如果超过了页内联存储的限制，则会被存储在溢出页中。
溢出信息相关资料，后续要补充进来： 了解 MySQL的数据行、行溢出机制吗？
优化思路
1、避免不必要的列
要：select projName
不要：select *
2、分页优化
延迟关联、书签
当数据量巨大时，传统的limit和offset可能会导致性能问题，因为数据库需要扫描 offset+limit数量的行。
延迟关联
延迟关联适用于 从多个表中获取数据，且主表数据较多的情况。首先从索引表中检索出需要的行id，然后再根据这些id去关联其他的表获取详细信息。
select a.name,b.name
from project a
join enterprise b on a.epid = b.id
order by a.create_time
limit 100000,20;
select a.name,b.name
from (
select name from project order by create_time limit 100000,20
) as a
join enterprise b on a.epid = b.id
order by a.create_time;
思路就是对主表进行分页查询，仅获取需要的行，根据行数据关联其他信息，减少了不必要的join操作。
书签
书签方法通过记录上一次查询返回的最后一行的某个值，然后下一次查询从这个值开始，避免了扫描大量不需要的行。
select id
from project
order by id
limit 100000,20;
select id
from project
where id > last_max_id -- 假设该id是上次查询最后一行的id
order by id
limit 100000,20;
3、索引优化
索引覆盖、避免使用(!= / <>)、适当使用前缀索引、避免列上函数运算、正确使用联合索引。
3.1、索引覆盖
使用非主键索引查询数据时需要回表，但如果索引的叶节点中已经包含要查询的字段，那么就不会再回表查询了，这就叫覆盖索引。
例：当前project表只有索引name
select code from project where name = '项目'
这个查询会查找name为‘项目’的数据，然后回表获取code。
可以把name和code建立为联合索引，那么查询结果就可以从索引中获取。
3.2、避免使用(!= / <>)
!= 或者 <>会导致mysql无法操作索引，从而导致全表扫描。
3.3、使用前缀索引
当列的类型为字符串(varchar,text,longtext)等等时，有时候会索引很长的字符串，就导致索引变得巨大，此时可以将字符串提取出一个前缀建立索引。
语法：create index idx_xxxx on table_name(column(n)) ;
n为要索引的前缀长度。
3.4、避免列上的函数运算
where子句中直接对列使用函数会导致索引失效
3.5、正确使用联合索引
联合索引的创建应遵循最左匹配原则，即:索引的顺序应根据列在查询中使用频率和重要性安排。
例：
select name from project where code='a' and address='a' and sort=1;
建立对应的索引为:
alter table project add index index_name(code,address,sort);
4、join优化
优化子查询、小表驱动大表、适当增加冗余字段、避免join太多的表
4.1 优化子查询
where子句中的子查询，往往会导致sql的性能问题，因为它们可能会为每一行外层查询执行一次子查询。
select name from project where id in (select id from business)
应使用join代替子查询。
select a.name from project a join business b on a.id = b.id;
4.2 小表驱动大表
join操作时，应尽量让行数较少的表(小表)，驱动行数较多的表(大表)，这样可以减少查询过程中需要处理的数据量。
比如left join，左边是驱动表，所以A应小于B，这样建立连接的次数少了，查询速度就快了。
select name from A left join B;
4.3 适当增加冗余字段
在高频查询的场景下，可以适当增加冗余字段来避免join操作。
4.4 避免使用join关联太多的表
阿里巴巴的开发规范上有写过规定，不要join关联太多的表，最多不要超过3张表。
因为join太多表会降低查询的速度，返回的数据量也会非常大，不利于后续的处理。
业务逻辑允许的情况下，可以考虑将复杂的join分解成多个简单查询，然后在应用层组合查询的结果。
5、排序优化
利用索引扫描做排序
mysql生成有序结果的方式有两种，一种是对结果集进行排序，另一种是按照索引顺序扫描得出的自然有序的结果。
因此在设计索引的时候需要充分考虑到排序的需求。
6、union优化
条件下推、union all替代union
6.1 条件下推
是指将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化。
例如：
select * from (
select * from a
union
select * from b
) as obj
where obj.id = 1;
select * from a where id = 1
union
select * from b where id = 1;
union all替代union，需要看情况使用，union all不会去重，union会去重。
日志
一共有6种：
错误日志(error log) ：记录mysql服务器启动、运行或停止时出现的问题。
慢查询日志(slow query log) ：记录执行时间超过long_query_time值的所有sql语句。这个值是可配置的，默认是关闭状态，用来识别和优化慢sql。
一般查询日志(general query log) ：记录所有mysql服务器的连接信息及所有的sql语句，不论这些语句是否修改了数据。
二进制日志(bin log)： 记录了所有修改数据库状态的sql语句，以及每个语句的执行时间。如insert、update、delete等，但不包括select和show这类操作。
重做日志(redo log)： 记录了innodb表的每个写操作，不是sql级别的，而是物理级别的，主要用于崩溃恢复。
回滚日志(undo log)： 记录数据被修改之前的值，用于事务的回滚。
bin log
bin log是物理日志，会在磁盘上记录下数据库的所有修改操作，以便进行数据恢复和主从复制。
- 当发生数据丢失时，binlog可以将数据库恢复到特定的时间点。
- 主服务器(master)上的二进制日志可以被从服务器(slave)读取，从而实现数据同步。
binlog包括两类文件：
- 二进制索引文件(.index)
- 二进制日志文件(.00000*)
binlog默认是没有启用的。要启用的话，需要在mysql配置文件(my.cnf或my.ini)设置log_bin的参数。
log_bin = mysql-bin # 开启binlog
redo log
redolog是innodb存储引擎所持有的一种日志， 用于记录事务的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。
可以做数据恢复并且提供crash-sage能力。
当有增删改相关的操作时，会先记录到innodb中，并修改缓存页中的数据，等到mysql闲下来的时候才会真正的将redolog中的数据写入到磁盘中。
redolog文件是固定大小的，从头开始写，写到末尾就又回到开头循环写，
比如：可以配置为一组4个文件，每个文件的大小是1GB，那么总共就可以记录4GB的操作数据。
所以，如果数据写满了，但是还没有来得及将数据真正的刷入磁盘中，那么就会发生内存抖动现象，从肉眼的角度来观察就是mysql宕机了一会儿，此时就是在刷盘了。
undo log
undolog是innodb存储引擎的日志，用于保证数据的原子性，保存了事务发生之前的数据的一个版本，也就是说记录的是数据，是修改之前的数据，可以用于回滚。 同时可以提供多版本并发控制下的读(MVCC)。
主要作用：
-
- 事务回滚
- 实现多版本控制(MVCC)
事务
mysql事务的四大特性是什么？
事务是一条或者多条sql语句组成的执行单元，要么全部执行成功，要么全部失败，不会出现部分执行的情况。
事务具有四个基本特性，也就是通常所说的ACID特性，即原子性、一致性、隔离性和持久性。主要作用是保证数据库操作的一致性，
原子性：原子性意味着事务中的所有操作要么全部完成，要么全部不完成，他是不可分割的单位。如果事务中的任何一个操作失败了，整个事务都会回滚到事务开始之前的状态，如同这些操作从未被执行过一样。
隔离性：隔离性意味着并发执行的事务是彼此隔离的，一个事务的执行不会被其他的事务所干扰。也就是事务与事务之间井水不犯河水。
隔离性主要是为了解决事务并发执行时可能出现的文件，如脏读、不可重复读、幻读等。
数据库系统通过事务隔离级别（如读未提交、读已提交、可重复读、串行化）来实现事务的隔离性。
一致性：确保事务从一个一致的状态转换为另一个一致的状态。
持久性：事务一旦提交，它对数据库所做的更改就是永久性的。即使发生系统崩溃，修改的数据也不会丢失，数据库也能恢复到最近一次提交的状态。
索引
索引的分类？
主键索引、唯一索引、普通索引、全文索引
主键索引：表中每行数据唯一标识的索引，强调列值的唯一性和非空性。当创建表时，id列被指定为主键索引，mysql会自动为这个列创建一个聚簇索引(主键索引一定是聚簇索引)。
唯一索引：保证数据列中每行数据的唯一性，但允许有空值。
普通索引：基本的索引类型，用于加速查询。
全文索引：特定于文本数据的索引，用于提高文本搜索的效率。
为什么innodb要使用B+树作为索引？
B树是一种自平衡的多路查找树，和红黑树、二叉平衡树不同，B树的每个节点可以有m个子节点，而红黑树和二叉平衡树只有2个。
B+树的非叶子结点只存储键值，不存储数据，而叶子结点存储了所有的数据，并且构成了一个有序链表。
这样做的好处是，非叶子节点上由于没有存储数据，就可以存储更多的键值对，再加上叶子结点构成了一个有序链表，范围查询时就可以直接通过叶子结点间的指针顺序访问整个范围内的所有记录，而无需对树进行多次遍历。查询的效率更高。
B+树的页是单向链表还是双向链表？如果大值向小值检索，如何操作？
叶子结点是双向链表连接的。
如果要从大值向小值检索，可以按照以下步骤：
- 定位到最右侧节点：首先，找到包含最大值的叶子结点。这通常通过从根节点开始向右遍历树的方式实现。
- 反向遍历：一旦定位到了最右侧的叶子结点，可以利用叶节点间的双向链表向左遍历。
为什么MongoDB使用B树，而Mysql使用B+树？
B树的特点是：每个节点都存储数据，相邻的叶子节点之间没有指针链接。
B+树的特点是：非叶子节点只存储索引，叶子节点存储数据，并且相邻的叶子结点之间有指针链接。
但由于B树的节点之间没有指针链接，所以并不适合做范围查询，因为范围查询需要遍历多个节点。
而B+树的叶子节点之间有指针链接，所以适合做范围查询，因为可以直接通过叶子节点间的指针顺序访问整个范围内的所有记录，而无需对树进行多次遍历。
Mysql属于关系型数据库，所以范围查询会比较多，所以采用了B+树；但MongoDB属于非关系型数据库，在大多数情况下，只需要查询单条数据，所以MongoDB选择了B树。
Hash索引和B+树索引区别是什么？
| 区别 | B+树索引 | Hash索引 |
|---|---|---|
| 范围查询 | 可以 | 不可以 |
| order by 排序 | 支持 | 不支持 |
| 联合索引的最左侧原则 | 支持 | 不支持 |
| 等值查询效率 | 一般 | 高 |
| 模糊查询 | like 'aa%'可以起到索引优化 | 无法进行 |
聚簇索引和非聚簇索引的区别？
- innodb采用的是聚簇索引，如果没有显示定义主键，innodb会选择一个唯一的非空列作为隐式的局促索引，如果这样的列也不存在，innodb会自动生成一个隐藏的行id作为聚簇索引。这意味着数据与主键是紧密绑定的，行数据直接存储在索引的叶子节点上。
- myisam采用的事非聚簇索引，表数据存储在一个地方，而索引存储在另一个地方，索引指向数据行的物理位置。
联合索引的叶子节点存的什么内容？
比如有一个联合索引idx_c1_c2_c3(c1是主键，c2和c3列)，那么叶子节点存储的事c2、c3索引列的值和c1主键列的值。这样当查询时，可以先通过联合索引找到对应的主键值，然后再通过主键值找到完整的数据行。
覆盖索引是什么？
覆盖索引是指索引能够覆盖到要查询的所有列。
锁
mysql有哪几种锁？
面试题
日志篇
1、binlog写入在哪一层？
binlog在服务层，负责记录sql语句的变化。它记录了所有对数据库进行更改的操作，用于数据恢复、主从复制等。
2、有了binlog为什么还要undolog和redolog？
binlog主要用于数据恢复和主从复制。 它记录了所有对数据库执行的修改操作( 如insert、update、delete )， 以逻辑日志的形式保存，binlog是mysql的server层提供的日志，独立于存储引擎。
redolog主要用于数据持久化和崩溃恢复。redolog是innodb存储引擎特有的日志，用于记录数据的物理修改，确保数据库在崩溃或异常宕机后能够恢复到一致状态。
undolog主要用于支持事务回滚和多版本并发控制(MVCC)。undolog是innodb存储引擎提供的逻辑日志，用于记录数据的逻辑操作，如更新，删除前的数据快照。
当一个事务在mysql中执行是，redolog、undolog、binlog共同协作确保数据的可靠性和一致性：
-
- 事务启动时，undolog开始记录修改前的数据快照，以便在发生错误或显式回滚时恢复数据。
- 数据被修改时，innodb会将修改记录到redolog中，同是也会生成相应的undolog。
- 事务提交时，innodb首先将redolog刷入磁盘，然后再将整个事务操作记录到binlog中。这一个过程称为“两阶段提交”，确保binlog和redolog的一致性。
- 如果数据库发生崩溃，innodb会使用redolog进行恢复，确保数据不会丢失。binlog则可以用来做主从复制或数据恢复到特定时间点。
3、为什么要两阶段提交？
如果采用“单阶段”进行提交，即要么先写入redolog，后写入binlog；要么先写入binlog，后写入redolog。这两种方式的提交都会导致原先数据库的状态和被恢复后的数据库的状态不一致。
4、说说redolog的工作机制？
redolog由两部分组成：ib_logfile0和ib_logfile1。这两个文件的总大小是固定的，默认情况下每个文件问48M，总共96M。它们以循环的方式写入，即当写满后，从头开始覆盖旧的日志。
每次修改数据时，都会生成一个新的日志序列号(log sequence number)，用于标记redolog中的日志位置，以确保数据恢复的一致性。
当一个事务对数据进行修改时，innodb会首先将这些修改记录到redolog中，而不是直接写入磁盘的数据文件。具体分为三步：
- 在缓冲池(buffer pool)中修改数据页。
- 将修改操作记录到redolog buffer中(这是内存中的一个日志缓冲区)。
- 当事务提交时，innodb会将redolog buffer中的数据刷新到磁盘上的redolog文件中，确保事务的持久性。
当mysql发生崩溃后，innodb会在重启时读取redolog，找到最近一次的检查点(checkpoint)，然后从该检查点开始，重放(replay)redolog中的日志记录，将所有已提交事务的修改重做一遍，恢复数据库到崩溃前的一致性状态。
6、binlog和redolog的区别？
binlog，即二进制日志，对所有存储引擎都可以用，是mysql服务器级别的日志。用于数据的复制、恢复和备份。
redolog主要用于保证事务的持久性，是innodb存储引擎特有的日志类型。
binlog记录的是逻辑sql语句，二redolog记录的是物理数据页的修改操作，不是具体的sql语句。
redolog是固定大小的。配置为一组文件，使用环形方式写入，旧的日志会在空间需要时被覆盖。binlog是追加写入的，新的事件总是被添加到当前日志文件的末尾，文件达到一定大小后，会创建新的binlog文件继续记录。
5、说说WAL?
WAL的核心思想是先写日志，再写数据，即在对数据进行任何修改之前，必须先将修改的日志记录(redolog)持久化到磁盘。
通过先写日志，确保系统在发生故障时可以通过重做日志恢复数据。
基础篇
一条查询语句如何执行？
1、客户端发送sql语句到mysql服务器。
2、mysql服务器的连接器处理请求，跟客户端建立连接、获取权限、管理连接。
3、解析器对sql语句进行解析，检查sql语法规则，确保引用的数据库、表和列都是存在的。并处理sql语句中的名称解析和权限验证。
4、优化器负责确定sql语句的执行计划，其中包括选择使用哪些索引，以及决定表之间的连接顺序等。
5、执行器调用存储引擎的api来进行数据的读写。
6、存储引擎执行，然后将执行结果返回给客户端。
7、客户端收到查询结构，完成这次查询请求。
一条更新语句怎么执行的？
更新语句的执行是Servier层个存储引擎层配置完成，数据除了要写入表中，还要记录相应的日志。
1、执行器先找引擎获取where条件之后的数据。存储引擎检索数据找到对应的数据。
如果这个数据所在的数据页本来就在内存中，就直接返回给执行器；
否则，需要先从磁盘读入内存，然后再返回。
2、执行器拿到引擎给的数据之后，把值set进去。得到新的数据，再调用引擎接口写入新的数据。
3、存储引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
4、执行器生成这个操作的binlog，并将binlog写入磁盘。
5、执行器调用引擎的提交事务接口，引擎把刚刚写入的redolog改成commit状态，更新完成。
