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
转载:http://www.cnblogs.com/fjchenqian/archive/2011/09/22/2184656.html
相关:
相关文章
- mysql-server-5.7.25源码编译安装
- mysql表检查分析优化
- MySQL Doublewrite Buffer及业务评估
- 基于表的数据字典构造MySQL建表语句
- 解决Lost connection to MySQL server during query错误方法
- Caused by: com.mysql.cj.core.exceptions.InvalidConnectionAttributeException: The server time zone value '�й���ʱ��' is unrecognized or represents more than one time zone. You must configure either the
- MySQL错误: could not retrieve transation read-only status server
- SQL学习之Can't connect to MySQL server on localhost (10061)
- MySQL 下mysqladmin日常管理命令总结
- Mysql Server System Variables [官网资料]
- [转] MySQL 的 MRR 到底是什么?
- Mysql mysql lost connection to server during query 问题解决方法
- Starting MySQL.. ERROR! The server quit without updating PID file (/usr/local/mysql/data/vm10-0-0-19
- mysql如何更改character-set-server默认为latin1
- MyBatis中如何一次执行多条语句(使用mysql数据库)
- MySQL 状态变量(Server Status Variables)
- mysql 修改root密码
- 【mysql】 获取几天前的日期
- MySQL逻辑架构、存储引擎和SQL预热 --【MySQL高级篇1】
- MySQL IF CASE 例子
- MySQL 中文未正常显示
- 【MySQL】MES中,发货计划取数逻辑
- Linux MySQL单实例源码编译安装5.6
- MySQL(Navicat)运行.sql文件时报错[Err] 2006 - MySQL server has gone away 的解决方法
- 【JAVA高级】——myEclipse连接mysql启动数据库服务
- mysql:三范式
- 【Mysql优化三章】第一章
- ERROR 1130 (HY000): Host ‘192.168.3.238‘ is not allowed to connect to this MySQL server
- jdbc连接mysql