Preface

In my previous article, I presented 8 essential SQL optimization techniques. This piece will delve into 7 advanced strategies aimed at improving your SQL performance.

1. Utilizing Join Queries Over Subqueries

When querying data from multiple tables in MySQL, you typically have two options: subqueries and join queries.

Subquery Example: SELECT * FROM order WHERE user_id IN (SELECT id FROM user WHERE status = 1); Subqueries can be executed using the IN clause, allowing a condition to reference results from another select statement. The inner query executes first, followed by the outer one.

While subqueries are straightforward and well-structured for a small number of tables, they can lead to inefficiencies. MySQL may create temporary tables during execution, resulting in additional performance overhead.

Join Query Example: SELECT o.* FROM order o INNER JOIN user u ON o.user_id = u.id WHERE u.status = 1;

2. Limiting the Number of Joined Tables

According to the Alibaba Developer Handbook, it's advisable to limit join operations to no more than three tables.

Poor Example: SELECT a.name, b.name, c.name, d.name FROM a INNER JOIN b ON a.id = b.a_id INNER JOIN c ON c.b_id = b.id INNER JOIN d ON d.c_id = c.id INNER JOIN e ON e.d_id = d.id INNER JOIN f ON f.e_id = e.id INNER JOIN g ON g.f_id = f.id; Excessive joins complicate index selection for MySQL, leading to potential misindexing. If no index is utilized, a nested loop join is employed, which has a complexity of n².

Better Example: SELECT a.name, b.name, c.name, a.d_name FROM a INNER JOIN b ON a.id = b.a_id INNER JOIN c ON c.b_id = b.id;

When dealing with additional tables, consider adding redundant fields to reduce the number of joins. For instance, include a d_name field in table a to facilitate data retrieval.

3. Considerations for Joining Tables

When querying across multiple tables, the JOIN keyword is essential. The most common types include left joins and inner joins.

• Left Join: Returns the intersection plus all remaining rows from the left table.
• Inner Join: Returns only rows with matching values in both tables.

Inner Join Example: SELECT o.id, o.code, u.name FROM order o INNER JOIN user u ON o.user_id = u.id WHERE u.status = 1;

Left Join Example: SELECT o.id, o.code, u.name FROM order o LEFT JOIN user u ON o.user_id = u.id WHERE u.status = 1;

Be cautious with left joins, ensuring the left table is smaller than the right to avoid performance issues.

4. Managing Index Quantity

Indexes greatly enhance SQL query performance, but excessive indexing can be detrimental. Creating an index for new data requires additional storage and may introduce performance overhead.

The Alibaba developer manual suggests maintaining five or fewer indexes per table, with no more than five fields per index. MySQL uses a B+ tree to store indexes, and excessive indexes can slow down insert, update, or delete operations.

5. Selecting Appropriate Field Types

The CHAR type allocates a fixed amount of space, which can lead to wasted storage, while VARCHAR adjusts based on data length, making it more efficient.

Example for CHAR: ALTER TABLE order ADD COLUMN code CHAR(20) NOT NULL; Example for VARCHAR: ALTER TABLE order ADD COLUMN code VARCHAR(20) NOT NULL;

For fixed-length fields like phone numbers, CHAR is suitable. However, for fields like company names, VARCHAR is preferred to avoid storage waste.

6. Enhancing Group By Efficiency

The GROUP BY clause is often used for grouping and deduplication. Combining it with HAVING can filter data post-grouping.

Poor Example: SELECT user_id, user_name FROM order GROUP BY user_id HAVING user_id <= 200; This query is inefficient as it groups all records before filtering.

Better Example: SELECT user_id, user_name FROM order WHERE user_id <= 200 GROUP BY user_id;

By filtering before grouping, performance improves.

7. Index Optimization

Index optimization is crucial in SQL. The first step is to verify if a SQL statement utilizes an index through the EXPLAIN command.

Example: EXPLAIN SELECT * FROM order WHERE code = '002';

If a SQL statement does not use an index, it may be due to the index being invalidated. Common causes include data type mismatches or outdated statistics.

In cases where SQL statements use different indexes based on input parameters, you can utilize FORCE INDEX to specify a particular index.

For more insights and resources on programming skills, follow my updates. Your support and engagement motivate me to share more content. Thank you for your attention, and see you in the next article!