Database Indexing

Introduction

Database indexing increases the efficiency of data retrieval, much like the table of contents in a book.

Common Indexing Models

• Hash Table:

  • A structure that stores data in a key-value format.
  • Suitable for scenarios that require only equality searches.

• Ordered Array:

  • Outstanding for both equality and range queries.
  • Inefficient for data updates.
  • Ideal for static storage, like storing a city’s population data for a specific year.

• Search Tree:

  • InnoDB tables are stored in index form based on the primary key order. This is known as index-organized tables.
  • InnoDB uses the B+ tree index model, so data is stored in B+ trees.

Types of Indexes

1. Primary Key Index (Clustered Index):

   • The leaf nodes store the entire row of data.

2. Secondary Index:

   • The leaf nodes contain the values of the primary key.
   • For InnoDB, non-primary indexes are also called secondary indexes.

Insights

• Queries using the primary key index only need to search the primary key’s B+ tree.
• Queries using the secondary index need to first search the secondary index tree to get the primary key’s value, then search the primary key’s B+ tree. This process is called “backtable”.

Index Maintenance

• B+ trees need maintenance to preserve their order.
• Inserting a new value might trigger a “page split” if the data page is full.
• Using an auto-increment primary key is often the most logical choice, both from a performance and storage perspective.

Additional Tips

• Use “covering indexes” to reduce the number of tree searches, which improves query performance.
• Index pushdown optimization, introduced in MySQL 5.6, filters records during index traversal to reduce the number of backtables.

$ hexo new "My New Post"

More info: Writing

Run server

$ hexo server

More info: Server

Generate static files

$ hexo generate

More info: Generating

Deploy to remote sites

$ hexo deploy

More info: Deployment