Boost MySQL Query Speed: Practical SQL Optimization Tips for 2021

Introduction

In latency‑sensitive web services, SQL performance directly determines whether a system can respond quickly; for many small‑to‑medium applications it is the sole metric of usability.

Optimizing queries requires understanding the capabilities of the underlying RDBMS and recognizing that slow performance may stem from memory allocation, file layout, or dirty‑page flushing, not just from the SQL text itself.

The following tips focus on generic, database‑independent improvements that work on any relational engine, demonstrated with MySQL 5.7.30‑log (InnoDB).

Environment Setup

Two tables are created for the examples:

DROP TABLE IF EXISTS tbl_customer; CREATE TABLE tbl_customer ( id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键', name VARCHAR(50) NOT NULL COMMENT '顾客姓名', age TINYINT(3) NOT NULL COMMENT '年龄', id_card CHAR(18) NOT NULL COMMENT '身份证', phone_number CHAR(11) NOT NULL COMMENT '手机号码', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='顾客表'; INSERT INTO tbl_customer(name,age,id_card,phone_number) VALUES ('张三',19,'430682198109129210','15174480311'),('李四',21,'430682198109129211','15174480312'),('王五',22,'430682198109129212','15174480313');

DROP TABLE IF EXISTS tbl_recharge_record; CREATE TABLE tbl_recharge_record ( id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键', customer_id INT(11) NOT NULL COMMENT '顾客ID', recharge_type TINYINT(2) NOT NULL COMMENT '充值方式 1:支付宝, 2:微信,3:QQ,4:京东,5:银联,6:信用卡,7:其他', recharge_amount DECIMAL(15,2) NOT NULL COMMENT '充值金额, 单位元', recharge_time DATETIME NOT NULL COMMENT '充值时间', remark VARCHAR(500) NOT NULL DEFAULT 'remark' COMMENT '备注', PRIMARY KEY (id), KEY idx_c_id(customer_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='顾客充值记录表'; INSERT INTO tbl_recharge_record(customer_id,recharge_type,recharge_amount,recharge_time) VALUES (1,1,10000,NOW()),(2,2,20000,NOW()),(1,2,10000,NOW());

Use EXISTS Instead of IN

When the IN predicate contains a sub‑query, MySQL builds a temporary table ( ) and scans it, which is slower. EXISTS stops scanning as soon as a matching row is found and can use an index on the join column.

Example:

SELECT * FROM tbl_customer WHERE id IN (SELECT customer_id FROM tbl_recharge_record);

Replace with:

SELECT * FROM tbl_customer WHERE EXISTS (SELECT 1 FROM tbl_recharge_record WHERE tbl_recharge_record.customer_id = tbl_customer.id);

EXISTS avoids the temporary table and benefits from the index on customer_id.

Use JOIN Instead of IN

Joining the two tables directly also eliminates the temporary table and lets the optimizer use indexes efficiently.

SELECT c.* FROM tbl_customer c JOIN tbl_recharge_record r ON r.customer_id = c.id;

Avoid Unnecessary Sorting

Operations such as ORDER BY, UNION, INTERSECT, EXCEPT, and DISTINCT often trigger a “Using temporary” or “Using filesort” step, which can degrade performance, especially when the sort spills to disk.

Use UNION ALL, INTERSECT ALL, or EXCEPT ALL when duplicate elimination is not required, and prefer EXISTS over DISTINCT to avoid sorting.

Leverage Indexes for Aggregate Functions

MAX and MIN normally cause a full‑table scan and an implicit sort. If the target column is indexed, the optimizer can retrieve the extreme value directly from the index.

SELECT MAX(customer_id) FROM tbl_recharge_record; -- uses idx_c_id index

Prefer WHERE Over HAVING

WHERE filters rows before aggregation and can use indexes, while HAVING evaluates after aggregation on a derived result set that often lacks index support. Placing filter conditions in WHERE improves performance.

Index‑Friendly GROUP BY and ORDER BY

If the GROUP BY or ORDER BY columns have (preferably unique) indexes, MySQL can avoid an explicit sort step.

General Index Usage

Always design queries to exploit existing indexes and investigate why an index is not being used when performance is poor.

Reduce Temporary Tables

Sub‑queries generate temporary tables that consume memory and may lose index information. Rewrite queries to eliminate unnecessary sub‑queries, combine IN predicates, or push filters earlier.

Smart HAVING Usage

When filtering aggregated results, HAVING is appropriate, but avoid complex expressions that force temporary tables; keep the HAVING clause simple.

Combine Multiple IN Predicates

SQL‑92 allows row‑value comparisons, enabling a single IN list that covers multiple columns, which can be indexed more effectively.

SELECT * FROM tbl_customer WHERE (id, name) IN (SELECT customer_id, customer_name FROM other_table);

Join Before Aggregation

When both JOIN and aggregation are needed, perform the JOIN first to reduce the data volume before grouping.

Use Views Judiciously

Views are convenient but can hide costly operations (e.g., functions, sub‑queries) that prevent index usage. Define views with performance in mind.

Summary of Tips

Replace sub‑query IN with EXISTS or JOIN.

Avoid hidden sorting operations; use UNION ALL, INTERSECT ALL, etc.

Write queries that can use indexes; verify index usage.

Minimize creation of temporary tables.

References

SQL Advanced Tutorial (《SQL进阶教程》) – Author: 青石路 – Original article: cnblogs.com/youzhibing/p/11909821.html