最近有人问到这个问题, 之前也一直没有深究联合索引具体使用逻辑, 查阅多篇文章, 并经过测试, 得出一些结论
测试环境: SQL Server 2008 R2
测试结果与 MySql 联合索引查询机制类似, 可以认为 MySQL 是一样的原理
====================================================
联合索引概念: 当系统中某几个字段经常要做查询, 并且数据量较大, 达到百万级别, 可多个字段建成索引
使用规则:
1. 最 左 原则, 根据索引字段, 由左往右依次 and(where 字段很重要, 从左往右)
2.Or 不会使用联合索引
3.where 语句中查询字段包含全部索引字段, 字段顺序无关, 可随意先后
4. 数据量较少时, 一般不会使用索引, 数据库本身机制会自动判断是否使用索引
=====================================================
测试脚本 (部分借鉴其他作者的脚本):
- /* 创建测试数据表 */
- create table MyTestTable
- (
- id varchar(10)not null,
- parent varchar(40) not null,
- addtime datetime default(getdate()),
- intcolumn int default(10),
- bitcolumn bit default(1)
- )
- go
- /* 添加万条随机字符串测试数据耗时分钟 */
- declare @count int=3557643
- declare @i int =0
- declare @id varchar(10),@parent varchar(40)
- while(@i<@count)
- begin
- select @id=left(newid(),10)
- if(@i % 20=0)
- begin
- select @parent=left(newid(),40)
- end
- insert MyTestTable(id,parent) values(@id,@parent)
- select @i=@i+1
- end
- go
/× 未建索引查询测试 ×/
- declare @beginTime datetime =getdate()
- declare @elapsedSecond int =0
- select * from MyTestTable where parent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4' and id='FD3687F4-1'
- select @elapsedSecond=DATEDIFF(MICROSECOND,@beginTime,GETDATE())
- print '未建立索引时查找数据消耗微秒数'
- print @elapsedSecond
- select @beginTime=GETDATE()
- select * from MyTestTable where parent='F535C18F-BD48-4D45-88DF-9653BB9B422D'
- select @elapsedSecond=DATEDIFF(MICROSECOND,@beginTime,GETDATE())
- print '未建立索引时查找第二列数据消耗微秒数'
- print @elapsedSecond
- /* 建立索引 */
- alter table MyTestTable add constraint PK_id_parent primary key(id asc,parent asc)
- /* 建立索引后的查询 */
- declare @beginTime datetime =getdate()
- declare @elapsedSecond int =0
- select * from MyTestTable where parent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4' and id='FD3687F4-1'
- select @elapsedSecond=DATEDIFF(MICROSECOND,@beginTime,GETDATE())
- print '建立索引时查找数据消耗微秒数'
- print @elapsedSecond
- select @beginTime=GETDATE()
- select * from MyTestTable where parent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4'
- select @elapsedSecond=DATEDIFF(MICROSECOND,@beginTime,GETDATE())
- print '建立索引后查找第二列数据消耗微秒数'
- print @elapsedSecond
- /* 索引使用测试结论 */
select * from MyTestTable where id='FD3687F4-1' -- 用索引
select * from MyTestTable where id='FD3687F4-1' and parent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4' and intcolumn>0 -- 用索引
select * from MyTestTable where id='FD3687F4-1' and intcolumn>0 and parent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4' -- 用索引
select * from MyTestTable where id='FD3687F4-1' and intcolumn>0 -- 用索引
select * from MyTestTable where parent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4' and id='FD3687F4-1' -- 用索引
select * from MyTestTable where parent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4' and intcolumn>0 -- 不用索引
select * from MyTestTable where parent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4' or id='FD3687F4-1' -- 不用索引
来源: http://www.linuxidc.com/Linux/2018-01/150559.htm