Master MySQL Performance: Practical SQL, Index, Join, and Hardware Optimization Techniques

SQL Optimization

Effective SQL optimization includes improving pagination, index usage, and query structure.

Pagination Optimization

Typical pagination query:

select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

Optimizations:

Delayed join – first fetch primary keys, then join on those keys.

select a.* from table a, (select id from table where type = 2 and level = 9 order by id asc limit 190289,10) b where a.id = b.id;

Bookmark method – locate the primary key of the first row in the limit range and filter by it.

select * from table where id > (select * from table where type = 2 and level = 9 order by id asc limit 190289,1) limit 10;

Index Optimization

Proper index usage prevents full‑table scans. Adding indexes on frequently queried columns dramatically improves performance.

Covering Index

When the index leaf contains all needed columns, InnoDB can avoid the extra table lookup. select name from test where city='上海'; Create a composite index to cover the query:

alter table test add index idx_city_name (city, name);

Avoid OR queries in MySQL versions before 5.0; use UNION or subqueries instead, as later versions support index merge.

Replace != or <> with equivalent OR conditions to keep indexes usable.

Use prefix indexes for columns with common prefixes (e.g., email domains) to save space, but note they cannot be used for ORDER BY or as covering indexes. alter table test add index index2(email(6)); Always select only required columns instead of SELECT * to reduce I/O.

Prefer joins over subqueries because subqueries create temporary tables that add overhead.

Apply the "small‑table drives large‑table" principle: query the smaller table first.

select name from A where id in (select id from B);

Avoid functions or arithmetic on indexed columns, as they prevent index usage.

select * from test where id + 1 = 50;  -- bad
select * from test where month(updateTime) = 7;  -- bad

Watch out for implicit type conversion; comparing a VARCHAR column with an integer forces a full scan.

select * from test where skuId = 123456;  -- skuId is VARCHAR, causes conversion

Adding redundant fields can reduce costly joins, trading space for speed.

When creating composite indexes, follow the left‑most prefix rule (e.g., idx(name,age,sex) works for queries using name, name+age, or name+age+sex).

Join Optimization

MySQL uses a nested‑loop join algorithm. Reduce the number of loop iterations by indexing join columns and driving the join with the smaller result set.

MySQL can automatically choose the smaller table as the driver for INNER JOIN.

Adjust join_buffer_size carefully; setting it too high with many joins can exhaust memory.

Sorting Optimization

Use an index that matches the ORDER BY clause to avoid extra sorting.

-- index on (date, staff_id, customer_id)
select staff_id, customer_id from test where date='2010-01-01' order by staff_id, customer_id;

UNION Optimization

Push down WHERE and LIMIT clauses into each sub‑query and prefer UNION ALL unless duplicate elimination is required.

Slow Query Log

Enable the slow‑query log to capture statements exceeding long_query_time: slow_query_log=1 Be aware that enabling the log adds overhead in production.

Schema Design Optimization

Avoid NULL; use NOT NULL with default values.

Choose minimal data lengths and simple data types (e.g., INT over VARCHAR when possible).

Limit use of TEXT and BLOB; store large text in separate tables if needed.

Apply partitioning (table sharding) and database sharding to reduce row counts per table.

Common Type Choices

Integer width (e.g., INT(11)) does not affect range; choose based on storage needs. CHAR is fixed‑length; VARCHAR saves space for variable strings but adds length bytes. DATETIME (8 bytes) vs TIMESTAMP (4 bytes); TIMESTAMP is more space‑efficient but timezone‑dependent. BLOB / TEXT should be used sparingly due to low query performance.

Normalization

Higher normal forms reduce data redundancy and improve cacheability, though they may increase join complexity.

1NF – atomic columns.

2NF – eliminate partial dependencies (use surrogate keys).

3NF – eliminate transitive dependencies (split tables).

Hardware Optimization

Disk : Use high‑performance SSDs and consider multiple disks for parallel I/O.

Network : Ensure low‑latency, high‑bandwidth connections; multiple NICs can improve throughput.

Memory : Larger RAM allows more data caching, speeding up query processing.