Indexes in databases. Types of indexes.
.png?locale=en)
Indexes in databases are used to speed up search and data retrieval. They work similarly to pointers in a book, allowing quick access to the required records without scanning the entire table. Main purposes of using indexes:
1. Search acceleration. Indexes allow fast retrieval of rows that match specific query conditions (e.g., `WHERE`).
2. Sorting and grouping optimization. If a query includes `ORDER BY` or `GROUP BY` operations, indexes help speed up execution.
3. Join acceleration (JOIN). When joining tables, indexes help quickly find matching rows in related tables.
4. Ensuring uniqueness. Unique indexes guarantee that there are no duplicate values in a column or a group of columns.
5. Performance improvement. Indexes reduce the number of input/output (I/O) operations, which is especially important for large tables.
However, indexes also have drawbacks:
- Storage overhead. Indexes occupy additional disk space.
- Update overhead. When inserting, updating, or deleting data, indexes must be updated, which can slow down these operations.
Therefore, indexes should be created with consideration of specific queries and database load to achieve an optimal balance between performance and maintenance costs.
Types of Indexes.
There are several types of indexes in databases, each designed for specific use cases. Main types of indexes:
1. Single-column indexes
- Created on a single column of a table.
- Suitable for accelerating searches based on one column.
Example: `CREATE INDEX idx_name ON users (name);`
2. Composite indexes
- Created on multiple columns.
- Useful for queries that filter or sort by multiple columns.
- Column order in the index matters: queries should use the index prefix (e.g., if an index is on `(col1, col2)`, queries filtering only by `col1` will use the index, while those filtering only by `col2` will not).
Example: `CREATE INDEX idx_name_age ON users (name, age);`
3. Unique indexes
- Ensure that all values in the indexed column (or group of columns) are unique.
- Used to maintain data integrity.
Example: `CREATE UNIQUE INDEX idx_email ON users (email);`
4. Full-text indexes
- Designed for searching text data (e.g., keywords or phrases).
- Support complex search queries, such as searching by synonyms or morphology.
Example: `CREATE FULLTEXT INDEX idx_content ON articles (content);`
5. Spatial indexes
- Used for working with geospatial data (e.g., coordinates, geometry).
- Optimize searches for geographic objects.
Example: `CREATE SPATIAL INDEX idx_location ON places (coordinates);`
6. Hash Indexes
- Use hash functions for fast lookups based on exact matches.
- Efficient for `=` operations but not suitable for range queries (e.g., `BETWEEN`, `>`).
Example: `CREATE INDEX idx_hash ON users USING HASH (email);`
7. B-tree Indexes
- The most common type of indexes.
- Support range queries (e.g., `>`, `<`, `BETWEEN`) as well as exact matches.
Example: `CREATE INDEX idx_btree ON users (age);`
8. Bitmap Indexes
- Used for columns with a small number of unique values (e.g., gender, status).
- Efficient for `AND`, `OR`, and `NOT` operations.
Example: `CREATE BITMAP INDEX idx_status ON orders (status);`
9. Clustered Indexes
- Define the physical order of data in the table.
- A table can have only one clustered index.
Example: In MySQL, the primary key (`PRIMARY KEY`) automatically creates a clustered index.
10. Non-clustered Indexes
- Create a separate data structure that references rows in the table.
- A table can have multiple non-clustered indexes.
Example:`CREATE INDEX idx_nonclustered ON users (name);`
11. Partial Indexes
- Index only a subset of data that meets a specific condition.
- Useful for large tables where indexing all rows is inefficient.
Example: `CREATE INDEX idx_active_users ON users (name) WHERE status = 'active';`
12. Function-based Indexes
- Index the result of a function applied to a column.
- Useful for queries that filter or sort data based on function results.
Example: `CREATE INDEX idx_lower_name ON users (LOWER(name));`
13. Covering Indexes
- Contain all the data needed to execute a query, avoiding access to the table itself.
Example: `CREATE INDEX idx_covering ON users (id, name, age);`(if the query selects only these columns).
14. Expression Indexes
- Similar to function-based indexes but can include more complex expressions.
Example: `CREATE INDEX idx_expr ON orders (total_price * quantity);`
15. Indexes for Arrays (GIN, GiST, BRIN)
- Used in PostgreSQL for working with arrays, JSON, full-text search, and other complex data types.
Example: `CREATE INDEX idx_gin ON documents USING GIN (tags);`
Choosing the right index type depends on data structure, query types, and performance requirements. Incorrect use of indexes can degrade performance, so it's important to analyze queries and test their impact on the database.
Назад