sql的普通索引和复合索引的问题

假如我在字段A中创建普通单索引，然后又在字段A创建复合索引，但是A不是复合索引
的初始列，现在我只用字段A作为查询条件进行查询，请问会用到索引么，还是全表查询

会用你A的普通索引来查询

专注，勤学，慎思
导航
博客园
首页
新随笔
联系
订阅订阅
管理
< 2017年3月 >
日一二三四五六
26 27 28 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31 1
2 3 4 5 6 7 8
统计
随笔 - 42
文章 - 0
评论 - 136
引用 - 0
公告
昵称：MSSQL123
园龄：5年
粉丝：77
关注：81
+加关注
搜索

最新随笔
1. PowerShell 连接SQLServer数据库执行DML，DDL，DCL操作
2. SQL Server 用角色(Role)管理数据库权限
3. sp_executesql 或者 EXECUTE 执行动态sql的权限问题
4. 关于T-SQL重编译那点事，内联函数和表值函数在编译生成执行计划的区别
5. 关于T-SQL重编译那点事，WITH RECOMPILE和OPTION(RECOMPILE)区别仅仅是存储过程级重编译和SQL语句级重编译吗
6. Nested Loops join时显示no join predicate原因分析以及解决办法
7. T-SQL字符串相加之后被截断的那点事
8. 浅析SQL查询语句未显式指定排序方式，无法保证同样的查询每次排序结果都一致的原因
9. PowerShell 数组以及XML操作
10. 野路子出身PowerShell 文件操作实用功能
我的标签
SQL Server管理(3)
统计信息 Statistics(3)
PowerShell(2)
自动参数化(1)
no join predicate(1)
parallel 并行(1)
Parameter Embedding Optimization(1)
parameter sniff(1)
pivot(1)
表值函数(1)
更多
随笔分类
MySQL(1)
oracle
PowerShell(3)
SQL Server 管理(4)
SQL Server 基础(3)
SQL Server 权限(2)
SQL Server 优化(21)
T-SQL(6)
工作随想(1)
其他(2)
随笔档案
2017年2月 (3)
2017年1月 (2)
2016年12月 (5)
2016年11月 (4)
2016年10月 (4)
2016年9月 (4)
2016年8月 (2)
2016年7月 (4)
2016年6月 (4)
2016年5月 (2)
2016年4月 (2)
2016年3月 (1)
2015年11月 (1)
2015年10月 (1)
2015年9月 (1)
2015年2月 (1)
2015年1月 (1)
积分与排名
积分 - 31439
排名 - 8411
最新评论
1. Re:T-SQL字符串相加之后被截断的那点事
@Uest感谢！知道这个问题之后，就提示小伙伴们避免直接对字符串相加，赋值给（n）varchar（max）之后再用变量相加了。...
--MSSQL123
2. Re:T-SQL字符串相加之后被截断的那点事
整理文件补充群里小伙伴提供
--Uest
3. Re:SQL Server 用角色(Role)管理数据库权限
如果对权限限制要求比较高，需要按业务划分帐户、分配不同的权限，比如应用程序只能有对应数据库执行存储过程的权限，开发人员只有查询、查看定义的权限、作业执行也是专门的帐户…所有帐户需定期修改密码、遇到离职......
--Uest
4. Re:关于T-SQL重编译那点事，内联函数和表值函数在编译生成执行计划的区别
支持
--坦荡
5. Re:SQL Server 存储过程中处理多个查询条件的几种常见写法分析，我们该用那种写法
@Uest我测试环境是sqlserver 2014declare @p_costomerId varchar(20)set @p_costomerId='C86'select * from SaleO......
--MSSQL123
阅读排行榜
1. SQL Server SQL性能优化之--通过拆分SQL提高执行效率，以及性能高低背后的原因(1404)
2. SQL优化查询语句中，用 inner join 作为过滤条件和用where作为过滤条件的区别(1285)
3. SQL Server 存储过程中处理多个查询条件的几种常见写法分析，我们该用那种写法(1150)
4. SQL Server创建复合索引时，复合索引列顺序对查询的性能影响(1056)
5. sqlserver 存储过程中使用临时表到底会不会导致重编译(1036)
评论排行榜
1. SQL Server SQL性能优化之--通过拆分SQL提高执行效率，以及性能高低背后的原因(15)
2. SQL Server 存储过程中处理多个查询条件的几种常见写法分析，我们该用那种写法(14)
3. SQL Server中参数化SQL写法遇到parameter sniff ，导致不合理执行计划重用的一种解决方案(13)
4. Sql Server 聚集索引扫描 Scan Direction的两种方式------FORWARD 和 BACKWARD(11)
5. SELECT TOP 1 比不加TOP 1 慢的原因分析以及SELECT TOP 1语句执行计划预估原理(7)
推荐排行榜
1. SQL Server 存储过程中处理多个查询条件的几种常见写法分析，我们该用那种写法(20)
2. SQL Server 并行操作优化，避免并行操作被抑制而影响SQL的执行效率(14)
3. SQL Server SQL性能优化之--通过拆分SQL提高执行效率，以及性能高低背后的原因(11)
4. SQL Server SQL性能优化之--数据库在“简单”参数化模式下，自动参数化SQL带来的问题(10)
5. 连接SQLServer时，因启用连接池导致孤立事务的原因分析和解决办法(9)
SQL Server创建复合索引时，复合索引列顺序对查询的性能影响

说说复合索引

写索引的博客太多了，一直不想动手写，有一下两个原因：
一是觉得有炒剩饭的嫌疑，有兄弟曾说：索引吗，只要在查询条件上建索引就行了，真的可以这么暴力吗？
二来觉得，索引是个非常大的话题，很难概括出所有的情况，你不整出点新意来，倒是有抄袭照搬的嫌疑

既然写了，就写一点稍微不一样的东西出来，
好了，废话打住，开搞

　　20160814备注：今天发现一个类似的文章：http://www.cnblogs.com/fly_zj/archive/2012/08/11/2633629.html ；

　　可以理解为：添加组合索引时，做相等运算字段应该放在最前面

搭建测试环境：

创建一张表，模拟实际业务中的一个表，往里面填入数据，
时间字段上，相对按照时间均匀地填充，其他字段以GUID填充

复制代码
Create table BusinessInfoTable
(
BuniessCode1 varchar(50),
BuniessCode2 varchar(50),
BuniessCode3 varchar(50),
BuniessCode4 varchar(50),
BuniessStatus1 tinyint,
BuniessStatus2 tinyint,
BuniessDateTime1 Datetime,
BuniessDateTime2 Datetime,
OtherColumn1 varchar(50),
OtherColumn2 varchar(50),
OtherColumn3 varchar(50)
)

declare @i int=0
while @i<1000000
begin
insert into BusinessInfoTable
values
(
NEWID(),NEWID(),NEWID(),NEWID(),RAND()*100,RAND()*100,
DATEADD(MI,@i,GETDATE()),DATEADD(MI,@i,GETDATE()),NEWID(),NEWID(),NEWID()
)
set @i=@i+1
end
复制代码
现在有这么一个查询（实际上查询远比这个复杂，我简化一点，不要说我刻意造环境）

复制代码
select OtherColumn2,
BuniessStatus1,
BuniessStatus2,
BuniessDateTime1,
BuniessDateTime2
from BusinessInfoTable
where BuniessDateTime1 between '2016-6-21' and '2016-6-28'
and BuniessDateTime2 between '2016-6-21' and '2016-6-28'
and BuniessStatus1 = 55
and BuniessStatus2 = 66
复制代码
郑重的说明一点：

暂时不考虑聚集索引，毕竟一个表上只能有一个聚集索引，
别人也不是傻子，不会轻易去建聚集索引，聚集索引早被占用了
既然被占用了，我的原则是一般不去动别人现有的东西的，比如别人建了聚集索引，你给人家删了，根据自己的情况建聚集索引
这不是找骂么

有经验的你一定考虑符合索引了，同时考虑到为避免Key Lookup导致的书签查找，我们把查询索要的OtherColumn2列include进来
比如这样

CREATE NONCLUSTERED INDEX IDX_1 ON BusinessInfoTable
(BuniessStatus1,BuniessStatus2,BuniessDateTime1,BuniessDateTime2)
INCLUDE(OtherColumn2)

或者这样，只是索引列顺序不一样

CREATE NONCLUSTERED INDEX IDX_1 ON BusinessInfoTable
(BuniessDateTime1,BuniessDateTime2,BuniessStatus1,BuniessStatus2)
INCLUDE(OtherColumn2)

当然可以随意调整四个列的顺序，我就不过多地做演示了，有兴趣的自己试

这里的前导列的顺序并不会影响到索引的使用，查询的时候都是非聚集索引Seek，绝对的

那么问题来了，完全一样的查询条件，结果一样，使用不同的索引，索引的区别仅仅是列顺序不一样，其代价一样吗，先猜测一下，有区别吗？

　　同样查询，使用不同索引的结果（分别是上面的IDX_1和IDX_2）：

下面看图说话

看看IO情况

原因分析

　　看来是有点差别吧，好似乎这个差别还真不小（以往写文章，我测试环境弄不好，对比出来的效果不明显，感觉没啥说服力，这次对比还是比较明显的）

　　究竟原因在何？

　　索引是以平衡树（B树）的方式存在的，复合索引的列的顺序决定了B树的信息的存储的顺序

　　如果是以BuniessStatus1列为前导列，因为BuniessStatus1分布的范围（相对）较小，

　　这样在查询的时候通过BuniessStatus1=55就可以过滤出来一个比较小的结果集，后面依次用其他条件过滤就相对较快了

　　比如BuniessStatus1=55过滤出来符合条件的数据有5条，

　　加上BuniessStatus2 BuniessDateTime1 BuniessDateTime2 这三个条件再过滤，出来一条数据。

　　如果BuniessDateTime1 是索引的前导列，用BuniessDateTime1 between '2016-6-21' and '2016-6-28'过滤

　　可能会有10000条数据，然后依次再用 BuniessDateTime2，BuniessStatus1， BuniessStatus2过滤

　　最后也只有一条符合条件的数据。

　差别就在于：一开始的过滤条件，决定了查询多少page初步确定满足条件的数据，再进一步的进行过滤

　　如果最开始就相对精确地确定了满足查询条件的数据范围，后面可以通过相对较小的代价来最终确认出满足条件的数据

　　如果最开始相对模糊地却确定了满足查询条件的数据范围，那么这个过程的代价就相对比较大，虽然后面通过每一个条件的过，结果是一样的

　　当然这种索引的建立跟数据分布有关，

　　但是，我没有下结论说，复合索引一定要按照什么什么顺序来是最好的

　　还是那句话：具体问题具体分析，避免经验主义，没有一刀切的手段可以解决所有的问题。

总结：

　　本文通过一个简单的例子，分析了创建符合索引时，列的顺序对查询的影响，说明在创建索引的时候，不仅仅要考虑在哪些列上创建索引，同时也要注意到，索引列的顺序，是否会对查询产生影响。避免一说到索引，就是“在查询条件上建索引”的暴力做法。

分类: SQL Server 优化
好文要顶关注我收藏该文

MSSQL123
关注 - 81
粉丝 - 77
+加关注
5
发表评论
« 上一篇：SQL Server在执行SQL语句时，表之间驱动顺序对性能的影响
» 下一篇：SQL Server中参数化SQL写法遇到parameter sniff ，导致不合理执行计划重用的一种解决方案
posted on 2016-06-21 17:43 MSSQL123 阅读(1056) 评论(2) 编辑收藏

#1楼 2016-06-21 18:53 Double_K

赞一个
支持(1)反对(0)

#2楼[楼主] 2016-06-21 19:44 MSSQL123

@ Double_K
多谢支持，没记错的话我应该是你的第一个关注者，看你写的文章，学到了好多。
支持(0)反对(0)

刷新评论刷新页面返回顶部
注册用户登录后才能发表评论，请登录或注册，访问网站首页。
【推荐】50万行VC++源码: 大型组态工控、电力仿真CAD与GIS源码库
【推荐】Google+GitHub联手打造前端工程师课程

最新IT新闻:
· 离开顺丰京东的日子
· 刘强东微博发声：电子商务不是法外之地
· 支付宝员工登记“器官捐献” 一招让妈妈从崩溃到骄傲
· LEGO Worlds登陆Xbox One平台
· Kindle包月服务上线一年后中国成为全球第三大市场
» 更多新闻...

最新知识库文章:
· 垃圾回收原来是这么回事
· 「代码家」的学习过程和学习经验分享
· 写给未来的程序媛
· 高质量的工程代码为什么难写
· 循序渐进地代码重构
» 更多知识库文章...
Powered by:
博客园
Copyright © MSSQL123

返回主页农夫和白菜
博客园首页新随笔联系订阅管理
随笔 - 3 文章 - 0 评论 - 0
mySql的普通索引和复合索引
有关普通索引和组合索引问题：
索引分单列索引和组合索引：单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引；组合索引，即一个索包含多个列。
MySQL索引类型包括：
(1)普通索引是最基本的索引，它没有任何限制。它有以下几种创建方式：
◆创建索引
　　CREATE INDEX indexName ON mytable(username(length));
　　如果是 CHAR，VARCHAR类型，length可以小于字段实际长度;如果是BLOB和TEXT类型，必须指定 length，下同。
◆修改表结构
　　ALTER mytable ADD INDEX [indexName] ON (username(length))
◆ 创建表的时候直接指定
　　CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX indexName) );
删除索引的语法：
　　 DROP INDEX [indexName] ON mytable;
(2)唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
◆创建索引
　　 CREATE UNIQUE INDEX indexName ON mytable(username(length))
◆修改表结构
　　 ALTER mytable ADD UNIQUE [indexName] ON (username(length))
◆创建表的时候直接指定
　　 CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE indexName) );
(3)主键索引是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：
　　 CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );
当然也可以用 ALTER 命令。记住：一个表只能有一个主键。
(4)组合索引
　　为了形象地对比单列索引和组合索引，为表添加多个字段：
　　 CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );
为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：
　　 ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);
建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。
如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。
建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：
　　 usernname,city,age usernname,city usernname
为什么没有 city，age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：
　　SELECT * FROM mytable WHREE username="admin" AND city="郑州" 　　SELECT * FROM mytable WHREE username="admin"
而下面几个则不会用到：
　　SELECT * FROM mytable WHREE age=20 AND city="郑州" 　　SELECT * FROM mytable WHREE city="郑州"
(5)建立索引的时机
　　到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢?一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：
SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'
此时就需要对city和age建立索引，由于mytable表的 userame也出现在了JOIN子句中，也有对它建立索引的必要。
刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：
　　SELECT * FROM mytable WHERE username like'admin%'
而下句就不会使用：
　　 SELECT * FROM mytable WHEREt Name like'%admin'
因此，在使用LIKE时应注意以上的区别。
(6)索引的不足之处
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：
　　 ◆虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
　　 ◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
　　索引只是提高效率的一个因素，如果你的 MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
(7)使用索引的注意事项
使用索引时，有以下一些技巧和注意事项：
　　 ◆索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
◆使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
◆索引列排序
MySQL查询只使用一个索引，因此如果 where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
◆like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
◆不要在列上进行运算
　　 select * from users where YEAR(adddate) 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
　　 select * from users where adddate ◆不使用NOT IN和操作
引用： http://zhidao.baidu.com/link?url=xF1BrmM9CNSE_ptqmRcEZQgTHIw5UK7WbMI_iAOMWzYS8MdNYbVMrpv820wzn6nxlkpyx2JAH_OXmAqFbfR32K

标签: 普通索引和复合索引
好文要顶关注我收藏该文

农夫和白菜
关注 - 1
粉丝 - 0
+加关注
0 0
« 上一篇：Linux常用命令
posted @ 2016-08-21 21:05 农夫和白菜阅读(630) 评论(0) 编辑收藏
刷新评论刷新页面返回顶部
注册用户登录后才能发表评论，请登录或注册，访问网站首页。
【推荐】50万行VC++源码: 大型组态工控、电力仿真CAD与GIS源码库
【推荐】Google+GitHub联手打造前端工程师课程

最新知识库文章:
· 垃圾回收原来是这么回事
· 「代码家」的学习过程和学习经验分享
· 写给未来的程序媛
· 高质量的工程代码为什么难写
· 循序渐进地代码重构
» 更多知识库文章...
公告
昵称：农夫和白菜
园龄：7个月
粉丝：0
关注：1
+加关注
< 2017年3月 >
日一二三四五六
26 27 28 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31 1
2 3 4 5 6 7 8
搜索