面试题：MySQL中InnoDB B+树索引构建过程及常见优化点

初始化：当创建表并为某列建立索引时，InnoDB开始构建B+树索引。最初，B+树为空，随着数据插入，逐渐构建。
插入节点：
- 叶子节点：新插入的数据首先查找合适的叶子节点位置。通过根节点开始，根据索引列值比较，沿着分支节点向下找到对应的叶子节点。如果叶子节点空间足够，直接插入数据记录（包含索引列值和对应的行数据指针）；若空间不足，进行节点分裂。
- 节点分裂：叶子节点分裂时，将原节点中的数据大致平分到两个新节点。原节点保留一半数据，另一半数据放入新节点。同时，原节点的父节点会新增一个指向新节点的指针，并更新分裂后节点的范围。如果父节点空间也不足，继续向上分裂，直到根节点。根节点分裂时，会产生一个新的根节点，树的高度增加一层。
维护结构：在插入、删除数据过程中，B+树会自动维护结构。例如删除数据时，如果叶子节点数据太少，会尝试与相邻节点合并，父节点指针也相应调整；如果合并导致父节点数据过少，会继续向上合并操作，以保持树的平衡和高效性。

合理创建索引：
- 覆盖索引：查询中涉及的列尽量都包含在索引中，这样查询时直接从索引中获取数据，无需回表操作。例如，SELECT col1, col2 FROM table WHERE col1 = 'value'; 如果为col1和col2创建联合索引 (col1, col2)，就可以利用覆盖索引提升性能。
- 前缀索引：对于较长的字符串列，使用前缀索引。如CREATE INDEX idx_name ON table_name (long_string_col(10)); 只对字符串的前10个字符建立索引，可减少索引空间占用，提升查询效率，但需权衡索引选择性。
避免索引失效：
- 避免函数操作：在索引列上避免使用函数，如 SELECT * FROM table WHERE UPPER(col1) = 'VALUE'; 会导致索引失效，应改为 SELECT * FROM table WHERE col1 = 'value';
- 避免类型转换：确保查询条件中的数据类型与索引列类型一致。例如，索引列是int类型，查询时不应写成 SELECT * FROM table WHERE col1 = '1'; 而应是 SELECT * FROM table WHERE col1 = 1;
优化索引结构：
- 分析索引：使用 EXPLAIN 关键字分析查询语句，查看索引使用情况。如 EXPLAIN SELECT * FROM table WHERE col1 = 'value'; 根据 EXPLAIN 结果调整索引。
- 定期维护：对于频繁插入、删除数据的表，定期重建或优化索引，以整理碎片，提升索引性能。如在MySQL中可使用 ALTER TABLE table_name ENGINE=InnoDB; 语句重建表及索引。

星途面试题库