Mysql Expert

MySQL Expert

MySQL differs from PostgreSQL in ways that matter for design, not just syntax: the clustered primary key, the storage-engine boundary, and a replication model built on the binary log.

Core Concepts

InnoDB Is the Database

Everything below assumes InnoDB. Rows are stored inside the primary key index — the table is the primary key B-tree. Three consequences drive most design decisions:

1. The primary key is present in every secondary index, so a wide primary key inflates every index on the table.
2. A secondary index lookup costs two traversals: the index, then the primary key. Unless the index covers the query.
3. Inserts in primary key order are cheap; random primary keys cause page splits and fragmentation.

This is why a BIGINT AUTO_INCREMENT or a time-ordered UUID (UUIDv7) beats a random UUIDv4 primary key by a wide margin on large tables.

Character Sets

Use utf8mb4 and nothing else. MySQL's utf8 is a three-byte subset that cannot store emoji or some CJK characters, and it fails by truncating or erroring at insert time.

CREATE TABLE orders (…) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Collation determines comparison and sorting. utf8mb4_0900_ai_ci is accent-insensitive and case-insensitive; use utf8mb4_0900_as_cs or _bin when you need exact matching, for example on tokens or hashes.

Isolation and Locking

The default is REPEATABLE READ, which is stricter than PostgreSQL's default and uses gap locks that surprise people migrating across.

SELECT @@transaction_isolation;
SET SESSION transaction_isolation = 'READ-COMMITTED';   -- often the better default

READ COMMITTED reduces gap locking and deadlocks for typical OLTP workloads. Change it deliberately and test — it also changes replication semantics for statement-based binlog formats.

Schema Design

CREATE TABLE orders (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id     BINARY(16) NOT NULL,                    -- UUID stored compactly
  customer_id   BIGINT UNSIGNED NOT NULL,
  status        ENUM('pending','paid','shipped','cancelled') NOT NULL,
  total_minor   BIGINT NOT NULL,                        -- money as integer minor units
  currency      CHAR(3) NOT NULL,
  created_at    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_orders_public_id (public_id),
  KEY idx_orders_customer_created (customer_id, created_at),
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Decisions worth defending:

• Surrogate BIGINT primary key, with the externally visible identifier as a separate unique column. Keeps the clustered index narrow and sequential.
• BINARY(16) for UUIDs, not CHAR(36): a quarter of the size, and it propagates into every secondary index.
• Money as integer minor units. DECIMAL is acceptable; FLOAT and DOUBLE are not, ever.
• TIMESTAMP in UTC, with fractional seconds where ordering matters. DATETIME does not carry a time zone conversion; be deliberate about which you want.
• Explicit NOT NULL. MySQL is permissive by default; strict mode and NOT NULL catch problems at write time.

Verify strict mode is on — without it, MySQL silently truncates data:

SELECT @@sql_mode;  -- must include STRICT_TRANS_TABLES and NO_ENGINE_SUBSTITUTION

Indexing

The leftmost-prefix rule governs composite indexes: an index on (a, b, c) serves a, (a, b) and (a, b, c), but not b alone.

-- Covering index: the query never touches the table
ALTER TABLE orders ADD KEY idx_cover (customer_id, created_at, status, total_minor);

EXPLAIN SELECT status, total_minor FROM orders
WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;
-- Extra: Using index    <- no lookup to the clustered index

Order composite index columns by: equality predicates first, then the range or sort column, then columns needed only for covering.

Things that silently disable an index:

WHERE DATE(created_at) = '2026-01-31'       -- function on the column
WHERE customer_id = '42'                    -- string compared to an integer column
WHERE name LIKE '%dupont'                   -- leading wildcard
WHERE status != 'paid'                      -- low selectivity negation

Rewrite the first as a range: created_at >= '2026-01-31' AND created_at < '2026-02-01'.

Read plans with EXPLAIN ANALYZE (MySQL 8.0.18+), which reports actual rows and time rather than estimates:

EXPLAIN ANALYZE SELECT … ;
EXPLAIN FORMAT=JSON SELECT … ;    -- cost details, used keys, filtering ratios

Finding Slow Queries

-- Enable capture
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Or query performance_schema, which needs no log parsing
SELECT DIGEST_TEXT,
       COUNT_STAR                              AS calls,
       ROUND(SUM_TIMER_WAIT/1e12, 2)           AS total_s,
       ROUND(AVG_TIMER_WAIT/1e9, 2)            AS avg_ms,
       SUM_ROWS_EXAMINED / NULLIF(SUM_ROWS_SENT, 0) AS examined_per_row
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

examined_per_row is the most useful single number: a high ratio means the query is scanning to produce few rows, which is an index problem.

Locking and Deadlocks

SHOW ENGINE INNODB STATUS\G          -- LATEST DETECTED DEADLOCK section

SELECT r.trx_id, r.trx_mysql_thread_id, r.trx_query AS waiting_query,
       b.trx_id AS blocking_trx, b.trx_query AS blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id;

Deadlocks are normal under concurrency; the application must retry them. Reduce their frequency by acquiring locks in a consistent order, keeping transactions short, and avoiding a SELECT … FOR UPDATE that scans more rows than it needs.

Online Schema Change

ALTER TABLE on a large table can block writes for a long time. Check the algorithm before running it in production.

ALTER TABLE orders ADD COLUMN note VARCHAR(255),
  ALGORITHM=INPLACE, LOCK=NONE;      -- fails loudly if it cannot be online

Specifying ALGORITHM and LOCK explicitly is the safety mechanism: MySQL refuses rather than silently falling back to a blocking copy.

For operations MySQL cannot do online, use a copy tool:

pt-online-schema-change \
  --alter "MODIFY COLUMN total_minor BIGINT NOT NULL" \
  --max-lag 2 --critical-load Threads_running=64 \
  --chunk-time 0.5 --execute \
  D=shop,t=orders

These tools create a shadow table, copy in chunks with triggers keeping it current, then swap. Always run with --dry-run first, and confirm foreign keys are handled the way you expect.

Replication

SHOW REPLICA STATUS\G
-- Seconds_Behind_Source, Replica_IO_Running, Replica_SQL_Running, Last_Error

Use GTID replication and row-based binary logging: together they make failover and position tracking tractable.

[mysqld]
gtid_mode                = ON
enforce_gtid_consistency = ON
binlog_format            = ROW
binlog_row_image         = MINIMAL
sync_binlog              = 1
innodb_flush_log_at_trx_commit = 1

The last two are the durability pair. Relaxing them improves write throughput and means a crash can lose committed transactions — a deliberate trade-off, never a default.

Replicas are asynchronous by default: a read immediately after a write may not see it. Route read-after-write to the primary, or use semi-synchronous replication when the application cannot tolerate the lag.

Best Practices

• Enable strict mode. Silent truncation is worse than an error.
• utf8mb4 everywhere — table, column, connection.
• Narrow, monotonic primary keys. Random UUIDs as clustered keys fragment large tables badly.
• Read the plan before adding an index, and drop unused indexes; each one taxes every write.
• Keep transactions short. Long transactions hold undo history and bloat the tablespace.
• Retry deadlocks in the application with backoff.
• Test backups by restoring them. An untested backup is a hypothesis.
• Pin the SQL mode and time zone in configuration, not per session.

Anti-Patterns

• utf8 instead of utf8mb4 — the classic emoji-truncation bug.
• FLOAT or DOUBLE for money.
• UUIDv4 as clustered primary key on a large, write-heavy table.
• SELECT * — defeats covering indexes and breaks on schema change.
• Functions on indexed columns in predicates.
• ORDER BY RAND() — sorts the whole result set.
• Offset pagination at depth — LIMIT 20 OFFSET 100000 scans 100 020 rows. Use keyset pagination.
• Blocking ALTER in production without checking the algorithm.

Reference Documentation

• Operations — configuration, backup and recovery, replication topologies, failover, monitoring queries and capacity signals

Resources

• MySQL 8.0 Reference Manual
• MariaDB Knowledge Base
• Percona Toolkit
• Baron Schwartz et al., High Performance MySQL (4th ed.)
• Use The Index, Luke