何时在SQL表字段（MySQL）上添加索引？

一尘不染

何时在SQL表字段（MySQL）上添加索引？

sql

有人告诉我，如果您知道您将经常使用字段进行联接，那么在其上创建索引可能会很好。

我通常理解索引表的概念（很像纸质书中的索引允许您查找特定术语，而不必一页一页地搜索）。但是我不清楚何时使用它们。

假设我有3个表：USERS，COMMENTS和VOTES表。我想创建一个类似Stackoverflow的评论线程，在该线程中查询返回评论以及这些评论的上/下投票数。

USERS table
user_id user_name   
 1         tim
 2         sue
 3         bill 
 4         karen
 5         ed

COMMENTS table
comment_id topic_id    comment   commenter_id
 1            1       good job!         1
 2            2       nice work         2
 3            1       bad job :)        3

VOTES table
 vote_id    vote  comment_id  voter_id
  1          -1       1          5
  2           1       1          4
  3           1       3          1
  4          -1       2          5
  5           1       2          4

这是要返回对topic_id = 1的投票的查询和SQLFiddle：

select u.user_id, u.user_name,
   c.comment_id, c.topic_id, c.comment,
   count(v.vote) as totals, sum(v.vote > 0) as yes, sum(v.vote < 0) as no,
   my_votes.vote as did_i_vote
from comments c
join users u on u.user_id = c.commenter_id
left join votes v on v.comment_id = c.comment_id
left join votes my_votes on my_votes.comment_id = c.comment_id
and my_votes.voter_id = 1
where c.topic_id = 1
group by c.comment_id, u.user_name, c.comment_id, c.topic_id, did_i_vote;

让我们假设评论和投票数达到数百万。为了加快查询，我的问题是我应该放在一个索引comments.commenter_id，votes.voter_id和votes.comment_id？

阅读 132

2021-03-17

共1个答案

一尘不染

这是一些使用了 http://www.sqlfiddle.com/#!2/94daa/1的密钥的更新

引擎必须将使用索引的成本与不使用索引的成本进行比较。您会注意到，我必须在其中添加更多行才能使用所使用的索引。

对于索引，引擎必须使用索引来获取匹配值，这是很快的。然后，它必须使用匹配项来查找表中的实际行。如果索引没有缩小行数，那么查找表中的所有行会更快。

我不确定mysql是否具有类似于SQL Server群集索引的内容。在这种情况下，索引和表数据具有相同的结构，因此您无需执行索引查找的第二步。

我以两种不同的方式介绍了索引，首先是通过定义主键在用户表上。这将在user_id列上隐式创建一个唯一索引。唯一索引表示无法两次插入同一组值。对于单列索引，这仅意味着您不能两次拥有相同的值。

如果您想象该表的用户手册，每页一个用户，那么创建的索引将为您提供user_id的排序列表，每个列表都有该用户的页码。该列表通常以某种树形形式存储，以使快速查找特定数字成为可能。考虑一下您在电话簿中查找姓名的方式，您不只是扫描所有页面，直到找到它，您猜测它会在哪里，然后跳回或前进几页页面，直到接近为止。通常，您可以在O（log
2 n）时间中在索引中查找值，其中n是行数，并且您需要读取相似数量的索引页。

现在，如果为数据库引擎提供了查询select * from users Where user_id = 3，它有两个选择。它可以读取每个数据页，并寻找正确的值（它可能会使用一个主键在第一个键处停止的事实）。另一种方法是读取索引以获取正确的数据页，然后查找数据页。

为了具体和简单起见，假定该表具有1024个条目。假设每个条目占用一个数据页。假设索引树中的每个条目占用一个索引页。假定索引是平衡的，所以它有10个级别，总共2047页。（所有这些假设都是可疑的，但是它们很重要，特别是索引页几乎总是小于数据页，因为您不倾向于一次索引所有列）。

要执行表扫描方法，将需要读取1024个数据页。要使用索引，将需要读取10个索引页和一个数据页。几乎所有数据库性能都与最大程度地减少读取的页面数量有关。

多列索引允许快速查找数据集。如果您的索引具有（col1，col2），则即使只是对col1的匹配也会得到改善。

该create index语句仅说明索引了哪些列，以及是否允许重复值。

再次使用这本书的类比，Create Index ix_comment_id on votes (comment_id, voter_id)将创建一个comment_id然后带投票者ID的有序列表，并带有对相应数据行的引用。

+------------+--------------+---------+
| comment_id | reference_id | row_ref |
+------------+--------------+---------+
|          1 |            4 |    ref1 |
|          1 |            5 |    ref2 |
|          2 |            4 |    ref3 |
|          2 |            5 |    ref4 |
|          3 |            1 |    ref5 |
+------------+--------------+---------+

2021-03-17