数据库优化之索引.

数据库优化—索引山东微图软件信息科技有限公司目录1索引的概念2索引的优缺点3索引分类4索引建立原则5注意事项1.索引概念1、索引的概念索引就是加快检索表中数据的方法，索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据。索引是针对一个表而建立的，它是由处存放表的数据页面以外的索引页面组成的。每个索引页面中的行都含有逻辑（指向表中的行），在数据查询时，系统先在索引中寻找匹配的索引值，再按照索引值的对应关系定位表中的位置，从而加速检索物理数据。2.索引的优缺点2.1、索引的特点1.索引可以加快数据库的检索速度2.索引降低了数据库插入、修改、删除等维护任务的速度3.索引既可以直接创建，也可以间接创建4.可以在优化隐藏中，使用索引5.使用查询处理器执行SQL语句，在一个表上，一次只能使用一个索引6.其他2.1、索引的优点1.创建唯一性索引，保证数据库表中每一行数据的唯一性2.大大加快数据的检索速度，这也是创建索引的最主要的原因3.加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。4.通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。2.1、索引的缺点1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度3.索引分类3.1索引分类•索引是在数据库表或者视图上创建的对象，目的是为了加快对表或视图的查询的速度。•按照存储方式分为：聚集索引与非聚集索引按照维护与管理索引角度分为：唯一索引、复合索引和系统自动创建的索引。•索引的结构是由：根节点---非叶节点---非叶节点---叶节点3.2聚集索引聚集索引:表中存储的数据按照索引的顺序存储,检索效率比普通索引高,但对数据新增/修改/删除的影响比较大。逻辑顺序决定了表中相应行的物理顺序。注意事项：(1)每张表只能有一个聚集索引(2)聚集索引是表的物理顺序(3)定义聚集索引时使用的列越少越好，主键是聚集索引的良好候选者(4)数据会根据索引键的顺序重新排列数据创建聚集索引的语法：createCLUSTEREDINDEXindex_mpIDONEMP(empID)3.3非聚集索引非聚集索引:不影响表中的数据存储顺序,检索效率比聚集索引低,对数据新增/修改/删除的影响很少，是通过二叉树的数据结构来描述的逻辑顺序。特点：(1)非聚集索引实际上是一个表的逻辑顺序(2)创建索引时，缺省为非聚集索引(3)返回精确匹配的搜索条件(where)中经常使用的列可考虑建立非聚聚索引(4)应在链接和分组操作中使用的列上创建非聚集索引(5)一个表可以最多可以创建249个非聚集索引创建非聚集索引的语法：createNONCLUSTEREDINDEXindxe_mpIDonemp(empID)3.4唯一性索引唯一性索引保证在索引列中的全部数据是唯一的，不会包含冗余数据。如果表中已经有一个主键约束或者唯一性键约束，那么当创建表或者修改表时，SQLServer自动创建一个唯一性索引。然而，如果必须保证唯一性，那么应该创建主键约束或者唯一性键约束，而不是创建一个唯一性索引。当创建唯一性索引时，应该认真考虑这些规则：当在表中创建主键约束或者唯一性键约束时，SQLServer自动创建一个唯一性索引；如果表中已经包含有数据，那么当创建索引时，SQLServer检查表中已有数据的冗余性；每当使用插入语句插入数据或者使用修改语句修改数据时，SQLServer检查数据的冗余性：如果有冗余值，那么SQLServer取消该语句的执行，并且返回一个错误消息；确保表中的每一行数据都有一个唯一值，这样可以确保每一个实体都可以唯一确认；只能在可以保证实体完整性的列上创建唯一性索引，例如，不能在人事表中的姓名列上创建唯一性索引，因为人们可以有相同的姓名。3.5复合索引复合索引就是一个索引创建在两个列或者多个列上。在搜索时，当两个或者多个列作为一个关键值时，最好在这些列上创建复合索引。当创建复合索引时，应该考虑这些规则：最多可以把16个列合并成一个单独的复合索引，构成复合索引的列的总长度不能超过900字节，也就是说复合列的长度不能太长；在复合索引中，所有的列必须来自同一个表中，不能跨表建立复合列；在复合索引中，列的排列顺序是非常重要的，因此要认真排列列的顺序，原则上，应该首先定义最唯一的列，例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同；为了使查询优化器使用复合索引，查询语句中的WHERE子句必须参考复合索引中第一个列；当表中有多个关键列时，复合索引是非常有用的；使用复合索引可以提高查询性能，减少在一个表中所创建的索引数量。3.5.1如何使用复合索引1、复合索引使用的目的是什么？:能形成索引覆盖,提高where语句的查询效率2、一个复合索引是否可以代替多个单一索引？:复合索引的使用原则是第一个条件应该是复合索引的第一列,依次类推,否则复合索引不会被使用所以,正常情况下复合索引不能替代多个单一索引3、在进行哪些类型的查询时，使用复合索引会比较有效？:如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引,形成索引覆盖可以提高查询的效率4、符合索引中索引列的排序原则是什么？:复合索引的使用原则是第一个条件应该是复合索引的第一列,依次类推5、什么情况下不适合使用复合索引？:建立索引的目的就是帮助查询,如果查寻用不到则索引就没有必要建立,另外如果数据表过大(5w以上)则有些字段(字符型长度超过(40))不适合作为索引,另外如果表是经常需要更新的也不适合做索引4.索引建立原则什么情况下设置索引？动作描述使用聚集索引使用非聚集索引外键列应应主键列应应列经常被分组排序(orderby)应应返回某范围内的数据应不应小数目的不同值应不应大数目的不同值不应应频繁更新的列不应应频繁修改索引列不应应一个或极少不同值不应不应4.1建立索引的原则：1)定义主键的数据列一定要建立索引。2)定义有外键的数据列一定要建立索引。3)对于经常查询的数据列最好建立索引。4)对于需要在指定范围内的快速或频繁查询的数据列;5)经常用在WHERE子句中的数据列。6)经常出现在关键字orderby、groupby、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。7)对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。8)对于定义为text、image和bit的数据类型的列不要建立索引。9)对于经常存取的列避免建立索引9)限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。10)对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。PPT模板下载：行业PPT模板：节日PPT模板：素材下载：背景图片：图表下载：优秀PPT下载：教程：教程：教程：资料下载：课件下载：范文下载：试卷下载：教案下载：要考虑创建索引要考虑创建索引定义主键和外键的列在集合过程中需要快速或频繁组合到一起的列需要在指定范围内快速查询的列需要按照顺序快速和频繁检索的列PPT模板下载：行业PPT模板：节日PPT模板：素材下载：背景图片：图表下载：优秀PPT下载：教程：教程：教程：资料下载：课件下载：范文下载：试卷下载：教案下载：不考虑创建索引不考虑创建索引查询中几乎不涉及到的列只有较少行数列没必要建立索引有text，ntext或image数据类型的列很少有唯一值的列例如：性别列注意事项注意事项1.不要把社会保障号码(SSN)或身份证号码(ID)选作键永远都不要使用SSN或ID作为数据库的键。除了隐私原因以外，SSN或ID需要手工输入。永远不要使用手工输入的键作为主键，因为一旦你输入错误，你唯一能做的就是删除整个记录然后从头开始。2.通常的情况下不要选择用户可编辑的字段作为键3.使用系统生成的主键假如你总是在设计数据库的时候采用系统生成的键作为主键，那么你实际控制了数据库的索引完整性。这样，数据库和非人工机制就有效地控制了对存储数据中每一行的访问。采用系统生成键作为主键还有一个优点：当你拥有一致的键结构时，找到逻辑缺陷很容易。4.经常有插入和删除操作的不要建立索引。对这些插入和删除操作的索引维护可能比扫描表空间消耗更多的时间。注意事项5.优化sql语句举例：如果tID是一个聚合索引，下列哪条语句更快：1.select*fromtable1wherename='zhangsan'andtID100002.select*fromtable1wheretID10000andname='zhangsan'答案：1.先从全表中查找看有几个name='zhangsan'的，而后再根据限制条件条件tID10000来提出查询结果2.仅仅从表的10000条以后的记录中查找就行了注意事项SARG(SearchableArguments操作，用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。Like语句是否属于SARG取决于所使用的通配符的类型如：namelike‘张%’，这就属于SARG而：namelike‘%张’,就不属于SARG。原因是通配符%在字符串的开通使得索引无法使用。or会引起全表扫描如：Name=’张三’and价格5000符号SARG，而：Name=’张三’or价格5000则不符合SARG。使用or会引起全表扫描。谢谢观看！