SQL Server中的聯合主鍵、聚集索引、非聚集索引、mysql 聯合索引

文章出處

我們都知道在一個表中當需要2列以上才能確定記錄的唯一性的時候，就需要用到聯合主鍵，當建立聯合主鍵以后，在查詢數據的時候性能就會有很大的提升，不過并不是對聯合主鍵的任何列單獨查詢的時候性能都會提升，但我們依然可以通過對聯合主鍵中的首列除外的其他列建立非聚集索引來提高性能。
本文將對聯合主鍵、聚集索引、非聚集索引對查詢性能的影響舉例說明。
步驟一，建立一個測試表，并且插入350萬條以上的數據。

/*創建測試數據表*/
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='DD7D9F34-3A9C-43CA-836B-F2BABD78CE70' and id='103ACE5C-7'
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
--(1 row(s) affected)
--未建立索引時查找數據消耗微秒數
--530000
--(20 row(s) affected)
--未建立索引時查找第二列數據消耗微秒數
--500000
從執行結果我們可以看出，當沒有索引的時候，SQL Server會遍歷整個表，因此需要很長的時間。
步驟三，建立聯合主鍵（會自動創建聚集索引）并查詢測試
go
/*建立聯合主鍵*/
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='DD7D9F34-3A9C-43CA-836B-F2BABD78CE70' and id='103ACE5C-7'
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

go
--(1 row(s) affected)
--建立索引時查找數據消耗微秒數
--0

--(20 row(s) affected)
--建立索引后查找第二列數據消耗微秒數
--500000
從上面看出，建立聯合主鍵后，查詢第一列或者同時查詢兩列（and關系）速度會非常的快，小于1微妙，但查詢聯合主鍵的第二列的時候卻特別的慢，因為無法通過索引查詢。
步驟四，給聯合主鍵的第二列建立非聚集索引，并且測試
go
/*給第二列創建非聚集索引*/
create index index_parent on MyTestTable(parent asc)
declare @beginTime datetime =getdate()
declare @elapsedSecond int =0
select * from MyTestTable where parent='DD7D9F34-3A9C-43CA-836B-F2BABD78CE70' and id='103ACE5C-7'
select @elapsedSecond=DATEDIFF(MICROSECOND,@beginTime,GETDATE())
print '為第二列建立索引時查找數據消耗微秒數'
print @elapsedSecond

select @beginTime=GETDATE()
select * from MyTestTable where parent='9A75DC47-DDF7-4922-9179-E87B91FE3921'
select @elapsedSecond=DATEDIFF(MICROSECOND,@beginTime,GETDATE())
print '為第二列建立索引后查找第二列數據消耗微秒數'
print @elapsedSecond

--(1 row(s) affected)
--為第二列建立索引時查找數據消耗微秒數
--0

--(20 row(s) affected)
--為第二列建立索引后查找第二列數據消耗微秒數
--0
從執行結果可以看出，建立索引后，查詢第二列的速度也非常的快了。
總結
一般情況下，對于一個表T，聯合主鍵（A,B)，下列情況的查詢時，SQL Server 可以從索引中查詢，速度較快：
select * from T where A=Value and B=Value
select * from T where B=Value and A=Value
select * from T where A=Value
下面的查詢不會經過索引，速度會比較的慢
select * from T where A=Value or B=Value
select * from T where B=Value