MySQL

[wxb]

[wxb]

DROP TABLE

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

• 需要账号有DROP操作权限
• DROP会删除表定义和所有的表数据
• 如果是已分区的表，DROP语句也会删除所有分区及其中的数据
• DROP语句也会删除表上定义的触发器

[wxb]

12.18 JSON Functions

JSON函数

[wxb]

12.18.3 Functions That Search JSON Values

• JSON_VALUE(json_doc, path)

  获取json文档指定路径上对应的值，并且可以指定期望返回的结果值类型，语法如下：

  JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])
  
  on_empty:
      {NULL | ERROR | DEFAULT value} ON EMPTY
  
  on_error:
      {NULL | ERROR | DEFAULT value} ON ERROR

[wxb]

12.8 String Functions and Operators

字符串函数和操作

[wxb]

SUBSTRING_INDEX(str,delim,count)

在字符串str中搜索分隔符delim，并返回分隔符出现第count次数前的子字符串

特点：

• count是正数：从左边开始计数
• count是负数：从右边开始计数
• delim搜索匹配时大小写敏感
• 多字节安全
• 任意参数为NULL时函数返回NULL

示例：

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

[wxb]

CONCAT_WS(separator,str1,str2,...)

使用指定的分隔符separator拼接字段成字符串

特点：

• separator分隔符可以是字符串
• separator是NULL时，函数返回结果是NULL
• 不会跳过空字符串
• 会跳过任意NULL值的待拼接字段

示例：

mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
        -> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
        -> 'First name,Last Name'

[wxb]

12.20 Aggregate Functions

聚合函数

[wxb]

SUM([DISTINCT] expr) [over_clause]

• 求和浮点数据精度丢失问题
• DISTINCT 关键字用来只求和不同值
• 如果没有查到匹配的数据行，SUM返回NULL

[wxb]

COUNT(expr) [over_clause]

参考：MySQL：COUNT(*) 、 COUNT(1) 和 COUNT(字段) 哪个效率高

[wxb]

GROUP_CONCAT(expr)

将组中的字符串连接成为具有各种选项的单个字符串

• DISTINCT 子句用于在连接分组之前消除组中的重复值
• ORDER BY 子句允许您在连接之前按升序或降序排序值。 默认情况下，它按升序排序值
• SEPARATOR 指定在组中的值之间分隔符, 默认使用逗号作为分隔符
• GROUP_CONCAT 函数忽略NULL值，如果找不到匹配的行，或者所有参数都为NULL值，则返回NULL
• 特别注意： group_concat_max_len 默认情况下，返回字符串的最大长度为1024，如果需要更多的长度，可以通过在SESSION或GLOBAL级别设置group_concat_max_len系统变量来扩展最大长度

[wxb]

13.2.17 UPDATE Statement

表数据修改DML语句

单表更新语法

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

多表更新语法

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

[wxb]

用一个表数据更新另一个表字段

• 单列

  UPDATE
      student s,
      city c
  SET
      s.city_name = c.name
  WHERE
      s.city_code = c.code

• 多列

  UPDATE
      a,
      b
  SET
      a.title = b.title,
      a.name = b.name
  WHERE
      a.id = b.id

• 子查询

    UPDATE
        student s
    SET
        s.city_name = (SELECT name FROM city WHERE code = s.city_code)

[wxb]

11.3.2 The CHAR and VARCHAR Types

[wxb]

mysql的varchar字段最大长度真的是65535吗？

声明char和varchar都需要在字段边上加个数字，比如char(100)和varchar(100)，这个100是指当前字段能放的最大字符数。

1. varchar最大值是多少

• 相信大家应该听说过varchar字段的最大长度是65535吧 → 但这并不准确

• varchar最大值收到好几个因素影响

2. 不同字符集的影响

• 不同的字符集要求使用的字节个数也不同（

  通过 show charset; 看到mysql支持哪些字符集，以及这些字符集里存储一个字符所需的最大字节数（Maxlen）

  

• 可以发现规律:

  • utf8mb4的maxlen=4，对应varchar最大长度=16383。4*16383 = 65532。
  • utf8mb3的maxlen=3，对应varchar最大长度=21844。3*21844 = 65532。
  • latin1的maxlen=1，对应varchar最大长度=65533。 1 * 65533 = 65533。

• varchar的最大长度，根据选择的字符集的不同，会有区别

  总的来说接近于 65535 除以 字符集的maxlen

3. 是否可以为NULL的影响

一个字段是否为NULL这件事情，是需要一个字节去记录下来的。而当字段为NOT NULL的时候，则可以省下这个字节。

• 前面NOT NULL的时候最大能使用65533去建表，成功
  

• 现在改成可以为NULL，报错了
  

4. 列数的影响

• 65535是mysql单行的最大长度

  mysql表里单行中的所有列加起来（不考虑其他隐藏列和记录头信息） ，占用的最大长度是65535个字节。

• 所有列加起来不超过65535

  如果还有int的列，那它占用4个字节，bigint占用8个字节，字段越多，留给单个varchar列的空间就越少。

• varchar 的最大长度，接近于 65535 除以 字符集的maxlen，但前提是只有一列not null 的varchar类型的字段。

5. 为什么不是65535而是65533？

数据库单行最大值应该是65535。65535 - 65533 = 2 。这里面还差了个2，为什么呢？

• 数据表行存储的格式

  通过 show table status 命令，查看到当前表格使用的行格式
  

• Dynamic 行格式

  Dynamic格式将行记录分为两部分，分为是行记录的额外信息和行记录的真实数据
  

• 行记录的额外信息

  • 变长字段长度列表：指的是varchar，text，blob这种类型，它们属于变长字段，这里表示的就是这些字段的长度。
  • NULL值列表：用来记录当前行里哪些列是为null的。如果全部列都是not null的话，那就不需要有这个字段。
  • 记录头信息：这是固定5个字节，用来记录一些特殊的信息，比如这一行是否被删了，这一行在这个16k的数据页内是不是最小的，以及指向下一条记录的指针之类的一些信息，不需要太关注。

• 行记录的真实数据：

  • 隐藏列

    比如Row_ID，这个是在建表是没有声明主键时，数据表自动会生成的隐藏主键

  • 只有一列not null的 varchar字段时，行记录长下面这样

    

  • 最左边的变长字段占用了两个字节

    也就是16位，2的16次方，最大可以表示65535的长度，正好足够用来表示varchar列当前的长度是65533。

6. 一个页才16k，怎么保存65533（64k）数据？

• mysql底层是以页的形式去存储数据的，而一个页固定16k
  

• varchar字段最大能放65533字节数据，大概是64k，整整4个16k的页。怎么实现的？

  行溢出：行数据里针对这个超大的varchar字段只保存个20字节的指针（实际上是个偏移量），这个指针会指向新的页（off page），这些页里保存的是实际的varchar字段里的65533字节数据。

  

7. 大于64k的字符串该怎么处理？

• 数据量更大，比64k还大

  需要改用text和blob类型字段。他们的存储方式也跟varchar的情况类似，只保存20个字节的指针，实际数据保存在其他溢出页里。

• 更多的磁盘IO

  某行数据里有个特别大的字符串时，我们如果还想把整行数据给读出来，那我们还得把off page的数据给全部读出来，这意味着更多的磁盘IO，性能就更差了。

• 规避这个问题

  如果发现某列字段，是个特别长的字符串时，能不读它就尽量不加到select里

8. blob和text的区别

• 校对规则

  影响比较和排序: 同样是a，大写A和小写a能不能算作是一个字符?

• blob没有字符集的概念，而text有

  用blob来存文本的话，就没法用字符集的校对规则来排序和做比较。

• blob还能保存二进制数据

  也就是常说的 二进制安全

[wxb]

8.4.7 Limits on Table Column Count and Row Size

表中列数和单个行大小的限制

[wxb]

Column Count Limits

MySQL对每个表的硬限制为4096列，但是对于给定的表，有效的最大值可能会更小。具体的列限制取决于以下几个因素:

[wxb]

Row Size Limits

[wxb]

Row Size Limit Examples

[wxb]

percona.com

[wxb]

percona-toolkit

https://github.com/percona/percona-toolkit

Percona Toolkit is a collection of advanced command-line tools used by Percona support staff to perform a variety of MySQL and system tasks that are too difficult or complex to perform manually.

These tools are ideal alternatives to private or "one-off" scripts because they are professionally developed, formally tested, and fully documented. They are also fully self-contained, so installation is quick and easy and no libraries are installed.

Percona Toolkit is developed and supported by Percona Inc. For more information and other free, open-source software developed by Percona, visit http://www.percona.com/software/.

---

• pt-online-schema-change

  介绍
  pt-online-schema-change是percona公司开发的一个工具，在percona-toolkit包里面可以找到这个功能，它可以在线修改表结构

  原理:

  首先它会新建一张一模一样的表，表名一般是_为前缀_new后缀,例如原表为t_user 临时表就是_t_user_new
  然后在这个新表执行更改字段操作
  然后在原表上加三个触发器，DELETE/UPDATE/INSERT，将原表中要执行的语句也在新表中执行
  最后将原表的数据拷贝到新表中，然后替换掉原表

[wxb]

percona-xtrabackup

https://github.com/percona/pxb-docs

Percona XtraBackup is an open-source hot backup utility for MySQL - based servers that doesn’t lock your database during the backup.

AI概览：

• XtraBackup和mysqldump都是MySQL数据库备份工具，各有优缺点。XtraBackup主要优势在于支持在线热备，备份速度快，尤其适合大型数据库，但备份文件较大，且不方便直接查看备份内容；mysqldump则备份文件较小，易于查看和恢复，但备份过程中可能锁定表，影响业务，且备份速度相对较慢
• XtraBackup 支持增量备份，只备份自上次备份以来发生变化的数据，从而减少备份时间和存储空间。
• XtraBackup 专门为InnoDB 和XtraDB 存储引擎优化，这两个存储引擎是MySQL 和Percona Server 的默认存储引擎。
• XtraBackup 提供了快速且可靠的数据恢复功能，可以快速从备份文件中还原数据库。

XtraBackup 备份数据表的方法

XtraBackup 是 Percona 提供的 MySQL 数据库热备份工具，以下是使用 XtraBackup 备份数据表的具体方法：

1. 备份整个数据库实例

这是最常用的方式，会备份所有数据库和表：

# 基本备份命令
xtrabackup --backup --target-dir=/path/to/backup --user=username --password=password

# 指定主机和端口
xtrabackup --backup --host=127.0.0.1 --port=3306 --user=root --password=123456 --target-dir=/data/backups/

2. 备份单个数据库

XtraBackup 8.0+ 版本支持使用 --databases 参数备份指定数据库：

xtrabackup --backup --databases="database_name" --target-dir=/path/to/backup --user=username --password=password

3. 备份单个数据表

XtraBackup 本身不直接支持备份单个表，但可以通过以下方法实现：

方法一：导出表结构+数据

# 先备份表结构
mysqldump -u username -p --no-data database_name table_name > table_structure.sql

# 使用XtraBackup备份整个数据库
xtrabackup --backup --target-dir=/path/to/backup --user=username --password=password

# 然后从备份中提取特定表的数据文件
cp /path/to/backup/database_name/table_name.{ibd,frm} /path/to/save/

方法二：使用部分备份功能

XtraBackup 8.0+ 支持部分备份（需要提前设置）：

# 1. 创建部分备份配置文件
echo "database_name.table_name" > /tmp/tables.txt

# 2. 执行部分备份
xtrabackup --backup --tables-file=/tmp/tables.txt --target-dir=/path/to/backup --user=username --password=password

4. 恢复单个数据表

# 1. 在目标数据库创建相同结构的表
mysql -u username -p database_name < table_structure.sql

# 2. 丢弃表空间
mysql -u username -p -e "ALTER TABLE database_name.table_name DISCARD TABLESPACE"

# 3. 复制备份的.ibd文件到数据目录
cp /path/to/save/table_name.ibd /var/lib/mysql/database_name/

# 4. 修改文件权限
chown mysql:mysql /var/lib/mysql/database_name/table_name.ibd

# 5. 导入表空间
mysql -u username -p -e "ALTER TABLE database_name.table_name IMPORT TABLESPACE"

注意事项

1. XtraBackup 需要足够的权限，通常需要 RELOAD, LOCK TABLES, REPLICATION CLIENT 权限
2. 备份期间不要中断，否则可能导致备份损坏
3. 备份前确保有足够的磁盘空间
4. 对于大表，备份和恢复可能需要较长时间
5. 建议在低峰期执行备份操作

常用参数说明

参数	说明
--backup	执行备份操作
--target-dir	指定备份目录
--user	数据库用户名
--password	数据库密码
--host	数据库主机地址
--port	数据库端口
--databases	指定要备份的数据库
--tables-file	指定包含表列表的文件
--compress	压缩备份
--parallel	并行备份线程数

如需更详细的帮助，可以使用 xtrabackup --help 查看所有可用参数。

[wxb]

MySQL 数据表导入命令大全

以下是 MySQL 数据库导入数据表的几种常用方法：

1. 使用 mysql 命令行工具导入

# 基本语法
mysql -u 用户名 -p 数据库名 < 导入文件.sql

# 示例（导入整个数据库）
mysql -u root -p mydatabase < backup.sql

# 指定主机和端口
mysql -u root -p -h 127.0.0.1 -P 3306 mydatabase < backup.sql

2. 使用 source 命令在 MySQL 客户端内导入

-- 连接到 MySQL
mysql -u root -p

-- 选择数据库
USE mydatabase;

-- 执行导入
SOURCE /path/to/backup.sql;

3. 导入特定数据表

方法一：从完整备份中导入单个表

# 先导出表结构
mysqldump -u root -p --no-data mydatabase mytable > mytable_structure.sql

# 导出表数据（不包含结构）
mysqldump -u root -p --no-create-info mydatabase mytable > mytable_data.sql

# 导入表结构
mysql -u root -p mydatabase < mytable_structure.sql

# 导入表数据
mysql -u root -p mydatabase < mytable_data.sql

方法二：使用 mysqldump 导出单个表后导入

# 导出单个表
mysqldump -u root -p mydatabase mytable > mytable_backup.sql

# 导入单个表
mysql -u root -p mydatabase < mytable_backup.sql

4. 导入 CSV 文件到数据表

-- 在 MySQL 中执行
LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE mytable
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;  -- 如果CSV有标题行，忽略第一行

5. 使用 mysqlimport 工具导入

# 导入CSV文件到指定表
mysqlimport -u root -p --fields-terminated-by=, --lines-terminated-by="\n" mydatabase /path/to/file.csv

# 注意：csv文件名必须与表名相同

常见问题解决方案

1. 权限问题：

   # 授予文件权限
   GRANT FILE ON *.* TO 'username'@'localhost';

2. 大文件导入：

   # 增加超时时间
   mysql -u root -p --max_allowed_packet=512M mydatabase < large_backup.sql

3. 只导入数据不导入结构：

   # 添加 --no-create-info 参数
   mysqldump -u root -p --no-create-info mydatabase > data_only.sql

注意事项

1. 导入前最好先备份现有数据
2. 确保导入文件的字符集与数据库一致
3. 大文件导入建议在低峰期进行
4. 导入过程中不要中断操作
5. 检查外键约束，必要时先禁用

   SET FOREIGN_KEY_CHECKS = 0;
   -- 导入操作
   SET FOREIGN_KEY_CHECKS = 1;

您可以根据具体需求选择合适的导入方法。

[wxb]

mysql中decimal(M, D)类型的存储及行为相关知识

• M范围是1到65，D范围是0到30，且D不大于M

• 使用二进制格式存储，每4字节存储9位数字

  • 例如，DECIMAL(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。
  • 存储空间 = 整数部分字节 + 小数部分字节 + 1字节（符号位）
  • 最大存储空间为 30 字节（DECIMAL(65,30)）

• decimal小数位

  • 超出时，超出部分会四舍五入
  • 不足会自动补0

• decimal整数位

  • 超出时，会报错插入失败
  • 首位数字为0自动忽略

• 使用场景

  • 金融系统：精确存储金额、利率、汇率
  • 避免浮点数精度问题（如 0.1+0.2≠0.3）
  • 科学计算
  • 计量系统

• 优点

  • 精确计算：完全避免浮点数精度问题
  • 大范围支持：最大支持65位数字
  • 可配置精度：自由定义整数和小数位数
  • 确定性结果：计算结果是精确的，不依赖硬件

• 缺点

  • 存储空间大：比FLOAT/DOUBLE占用更多空间
  • 计算性能低：比浮点数运算慢约2-5倍
  • 复杂查询性能差：涉及DECIMAL的GROUP BY/ORDER BY较慢
  • 转换开销：与程序语言的浮点数转换可能损失精度

[wxb]

MySQL的索引

在 MySQL 中，索引是基于 B+ 树实现的，聚簇索引、二级索引和联合索引是三种常见的索引类型，它们的核心区别在于 B+ 树叶子节点存储的内容 和 应用场景 不同。

一、聚簇索引（Clustered Index）

聚簇索引是 数据物理存储顺序与索引顺序一致 的索引，即索引的 B+ 树叶子节点直接存储 完整的一行数据。

特点：

1. 一个表只有一个聚簇索引（因为数据物理存储顺序只有一种）。
2. 默认使用主键作为聚簇索引：如果表定义了主键，InnoDB 会以主键构建聚簇索引；如果没有主键，会选择唯一非空索引作为聚簇索引；如果都没有，InnoDB 会隐式生成一个自增的 rowid 作为聚簇索引。
3. 查询效率最高：通过聚簇索引查询时，找到叶子节点就直接获取了整行数据，不需要额外的磁盘 IO。

示例：

假设有一张 user 表，主键为 id，聚簇索引的 B+ 树结构如下：

• 非叶子节点：存储 id 的索引键和子节点指针（用于导航）。
• 叶子节点：存储 id + name + age + ...（整行数据），且按 id 有序排列。

当执行 SELECT * FROM user WHERE id = 100 时，通过聚簇索引找到叶子节点后，直接就能拿到该用户的所有信息。

二、二级索引（Secondary Index，又称非聚簇索引）

二级索引是 索引顺序与数据物理存储顺序无关 的索引，其 B+ 树叶子节点存储的是 索引键 + 聚簇索引键（主键），而非完整数据。

特点：

1. 一个表可以有多个二级索引（如按 name、age 等字段建立的索引）。
2. 查询需要“回表”：通过二级索引查询时，先找到叶子节点中的主键值，再通过主键到聚簇索引中查找完整数据（这个过程称为“回表”）。
3. 索引体积小：叶子节点不存完整数据，因此二级索引的 B+ 树更“矮胖”，占用磁盘空间更少。

示例：

在 user 表上为 name 建立二级索引，其 B+ 树结构如下：

• 非叶子节点：存储 name 的索引键和子节点指针。
• 叶子节点：存储 name + id（主键），按 name 有序排列。

当执行 SELECT * FROM user WHERE name = '张三' 时：

1. 先通过 name 二级索引找到对应的 id（如 100）。
2. 再通过 id = 100 到聚簇索引中查找到完整的用户数据（回表过程）。

三、联合索引（Composite Index）

联合索引是基于 多个字段组合 建立的索引，其 B+ 树的索引键是 多个字段的有序组合，叶子节点存储的是 联合索引键 + 聚簇索引键（主键）。

特点：

1. 遵循“最左前缀原则”：索引的生效顺序从左到右，例如 (a, b, c) 的联合索引，能生效的查询条件是 a、a AND b、a AND b AND c，但 b、b AND c 等不包含最左字段 a 的条件无法使用该索引。
2. 减少回表次数：如果查询的字段刚好是联合索引的字段 + 主键，就不需要回表（称为“索引覆盖”）。
3. 替代多个单列索引：一个联合索引 (a, b) 可以替代 a 的单列索引，但不能替代 b 的单列索引，合理设计可减少索引数量，节省空间。

示例：

在 user 表上建立 (age, name) 的联合索引，其 B+ 树结构如下：

• 非叶子节点：按 age 排序，相同 age 内部按 name 排序，存储组合键和子节点指针。
• 叶子节点：存储 age + name + id（主键），按 age 先排序，age 相同则按 name 排序。

适用场景：

• SELECT * FROM user WHERE age = 20 AND name = '张三'（全匹配，效率高）。
• SELECT id FROM user WHERE age = 20（索引覆盖，无需回表）。
• 不适用 SELECT * FROM user WHERE name = '张三'（不满足最左前缀，无法使用索引）。

三者的核心区别总结

索引类型	叶子节点存储内容	数量限制	典型查询场景	额外开销
聚簇索引	完整的一行数据	1个（默认主键）	按主键查询整行数据	无（无需回表）
二级索引	索引键 + 主键	多个	按单个非主键字段查询	可能需要回表
联合索引	联合索引键 + 主键	多个	多字段组合查询（遵循最左前缀）	可能需要回表

理解这三种索引的区别，是 MySQL 性能优化的基础。例如：

• 频繁按主键查询时，依赖聚簇索引的高效性；
• 频繁按多个字段筛选时，优先考虑联合索引（利用最左前缀和索引覆盖）；
• 避免过度创建二级索引（会增加插入/删除的开销，因为每个索引的 B+ 树都需要维护）。

[wxb]

InnoDB 索引使用 B+树数据结构

MySQL中，B+树主要用作索引的数据结构，尤其是InnoDB存储引擎的聚簇索引，它直接决定了数据的存储和查询效率。简单说，B+树是一种多叉平衡树，专门为磁盘IO优化设计，能让MySQL快速定位到目标数据。

一、B+树的核心结构特点

B+树的结构分为“非叶子节点”和“叶子节点”两层，每层职责明确，完全适配磁盘存储的特性（磁盘读取以“页”为单位，单次读取多数据更高效）。

1. 非叶子节点：只存索引键，不存实际数据

   • 作用是“导航”，类似目录的章节目录，只记录下一级节点的索引范围和地址。
   • 每个节点包含多个“索引键+子节点指针”，键的数量由磁盘页大小决定（比如InnoDB默认页16KB，能存更多键，树的高度更低）。

2. 叶子节点：存完整索引键+实际数据（或数据地址）

   • 这是B+树的“数据层”，InnoDB的聚簇索引中，叶子节点直接存整行数据；非聚簇索引（如二级索引）存的是“索引键+聚簇索引键（主键）”，需要回表查询完整数据。
   • 所有叶子节点通过双向链表连接，能快速实现“范围查询”（比如查id在100-200之间的数据，找到起始叶子节点后，顺着链表遍历即可）。

3. 树高度低，查询效率稳定

   • 由于非叶子节点不存数据，单个节点能存更多索引键，树的高度通常只有2-4层（即使数据量达千万级）。
   • 不管查哪个数据，都只需要2-4次磁盘IO，查询效率不会随数据量增长而大幅下降（对比二叉树，数据量大时高度会急剧增加，IO次数飙升）。

二、MySQL中B+树的核心作用：索引实现

MySQL的索引本质就是用B+树组织起来的“有序数据结构”，不同索引的B+树结构略有差异，但核心逻辑一致。

索引类型	B+树叶子节点存储内容	核心用途
聚簇索引（主键）	完整的一行数据	按主键快速查询整行数据
二级索引（非主键）	索引键 + 聚簇索引键（主键）	按非主键字段查询（需回表）
联合索引	联合索引键 + 聚簇索引键	多字段组合查询（最左前缀原则）

三、B+树相比其他结构的优势（为什么不用B树/二叉树）

1. 对比B树：更适合磁盘IO

   • B树的非叶子节点也存数据，单个节点能存的索引键更少，树的高度更高，磁盘IO次数更多；
   • B+树非叶子节点只存索引键，叶子节点存所有数据且有序链接，既减少IO，又支持范围查询。

2. 对比二叉树：避免“树失衡”

   • 普通二叉树在数据有序插入时会变成“链表”（高度等于数据量），查询效率从O(logn)退化为O(n)；
   • B+树是“平衡树”，插入/删除时会自动调整结构，保证树高度稳定，查询效率始终是O(logn)。

四、B+树的插入与删除：保证平衡的关键

B+树能稳定工作，核心在于插入和删除时的“平衡调整机制”，避免树出现“一边高一边低”的情况：

• 插入：当节点数据满了（达到页大小限制），会触发“分裂”——将节点分成两个，中间的索引键提升到父节点，保证树高度不变；
• 删除：当节点数据过少（低于阈值），会尝试“合并”相邻节点，或从兄弟节点“借”数据，确保节点利用率，同时维持树的平衡。

[wxb]

各种树形式的数据结构

一、二叉树（Binary Tree）

最基础的树结构，每个节点最多有两个子节点（左子树、右子树），没有其他规则限制。

• 特点：结构简单，但可能退化为“链表”（如插入有序数据时，所有节点都只有右子树，高度等于数据量）。
• 问题：查询效率不稳定，最佳情况O(logn)，最坏情况O(n)（类似链表遍历）。
• 适用场景：几乎不直接用于实际开发，是其他高级树结构的基础。

二、平衡二叉树（Balanced Binary Tree）

对二叉树的“平衡”约束：左右子树的高度差不超过1（典型代表是AVL树）。

• 核心目标：避免二叉树退化为链表，保证查询效率稳定在O(logn)。
• 特点：插入/删除时会通过“旋转”（左旋、右旋）调整结构，维持平衡。
• 问题：平衡条件严格，频繁插入/删除时旋转操作多，性能开销大；且仅适合内存中的小规模数据（节点存储数据量少，树高度较高）。
• 适用场景：内存中对查询效率要求高、插入删除不频繁的场景（如某些缓存实现）。

三、红黑树（Red-Black Tree）

一种“近似平衡”的二叉树，通过颜色规则（红/黑）和旋转维持平衡，平衡条件比AVL树宽松。

• 核心规则：
  1. 节点非红即黑；
  2. 根节点是黑色；
  3. 叶子节点（空节点）是黑色；
  4. 红色节点的子节点必须是黑色；
  5. 从任意节点到其叶子节点的所有路径，黑色节点数量相同。
• 特点：
  • 插入/删除时旋转次数少（最多3次），性能优于AVL树；
  • 树的高度最坏情况下是2log(n+1)，仍能保证查询效率O(logn)。
• 适用场景：内存中的动态数据结构（如Java的TreeMap、HashMap的桶扩容后结构、Linux内核的进程调度等），适合频繁插入删除的场景。

四、B树（B-Tree）

多叉平衡树（不是二叉！），专为磁盘存储设计，节点可以存储多个数据，降低树的高度。

• 核心结构：
  • 每个节点有多个“关键字”（按顺序排列），关键字数量=子节点数量-1；
  • 非叶子节点同时存储关键字和子节点指针；
  • 叶子节点存储关键字和对应数据（或数据地址）；
  • 所有叶子节点在同一层，保证平衡。
• 特点：
  • 多叉结构使树高度极低（例如100阶B树，百万级数据仅需3层），大幅减少磁盘IO次数；
  • 每个节点大小通常等于磁盘页（如4KB），单次IO可加载整个节点，提高效率。
• 问题：非叶子节点存储数据，导致节点能容纳的关键字数量减少，树高度略高于B+树；不支持范围查询的快速遍历。
• 适用场景：磁盘存储系统（如早期数据库索引、文件系统）。

五、B+树（B+ Tree）

B树的优化版本，是数据库索引的主流实现（如MySQL InnoDB）。

• 核心改进：
  1. 非叶子节点只存关键字（导航作用），不存数据：单个节点可容纳更多关键字，树高度更低（比同阶B树更“矮胖”）。
  2. 叶子节点存所有关键字+数据：且叶子节点通过双向链表连接，支持高效范围查询（如id > 100）。
  3. 所有查询必须到叶子节点，查询效率稳定（不像B树可能在非叶子节点找到数据）。
• 特点：
  • 磁盘IO效率更高（树更矮，节点关键字更多）；
  • 范围查询极快（叶子节点链表遍历）；
  • 适合大量数据的有序存储和查询。
• 适用场景：关系型数据库索引（MySQL、PostgreSQL等）、NoSQL数据库的有序存储场景。

六、核心区别对比

结构	节点类型	平衡方式	适用场景	核心优势
二叉树	二叉	无	基础理论	结构最简单
平衡二叉树	二叉	严格平衡（高度差≤1）	内存中查询优先场景	查询效率稳定O(logn)
红黑树	二叉	颜色规则+近似平衡	内存中频繁插入删除场景	插入删除效率高
B树	多叉	节点分裂/合并	早期磁盘存储系统	减少磁盘IO（多叉结构）
B+树	多叉	节点分裂/合并+链表连接	数据库索引、大规模有序存储	范围查询高效，IO效率最优

总结

• 内存场景：红黑树（平衡二叉树的高效替代）是首选，兼顾查询和插入删除性能。
• 磁盘/大规模数据场景：B+树碾压其他结构，通过多叉+链表设计，完美适配磁盘IO特性和范围查询需求。
• 从“二叉”到“多叉”，从“内存”到“磁盘”，这些树结构的演进本质是不断优化数据访问的效率，适配不同存储介质的特性（内存速度快但容量小，磁盘容量大但IO慢）。

[wxb]

为什么说B树和B+树专门为磁盘存储设计

B树和B+树被称为“专为磁盘存储设计”，核心原因是它们的结构特性完美适配了磁盘的存储原理和IO特性，能够最大限度减少磁盘访问次数（这是提升磁盘存储系统性能的关键）。

一、先理解磁盘的“致命弱点”：IO效率低

磁盘存储的底层原理决定了它和内存的访问效率有天壤之别：

• 内存：随机访问速度极快（纳秒级），可以直接通过地址定位数据。
• 磁盘：数据存储在物理盘片上，需要通过磁头移动（寻道）和盘片旋转（旋转延迟）才能定位数据，一次IO操作耗时约毫秒级（比内存慢10万倍以上）。

更重要的是：磁盘IO是“按块读取”的——每次IO会读取一整个“磁盘块”（通常4KB、8KB或16KB），即使只需要这个块中的1个字节，也必须把整个块加载到内存。

二、B树/B+树如何适配磁盘特性？

它们通过多叉结构和节点大小优化，完美解决了磁盘IO效率低的问题，核心设计思路是：让单次磁盘IO能加载更多有效数据，同时减少查询所需的IO次数。

1. 多叉结构：降低树的高度，减少IO次数

• 二叉树（如红黑树）每个节点最多2个子节点，当数据量达到百万级时，树的高度会超过20（2^20≈100万），查询需要20次磁盘IO（每次IO加载一个节点）。

• B树/B+树是多叉树（一个节点可以有100+个子节点，称为“阶数高”），例如100阶的B树：

  • 第1层（根节点）：100个关键字 → 指向101个子节点
  • 第2层：每个节点100个关键字 → 总关键字100×101≈1万
  • 第3层：总关键字100×100×101≈100万
  • 第4层：总关键字可达1亿级

  这意味着：存储1亿条数据，B树/B+树的高度仅需4层，查询最多只需4次磁盘IO（对比二叉树的27次），IO次数呈指数级减少。

2. 节点大小=磁盘块大小：最大化单次IO的利用率

B树/B+树的每个节点设计为恰好等于一个磁盘块的大小（如4KB）：

• 每次磁盘IO会把整个节点（一个磁盘块）加载到内存，节点中的所有关键字和指针都能被高效利用。
• 例如，一个4KB的节点，若每个关键字+指针占40字节，可存储约100个关键字（40×100=4000字节），正好匹配100阶B树的设计，单次IO就能加载100个导航信息。

反观二叉树，每个节点只存1个关键字+2个指针（约20字节），若按4KB磁盘块读取，单次IO的利用率仅为0.5%（20/4096），严重浪费磁盘带宽。

3. B+树的额外优化：进一步适配范围查询

B+树在B树基础上，针对磁盘存储做了更极致的优化：

• 非叶子节点只存导航信息：不存实际数据，相同大小的节点可容纳更多关键字（比同阶B树多存50%以上），树更“矮胖”，IO次数更少。
• 叶子节点用链表连接：磁盘存储中，范围查询（如id>100 and id<200）是高频操作，B+树的叶子节点通过双向链表串联，找到起始位置后可顺序遍历，无需回退到父节点重新查询，大幅减少IO次数。

三、总结：为什么它们是磁盘的“天选之子”？

磁盘的核心瓶颈是IO次数和单次IO的有效数据量，而B树/B+树的设计完全围绕这两点：

1. 多叉高阶 → 树高度极低 → 大幅减少查询所需的IO次数；
2. 节点大小=磁盘块大小 → 单次IO利用率100% → 不浪费磁盘带宽；
3. B+树的链表结构 → 范围查询效率极高 → 适配磁盘上的有序数据访问场景。

这就是为什么数据库（MySQL、Oracle）、文件系统（如EXT4）等依赖磁盘存储的系统，都选择B树/B+树作为核心数据结构——它们不是为内存设计的，而是为“慢且按块读取”的磁盘量身定制的。

[wxb]

【MySQL 系列】MySQL 索引篇

转载
好文值的仔细研读

2.1、B+ 树索引

3.1、MySQL 数据页结构

3.2、User Records 如何组织数据

4.1、什么是聚簇索引
聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据 ；

4.2、非聚簇索引和二级索引
一张表只能有一个聚簇索引，那为了实现非主键字段的快速搜索，就引出了二级索引（非聚簇索引/辅助索引），它也是利用了 B+ 树的数据结构，但是二级索引的叶子节点存放的是主键值，不是实际数据。