USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(id INT IDENTITY(1,1) PRIMARY KEY, a INT,b INT,c INT)
GO
--插入 1000 * 1000 = 1百万条数据
;WITH cte AS (SELECT TOP 1000 1 AS id FROM [master].dbo.spt_values AS sv )
,t1 as (SELECT ROW_NUMBER() OVER(ORDER BY(SELECT 1)) AS id FROM cte AS a CROSS APPLY cte AS b)
INSERT INTO t(a,b,c)
SELECT id,id,id FROM t1
GO
CREATE INDEX ix_t_a ON t(a)
CREATE INDEX ix_t_b ON t(b)
CREATE INDEX ix_t_c ON t(c)
GO
--索引查找 ix_t_a 50%, 键查找 50%
SELECT * FROM t WHERE a=10 AND b=10 AND c=10
--索引查找 ix_t_b 50%, 键查找 50%
SELECT * FROM t WHERE a>10 AND b=10 AND c=10
--索引查找 ix_t_b 50%, 键查找 50%
SELECT * FROM t WHERE a>999999 AND b=10 AND c=10
-- 1000000
--索引查找 ix_t_a 50%, 键查找 50%
SELECT * FROM t WHERE a>1000000 AND b=10 AND c=10
--索引查找 ix_t_a 16%, ix_t_b 16%, 合并联接 43%, ix_t_c 16%, 合并联接 0%, 键查找 24%
SELECT TOP 10 * FROM t WHERE a=10 OR b=10 OR c=10
/*******************************************
* 多个单列索引总结:
* 1. 在查询时如果全是and, 只可能用到一个
* 2. 如果有 or, 可能用到多个
* 3. 挑选密度最小的,IO代价最小的索引作为实际运行的索引
* 4. = 比 > 优先, 除非 > 条件产生的根本没有记录。
*******************************************/