关闭

SQL优化之索引

标签: sql优化索引
129人阅读 评论(0) 收藏 举报
分类:

举例说明:
假设有一个图书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 ;

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:11198次
    • 积分:246
    • 等级:
    • 排名:千里之外
    • 原创:10篇
    • 转载:14篇
    • 译文:0篇
    • 评论:1条