共计 2548 个字符，预计需要花费 7 分钟才能阅读完成。
最近有人问到这个问题，之前也一直没有深究联合索引具体使用逻辑，查阅多篇文章，并经过测试，得出一些结论
测试环境：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
正文完
星哥玩云-微信公众号
