数据库八股
三大范式
第一范式
数据库表的所有字段都是不可分解的元组。第二范式
满足第一范式的情况下，所有非主键字段完全依赖整个主键。第三范式
满足第二范式情况下，所有非主键字段均不依赖其他非主键字段。sql语句执行过程
解析：检查语法和语义，生成解析树。
预处理：参数化处理（如果有参数）。
优化：查询优化器选择最优的执行计划。
执行：按照执行计划，读写数据，对数据进行整理。
结果返回：将结果集返回给客户端。
清理：释放资源，记录日志。
| 名称 | 介绍 | 优点 | 缺点 |
|---|---|---|---|
| 表级锁 | 对当前整张表加锁 | 实现简单，加锁块 | 并发性能差 |
| 行锁 | 对某行加锁。若存在索引，则锁记录在索引上；若没索引，innoDB会创建一个隐藏的聚簇索引加锁。 | 锁粒度小，并发高 | 加锁开销大，加锁慢，可能死锁 |
| Gap 锁 | 间隙锁，锁定索引记录的间隙，防止其他事务在两个索引记录中间插入新的记录。 | 可防止幻读，保证事务一致性，隔离性 | 阻塞在间隙中的数据插入 |
| Next-key lock | Next-Key 锁是 Gap 锁和 行锁的组合。它不仅锁定索引记录本身，还锁定记录之前的间隙。相比gap锁，就相当于从开区间*(1,3)变为闭区间(1,3]* | 可防止幻读，保证事务一致性，隔离性 | 阻塞在间隙中的数据插入 |
共享锁&排他锁
共享锁
读锁，允许多个事务同时读取同一数据，但不允许其他事务修改。
查询操作，读多写少排他锁
写锁，仅允许一个事务在执行阶段访问数据，不允许其他事务读取、修改。
更新删除操作，写多读少乐观锁&悲观锁
乐观锁
假设事务执行过程中数据不会被修改，仅在提交事务时会检查数据是否被修改*(时间戳、版本号)*
加锁时间短，并发性能好，适合读多写少悲观锁
假设事务执行过程中数据可能被修改，在读取数据时就加锁，事务结束释放锁。
加锁时间长，并发性能差，适合写频繁场景，有效避免数据冲突如何解决死锁
预防
- 按顺序加锁
- 使用事务隔离级别
- 减少事务持有锁的时间，将大事务拆成多个小事务
- 读多写少的场景，使用乐观锁
- 避免事务嵌套
- 处理
- 设置事务的超时时间*(innodb_lock_wait_timeout)*，超时自动会滚
- 回滚重试，程序检测到死锁异常时，手动回滚重试
- 数据库会存在死锁检测，检测到以后会自动选择一个事务回滚
| 名称 | 介绍 |
|---|---|
| InnoDB | 适合需要事务支持和高并发写操作的场景 |
| MyISAM | 适合读多写少的场景，对事务支持要求不高，支持全文索引 |
| MEMORY | 适合需要高速读写的临时数据 |
| ARCHIVE | 适合存储历史数据或日志数据 |
| CSV | 适合数据导入导出和交换 |
| BLACKHOLE | 适合测试和开发环境，日志记录 |
| FEDERATED | 适合分布式数据库和跨服务器查询 |
| NDB | 适合高可用性和高并发的实时数据处理场景 |
mysql的默认事务存储引擎，支持事务ACID(原子性、一致性、隔离性、持久性)、行级锁、外间约束、多版本并发控制(MVCC)与聚簇索引等特点。
用于加速数据库数据检索的一种数据结构，索引检索数据不需要扫描整个表，即可直接定位到对应数据。
- 能提高查询速度
- 需要占用额外空间
- 影响写操作性能(需要维护索引)
按数据结构：
| name | memo |
|---|---|
| B+Tree | innodb默认使用 范围查询、精确匹配 |
| 哈希 | memory引擎显式支持 等值查询，不支持范围查询 |
| 全文 | 搜索引擎、文档管理、知识库 全文搜索，支持文本匹配 |
| 位图 | 数据仓库 低基数列 |
| B-Tree | MyISAM默认使用 等值、范围查询、排序分组 |
按数据存储角度：
| name | memo |
|---|---|
| 聚簇索引(主键索引) | innodb的主键索引，叶子结点包含整行数据，索引顺序与数据顺序相同，范围查询、排序性能好 |
| 非聚簇索引(非主键索引，二级索引，辅助索引) | 叶子结点一般存的是指向数据行的地址以及指定列，索引顺序与数据顺序无关，等值查询性能好 |
业务使用角度：
| name | memo |
|---|---|
| 聚集索引 | 所有数据行都有，直接包含数据行，决定数据存储顺序，每个表唯一，范围查询、排序性能好，维护成本高 |
| 稀疏索引 | 不是所有数据行都有，一般只包含部分列，适用数据分布不均匀的场景，维护成本低 |
hash索引&自适应hash索引：
| name | memo |
|---|---|
| hash索引 | 仅memory引擎显式支持，每个数据行都对应一个hash值，存储在索引中，同时维护一个hash表，可根据hash值在hash表中找到对应数据行的地址。由于hash的特性，hash索引不支持范围查询、排序以及部分索引列匹配 |
| 自适应hash索引 | innodb引擎对频繁使用的索引会在内存中基于B-Tree索引上在创建一个hash索引，用于快速查询 |
- 先根据条件查询到对应索引，若索引覆盖(查询的列全在辅助索引的数据列中)，直接从索引返回数据
- 若不满足索引覆盖，根据索引中的数据行地址，查询实际的数据行，返回数据
联合索引(复合索引，包含多个列的索引)，
- 解析查询条件，与所需筛选的列
- 选择索引
- 依据最左匹配原则(按索引顺序与查询条件进行匹配)，逐级分次匹配到满足条件的数据行
- 若满足索引覆盖则直接返回数据，若不满足则需要回表查询
主键索引叶子结点存储了整个数据行的数据，所以不会存在回表查询，而非主键索引可能会出现索引不覆盖的情况需要回表查询
红黑树等二叉搜索树的出度*(分叉个数、子结点数码)*最多为2，会导致树高相对更高，在查询时会io次数更多，效率更低。
b+Tree的数据全在叶子结点中，叶子结点是一个有序连表，而bTree结点数据分布在所有节点中。
高效的范围查询：叶子节点的有序链表使得范围查询非常高效。
减少磁盘 I/O：内部节点只存储键值和指针，同样空间下可以存储更多结点，减少树的高度，减少磁盘访问次数。
更好的缓存性能：更多的节点可以被缓存，提高查询性能。
高度平衡：所有叶子节点在同一层，保证了稳定的查询性能。
高效的插入和删除操作：主要影响叶子节点，减少内部节点的调整。
explain关键字用于分析sql语句的执行情况，可以通过他进行sql语句的性能分析。
type：表示连接类型，从好到差的类型排序为
- system：系统表，数据已经加载到内存里。
- const：常量连接，通过索引一次就找到。
- eq_ref：唯一性索引扫描，返回所有匹配某个单独值的行。
- ref：非主键非唯一索引等值扫描，const或eq_ref改为普通非唯一索引。
- range：范围扫描，在索引上扫码特定范围内的值。
- index：索引树扫描，扫描索引上的全部数据。
- all：全表扫描。
key：显示MySQL实际决定使用的键。
key_len：显示MySQL决定使用的键长度，长度越短越好
Extra：额外信息
Using filesort：MySQL使用外部的索引排序，很慢需要优化。
Using temporary：使用了临时表保存中间结果，很慢需要优化。
Using index：使用了覆盖索引。
Using where：使用了where。
简述优化过程
- 通过慢日志定位执行较慢的SQL语句
- 利用explain对这些关键字段进行分析
- 根据分析结果进行优化
垂直分库：将不同业务模块数据分离到不同数据库
水平分库：按照一定规则将数据分散到多个数据库
垂直分表：将表中不同字段按业务逻辑分离
水平分表：按特定规则将表数据分到多个表
| type | memo |
|---|---|
| redo log | 存储引擎级别的log（InnoDB有，MyISAM没有），该log关注于事务的恢复.在重启mysql服务的时候，根据redo log进行重做，从而使事务有持久性。 |
| undo log | 是存储引擎级别的log（InnoDB有，MyISAM没有）保证数据的原子性，该log保存了事务发生之前的数据的一个版本，可以用于回滚，是MVCC的重要实现方法之一。 |
| bin log | 数据库级别的log，关注恢复数据库的数据。 |
- redo log是InnoDB引擎特有的，只记录该引擎中表的修改记录。binlog是MySQL的Server层实现
的，会记录所有引擎对数据库的修改。 - redo log是物理日志，记录的是在具体某个数据页上做了什么修改；binlog是逻辑日志，记录的是这
个语句的原始逻辑。 - redo log是循环写的，空间固定会用完；binlog是可以追加写入的，binlog文件写到一定大小后会切
换到下一个，并不会覆盖以前的日志。
InnoDB通过redo log保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crashsafe。
事务
简述
是数据库中用于确保数据一致性和完整性的机制。具体来讲，事务是一连串数据库操作的集合，这些操作要么全部执行，要么全部不执行，以保证数据库的一致状态。
主要特性如下(ACID):特性 memo 原子性 事务是一个不可分割的最小工作单位，事务中的所有操作要么全部成功，要么全部失败。如果事务中的任何一个操作失败，整个事务都会回滚，撤销所有已经完成的操作 一致性 事务必须保证数据库从一个一致状态转换到另一个一致状态。事务执行前后，数据库的完整性约束必须保持不变 隔离性 多个事务并发执行时，每个事务的执行不应受到其他事务的影响。事务的隔离性确保了事务之间的独立性，避免了修改丢失、脏读、不可重复读和幻读等问题 持久性 一旦事务提交，其对数据库的更改将是永久的，即使系统发生故障也不会丢失
脏读：读取了另一个事务尚未提交的数据。
不可重复读：在一个事务中，多次读取同一数据行得到的结果不一致。
幻读：在一个事务中，多次执行相同的查询，但结果集不同。
修改丢失、脏读、不可重复读和幻读等。
| category | memo |
|---|---|
| 读未提交 | 最低的隔离级别，允许一个事务读取另一个事务尚未提交的数据，可能会导致脏读。 |
| 读已提交 | 一个事务只能读取另一个事务已经提交的数据，可以避免脏读，但可能会导致不可重复读和幻读。 |
| 可重复读 | 在同一个事务中，多次读取同一数据的结果是一致的，可以避免脏读和不可重复读，但可能会导致幻读。 |
| 序列化 | 最高的隔离级别，完全隔离并发事务，避免了脏读、不可重复读和幻读，但性能较差。 |
多版本并发控制（MVCC）通过生成和管理数据行的多个版本，减少了锁的竞争，提高了数据库的并发性能。MVCC 允许多个事务同时读取和写入数据，而不会相互干扰，从而支持更高效的并发操作。
读提交和可重复读都基于MVCC实现，有什么区别
读已提交：每次读取操作都会看到最新的已提交数据，可能产生不可重复读。
可重复读：事务在开始时会看到一个快照，避免不可重复读，但可能产生幻读。
InnoDB如何保证事务的原子性、持久性和一致性
- 利用undo log保障原子性。该log保存了事务发生之前的数据的一个版本，可以用于回滚，从而保证事务原子性。
- 利用redo log保证事务的持久性，该log关注于事务的恢复。在重启mysql服务的时候，根据redo log进行重做，从而使事务有持久性。
- 利用undo log+redo log保障一致性。事务中的执行需要redo log，如果执行失败，需要undo log 回滚。
通过binlog完成数据复制，提高可用性，拓展读取能力，数据备份。
其中：
主服务器（Master）：负责处理所有的写操作（INSERT、UPDATE、DELETE等），并将这些操作记录到 Binlog 中。
从服务器（Slave）：从主服务器获取 Binlog，并应用这些日志来保持与主服务器的数据同步。
在master上开启、设置binlog
在master上创建一个复制的用户并授权
在slave上的配置文件中设置
1
2
3
4
5[mysqld]
server-id=2
relay-log=mysql-relay-bin
log-slave-updates=1
read-only=1在slave设置主从关系
1
2
3
4
5
6CHANGE MASTER TO
MASTER_HOST='master_host',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=12345;启动slave的I/O线程从master获取binlog事件，启动SQL线程应用binlog事件