Cheatsheet

-- ===== Schema =====
CREATE TABLE customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL,
  name VARCHAR(120) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 'utf8' is legacy 3-byte; 'utf8mb4' is real UTF-8

-- ===== Indexes =====
CREATE INDEX ix_status_created ON orders (status, created_at DESC);
ALTER TABLE orders ADD KEY ix_customer_status_created (customer_id, status, created_at DESC);
-- ESR rule: equality, sort, range
-- Verify with EXPLAIN; look for 'Using index' and 'rows: <small>'

EXPLAIN SELECT * FROM orders WHERE customer_id=42 AND status='paid'
        ORDER BY created_at DESC LIMIT 20;

-- ===== JOINs =====
SELECT c.name, COUNT(o.id) AS orders, SUM(o.total_cents) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status IN ('paid','shipped')
GROUP BY c.id, c.name
ORDER BY revenue DESC NULLS LAST                 -- MySQL 8.0.31+
LIMIT 20;

-- ===== Transactions =====
START TRANSACTION;
  UPDATE customers SET orders_count = orders_count + 1 WHERE id = 42;
  INSERT INTO orders(customer_id, total_cents, status) VALUES (42, 4995, 'new');
COMMIT;

-- Lock the row you intend to update
SELECT * FROM orders WHERE id = 1 FOR UPDATE;

-- ===== JSON =====
INSERT INTO orders(customer_id, details) VALUES (1, JSON_OBJECT('source','web','utm','spring'));
SELECT id, JSON_EXTRACT(details, '$.source') AS src,
            details->>'$.utm'                 AS utm
FROM orders;

-- Index JSON via a generated column
ALTER TABLE orders
  ADD COLUMN source VARCHAR(32) GENERATED ALWAYS AS (details->>'$.source') STORED,
  ADD KEY ix_source (source);

-- ===== Window functions (8.0+) =====
SELECT id, customer_id, total_cents,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS n,
       SUM(total_cents) OVER (PARTITION BY customer_id) AS lifetime
FROM orders;

-- ===== CTEs =====
WITH paid AS (SELECT * FROM orders WHERE status = 'paid')
SELECT customer_id, COUNT(*) FROM paid GROUP BY customer_id;

-- ===== UPSERT =====
INSERT INTO customers(email, name) VALUES ('alice@example.com','Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- ===== Replication =====
-- 1) Source: server_id=1; log_bin=mysql-bin; binlog_format=ROW
-- 2) Replica: server_id=2; relay_log=mysql-relay
-- 3) Snapshot via mysqldump --source-data=2 --set-gtid-purged=ON
-- 4) Replica points at the source:
--    CHANGE REPLICATION SOURCE TO SOURCE_HOST='primary', SOURCE_USER='repl', SOURCE_AUTO_POSITION=1;
--    START REPLICA;
-- 5) Watch: SHOW REPLICA STATUS\G

-- ===== Performance flags / tooling =====
-- Slow query log: long_query_time = 1
-- EXPLAIN ANALYZE (8.0.18+) — runs the query and shows actual costs
-- pt-query-digest on slow log for a top-N report
-- innodb_buffer_pool_size = ~70% of free RAM on a dedicated host
-- innodb_flush_log_at_trx_commit = 1 (durability) — relax for write-heavy logging

-- ===== Backup / restore =====
mysqldump -uadmin -p shop > shop-$(date +%F).sql
xtrabackup --backup --target-dir=/backup/full

-- Restore
mysql -uadmin -p shop < shop-2026-06-18.sql

-- ===== Common pitfalls =====
-- - utf8 charset (use utf8mb4)
-- - 'AUTO_INCREMENT' gaps after rollbacks: normal; do not chase them
-- - REPLACE INTO is DELETE + INSERT; prefer ON DUPLICATE KEY UPDATE
-- - ENUM('a','b') columns are painful to add values to later; consider a lookup table
-- - 'SELECT *' on wide tables in transactions; pick the columns you need

-- SHOW DATABASES; USE; SHOW TABLES; DESCRIBE table; SHOW INDEX; SHOW STATUS;