MariaDB查询优化

Character Set

• - Always use utf8mb4 for tables and connections—full Unicode including emoji
• INLINECODE1 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: INLINECODE4
• Composite index order matters—(a, b) serves WHERE a=? but not INLINECODE7
• Foreign keys auto-create index on child table—but verify with INLINECODE8
• Covering indexes: include all SELECT columns to avoid table lookup

Sequences

• - CREATE SEQUENCE seq_name for guaranteed unique IDs across tables
• INLINECODE10 to get next—survives transaction rollback
• Better than auto-increment when you need ID before insert
• INLINECODE11 to reset—useful for migrations

System Versioning (Temporal Tables)

• - ALTER TABLE t ADD SYSTEM VERSIONING to track all historical changes
• INLINECODE13 queries past state
• INLINECODE14 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
• INLINECODE18 extracts object/array with quotes preserved
• INLINECODE19 converts JSON array to rows—powerful for unnesting
• INLINECODE20 before insert if column isn't strictly typed

Galera Cluster

• - All nodes writable—but same-row conflicts cause rollback
• INLINECODE21 before critical reads—ensures node is synced
• Keep transactions small—large transactions increase conflict probability
• INLINECODE22 should be odd number—avoids split-brain

Window Functions

• - ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) for ranking within groups
• INLINECODE24 for previous row value
• INLINECODE25 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
• INLINECODE28 = CPU cores for CPU-bound, higher for I/O-bound
• Reduces context switching with many concurrent connections
• Monitor with INLINECODE29

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: INLINECODE30

Locking

• - SELECT ... FOR UPDATE locks rows until commit
• INLINECODE32 for DDL-like exclusive access—blocks all other sessions
• Deadlock detection automatic—one transaction rolled back; must retry
• INLINECODE33 default 50s—lower for interactive apps

Query Optimization

• - EXPLAIN ANALYZE for actual execution times (10.1+)
• INLINECODE35 for deep dive: INLINECODE36
• INLINECODE37 when optimizer chooses wrong index
• INLINECODE38 to force join order—last resort

Backup and Recovery

• - mariadb-dump --single-transaction for consistent backup without locks
• INLINECODE40 for hot InnoDB backup—incremental supported
• Binary logs for point-in-time recovery: INLINECODE41
• 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 INLINECODE43
• "Row size too large"—TEXT/BLOB stored off-page, but row pointers have limits
• "Duplicate entry for key"—check unique constraints, use INLINECODE44