sql - 用于优化数据库性能的单列与多列索引

Question

在我的数据库（RDBMS：MariaDB 10.3）中，我有一个结构如下的表：

+------+----------+----------+----------+----------+----------+------+--..
|  ID  |  user_1  |  user_2  |  info_1  |  info_2  |  info_3  |  ..  |  ..
+------+----------+----------+----------+----------+----------+------+--..

在使用表的应用程序中，我必须按如下方式查询记录：

a) 在 f( ID) 中查询；意义WHERE ID = id_value

B) 在 f( users) 中查询，意思是WHERE user_1 = value_user_1 AND user_2 = value_user_2 OR user_1 = value_user_2 AND user_2 = value_user_1

C) 查询如 A) 或 B) 中所述，并且其中任何附加列info_1、info_2和info_3或它们的子集一起匹配给定值，例如WHERE ID = id_value AND info_1 = value_info_1 AND info_2 = value_info_2，对于以 A) 开头的类型 C) 的查询您可以推导出从 B)) 开始的 C) 类型查询的其他场景。

我想要实现的目标：对于所有三种查询类型 A) B) 和 C)，我想避免全表扫描，并尽可能高效，因为表在应用程序发布后可能会增长得非常快。所以，我想，如果可能的话，理想情况下EXPLAIN SELECT **anything** FROM my_table，根据 A) B) 或 C) 选择时应该根据thisconst返回一个or类型。eq_ref

我尝试了什么：

• 定义ID为表的唯一主键。返回constA) 的查询类型，好的，但问题是 B) 查询或 C) 以 B) 的用户查询开头的查询都会导致全表扫描。

• ID定义了所有三个字段 ( , user_1, user_2)的多列索引。在结果解释表中返回refA) 的查询类型，但在与上述相同的情况下再次导致全表扫描 (B) 或 C) 从 B) 开始。ref_typeconst

• 定义user_1和user_2作为数据类型 JSON 的单列users，包含 a JSON_ARRAYofvalue_user_1和value_user_2. 我的想法是避免ORB) 中所述子句的必要性，并简单地查询users列中的数组是否同时包含value_user_1and value_user_2。然后定义复合键 ( ID, users)。仍然是完全相同的结果（使用WHERE JSON_CONTAINS(users,JSON_ARRAY(alue_user_1,value_user_2))）。

我怎样才能获得基于索引的查询/避免所有三种情况 A)、B) 和 C) 的全表扫描？

标签： sqlindexingmariadbprimary-keycomposite-primary-key

Answer 1

我建议

• 正如您在第一个选项中建议的那样，将 PRIMARY KEY 放在 ID 上。这允许您在按 ID 过滤时进行直接索引搜索
• 还要在 User_1 和 User_2 上放置一个非聚集索引 - 这允许您为 B 类型的查询执行（两个）索引搜索。

对于类型 C（基于 B），这确实意味着查询将（可以）从索引开始以查找相关行，然后返回表读取其余数据（例如 info_1、info_2）。

• Uou 可能还希望将 info_1、info_2 和 info_3 包含到非聚集索引中
• 这将有助于基于 B 的类型 C 的查询，同时使索引维护和包括索引在内的其他任务变慢一些。
• 我建议尝试不使用这些额外的列，然后仅在必要时添加。