Why B+ Trees are the Preferred Index Strcuture

The B+ tree architecture is superior for database indexing compared to alternatives due to its storage efficiency and search capabilities:

• Red-Black Trees: These are binary structures. In a database context where data is read in blocks (pages), a binary tree becomes too tall, requiring many disk I/O operations to traverse from root to leaf.
• B Trees: While B Trees reduce height by having multiple children, they store both pointers and data records within the non-leaf nodes. Since MySQL pages are limited (typically 16KB), storing actual row data in internal nodes reduces the number of pointers (branching factor) those nodes can hold, resulting in a taller tree than necessary.
• Hash Indexes: These structures are limited to equality checks (=) and cannot handle range queries (>, <, BETWEEN) or sorting efficiently.

B+ trees store data only at the leaf level and maximize the pointer count in internal nodes, keeping the tree short and minimizing disk seeks.

Clustered vs. Secondary Indexes

• Clustered Index: Every table has exactly one clustered index. It defines the physical storage order of the data rows. If a Primary Key exists, it is used; otherwise, the engine looks for a unique non-null column. If none exist, MySQL generates a hidden row ID.
• Secondary Index: These are indexes created on non-primary key columns. The leaf nodes of a secondary index store the index key values plus the primary key values (for row lookup), rather than the actual row data.

Best Practices for Index Management

1. Leftmost Prefix Principle: Design composite indexes to cover multiple query patterns and limit the total number of indexes per table (recommended < 6) to avoid excessive write overhead.
2. Range Query Handling: Using >= or <= is generally safer than > or < in some optimizer scenarios, as strict inequalities can sometimes force the optimizer to ignore subsequent columns in a composite index.
3. Avoid Expression on Columns: Never apply functions or calculations to an indexed column in the WHERE clause (e.g., WHERE YEAR(created_at) = 2023), as this prevents index usage.
4. Like Clause: For LIKE patterns, place the wildcard on the right (e.g., 'abc%'). A leading wildcard ('%abc') invalidates the index.
5. OR Logic: Indexes are typically not used if an OR condition mixes an indexed column with a non-indexed column. Both sides must be indexed for the optimizer to utilize them.
6. Index Hints: Use USE INDEX, IGNORE INDEX, or FORCE INDEX in queries to guide the optimizer if it chooses a suboptimal execution path.
7. Covering Index: Ensure your SELECT statement only retrieves columns that exist within the index. This prevents the engine from performing a "bookmark lookup" (back to the clustered index) to fetch the full row.
8. Prefix Indexing: For large VARCHAR or TEXT fields, create an index on the first N characters (prefix) to reduce index size and I/O, provided the prefix is selective enough.

Optimizing Data Insertion

• Transaction Control: Wrap multiple insert statements in a transaction (BEGIN ... COMMIT) rather than auto-committing after every row.
• Batch Values: Combine multiple rows into a single INSERT statement: INSERT INTO tbl (a, b) VALUES (1, 'x'), (2, 'y'), (3, 'z');
• Bulk Load: For massive datasets, use the LOAD DATA INFILE utility which is significantly faster than standard INSERT statements.
• JDBC Tuning: When using MyBatis or JDBC for batch inserts, append rewriteBatchedStatements=true to the connection URL to allow the driver to optimize packet sending.

Sorting and Ordering

To avoid filesort operations, align your index order with your ORDER BY clause:

1. ORDER BY a ASC, b ASC -> Index (a ASC, b ASC) or (a DESC, b DESC)
2. ORDER BY a DESC, b DESC -> Index (a ASC, b ASC) or (a DESC, b DESC)
3. ORDER BY a ASC, b DESC -> Index (a ASC, b DESC)
4. ORDER BY a DESC, b ASC -> Index (a DESC, b ASC)

Pagination Optimization

Deep pagination (e.g., LIMIT 1000000, 10) is slow because the engine reads and discards a million rows. Optimize using a subquery with a covering index:

SELECT main.* 
FROM large_table main 
JOIN (
    SELECT id FROM large_table 
    ORDER BY id 
    LIMIT 1000000, 10
) AS pager ON main.id = pager.id;

Counting Rows Efficiently

Generally, the performance hierarchy for counting is: COUNT(1) or COUNT(*) is slightly more efficient than COUNT(primary_key), provided the primary key is not nullable. COUNT(1) simply checks for row existence without inspecting column data.

Update Statement Safety

The locking behavior of an UPDATE depends entirely on the WHERE clause:

• With Index: The engine uses Row Locks, allowing other transactions to modify different rows simultaneously.
• With out Index: The engine is forced to use a Table Lock (or lock a massive range of rows), blocking all other write operations on that table and drastically reducing concurrency.

Always ensure you're update conditions are backed by an index.

General Optimization Workflow

1. Identify Bottlenecks: Enable the slow query log or use SHOW PROFILE to capture expensive queries.
2. Analyze Execution: Run EXPLAIN on the queries to check if indexes are being used or if they are失效 (invalidated).
3. Leverage Coverage: Adjust queries to select only indexed columns where possible.
4. Interpret Access Types: Review the type column in the execution plan:
   • NULL: Best case for specific aggregate lookups (e.g., MIN/MAX).
   • const/system: Single row lookup via Primary Key or Unique Index.
   • eq_ref: One row read per previous table row during a join using a unique index.
   • ref: Non-unique index lookup returning possibly multiple matches.
   • range: Index scan for a specific range (e.g., BETWEEN, IN).
   • index: Full scan of the index tree (usually when covering index is used but data is not filtered efficiently).
   • ALL: Full table scan. This is the worst-case scenario for large tables and should be avoided.