Indexing Rules of Thumb & Index Selection Decisions

Indexing Rules of Thumb :


 

 

 

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

 Index Selection Decisions :


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

 

-- <Physical Database Design >

 

 

posted @   ok_008  阅读(1022)  评论(0编辑  收藏  举报
编辑推荐:
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列:向量存储与相似性搜索在 .NET 中的实现
阅读排行:
· 10年+ .NET Coder 心语 ── 封装的思维:从隐藏、稳定开始理解其本质意义
· 地球OL攻略 —— 某应届生求职总结
· 提示词工程——AI应用必不可少的技术
· Open-Sora 2.0 重磅开源!
· 周边上新:园子的第一款马克杯温暖上架
给我写信
点击右上角即可分享
微信分享提示