聊聊对Mysql的理解

目录

1、Sql介绍

1.1、 SQL的分类

1.2、数据库的三大范式

1.3、数据表的约束

1.4、约束的添加与删除

2、核心特性

3、主要组件

3.1、存储引擎

3.2、查询优化器

3.3、连接池

3.4、日志系统

4、存储文件

4.1、数据存储类型

4.2、共享表空间

4.3、独立表空间 (File-per-table)

4.4、如何配置

4.5、表的空间结构

1. 行（Row）

2. 页（Page）

3. 区（Extent）

4. 段（Segment）

5、mysql数据结构

5.1、聚簇索引

5.2、二分查找

5.3、自平衡二叉树

5.4、B树(多叉树)：

5.5、B+树

5.6、回表

5.7、覆盖索引

6、索引失效

7、Sql执行顺序

8、Mysql的事务

8.1、事务的基本要素（ACID）

8.2、事务的隔离级别

8.3、区间锁（Range Lock）

8.4、悲观锁和乐观锁

9、常用问题

---

前言

        MySQL是一个开源的关系型数据库管理系统(RDBMS)，由瑞典MySQL AB公司开发，现属于Oracle旗下产品。

以上为mysql的整体调用示意图。

        MySQL的数据主要存储在磁盘上，但在查询和操作时，MySQL会使用内存来加速这些操作。

具体来说：

1. 数据存储：MySQL的主要数据文件（如表和索引）存放在磁盘上。这确保了数据的持久性，即使在服务器重启后，数据也不会丢失。

2. 内存使用：

   • 缓冲池：MySQL使用缓冲池（如InnoDB的Buffer Pool）来缓存频繁访问的数据和索引，以减少磁盘I/O的需求，提高查询性能。
   • 临时数据：在某些情况下（如排序、连接操作等），MySQL会在内存中创建临时表。
   • 查询缓存：如果启用了查询缓存，MySQL会将查询的结果缓存到内存中，以提高相同查询的响应速度。

        因此，MySQL的数据是存放在磁盘上的，但在处理请求时，通过内存进行高效的访问和操作。

---

1、Sql介绍

Structure Query Language(结构化查询语言)简称SQL。

而mysql属于关系型数据库。

关于mysql的安装可参考：MySQL最新版8.0.21安装配置教程~-CSDN博客

以上为数据库在服务中的使用。

1.1、 SQL的分类

1、DDL(Data Definition Language) 数据定义语言

用来操作数据库、表、列等； 常用语句：CREATE、 ALTER、DROP。

2、DML(Data Manipulation Language) 数据操作语言

用来操作数据库中表里的数据；常用语句：INSERT、 UPDATE、 DELETE。

3、DCL(Data Control Language) 数据控制语言

用来操作访问权限和安全级别； 常用语句：GRANT、DENY。

4、DQL(Data Query Language) 数据查询语言

用来查询数据 常用语句：SELECT。‘’

关于在使用sql命令的过程中，每个命令执行完成后，均需要以";"结尾。

参考如下：

show databases;	--查看当前所有的数据库
use 数据库名;	--打开指定的数据库
show tables;	--查看所有的表
describe/desc 表名;	--显示表的信息
create database 数据库名;	--创建一个数据库
exit	--退出连接

1.2、数据库的三大范式

1、第一范式(1NF)

数据库表的每一列都是不可分割的基本数据线（每列的值具有原子性，不可再分割）。

2、第二范式(2NF)

在第一范式(1NF)的基础上，如果表是单主键，那么主键以外的列必须完全依赖于主键；如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。

3、第三范式(3NF)

在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。第三范式(3NF)要求：表中的非主键列必须和主键直接相关而不能间接相关；（非主键列之间不能相关依赖）。

1.3、数据表的约束

1. 主键约束 (PRIMARY KEY)

• 唯一标识表中的每一行记录

• 不允许 NULL 值

• 一个表只能有一个主键

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50)
);

-- 复合主键
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

2. 外键约束 (FOREIGN KEY)

• 建立表与表之间的关系

• 确保引用完整性

• 被引用的列必须是主键或唯一键

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

3. 唯一约束 (UNIQUE)

• 确保列中的所有值都是唯一的

• 允许 NULL 值（但只能有一个 NULL）

• 一个表可以有多个 UNIQUE 约束

CREATE TABLE employees (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) UNIQUE
);

4. 非空约束 (NOT NULL)

• 强制列不接受 NULL 值

• 必须包含值

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

5. 默认约束 (DEFAULT)

• 当插入数据时，如果没有提供值，则使用默认值

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'pending'
);

6. 检查约束 (CHECK) - MySQL 8.0+

• 限制列中值的范围

• 确保数据满足特定条件

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 18),
    salary DECIMAL(10,2) CHECK (salary > 0)
);

通常在设计数据库表的时候，会有以下常用的字段，参考如下图：

1.4、约束的添加与删除

1.添加约束

-- 添加主键
ALTER TABLE students ADD PRIMARY KEY (id);

-- 添加外键
ALTER TABLE orders ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id);

-- 添加唯一约束
ALTER TABLE products ADD UNIQUE (product_code);

2.删除约束

-- 删除主键
ALTER TABLE students DROP PRIMARY KEY;

-- 删除外键
ALTER TABLE orders DROP FOREIGN KEY fk_user;

-- 删除唯一约束
ALTER TABLE products DROP INDEX product_code;

合理使用约束可以确保数据库数据的完整性和一致性，是数据库设计中的重要环节。

---

2、核心特性

1. 开源免费：社区版可免费使用，有活跃的开源社区支持

2. 跨平台：支持Windows、Linux、macOS等多种操作系统

3. 高性能：优化过的存储引擎(InnoDB等)提供良好的读写性能

4. 可扩展性：支持主从复制、分片等扩展方案

5. 关系型数据库：基于表结构存储数据，支持SQL(结构化查询语言)

---

3、主要组件

3.1、存储引擎

InnoDB(默认)、MyISAM、Memory等，各有适用场景。

由以下图可以看到不同存储引擎的区别：

        默认使用InnoDB，安全性高，支持事务的处理，多表多用户操作。而MyISAM早些年使用，节约空间，速度较快。

        在正常重启的情况下，InnoDB 和 MyISAM 都不会影响已经存储在数据库中的数据。

但在不正常情况下（如意外关闭服务、崩溃等）：

• InnoDB 会利用其日志（重做日志和撤销日志）进行数据恢复。
• MyISAM 可能会出现数据损坏的风险，特别是正在写操作时。

3.2、查询优化器

        负责评估和决定 SQL 查询语言中给定查询的最佳执行计划。优化器会根据表的结构、索引、数据分布、查询模式以及其他多种因素来选择执行查询的方式。

以下是整个的链路：

举例如下：

1.查询优化器的原理：

在处理 SQL 查询时，优化器通过以下步骤工作：

• 解析：首先，SQL 查询被解析为内部的结构化表示（抽象语法树），然后转换为 MySQL 可以理解的形式。

• 预处理：检查 SQL 查询的语法和语义，确定表存在性和字段的类型。

• 生成执行计划：

  • 优化器会生成一个或多个可能的执行计划，通过不同的策略评估这些计划的性能。
  • 在此过程中，优化器可以考虑使用索引、连接顺序等。

• 选择最佳计划：在评估多个执行计划后，优化器选择一个预计性能最佳的执行计划并将其提供给查询执行引擎。

因此可以理解为：

        sql---->缓存---->解析器---->预处理器----->查询优化器---->>查询执行引擎---->存储引擎---->返回

2.优化策略

MySQL 查询优化器使用多种策略来优化 SQL 查询。以下是一些主要的优化策略：

• 使用索引：

  • 优化器会决定是否使用索引来加速数据检索。使用索引可以显著提高查询的性能，特别是在大表中。

• 选择合适的连接顺序：

  • 对于涉及多个表的联接查询，优化器会选择最优的连接顺序，以最小化中间结果集的大小。

• 过滤和限制数据：

  • 优化器尝试在数据检索过程中尽早过滤不必要的数据，通过 WHERE 子句等减少需要处理的数据量。

• 防止 Cartesian product：

  • 优化器能检测并避免产生笛卡尔积（cartesian product）的不必要的连接，这会导致性能下降。

• 重写查询：

  • 在一些情况下，优化器可能会重写查询以提高性能，例如将相同的查询合并、优化 UNION 操作等。

3. 优化器类型

MySQL 有两种主要的查询优化器类型：

3.1 基于成本的优化器（Cost-Based Optimizer, CBO）

• 定义：根据不同执行计划的成本估算来决定选择哪个计划执行。它会评估 CPU 时间、I/O 操作及其他资源消耗。
• 特点：更智能，能够根据表的统计数据和估算的成本来做出决策。适用于复杂查询。

3.2 基于规则的优化器（Rule-Based Optimizer, RBO）

• 定义：优先使用已定义的一组规则来选择执行计划。
• 特点：尽管简单，但在复杂性和动态性方面不如基于成本的优化器。一些早期版本的 MySQL 使用这种优化器。

        MySQL 的查询优化器是执行 SQL 查询的重要组成部分，它使用成本优化算法来选择最佳的执行计划。        

3.3、连接池

        连接池是一个用于管理数据库连接的组件，它的主要目的是提高应用程序的性能和资源利用率，简化数据库连接的管理。

        连接池维护一组可重用的数据库连接，从而减少连接的创建和销毁带来的开销，尤其是在高并发的环境中极为重要。

关于连接池的效果，如下图所示：

1.连接池的工作原理

• 初始化：在应用程序启动时，连接池会预先创建一定数量的数据库连接并将它们存储在池中。

• 使用连接：当应用程序需要数据库连接时，它从连接池中获取一个可用的连接。

• 归还连接：使用完毕后，连接不会被关闭，而是返回连接池中，供后续请求重用。

• 连接管理：连接池通常会管理连接的生命周期，包括创建、关闭、验证和监控连接的健康状态。

2. 常用的连接池实现

有多种连接池可供选择，以下是一些广泛使用的连接池：

• HikariCP：

  • 以高性能著称，适用于要求严格的连接池场景。
  • 默认使用的连接池实现，Spring Boot 2.x 版本会包含它。

• Apache DBCP：

  • Apache Commons DBCP 是一个历史悠久的连接池实现，功能稳定。
  • 适合对性能要求不太高的应用。

• C3P0：

  • 提供丰富的配置选项，可以根据需求灵活调整。
  • 但在高并发性能上大致上不如 HikariCP。

• Tomcat JDBC：

  • Tomcat 提供的连接池实现，与 Tomcat 服务器紧密集成。
  • 适合运行在 Tomcat 上的应用程序。

3. 配置连接池

以下是使用 HikariCP 配置 MySQL 数据库连接池的示例，适用于 Spring Boot 项目。

1. Maven 依赖

在 pom.xml 中添加 HikariCP 相关依赖（如果使用 Spring Boot，则 HikariCP 默认为连接池实现）：

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.x</version>
</dependency>

2.配置文件

在 application.properties 或 application.yml 中配置 HikariCP 连接池参数：

application.properties 示例：

spring.datasource.url=jdbc:mysql://localhost:3306/your_database
spring.datasource.username=root
spring.datasource.password=your_password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.type=com.zaxxer.hikari.HikariDataSource

# HikariCP 配置
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000

        MySQL 连接池是提高应用性能和资源管理的重要工具。通过使用连接池，应用程序可以有效管理数据库连接，减少高并发环境下的开销和延迟。

3.4、日志系统

        错误日志、查询日志 和 慢查询日志 作为文本文件，存储在文件系统中，位置可以在 MySQL 的配置文件中设置。日志文件一般位于服务器的 /var/log/mysql/ 目录（Linux）或配置的指定位置。

1.分类

1.错误日志

• 功能：记录 MySQL 服务器启动、运行和关闭过程中的错误消息和关键警告。

• 用途：用于诊断问题，例如数据库无法启动或服务崩溃等。

• 默认位置：错误日志的默认位置因操作系统而异，可以在配置文件 (my.cnf 或 my.ini) 中使用 log_error 进行配置。

[mysqld]
log_error = /var/log/mysql/error.log

2.查询日志

• 功能：记录所有对服务器的查询，包括 SELECT、INSERT、UPDATE 等。

• 用途：用于审计用户活动和性能分析，可以帮助你检查哪些查询消耗了最多资源。

• 配置：在配置文件中通过 general_log 开启：

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/mysql.log

3.慢查询日志

• 功能：记录执行时间超过设定阈值的慢查询。

• 用途：帮助开发者优化性能并识别高延迟的查询。

• 配置：在配置文件中可以通过设置 slow_query_log 和 long_query_time 来启用和配置：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2  # 记录超过2秒的慢查询

        

2.日志轮换

        为了避免生成巨大的日志文件，建议对日志文件进行轮换（即分割或清除旧日志）。可以通过操作系统的 logrotate 工具进行配置，确保不会占用过多的磁盘空间。

假设你想对 /var/log/myapp.log 文件进行轮换，你可以建 /etc/logrotate.d/myapp 文件并添加以下内容：

# /etc/logrotate.d/myapp
/var/log/myapp.log {
    daily               # 每天轮换
    rotate 7           # 保存 7 份轮换的日志文件
    compress           # 轮换的日志文件进行压缩
    delaycompress      # 延迟压缩，下一次轮换时再压缩
    missingok          # 如果文件不存在，不报错
    notifempty         # 只有文件非空时才进行轮换
    create 0644 root root  # 轮换后新文件的权限和拥有者
    sharedscripts      # 轮换时执行脚本
    postrotate         # 轮换后执行的操作
        systemctl reload myapp  # 重新加载服务
    endscript
}

3.故障恢复

        日志系统支持一些恢复策略，例如在使用二进制日志时，可以恢复到某一个时间点或某一个操作，帮助在系统崩溃时恢复数据的完整性。

以下为更新数据的流程示意图：

• 

4、存储文件

MySQL数据表以文件方式存放在磁盘中，默认使用共享表空间（0）存储。

       针对 InnoDB 存储引擎，表的数据可以在两种不同的模式下存储：独立表空间和共享表空间。这两种存储模式的选择影响数据和文件的管理。

4.1、数据存储类型

1、数据目录：

        MySQL 数据库的文件通常存储在 MySQL 数据目录中，路径因安装方式和操作系统而异。在 Linux 系统中，通常是 /var/lib/mysql。而在 Windows 系统中则可能在 C:\ProgramData\MySQL\MySQL Server x.x\data\。

2、行存储与表类型

1.对于 InnoDB：

ibdata 文件：

        当使用共享表空间时，所有表的数据和索引会存储在一个共享的 ibdata 文件中。

.ibd 文件：

        如果使用了独立表空间，InnoDB 会将每个表的结构和数据存储在独立的 .ibd 文件中。每当表的数据或索引被更新时，文件也会随之变化。

2.对于 MyISAM：

每个表的数据和索引不属于共享空间或独立空间的问题，而是直接通过三个文件来管理的：

• MyISAM 数据表通常使用三种文件，分别是：
  • .frm：存储表的结构信息。
  • .MYD：存储表的数据。
  • .MYI：存储表的索引。

4.2、共享表空间

1. 共享表空间 (Shared Tablespace)

• 定义：在共享表空间模式下，所有 InnoDB 表的数据和索引存储在一个或多个共享的 ibdata 文件中。

• 主要特点：

  • 所有自 InnoDB 引擎创建的表的记录存储在同一个 ibdata1 文件中（默认情况下，文件名为 ibdata1）。
  • 除了数据，索引也存储在这个共享区域中。
  • 表结构以 .frm 文件的形式存储在与表对应的文件夹中，但数据和索引都在 ibdata 文件中。

• 优点：

  • 简化了存储管理，所有数据集中在一个地方。

• 缺点：

  • 数据无法单独移动或导出，所有表的删除和重建需要手动管理。
  • 当数据量增大时，ibdata 文件可能会变得非常大，且无法轻易缩减大小。

4.3、独立表空间 (File-per-table)

由下图可知独立表空间，由段、区、页、行组成。

• 定义：在独立表空间模式下，每个 InnoDB 表的数据和索引存储在单独的 .ibd 文件中。

• 主要特点：

  • 每个表的数据和索引分别存储在对应的 .ibd 文件中，文件名与表名相同。
  • 表的结构仍然以 .frm 文件存储。
  • 只有用户删除表时，相关的 .ibd 文件才会被删除。

• 优点：

  • 更好的管理和灵活性，能够更轻松地移动和备份单个表。
  • 文件大小可以独立控制，便于检查每个表的大小及空间利用。

• 缺点：

  • 在存在多个表时，文件数量会增加，对文件系统的管理可能造成一定的负担。

以下对两种表结构进行一个简单的归纳：

4.4、如何配置

上面介绍了MyISAM没有这个表空间的概念，因此下面这个均为innodb存储引擎的操作。

• 使用独立表空间:

  • 可以在 MySQL 配置文件（my.cnf 或 my.ini）中启用独立表空间，添加如下配置：

[mysqld]
innodb_file_per_table=1

• 这将允许每个 InnoDB 表都有其独立的 .ibd 文件。

• 使用共享表空间:

  • 只需将 innodb_file_per_table 设置为 0 或不设置（默认值）。

4.5、表的空间结构

        在 InnoDB 中，每个表和索引都有各自的段，在段中可以有多个区，每个区由多个页构成，而每个页包含一或多条行。

下面是更层次的结构示例：

Table Segment (段)
 └── Data Segment (数据段)
     ├── Extent (区)
     │   ├── Page (页)
     │   │   ├── Row (行)
     │   │   └── Row (行)
     │   ├── Page (页)
     │   │   ├── Row (行)
     │   │   └── Row (行)
     │   └── Page (页)
     └── Index Segment (索引段)
         └── Index Extent (索引区)
             └── Index Page (索引页)

示例操作

在创建 InnoDB 表时，这些结构将由数据库管理系统自动处理。下面是 SQL 示例：

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2)
) ENGINE=InnoDB;

在执行这个 SQL 语句时，InnoDB 会进行如下操作：

1. 为 products 表分配一段逻辑区域，包含指向数据和索引的分配。
2. 分配区，将它们分配给数据页。
3. 将具体数据存储在行中，以便读取和写入。

        InnoDB 存储引擎的数据组织结构主要由段（Segment）、区（Extent）、页（Page）和行（Row）组成。下面是对这些组成部分的详细解释：

1. 行（Row）

• 定义：行是表中存储的基本数据单位。每一行代表一个具体的记录，包含多个列。

2. 页（Page）

• 定义：页是 InnoDB 存储引擎存储数据的基本单位。所有的 I/O 操作都是按照页进行的。
• 大小：默认情况下，InnoDB 页的大小为 16 KB（可以根据配置调整为 4 KB、8 KB、16 KB 或 32 KB）。
• 页面种类：
  • 数据页：存储表的行数据。
  • 索引页：存储索引的结构信息。
  • 系统页：存储各种元数据，如表的描述。

3. 区（Extent）

• 定义：区是由多个连续的页组成，一般包含 8 个页（即 128 KB）。
• 用途：区用于分配表的数据。每个表的每个部分会使用区来组织数据。
• 类型：
  • 数据区：存放实际的数据行。
  • 索引区：存放索引结构。

4. 段（Segment）

• 定义：段是一个逻辑上的概念，包含多个区，用来存储表的不同部分。
• 类型：
  • 根段：存储整个表的数据。
  • 索引段：存储表的索引信息。

这种细粒度的结构使得数据管理和存储更加高效，同时为事务处理和一致性维护提供了支持。

---

5、mysql数据结构

5.1、聚簇索引

        聚簇索引是 InnoDB 中最基本的索引类型，通常由主键构成。在聚簇索引中，数据和索引是存储在同一结构中的。

聚簇索引的结构：

        在 B+ 树中，如果叶子节点存储的是实际的用户数据，那么这个索引就是聚簇索引。换句话说，聚簇索引的叶子节点存储的是完整的记录，而非索引值。

一个表只能有一个聚簇索引：

        由于数据本身已经通过聚簇索引进行组织，所以每个表只能有一个聚簇索引。通常情况下，如果没有明确指定主键，那么 InnoDB 会选择第一个唯一的非空索引作为聚簇索引。

主键值的有序性：

        聚簇索引保证了表中记录的主键值是有序的，这就意味着主键值在 B+ 树的叶子节点中是按顺序排列的。通过这种有序性，InnoDB 可以高效地进行查找、范围查询以及排序操作。

5.2、二分查找

1.什么是二分查找？

        二分查找是一种在已排序数组中查找目标元素的高效方法。

        它通过将数组递归地分割为两半，不断缩小查找范围。每次比较中间元素与目标值的大小关系，从而决定是继续查找左半部分还是右半部分。

时间复杂度：O(log N)，因为每次操作都将问题规模减半。

什么是二分查找树？

结构示意图如下所示：

        二分查找树（Binary Search Tree, BST）是一种基于二分查找思想的数据结构，它由节点组成，每个节点都有左子树和右子树。

对于每个节点，左子树的所有元素值小于节点值，右子树的所有元素值大于节点值。

查找、插入、删除操作的时间复杂度：O(log N)（前提是树保持平衡）。

5.3、自平衡二叉树

结构示意图如下所示：

        

性能：

        平衡二叉树在添加和删除时需要进行复杂的旋转保持整个树的平衡，最终，插入、查找的时间复杂度都是 O(logn)，性能已经相当好了。

        是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。
　　平衡二叉树必须是二叉查找树。

5.4、B树(多叉树)：

        分批次的将磁盘块加载进内存中进行检索,若查到数据,则直接返回,若查不到,则释放内存,并重新加载同等数据量的索引进内存,重新遍历。

        平衡多路查找树（查找路径不只两个），不同于常见的二叉树，它是一种多叉树。O(logN)

由上图可知：

        B树是一种自平衡的多叉树数据结构，适合用作关系数据库和文件系统中的数据存储。与二叉树（每个节点最多只有两个子节点）不同，B树可以有多个子节点，因此被称为多叉树。

 1.B树的定义

        B树是一种以节点为基础的树形结构，主要用于在大量数据存储和高效访问之间取得平衡。B树的主要特点包括：

• 多叉性：每个节点可以有多个子节点（即多个分支），通常在某个特定的范围内，具体数目依赖于树的阶（Order）。例如，B树的阶为 m，每个节点最多可以有 m-1 个键（key）和 m 个子节点。

2. 为何使用多叉结构

B树作为多叉树的优势体现在以下几个方面：

2.1 降低树的高度

• 更高的每节点存储量：由于每个节点可以存储多个键，B树能在较少的层级（高度）中存储更多的数据。因此，当数据量较大时，B树的层数会显著减少，从而提高查找效率。

• 减少 I/O 操作：B树常用于外部存储（如硬盘），其更高的存储密度意味着读取和写入的 I/O 操作（访问硬盘的效率）能得到优化。较少的层级意味着查找所需的 I/O 操作数量减少，从而加速数据访问。

2.2 提升性能

• 快速查找：与二叉树相比，B树通过较少的比较和更少的访问可以更快地找到目标值。对于大规模数据，查找、插入和删除操作的时间复杂度均为 O(log n)，这种高效性得益于 B树的多叉结构。

• 适合范围查询：多叉的结构使得 B树叶子节点能够通过链表相互连接，便于范围查询操作。这样，你可以在较小的时间内获取到相邻的元素。

2.3 自平衡性

• 通过分裂：B树的设计使其具备自平衡的能力。每当插入或删除键时，可能会导致节点超出最大或小于最小容量。B树会自动分裂或合并节点，以保持树的平衡性。

3. B树的基本结构

1. 节点结构：

   • 每个节点包含多个键（key）和指向子节点的指针（pointer），并遵循以下性质：
     • 所有键都是有序的。
     • 每个节点的子指针数量 = 节点中键值数量 + 1。

2. 键和子指针的关系：

   • 对于任意节点，若该节点有 k 个键，则节点必须有 k + 1 个子指针。
   • 所有子指针都指向其大小相对应的子树。

        虽然 B 树因其高效的查找性能和自平衡特性被广泛使用，但它也存在实现复杂性、内存开销、频繁更新性能、数据倾斜、依赖于磁盘 I/O 性能等缺点。

5.5、B+树

        分批次的将磁盘块加载进内存中进行检索,若查到数据,则直接返回,若查不到,则释放内存,并重新加载同等数据量的索引进内存,重新遍历 。

        在 B+ 树中，叶子节点会存储多条数据行的引用（通常是行 ID），而不是每个叶子节点只存储一条记录。这样设计的目的是为了提高空间利用率和访问效率。

关于b+树的结构示意图如下所示：

1.B+树基本概念

        B+ 树是一种多路自平衡的查找树，它在索引的结构上非常高效，特别适合大规模数据的查找。

在 B+树中，节点被分为两种类型：

1. 非叶子节点（或内部节点）：不存储实际数据，但用于导航。它们存储键值，并指向子节点。
2. 叶子节点：存储实际的数据（行）或指向数据的引用。叶子节点相互连接，形成一条链表，方便进行范围查询。

2.结构示意图：

我们假设 B+树的初始结构可能是这样的：

         [20]
        /    \
     [10]    [30]

• 20 是根节点的键信息，它充当分隔其子树的指引。具体来说：
  • 左侧子树是小于 20 的所有元素（如 10）。
  • 右侧子树是大于或等于 20 的所有元素。

进一步分析：

1. 非叶子节点的功能

• 导航：非叶子节点的存在使得 B+树能够通过较少的比较找到目标数据。例如，在查找某个值时，可以通过比较根节点的值（如 20）来决定是向左子树（小于 20 的部分）还是右子树（大于或等于 20 的部分）继续进行查找。

2. 叶子节点的功能

• 数据存储：叶子节点存储实际的数据行（或索引），例如：

        [20]
       /     \
  [10]       [30]
  /  \        /  \
[5] [15]  [25] [35]

在这个结构中，你可以看到：

• 10 小于 20，因此在 20 的左子树中。
• 30 大于等于 20，因此在 20 的右子树中。

叶子节点最终存储的数据：

• 假设有的数据：
  • 5, 10, 15 存储在左侧子树中。
  • 25, 30, 35 存储在右侧子树中。

3. B+树的性质

• 所有数据在叶子节点：和内部节点不同，B+树的所有实际行数据只存储在叶子节点中。内部节点只存储用于导航的键值。

• 连接的叶子节点：叶子节点相互通过指针相连，允许快速遍历。也就是将所有叶子节点用链表连接，可以加速范围查询。

4. 插入与调整示例

假设我们开始需要往 B+树中插入数据，以下是插入 5, 10, 15, 20, 25, 30, 35, 40 的过程。

插入过程

• 开始插入 10, 20, 30：
  • 首先建立如下结构：

         [20]
        /    \
     [10]    [30]

• 然后插入 5, 15，调整结构：

          [20]
         /     \
     [10]      [30]
     /  \         
   [5] [15]    

• 插入 25, 35, 40 时，当需要在叶子节点之间分裂时，更新结构，形成：

               [20]
             /      \
         [10]       [30]
         /  \       /   \
      [5]  [15]  [25]  [35]
                      \
                      [40]

        在此过程中，每次插入数据时，B+树会根据现有的结构自动调整和重新平衡，保持树的性质和结构。

总结

• 非叶子节点（如 20，30）用于指引查找和高效导航.
• 叶子节点存储实际的数据，确保按顺序查找高效。
• 任何插入或删除操作都会自动调整 B+树，确保其平衡和性能。

总结：

在 MySQL 中，InnoDB 存储引擎采用 B+ 树作为索引结构。

5.具体应用如下：

聚簇索引：

        InnoDB 使用主键作为聚簇索引，叶子节点存储的是实际数据。当我们查询主键时，查询效率很高。

二级索引：

        对于非主键的索引，InnoDB 创建二级索引，叶子节点存储的是主键值。查询时，如果找到二级索引的结果，还需要通过主键去聚簇索引中查找实际数据，这一过程称为 回表。

        B+ 树的应用让 MySQL 在处理大量数据时，能够保持较低的查询时间和较高的存储效率。

5.6、回表

        回表 是指在使用索引进行数据查询时，首先通过索引找到某个记录的存储位置（例如主键），然后再查找主表，以获取完整的行数据。

这一过程通常出现在如下场景：

• 使用非主键索引（有时称为二级索引）来查找数据时，索引叶子节点中只存储了字段的索引和指向主键的引用，而不直接存储完整的记录。

1. 回表过程示例

假设情况

• 在一个数据库表 products 中有以下字段：

  • id (主键)
  • name
  • price

• 使用的 B+ 树的二级索引是按照 name 字段建立的。

执行查询

1.通过索引查找：

SELECT * FROM products WHERE name = 'Example Product';

2.查找过程：

• 数据库利用 B+ 树的索引查询找到 Example Product 对应的 id。

此时，仅从索引叶子节点返回的可能是：
id (主键)      name
----------     ----------
1              Example Product

3.回表查询：

• 通过主键 id （例如 1），数据库会再查找主表 products 以获取完整的记录，通常这会导致一次额外的查询操作。
• 对应的查询可能是.

SELECT * FROM products WHERE id = 1;

4. 性能影响

1.性能开销：

        回表查询会导致一次额外的查询，可能会引发性能下降，尤其是在高并发的情况下，每个查询都需要额外访问一次主表。

 2.避免回表：

        为了解决回表的问题，如果查询需要经常使用非主键索引来获取完整数据，建议在索引中包括所有需要的字段，使用覆盖索引。B+ 树中的覆盖索引可以使得查询更高效，因为不需要回表而直接从索引中获得所有数据。

5.7、覆盖索引

        覆盖索引 是指一个索引包含了查询所需要的所有列的信息，因此在执行查询时，只需访问索引而无需访问数据表（行）本身。

        通过覆盖索引，数据库可以直接从索引中返回所需的数据，从而避免额外的查询开销。

CREATE TABLE users (
    id INT PRIMARY KEY,            -- 作为主键的聚簇索引
    username VARCHAR(50),          -- 二级索引字段
    email VARCHAR(100)              -- 查询中需要的字段
);



CREATE INDEX idx_username_email ON users(username, email);

SELECT email FROM users WHERE username = 'exampleUser';

总结

• 覆盖索引：是一个包含查询所需所有字段的索引，允许数据库在不访问底层数据表的情况下完成查询。
• 避免回表：因查询所需数据完全存在于索引中，所以不再需要通过索引查找主键后再回到表中获取具体字段，从而提升了效率。

关于覆盖索引和复合索引由许多相似之处，感兴趣的可以研究下哦。

覆盖索引可以被认为是复合索引的一种特殊情况，主要用于提取所需数据而无需读取主表。

---

6、索引失效

1. 索引列参与计算（破坏有序性）

底层机制：

• B+树按照原始值排序存储

• 计算后的值无法与索引中的存储值直接比较

-- 失效案例
SELECT * FROM products WHERE price*0.9 > 100;

-- 优化方案
SELECT * FROM products WHERE price > 100/0.9;

2. 隐式类型转换（改变比较规则）

类型转换优先级：

1. 字符串 vs 数字 → 字符串转为数字

2. 不同字符集 → 需要字符集转换

EXPLAIN SELECT * FROM users WHERE phone = 13800138000;
-- 在Extra列可能看到"Using where; Using index condition"

3. 最左前缀原则失效（复合索引结构限制）

B+树排列方式：

• 先按第一列排序

• 第一列相同时按第二列排序

• 以此类推

-- 索引(a,b,c)
WHERE b = 1 AND c = 2  -- 完全失效
WHERE a = 1 AND c = 2  -- 只能用到a列

4.使用否定条件

SELECT * FROM users WHERE status != 'active';
SELECT * FROM orders WHERE id NOT IN (1,2,3);

5.模糊查询前导通配符

SELECT * FROM articles WHERE title LIKE '%mysql%';

6.IS NULL/IS NOT NULL判断

SELECT * FROM customers WHERE phone IS NULL;

7.使用NOT EXISTS

SELECT * FROM orders WHERE NOT EXISTS (SELECT 1 FROM payments WHERE payments.order_id = orders.id);

总结起来就是：

• where 后面使用函数
• 使用or条件
• 模糊查询 %放在前边
• 类型转换
• 组合索引 （最佳左前缀匹配原则）

---

7、Sql执行顺序

由下图可知：

整体的执行顺序由上到下，如下所示：

• 从表中获取数据 (FROM)
• 筛选符合条件的行 (WHERE)
• 分组数据 (GROUP BY)
• 对分组结果进行筛选 (HAVING)
• 返回具体列（select、distinct）
• 排序结果 (ORDER BY)
• 限制返回的行数 (LIMIT)

---

8、Mysql的事务

8.1、事务的基本要素（ACID）

以下是对四种要素的介绍：

原子性（Atomicity）：

        事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。

        也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

一致性（Consistency）：

        事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

隔离性（Isolation）：

        同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

持久性（Durability）：

        事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

8.2、事务的隔离级别

关于mysql的事务隔离级别，主要可分为读未提交、读提交、可重复读、串行化。

关于事务并发过程中会出现的现象：

脏读：

        事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

不可重复读：

        事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

幻读：

        系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

如何解决脏读、幻读、不可重复读

由上图的隔离级别可知：

脏读：

         隔离级别为 读提交、可重复读、串行化可以解决脏读。

不可重复读：

        隔离级别为可重复读、串行化可以解决不可重复读。

幻读：

        隔离级别为串行化可以解决幻读、通过MVCC + 区间锁可以解决幻读。

关于mvcc可参考本人的mvcc理解：探讨LRU和MVCC的理解-CSDN博客。

如果觉得麻烦，mvcc的原理简单来说就是：

        当同一条数据被修改多次，那么通过数据的事务ID和回滚指针能够形成一个非常好的修改链路。

如下图所示：

        主要是依赖记录中的2个隐式字段：DB_TRX_ID（事务ID）和DB_ROLL_PTR（回滚指针）和undo日志 ，Read View（快照）来实现的。

        就是事务A开始时获取了一份快照，并在其生命周期内保持这一视图。即使事务B更新了数据，事务A在快照读操作中仍然只看到快照开始时的状态。

8.3、区间锁（Range Lock）

        区间锁是一种锁定机制，适用于范围查询的场景。它可以防止其他事务在特定的范围内插入或修改数据，从而避免了幻读（Phantom Read）现象。

1.区间锁的工作原理

• 保护范围：在串行化隔离级别下，如果一个事务在进行范围查询（例如 SELECT * FROM table WHERE column BETWEEN 1 AND 10），区间锁会锁定从条件开始到结束的整个范围。这意味着其他事务无法在此范围内插入新数据。

• 避免幻读：通过锁定范围，串行化隔离可以确保在一个事务读取某个范围的数据时，后续对这个范围的插入操作会被阻塞，确保两个事务之间没有可见的干扰。

2.类型

• 共享区间锁（S锁）：允许其他事务读取锁定的插入范围，但不允许其他事务在此范围内进行插入。

• 排他区间锁（X锁）：不允许其他事务进行任何插入、更新或删除操作。

3.配合使用

事务的起始

1. 事务启动：当一个事务以串行化隔离级别开始时，首先会通过 MVCC 获得数据的快照。

2.区间锁生效

执行范围查询

1. 如果这个事务执行一个需要读取范围的数据查询（如 SELECT * FROM table WHERE column BETWEEN 10 AND 20），InnoDB 将会为这个范围申请区间锁。
2. 这个锁会阻止其他事务在这个范围内插入或修改记录，直到当前事务完成。

3.读操作与版本控制

读取数据

1. 由于 MVCC 的存在，这个事务在进行读取操作时，不会受到其他事务对同一行的修改影响。它只会获取在事务开始前已经提交的数据版本。

4.提交或回滚

提交或回滚

1. 当事务完成后，如果所有操作均成功，它会提交，并释放区间锁，同时新的版本将会被其他事务看到。
2. 如果事务被回滚，则任何在它期间产生的写入版本都会被丢弃。

4.局限性

• 这种机制需要额外的内存来存储多个数据版本，可能会导致性能开销。
• 串行化隔离级别可能导致死锁和锁竞争，性能下降。

8.4、悲观锁和乐观锁

  
1.悲观锁：

        select...for update是MySQL提供的实现悲观锁的方式。

        此时在items表中，id为100的那条数据就被我们锁定了，其它的要执行select price from items where id=100 for update的事务必须等本次事务提交之后才能执行。

        这样我们可以保证当前的数据不会被其它事务修改。MySQL有个问题是select...for update语句执行中所有扫描过的行都会被锁上，因此在MySQL中用悲观锁务必须确定走了索引，而不是全表扫描，否则将会将整个数据表锁住。

START TRANSACTION;

-- 选择 Alice 的账户并锁定这一行
SELECT * FROM accounts WHERE username = 'Alice' FOR UPDATE;

-- 进行某种计算，例如将余额增加 200
-- (此时，Alice 的账户行处于锁定状态)
UPDATE accounts SET balance = balance + 200 WHERE username = 'Alice';

COMMIT;

2.乐观锁：

        乐观锁相对悲观锁而言，它认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回错误信息，让用户决定如何去做。

        利用数据版本号（version）机制是乐观锁最常用的一种实现方式。

        一般通过为数据库表增加一个数字类型的 “version” 字段，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值+1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，返回更新失败。

举例：

//1: 查询出商品信息

select (quantity,version) from items where id=100;

//2: 根据商品信息生成订单

insert into orders(id,item_id) values(null,100);

//3: 修改商品的库存

update items set quantity=quantity-1,version=version+1 where id=100 and version=#{version};

---

9、常用问题

1、count（*）和count（1）和count（列）区别

1. COUNT(*)和COUNT(1)在实际结果上完全相同。

2. 它们的执行计划也完全一致。

3. 在复杂查询和性能测试中表现相同。

4. 与COUNT(列名)有明显区别，count（列）会忽略NULL值。

2、Delete和Truncate区别

        TRUNCATE和DETELE都能实现删除表中的所有数据的功能。

但两者也是有区别的：

1、DELETE语句后可跟WHERE子句，可通过指定WHERE子句中的条件表达式只删除满足条件的部分记录；但是，TRUNCATE语句只能用于删除表中的所有记录。

2、使用TRUNCATE语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始；使用DELETE语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1。

3、DELETE语句是DML语句，TRUNCATE语句通常被认为是DDL语句。

4、DELETE可以进行回滚，而TRUNCATE不支持事务。

4、关于连接查询。

        在进行连接查询的时候，可以遵循，1对多情况，使用一张表里面的key-id作为外键，而多对多情况，使用第三张表，分别对表1和表2的key-id进行关联。

5、如何处理慢查询  
 

慢的原因是什么?

1、是查询条件没有命中索引。

2、是加载了不需要的数据列。

3、还是数据量太大。

所以优化也是针对这三个方向来的

        首先分析语句，看看是否加载了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。

        分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。

        如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

---

总结

优势：

• 成熟稳定，社区支持完善

• 易于安装和使用

• 良好的兼容性和工具生态

• 支持事务处理(ACID特性)

局限：

• 在大数据量(如PB级)场景下不如NoSQL高效

• 水平扩展能力相对有限

• 复杂查询性能可能不如专用分析型数据库

---

参考文章：

1、MySQL 有这一篇就够（呕心狂敲37k字，只为博君一点赞！！！）_mysql有这一篇幅就够了-CSDN博客

2、MySQL详细学习教程（建议收藏）-CSDN博客

3、【MySQL】数据结构--B树、B+树_mysql b+树-CSDN博客