欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Indexing Rules of Thumb Index Selection Decisions

程序员文章站 2022-05-15 09:49:16
...

Indexing Rules of Thumb : Index every primary key and most foreign keys in the database. Attributes frequently referenced in SQL WHERE clauses are potentially good candidates for an index. Use a B+tree index for bothequality and range quer

Indexing Rules of Thumb :

  • Index every primary key and most foreign keys in the database.
  • Attributes frequently referenced in SQL WHERE clauses are potentially
    good candidates for an index.
  • Use a B+tree index for both equality and range queries.
  • Choose carefully one clustered index for each table.
  • Avoid or remove redundant indexes.
  • Add indexes only when absolutely necessary.
  • Add or delete index columns for composite indexes to improve performance.
    Do not alter primary key columns.
  • Use attributes for indexes with caution when they are frequently updated.
  • Keep up index maintenance on a regular basis; drop indexes only when
    they are clearly hurting performance.
  • Avoid extremes in index cardinality and value distribution.
  • Covering indexes (index only) are useful, but often overused.
  • Use bitmap indexes for high-volume data, especially in data warehouses.
  • Index Selection Decisions :

  • Does this table require an index or not, and if so which search
    key should I build an index on?
  • When do I need multi-attribute (composite) search keys, and
    which ones should I choose?
  • Should I use a dense or sparse index?
  • When can I use a covering index?
  • Should I create a clustered index?
  • Is an index still preferred when updates are taken into
    account? What are the tradeoffs between queries and updates for each index chosen?
  • How do I know I made the right indexing choice?
  • --

    ,网站空间,网站空间,香港空间