Essential MySQL Optimization: SQL Tricks and Parameter Tuning

Whether in daily work or interviews, MySQL optimization is a common challenge; this article organizes tips from SQL optimization to MySQL parameter tuning.

1. SQL Optimization

(1) Limit optimization

Deep pagination on large tables can be improved by avoiding offset‑based LIMIT and using a condition on the indexed column:

<code># Before limit optimization
select * from student where limit 20000, 10;
# After limit optimization
select * from student where id > 20000 limit 10;</code>

(2) LIKE fuzzy query

When using LIKE, place the wildcard at the end to keep index usage:

<code># Correct LIKE usage
select * from student where name like "zhang%";</code>

(3) UNION query

Replace UNION with UNION ALL to avoid unnecessary duplicate removal and sorting when duplicates are not a concern:

<code># UNION ALL example
select * from student where name = "zhangsan"
union all
select * from student where age = 15;</code>

(4) EXISTS vs IN

Use IN when the subquery returns few rows and the outer table is large with indexed columns; use EXISTS when the outer table is small and the subquery table is large:

<code># IN example
select * from student where teacher_id in (select id from teacher where name = "张老师");
# EXISTS example
select * from teacher as t where exists (select 1 from user as u where u.id = t.id);</code>

(5) TRUNCATE for deletion

TRUNCATE quickly drops and recreates a table, freeing space and bypassing the transaction log, but it cannot be rolled back.

<code>truncate student;</code>

(6) Batch inserts

Insert multiple rows in a single statement to reduce I/O and parsing overhead:

<code>insert into student(name, age) values ("zhangsan",1),("lisi",2);</code>

(7) Early filtering

Filter unnecessary rows before grouping, sorting, or joining to lower data volume:

<code>select name from student where student_area = "北京" or student_area = "广州" group by name;</code>

(8) Functions on the right side

Place functions on the right side of the equality to preserve index usage:

<code>select * from student where area = left("中国北京",4);</code>

(9) Minimize field types

Choose the smallest appropriate data type (e.g., TINYINT for soft‑delete flags) to reduce storage and improve CPU cache efficiency.

(10) CHAR vs VARCHAR

CHAR is fixed‑length and fast for truly fixed data; VARCHAR stores variable‑length data efficiently, saving space when lengths vary.

(11) Indexing principles

Index frequently queried columns, avoid indexing columns that are frequently updated.

Index join, group‑by, and order‑by columns.

Prefer high‑cardinality columns for indexes.

Create composite indexes for multi‑condition queries.

Ensure DELETE and UPDATE statements have indexed WHERE clauses to prevent table locks.

(12) FORCE INDEX

When the optimizer chooses a suboptimal index, you can force a specific one:

<code>select * from student force index(idx_teacher_id) where teacher_id in (123,124,154);</code>

2. MySQL Parameter Optimization

(1) Buffer Pool Tuning

The InnoDB buffer pool caches data in memory; its default size is 128 MB. Check its size and hit rate, and increase it if the hit rate falls below 90%.

<code># Show buffer pool size
show variables like 'innodb_buffer_pool_size%';
# Show buffer pool read statistics
show status like 'innodb_buffer_pool_read%';
# Set a larger buffer pool (e.g., 6 GB)
set global innodb_buffer_pool_size = 6442450944;
# Verify resize status
show status where variable_name = 'Innodb_buffer_pool_resize_status';</code>

(2) Redo Log Tuning

The redo log buffers transaction changes; a larger log buffer reduces I/O for large transactions. Adjust both the log buffer size and the log file size according to workload.

<code># Check log buffer size
show variables like 'innodb_log_buffer_size%';
# Check log file size
show variables like 'innodb_log_file_size%';</code>

Setting the log file size too small causes frequent flushes; setting it too large can prolong recovery after a crash. Choose a size based on estimated log generation per minute and per hour for your application.