MySQL 百万级测试数据生成与索引失效场景深度实战

在数据库性能优化领域，索引是提升查询效率最直接有效的手段之一。然而在实际工作中，我们常常会遇到"明明建立了索引，查询却依然缓慢"的情况。本文将带您从零开始构建一个完整的索引优化实验环境，通过存储过程快速生成百万级测试数据，并精心设计5种典型的索引失效场景进行实战分析。

1. 实验环境搭建与测试数据生成

1.1 测试表结构设计

我们首先设计一个包含多种数据类型和索引结构的测试表，模拟真实业务场景：

CREATE TABLE `performance_lab` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID', `user_name` varchar(100) NOT NULL COMMENT '用户名', `user_code` varchar(20) NOT NULL COMMENT '用户编码', `id_card` varchar(18) NOT NULL COMMENT '身份证号', `phone` varchar(11) NOT NULL COMMENT '手机号', `order_amount` decimal(10,2) NOT NULL COMMENT '订单金额', `create_time` datetime NOT NULL COMMENT '创建时间', `status` tinyint NOT NULL COMMENT '状态(0-未处理,1-已处理)', `random_num` int NOT NULL COMMENT '随机数', PRIMARY KEY (`id`), KEY `idx_user_code` (`user_code`), KEY `idx_id_card` (`id_card`), KEY `idx_phone` (`phone`), KEY `idx_create_time` (`create_time`), KEY `idx_status_random` (`status`,`random_num`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这个表设计具有以下特点：

• 包含主键自增ID
• 设置了5个不同类型的索引（单列索引和复合索引）
• 包含字符串、数值、时间等多种数据类型
• 模拟了用户、订单等常见业务字段

1.2 百万级数据生成存储过程

为了高效生成测试数据，我们编写以下存储过程：

DELIMITER // CREATE PROCEDURE generate_test_data(IN data_count INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE start_time DATETIME DEFAULT NOW(); WHILE i <= data_count DO INSERT INTO performance_lab ( user_name, user_code, id_card, phone, order_amount, create_time, status, random_num ) VALUES ( CONCAT('用户', i), CONCAT('UC', LPAD(FLOOR(RAND()*100000), 5, '0')), CONCAT('11010519', LPAD(FLOOR(RAND()*10000), 4, '0'), LPAD(FLOOR(RAND()*10000), 4, '0')), CONCAT('13', LPAD(FLOOR(RAND()*100000000), 9, '0')), ROUND(RAND()*10000, 2), DATE_ADD(start_time, INTERVAL FLOOR(RAND()*365) DAY), FLOOR(RAND()*2), FLOOR(RAND()*10000) ); SET i = i + 1; -- 每生成1万条数据提交一次 IF i % 10000 = 0 THEN COMMIT; END IF; END WHILE; COMMIT; END // DELIMITER ;

执行存储过程生成100万测试数据：

-- 调用存储过程生成100万数据 CALL generate_test_data(1000000); -- 查看数据生成情况 SELECT COUNT(*) FROM performance_lab;

提示：在生成百万级数据时，建议在非业务高峰期执行，并确保数据库服务器有足够的内存和磁盘空间。

2. 索引失效场景分析与复现

2.1 隐式类型转换导致索引失效

场景描述：当查询条件中的数据类型与索引列定义的数据类型不一致时，MySQL会进行隐式类型转换，导致索引失效。

复现实验：

-- 正常使用索引的情况（字符串匹配字符串） EXPLAIN SELECT * FROM performance_lab WHERE id_card = '11010519900101001';

执行计划显示使用了idx_id_card索引：

+----+-------------+----------------+------------+------+---------------+------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+------+---------------+------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | performance_lab| NULL | ref | idx_id_card | idx_id_card| 74 | const | 1 | 100.00 | NULL | +----+-------------+----------------+------------+------+---------------+------------+---------+-------+------+----------+-------+

-- 索引失效的情况（数值匹配字符串） EXPLAIN SELECT * FROM performance_lab WHERE id_card = 11010519900101001;

执行计划显示进行了全表扫描：

+----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | performance_lab| NULL | ALL | idx_id_card | NULL | NULL | NULL | 1000000 | 10.00 | Using where | +----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

根因分析：当使用数值类型查询字符串类型的id_card列时，MySQL需要将id_card列的值转换为数值类型进行比较，相当于执行了CAST(id_card AS signed) = 11010519900101001，导致无法使用索引。

优化方案：

1. 确保查询条件中的数据类型与列定义一致
2. 如果必须进行类型转换，应显式转换查询值而非列值

2.2 前导模糊查询导致索引失效

场景描述：使用LIKE进行模糊查询时，如果通配符%出现在模式字符串的开头，索引将失效。

复现实验：

-- 可以使用索引的情况（后缀匹配） EXPLAIN SELECT * FROM performance_lab WHERE user_code LIKE 'UC123%';

执行计划显示使用了索引：

+----+-------------+----------------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | performance_lab| NULL | range | idx_user_code | idx_user_code | 82 | NULL | 1 | 100.00 | Using index condition | +----+-------------+----------------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+

-- 索引失效的情况（前导模糊） EXPLAIN SELECT * FROM performance_lab WHERE user_code LIKE '%123';

执行计划显示进行了全表扫描：

+----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | performance_lab| NULL | ALL | NULL | NULL | NULL | NULL | 1000000 | 11.11 | Using where | +----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

根因分析：B+树索引是按照列值的有序结构组织的，前导模糊查询无法利用索引的有序性，只能进行全表扫描。

优化方案：

1. 避免使用前导模糊查询
2. 考虑使用全文索引(FULLTEXT)替代
3. 使用专门的搜索引擎如Elasticsearch处理复杂搜索需求

2.3 函数操作导致索引失效

场景描述：对索引列使用函数操作会使索引失效，因为索引存储的是原始值而非函数计算结果。

复现实验：

-- 可以使用索引的情况 EXPLAIN SELECT * FROM performance_lab WHERE create_time > '2023-01-01';

执行计划显示使用了索引：

+----+-------------+----------------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-----------------------+ | 1 | SIMPLE | performance_lab| NULL | range | idx_create_time | idx_create_time | 5 | NULL | 500000 | 100.00 | Using index condition | +----+-------------+----------------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-----------------------+

-- 索引失效的情况（使用DATE函数） EXPLAIN SELECT * FROM performance_lab WHERE DATE(create_time) = '2023-01-01';

执行计划显示进行了全表扫描：

+----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | performance_lab| NULL | ALL | NULL | NULL | NULL | NULL | 1000000 | 100.00 | Using where | +----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

根因分析：当对索引列使用函数时，MySQL无法利用索引的有序性，必须对每一行数据都计算函数值后再进行比较。

优化方案：

1. 避免在索引列上使用函数
2. 如果必须使用函数，考虑使用计算列并为其建立索引
3. 重构查询条件，使用范围查询替代函数操作

2.4 OR条件导致索引失效

场景描述：当WHERE条件中包含OR连接的非索引列条件时，可能导致整个查询无法使用索引。

复现实验：

-- 可以使用索引的情况（AND条件） EXPLAIN SELECT * FROM performance_lab WHERE status = 1 AND random_num > 5000;

执行计划显示使用了复合索引：

+----+-------------+----------------+------------+-------+--------------------+--------------------+---------+------+--------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+-------+--------------------+--------------------+---------+------+--------+----------+-----------------------+ | 1 | SIMPLE | performance_lab| NULL | range | idx_status_random | idx_status_random | 5 | NULL | 500000 | 50.00 | Using index condition | +----+-------------+----------------+------------+-------+--------------------+--------------------+---------+------+--------+----------+-----------------------+

-- 索引失效的情况（OR条件连接非索引列） EXPLAIN SELECT * FROM performance_lab WHERE status = 1 OR order_amount > 5000;

执行计划显示进行了全表扫描：

+----+-------------+----------------+------------+------+--------------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+------+--------------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | performance_lab| NULL | ALL | idx_status_random | NULL | NULL | NULL | 1000000 | 50.00 | Using where | +----+-------------+----------------+------------+------+--------------------+------+---------+------+---------+----------+-------------+

根因分析：当使用OR连接条件时，如果其中任一条件不能使用索引，MySQL通常会选择全表扫描而非索引扫描。

优化方案：

1. 将OR条件改写为UNION ALL查询
2. 为OR条件中涉及的列建立合适的索引
3. 使用覆盖索引减少回表操作

2.5 违反最左前缀原则导致索引失效

场景描述：对于复合索引，查询条件必须包含索引的最左列，否则无法使用索引。

复现实验：

-- 可以使用索引的情况（包含最左列status） EXPLAIN SELECT * FROM performance_lab WHERE status = 1 AND random_num > 5000;

执行计划显示使用了复合索引：

+----+-------------+----------------+------------+-------+--------------------+--------------------+---------+------+--------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+-------+--------------------+--------------------+---------+------+--------+----------+-----------------------+ | 1 | SIMPLE | performance_lab| NULL | range | idx_status_random | idx_status_random | 5 | NULL | 500000 | 50.00 | Using index condition | +----+-------------+----------------+------------+-------+--------------------+--------------------+---------+------+--------+----------+-----------------------+

-- 索引失效的情况（不包含最左列status） EXPLAIN SELECT * FROM performance_lab WHERE random_num > 5000;

执行计划显示进行了全表扫描：

+----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | performance_lab| NULL | ALL | NULL | NULL | NULL | NULL | 1000000 | 50.00 | Using where | +----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

根因分析：复合索引是按照索引定义的列顺序组织的，跳过最左列将无法利用索引的有序性。

优化方案：

1. 确保查询条件包含复合索引的最左列
2. 调整索引定义顺序以匹配查询模式
3. 为常用查询条件单独建立单列索引

3. 索引优化高级技巧

3.1 强制索引的使用场景与风险

在某些特殊情况下，MySQL优化器可能不会选择最优的索引，此时可以考虑使用FORCE INDEX或USE INDEX提示。

FORCE INDEX与USE INDEX对比：

使用示例：

-- 强制使用特定索引 EXPLAIN SELECT * FROM performance_lab FORCE INDEX (idx_status_random) WHERE status = 1 OR random_num > 5000; -- 建议使用特定索引 EXPLAIN SELECT * FROM performance_lab USE INDEX (idx_status_random) WHERE status = 1 OR random_num > 5000;

注意：强制索引应谨慎使用，仅在经过充分测试确认性能提升的情况下使用，因为随着数据分布变化，强制使用的索引可能不再是最优选择。

3.2 索引选择性优化

索引选择性是指索引列中不同值的数量与表中记录总数的比例，高选择性的索引更有效。

计算索引选择性的SQL：

-- 计算某列的选择性 SELECT COUNT(DISTINCT user_code) / COUNT(*) AS user_code_selectivity, COUNT(DISTINCT status) / COUNT(*) AS status_selectivity FROM performance_lab;

优化建议：

1. 优先为高选择性的列建立索引
2. 低选择性的列考虑与其他列组成复合索引
3. 避免为布尔类型等低选择性的列单独建立索引

3.3 覆盖索引优化

当查询的所有列都包含在索引中时，可以避免回表操作，显著提升性能。

示例：

-- 创建覆盖索引 ALTER TABLE performance_lab ADD INDEX idx_covering (user_code, user_name); -- 使用覆盖索引的查询 EXPLAIN SELECT user_code, user_name FROM performance_lab WHERE user_code LIKE 'UC1%';

执行计划显示使用了覆盖索引：

+----+-------------+----------------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | performance_lab| NULL | range | idx_covering | idx_covering | 82 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+----------------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+

优化建议：

1. 分析常用查询模式，设计合适的覆盖索引
2. 避免SELECT *，只查询需要的列
3. 权衡索引大小和查询性能，避免创建过多大字段的覆盖索引

4. 索引监控与维护

4.1 索引使用情况监控

通过performance_schema监控索引使用情况：

-- 查看未使用的索引 SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name;

4.2 索引维护最佳实践

定期维护索引可以保持查询性能：

-- 分析表状态 ANALYZE TABLE performance_lab; -- 优化表结构 OPTIMIZE TABLE performance_lab; -- 重建索引 ALTER TABLE performance_lab ENGINE=InnoDB;

维护建议：

1. 定期分析表以更新统计信息
2. 对大表进行分批维护，避免长时间锁表
3. 监控索引碎片率，适时重建索引

在实际项目中，我们曾遇到一个案例：一个核心查询在测试环境运行很快，但在生产环境却非常缓慢。通过EXPLAIN分析发现，生产环境的数据分布与测试环境差异很大，导致优化器选择了不同的执行计划。最终我们通过收集更准确的统计信息解决了这个问题。