52 SQL Statement Performance Optimization Strategies

SQL query performance can be dramatically improved by following a series of practical optimization strategies.

1. Index usage : Create indexes on columns used in WHERE and ORDER BY clauses, avoid full table scans, limit the number of indexes per table (preferably no more than six), and ensure indexes are on selective, small, numeric columns.

2. WHERE clause best practices : Avoid NULL checks, != / <>, OR, functions, and expressions on indexed columns; prefer IN only when necessary, use BETWEEN for continuous ranges, and place the most frequent values first in IN lists.

3. Query rewriting : Replace OR conditions with UNION ALL when possible, use EXISTS instead of IN, and rewrite expressions so that operations occur on constants rather than column values (e.g., WHERE amount < 1000*30 instead of WHERE amount/30 < 1000).

4. Use of temporary structures : Store intermediate results in temporary tables or table variables to avoid repeated scans, but prefer table variables for small datasets to reduce I/O overhead.

5. Locking and concurrency : Apply NOLOCK only for read‑only queries that can tolerate dirty reads, and follow its three principles: never on DML statements, avoid on tables with frequent page splits, and prefer temporary tables over NOLOCK when possible.

6. JOIN ordering and table selection : In multi‑table joins, place the table with the smallest row count (the driving table) last in the FROM clause; for three or more tables, use an intersection table as the driving table.

7. GROUP BY optimization : Filter rows before grouping to reduce the dataset processed by GROUP BY.

8. Coding style : Write SQL keywords in uppercase, use short aliases for tables and columns, and avoid * in SELECT statements.

9. Storage engine selection : Choose MyISAM for read‑heavy workloads with few updates, and InnoDB for transactional workloads requiring consistency and concurrency.

10. Data type choices : Prefer smaller numeric types (e.g., MEDIUMINT over INT), use ENUM for fixed‑value columns, and avoid unnecessary CHAR lengths; use VARCHAR instead of large CHAR fields.

11. Backup best practices : Use secondary replication servers for backups, stop replication during backup, avoid relying on LVM snapshots, export data per table for easier recovery, and use mysqldump --opt with disabled foreign‑key checks for faster imports.

12. Miscellaneous tips : Enable query cache for repeated queries, use EXPLAIN to analyze execution plans, apply LIMIT 1 when only one row is needed, and set THREADS = MAX_CONNECTIONS + 5 when memory permits.

Below are representative code examples illustrating some of the above points:

select id from t where num between 1 and 3

select id from t where name like 'abc%'

SELECT * FROM record WHERE card_no LIKE '5378%'

SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE num = a.num)

select a.personMemberID, * from chineseresume a, personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681'

By systematically applying these strategies, developers can achieve faster query execution, lower resource consumption, and more scalable database systems.