Execution plans, indexing, normalization, locking, partitioning and caching.
An execution plan shows how the database will execute a query. It helps identify performance bottlenecks and optimization opportunities.
-- MySQL EXPLAIN SELECT * FROM employees WHERE department = 'Sales'; -- PostgreSQL EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Sales'; -- SQL Server SET SHOWPLAN_TEXT ON; GO SELECT * FROM employees WHERE department = 'Sales'; GO SET SHOWPLAN_TEXT OFF;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 10.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
Normalization organizes data to reduce redundancy, while denormalization intentionally adds redundancy to improve performance.
-- Separate tables for related data
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE order_items (
order_item_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-- Combined table for faster reads
CREATE TABLE order_details (
order_id INT,
customer_name VARCHAR(100),
customer_email VARCHAR(100),
order_date DATE,
product_name VARCHAR(100),
quantity INT,
unit_price DECIMAL(10,2),
total_price DECIMAL(10,2) -- Redundant but pre-calculated
);
Database locking prevents multiple transactions from modifying the same data simultaneously, ensuring data consistency.
-- SQL Server explicit locking BEGIN TRANSACTION; -- Get exclusive lock on table SELECT * FROM employees WITH (TABLOCKX); -- Or row-level lock SELECT * FROM employees WITH (ROWLOCK, XLOCK) WHERE id = 1; COMMIT; -- MySQL locking START TRANSACTION; -- Lock tables explicitly LOCK TABLES employees WRITE, departments READ; -- Perform operations UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering'; -- Release locks UNLOCK TABLES; COMMIT; -- PostgreSQL advisory locks BEGIN; SELECT pg_advisory_lock(123); -- Application-level lock -- Critical section UPDATE accounts SET balance = balance - 100 WHERE id = 1; SELECT pg_advisory_unlock(123); COMMIT;
Query caching stores the results of SQL queries so subsequent identical queries can be served faster without re-execution.
-- Check query cache status SHOW VARIABLES LIKE 'query_cache%'; -- Typical configuration in my.cnf [mysqld] query_cache_type = 1 -- Enable cache query_cache_size = 64M -- Cache size query_cache_limit = 2M -- Max result size to cache query_cache_min_res_unit = 4K -- Minimum allocation unit -- Check cache performance SHOW STATUS LIKE 'Qcache%'; -- Force query to use cache (MySQL 8+ removed query cache) SELECT SQL_CACHE * FROM employees WHERE department = 'Sales'; -- Force query to NOT use cache SELECT SQL_NO_CACHE * FROM employees WHERE department = 'Sales';
-- PostgreSQL relies on shared buffers and OS cache
-- Check buffer cache hit ratio
SELECT
sum(blks_hit) * 100.0 / (sum(blks_hit) + sum(blks_read)) as cache_hit_ratio
FROM pg_stat_database;
-- Use pg_prewarm to load data into cache
CREATE EXTENSION pg_prewarm;
SELECT pg_prewarm('employees');
-- Materialized views as manual cache
CREATE MATERIALIZED VIEW mv_employee_stats AS
SELECT department, COUNT(*), AVG(salary)
FROM employees
GROUP BY department;
-- Refresh cache periodically
REFRESH MATERIALIZED VIEW mv_employee_stats;
Partitioning divides a large table into smaller, more manageable pieces while maintaining logical unity. Each partition can be stored and accessed separately.
-- Create partitioned table
CREATE TABLE sales (
sale_id SERIAL,
sale_date DATE NOT NULL,
amount DECIMAL(10,2),
region VARCHAR(50)
) PARTITION BY RANGE (sale_date);
-- Create partitions for each year
CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE sales_2024 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE sales_2025 PARTITION OF sales
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
-- Create default partition
CREATE TABLE sales_default PARTITION OF sales DEFAULT;
-- Partition by region
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
region VARCHAR(50),
email VARCHAR(100)
)
PARTITION BY LIST COLUMNS(region) (
PARTITION p_north VALUES IN ('NY', 'NJ', 'CT'),
PARTITION p_south VALUES IN ('TX', 'FL', 'GA'),
PARTITION p_west VALUES IN ('CA', 'OR', 'WA'),
PARTITION p_other VALUES IN (DEFAULT)
);
A covering index includes all columns needed for a query, allowing the database to satisfy the query entirely from the index without accessing the table.
-- Query needing multiple columns
SELECT employee_id, name, department, salary
FROM employees
WHERE department = 'Engineering'
AND hire_date > '2023-01-01';
-- Non-covering index (requires table lookup)
CREATE INDEX idx_dept_hire ON employees(department, hire_date);
-- Covering index (includes all needed columns)
CREATE INDEX idx_covering ON employees(department, hire_date)
INCLUDE (employee_id, name, salary);
-- Or include columns in index key
CREATE INDEX idx_covering_complete ON employees(
department,
hire_date,
employee_id,
name,
salary
);
-- Without covering index: 1. Use idx_dept_hire to find matching rows 2. For each match, read table (heap/clustered index) to get employee_id, name, salary 3. Return results -- With covering index: 1. Use idx_covering to find matching rows 2. All needed columns are in index 3. Return results directly from index 4. No table access needed!
Connection pooling maintains a cache of database connections that can be reused by multiple applications, reducing overhead of establishing new connections.
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("user");
config.setPassword("password");
config.setMaximumPoolSize(20); // Max connections in pool
config.setMinimumIdle(10); // Minimum idle connections
config.setConnectionTimeout(30000); // 30 seconds timeout
config.setIdleTimeout(600000); // 10 minutes idle timeout
config.setMaxLifetime(1800000); // 30 minutes max lifetime
config.setConnectionTestQuery("SELECT 1");
HikariDataSource dataSource = new HikariDataSource(config);
import psycopg2
from psycopg2 import pool
# Create connection pool
connection_pool = psycopg2.pool.SimpleConnectionPool(
5, 20, # minconn, maxconn
host="localhost",
database="mydb",
user="user",
password="password",
port=5432
)
# Get connection from pool
conn = connection_pool.getconn()
try:
cur = conn.cursor()
cur.execute("SELECT * FROM employees")
results = cur.fetchall()
finally:
# Return connection to pool (don't close!)
connection_pool.putconn(conn)
Connection multiplexing allows multiple logical client connections to share a single physical database connection, reducing connection overhead.
-- Traditional: Each client has dedicated connection Client1 → Connection1 → Database Client2 → Connection2 → Database Client3 → Connection3 → Database -- 3 physical connections, each with memory/CPU overhead -- Multiplexed: Multiple clients share connections Client1 → \ Client2 → → Multiplexer → Single Connection → Database Client3 → / -- 3 logical connections, 1 physical connection
// Pseudo-code for connection multiplexer
class ConnectionMultiplexer {
private ConnectionPool physicalPool;
private Map virtualConnections;
executeQuery(clientId, sql) {
// Get or create virtual connection for client
VirtualConnection vconn = virtualConnections.get(clientId);
if (vconn == null) {
vconn = new VirtualConnection(clientId);
virtualConnections.put(clientId, vconn);
}
// Borrow physical connection from pool
PhysicalConnection pconn = physicalPool.borrow();
try {
// Execute on physical connection
ResultSet rs = pconn.execute(sql);
// Map results back to virtual connection
return vconn.processResults(rs);
} finally {
// Return physical connection to pool
physicalPool.return(pconn);
}
}
}
Parameter sniffing occurs when SQL Server creates an execution plan optimized for the first parameter values, which may be suboptimal for subsequent calls with different values.
-- Stored procedure with parameter
CREATE PROCEDURE GetOrdersByStatus
@status VARCHAR(20)
AS
BEGIN
SELECT * FROM orders
WHERE order_status = @status
ORDER BY order_date DESC;
END;
-- First execution (with 'Completed' - few rows)
EXEC GetOrdersByStatus @status = 'Completed';
-- Creates plan optimized for few rows (uses index seek)
-- Second execution (with 'Pending' - many rows)
EXEC GetOrdersByStatus @status = 'Pending';
-- Uses same plan (seek), but table scan would be better
-- Result: Poor performance for 'Pending' status
-- 1. Use OPTION (RECOMPILE) - recompile each time
CREATE PROCEDURE GetOrdersByStatus
@status VARCHAR(20)
AS
BEGIN
SELECT * FROM orders
WHERE order_status = @status
ORDER BY order_date DESC
OPTION (RECOMPILE); -- New plan each execution
END;
-- 2. Use OPTIMIZE FOR UNKNOWN
CREATE PROCEDURE GetOrdersByStatus
@status VARCHAR(20)
AS
BEGIN
SELECT * FROM orders
WHERE order_status = @status
ORDER BY order_date DESC
OPTION (OPTIMIZE FOR UNKNOWN); -- Use average statistics
END;
-- 3. Use OPTIMIZE FOR specific value
CREATE PROCEDURE GetOrdersByStatus
@status VARCHAR(20)
AS
BEGIN
SELECT * FROM orders
WHERE order_status = @status
ORDER BY order_date DESC
OPTION (OPTIMIZE FOR (@status = 'Pending')); -- Optimize for common case
END;
-- 4. Use local variables (disables sniffing)
CREATE PROCEDURE GetOrdersByStatus
@status VARCHAR(20)
AS
BEGIN
DECLARE @local_status VARCHAR(20) = @status;
SELECT * FROM orders
WHERE order_status = @local_status
ORDER BY order_date DESC;
END;
Failover automatically switches to a standby database when the primary fails, ensuring high availability and minimal downtime.
-- Configure database mirroring
ALTER DATABASE MyDB SET PARTNER = 'TCP://primary-server:5022';
ALTER DATABASE MyDB SET WITNESS = 'TCP://witness-server:5023';
-- On secondary server
ALTER DATABASE MyDB SET PARTNER = 'TCP://secondary-server:5022';
-- Check mirroring status
SELECT
database_name,
mirroring_state_desc,
mirroring_role_desc,
mirroring_safety_level_desc
FROM sys.database_mirroring
WHERE mirroring_state IS NOT NULL;
-- Manual failover
ALTER DATABASE MyDB SET PARTNER FAILOVER;
-- Primary server configuration (postgresql.conf) wal_level = replica max_wal_senders = 10 wal_keep_size = 1GB -- Primary pg_hba.conf host replication repuser secondary-ip/32 md5 -- Secondary setup # Create base backup pg_basebackup -h primary-host -D /var/lib/postgresql/data -U repuser -v -P -- Secondary recovery.conf (PostgreSQL 12+ uses postgresql.conf) primary_conninfo = 'host=primary-host user=repuser password=secret' promote_trigger_file = '/tmp/promote_to_primary' -- Check replication status on primary SELECT * FROM pg_stat_replication; -- Promote secondary to primary # On secondary server touch /tmp/promote_to_primary # Or: pg_ctl promote -D /var/lib/postgresql/data
-- JDBC connection string with failover
jdbc:postgresql://primary:5432,secondary:5432/mydb
?targetServerType=primary
&loadBalanceHosts=true
&hostRecheckSeconds=10
-- Application logic
try {
// Connect to primary
executeQuery();
} catch (SQLException e) {
// Connection failed, retry with secondary
updateConnectionString();
executeQuery();
}