SQL优化之索引

转载 2016年05月30日 11:09:01

举例说明:
假设有一个图书Book表,里面有字段id,name, isbn等。如果图书数量巨大的话,我们通过isbn查询通常是比较慢的。
这里写图片描述
这里写图片描述
添加索引:
create index index_isbn ON book (isbn);

再次执行查询:
这里写图片描述
这里写图片描述
查询时间从0.134缩短到0.001,效果还是很明显的。

接下来通过一个故事来说明一下,索引是什么?

很久以前,在一个古城的的大图书馆中珍藏有成千上万本书籍,但书架上的书没有按任何顺序摆放,因此每当有人询问某本书时,图书管理员只有挨个寻找,每一次都要花费大量的时间。[这就好比数据表没有主键一样,搜索表中的数据时,数据库引擎必须进行全表扫描,效率极其低下。]
更糟的是图书馆的图书越来越多,图书管理员的工作变得异常痛苦,有一天来了一个聪明的小伙子,他看到图书管理员的痛苦工作后,想出了一个办法,他建议将每本书都编上号,然后按编号放到书架上,如果有人指定了图书编号,那么图书管理员很快就可以找到它的位置了。
[给图书编号就象给表创建主键一样,创建主键时,会创建聚集索引树,表中的所有行会在文件系统上根据主键值进行物理排序,当查询表中任一行时,数据库首先使用聚集索引树找到对应的数据页(就象首先找到书架一样),然后在数据页中根据主键键值找到目标行(就象找到书架上的书一样)。]
于是图书管理员开始给图书编号,然后根据编号将书放到书架上,为此他花了整整一天时间,但最后经过测试,他发现找书的效率大大提高了。[在一个表上只能创建一个聚集索引,就象书只能按一种规则摆放一样。]

  
但问题并未完全解决,因为很多人记不住书的编号,只记得书的名字,图书管理员无赖又只有扫描所有的图书编号挨个寻找,但这次他只花了20分钟,以前未给图书编号时要花2-3小时,但与根据图书编号查找图书相比,时间还是太长了,因此他向那个聪明的小伙子求助。
[这就好像你给Product表增加了主键ProductID,但除此之外没有建立其它索引,当使用Product Name进行检索时,数据库引擎又只要进行全表扫描,逐个寻找了。]
聪明的小伙告诉图书管理员,之前已经创建好了图书编号,现在只需要再创建一个索引或目录,将图书名称和对应的编号一起存储起来,但这一次是按图书名称进行排序,如果有人想找“Database Management System”一书,你只需要跳到“D”开头的目录,然后按照编号就可以找到图书了。
于是图书管理员兴奋地花了几个小时创建了一个“图书名称”目录,经过测试,现在找一本书的时间缩短到1分钟了(其中30秒用于从“图书名称”目录中查找编号,另外根据编号查找图书用了30秒)。

图书管理员开始了新的思考,读者可能还会根据图书的其它属性来找书,如作者,于是他用同样的办法为作者也创建了目录,现在可以根据图书编号,书名和作者在1分钟内查找任何图书了,图书管理员的工作变得轻松了,故事也到此结束。

通过这个故事很容易理解索引的真正含义。假设我们有一个Products表,创建了一个聚集索引(根据表的主键自动创建的),我们还需要在ProductName列上创建一个非聚集索引,创建非聚集索引时,数据库引擎会为非聚集索引自动创建一个索引树(就象故事中的“图书名称”目录一样),产品名称会存储在索引页中,每个索引页包括一定范围的产品名称和它们对应的主键键值,当使用产品名称进行检索时,数据库引擎首先会根据产品名称查找非聚集索引树查出主键键值,然后使用主键键值查找聚集索引树找到最终的产品。

确保每个表都有主键
这样可以确保每个表都有聚集索引(表在磁盘上的物理存储是按照主键顺序排列的),使用主键检索表中的数据,或在主键字段上进行排序,或在where子句中指定任意范围的主键键值时,其速度都是非常快的。
在下面这些列上创建非聚集索引:
1)搜索时经常使用到的;
2)用于连接其它表的;
3)用于外键字段的;
4)高选中性的;
5)ORDER BY子句使用到的;

增加普通索引和UNIQUE两种索引。其格式如下:
create index index_name on table_name (column_list) ;
create unique index index_name on table_name (column_list) ;
删除索引
drop index index_name on table_name ;
alter table table_name drop index index_name ;

相关文章推荐

索引和sql语句优化

  • 2012年01月04日 09:57
  • 62KB
  • 下载

索引优化.sql

  • 2011年12月28日 10:49
  • 1KB
  • 下载

Sql Server查询性能优化之创建合理的索引(上篇)

原文链接:http://www.cnblogs.com/lzrabbit/archive/2012/06/11/2517963.html 本文主要作为优化查询性能的一些知识储备,感觉知识点有...

高效SQL优化及战略索引设计技术

  • 2016年07月27日 11:07
  • 6.51MB
  • 下载

SQL优化-索引

  • 2014年08月18日 14:05
  • 114KB
  • 下载

Sql Server查询性能优化之创建合理的索引(下篇)

原文链接:http://www.cnblogs.com/lzrabbit/archive/2012/07/03/2549558.html 数据库索引分为聚集索引和非聚集索引,聚集索引就是物理索引,也...

SQL优化-索引

  • 2016年01月27日 11:00
  • 452KB
  • 下载

优化SQL Server索引的小技巧

  • 2010年01月08日 09:11
  • 29KB
  • 下载

Oracle 建立索引及SQL优化

Oracle 建立索引及SQL优化 数据库索引: 索引有单列索引 复合索引之说 如何某表的某个字段有主键约束和唯一性约束,则Oracle 则会自动在相应的约束列上建议唯一索引。数据库索...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL优化之索引
举报原因:
原因补充:

(最多只允许输入30个字)