Master MySQL Performance: Slow Queries, Indexes, Transactions & Replication

1.0 How to locate slow queries in MySQL?

During stress testing we discovered some interfaces responding slower than 2 seconds. Using SkyWalking we can see which interface is slow and which part of the request consumes time, including the exact SQL execution time, allowing us to pinpoint the problematic SQL. If no monitoring system is available, MySQL’s slow query log can be enabled in the configuration file, setting a threshold (e.g., 2 seconds) so that any query exceeding the limit is recorded for later analysis.

2.0 How to analyze a slow SQL statement?

Use MySQL’s EXPLAIN command to examine execution details. Check key and key_len to see if an index is used, verify index effectiveness, inspect the type field for possible full‑index or full‑table scans, and read the extra suggestions to detect row‑lookup (back‑table) situations, which can be mitigated by adding indexes or adjusting selected columns.

3.0 What is an index?

An index is a data structure that helps MySQL retrieve rows efficiently, reducing I/O cost, speeding up sorting, and lowering CPU usage.

4.0 Underlying data structure of indexes?

InnoDB, MySQL’s default storage engine, uses a B+ tree for indexes. B+ trees have many children per node, short paths, lower disk I/O, store only keys and pointers in non‑leaf nodes, and keep data in leaf nodes linked as a doubly‑linked list, which is ideal for range queries.

5.0 Difference between B‑tree and B+‑tree?

B‑tree stores data in both leaf and non‑leaf nodes, while B+‑tree stores data only in leaf nodes, giving more stable query performance. B+‑tree’s leaf nodes form a linked list, making range queries faster.

6.0 What are clustered and non‑clustered indexes?

A clustered index stores the entire row in the leaf nodes of the B+ tree, usually built on the primary key, and there is only one per table. A non‑clustered index stores only the primary‑key value in leaf nodes, allowing multiple such indexes per table.

7.0 What is a back‑table (row‑lookup) query?

It occurs when a secondary index provides the primary‑key value, and MySQL must then fetch the full row from the clustered index using that key.

8.0 What is a covering index?

A covering index contains all columns required by a SELECT statement, eliminating the need for a back‑table lookup and improving performance.

9.0 How to handle huge pagination?

Instead of using LIMIT offset, count with sorting, first query only the primary‑key IDs using a covering index, then retrieve the rows for those IDs with a sub‑query, which leverages the index and speeds up pagination.

10.0 Principles for creating indexes

Consider indexes when a table exceeds about 100,000 rows.

Index columns that are frequently used in WHERE, ORDER BY, or GROUP BY clauses.

Prefer composite indexes that cover the query’s returned columns.

Use prefix indexes for low‑cardinality columns.

Limit the number of indexes because they affect INSERT/UPDATE performance.

11.0 When can an index become ineffective?

Violating the left‑most prefix rule.

Using leading wildcards in LIKE patterns (e.g., %abc).

Applying functions or type casts on indexed columns.

Using a range condition on a composite index before the right‑most columns, causing those columns to be ignored.

12.0 SQL optimization best practices

Choose appropriate column types when creating tables.

Use indexes according to the creation principles.

Avoid SELECT * ; prefer explicit column lists, use UNION ALL instead of UNION , and prefer INNER JOIN for joins.

Employ master‑slave replication and read/write splitting.

Consider sharding (horizontal partitioning) for very large data sets.

13.0 How to optimize table creation?

Follow the project’s development handbook: select field types based on data (e.g., TINYINT, INT, BIGINT for numbers; CHAR, VARCHAR, TEXT for strings).

14.0 How to optimize index usage?

Follow index creation principles, ensure indexed columns are frequently queried, use composite covering indexes, avoid operations on indexed columns, and control the total number of indexes.

15.0 How to optimize SQL statements?

Specify column names instead of SELECT * , avoid patterns that invalidate indexes, use UNION ALL for set unions, prefer INNER JOIN , and when using LEFT/RIGHT JOIN , make the smaller table the driving side.

16.0 What are the ACID properties of a transaction?

Atomicity, Consistency, Isolation, Durability. For example, a transfer of 500 units from A to B must either complete fully or not at all (atomicity), keep balances consistent, remain isolated from other concurrent transactions, and be persisted after commit.

17.0 Problems caused by concurrent transactions

Dirty reads, non‑repeatable reads, and phantom reads.

18.0 How to solve these problems? MySQL’s default isolation level?

Use transaction isolation levels. MySQL supports: READ UNCOMMITTED – does not solve any problem. READ COMMITTED – prevents dirty reads. REPEATABLE READ – prevents dirty and non‑repeatable reads; this is MySQL’s default. SERIALIZABLE – prevents all three but has lower performance.

19.0 Difference between undo log and redo log

Redo log records physical changes to data pages for crash recovery (ensuring durability). Undo log records logical changes to allow transaction rollback (ensuring atomicity and consistency).

20.0 How is isolation guaranteed? (Explain MVCC)

Isolation is achieved via locks and Multi‑Version Concurrency Control (MVCC). MVCC keeps multiple versions of rows using hidden fields (trx_id, roll_pointer), undo logs, and read views. The read view determines which version a transaction sees based on its isolation level.

21.0 MySQL master‑slave replication principle

The master writes data changes to the binary log (Binlog). Slaves read the Binlog, write it to a relay log, and then replay the events to apply the changes to their own data.

22.0 Have you used MySQL sharding?

In a micro‑service architecture each service has its own database, which is a form of vertical sharding.

23.0 Have you used horizontal sharding?

Yes. When a table grew beyond 10 million rows, we deployed three servers with three databases and used Mycat to shard data by ID modulo, migrating old data accordingly to distribute storage and read load.