可以同时激活表的两个索引 [英] Two index of a table can be activated at the same time
问题描述
嗨人。
我想知道我是否有两个INDEX的TABLE。第一个DataType Int，第二个DataType DateTime
如果我必须执行查询以使用ColumnTypeInt和ColumnTypeDateTime
索引可以激活吗？或者在这种情况下是什么？
SELECT * FROM Table WHERE ColumnTypInt = 12345 AND ColumnTypeDateTime ='2018 -04-26'
祝你好运。
Rene
我尝试了什么：
我执行查询，但结果需要花费很多时间。
一种方法可能是使用计算列和索引：如何在SQL Server计算列上创建索引 [ ^ ]
除了那个，你可以在同一个桌子上有多个索引。
这真的取决于你在桌子上做什么，没有任何一个尺寸适合所有能给你最好的解决方案结果。
我建议尝试一些测试方法，并检查执行计划以查看SQL最终结果。
查看SSMS中的估计或实际执行计划，以查看正在使用的索引：
显示预计执行计划Microsoft Docs [ ^ ]
显示实际执行计划Microsoft Docs [ ^ ]
您可以在多列上创建索引，这可能会有所帮助。但是，由于您正在从表中选择每一列，查询优化器可能会 - 可能不正确 - 决定扫描整个表更有效，而不是寻找索引，然后对每一行执行RID查找。 / blockquote>
首先，您不要激活索引。 SQL Server优化器决定是否使用索引（或多个索引）。你的工作是确保你有适当的索引，但你也没有过度索引，因为这会降低修改的性能。
说完了，回到您的实际问题：是，如果优化程序认为合理，单个表上的单个查询可能会使用多个索引。这是通过'加入索引'来完成的。换句话说，从两个索引中分别获取rowid，然后使用结果进行内部联接。
这可以通过简单的测试来测试。首先让我们创建一个表
CREATE TABLE IndexTest（
col1 int ，
col2 date
）;
GO
然后在其中添加合理数量的数据。数据必须包含重复值，以便单个索引不会过于选择性
SET NOCOUNT ON ;
DECLARE
@ counter1 int ，
@ counter2 int ，
@ col1 int ;
BEGIN
SET @ counter1 = 0 ;
WHILE @ counter1 < = 1000 BEGIN
SET @ col1 = @ counter1 ;
SET @ counter2 = 1 ;
WHILE @ counter2 < = 1000 BEGIN
INSERT INTO IndexTest（col1，col2） VALUES （ @ col1 ，GETDATE（）+ ROUND（RAND（） * 100， 0 ））
SET @ counter2 = @ counter2 + 1 ;
END ;
SET @ counter1 = @ counter1 + 1 ;
END ;
END ;
GO
现在我们有超过1'000'000行。
让我们创建索引
创建 < span class =code-keyword> INDEX X1 ON IndexTest（col1）;
CREATE INDEX X2 ON IndexTest（col2）;
现在考虑以下查询
SELECT *
FROM IndexTest
WHERE col1 = 123
AND col2 = CAST（（GETDATE（）+ 2 ） AS DATE ）
这两个条件将分别返回一千或更多行，但实际结果可能要小得多。在我的测试运行中，查询返回了13行。
所以让我们来看看查询是如何运行的。执行计划如下所示
| - 哈希匹配（内部联接，HASH：（[Bmk1000]）=（[Bmk1000]），残留：（[Bmk1000] = [Bmk1000]））
| - 索引寻求（OBJECT：（[IndexTest]。[X1]），SEEK：（[IndexTest]。[col1] =（123））ORDERED FORWARD）
| --Index Seek（OBJECT：（[IndexTest]。[X2]），SEEK：（[IndexTest]。[col2] = CONVERT（date，getdate（）+ CONVERT_IMPLICIT（datetime，[@ 2]，0），0） ）订购前进）
如您所见，使用两个单独的索引搜索，一个在X1上，另一个在X2上。在搜索之后，使用散列连接来组合结果。
但是，请记住，如果这对您来说是正确的方法是不可能的。这取决于几个因素，例如：
- 实际表格和列的估计基数是多少
- 您使用的其他类型的陈述
- 实际查询是否使用连接和顺序
- 依此类推......
Hi People.
I wondering if i have a TABLE with two INDEX. first one DataType Int, second one DataType DateTime
if i must to do a query that seeks results with ColumnTypeInt AND ColumnTypeDateTime
The indexs could be Activated? or what Happend in this case?
SELECT * FROM Table WHERE ColumnTypInt=12345 AND ColumnTypeDateTime='2018-04-26'
Best regards.
Rene
What I have tried:
Im execute the query, but the results take a lot of time.解决方案One way to do it might be to use a computed column and index that: How to create indexes on SQL Server computed columns[^]
Other than that, you can have multiple indexes on the same table.
It's really going to depend on what you are doing with the table, there aren't any "one size fits all" solutions which will give you the best results.
I'd suggest trying a number of approaches for testing, and checking the execution plans to see what SQL ends up with.
Check out the estimated or actual execution plan in SSMS to see which index is being used:
Display the Estimated Execution Plan | Microsoft Docs[^]
Display an Actual Execution Plan | Microsoft Docs[^]
You can create an index on multiple columns, which might help. However, since you're selecting every column from the table, the query optimizer might decide - possibly incorrectly - that it's more efficient to scan the entire table, rather than seeking on the index and then performing a RID-lookup for each row.
First of all, you don't activate indexes. SQL Server optimizer makes the decision if an index (or several indexes) will be used or not. Your job is to ensure that you have proper indexes in place, but also that you don't over-index since that would degrade performance for modifications.
Having that said, back to your actual question: Yes, a single query on a single table may use multiple indexes if the optimizer sees it reasonable. This is done by 'joining indexes'. In other words fetching rowid's separately from two indexes and then making an inner join with the results.
This can be tested with a simple test. First let's create a table
CREATE TABLE IndexTest ( col1 int, col2 date ); GO
And then add a reasonable amount of data into it. The data must contain repetitive values so that a single index isn't too selective
SET NOCOUNT ON; DECLARE @counter1 int, @counter2 int, @col1 int; BEGIN SET @counter1 = 0; WHILE @counter1 <= 1000 BEGIN SET @col1 = @counter1; SET @counter2 = 1; WHILE @counter2 <= 1000 BEGIN INSERT INTO IndexTest (col1, col2) VALUES (@col1, GETDATE() + ROUND(RAND()*100,0)) SET @counter2 = @counter2 + 1; END; SET @counter1 = @counter1 + 1; END; END; GO
Now we have a little bit over 1'000'000 rows.
Let's create our indexes
CREATE INDEX X1 ON IndexTest (col1); CREATE INDEX X2 ON IndexTest (col2);
Now consider the following query
SELECT * FROM IndexTest WHERE col1 = 123 AND col2 = CAST((GETDATE() + 2) AS DATE)
Both conditions would return thousand or more rows separately but the actual result is likely to be much smaller. In my test run the query returned 13 rows.
So let's take a look how the query was run. The execution plan looks like this
|--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]), RESIDUAL:([Bmk1000] = [Bmk1000])) |--Index Seek(OBJECT:([IndexTest].[X1]), SEEK:([IndexTest].[col1]=(123)) ORDERED FORWARD) |--Index Seek(OBJECT:([IndexTest].[X2]), SEEK:([IndexTest].[col2]=CONVERT(date,getdate()+CONVERT_IMPLICIT(datetime,[@2],0),0)) ORDERED FORWARD)
So as you can see, two separate index seeks are used, one on X1 and another one on X2. After the seeks a hash join is used to combine the results.
However, bear in mind that it's impossible to say if this is the correct approach for you. It depends on several factors like:
- What is estimated cardinality for your actual tables and columns
- What other kinds of statements you use
- Are the actual queries using joins and in which order
- And so on...
这篇关于可以同时激活表的两个索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
