-
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;
-
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;
-
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');
-
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 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;
-
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;
-
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;
-
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;
-
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;
-
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;
-
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'@'%';
