[转]数据库索引
数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
索引是对数据库表中一个或多个列（例如，employee 表的姓氏 (lname) 列）的值进行排序的结构。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。
例如这样一个查询：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，在索引中查找，但索引是经过某种算法优化过的，查找次数要少的多的多。可见，索引是用来定位的。
编辑本段索引的优缺点
概述
建立索引的目的是加快对表中记录的查找或排序。
详述
创建索引可以大大提高系统的性能。第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，增加索引也有许多不利的方面。第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
在经常需要搜索的列上，可以加快搜索的速度；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改操作远远多于检索操作时，不应该创建索引。
编辑本段索引列
确定索引的有效性：
检查查询的WHERE和JOIN子句。在任一子句中包括的每一列都是索引可以选择的对象。
对新索引进行试验以检查它对运行查询性能的影响。
检查某列中唯一数据值的数量，并将该数量与表中的行数进行比较。比较的结果就是该列的可选择性，这有助于确定该列是否适合建立索引，如果适合，确定索引的类型。
编辑本段类型
唯一索引 唯一索引是不允许其中任何两行具有相同索引值的索引。
当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。
主键索引
数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。
编辑本段创建索引
CREATE TABLE mytable(
idserial primary key,
category_id int not null default0,
user_id int not null default0,
adddate int not null default0
);
如果在查询时常用类似以下的语句：
SELECT * FROM mytable WHERE category_id=1;
最直接的应对之道，是为category_id建立一个简单的索引：
CREATE INDEX mytable_categoryid ON mytable (category_id);
OK.如果有不止一个选择条件呢？例如：
SELECT * FROM mytable WHERE category_id=1 AND user_id=2;
第一反应可能是，再给user_id建立一个索引。不好，这不是一个最佳的方法。可以建立多重的索引。
CREATE INDEX mytable_categoryid_userid ON mytable(category_id,user_id);
注意到在命名时的习惯了吗？使用"表名_字段1名_字段2名"的方式。很快就会知道为什么这样做了。
EXPLAIN
SELECT * FROM mytable
WHERE category_id=1 AND user_id=2;
This is what Postgres 7.1 returns (exactlyasI expected)
NOTICE:QUERY PLAN:
Index Scan using mytable_categoryid_userid on
mytable(cost=0.00..2.02 rows=1 width=16)
EXPLAIN
接着，来个稍微复杂一点的，如果有个ORDERBY 子句呢？不管你信不信，大多数的数据库在使用orderby的时候，都将会从索引中受益。
SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY adddate DESC;
CREATE INDEX mytable_categoryid_userid_adddate ON mytable (category_id,user_id,adddate);
注意:"mytable_categoryid_userid_adddate"将会被截短为"mytable_categoryid_userid_addda"
CREATE
EXPLAIN SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY adddate DESC;
NOTICE:QUERY PLAN:
Sort(cost=2.03..2.03 rows=1 width=16)
->Index Scanusing mytable_categoryid_userid_addda
on mytable(cost=0.00..2.02 rows=1 width=16)
EXPLAIN
看看EXPLAIN的输出，数据库多做了一个没有要求的排序，这下知道性能如何受损了吧，看来对于数据库的自身运作是有点过于乐观了，那么，给数据库多一点提示吧。
为了跳过排序这一步，并不需要其它另外的索引，只要将查询语句稍微改一下。这里用的是postgres，将给该数据库一个额外的提示--在ORDER BY语句中，加入where语句中的字段。这只是一个技术上的处理，并不是必须的，因为实际上在另外两个字段上，并不会有任何的排序操作，不过如果加入，postgres将会知道哪些是它应该做的。
EXPLAIN SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY category_id DESC,user_id DESC,adddate DESC;
NOTICE:QUERY PLAN:
Index Scan Backward using
mytable_categoryid_userid_addda on mytable(cost=0.00..2.02 rows=1 width=16)
EXPLAIN
现在使用料想的索引了，而且它还挺聪明，知道可以从索引后面开始读，从而避免了任何的排序。
以上说得细了一点，不过如果数据库非常巨大，并且每日的页面请求达上百万算，想会获益良多的。不过，如果要做更为复杂的查询呢，例如将多张表结合起来查询，特别是where限制字句中的字段是来自不止一个表格时，应该怎样处理呢？通常都尽量避免这种做法，因为这样数据库要将各个表中的东西都结合起来，然后再排除那些不合适的行，搞不好开销会很大。
如果不能避免，应该查看每张要结合起来的表，并且使用以上的策略来建立索引，然后再用EXPLAIN命令验证一下是否使用了料想中的索引。如果是的话，就OK。不是的话，可能要建立临时的表来将他们结合在一起，并且使用适当的索引。
要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。
