MySQL Best Practices Core Principles Design schemas with appropriate storage engines (InnoDB for most use cases) Optimize queries using EXPLAIN and proper indexing Use proper data types to minimize storage and improve performance Implement connection pooling and query caching appropriately Follow MySQL-specific security hardening practices Schema Design Storage Engine Selection Use InnoDB as the default engine (ACID compliant, row-level locking) Consider MyISAM only for read-heavy, non-transactional workloads Use MEMORY engine for temporary tables with high-speed requirements CREATE TABLE orders ( order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id INT UNSIGNED NOT NULL, order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, total_amount DECIMAL(12, 2) NOT NULL, status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') NOT NULL DEFAULT 'pending', INDEX idx_customer (customer_id), INDEX idx_date_status (order_date, status), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Data Types Use smallest data type that fits your needs Prefer INT UNSIGNED over BIGINT when possible Use DECIMAL for financial calculations, not FLOAT/DOUBLE Use ENUM for fixed sets of values Use VARCHAR for variable-length strings, CHAR for fixed-length Always use utf8mb4 charset for full Unicode support -- Appropriate data type selection CREATE TABLE products ( product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(50) NOT NULL, name VARCHAR(255) NOT NULL, description TEXT, price DECIMAL(10, 2) NOT NULL, quantity SMALLINT UNSIGNED NOT NULL DEFAULT 0, weight DECIMAL(8, 3), is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_sku (sku) ) ENGINE=InnoDB;

Primary Keys Use AUTO_INCREMENT integer primary keys for InnoDB tables Consider UUIDs stored as BINARY(16) for distributed systems Avoid composite primary keys when possible -- UUID storage optimization CREATE TABLE distributed_events ( event_id BINARY(16) PRIMARY KEY, event_type VARCHAR(50) NOT NULL, payload JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

-- Insert with UUID INSERT INTO distributed_events (event_id, event_type, payload) VALUES (UUID_TO_BIN(UUID()), 'user_signup', '{"user_id": 123}');

-- Query with UUID SELECT * FROM distributed_events WHERE event_id = UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000');

Indexing Strategies Index Types Use B-tree indexes (default) for most queries Use FULLTEXT indexes for text search Use SPATIAL indexes for geographic data Consider covering indexes for frequently executed queries -- Composite index for common query patterns CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Covering index CREATE INDEX idx_orders_covering ON orders(customer_id, order_date, status, total_amount);

-- Fulltext index for search ALTER TABLE products ADD FULLTEXT INDEX ft_name_desc (name, description);

-- Search using fulltext SELECT * FROM products WHERE MATCH(name, description) AGAINST('wireless bluetooth' IN NATURAL LANGUAGE MODE);

Index Guidelines Index columns used in WHERE, JOIN, ORDER BY, and GROUP BY Place most selective columns first in composite indexes Avoid indexing low-cardinality columns alone Monitor and remove unused indexes -- Check index usage SELECT table_schema, table_name, index_name, seq_in_index, column_name, cardinality FROM information_schema.STATISTICS WHERE table_schema = 'your_database' ORDER BY table_name, index_name, seq_in_index;

Query Optimization EXPLAIN Analysis Use EXPLAIN to analyze query execution plans Look for full table scans (type: ALL) Check for proper index usage Monitor rows examined vs rows returned EXPLAIN FORMAT=JSON SELECT c.name, COUNT(o.order_id) AS order_count FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE c.created_at > '2024-01-01' GROUP BY c.customer_id;

Query Best Practices Avoid SELECT * in production code Use LIMIT for pagination Prefer JOINs over subqueries when possible Use prepared statements for repeated queries -- Efficient pagination SELECT order_id, order_date, total_amount FROM orders WHERE customer_id = ? ORDER BY order_date DESC LIMIT 20 OFFSET 0;

-- Keyset pagination (more efficient for large offsets) SELECT order_id, order_date, total_amount FROM orders WHERE customer_id = ? AND (order_date, order_id) < (?, ?) ORDER BY order_date DESC, order_id DESC LIMIT 20;

Avoiding Common Pitfalls -- Avoid: Function on indexed column SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- Preferred: Range comparison SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

-- Avoid: Implicit type conversion SELECT * FROM users WHERE user_id = '123'; -- user_id is INT

-- Preferred: Proper types SELECT * FROM users WHERE user_id = 123;

-- Avoid: LIKE with leading wildcard SELECT * FROM products WHERE name LIKE '%phone%';

-- Preferred: Fulltext search for text matching SELECT * FROM products WHERE MATCH(name) AGAINST('phone');

JSON Support Use JSON data type for semi-structured data (MySQL 5.7+) Create generated columns for frequently accessed JSON fields Use appropriate JSON functions for queries CREATE TABLE events ( event_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_type VARCHAR(50) NOT NULL, payload JSON NOT NULL, -- Generated column for indexing user_id INT UNSIGNED AS (payload->>'$.user_id') STORED, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (user_id) );

-- Query JSON data SELECT event_id, event_type, JSON_EXTRACT(payload, '$.action') AS action FROM events WHERE JSON_EXTRACT(payload, '$.user_id') = 123;

-- Or using -> operator SELECT * FROM events WHERE payload->'$.user_id' = 123;

Transaction Management Use InnoDB for transactional tables Keep transactions short to minimize lock contention Choose appropriate isolation level Handle deadlocks gracefully -- Transaction with error handling START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- Check for errors and commit or rollback COMMIT;

-- Set isolation level SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Replication and High Availability Read Replicas Direct read queries to replicas Use connection pooling with read/write splitting Monitor replication lag -- Check replication status SHOW SLAVE STATUS\G

-- Check replication lag SELECT TIMESTAMPDIFF(SECOND, MAX(LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP), NOW()) AS lag_seconds FROM performance_schema.replication_applier_status_by_worker;

Security Use strong passwords and secure connections (SSL/TLS) Apply principle of least privilege Use prepared statements to prevent SQL injection Audit sensitive operations -- Create user with limited privileges CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password'; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%'; FLUSH PRIVILEGES;

-- Require SSL ALTER USER 'app_user'@'%' REQUIRE SSL;

-- View user privileges SHOW GRANTS FOR 'app_user'@'%';

Maintenance Regular Maintenance Tasks -- Analyze tables for optimizer statistics ANALYZE TABLE orders, customers, products;

-- Optimize tables (reclaim space, defragment) OPTIMIZE TABLE orders;

-- Check table integrity CHECK TABLE orders;

Monitoring Queries -- Find slow queries SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

-- Current process list SHOW FULL PROCESSLIST;

-- InnoDB status SHOW ENGINE INNODB STATUS;

-- Table sizes SELECT table_name, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb, table_rows FROM information_schema.TABLES WHERE table_schema = 'your_database' ORDER BY data_length DESC;

Configuration Recommendations

my.cnf recommended settings

[mysqld]

InnoDB settings

innodb_buffer_pool_size = 70%_of_RAM innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT

Connection settings

max_connections = 500 wait_timeout = 300 interactive_timeout = 300

Query cache (disabled in MySQL 8.0+)

query_cache_type = 0

Slow query log

slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 2