Mysql Optimizer

EXPLAIN FORMAT=TREE
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
  AND o.created_at > '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

EXPLAIN ANALYZE
SELECT c.name, COUNT(*) as order_count, SUM(o.total) as total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.created_at >= '2025-01-01'
GROUP BY c.id
HAVING total_spent > 1000
ORDER BY total_spent DESC
LIMIT 10;

-- Shows actual execution times and row counts vs estimates
-- -> Limit: 10 row(s)  (actual time=45.3..45.3 rows=10 loops=1)
--     -> Sort: total_spent DESC, limit input to 10 row(s)
--         -> Filter: (total_spent > 1000)
--             -> Table scan on <temporary>

-- Problem 1: Full table scan (type=ALL)
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2025;
-- Fix: Avoid functions on indexed columns
SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

-- Problem 2: Using filesort on large result set
EXPLAIN SELECT * FROM orders WHERE customer_id = 123 ORDER BY created_at DESC;
-- Fix: Composite index matching WHERE + ORDER BY
CREATE INDEX idx_customer_date ON orders (customer_id, created_at DESC);

-- Problem 3: Using temporary (GROUP BY / DISTINCT)
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- Fix: Index on the GROUP BY column
CREATE INDEX idx_status ON orders (status);

-- Problem 4: Implicit type conversion
EXPLAIN SELECT * FROM users WHERE phone = 5551234567;  -- phone is VARCHAR
-- Fix: Use correct type
SELECT * FROM users WHERE phone = '5551234567';

-- B-tree index (default, general purpose)
CREATE INDEX idx_email ON users (email);

-- Composite index (column order follows query patterns)
CREATE INDEX idx_composite ON orders (customer_id, status, created_at);
-- Supports queries filtering on:
-- (customer_id), (customer_id, status), (customer_id, status, created_at)
-- Does NOT support: (status), (status, created_at), (created_at)

-- Covering index (all query columns in index)
CREATE INDEX idx_covering ON orders (customer_id, status, created_at, total);
-- Query uses "Using index" (index-only scan, no table lookup)
SELECT status, created_at, total FROM orders WHERE customer_id = 123;

-- Prefix index (for long text columns)
CREATE INDEX idx_url ON pages (url(100));

-- Full-text index
CREATE FULLTEXT INDEX idx_content ON articles (title, body);
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('mysql optimization' IN BOOLEAN MODE);

-- Spatial index
CREATE SPATIAL INDEX idx_location ON stores (location);

-- Descending index (MySQL 8.0+)
CREATE INDEX idx_recent ON events (created_at DESC);

-- Invisible index (test impact without dropping)
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;
-- Monitor performance, then:
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;
-- Or drop if not needed

1. Start with queries from the slow query log
2. Identify WHERE, JOIN, ORDER BY, and GROUP BY columns
3. Build composite indexes following the order:
   - Equality conditions first (WHERE status = 'active')
   - Range conditions next (WHERE created_at > '2025-01-01')
   - ORDER BY / GROUP BY columns last
4. Include SELECT columns for covering index when feasible
5. Avoid over-indexing: each index slows writes
6. Use FORCE INDEX only as last resort

-- Check index usage (MySQL 8.0+ with sys schema)
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb';

-- Check duplicate/redundant indexes
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'mydb';

-- Index statistics
SHOW INDEX FROM orders;
ANALYZE TABLE orders;  -- Update index statistics

-- Index size
SELECT table_name, index_name,
       ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'mydb' AND stat_name = 'size';

# my.cnf / my.ini

[mysqld]
# Buffer pool: 70-80% of available RAM on dedicated server
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8   # 1 per GB, max 64

# Redo log: larger = better write performance, longer recovery
innodb_redo_log_capacity = 4G      # MySQL 8.0.30+

# I/O configuration
innodb_io_capacity = 2000          # IOPS for background tasks (SSD: 2000-10000)
innodb_io_capacity_max = 4000
innodb_flush_method = O_DIRECT     # Linux: bypass OS cache
innodb_flush_neighbors = 0         # Disable for SSD

# Concurrency
innodb_thread_concurrency = 0      # Let InnoDB manage
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# Doublewrite buffer
innodb_doublewrite = ON            # Data safety

# Change buffer
innodb_change_buffer_max_size = 25  # Percentage of buffer pool

# Flush behavior
innodb_flush_log_at_trx_commit = 1  # 1=ACID safest, 2=per second, 0=OS decides
sync_binlog = 1                     # Sync binlog on each commit

-- Buffer pool utilization
SELECT
  ROUND(@@innodb_buffer_pool_size / 1024 / 1024 / 1024, 2) AS pool_size_gb,
  ROUND(data_length / 1024 / 1024 / 1024, 2) AS data_size_gb
FROM (
  SELECT SUM(data_length + index_length) AS data_length
  FROM information_schema.tables
  WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
) t;

-- Buffer pool hit rate (should be > 99%)
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Hit rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

[mysqld]
slow_query_log = ON
slow_query_log_file = [system-path]
long_query_time = 0.5           # Log queries taking > 500ms
log_queries_not_using_indexes = ON
min_examined_row_limit = 1000   # Only log if examining > 1000 rows
log_slow_admin_statements = ON
log_slow_replica_statements = ON

# Percona Toolkit: aggregate and rank slow queries
pt-query-digest [system-path] --limit=20

# Filter by database
pt-query-digest [system-path] --filter '$event->{db} eq "mydb"'

# Filter by time range
pt-query-digest [system-path] --since '2025-03-01' --until '2025-03-02'

-- Top queries by total execution time
SELECT
  DIGEST_TEXT AS query_pattern,
  COUNT_STAR AS executions,
  ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_seconds,
  ROUND(AVG_TIMER_WAIT / 1e12, 4) AS avg_seconds,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT,
  FIRST_SEEN, LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- Add MySQL backend servers
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES
  (10, 'mysql-primary', 3306, 100),
  (20, 'mysql-replica-1', 3306, 50),
  (20, 'mysql-replica-2', 3306, 50);

-- Query rules: route reads to replicas
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup) VALUES
  (1, 1, '^SELECT.*FOR UPDATE', 10),
  (2, 1, '^SELECT', 20),
  (3, 1, '.*', 10);

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

-- On primary
CREATE USER 'repl'@'%' IDENTIFIED BY 'YOUR_SECURE_PASSWORD_HERE';  -- CHANGE THIS PASSWORD
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- On replica
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary-host',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='YOUR_SECURE_PASSWORD_HERE',  -- CHANGE THIS PASSWORD
  SOURCE_AUTO_POSITION=1;
START REPLICA;

-- Monitor replication
SHOW REPLICA STATUS\G

-- Bootstrap first node
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

-- Join additional nodes
START GROUP_REPLICATION;

-- Range partitioning by date
CREATE TABLE events (
    id BIGINT AUTO_INCREMENT,
    event_type VARCHAR(50),
    payload JSON,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p2025_01 VALUES LESS THAN (TO_DAYS('2025-02-01')),
    PARTITION p2025_02 VALUES LESS THAN (TO_DAYS('2025-03-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Partition management
ALTER TABLE events ADD PARTITION (
    PARTITION p2025_04 VALUES LESS THAN (TO_DAYS('2025-05-01'))
);
ALTER TABLE events DROP PARTITION p2024_01;

-- Use utf8mb4 everywhere (full Unicode including emoji)
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Convert existing table
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Collation comparison:
-- utf8mb4_unicode_ci: Standard Unicode (case-insensitive)
-- utf8mb4_0900_ai_ci: MySQL 8.0 default, accent-insensitive
-- utf8mb4_bin: Binary (case-sensitive, exact match)

mysqldump --single-transaction --routines --triggers --events \
  --set-gtid-purged=ON --source-data=2 \
  --databases mydb > backup_$(date +%Y%m%d).sql

# Full backup (online, non-blocking for InnoDB)
xtrabackup --backup --target-dir=/backups/full/$(date +%Y%m%d) \
  --user=root --password=pass

# Incremental backup
xtrabackup --backup --target-dir=/backups/inc/$(date +%Y%m%d) \
  --incremental-basedir=/backups/full/20250301

# Prepare and restore
xtrabackup --prepare --target-dir=/backups/full/20250301
xtrabackup --copy-back --target-dir=/backups/full/20250301