Recently, someone asked this question in the interview. Before that, they had not studied the specific use logic of the joint index, consulted several articles, and came to some conclusions after testing

Test environment: SQL Server 2008 R2

The test results are similar to the query mechanism of MySql joint index. We can think that MySql is the same principle

====================================================

Joint index concept: when several fields in the system are often queried, and the data volume is large, reaching the level of one million, multiple fields can be used to build an index

Rules of use:

1. The leftmost principle: according to the index field, from left to right and (where field is very important, from left to right)

2.Or will not use union index

3. The query field in the where statement contains all index fields. The order of the fields is independent and can be arranged in any order

4. When the data volume is small, the index will not be used generally, and the database mechanism will automatically determine whether to use the index

=====================================================

Test scripts (some of them refer to scripts of other authors):

/*Create test data table*/
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
/*Add 10000 random string test data in minutes*/
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

/×Query test without index×/
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 'Microseconds spent looking up data when index is not established'
print @elapsedSecond

select @beginTime=GETDATE()
select * from MyTestTable where parent='F535C18F-BD48-4D45-88DF-9653BB9B422D'
select @elapsedSecond=DATEDIFF(MICROSECOND,@beginTime,GETDATE())
print 'Microseconds spent looking up the second column of data when not indexed'
print @elapsedSecond

/*Indexing*/
alter table MyTestTable add constraint PK_id_parent primary key(id asc,parent asc)

/*Query after indexing*/
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 'Microseconds spent looking up data when indexing'
print @elapsedSecond
 
select @beginTime=GETDATE()
select * from MyTestTable where parent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4'
select @elapsedSecond=DATEDIFF(MICROSECOND,@beginTime,GETDATE())
print 'Microseconds spent searching the second column of data after indexing'
print @elapsedSecond

/*Index usage test conclusion*/
select * from MyTestTable where   id='FD3687F4-1' --Use index
select * from MyTestTable where  id='FD3687F4-1' and parent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4' and intcolumn>0  --Use index
select * from MyTestTable where  id='FD3687F4-1' and intcolumn>0  and parent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4'    --Use index
select * from MyTestTable where  id='FD3687F4-1' and intcolumn>0 --Use index
select * from MyTestTable where parent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4' and id='FD3687F4-1'   --Use index

select * from MyTestTable where   parent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4' and intcolumn>0   --No index
select * from MyTestTable where parent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4' or id='FD3687F4-1'   --No index

If you have any questions, please leave a message!