Master MySQL Performance: Indexes, EXPLAIN, and Slow‑Query Optimization

1. Index Fundamentals

Index cardinality = number of distinct values in a column. High cardinality (distinct/total close to 1) gives best selectivity. Low‑cardinality columns (e.g., gender) rarely benefit from indexing; optimizer may ignore indexes when a value appears in >30 % of rows.

Common reasons indexes become ineffective:

Applying arithmetic or functions directly to indexed columns (e.g., ROUND(t.logicdb_id) = 1) without a functional index.

Using functions on indexed columns; MySQL 8.0 supports functional indexes, MySQL 5.7 requires a generated column or a separate column storing the computed value.

OR conditions: the optimizer cannot use a single index unless each operand column has its own index.

String literals must be quoted; B‑tree indexes do not support IS NULL but do support IS NOT NULL. Composite indexes follow the left‑most principle.

When creating indexes, prioritize columns with a high distinct‑value ratio calculated as COUNT(DISTINCT col) / COUNT(*). Unique keys are preferred for both data integrity and performance.

2. Using EXPLAIN

Run EXPLAIN or DESC before a query. EXPLAIN EXTENDED followed by SHOW WARNINGS shows the optimizer’s final execution plan.

Features that improve performance:

Covering index – all required columns are present in the index, eliminating the need to read the table ( Using index in the Extra column).

Index Condition Pushdown (ICP) – from MySQL 5.6 onward, WHERE filtering can be pushed to the storage engine ( Using index condition).

Index merge – combines results from multiple indexes; appears as index_merge in the type column.

Important values in the Extra column: Using filesort – MySQL performs an external sort (often a bottleneck). Using temporary – a temporary table is created for GROUP BY or ORDER BY. Using index – a covering index is used. Impossible where – the WHERE clause can never be true. Select tables optimized away – MIN/MAX or COUNT(*) can be computed using only the index. Distinct – stops scanning after the first matching row.

Values of the type column (access method): SYSTEM – single‑row system table. CONST – primary key or unique index matches a single row. EQ_REF – unique index scan, one row per key. REF – non‑unique index scan, may return multiple rows. RANGE – index range scan (e.g., BETWEEN, <, >, IN). INDEX – full index scan (faster than a full table scan). ALL – full table scan.

3. Field Types and Character Sets

CHARACTER_LENGTH()

returns the number of characters; LENGTH() returns the number of bytes (Chinese characters occupy three bytes in UTF‑8).

For VARCHAR indexes, evaluate selectivity with:

SELECT COUNT(DISTINCT LEFT(col,5)) / COUNT(*) FROM tbl;

Values close to 1 indicate high selectivity.

MySQL’s default utf8 charset stores up to three bytes per character and cannot store emojis. Use utf8mb4 and set character_set_server=utf8mb4 (e.g., SET NAMES utf8mb4 in the connection).

Common collations: utf8_general_ci – case‑insensitive. utf8_bin – binary comparison, case‑sensitive.

4. Frequently Used SQL Statements

INSERT IGNORE INTO …

– skips rows that would cause primary‑key/unique‑key conflicts. INSERT … ON DUPLICATE KEY UPDATE … – updates existing rows on conflict (affects auto‑increment). REPLACE INTO … – deletes the conflicting row and inserts the new one. CREATE TABLE backup AS SELECT * FROM original; – creates a backup copy. CREATE TABLE new LIKE old; – copies table structure. INSERT INTO new SELECT * FROM old; – copies 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 during testing:

SELECT SQL_NO_CACHE * FROM tbl LIMIT 2;

7. Server Variables

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

SHOW VARIABLES LIKE 'collation%';

8. SQL Writing Tips

Place filtering conditions in WHERE rather than HAVING.

Use deferred join (e.g., LIMIT 10000,10) to avoid unnecessary table lookups.

Avoid DISTINCT when possible; prefer GROUP BY for aggregation.

Limit the number of joined tables to three for better performance.

9. Common Pitfalls

TRUNCATE

resets auto‑increment counters.

Aggregate functions return NULL when all inputs are NULL (e.g., SUM(col)).

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

10. Online Schema Change for Large Tables

When altering massive 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 Analysis

Check whether slow‑query logging is enabled and view related variables:

SHOW VARIABLES LIKE '%slow%';
SHOW VARIABLES LIKE 'log_output';
SHOW GLOBAL STATUS LIKE 'slow_queries';

Summarize the log with mysqldumpslow. Common options: -t – limit output rows (e.g., top 10). -s – sort by average time ( t), count ( c), etc. -v – verbose output.

Example:

mysqldumpslow -v -s t -t 10 mysql_slow.log

12. Viewing and Killing SQL Processes

List running queries: SHOW PROCESSLIST; Terminate a problematic query:

KILL 183665;

13. Performance Considerations

Avoid looping over IDs and issuing a separate query per ID; batch queries are far more efficient.

Heavy aggregation (e.g., leaderboards) can be offloaded to a replica or cached in Redis.

Very large OFFSET pagination (e.g., LIMIT 40000,1000) should be mitigated with deferred join or request‑level limits.