Databases

1. Database Performance
   1. Query Optimization
      1. Execution Plan Analysis
         1. Understanding Execution Plans
         2. Identifying Bottlenecks
         3. Cost-Based Optimization
      2. Query Rewriting
         1. Subquery Unnesting
         2. Predicate Pushing
         3. Join Reordering
      3. Statistics Gathering
         1. Importance of Accurate Statistics
         2. Automated Statistics Collection
         3. Manual Statistics Update
   2. Indexing Strategies
      1. Types of Indexes
         1. B-Tree Indexes
         2. Hash Indexes
         3. Bitmap Indexes
         4. Full-Text Indexes
         5. Spatial Indexes
      2. Index Design
         1. Choosing Columns for Indexing
         2. Covering Indexes and Include Columns
         3. Database Index Maintenance
      3. Impact on Write Performance
         1. Balanced Indexing Strategies
         2. Trade-offs Between Read and Write Performance
   3. Caching Techniques
      1. Database-Level Caching
         1. Use of In-Memory Databases
         2. Buffer Cache Management
      2. Application-Level Caching
         1. Query Result Caching
         2. Object Caching
      3. Distributed Caching
         1. Use of Redis and Memcached
         2. Cache Consistency and Invalidation Strategies
   4. Load Balancing
      1. Horizontal Scaling
         1. Use of Database Clusters
         2. Database Replication and Load Balancing
      2. Vertical Scaling
         1. Hardware Upgrades and Optimization
      3. Load Balancer Configuration
         1. Session Persistence
         2. Health Checking and Failover
   5. Database Sharding
      1. Sharding Strategies
         1. Range-Based Sharding
         2. Hash-Based Sharding
         3. Directory-Based Sharding
      2. Shard Key Selection
         1. Characteristics of a Good Shard Key
         2. Avoiding Hot Spots
      3. Managing Sharded Environments
         1. Balancing Data and Traffic
         2. Re-Sharding Strategies
         3. Cross-Shard Query Handling
   6. Performance Monitoring and Analysis
      1. Key Performance Indicators (KPIs)
         1. Latency and Throughput
         2. Transaction Per Second (TPS)
         3. Query Execution Time
      2. Monitoring Tools
         1. Built-in Monitoring Features
         2. Third-Party Monitoring Solutions
      3. Performance Tuning and Iteration
         1. Identifying Slow Queries
         2. Dynamic Performance Tuning
         3. Ongoing Performance Assessment
   7. Hardware and Infrastructure Considerations
      1. Storage Solutions
         1. SSD vs. HDD for Database Storage
         2. Network Attached Storage (NAS) vs. Direct Attached Storage (DAS)
      2. CPU & Memory Optimization
         1. Sizing for Database Workloads
         2. NUMA Considerations
      3. Network Infrastructure
         1. Network Latency and Throughput Optimization
         2. Use of High-Speed Interconnects
   8. Concurrency and Transactions
      1. Locking and Concurrency Control
         1. Pessimistic vs. Optimistic Locking
         2. Read/Write Lock Mechanisms
      2. Transaction Isolation Levels
         1. Trade-offs of Different Isolation Levels
         2. Implementation in Various DBMSs
      3. Deadlock Detection and Resolution
         1. Identification of Deadlocks
         2. Techniques for Deadlock Resolution