MariaDB

## Character Set - Always use `utf8mb4` for tables and connections—full Unicode including emoji - `utf8mb4_unicode_ci` for proper linguistic sorting, `utf8mb4_bin` for byte comparison - Set connection charset: `SET NAMES utf8mb4` or in connection string - Collation mismatch in JOINs forces conversion—kills index usage ## Indexing - TEXT/BLOB columns need prefix length: `INDEX (description(100))` - Composite index order matters—`(a, b)` serves `WHERE a=?` but not `WHERE b=?` - Foreign keys auto-create index on child table—but verify with `SHOW INDEX` - Covering indexes: include all SELECT columns to avoid table lookup ## Sequences - `CREATE SEQUENCE seq_name` for guaranteed unique IDs across tables - `NEXT VALUE FOR seq_name` to get next—survives transaction rollback - Better than auto-increment when you need ID before insert - `SETVAL(seq_name, n)` to reset—useful for migrations ## System Versioning (Temporal Tables) - `ALTER TABLE t ADD SYSTEM VERSIONING` to track all historical changes - `FOR SYSTEM_TIME AS OF '2024-01-01 00:00:00'` queries past state - `FOR SYSTEM_TIME BETWEEN start AND end` for change history - Invisible columns `row_start` and `row_end` store validity period ## JSON Handling - `JSON_VALUE(col, '$.key')` extracts scalar, returns NULL if not found - `JSON_QUERY(col, '$.obj')` extracts object/array with quotes preserved - `JSON_TABLE()` converts JSON array to rows—powerful for unnesting - `JSON_VALID()` before insert if column isn't strictly typed ## Galera Cluster - All nodes writable—but same-row conflicts cause rollback - `wsrep_sync_wait = 1` before critical reads—ensures node is synced - Keep transactions small—large transactions increase conflict probability - `wsrep_cluster_size` should be odd number—avoids split-brain ## Window Functions - `ROW_NUMBER() OVER (PARTITION BY x ORDER BY y)` for ranking within groups - `LAG(col, 1) OVER (ORDER BY date)` for previous row value - `SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)` for running total - CTEs with `WITH cte AS (...)` for readable complex queries ## Thread Pool - Enable with `thread_handling=pool-of-threads`—better than thread-per-connection - `thread_pool_size` = CPU cores for CPU-bound, higher for I/O-bound - Reduces context switching with many concurrent connections - Monitor with `SHOW STATUS LIKE 'Threadpool%'` ## Storage Engines - InnoDB default—ACID transactions, row locking, crash recovery - Aria for temporary tables—crash-safe replacement for MyISAM - MEMORY for caches—data lost on restart, but fast - Check engine: `SHOW TABLE STATUS WHERE Name='table'` ## Locking - `SELECT ... FOR UPDATE` locks rows until commit - `LOCK TABLES t WRITE` for DDL-like exclusive access—blocks all other sessions - Deadlock detection automatic—one transaction rolled back; must retry - `innodb_lock_wait_timeout` default 50s—lower for interactive apps ## Query Optimization - `EXPLAIN ANALYZE` for actual execution times (10.1+) - `optimizer_trace` for deep dive: `SET optimizer_trace='enabled=on'` - `FORCE INDEX (idx)` when optimizer chooses wrong index - `STRAIGHT_JOIN` to force join order—last resort ## Backup and Recovery - `mariadb-dump --single-transaction` for consistent backup without locks - `mariadb-backup` for hot InnoDB backup—incremental supported - Binary logs for point-in-time recovery: `mysqlbinlog binlog.000001 | mariadb` - Test restores regularly—backups that can't restore aren't backups ## Common Errors - "Too many connections"—increase `max_connections` or use connection pool - "Lock wait timeout exceeded"—find blocking query with `SHOW ENGINE INNODB STATUS` - "Row size too large"—TEXT/BLOB stored off-page, but row pointers have limits - "Duplicate entry for key"—check unique constraints, use `ON DUPLICATE KEY UPDATE`