mysql - 在包含逗号分隔值的 varchar 字段上创建索引是否合适？

Question

我正在创建一个小型数据库，可以存储大量数据。该表有一列将以 varchar 格式存储外键(例如 1,12,13...)，以便我可以一次性获取所有数据。那么，如果我将该列索引到主表的主键，会很重要吗？(我只是索引而不创建外键引用)

Answer 1

为了稍微解释一下索引，请看以下示例。

类别

Id  Category
1   Cat A
2   Cat B
3   Cat C
4   Cat D
5   Cat E
6   Cat F

用户

Id  Name    CategoryList
1   Bill    1,2,3
2   Burt    4,5,6
3   Jill    1,3,5
4   Alli    2,4,6

如果用户表中的类别字段有索引，则如下所示:-

1,2,3   1
1,3,5   3
2,4,6   4
4,5,6   2

如果您查找“1,3,5”，那么很容易按顺序搜索该表并找到匹配的 ID。例如，它可以跳转到索引的一半，并检查它正在寻找的值是更高还是更低。然后重复这个过程来找到它想要的记录。

但是，如果您搜索包含类别 2 的行(如下所示)，则由于 2 可能位于字符串中的任何位置，因此无法使用索引。

SELECT Users.Name, Categories.Category
FROM Users
INNER JOIN Categories
ON FIND_IN_SET(Category.Id, Users.CategoryList)
WHERE Categories.Category = 2

FIND_IN_SET 无法使用索引，因为它正在检查的值可能位于该逗号分隔列表中的任何点。相反，它必须检查每一行并查找逗号分隔值为 2 的行。这肯定比在 SQL 中手动拆分字符串(这是可能的)更有效，但与使用索引相比速度较慢.

例如，如果您有 1000 行，每行有 1000 个逗号分隔的 id，并且您想要列表中 id 为 123 的单行，那么 FIND_IN_SET 需要读取 1000 行，将这些行中的每一行拆分并检查这些行值为 123，因此 1000000 次比较(加上将逗号分隔的列表拆分的处理)。如果 id 单独存在于索引字段中，则它会进行接近 1 次比较。

如果表格是:-

类别

Id  Category
1   Cat A
2   Cat B
3   Cat C
4   Cat D
5   Cat E
6   Cat F

用户

Id  Name    
1   Bill    
2   Burt    
3   Jill    
4   Alli    

用户类别

Id  UserId  CatId
1   1       1
2   1       2
3   1       3
4   2       4
5   2       5       
6   2       6
7   3       1
8   3       3
9   3       5
10  4       2
11  4       4
12  4       6

您可以在整数字段上有一个索引(这会很快)，并且您可以简单地进行连接:-

SELECT Users.Name, Categories.Category
FROM Categories
INNER JOIN UsersCategories
ON Categories.Id = UsersCategories.CatId
INNER JOIN Users
ON UsersCategories.UserId = Users.Id
WHERE Categories.Category = 2

这可以轻松地在每个比较/连接上使用索引来提供可观的性能。

编辑 - 以下将为您提供所有用户的列表以及每个用户的类别列表:-

SELECT Users.Name, GROUP_CONCAT(Categories.Category)
FROM Users
LEFT OUTER JOIN UsersCategories
ON UsersCategories.UserId = Users.Id
LEFT OUTER JOIN Categories
ON Categories.Id = UsersCategories.CatId
GROUP BY Users.Name