Comprehensive MySQL Performance Optimization Guide

SQL Statement and Index Optimization

Avoid sub‑queries, replace OR with IN, use appropriate LIMIT clauses, eliminate unnecessary ORDER BY, prefer UNION ALL over UNION for aggregation, avoid random record selection, batch INSERTs, select only required columns, distinguish IN and EXISTS, and fine‑tune GROUP BY statements.

SELECT * FROM t WHERE id = 10 OR id = 20 OR id = 30;

SELECT * FROM t WHERE id IN (10,20,30);

SELECT id,name FROM t LIMIT 866613,20;

Index Optimization and Preventing Index Failure

Follow the left‑most prefix rule, avoid functions or arithmetic on indexed columns, use proper type matching, ensure range conditions are on the left side of composite indexes, leverage covering indexes, and be aware that NOT, NULL checks, and leading wildcards in LIKE can invalidate indexes.

ALTER TABLE t_user ADD KEY idx_name_length ((LENGTH(name)));
SELECT * FROM user WHERE phone = 13030303030;  -- may cause index loss due to type conversion
SELECT * FROM user WHERE id = '1';               -- can use index because conversion is on constant

Table Structure Normalization

Design tables to satisfy the three normal forms and BCNF, ensuring data integrity and reducing redundancy.

System Configuration and Hardware Optimization

Adjust MySQL variables such as join_buffer_size, tmp_table_size, and use SSDs to reduce I/O latency. Optimize join order by using the smaller result set as the driver, add indexes on join columns, and prefer INNER JOIN over LEFT JOIN when possible.

MySQL Internal Components

The MySQL architecture consists of a connector, query cache (deprecated in 8.0), parser/optimizer, and executor. The optimizer builds execution plans using statistics and may choose different join algorithms (NLJ, BLJ, SLJ) based on index availability.

SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a = t2.a);  -- forces left table as driver

Overall, applying these best practices can significantly improve query response times and resource utilization in MySQL deployments.