跳到主要内容

MySQL 创建索引的原则

核心理念​

索引即数据，数据即索引 - 这是理解MySQL索引的核心理念

一、索引设计原则相关面试题​

1. 最左前缀匹配原则（⭐⭐⭐⭐⭐）​

面试官: 请解释MySQL联合索引的最左前缀匹配原则，并举例说明什么情况下索引会失效？

考察点:

  • 联合索引的工作机制
  • 索引失效的场景
  • SQL优化能力

标准答案: 最左前缀匹配原则是指在联合索引中，MySQL会从最左边的列开始匹配，直到遇到范围查询 (>、<、between、like) 就停止匹配。

优化建议: 如果建立(a,b,d,c)的索引，则a,b,d都可以用到索引。

2. 索引创建的其他重要原则​

面试官: 在什么情况下你会选择不创建索引？索引设计有哪些最佳实践？

考察点:

  • 索引的适用场景判断
  • 性能权衡思维
  • 实际项目经验

标准答案:

  1. 频繁查询字段才创建索引 - 避免无用索引占用空间
  2. 避免在频繁更新字段上创建索引 - 降低写入性能损耗
  3. 低区分度字段不适合索引 - 如性别字段(男/女/未知)
  4. 优先扩展现有索引 - 如已有索引a，需要(a,b)时修改现有索引
  5. 外键字段必须建索引 - 保证关联查询性能
  6. 避免在TEXT、BLOB等大字段建索引 - 影响索引效率

二、索引性能与优化相关面试题​

3. 索引一定能提高查询性能吗？（⭐⭐⭐⭐）​

面试官: 有人说"加了索引查询就一定快"，你认为这个说法对吗？什么情况下索引可能不起作用？

考察点:

  • 对索引性能的深度理解
  • 异常场景分析能力
  • 实际调优经验

不适用索引的场景:

  1. 低选择性查询 - 返回大部分数据时，全表扫描可能更快
  2. 频繁数据更新 - 索引维护成本超过查询收益
  3. 不合理的索引设计 - 索引冗余或过多
  4. 小表查询 - 数据量很小时索引开销大于收益

4. 索引的代价分析（⭐⭐⭐⭐）​

面试官: 你在项目中是如何权衡索引的收益和代价的？索引会带来哪些负面影响？

三、索引评估与选择相关面试题​

5. Cardinality 基数评估（⭐⭐⭐⭐⭐）​

面试官: 如何判断一个字段是否适合建立索引？请介绍 Cardinality 的概念和实际应用。

考察点:

  • 数据库优化理论知识
  • 实际性能调优经验
  • SQL分析能力

实际操作命令:

-- 查看索引基数
SHOW INDEX FROM table_name;

-- 计算字段唯一值数量
SELECT COUNT(DISTINCT column_name) FROM table_name;

-- 计算选择性
SELECT 
    COUNT(DISTINCT column_name)/COUNT(*) as selectivity 
FROM table_name;

基数分类标准:

  • 低基数（< 5%）: 如性别、状态字段，不适合建索引
  • 中等基数（5%-20%）: 如地区、分类，视查询频率决定
  • 高基数（> 20%）: 如用户ID、订单号，非常适合建索引

6. 索引创建注意事项（⭐⭐⭐）​

面试官: 在创建索引时，字段的选择有什么技巧？为什么要考虑字段的大小？

考察点:

  • 索引底层存储机制理解
  • 数据库页存储原理
  • 实际优化经验

关键原则:

  1. 非空字段优先 - NULL值使索引统计复杂化，影响优化器判断
  2. 离散度高的字段放在联合索引前面 - 提高索引过滤效率
  3. 字段越小越好 - 数据库以页为单位存储，字段小则单页存储更多索引项

四、综合实战面试题​

7. 索引失效场景分析（⭐⭐⭐⭐⭐）​

面试官: 给定以下SQL和索引，请分析哪些会导致索引失效？

-- 已有索引: KEY idx_name_age_status (name, age, status)

-- SQL1: SELECT * FROM users WHERE name = 'John' AND age = 25;
-- SQL2: SELECT * FROM users WHERE age = 25 AND status = 1;
-- SQL3: SELECT * FROM users WHERE name LIKE '%John%';
-- SQL4: SELECT * FROM users WHERE name = 'John' AND age > 25 AND status = 1;

时序图分析:

8. 索引优化实战（⭐⭐⭐⭐⭐）​

面试官: 如果你发现一个查询很慢，你会如何进行索引优化？请描述完整的分析流程。

优化流程时序图:

具体分析步骤:

  1. EXPLAIN分析 - 查看扫描行数、索引使用
  2. 慢查询日志 - 找出性能瓶颈SQL
  3. 索引覆盖分析 - 检查是否可以避免回表
  4. 基数统计 - 评估字段选择性
  5. 索引重构 - 合并、删除冗余索引

五、原有问题保留​

创建索引的原则​

索引虽好，但也不是无限制的使用，最好符合以下几个原则：

  1. 最左前缀匹配原则
  2. 较频繁作为查询条件的字段才去创建索引
  3. 更新频繁字段不适合创建索引
  4. 低区分度字段不适合做索引列
  5. 尽量扩展索引，不要新建索引
  6. 定义有外键的数据列一定要建立索引
  7. 对于查询中很少涉及的列，重复值比较多的列不要建立索引
  8. 对于定义为text、image和bit的数据类型的列不要建立索引

原始问题及答案​

  • 使用索引查询一定能提高性能吗？ - 不一定，取决于查询选择性、数据分布等因素
  • 索引带来的代价 - 存储空间、写入性能、维护成本等
  • 通过Cardinality值判断是否需要索引 - 高基数字段适合建索引

Reference​