Performance Analysis Tools

Database Server Optimization Steps

When approaching database optimization, follow these key steps:

1. Analyze current performance metrics
2. Identify bottlenecks
3. Implement targeted optimizations

Viewing System Performance Parameters

SHOW [GLOBAL|SESSION] STATUS LIKE 'parameter_name';

Key metrics to monitor:

• Connections: Server connection count
• Slow_queries: Slow query occurrences
• Innodb_rows_read: Rows retrieved
• Com_select: Select operation frequency

Query Cost Analysis

SHOW STATUS LIKE 'last_query_cost';

Example comparing query costs:

-- Single row lookup
SELECT student_id FROM student_data WHERE id = 900001;

-- Range lookup
SELECT student_id FROM student_data WHERE id BETWEEN 900001 AND 900100;

Slow Query Logging

Enable slow query logging:

SET GLOBAL slow_query_log='ON';
SET GLOBAL long_query_time=1;

Analyze slow queries with:

mysqldumpslow -s t -t 5 /path/to/slow-query.log

Query Execution Analysis

EXPLAIN Command

EXPLAIN SELECT * FROM users WHERE username = 'admin';

Key columns in EXPLAIN output:

Optimizer Tracing

SET optimizer_trace="enabled=on";
SELECT * FROM products WHERE price > 100;
SELECT * FROM information_schema.optimizer_trace\G

Index Optimization

Common Index Pitfalls

• Functions on indexed columns: WHERE UPPER(name) = 'SMITH'
• Implicit type conversion: WHERE user_id = '123' (when user_id is INT)
• Leading wildcards: WHERE description LIKE '%search%'

Compsoite Index Best Practices

Follow left-prefix rule for composite indexes:

-- Good for index (a,b,c)
WHERE a = 1 AND b = 2
WHERE a = 1 AND b > 2 AND c = 3

-- Won't use full index
WHERE b = 2
WHERE a = 1 AND c = 3

Covering Indexes

When indexes contain all columns needed for query:

-- If index exists on (user_id, status)
SELECT user_id, status FROM orders WHERE user_id = 1001;

Query Optimization

Join Optimization

Prefer indexed joins and small tables driving large tables:

-- Better performance
SELECT * FROM small_table s JOIN large_table l ON s.id = l.small_id;

-- Worse performance
SELECT * FROM large_table l JOIN small_table s ON l.small_id = s.id;

Subquery Alternatives

Replace correlated subqueries with joins when possible:

-- Instead of
SELECT * FROM orders WHERE customer_id IN 
  (SELECT id FROM customers WHERE status = 'active');

-- Use
SELECT o.* FROM orders o JOIN customers c 
  ON o.customer_id = c.id WHERE c.status = 'active';

Pagination Optimization

Optimize large offset queries:

-- Instead of
SELECT * FROM products LIMIT 1000000, 20;

-- Use
SELECT p.* FROM products p JOIN 
  (SELECT id FROM products ORDER BY id LIMIT 1000000, 20) tmp
  ON p.id = tmp.id;

Schema Design Considerations

Primary Key Selection

Avoid auto-increment for distributed systems:

-- Recommended approach
CREATE TABLE transactions (
  id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), TRUE)),
  amount DECIMAL(10,2),
  created_at TIMESTAMP
);