一、为什么需要索引?两种数据访问方式对比

 访问方式  工作原理  比喻  优点  缺点

1. 顺序访问 (Full Table Scan) 逐行扫描整个表,直到找到所有符合条件的记录。 逐页翻阅字典查找一个字。  实现简单。 效率极低。当表数据量巨大时,查询耗时无法接受。

2. 索引访问 (Index Lookup) 首先在索引结构(如B+树)中快速定位到目标值,然后根据指针直接找到对应的数据行。 使用字典的音序表先找到拼音所在的页,再直接翻到那一页。 查询速度极快。大大减少了需要扫描的数据量。  需要额外的存储空间来维护索引结构。

结论: 索引的核心目的是用空间换时间,通过消耗额外的磁盘空间来建立一种高效的数据结构,从而极大提升数据的检索速度。

 

二、索引的优缺点

优点

1.  极大加快查询速度:这是创建索引最主要的原因。特别是对于大表的主键查询、范围查询、分组和排序操作,性能提升尤为明显。

2.  确保数据唯一性:通过创建唯一索引(UNIQUE INDEX),可以保证表中某一列或多列组合的值是唯一的。

3.  加速表连接:在实现多表连接(JOIN)时,如果连接条件列上有索引,可以显著提高连接性能。

4.  优化排序与分组:使用 `ORDER BY` 和 `GROUP BY` 子句时,如果排序或分组的字段上有索引,MySQL可以直接利用索引的有序性,避免昂贵的文件排序(Filesort)操作。

 

缺点

1.  占用磁盘空间:每个索引都会创建一个索引文件,需要占用额外的物理存储空间。如果索引很多,索引文件甚至可能比数据文件还要大。

2.  降低写操作性能:对数据进行增(INSERT)、删(DELETE)、改(UPDATE)时,数据库不仅需要操作数据本身,还需要更新相关的索引结构来保持同步。这会降低写操作的速度。

3.  增加维护成本:索引需要被维护和管理。不合理的索引(如过多或未使用的索引)会成为数据库的负担。

 

三、索引的使用策略与建议

权衡读写比例:对于读多写少(例如报表系统、网站内容页)的应用,索引的收益非常大。对于写多读少(例如高频的日志录入系统)的应用,创建索引需要非常谨慎,因为写操作的性能损耗可能会成为瓶颈。

为哪些列建索引?

    WHERE 子句中的条件列。

    JOIN 操作中使用的连接列。

    经常用于 ORDER BY 和 GOUP BY 的列。

    选择性高的列(即列中不同值多的列,如用户名、手机号),而不是选择性低的列(如性别、状态标志)。

不建议建索引的情况:

    表数据量非常小。

    频繁进行写操作但很少读取的表。

    包含大量重复数据的列(低选择性)。

大批量数据导入优化:正如您提到的,在进行大量数据插入(如数据迁移、初始化)时,先删除索引,再导入数据,最后重建索引的策略通常比带着索引插入要快得多,因为避免了频繁的索引维护开销。

 

总结

索引是数据库性能调优的基石,但它是一把双刃剑。

 场景  建议

核心业务查询慢 优先考虑增加索引。分析慢查询日志,为查询条件涉及的列添加合适的索引。

数据导入慢 考虑暂时删除索引,导入完成后再重建。

磁盘空间紧张 审视现有索引,删除 unused 或冗余的索引。

写操作成为瓶颈 检查索引数量,评估是否过度索引。

 

正确地创建和使用索引是每个数据库开发者和DBA必须掌握的核心技能。

点赞(1)

C语言网提供由在职研发工程师或ACM蓝桥杯竞赛优秀选手录制的视频教程,并配有习题和答疑,点击了解:

一点编程也不会写的:零基础C语言学练课程

解决困扰你多年的C语言疑难杂症特性的C语言进阶课程

从零到写出一个爬虫的Python编程课程

只会语法写不出代码?手把手带你写100个编程真题的编程百练课程

信息学奥赛或C++选手的 必学C++课程

蓝桥杯ACM、信息学奥赛的必学课程:算法竞赛课入门课程

手把手讲解近五年真题的蓝桥杯辅导课程

Dotcpp在线编译      (登录可减少运行等待时间)