SQL优化之索引

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 ;