设计、创建和维护索引(index)（上）
1:索引的基本概念:
2:索引的设计准则:
3:索引的类型:
4:聚集索引和非聚集索引:
5:唯一索引和非唯一索引:
A:索引的基本概念
索引在关系型数据库中扮演着极其重要的角色,它最主要的作用是:
1:确保数据的唯一性:2:提高数据查询速度
提高数据的查询速度
为什么可以提高数据的访问速度:索引可以把表中的数据进行排序.
举例:创建一个work表最好有80万行.执行:select * from work where 年龄<50
1:[不建索引]观察所用时间
2:[创建一个索引]观察所用时间
1:表扫描:一行一行把表的数据全部扫描到.[tabscan]
2:使用索引:把表的数据进行排序,从而可以提高数据的查询效率
3:索引的结构:索引页,数据页
举列说明:
A:select * from work where 职工号=\'001\'.如果事先能够为职工号建立索引.则可以提高查询速度
B:select * from work where 基本工资>1200 and 基本工资<3000.如果事先能够为基本工资创建索引则可以提高查询效率
C:select * from work order by 基本工资.若能够事先为基本工资创建索引也能够提高查询效率
sql server在什么时候要使用索引:
不要以为只要为某个字段创建索引之后,sql server在查询的时候就一定要使用它.事实上sql server在执行查询之前会通过一个叫“查询优化器”的对像来判断是采用表扫描,还是使用索引,如果利用索引不能使查询得到优化,则不会使用索引,而改用表扫描.
确保数据的唯一性:
索引中有一类索引叫唯一索引(unique),利用它可以保证以该字段为索引的值不会出现重复但它可以为空.与主键有区别,当然如果你创建的索引不要求保持数据唯一性,只是为了提高查询效率你完全可以不建唯一索引,建一个一般的索引就行了.当然确保数据唯一性的方法有很多,比如可以创立主键等.索引只是确保数据唯一性的其中一种方法.
B:设计索引的准则
1:经常被用来查询数据记录的字段.
说明:经常被用来查询数据记录的字段最好是识别度较高的字段(也就是数据内容重复性低).
比如:使用员工编号或身份证号来作索引是非常恰当的.但如果使用家庭住址或使用基本工资来寻找员工就显然不恰当.家庭住址一般有很多字符,会使索引过于庞大,基本工资识别性太低.
2:主键所定义的字段一般应作索引,不过sql server自动帮我们作好了
3:在查询中用来连接表的字段:
4:经常用作排序的字段:
说明:不能为bit,text,image等数据类型的字段创建索引.
索引的负面影响:
那既然索引可以加快查询效率,那我们就把索引建多,越多越好,这一种说法完全错误.
理由:
1:索引显然会占用磁盘空间,创建不必要的索引只会浪费空间[任务面板中查看]
一个索引的大小绝对大于以该字段作为索引的大小乘以记录条数.
2:以某个字段创建一个索引之后,当以后修改或添加该字段时,为了让索引能够实时反映表的动态更新必须有相关的索引.[索引页]
所以得到一个结论:如果一个表是作为引用性的,也就是说它仅仅只作为查询,或者说它的改动非常少,多几个索引倒也无所谓,但是一个经常性修改的表则请谨慎的创建必须的索引[举列说明]
A:在work中执行update work set 年龄=年龄+1 where 年龄=20[观察使用时间]
B:在work中创建一个索引以年龄为索引,再执行同样的语句[观察使用的时间]
3:数据记录越多,索引提高数据访问效率越明显,反过来如果表内含有数据记录很少,建个索引是不会有任何作用的,所以不主张对一个记录很少的表创建索引
C:索引的类型
1:按存储结构区分:聚集索引(clustered index),非聚集索引(nonclustered index)
2:按数据唯一性分:唯一索引(unique index)和普通索引
3:以键列的个数分:单列索引与多列索引.
D:聚集索引和非聚集索引
聚集索引:数据页内容排列的次序与索引页的索引字段的值相同.即:聚集索引会改变表原先的排列顺序使之按照索引升序或降序排列.
正由于聚集索引会使得值相同的数据记录排列在一起,因此要搜索介于某范围的数据值时将会特别有效.因为一但使用聚集索引找到第一条符合条件的记录,则同范围之后的记录保证是相邻排列的,也就是说把这一范围找到之后就不会往下面找了.
怎么使用聚集索引
一般而言下列的数据访问操作非常适合使用聚集索引:
1:如果某个字段所包含的有差别的数据较少,则非常适合以该字段创建聚集索引.比如:员工表中部门字段用来存放员工所在的部门.但一个单位的部门不会超过十个,则以部门创建聚集索引最好.[为什么？]
2:使用betwwen,>,>=,<=,<等运算符查询[注意不是等于]
3:会返回大结果集的查询 至少10万以上
说明:
1:每一个表最多只能有一个聚集索引
2:聚集索引可以是一个唯一索引或普通索引
3:聚集索引的大小平均是表大小的5%,在创建聚集索引期间,它需要使用数据库的一些磁盘空间来存放编制索引过程中的一些临时数据,所需的临时空间大约是表大小的1.2倍,这些临时数据会在索引创建完成后释放出来.
4:如果某一个字段的内容经常变动则非常不适合给这个字段创建聚集索引.因为聚集索引要求表的记录按照索引列排序,所以如果一个字段的变动将会导致整个表的记录的要重新排列一次
非聚集索引:
与聚集索引不同的是,非聚集索引尽管可以按照升序或降序排列,但它丝毫不影响表中数据的实际的存放顺序[注意:索引的排序与表的实际数据的排序的区别]
所以非聚集索引并不会使表的记录排序[是不是就达不到提高数据搜索的效果呢？思考]
另外每一个表中可以拥有多个非聚集索引,正是由于一个表能拥有多个非聚集索引,所以如果你不需要用多种方式搜索数据记录时,非聚集索引便显得特别好用.比如你想先对年龄进行搜索而后又对基本工资进行搜索则我们可以分别为年龄和基本工资创建一个非聚集索引
怎么使用非聚集索引
一般而言下列情况非常适合使用非聚集索引:
1:如果某字段包含的有差别数据非常多,则非常适合为该字段创建非聚集索引.比如:一个客户表的姓名而言,虽然也有相同的姓名,但是大多数的客户的姓名都不一样,所以就要以姓名作为非聚集索引
2:所返回的结果量是不大的查询
3:使用where子句的字段,并要求该字段等于特殊的某个值.比如:where 职称=\'经理\'
4:每一个表最多能够拥有249个非聚集索引
5:非聚集索引可以是唯一索引和普通索引
E:唯一索引和非唯一索引
唯一索引
如果在创建索引的时候加上unique关键字就变成的唯一索引,唯一索引的特点:
1:唯一索引可以确保索引列不包含重复的值.在多列唯一索引的情况下,该索引可以确保索引列中每个值组合都是唯一的.
2:聚集索引和非聚集索引都可以是唯一的.因此,只要列中的数据是唯一的,就可以在同一个表上创建一个唯一的聚集索引和多个唯一的非聚集索引.
3:唯一索引不仅可以保证数据唯一性还可以提高查询效率[用执行计划的io成本来察看]
思考:如果你创建唯一索引的目的仅仅是为了保证数据唯一性,没有必要创建索引,但如果你创建唯一索引的目的是既要保持数据唯一性又要提高查询效率,想一想应该创建聚集的还是非聚集的.
4:当你为一个表创建一个主键时,sql server会自动帮你创建一个唯一索引
5:唯一索引与主键都能够确保数据唯一性,但是它与主键有区别[思考有什么区别]
思考:有work表有职工号,姓名,学历.假如本公司没有一个人同名,应该怎么做:
方法1:创建一个由列职工号和姓名组成的主键.
方法2:由创建一个主键职工号,再创建一个唯一索引,其索引列为:姓名
分析两种方法的结果.
怎么使用唯一索引
1:虽然唯一索引可以保证数据的唯一性,但是如果你只想保证数据唯一性话,则不是必须要设置唯一索引,你可以使用sql server其它方法 比如:主键,约束等.
2:只有你既希望保证数据唯一又想提高查询效率时,才使用唯一索引.
比如:work表中有职工号,姓名,性别,年龄,学历.假定本公司没有同名的人,为了在以后输入姓名的过程中避免出现复制错,而且你经常要用姓名列进行查询,则你可以在姓名列上创建一个唯一索引,这样既保证了数据的完整性又可以提高查询的效率
非唯一索引
如果你在创建索引时没有使用unique关键字,则你创建的索引为非唯一索引,这样该索引的作用仅仅是为了提高查询的效率
