(格式md直接粘贴过来有点乱 有空一定改 需要md的可以留言)

一、基本数据结构

1、聚簇索引和非聚簇索引

1.1 数据存储方式

聚簇索引

• 数据存储与索引结构结合：在聚簇索引中，表的数据行存储在索引的叶子节点上。也就是说，索引的结构和数据存储是紧密结合的。
• 主键索引通常为聚簇索引：在许多数据库系统（如MySQL的InnoDB存储引擎）中，主键索引默认是聚簇索引。
• 排序存储：数据在物理存储上按照索引的顺序排列。例如，如果主键是id，那么数据在磁盘上会按照id的顺序存储。

非聚簇索引

• 数据存储与索引分离：非聚簇索引的叶子节点不存储数据行本身，而是存储指向数据行的指针（或主键值）。数据行仍然按照表的物理存储顺序存放，与索引结构无关。
• 辅助索引通常为非聚簇索引：在数据库中，除了主键索引之外的其他索引通常是非聚簇索引。
• 额外的查找步骤：查询时，非聚簇索引需要先找到索引的叶子节点，再通过指针或主键值回表查找实际数据，这称为“回表操作”。

1.2 查询效率

聚簇索引

• 范围查询效率高：由于数据在物理存储上是有序的，聚簇索引在范围查询（如WHERE id BETWEEN 10 AND 20）时效率很高，因为数据是连续存储的。
• 顺序扫描速度快：对于需要顺序扫描的查询，聚簇索引可以快速遍历数据。
• 减少I/O操作：由于数据和索引结合，查询时通常只需要访问索引树，减少了磁盘I/O操作。

非聚簇索引

• 点查询效率高：对于等值查询（如WHERE id = 10），非聚簇索引可以通过索引快速定位到数据行的指针，然后回表获取数据。
• 范围查询效率低：由于数据存储与索引分离，范围查询需要多次回表操作，效率相对较低。
• 额外的I/O开销：由于需要回表操作，非聚簇索引在某些情况下可能会增加I/O开销。

1.3 插入和更新性能

聚簇索引

• 插入性能：插入数据时，聚簇索引需要将数据插入到正确的位置（因为数据是有序的），可能会导致数据移动或页分裂，从而影响性能。
• 更新性能：更新聚簇索引的键值（如主键）可能导致数据行的物理移动，性能开销较大。

非聚簇索引

• 插入性能：插入数据时，非聚簇索引不需要移动数据行本身，只需要更新索引结构，性能相对较好。
• 更新性能：更新非聚簇索引的键值时，只需要更新索引结构，不需要移动数据行，性能开销较小。

1.4 适用场景

聚簇索引

• 适用场景：
  • 需要频繁进行范围查询的场景。
  • 数据访问模式以顺序扫描为主。
  • 表中有一个自然的、唯一的排序键（如主键）。

非聚簇索引

• 适用场景：
  • 需要频繁进行等值查询的场景。
  • 表中有多个查询条件，需要多个索引支持。
  • 不希望因为更新键值而导致数据行移动的场景。

2、InnoDB 存储引擎

InnoDB 是 MySQL 最常用的存储引擎，其底层数据结构主要包括以下部分：

2.1 B+树

InnoDB 使用 B+树 作为其主要的数据结构来组织数据和索引。B+树是一种多路平衡查找树，具有以下特点：

• 数据存储在叶子节点：B+树的所有数据都存储在叶子节点上，而非叶子节点只存储键值和指针。
• 顺序访问友好：B+树的叶子节点通过指针连接，便于范围查询和顺序扫描。
• 高度平衡：B+树的高度较低，能够快速定位数据，适合大规模数据存储。

InnoDB 的主键索引和辅助索引都基于 B+树实现：

• 主键索引（Clustered Index）：主键索引的叶子节点存储了完整的行数据。
• 辅助索引（Secondary Index）：辅助索引的叶子节点存储了索引列的值和主键值的映射。

2.2 行格式

InnoDB 支持多种行格式，用于存储表中的行数据：

• Compact：默认的行格式，紧凑存储数据。
• Dynamic：支持可变长度的列存储，适合存储大字段（如 VARCHAR、TEXT）。
• Redundant：早期的行格式，存储更多冗余信息。
• Compressed：用于压缩表数据，节省存储空间。

2.3 缓冲池

InnoDB 使用缓冲池来缓存数据和索引页。缓冲池是内存中的一个区域，用于提高数据读写的效率。当查询数据时，InnoDB 会优先从缓冲池中读取数据，而不是直接从磁盘读取。

2.4 日志文件

InnoDB 使用日志文件来支持事务的持久性和回滚：

• Redo Log：用于记录数据的修改操作，支持事务的持久性和恢复。
• Undo Log：用于支持事务的回滚和多版本并发控制（MVCC）。

3、MyISAM存储引擎

3.1 表文件结构

MyISAM 存储引擎的每个表由三个文件组成：

• .frm 文件：存储表的定义信息，包括表结构、字段定义等。
• .MYD 文件：存储表的实际数据，数据以行的形式存储。MyISAM 使用固定大小的行格式，每行占用固定空间，即使某些列未完全利用也会占用固定空间。
• .MYI 文件：存储表的索引信息。

3.2 B+树

MyISAM 使用B+树作为索引的底层数据结构，其索引特点如下：

• 主键索引和辅助索引：MyISAM 的主键索引和辅助索引结构类似，叶子节点存储索引值和数据文件的行指针。
• 非聚集索引：索引文件和数据文件是分离的，索引文件仅存储数据记录的地址（行指针），而不是数据本身。
• 范围查询效率高：叶子节点通过双向链表连接，支持高效的范围查询和顺序扫描

3.3 数据存储特点

• 固定行格式：MyISAM 使用固定大小的行格式，每行占用的空间是固定的，这可能导致某些列（如 VARCHAR 或 BLOB）存储可变长度数据时出现空间浪费。
• 动态行格式：对于可变长度的列，MyISAM 支持动态行格式，允许将部分数据存储在溢出页中。
• 空闲空间管理：MyISAM 在 .MYD 文件中维护空闲空间映射，用于跟踪未使用的空间，以便在插入新数据时复用这些空间。

4、InnoDB和MyISAM的区别

1. 事务支持

• InnoDB：
  • 支持事务：InnoDB 是事务型存储引擎，支持 ACID（原子性、一致性、隔离性、持久性）特性。
  • 回滚与恢复：通过 Redo Log（重做日志）和 Undo Log（回滚日志）实现事务的持久性和回滚功能。
  • 适用场景：适用于需要事务支持的场景，如金融系统、电子商务等。
• MyISAM：
  • 不支持事务：MyISAM 是非事务型存储引擎，没有事务支持功能。
  • 适用场景：适用于不需要事务支持的场景，如日志记录、数据仓库等。

2. 锁机制

• InnoDB：
  • 行级锁：InnoDB 默认使用行级锁（Row-Level Locking），在更新数据时只锁定涉及的行，而不是整个表。
  • 并发性能高：行级锁减少了锁的粒度，提高了并发性能，适合高并发的场景。
  • 死锁检测：InnoDB 支持死锁检测和自动回滚。
• MyISAM：
  • 表级锁：MyISAM 使用表级锁（Table-Level Locking），在更新数据时会锁定整个表。
  • 并发性能低：表级锁会导致并发性能较差，尤其是在高并发写操作时。
  • 适用场景：适合读多写少的场景，如数据仓库。

3. 索引实现

• InnoDB：
  • 主键索引（Clustered Index）：InnoDB 的主键索引是聚集索引，数据行存储在主键索引的叶子节点上。
  • 辅助索引（Secondary Index）：辅助索引的叶子节点存储主键值，通过主键值回表查询完整数据。
  • 索引效率高：主键索引的查询效率很高，但辅助索引查询可能需要额外的回表操作。
• MyISAM：
  • 非聚簇索引：MyISAM 的索引是非聚簇索引，数据文件和索引文件分开存储。
  • 索引效率：索引查询效率较高，但不支持聚集索引。

4. 数据存储

• InnoDB：
  • 表空间（Tablespace）：InnoDB 使用表空间存储数据，支持表空间的共享和独立存储。
  • 数据文件：默认情况下，所有表的数据存储在共享表空间 ibdata1 中，也可以配置为每个表一个文件（innodb_file_per_table）。
• MyISAM：
  • 文件存储：MyISAM 使用 .MYD 文件存储数据，.MYI 文件存储索引。
  • 数据文件独立：每个表的数据和索引文件是独立的。

5. 数据完整性

• InnoDB：
  • 外键支持：InnoDB 支持外键，可以维护数据的完整性。
  • MVCC（多版本并发控制）：通过 Undo Log 实现 MVCC，支持快照读，减少锁冲突。
• MyISAM：
  • 不支持外键：MyISAM 不支持外键，需要通过应用层逻辑维护数据完整性。
  • 无 MVCC：不支持多版本并发控制。

6. 性能表现

• InnoDB：
  • 写入性能：由于支持行级锁，写入性能在高并发场景下优于 MyISAM。
  • 读取性能：对于主键查询，InnoDB 的聚集索引效率很高，但对于全表扫描，性能可能不如 MyISAM。
• MyISAM：
  • 写入性能：在低并发场景下，写入性能较好，但在高并发写入时性能较差。
  • 读取性能：对于全表扫描和范围查询，MyISAM 的性能表现较好。

7. 安全性

• InnoDB：
  • 崩溃恢复：InnoDB 支持崩溃恢复，通过 Redo Log 和 Undo Log 恢复数据。
  • 数据安全性高：支持事务和 MVCC，数据安全性更高。
• MyISAM：
  • 崩溃恢复弱：MyISAM 不支持崩溃恢复，数据可能在崩溃后丢失。
  • 数据安全性低：不支持事务，数据安全性较低。

8. 适用场景

• InnoDB：
  • 高并发场景：适合需要高并发读写的应用，如在线交易系统、电子商务平台。
  • 事务支持：适用于需要事务支持的场景，如金融系统。
  • 数据完整性：适用于需要维护数据完整性的场景。
• MyISAM：
  • 读多写少：适合读多写少的场景，如日志记录、数据仓库。
  • 轻量级应用：适用于对性能要求不高、数据安全性要求较低的场景。

总结

• InnoDB 是事务型存储引擎，支持行级锁、事务、外键和 MVCC，适用于高并发、需要事务支持和数据完整性的场景。

• MyISAM 是非事务型存储引擎，支持表级锁，读取性能较好，但不支持事务和外键，适用于读多写少的场景。

  在选择存储引擎时，应根据实际需求和应用场景进行选择。目前，InnoDB 是 MySQL 的默认存储引擎，也是大多数生产环境的首选。

二、索引

1、索引类型

1. 主键索引

   当表没有指定主键的时候，mysql会隐式生成一个自增主键id的主键索引

2. 唯一索引

3. 普通索引

4. 组合索引

5. 全文索引

2、索引的优劣

优点：

1. 加快数据的检索速度

2. 加速表和表之间的连接

3. 优化查询范围，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间

4. 创建唯一性索引，可以保证数据库表中每一行数据的唯一性

5. 全文索引可以高效地支持文本内容的搜索，适用于对文本数据的复杂查询

6. 在支持外键的存储引擎（如InnoDB）中，索引可以用于实现外键约束，从而维护数据的完整性

缺点：

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

2. 索引需要占物理空间，如果要建立聚簇索引，那么需要的空间就会更大

3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的增删改速度

4. 需要定期分析和优化索引，以确保其性能。例如，删除冗余索引、调整索引列的顺序等

5. 如果索引列的值分布不均匀（选择性低），查询优化器可能不会使用索引，而是选择全表扫描

3、索引失效

在MySQL中，索引失效是指查询优化器没有选择使用索引，而是直接进行全表扫描，从而导致查询性能下降。

3.1 查询条件导致索引失效

• 使用函数或表达式

• 隐式类型转换

• 使用不等于操作符（<> 或 !=）

• 使用 OR 条件且未对所有字段加索引

• 使用 IS NULL 或 IS NOT NULL（某些情况下）

  如果查询条件是IS NOT NULL，且表中NOT NULL值的比例很高，优化器可能会认为全表扫描更高效

• 使用 LIKE 时通配符在开头

• 在 where 子句中使用参数，也会导致全表扫描。如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项

3.2 索引设计或使用不当

• 复合索引遵循最左前缀原则，如果查询未使用索引的最左列，索引会失效

• 组合索引中，如果包含范围条件（如 >、<、BETWEEN），后续的列索引无法被使用

3.3 数据特性导致索引失效

• 数据选择性过低，当查询的条件匹配大部分数据时（如索引列只有几个不同值），MySQL可能会选择全表扫描

• 统计信息不准确，如果表的数据变化频繁，索引的统计信息可能不准确，MySQL会错误地选择不使用索引，

  定期更新表统计信息，使用 ANALYZE TABLE

3.4 查询优化器的行为

• 小表扫描优先
  如果表数据量较小，MySQL优化器可能会选择全表扫描，而不是使用索引
• 不同字符集导致索引失效
  不同字符集之间的比较需要进行转换，可能导致索引失效

3.5 特殊情况

• 大量数据删除导致索引碎片化
  大量删除操作可能导致索引碎片化，增加查询时的I/O开销，甚至导致索引失效。对于碎片化的索引，使用 OPTIMIZE TABLE 或重建索引
• 未使用支持索引的存储引擎
  某些存储引擎（如 MEMORY）不支持某些字段类型的索引

 

4、索引优化

4.1 合理设计索引

• 选择合适的索引类型

• 为选择性高的列创建索引，因为这些列的值分布更均匀，索引的效率更高，如果某个列的值大部分是相同的，索引可能不会被优化器选择

• 尽量使用数字型字段，只含数值信息的字段尽量不要设计为字符型

• 复合索引遵循最左匹配原则

• 避免冗余索引

• 创建覆盖索引

  # 如果查询条件和返回字段都包含在索引中，MySQL可以直接从索引中获取数据，而无需回表查询
  # 这种情况下，索引覆盖了查询，避免了回表操作
  CREATE INDEX idx_cover ON table_name (column1, column2);
  SELECT column1, column2 FROM table_name WHERE column1 = 'value';
  

4.2 避免索引失效

分组统计可以禁止排序

4.3 索引维护

• 分析表

  # 更新表的统计信息，帮助查询优化器更好地选择索引
  ANALYZE TABLE table_name;
  

• 优化表

  # 重建表并清理碎片
  OPTIMIZE TABLE table_name;
  

  如果索引碎片化严重，可以通过重建索引来优化

  ALTER TABLE table_name DROP INDEX idx_fragmented;
  ALTER TABLE table_name ADD INDEX idx_fragmented (column_name);
  

• 监控索引的使用

  # 分析查询计划，查看是否使用了索引
  EXPLAIN SELECT * FROM table_name WHERE column1 = 'value';
  

5、评估索引优化效果

5.1 使用 EXPLAIN 分析查询计划

[id]:选择标识符	[select_type]:表示查询的类型。	[table]:输出结果集的表	[partitions]:匹配的分区[type]:表示表的连接类型	[possible_keys]:表示查询时，可能使⽤的索引	[key]:表示实际使⽤的索引	[key_len]:索引字段的长度	[ref]:列与索引的比较[rows]:扫描出的行数(估算的行数)	[filtered:按表条件过滤的⾏百分比	[Extra]:执行情况的描述和说明

5.2 比较查询执行时间

5.3 慢SQL日志分析

set global slow_query_log=on;
SET GLOBAL long_query_time = 0.2; -- 设置查询时间阈值为0.2秒

5.4 使用性能分析工具

5.5 检查索引的物理布局和存储开销

 

6、索引树高度

B+ 树高度与数据量的关系

1. 2 层 B+ 树：
   • 可以存储约 1 万到 20 万条 数据。
   • 适合较小的数据量，查询效率极高。
2. 3 层 B+ 树：
   • 可以存储约 100 万到 2000 万条 数据。
   • 这是大多数生产环境中常见的高度，能够满足大多数业务需求。
3. 4 层 B+ 树：
   • 可以存储约 1 亿到 60 亿条 数据。
   • 当数据量非常大时，B+ 树可能会扩展到 4 层，但此时性能会有所下降。

影响 B+ 树高度的因素

1. 页大小：
   • InnoDB 的默认页大小为 16KB。页大小越大，每个节点可以存储的索引条目越多，树的高度越低。
2. 索引键大小：
   • 如果索引键（如主键）类型较大（如 VARCHAR(255)），每个节点存储的条目会减少，导致树的高度增加。
3. 数据行大小：
   • 数据行大小（如每行 1KB 或 2KB）也会影响树的高度。行数据越大，叶子节点存储的条目越少。

实际应用

• 一般场景：对于大多数业务系统，B+ 树高度在 2 到 3 层 是较为理想的，能够满足高效查询。
• 大数据量场景：当数据量超过 2000 万条时，B+ 树可能会扩展到 4 层，此时需要关注查询性能。

 

三、事务

1、ACID

• 原子性：将一组操作作为一个事务，这些操作要么都做，要么都不做
• 一致性：事务开始前和结束后，数据库的完整性没有被破坏。如A扣了钱，B没收到
• 隔离性：同一时间只允许一个事务请求同一数据，不同的事务之间彼此没有干扰。
• 持久性：事务完成后，事务对数据库的所有更新将被保存，不能回滚，即使系统发生崩溃，事务执行的结果也不能丢失。

事务的 ACID 特性概念简单，但不是很好理解，主要是因为这几个特性不是一种平级关系：

• 只有满足一致性，事务的执行结果才是正确的。
• 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
• 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
• 事务满足持久化是为了能应对系统崩溃的情况。

 

2、事务的隔离级别

2.1 事务的并发问题

• 脏读：指在不同的事务下，当前事务可以读到另外事务未提交的数据。A读取B更新的数据后，B回滚，则A读到的是脏数据

• 不可重复读：B在A读取数据时提交修改，导致A多次读取同一数据的结果不一致

• 幻读：T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。A对数据库进行修改时B插入了新数据，导致A修改结束后发现还有记录未修改，如系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来

  小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

2.2 事务的隔离级别

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的 影响也越大。隔离级别：默认可重复读

 

3、mysql如何保证原子性（undo log）

mysql原子性的保证是利用了undo log。undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。

undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

如：

update一条数据，undo log中记录这条数据的旧值，update失败就换为旧值

insert数据时记录主键，失败了就delete主键

 

4、 mysql如何保证一致性（redo log）

Redo Log 是 InnoDB 存储引擎实现 WAL（Write-Ahead Logging） 机制的核心。WAL 机制的核心思想是：先写日志，再写数据

mysql持久性的保证是利用了Innodb 的redo log。Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。

redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。

当数据库宕机时，会将redo log中的内容恢复到数据库中，再根据undo log和binlog决定是提交数据还是回滚数据

 

5、undo log和redo log的区别

特性	Redo Log	Undo Log
记录类型	物理日志（数据页的修改）	逻辑日志（数据的旧版本）
记录内容	数据页的修改操作（如偏移量、修改值）	数据的旧版本（如字段的旧值）
格式	二进制格式	逻辑格式（可读性更强）
作用	支持事务持久性、崩溃恢复	支持事务回滚、MVCC
存储位置	InnoDB 存储引擎的 Redo Log 文件	InnoDB 存储引擎的 Undo 表空间
写入时机	事务修改数据时（WAL 机制）	事务修改数据时（记录旧版本）
恢复方式	物理恢复（直接修改数据页）	逻辑恢复（撤销操作）

 

6、 binlog和redo log的区别

特性	Binlog	Redo Log
存储位置	MySQL Server 层	InnoDB 存储引擎层
日志内容	记录逻辑操作（SQL 语句或行变化）	记录物理操作（数据页的修改）
格式	Statement/Row/Mixed	二进制格式（物理日志）
用途	主从复制、数据恢复、审计	事务持久性、崩溃恢复
写入时机	在事务提交时写入	在事务修改数据时写入（WAL 机制）
性能影响	写入磁盘，可能影响性能	通过缓冲区写入，性能更高
持久性	提供逻辑层面的持久性	提供物理层面的持久性
恢复范围	数据库级别的恢复	实例级别的恢复

Binlog 和 Redo Log 的协同工作

• 当事务提交时：
  1. Redo Log 先写入磁盘，确保事务的物理更改被持久化。
  2. Binlog 再写入磁盘，用于逻辑层面的持久化和主从复制。
• 如果系统崩溃：
  1. Redo Log 用于恢复实例级别的数据更改，确保数据文件的一致性。
  2. Binlog 无法恢复到崩溃前的精确状态，可用于进一步的数据恢复或主从复制。

 

四、锁机制

用户既能使用SQL Server的缺省设置也可以在select 语句中使用“加锁选项”来实现预期的效果

1、锁的分类

1.1 按锁的粒度分类

• 表锁（Table Lock）表锁是对整个表加锁，锁定范围是整个表

  LOCK TABLES table_name READ;  -- 加共享锁，允许读
  LOCK TABLES table_name WRITE; -- 加排他锁，允许写
  

• 行锁（Row Lock）行锁是对表中的单行或多行数据加锁，锁定范围是单行或部分行

  SELECT * FROM table_name WHERE id = 1 FOR UPDATE; -- 加行锁
  

• 页锁（Page Lock）页锁是对表中的一个数据页加锁，锁定范围是一个数据页（通常包含多行数据）

  在某些存储引擎（如 BDB）中使用页锁，但 MySQL 中的 InnoDB 不使用页锁。

1.2 按锁的性质分类

• 共享锁（Shared Lock，S 锁）共享锁是一种读锁，允许多个事务同时对同一资源加锁

  SELECT * FROM table_name WHERE id = 1 LOCK IN SHARE MODE;
  

• 排他锁（Exclusive Lock，X 锁）排他锁是一种写锁，只允许一个事务对资源加锁

  SELECT * FROM table_name WHERE id = 1 FOR UPDATE;
  

• 意向锁（Intention Lock）意向锁是一种表级锁，用于表示事务对表中某行或某页加锁的意图

  在 InnoDB 中，意向锁是自动管理的，不需要显式声明

1.3 其他锁类型

• 乐观锁（Optimistic Lock）乐观锁是一种基于版本号（Version Number）或时间戳的锁机制

  UPDATE table_name SET column = value, version = version + 1 WHERE id = 1 AND version = old_version;
  

• 悲观锁（Pessimistic Lock）悲观锁是一种基于锁的机制，假设冲突较多，因此在操作时直接加锁

  SELECT * FROM table_name WHERE id = 1 FOR UPDATE;
  

• 间隙锁（Gap Lock）间隙锁是 InnoDB 中的一种锁，用于锁定索引间隙，防止幻读

  当事务对一个范围内的数据进行操作时，InnoDB 会锁定该范围内的所有间隙，防止其他事务插入数据

  在插入数据时，InnoDB 会检查插入点的间隙锁，以避免插入冲突

• Next-Key 锁 是行锁和间隙锁的组合，锁定索引记录及其后面的间隙

 

2、多版本并发控制

MVCC（Multi-Version Concurrency Control，多版本并发控制） 是 MySQL InnoDB 存储引擎中实现高并发事务处理的一种机制。它通过为每行数据维护多个版本，使得不同的事务可以看到不同版本的数据，从而实现并发事务之间的隔离性

2.1. MVCC 的核心原理

1. 版本链：
   • 每行数据都有一个版本链，记录了该行数据的多个版本。每个版本包含创建时间和删除时间（未删除的版本删除时间为无穷大）。
   • 当事务修改数据时，InnoDB 会创建一个新的版本，并将其插入到版本链中。
2. 隐藏字段：
   • DB_TRX_ID：记录创建或修改数据的事务ID。
   • DB_ROLL_PTR：回滚指针，指向该版本的上一个版本。
3. 事务可见性判断：
   • 当事务读取数据时，InnoDB 会根据事务的开始时间和版本链中的版本信息，判断数据版本是否可见。
   • 可见性规则：
     • 如果版本的创建时间小于等于事务的开始时间，且删除时间大于事务的开始时间，则该版本对事务可见。
4. Undo Log：
   • Undo Log 用于记录数据的旧版本，支持事务回滚和快照读。
   • 它分为：
     • Insert Undo Log：记录插入操作，事务提交后可丢弃。
     • Update Undo Log：记录更新和删除操作，需保留以支持快照读。
5. Read View（读视图）：
   • 每个事务启动时生成一个 Read View，记录当前活跃事务的列表。
   • Read View 用于判断哪些版本对当前事务可见。

2.2 MVCC 的实现方式

1. 快照读（Snapshot Read）：
   • 读取的是事务开始时的数据版本，不加锁。
   • 通过 Undo Log 获取旧版本数据。
2. 当前读（Current Read）：
   • 读取最新版本的数据，并对读取的数据加锁。
   • 常见操作包括 SELECT ... FOR UPDATE、UPDATE、DELETE 等。

2.3 MVCC 的优势

1. 提高并发性能：
   • 读操作不需要加锁，避免了读写冲突。
2. 减少锁开销：
   • 通过版本控制减少了锁的使用，提高了效率。
3. 支持事务隔离：
   • 不同隔离级别下，MVCC 可以提供一致的数据读取。

2.4 MVCC 的局限性

1. 存储开销：
   • Undo Log 的存在会增加存储空间的占用。
2. 长事务问题：
   • 长事务可能导致 Undo Log 无法及时清理，增加性能开销。

 

五、视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

对视图的操作和对普通表的操作一样。

视图具有如下好处：

• 简化复杂的 SQL 操作，比如复杂的连接；
• 只使用实际表的一部分数据；
• 通过只给用户访问视图的权限，保证数据的安全性；
• 更改数据格式和表示。

 

六、执行原理

1. 客户端把语句发给服务器端执行

2. 服务端

   1. 查询高速缓存； 先在数据库的高速缓存中去查找是否存在相同语句的执行计划
   2. 语句合法性检查
   3. 语言含义检查
   4. 获得对象解析锁
   5. 数据访问权限的核对
   6. 确定最佳执行计划

3. 语句执行

   若被选择行所在的数据块已经被读取到数据缓冲区的话,则服务器进程会直接把这个数据传递给客户端,而不是从数据库文件中去查询数据

   若数据不在缓冲区中,则服务器进程将从数据库文件中查询相关数据,并把这些数据放入到数据缓冲区中

4. 提取数据

5. 返回数据

七、数据量极限

数据量阈值：根据业界经验，单表行数超过 500 万行 或单表容量超过 2GB 时，建议进行分库分表

 

八、语法

1、DCL 数据控制 (grant、revoke)

1.1 创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
# host：用户可以从哪个主机连接到MySQL服务器。可以是具体的主机名、IP地址，或者使用 % 表示任何主机。

# 创建一个userA用户可以在任意IP地址上登陆mysql
create user 'userA'@'%' identified by '123456';
# 修改用户密码
set password for 'userA'@'%' = '654321';
# 刷新权限(修改完密码，一定要刷新权限)
flush privileges;

1.2 授权

GRANT privileges_type ON database_name.table_name TO 'username'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

# 给用户授予test库user表所有的权限
grant all on test.tableA to 'userA'@'%';
# 给数据库中test库的所有表授权
grant all on test.* to 'userA'@'%';
# 授予普通数据用户，查询、插入、更新、删除数据库中test库tableA表数据的权限
grant insert,update,delete,select on test.tableA to 'userA'@'%';

1.3 取消授权

# 查看当前授权
SHOW GRANTS FOR 'userA'@'%';
# 撤销用户对所有数据库和表的权限：
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'userA'@'%';
# 撤销特定数据库的权限
REVOKE ALL PRIVILEGES ON test.* FROM 'userA'@'%';
# 撤销特定表的权限
REVOKE SELECT, INSERT ON test.tableA FROM 'userA'@'%';

2、DDL 数据定义(create、drop、alter)

2.1 创建表

create table student (# int 类型，不为空id INT NOT NULL,# 变长字符串类型，最长为 45 个字符，可以为空name VARCHAR(45) NULL,
);

2.2 修改表

//增加列
ALTER TABLE student
ADD col CHAR(20);//删除列
alter table student
drop COLUMN name;//删除表
drop table student

3、DML 数据操纵(insert、update、delete)

3.1 插入记录

insert into student (id,name) values (10,shuai)

3.2 更新记录

update student
set name=nice
where id=10

3.3 删除行

delete from student
where id=10

4、DQL 数据查询(select)

4.1 查询指定条数数据

//查询返回的第5-10条
select *
from student
limit 5,10;

4.2 排序

• ASC ：升序（默认）
• DESC ：降序

//排序
select *
from student
order by name desc;

4.3 查询指定值数据

where id = 10

//查询id大于10的数据
select *
from student
where id > 10//查询id在5到10之间的姓名
select name
from student
where id between 5 and 10//查询值为NULL
SELECT *
FROM student
WHERE col IS NULL;

4.4 查询后计算

//计算两列之和并以别名sum输出
SELECT col1 + col2 AS sum
FROM mytable;

4.5 最大值最小值平均值

sum() count() min() max() avg() lower() upper() length()

//获取age最大值
SELECT id,AVG(age)
FROM student;//获取不同年龄的平均值,AVG() 会忽略 NULL 行
//使用 DISTINCT 可以汇总不同的值
SELECT AVG(DISTINCT age) AS avg_age
FROM student;

4.6 分组

//以name分组统计
SELECT name, COUNT(*) AS num
FROM student
GROUP BY name;//以name分组统计后排序
SELECT name, COUNT(*) AS num
FROM student
GROUP BY name
order by num

//WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。
select name, count(*) as num
from student
where age >2
group by name
having num > 2

4.7 嵌套查询

SELECT *
FROM student
WHERE name IN (
SELECT name
FROM grade);

SELECT cust_name, (SELECT COUNT(*)FROM OrdersWHERE Orders.cust_id = Customers.cust_id)AS orders_num
FROM Customers
ORDER BY cust_name;

4.8 连接

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

连接可以替换子查询，并且比子查询的效率一般会更快。

可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。

4.8.1 内连接

又称等值连接，使用 INNER JOIN 关键字。

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;//或者不使用INNER JOIN
ELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;

4.8.2 自连接

SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.departmentAND e2.name = "Jim";

4.8.3 自然连接

内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列

SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;

4.8.4 外连接

分为左外连接(LEFT OUTER JOIN)，右外连接(RIGHT OUTER JOIN)以及全外连接，左外连接就是保留左表没有关联的行。

SELECT Customers.cust_id, Customer.cust_name, Orders.order_id
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

 
参考链接：肯定有 但是我当年没记…现在不知道了

如有错误，千万指出，感谢！