SQL Server（MySql）中的聯合主鍵（聯合索引） 索引分析

最近有人問到這個問題，之前也一直沒有深究聯合索引具體使用邏輯，查閱多篇文章，並經過測試，得出一些結論

測試環境：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'   --不用索引

如有問題歡迎留言交流！