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

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=3557643declare @i int =0declare @id varchar(10),@parent varchar(40)while(@i<@count)beginselect @id=left(newid(),10)if(@i % 20=0)beginselect @parent=left(newid(),40)endinsert MyTestTable(id,parent) values(@id,@parent)select @i=@i+1endgo

  

/×未建索引查詢測試×/
declare @beginTime datetime =getdate()declare @elapsedSecond int =0select * from MyTestTable where parent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4' and id='FD3687F4-1'select @elapsedSecond=DATEDIFF(MICROSECOND,@beginTime,GETDATE())print '未建立索引時尋找資料消耗微秒數'print @elapsedSecondselect @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 =0select * 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'   --不用索引

如有問題歡迎留言交流！