Index or Not Index

适合建索引的情况

* The column is queried frequently.

* A referential integrity constraint exists on the column.

* A UNIQUE key integrity constraint exists on the column.

*  Create an index if you frequently want to retrieve less than 15% of the rows in a large table.

* To improve performance on joins of multiple tables, index columns used for joins.

Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key.

* There is a wide range of values (good for regular indexes).

* There is a small range of values (good for bitmap indexes).

* The column contains many nulls, but queries often select all rows having a value. 

   In this case, use the following phrase:
   WHERE COL_X > -9.99 * power(10,125)
   Using the preceding phrase is preferable to:
   WHERE COL_X IS NOT NULL
   This is because the first uses an index on COL_X (assuming that COL_X is a numeric column).

不适合建索引的情况

* Small tables do not require indexes. 

* There are many nulls in the column and you do not search on the not null values.

参考

Index the Correct Tables and Columns