[ByteByteGo Daily] Database Indexing Strategy

[reboottime]

Overview

This a note of reading the Database Indexing Strategies by Alex Xu, which provides an in-depth look at index architecture and discusses best practices to design effective indexes to meet the needs of our application.

Why indexing strategy matters:

• Effect of poorly designed indexes and lack of indexes: are primary sources of database applications bottlenecks
• As database grows in size, finding efficient ways to retrieve and manipulate data becomes increasingly important.

[reboottime]

Basic Of Indexing

An index in database is much like the index of a book, is a data structure that speeds up data retrieval operations.

The structure of a database index includes an ordered list of values, which each value connected to points leading to data pages where these values reside.

• The structure of database index includes an ordered list of values, with each value connected to pointers leading to data pages where these values reside.
• Indexes are typically stored on disk. Keys are made from one or more columns in the table make up the index, which most relational databases. are stored in a B+ tree structure
• Finding the right indexes for a database is a balancing act between quick query responses and updates costs
  • Narrow Indexes, save on disk space and maintenance
  • wide indexes cater to a broader range of queries.
  • Often, it requires several iterations of designs to find the most efficient index.

[reboottime]

Primer on B+ Tree

[reboottime]

Clustered Index

[reboottime]

Non-clustered Index

[reboottime]

Understanding Index Types

Primary Index