为什么和何时使用索引 - 优缺点

Question

我对数据库编程比较新，想知道索引的负面影响是什么？就我所了解的，索引可以加快那些需要在数据库中搜索特定值（例如 SELECT）的操作。

考虑以下例子：

对于表格 Example，有一个在列 user_name 上的索引，针对以下操作：

SELECT TestField FROM Example WHERE user_name=XXXX

由于索引的存在，查询速度将会更快。

我的问题是：使用索引有什么缺点？如果索引只带来优点（性能提升），为什么它们不被设置为默认？

- Jhon Zunda

---

相关问题，可能是重复的：https://dev59.com/7W025IYBdhLWcg3w96wW - TZHX

索引不是免费的。它们需要被构建和更新，因此插入和更新会变慢。 - Marvin

尝试在这里询问同样的问题：https://dba.stackexchange.com/help/on-topic - chharvey

Answer 1

对于索引（indices）的内容，可以写很多东西。简单来说，创建索引时需要考虑以下几点：

虽然索引（大多数情况下）会加快 select 查询的速度，但它会减慢 insert、update 和 delete 的速度，因为数据库引擎不仅需要写入数据，还需要写入索引。 索引需要硬盘空间（更重要的是）需要 RAM 空间。无法放入 RAM 中的索引基本上毫无用处。 在只有少量不同值的列上创建索引不会加快查询速度，因为它无法筛选出太多行记录（例如，“性别”列通常只有两个不同的值 - 男性和女性）。

例如，如果您使用 MySQL，可以通过在 select 前添加 "explain" 来检查引擎是否使用索引 - 对于上面的示例，语句应该是 EXPLAIN SELECT TestField FROM Example WHERE username=XXXX。

- Stefan P

Answer 2

索引是什么，它们在数据库中的作用是什么？

如果在列user_name上没有索引，系统将不得不逐行扫描整个Example表以查找所有匹配项。如果特定表中的数据分布指向只有一些行，那么这显然是获取这些行的低效方式。

然而，使用索引时，您可以将搜索功能重定向到不同的树结构，它具有更快的查找和非常小的深度。

请记住，索引是纯冗余。数据库索引就像电话簿中的索引或书中的任何其他索引一样（您可能只想阅读其中一部分，以快速找到所需内容）。

如果您对一本书的某一章感兴趣，索引让您能够相对快速地找到它，这样您就不必浏览多页才能找到它。

为什么不默认创建索引？

索引是与表一起创建的数据结构，并在表发生更改时进行维护。其存在的事实意味着使用数据存储。

如果对大表上的每个列都建立索引，则用于保留索引的存储将远远超过表本身的大小。

自我维护索引结构还意味着每当发生UPDATE, INSERT, DELETE时，索引都必须更新（它是自动完成的，不需要您的操作），但这会花费时间，这意味着这些操作执行得更慢。

有些情况下，需要检索大部分表格（例如，输出将包含90%的行），或整个表格，在这种情况下，整个表的顺序扫描（无索引的行为）比进行树遍历和叶节点链（这是导航索引树结构的行为）更有效。

- Kamil Gosciminski

Answer 3

为什么我们不把索引作为默认值呢？主要原因在于维护问题。例如，当我们更新（插入、删除或更新）表中已经建立索引的某个列时，该索引必须进行动态更新，这是一个耗时的过程。此外，维护该索引会增加额外负担。

- Vinjamuri Satya Krishna

Answer 4

这取决于你的索引是如何设置的，但基本上它们是每个表行的唯一标识符，通常递增一个值，例如：

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”，而不是逐个遍历每个表格、每一行和每一列。

- Careen

---

请说明这如何回答问题？ - Marvin

我提供了一组基本的基础知识，可以查看Stefan P的答案 :) - Careen