和刚入门的菜鸟们聊聊--什么是聚簇索引与非聚簇索引
今天我们来聊一聊关于 聚簇索引和非聚簇索引的问题;
刚开始学数据库SQL的时候,就知道有主键啊(Primary-key),外键啊(Foreign-key)啥的,连个表查询就已经不清楚是要on 那几个字段了,在数据量不太大的情况下,根本不会考虑索引的问题了,然后,随着大数据时代的到来,数据量大了,没有索引那是要不得的啊,客户又吐槽了,你知不知道,你知不知道,我等到花儿都谢鸟。。。。。没有索引,完全依赖生硬的where条件,达到百万级数据记录之后,如果没有主键,页面加载慢的一比,查询页面分分钟可能被用户直接关闭,剩下的你就等着 产品经理的一顿又一顿吐槽了吧。。。。。
OK,闲话不多扯了,言归正传,那我们来了解一下:
(一下仅供新人学习交流与个人体会,数据库大神可直接无视,若有不当之处,欢迎大神雅正.)
- 什么是索引;
- 什么是聚簇索引和非聚簇索引;
- 为什么要建索引;
- 动手试试,看看代码怎么敲的;
- 性能比较与分析;
什么是索引.
我们来看看比较大众的定义,OK,那就直接百度百科吧:"索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。" 看中心语-关键词[一种结构],说到底索引就是对数据列的值进行结构化排序的一个东西.
通俗点讲吧. 还记得大学军训的时候吗,大伙第一天穿上迷彩服到运动场或者野外军训场地进行军训的时候,一般都是乱扎堆的吧,乱成一团,结果军训的教官来了,教官一看大伙,有男有女,有高有矮 几分钟很快就把大伙排成了m行n列的方针,尽然有序,高低有序;而且没多久教官还能很快滴说出大家的名字,‘x行y列(或者xx号学员),王大锤,出列!’ 一声令下,王大锤就从队列中走出来了,...
这段场景中,教官就是军训场地上最好的【索引】;
什么是聚簇索引和非聚簇索引
有了索引的概念认知,聚簇索引和非聚簇索引就好理解了,说一个最简单的例子吧;
【聚簇索引】
平时习惯逛图书馆的童鞋可能比较清楚,如果你要去图书馆借一本书,最开始是去电脑里面查书名然后根据书名来定位藏书在那个区,哪个书柜,哪一行,第多少本。。。清晰明确,一目了然,因为藏书的结构与图书室的位置,书架的顺序,书本的摆放顺序与书籍的编号都是从大到小一致的顺序摆放的,所以很容易找到。比如,你的目标藏书在C区2柜3排5仓,那么你走到B区你就很快知道前面就快到了C区了,你直接奔着2柜区就能找到了。 这就是雷同于聚簇索引的功效了,聚簇索引,实际存储的循序结构与数据存储的物理机构是一致的,所以通常来说物理顺序结构只有一种,那么一个表的聚簇索引也只能有一个,通常默认都是主键,设置了主键,系统默认就为你加上了聚簇索引,当然有人说我不想拿主键作为聚簇索引,我需要用其他字段作为索引,当然这也是可以的,这就需要你在设置主键之前自己手动的先添加上唯一的聚簇索引,然后再设置主键,这样就木有问题啦。
总而言之,聚簇索引是顺序结构与数据存储物理结构一致的一种索引,并且一个表的聚簇索引只能有唯一的一条;
【非聚簇索引】
同样的,如果你去的不是图书馆,而是某城市的商业性质的图书城,那么你想找的书就摆放比较随意了,由于商业图书城空间比较紧正,藏书通常按照藏书上架的先后顺序来摆放的,所以如果查询到某书籍放在C区2柜3排5仓,但你可能要绕过F区,而不是A.B.C.D...连贯一致的,也可能同在C区的2柜,书柜上第一排是计算机类的书记,也可能最后一排就是医学类书籍;
那么对照着来看非聚簇索引的概念就比较好理解了,非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系,与数据的存储物理结构没有关系;一个表对应的非聚簇索引可以有多条,根据不同列的约束可以建立不同要求的非聚簇索引;
为什么要建索引
这个问题肯定很简单啦,看了上面的描述就知道了,肯定是为了加快找到目标数据的速度,节约查找话费的时间啦,用数据库属于来描述就是 :
建立索引的目的是加快对表中记录的查找或排序。
但是话又说回来了,有了索引是不是就以为的数据的查询快得不要不要的,。。。。
或者说,添加了索引之后,查询速度一定回避没有添加索引的情况下更快? 我看未必哦。。。
我们还是先了解一下 家里索引需要付出的代价和带来的弊端吧:
一.增加了数据库的存储空间,
二.在插入和修改数据时要花费较多的时间(因为索引也要随之变动);
我们假设在一张表中的一条记录在磁盘上占用1KB话,我们对其中10B的一个字段建立索引,那么该记录对应的索引块的大小只有10B,如果一张表的的数据量比较大,大约100,000条,那么用来存储索引耗费的空间就是100,000X10B=1000,000B=10000KB=1MB,换句话说,这张白表也因为这个索引的建立而多使用了大约1MB的存储空间,当然对与大批量数据来说,这么点空间是不足为道的。但事实是,索引确实耗费了更多空间;
关于第二条我就不用赘述了,这个文字描述已经说的很清楚;
还有就是,对某些场景下,数据量不是特别大的情况下,对于某些添加索引的行为,不但不能优化查询速度,反而会减慢查询速度,当然,如果索引的建立不恰当,所选择建立索引的字段不合适,也可能会削弱查询速度,当然在数据量不大的情况下,基于SQL服务器本身强大的处理能力,这种削弱表现是非常微弱的,但是一旦数据量大起来,原本可以不需要考虑索引就能很快查询出来数据的,结果因为添加了索引反而加重了查询数据的消耗,不恰当的索引方式造成的影响就会表现的很明显;
所以,索引不是万能的,某些情况下,添加索引可能比不添加索引更慢!
动手试试:看看代码怎么敲的
建立索引之前选好表对象,假设表明为IndexTestTable此表中包含三个字段Id,Name,UniqueCode
为了更快的进行姓名查询,我们可以在Name字段上添加非聚簇索引;
创建索引的格式如下:
CREATE NONCLUSTERED INDEX [index_name【索引名称】] ON [table_name【表名称】]([column_name1【列名称】],[column_name2【列名称】],...);
我们给IndexTestTable表的Name字段添加一个非聚簇索引:
CREATE NONCLUSTERED INDEX IndexTestTable_index_name ON IndexTestTable(Name);
给IndexTestTable表的UniqueCode字段添加一个聚簇索引:
CREATE CLUSTERED INDEX IndexTestTable_index_uniquecode ON IndexTestTable(UniqueCode)
以上的代码是最简单最直接设置索引的方式,而通常实际应用中,会有多字段联合添加索引的情况,这个就需要你根据实际的应用查询场景,以及在where条件下最常用的查询字段,例如:在 TableX中你最经常查询的条件为:
SELECT Name,Message FROM TableX
WHERE 1=1 AND DeptId='003523'
AND LimitedCondition='SomeValue'
这个时候你就可以 添加一个基于 DeptId 和 LimitedCondition 两个字段的非聚簇索引,以便于加速查询速度;
CREATE NONCLUSTERED INDEX TableX_index_departid_limitedcondition ON TableX(DeptId,LimitedCondition);
简言之,就是需要根据你的实际应用场景,添加有用并且高效的索引;
性能比较与分析;
在一个有千万级数据量的某表mytable中(表没有实际意义用途,仅限于数据查询研究,只用三个字段),查询数据总数,遍历表记录耗时大约15秒;
SELECT COUNT(id) FROM mytable;
/* Affected rows: 0 已找到记录: 1 警告: 0 持续时间 1 query: 14.750 sec. */
查询某一行数据,基于主键Id查询,耗时1秒不到;
SELECT * FROM mytable WHERE id = 7351158;
/* Affected rows: 0 已找到记录: 1 警告: 0 持续时间 1 query: 0.031 sec. */
但是同样是上面 一行数据 如果查询UUID这个种字符型数据且未设置索引的情况下,则需要耗时较长时间;
SELECT * FROM mytable WHERE xuuid = '0e670e7a-427e-11e6-beb1-286ed48926ad';
/* Affected rows: 0 已找到记录: 1 警告: 0 持续时间 1 query: 15.563 sec. */
现在我们在 xuuid上添加一条索引;
CREATE NONCLUSTERED INDEX mytable_index_xuuid ON mytable(xuuid);
好吧 接下来见证奇迹的时候到了,我们一起来看一下,加完索引之后有什么神奇的变化:
SELECT * FROM mytable WHERE xuuid = '0e670e7a-427e-11e6-beb1-286ed48926ad';
/* Affected rows: 0 已找到记录: 1 警告: 0 持续时间 1 query: 0.046 sec. */
看清楚了, 是0.046秒,换句话说,是46毫秒;
这样来说,数据查询优化的空间可是相当大有可为的,童鞋们学好索引的正确打开方式,对以后加快查询方式会有很大的帮助哦....
最后引用别人[姜敏(http://www.cnblogs.com/aspnet2008/)]曾经总结过的几句话来描述一下索引的使用原则:
总结索引使用原则:
1:不要索引数据量不大的表,对于小表来讲,表扫描的成本并不高。
2:不要设置过多的索引,在没有聚集索引的表中,最大可以设置249个非聚集索引,过多的索引首先会带来更大的磁盘空间,而且在数据发生修改时,对索引的维护是特别消耗性能的。
3:合理应用复合索引,有某些情况下可以考虑创建包含所有输出列的覆盖索引。
4:对经常使用范围查询的字段,可能考虑聚集索引。
5:避免对不常用的列,逻辑性列,大字段列创建索引。
posted on 2019-02-15 19:33 ExplorerMan 阅读(3476) 评论(0) 编辑 收藏 举报