MySQL索引18连问，谁能顶住

1. 索引是什么？

• 定义：索引是一种数据结构，用于提升查询和检索数据的速度。类似于书籍的目录，帮助快速定位数据位置。
• 物理特性：索引是一个文件，占用物理空间。

2. MySQL索引有哪些类型？

• 数据结构维度：
  • B+树索引：最常用，数据存储在叶子节点，时间复杂度为O(log n)，擅长范围查询。
  • Hash索引：通过哈希算法存储，擅长等值查询（=，in）。
  • Full-text索引：建立倒排索引，用于信息检索，支持程度因版本而异。
  • R-Tree索引：用于地理空间数据类型查询，使用较少。
• 物理存储维度：
  • 聚簇索引：数据按索引顺序存储，InnoDB引擎要求必须有。
  • 非聚簇索引：基于指针的索引，存储表主键和索引列。
• 逻辑维度：
  • 主键索引：唯一，不允许重复或为空。
  • 普通索引：允许重复值和空值。
  • 联合索引：多字段索引，遵循最左前缀规则。
  • 唯一索引：值必须唯一，允许空值。
  • 空间索引：针对空间数据类型建立的特殊索引。

3. 主键索引和唯一索引的区别

• 数量限制：主键索引一张表只能有一个，唯一索引可以有多个。
• 本质区别：唯一索引可以为空，主键索引不可以。
• 外键引用：主键可以被其他表作为外键，唯一索引不能。

4. 聚簇索引和非聚簇索引

• 聚簇索引：数据按索引顺序存储，减少磁盘随机读取，效率高。
• 非聚簇索引：基于指针的索引，查询时需要额外磁盘I/O操作。

5. 复合索引和单列索引的区别

• 单列索引：一个字段上创建，查询单个字段时快速。
• 复合索引：多个字段上创建，支持多字段查询，列顺序重要。

6. Hash 索引和 B+ 树索引的区别

• 哈希索引：
  • 工作原理：通过哈希算法存储。
  • 查询效率：等值查询高效，范围查询和排序效率低。
  • 磁盘存储：随机访问，可能降低磁盘效率。
  • 插入和删除：操作简单。
• B+树索引：
  • 工作原理：平衡树，顺序存储。
  • 查询效率：擅长范围查询和排序。
  • 磁盘存储：顺序访问，减少I/O次数。
  • 插入和删除：需要维护树平衡，操作复杂。

7. 索引是否越多越好？

• 不是：索引增加查询和维护开销，占用更多存储空间。
• 适用情况：数据量小的表不需要索引，数据重复且分布平均的字段不适合索引。

8. 索引什么时候会失效？

• 常见情况：
  • 查询条件包含OR。
  • 多列索引没有最左匹配。
  • LIKE查询以%开头。
  • 索引列参与计算。
  • 类型不匹配导致隐式转换。
  • 全表扫描效率更优。
  • 数据分布不均。
  • 索引列包含NULL值。

9. 哪些情况下适合建立索引？

• 高频查询列：如username或email。
• 连接键的列：用于表连接操作。
• 具有唯一性约束的列：如主键。
• 排序和分组操作的列：如ORDER BY列。
• 具有高选择性的列：值分布广泛的列。
• 多列查询的前导列：常用作查询条件的列。

10. 为什么要用 B+ 树，而不用二叉树？

• 查询性能稳定：B+树查询时间复杂度为O(log n)。
• 空间局部性：B+树叶子节点形成有序链表。
• 磁盘I/O优化：B+树减少磁盘访问次数。
• 高效的范围查询和排序：B+树有序链表结构。
• 节点分裂和合并的开销：B+树减少节点分裂和合并次数。
• 非叶子节点的简洁性：B+树非叶子节点不存储数据。
• 更新操作的效率：B+树更新操作效率高。

11. 什么是回表？如何减少回表？

• 定义：通过索引定位数据行后，需要访问表中的其他列数据的过程。
• 减少方法：
  • 覆盖索引：查询结果完全通过索引获取。
  • **避免SELECT ***：指定需要的列。
  • 索引包含所需列：确保索引包含所有查询列。
  • 使用复合索引：创建包含多个查询列的索引。
  • 优化查询逻辑：减少不必要的回表。
  • 使用物化视图或汇总表：存储查询结果。

12. 位图索引及其使用场景

• 定义：将数据列的所有可能值映射到二进制位上。
• 适用场景：低基数列（不同值数量较少）。
• 示例：为age和country列创建位图索引。

13. 如何查看MySQL表中已有的索引？

• 方法一：使用SHOW INDEX。
• 方法二：查询information_schema数据库的STATISTICS表。

14. 如何在MySQL中创建全文索引？

• 创建方法：
  • 在建表时使用FULLTEXT INDEX。
  • 使用ALTER TABLE添加全文索引。
• 使用场景：内容管理平台（CMS），问答社区等。

15. 当表中的数据量非常大时，如何有效地维护和管理索引？

• 性能开销：索引维护增加计算和存储资源。
• 存储空间：索引占用存储空间。
• 索引失效：频繁更新导致索引页碎片化。
• 更新锁竞争：高并发更新中索引可能成为锁竞争瓶颈。

16. 如何优化字段的索引以提高排序操作的性能？

• 问题：为大表添加索引可能遇到问题。
• 解决方法：
  • 垂直拆分：按业务维度拆分。
  • 水平拆分：按行分片。
  • 创建索引：在建表时添加索引。
  • 分区：根据实际情况进行数据分区。
  • 优化查询语句：减少不必要的查询。
  • 分布式数据库：使用分布式数据库技术。

17. 如何优化索引

• 评估索引必要性：不是所有字段都需要索引。
• 选择正确索引类型：如B-tree适合范围查询，Hash适合等值查询。
• 优化索引列顺序：将常用查询条件列放在前面。
• 使用覆盖索引：避免访问数据行本身。
• 分析数据分布：避免在高度重复列上创建索引。
• 避免过度索引：减少数据库维护成本。

18. MySQL 索引碎片化的理解及检测和修复

• 检测方法：
  • 使用SHOW TABLE STATUS命令。
  • 查询INFORMATION_SCHEMA.TABLES表。
• 修复方法：
  • 优化表的存储引擎：使用OPTIMIZE TABLE。
  • 重建索引：使用ALTER TABLE重建索引。
  • 定期维护：定期执行优化操作。