1. 索引创建与数据更新实验概述上周在数据库原理课上做完索引实验后有几个学弟跑来问我为什么明明建了索引查询速度反而变慢了这个问题让我想起自己第一次做索引实验时踩过的坑。今天就把这个实验的完整操作和避坑指南整理出来特别适合正在学习《数据库原理》的同学参考。这个实验主要涉及两个核心操作索引创建和数据更新。通过SQL语句创建不同类型的索引普通索引、唯一索引、复合索引等然后观察数据插入、修改、删除操作时的性能变化。实验环境我推荐使用MySQL 8.0或SQL Server 2019这两个版本对索引功能的支持都比较完善。重要提示实验前务必先备份数据库我在大三时就因为没做备份误操作导致实验数据全部丢失最后只能重做。2. 实验环境准备与数据表设计2.1 实验环境配置我习惯用Docker快速搭建实验环境这里分享我的MySQL 8.0容器启动命令docker run --name mysql-lab -e MYSQL_ROOT_PASSWORD123456 -p 3306:3306 -d mysql:8.0 --character-set-serverutf8mb4 --collation-serverutf8mb4_unicode_ci这个配置使用了utf8mb4字符集能完美支持中文和emoji。相比学校实验室的老旧MySQL 5.68.0版本在索引优化上有很多改进特别是新增的倒序索引和函数索引特别实用。2.2 实验数据表设计我们设计一个学生成绩管理表来演示索引效果CREATE TABLE student_scores ( id INT AUTO_INCREMENT PRIMARY KEY, student_id CHAR(10) NOT NULL, course_name VARCHAR(50) NOT NULL, score DECIMAL(5,2), exam_date DATE, class_id INT ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;先插入10万条测试数据存储过程略数据量足够大才能明显看出索引效果。这里有个技巧使用FLOOR(RAND()*100)生成随机分数用DATE_SUB(NOW(), INTERVAL FLOOR(RAND()*365) DAY)生成随机考试日期这样数据更接近真实场景。3. 索引创建实战与性能对比3.1 基础索引创建先创建三个典型索引做对比-- 普通单列索引 CREATE INDEX idx_student_id ON student_scores(student_id); -- 唯一索引 CREATE UNIQUE INDEX uq_student_course ON student_scores(student_id, course_name); -- 复合索引 CREATE INDEX idx_class_score ON student_scores(class_id, score);建索引时我踩过的一个坑在MySQL中索引长度默认是767字节如果对长字符串建索引可能失败。解决方案是修改innodb_large_prefix参数或者指定索引长度CREATE INDEX idx_course_name ON student_scores(course_name(20));3.2 索引效果验证用EXPLAIN分析查询计划EXPLAIN SELECT * FROM student_scores WHERE student_id 20230001 AND course_name 数据库原理;重点关注type列ALL是全表扫描index是索引扫描range是范围扫描const是常量查询。我整理了一个性能对比表格查询类型无索引耗时有索引耗时扫描行数对比等值查询120ms3ms100000 vs 1范围查询150ms30ms50000 vs 200排序操作300ms50ms全表vs索引树4. 数据更新操作与索引维护4.1 插入性能测试先关闭自动提交然后批量插入1000条记录SET autocommit0; INSERT INTO student_scores (...) VALUES (...); COMMIT;有索引时插入耗时约1.2秒无索引时仅0.3秒。这是因为每次插入都需要维护索引树。建议大批量导入数据时先删除索引导入后再重建。4.2 更新操作陷阱执行这个更新语句UPDATE student_scores SET student_id CONCAT(student_id, x) WHERE class_id 5;如果student_id列有索引这个更新会导致索引重建10万数据要8秒而更新非索引列如score只需0.5秒。这就是为什么高频更新的字段要慎重建索引。4.3 删除操作优化删除操作也有讲究-- 低效写法 DELETE FROM student_scores WHERE score 60; -- 高效写法利用索引 DELETE FROM student_scores WHERE class_id 3 AND score 60;第一个语句全表扫描10万数据删除要6秒第二个用上复合索引只要0.8秒。5. 高级索引技巧与避坑指南5.1 覆盖索引优化看这个查询SELECT student_id, course_name FROM student_scores WHERE class_id 5 AND score 90;如果创建(class_id, score, student_id, course_name)索引引擎直接从索引取数据不需要回表速度提升3倍以上。5.2 索引失效的常见场景我总结的六大失效场景对索引列使用函数WHERE YEAR(exam_date) 2023隐式类型转换WHERE student_id 20230001student_id是字符串前导模糊查询WHERE course_name LIKE %原理%使用OR条件且部分列无索引不符合最左前缀原则索引列参与计算WHERE score 10 1005.3 索引维护建议定期检查索引使用情况SELECT * FROM sys.schema_unused_indexes WHERE object_schema 你的数据库名;对于不常用的索引要及时删除我见过一个表建了15个索引插入速度比蜗牛还慢。6. 实验报告撰写要点写实验报告时除了记录操作步骤还要重点分析不同索引类型的适用场景数据量对索引效果的影响更新操作与查询操作的性能平衡执行计划的分析方法可以像这样用表格对比实验结果操作类型无索引性能有索引性能性能变化率精确查询120ms3ms3900%批量插入1000条300ms1200ms-75%范围更新500ms8000ms-94%最后分享一个排查索引问题的万能命令SHOW INDEX FROM student_scores;关注Cardinality列这个值越大索引区分度越高。如果值很小比如性别列只有2建索引基本没用。