国内最全IT社区平台 联系我们 | 收藏本站
阿里云优惠2流量王
您当前位置:首页 > 数据库 > 数据库应用 > SQL优化之索引

SQL优化之索引

来源:程序员人生   发布时间:2016-06-24 13:30:00 阅读次数:912次

SQL优化有很多方法,今天来讲1说数据库索引。

举例说明:
假定有1个图书Book表,里面有字段id,name, isbn等。如果图书数量巨大的话,我们通过isbn查询通常是比较慢的。
这里写图片描述

添加索引:
create index index_isbn ON book (isbn);

再次履行查询:
这里写图片描述

查询时间从0.134缩短到0.001,效果还是很明显的。

接下来通过1个故事来讲明1下,索引是甚么?

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

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

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

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

确保每一个表都有主键
这样可以确保每一个表都有聚集索引(表在磁盘上的物理存储是依照主键顺序排列的),使用主键检索表中的数据,或在主键字段上进行排序,或在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 ;

生活不易,码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
程序员人生
------分隔线----------------------------
分享到:
------分隔线----------------------------
关闭
程序员人生