如何将低/高基数列配对为复合索引? [英] How to pair low/high cardinality columns as composite indexes?
问题描述
此问题与Oracle DB有关，因此，如果我想知道一些一般性的答案.由于我正在从Derby/MySQL和其他数据库中删除有关此主题的信息.
This question concerns to Oracle DB, so if there are general answers I would like to know. As I am discarding information from Derby/MySQL and other DBs regarding this subject.
假设我在WHERE子句的以下列中使用了几个查询:
Let's say I have several queries using the following columns on its WHERE clause:
Column | Cardinality | Selectivity
_______|__________________________
A | low | low
B | high | low
C | low | low
D | high | high
E | low | low
F | low | low
-- Queries
SELECT * FROM T WHERE A:=? AND B:=?
SELECT * FROM T WHERE A:=? AND B:=? AND C:=?
SELECT * FROM T WHERE A:=? AND C:=?
SELECT * FROM T WHERE A:=? AND C:=? AND D:=?
SELECT * FROM T WHERE A:=? AND E:=? AND F:=?
-
将这些列(考虑基数混合)配对作为复合索引是否有任何好处?如果是这样，遵循的逻辑是什么? 我已经理解此解释，但这是针对SQL的服务器，其行为可能有所不同.
Is there any benefit from pairing these columns (taking into account cardinality mixing) as composite indexes? If so, what is the logic to follow? I have understood this explanation but it is for SQL Server and it may behave differently.
是否有必要覆盖索引而不是单个小的复合索引?
Is it worthwhile to do covering indexes instead of individual small composite indexes?
复合索引的列顺序是否重要?即:
Does it matter the column order of composite indexes? i.e:
-- Regardless the column order on the table creation.
CREATE INDEX NDX_1 ON T (A, C);
-- Versus:
CREATE INDEX NDX_1 ON T (C, A);
- 该索引有用吗?
CREATE INDEX NDX_2 ON T(E, F); -- (low + low) Ignoring 'A' column.
推荐答案
一些注意事项，请记住这些是普遍性
A few things and bear in mind these are generalities
-
通常，您只能使用索引的前导部分.所以看 在你的例子中
Generally you can only use the leading parts of an index. So looking at your examples
如果您在(A，B，C)上有一个索引并且在A和上有一个谓词 C，则只能使用A上的索引.现在有一些情况 可以使用索引 的非前导部分；你会看见 这在执行计划中作为SKIP-SCAN操作，但是它们是 通常不是最理想的.因此，您可能想要拥有(A，C)和(C，A)
If you have an index on ( A, B, C ) and you have a predicate on A and C, then only the index on A can be used. Now there are some cases where the non-leading part of an index can be used; you will see this in an execution plan as a SKIP-SCAN operation, but they are often sub-optimal. So you may want to have (A, C) and ( C, A )
如果您不投影除索引中的列之外的其他列，则覆盖索引可能会很有用.
A covering index can be useful, if you are not projecting columns other than those in the index.
通常，如果列的选择性低，通常不需要或不需要索引.但是，可能有两个色谱柱分别具有较低的选择性，但在组合使用时具有较高的选择性. (实际上，这是维模型中位图索引/星形转换的前提.)
Again generally, you do not usually want or need an index if the column has low selectivity. However, it's possible that you have two columns that individually have low selectivity, but have high selectivity when used in combination. (In fact, this is the premise of a bitmap index / star transformation in a dimensional model).
如果多列索引很有用，则您可能希望将选择性最低的列放在第一位并启用索引压缩.索引压缩在某些情况下可以节省大量空间，而CPU开销却很小.
If a multi-column index is useful you may want to put the column with the lowest selectivity first and enable index compression. Index compression can save a huge amount of space in some cases and has very little CPU overhead.
最后，SQL Monitor报表将帮助您优化运行sql语句.
Finally, a SQL Monitor report will help you optimizing a sql statement when it comes to running it.
这篇关于如何将低/高基数列配对为复合索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
