全面理解MySQL架构
1. 索引的本质
索引是帮助MySQL高效获取数据的排好序的数据结构。
/**
查询 age = 38 的数据
*/
select * from person where age = 89;/**
请先创建 study 数据库，
然后引用下面SQL在库中创建person表
*/
CREATE TABLE `study`.`person` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
`age` int NULL DEFAULT NULL COMMENT '年龄',
`creater` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建人',
`create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
`updater` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '更新人',
`update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
`deleted` bit(1) NULL DEFAULT NULL COMMENT '是否删除',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;/**
插入数据
*/
INSERT INTO `study`.`person` (`id`, `name`, `age`, `creater`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (1, '贺云熙', 34, 'Vn9AIaFWJR', '2009-05-28 17:24:41', '2010-01-29', '2021-08-26 06:38:24', b'0');
INSERT INTO `study`.`person` (`id`, `name`, `age`, `creater`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (2, 'Gary Nguyen', 77, 'r41QNcApex', '2022-02-25 00:35:03', '2010-11-26', '2006-01-15 16:58:35', b'0');
INSERT INTO `study`.`person` (`id`, `name`, `age`, `creater`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (3, '程睿', 5, '3nwym3nU3a', '2002-04-03 11:27:21', '2004-08-04', '2005-05-07 01:19:19', b'0');
INSERT INTO `study`.`person` (`id`, `name`, `age`, `creater`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (4, '侯晓明', 91, 'Z3vUAwY1n5', '2003-01-18 03:00:17', '2024-06-05', '2021-01-22 18:20:15', b'0');
INSERT INTO `study`.`person` (`id`, `name`, `age`, `creater`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (5, '莫睿', 22, 'Rb2auD6cuC', '2000-07-07 16:27:05', '2000-02-05', '2019-06-10 02:16:37', b'0');
INSERT INTO `study`.`person` (`id`, `name`, `age`, `creater`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (6, 'Tiffany Harris', 89, 'wYL3jLpwlK', '2024-01-10 06:17:43', '2012-11-23', '2017-08-11 16:29:06', b'0');
INSERT INTO `study`.`person` (`id`, `name`, `age`, `creater`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (7, '余睿', 23, 'VZ6T3v1cJN', '2004-11-22 04:34:16', '2003-05-09', '2012-04-18 19:15:20', b'0');假设每次从磁盘拿取一条数据，当没有在 age 列创建索引时，需要从第一条数据开始遍历，直至找到 age = 89 的数据为止，需要 6 次 I/O 交互。
从磁盘中拿取数据需要与磁盘做 I/O 交互，在没有索引的情况下，需要在磁盘中逐条数据拿取，然后在程序中通过CPU进行比对，查看是否符合查询条件。明显，这样的效率是不高的。
因此提高查询效率的关键之一就是减少与磁盘交互的 I/O 次数，如果将次数控制在一定的范围内，则效率会有明显提升，这也是数据结构的明显作用。
数据在硬盘中的存储
数据表中的数据在电脑磁盘(硬盘)中是随机分布的，并不一定是紧邻的。
比如我第一天往表里插入 id = 1 的记录，过了几天才插入了 id = 2 的记录，而磁盘写入数据是一个磁道一个磁道去写入，因此他们的实际存储位置可能不是紧邻的，插入 id = 2 的数据之前，可能早已有其他程序已经把 id = 1 的数据所在磁盘位置的周围都写满了。
2. 索引数据结构
数据结构网址，可在其中了解各类数据结构的存储方式
2.1 二叉排序树
- 对半搜索,每个节点最多两个子节点
- 若左子树不空，则左子树上所有节点的值都小于根节点的值；
- 若右子树不空，则右子树上所有节点的值都大于根节点的值；
- 任意节点的子树也都是二叉排序树(二叉搜索树)；
- 二叉排序树的查找性能在O(log2n) - O(n),其中 n 是节点数量。
如果在 age 字段上使用 二叉树 结构建立一个索引，key为 age 的值，value为 对应行数据所在的磁盘文件地址 ，则从根节点去查找，只需要两次I/O交互，即可查询到指定数据
然而，在实际应用中，二叉排序树如果不进行任何平衡维护，可能会退化成链表结构（比如连续插入已排序的数据），这会导致最坏情况下的查找、插入和删除的时间复杂度退化为O(n)。
为了保持高效的查找性能，数据库系统通常不会使用普通的二叉排序树作为索引结构，而是选择自平衡的树结构如红黑树（Red-Black Tree）或 AVL 树，或者更常见的 B-Tree 和其变种（例如B+树），这是因为它们在磁盘I/O方面表现更好，并且总是保持相对平衡，从而确保了平均和最坏情况下的查找时间都是O(log n)。