我对数据库编程比较新,想知道索引的负面影响是什么?就我所了解的,索引可以加快那些需要在数据库中搜索特定值(例如 SELECT)的操作。
考虑以下例子:
对于表格 Example
,有一个在列 user_name
上的索引,针对以下操作:
SELECT TestField FROM Example WHERE user_name=XXXX
由于索引的存在,查询速度将会更快。
我的问题是:使用索引有什么缺点?如果索引只带来优点(性能提升),为什么它们不被设置为默认?
我对数据库编程比较新,想知道索引的负面影响是什么?就我所了解的,索引可以加快那些需要在数据库中搜索特定值(例如 SELECT)的操作。
考虑以下例子:
对于表格 Example
,有一个在列 user_name
上的索引,针对以下操作:
SELECT TestField FROM Example WHERE user_name=XXXX
由于索引的存在,查询速度将会更快。
我的问题是:使用索引有什么缺点?如果索引只带来优点(性能提升),为什么它们不被设置为默认?
对于索引(indices)的内容,可以写很多东西。简单来说,创建索引时需要考虑以下几点:
虽然索引(大多数情况下)会加快 select 查询的速度,但它会减慢 insert、update 和 delete 的速度,因为数据库引擎不仅需要写入数据,还需要写入索引。 索引需要硬盘空间(更重要的是)需要 RAM 空间。无法放入 RAM 中的索引基本上毫无用处。 在只有少量不同值的列上创建索引不会加快查询速度,因为它无法筛选出太多行记录(例如,“性别”列通常只有两个不同的值 - 男性和女性)。
例如,如果您使用 MySQL,可以通过在 select 前添加 "explain" 来检查引擎是否使用索引 - 对于上面的示例,语句应该是 EXPLAIN SELECT TestField FROM Example WHERE username=XXXX
。
索引是什么,它们在数据库中的作用是什么?
如果在列user_name
上没有索引,系统将不得不逐行扫描整个Example
表以查找所有匹配项。如果特定表中的数据分布指向只有一些行,那么这显然是获取这些行的低效方式。
然而,使用索引时,您可以将搜索功能重定向到不同的树结构,它具有更快的查找和非常小的深度。
请记住,索引是纯冗余。数据库索引就像电话簿中的索引或书中的任何其他索引一样(您可能只想阅读其中一部分,以快速找到所需内容)。
如果您对一本书的某一章感兴趣,索引让您能够相对快速地找到它,这样您就不必浏览多页才能找到它。
为什么不默认创建索引?
索引是与表一起创建的数据结构,并在表发生更改时进行维护。其存在的事实意味着使用数据存储。
如果对大表上的每个列都建立索引,则用于保留索引的存储将远远超过表本身的大小。
自我维护索引结构还意味着每当发生UPDATE, INSERT, DELETE
时,索引都必须更新(它是自动完成的,不需要您的操作),但这会花费时间,这意味着这些操作执行得更慢。
有些情况下,需要检索大部分表格(例如,输出将包含90%的行),或整个表格,在这种情况下,整个表的顺序扫描(无索引的行为)比进行树遍历和叶节点链(这是导航索引树结构的行为)更有效。
为什么我们不把索引作为默认值呢?主要原因在于维护问题。例如,当我们更新(插入、删除或更新)表中已经建立索引的某个列时,该索引必须进行动态更新,这是一个耗时的过程。此外,维护该索引会增加额外负担。
这取决于你的索引是如何设置的,但基本上它们是每个表行的唯一标识符,通常递增一个值,例如:
mytable{
index | name | m/f | age
1 | bob | male | 22 |
2 | joe b | male | 27 |
3 | sam | female | 42 |
4 | bef | female | 21 |
}
看看我们如何检查数字3是否为“sam”,而不是逐个遍历每个表格、每一行和每一列。