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重建索引。
- 定期维护:定期执行优化操作。