[Spring] The importance of index in DB queries

1. Concept and Characteristics of Indexes

Concept

• An index is a tool that improves query performance.
• Without an index, retrieving specific data requires checking every single row in the table. This is called a Full Table Scan.
• With an index, lookups are extremely fast because a reference table is used. This is called an Index Scan.

Limitations

• Since the reference table is managed in a separate space from the original table, additional storage space is required.
• Due to the tree structure, insert and update operations take significantly longer compared to Full Table Scan.

---

2. How Indexes Work

• Indexes are created on a per-column basis.
• Columns with an index can utilize Index Scan, while columns without one cannot.

---

3. Types of Index Scan

Various Index Scan methods exist.

 

Method	Description
Unique Scan	Finds a single match. The fastest method.
Range Scan	Retrieves data consecutively within a specified range or interval.
(Full) Index Scan	Finds the desired data by scanning the entire table or index. → Needs resolution
ICP	An optimization technique that evaluates WHERE clause conditions at the storage engine level rather than the MySQL server level. This reduces the number of rows unnecessarily passed to the server, decreasing I/O.

Optimizer and Explain

• The most effective scan method varies depending on the query. The feature that automatically determines this is called the Optimizer.
  • The tool that predicts in advance which scan method the Optimizer will choose is the Explain command.
  • Explain outputs a table as its result, and the part corresponding to the type column indicates the index scan method.

 

Method	Display in type column
Unique Scan	const
Range Scan	range
Index Scan	index or all
ICP	One of the above. Whether ICP is applied can be confirmed by Using index condition appearing in the Extra column.

---

4. Index Storage and Management

• Indexes are stored in a separate data structure called a B-tree.
• Due to the nature of this data structure, data insertion and modification take longer compared to Full Table Scan.
• A B-tree is a data structure where values smaller than the middle value are connected to the left, and values larger are connected to the right, in a branching structure.
• Since the entire tree structure changes every time data is added, insertion and modification operations take longer.
  • B-tree restructuring is handled automatically by the database engine itself, regardless of which framework is being used.