[MySQL 面试题]-索引 33-34 什么时候适合创建索引，什么时候不适合创建索引？

33 什么时候适合创建索引，什么时候不适合创建索引？

适合创建索引的场景

场景类别	具体说明	理由
主键/唯一约束字段	如：ID、手机号，邮箱	保证唯一性+加速查询
高频查询字段	经常出现在where，join、order by、group by 中的字段	提高查询效率，避免全表扫描
大数据量表	数据行数较多时对某列进行筛选	使用索引可显著减少I/O
范围查询字段	使用 between >、<, ≥, ≤ 查询	可有效使用 B+ 树索引进行范围遍历
组合条件过滤字段	多字段经常联合查询	可创建复合索引来优化组合查询效率
外键字段	外键关系中的引用字段	提高关联一致性查询效率
覆盖查询字段	查询中涉及索引中的字段	可使用“覆盖索引”，避免回表操作

不适合创建索引的场景

场景类别	具体说明	理由
低基数字段	如：性别、布尔字段、状态（状态值极少）	索引选择性差，全表扫描反而更快
频繁更新的字段	如经常更新的记录时间，计数器等	修改数据时需要维护索引，性能开销大
不参与查询条件的字段	仅用于显示、备注的字段	无查询用途，创建索引无意义
大文本字段	如 text、blob、longtext	索引效率低，占用空间大、不能精确匹配
临时表/小表	数据量小的临时计算结果表	全表扫描成本更低，创建索引反而浪费资源
频繁变动结构的表	动态新增/删除列的系统表	索引维护复杂，使用成本高

---

34 什么是索引下推（Index Condition Pushdown, ICP）？

目的是 减少回表次数，提高查询性能

是MySQL在 InnoDB存储引擎中的一种优化策略

它指的是：在使用二级索引（非聚簇索引）查询时，将更多的 where 条件在索引层（存储引擎层）完成过滤 ，而不是先拿到主键再到 Server 层过滤。

MySQL 查询流程中通常包括：

1. 使用 二级索引 找到满足索引条件的主键值，
2. 回表（根据主键）到聚簇索引读取完整行数据
3. 在 Server 应用层用剩余 where 条件

ICP的优化目标是在第1步时就应用尽可能多的过滤条件，减少不必要的回表操作.

Using index condition: 叫作 Index Condition Pushdown Optimization （索引下推优化）

创建一个测试表

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary INT,
    hire_date DATE,
    INDEX idx_department_salary (department, salary),
    INDEX idx_last_name (last_name)
) ENGINE=InnoDB;

用go生成测试数据 1w 条记录

package main

import (
	"database/sql"
	"fmt"
	"log"
	"math/rand"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	// 数据库连接配置
	db, err := sql.Open("mysql", "root:admin123@tcp(127.0.0.1:3306)/exp")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// 生成测试数据
	generateTestData(db, 10000)
}

func generateTestData(db *sql.DB, count int) {
	// 准备插入语句
	stmt, err := db.Prepare(`
		INSERT INTO employees 
		(first_name, last_name, department, salary, hire_date) 
		VALUES (?, ?, ?, ?, ?)
	`)
	if err != nil {
		log.Fatal("准备插入语句失败:", err)
	}
	defer stmt.Close()

	// 部门列表
	departments := []string{"Engineering", "Marketing", "Sales", "HR", "Finance"}

	// 开始事务
	tx, err := db.Begin()
	if err != nil {
		log.Fatal("开始事务失败:", err)
	}

	// 设置随机种子
	rand.Seed(time.Now().UnixNano())

	// 生成数据
	for i := 0; i < count; i++ {
		// 随机选择部门
		dept := departments[rand.Intn(len(departments))]

		// 基于部门的薪资范围
		var salary int
		switch dept {
		case "Engineering":
			salary = 50000 + rand.Intn(100000)
		case "Marketing":
			salary = 40000 + rand.Intn(80000)
		case "Sales":
			salary = 30000 + rand.Intn(120000)
		case "HR":
			salary = 35000 + rand.Intn(70000)
		default: // Finance
			salary = 45000 + rand.Intn(90000)
		}

		// 随机雇佣日期(最近5年)
		daysAgo := rand.Intn(1825) // 5年大约1825天
		hireDate := time.Now().AddDate(0, 0, -daysAgo).Format("2006-01-02")

		// 随机名字
		firstName := fmt.Sprintf("First%d", rand.Intn(1000))
		lastName := fmt.Sprintf("Last%d", rand.Intn(1000))

		// 执行插入
		_, err = stmt.Exec(firstName, lastName, dept, salary, hireDate)
		if err != nil {
			tx.Rollback()
			log.Fatal("插入数据失败:", err)
		}
	}

	// 提交事务
	err = tx.Commit()
	if err != nil {
		log.Fatal("提交事务失败:", err)
	}
	fmt.Printf("成功插入 %d 条测试数据\n", count)
}

验证

-- 查看ICP相关的状态变量
SHOW STATUS LIKE 'Handler_read%';

Handler_read_first	18
Handler_read_key	538
Handler_read_last	0
Handler_read_next	235
Handler_read_prev	0
Handler_read_rnd	18
Handler_read_rnd_next	750

-- 不使用ICP的情况(MySQL 5.6之前)
SET optimizer_switch='index_condition_pushdown=off';
EXPLAIN SELECT * FROM employees 
WHERE department = 'Engineering' AND salary > 80000 AND first_name LIKE 'First1%';

-- 使用ICP的情况
SET optimizer_switch='index_condition_pushdown=on';
EXPLAIN SELECT * FROM employees 
WHERE department = 'Engineering' AND salary > 80000 AND first_name LIKE 'First1%';

{
  "id": 1,
  "select_type": "SIMPLE",
  "table": "employees",
  "partitions": null,
  "type": "range",                              -- 表示了使用范围查找
  "possible_keys": "idx_department_salary",
  "key": "idx_department_salary",               -- 实际使用了复合索引
  "key_len": "208",
  "ref": null,
  "rows": "1467",                               -- 预估 1467 行
  "filtered": 11.11,                            -- 经过过滤条件后，保留约11.11%的数据（163行）
  "Extra": "Using index condition; Using where" -- 说明使用了索引下推
}

-- 启用ICP并测试执行时间
SET optimizer_switch='index_condition_pushdown=on';
SELECT SQL_NO_CACHE * FROM employees 
WHERE department = 'Marketing' AND salary > 60000 AND first_name LIKE 'First2%';

-- 禁用ICP并测试执行时间
SET optimizer_switch='index_condition_pushdown=off';
SELECT SQL_NO_CACHE * FROM employees 
WHERE department = 'Marketing' AND salary > 60000 AND first_name LIKE 'First2%';