本文基于 MySQL 8.0.35 LTS 版本实测，带你从现象到本质理解IN查询的索引选择

一、核心问题：破除常见误区

在MySQL开发中，下面这个问题的出现频率极高：

-- 这些查询会走索引吗？SELECT * FROM users WHERE id IN (1, 2, 3);SELECT * FROM users WHERE id = 1 OR id = 2 OR id = 3;SELECT * FROM users WHERE id IN (1, 2, 3, ..., 1000);

网上流传的误区：

• ❌ "IN 超过 3 个值就不走索引了"
• ❌ "IN 超过 100 个值会自动转全表扫描"
• ❌ "IN 永远比 OR 快"
• ❌ "IN 永远不会走索引"

二、真相：优化器决策流程图

三、实测验证：数据说话

3.1 测试环境搭建

-- 创建测试表CREATE TABLE test_in_index (    id INT PRIMARY KEY AUTO_INCREMENT,    user_id INT NOT NULL,    status TINYINT DEFAULT 1,    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,    INDEX idx_user_id (user_id),    INDEX idx_status (status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 插入100万条测试数据DELIMITER $$CREATE PROCEDURE insert_test_data()BEGIN    DECLARE i INT DEFAULT 1;    WHILE i <= 1000000 DO        INSERT INTO test_in_index (user_id, status)         VALUES (FLOOR(1 + RAND() * 10000), FLOOR(1 + RAND() * 5));        SET i = i + 1;    END WHILE;END$$DELIMITER ;CALL insert_test_data();ANALYZE TABLE test_in_index;

表结构信息：

SELECT     TABLE_NAME,    TABLE_ROWS,    AVG_ROW_LENGTH,    DATA_LENGTH FROM information_schema.TABLES WHERE TABLE_NAME = 'test_in_index';

结果：

| TABLE_NAME    | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH ||---------------|------------|----------------|-------------|| test_in_index | 1,000,000  | 45             | 45088768    |

3.2 不同场景下的索引使用情况

场景1：主键索引 + 不同数据量占比

-- 测试少量IN值（0.0003%）EXPLAIN SELECT * FROM test_in_index WHERE id IN (1, 2, 3);

执行计划结果：

| select_type | table          | type  | key     | key_len | rows | Extra       ||-------------|----------------|-------|---------|---------|------|-------------|| SIMPLE      | test_in_index | range | PRIMARY | 4       | 3    | Using where |

场景2：普通索引选择性测试

-- 测试不同选择性的字段EXPLAIN SELECT * FROM test_in_index WHERE user_id IN (100, 200, 300);EXPLAIN SELECT * FROM test_in_index WHERE status IN (1, 2);

索引选择性对比图表：

四、深度原理：优化器成本模型

4.1 成本计算公式

MySQL优化器基于以下成本模型决策：

# 伪代码：优化器决策逻辑def optimizer_decision(in_list_size, table_size, index_type):    # 计算IN列表占比    selectivity_ratio = in_list_size / table_size        # 计算索引扫描成本    index_cost = in_list_size * (io_cost_per_row + cpu_cost_per_row)        # 计算全表扫描成本      full_scan_cost = table_size * (io_cost_per_page + cpu_cost_per_row)        # 考虑索引类型权重    if index_type == "PRIMARY":        threshold = 0.3  # 30%    elif index_type == "UNIQUE":        threshold = 0.2  # 20%    else:  # SECONDARY        threshold = 0.1  # 10%        if selectivity_ratio < threshold and index_cost < full_scan_cost:        return "USE_INDEX"    else:        return "FULL_SCAN"

4.2 各因素权重分析

五、性能对比测试

5.1 IN vs OR 性能测试

-- 测试查询1：IN语法SELECT SQL_NO_CACHE * FROM test_in_index WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);-- 测试查询2：OR语法  SELECT SQL_NO_CACHE * FROM test_in_index WHERE id = 1 OR id = 2 OR id = 3 OR id = 4 OR id = 5    OR id = 6 OR id = 7 OR id = 8 OR id = 9 OR id = 10;

性能对比结果：

5.2 不同IN列表大小的性能衰减

# 测试数据生成代码示例import timeimport mysql.connectordef test_in_performance(sizes):    results = []    for size in sizes:        # 生成IN列表        in_list = ','.join(str(i) for i in range(1, size + 1))                start_time = time.time()        # 执行查询        cursor.execute(f"SELECT * FROM test_in_index WHERE id IN ({in_list})")        end_time = time.time()                results.append({            'size': size,            'time': end_time - start_time,            'type': 'index' if size < 300000 else 'full_scan'        })    return results

性能衰减曲线：

六、实战优化方案

6.1 大IN列表分批查询方案

def batch_query_in_list(connection, base_query, id_list, batch_size=1000):    """    分批处理大IN列表查询    """    results = []        for i in range(0, len(id_list), batch_size):        batch = id_list[i:i + batch_size]        placeholders = ','.join(['%s'] * len(batch))                query = f"{base_query} IN ({placeholders})"        cursor = connection.cursor()        cursor.execute(query, batch)                batch_results = cursor.fetchall()        results.extend(batch_results)                cursor.close()        return results# 使用示例user_ids = list(range(1, 10001))  # 10000个IDbase_sql = "SELECT * FROM orders WHERE user_id"results = batch_query_in_list(conn, base_sql, user_ids, batch_size=500)

6.2 临时表优化方案

-- 对于超大型IN列表，使用临时表优化CREATE TEMPORARY TABLE temp_user_ids (    id INT PRIMARY KEY) ENGINE=Memory;-- 批量插入数据INSERT INTO temp_user_ids VALUES (1), (2), (3), ...;-- 使用JOIN替代IN查询SELECT o.* FROM orders o INNER JOIN temp_user_ids t ON o.user_id = t.id;-- 清理临时表DROP TEMPORARY TABLE temp_user_ids;

6.3 不同场景的最佳实践

-- ✅ 推荐：小IN列表直接使用SELECT * FROM users WHERE id IN (1, 2, 3);-- ✅ 推荐：高选择性字段SELECT * FROM orders WHERE user_id IN (100, 200, 300);-- ⚠️ 注意：低选择性字段需要结合其他条件SELECT * FROM users WHERE status IN (1, 2) AND created_at > '2024-01-01';-- ❌ 避免：超大IN列表-- SELECT * FROM users WHERE id IN (1, 2, 3, ..., 10000);-- ✅ 改进：分批查询或使用临时表

七、监控与诊断工具

7.1 查询分析语句

-- 查看索引使用情况EXPLAIN FORMAT=JSON SELECT * FROM test_in_index WHERE user_id IN (100, 200, 300);-- 查看索引统计信息SHOW INDEX FROM test_in_index;-- 分析表统计信息ANALYZE TABLE test_in_index;-- 性能模式监控SELECT * FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST_TEXT LIKE '%IN (%';

7.2 自定义诊断函数

DELIMITER $$CREATE FUNCTION estimate_in_selectivity(    p_table_name VARCHAR(64),    p_column_name VARCHAR(64),    p_in_count INT) RETURNS DECIMAL(10,4)READS SQL DATADETERMINISTICBEGIN    DECLARE total_rows INT;    DECLARE distinct_values INT;    DECLARE selectivity DECIMAL(10,4);        -- 获取表总行数    SELECT TABLE_ROWS INTO total_rows    FROM information_schema.TABLES     WHERE TABLE_NAME = p_table_name;        -- 获取列不同值数量    SELECT COUNT(DISTINCT p_column_name) INTO distinct_values    FROM p_table_name;        -- 计算选择性    SET selectivity = (p_in_count / distinct_values) * (distinct_values / total_rows);        RETURN selectivity;END$$DELIMITER ;-- 使用示例SELECT estimate_in_selectivity('test_in_index', 'user_id', 100) as selectivity;

八、总结与最佳实践

8.1 核心结论速查表

8.2 决策流程图

8.3 关键要点总结

1. 没有固定阈值：IN查询是否走索引取决于数据占比，而非绝对数量
2. 优化器很智能：MySQL 8.0+ 的优化器能很好处理IN和OR的转换
3. 统计信息关键：定期执行ANALYZE TABLE更新统计信息
4. 监控是王道：使用EXPLAIN分析执行计划，不要凭经验猜测
5. 业务导向：结合具体业务场景选择最优方案

通过本文的详细分析和实战示例，您应该能够深入理解MySQL IN查询的索引使用机制，并在实际工作中做出正确的优化决策。