欢迎来到电脑知识学习网,专业的电脑知识大全学习平台!

手机版

mysql联合索引支持的匹配原则(mysql联合索引的使用规则)

网络知识 发布时间:2022-01-08 08:23:03

最近有人问到这个问题,之前也一直没有深究联合索引具体使用逻辑,查阅多篇文章,并经过测试,得出一些结论

测试环境:SQL Server 2008 R2

测试结果与MySql联合索引查询机制类似,可以认为MySql是一样的原理

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

联合索引概念:当系统中某几个字段经常要做查询,并且数据量较大,达到百万级别,可多个字段建成索引

使用规则:

1.最 左 原则,根据索引字段,由左往右依次and(where字段很重要,从左往右)

2.Or 不会使用联合索引

3.where语句中查询字段包含全部索引字段,字段顺序无关,可随意先后

4.数据量较少时,一般不会使用索引,数据库本身就会自动判断是否使用索引

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

测试脚本(部分借鉴其他作者的脚本):

mysql联合索引支持的匹配原则(mysql联合索引的使用规则)(1)

/*创建测试数据表*/
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

mysql联合索引支持的匹配原则(mysql联合索引的使用规则)(2)

mysql联合索引支持的匹配原则(mysql联合索引的使用规则)(3)

/×未建索引查询测试×/
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

mysql联合索引支持的匹配原则(mysql联合索引的使用规则)(4)

mysql联合索引支持的匹配原则(mysql联合索引的使用规则)(5)

/*建立索引*/
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

mysql联合索引支持的匹配原则(mysql联合索引的使用规则)(6)

mysql联合索引支持的匹配原则(mysql联合索引的使用规则)(7)

mysql联合索引支持的匹配原则(mysql联合索引的使用规则)(8)

/*索引使用测试结论*/
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'   --不用索引

mysql联合索引支持的匹配原则(mysql联合索引的使用规则)(9)

mysql联合索引支持的匹配原则(mysql联合索引的使用规则)(10)


责任编辑:电脑知识学习网

网络知识