MySQL Index Optimization, EXPLAIN Usage, and Common Pitfalls

This article summarizes frequently used and sub‑optimal MySQL operations, focusing on index management, EXPLAIN analysis, SQL tricks, and performance‑tuning techniques for developers with a basic MySQL background.

1. Index Basics

Cardinality: The number of distinct values in a column; high cardinality (few duplicates) makes an index most effective. When a column has low cardinality (e.g., gender "M/F"), the optimizer may ignore the index, typically using a 30% selectivity threshold.

Reasons for Index Failure:

1. Operations on indexed columns (e.g., +, -, *, /, !, <>, LIKE '%_') 2. Type mismatch (e.g., varchar column compared with a number) 3. Applying functions to indexed columns without a functional index (e.g., SELECT * FROM t WHERE ROUND(t.id)=1 ) 4. Use of OR prevents index usage unless every column in the OR has its own index 5. String literals must be quoted 6. B‑tree index does not use IS NULL but does use IS NOT NULL 7. Composite indexes follow the left‑most rule

Index Creation Guidelines:

Base indexes on the most frequently queried business statements.

Prefer columns with high distinct‑value ratio (COUNT(DISTINCT col)/COUNT(*)).

Use UNIQUE keys for columns with natural uniqueness to improve correctness and speed.

2. Useful Information in EXPLAIN

Basic Usage: Run DESC or EXPLAIN followed by the SQL; EXPLAIN EXTENDED plus SHOW WARNINGS reveals the actual execution statement.

Performance‑Boosting Features:

Covering index (EXTRA shows using index) – all needed columns are in the index, avoiding table look‑ups.

Index Condition Pushdown (ICP) – MySQL pushes qualifying WHERE conditions to the storage engine; shown as using index condition in EXTRA.

Index Merge – combines results from multiple indexes (shown as index_merge).

EXTRA column meanings: using filesort – MySQL performs an external sort (often costly). using temporary – a temporary table is created for GROUP BY or ORDER BY. using index – a covering index is used. impossible where – WHERE clause can never be true. select tables optimized away – MIN/MAX or COUNT(*) optimized using the index. distinct – stops after the first matching row for each distinct value.

3. Column Types and Collations

CHARACTER_LENGTH returns character count; LENGTH returns byte count (Chinese characters occupy three bytes). For varchar indexes, calculate selectivity with SELECT COUNT(DISTINCT LEFT(col,5))/COUNT(*) FROM tbl.

UTF‑8 in MySQL is limited to three bytes and cannot store emojis; use utf8mb4 and set the client charset accordingly (e.g., via SET NAMES utf8mb4 in the connection pool).

Collation differences: utf8_general_ci – case‑insensitive. utf8_bin – binary comparison, case‑sensitive.

4. Frequently Forgotten SQL Commands

INSERT IGNORE INTO …

– skip rows that would cause primary‑key/unique‑key conflicts. INSERT … ON DUPLICATE KEY UPDATE … – update on conflict (affects auto‑increment). REPLACE INTO … – delete existing row and insert a new one. CREATE TABLE new_tbl SELECT * FROM old_tbl – backup table. CREATE TABLE new_tbl LIKE old_tbl – copy structure only. INSERT INTO new_tbl SELECT * FROM old_tbl – copy data. UPDATE a, b SET a.col=a.col+1 WHERE a.id=b.id – multi‑table update. DELETE a FROM a, b WHERE a.id=b.id – multi‑table delete.

5. Locking (Reference Only)

Shared lock: SELECT id FROM tbl WHERE id=1 LOCK IN SHARE MODE; Exclusive lock:

SELECT id FROM tbl WHERE id=1 FOR UPDATE;

6. Optimization Hints

Force a specific index: SELECT * FROM tbl FORCE INDEX(idx_user) LIMIT 2; Ignore a specific index: SELECT * FROM tbl IGNORE INDEX(idx_user) LIMIT 2; Disable query cache for testing:

SELECT SQL_NO_CACHE * FROM tbl LIMIT 2;

7. Viewing Server Status

Show character set: SHOW VARIABLES LIKE 'character_set%'; Show collation:

SHOW VARIABLES LIKE 'collation%';

8. SQL Writing Tips

WHERE clauses are evaluated right‑to‑left; prefer conditions in WHERE over HAVING.

Use deferred join for large offsets (e.g., LIMIT 10000,10) to avoid costly back‑table lookups.

Avoid DISTINCT when possible; prefer GROUP BY.

Limit joins to three tables for better performance.

9. Common Pitfalls

TRUNCATE resets auto‑increment counters.

Aggregate functions return NULL when all inputs are NULL.

Never compare to NULL with “=”; use IS NULL or IS NOT NULL.

10. Online Schema Change for Large Tables

When altering huge tables, use Percona Toolkit's pt-online-schema-change to avoid locking:

pt-online-schema-change --user='root' --host='localhost' --ask-pass --alter "add index idx_user_id(room_id,create_time)" D=fission_show_room_v2,t=room_favorite_info --execute

11. Slow Query Log

Enable and inspect the slow‑query log, then analyze with mysqldumpslow:

mysqldumpslow -v -s t -t 10 mysql_slow.log.2018-11-20-0500

12. Viewing and Killing SQL Processes

Show process list: SHOW PROCESSLIST; Kill a runaway query: KILL 183665; GUI tools like SQLyog also provide a process‑list view with right‑click kill.

13. Performance Reflections

Repeated per‑ID lookups cause heavy load; batch queries are preferred.

Heavy aggregation (e.g., leaderboards) should be offloaded to read replicas or cached in Redis.

Very large pagination (e.g., LIMIT 40000,1000) may indicate abuse; add request validation.