MySQL Database

  1. MySQL Architecture
    1. Client/Server Model
      1. Architecture Overview
        1. Multi-Tier Architecture
          1. Client-Server Communication
            1. Protocol Stack
            2. Client Applications
              1. Native MySQL Clients
                1. Third-Party Applications
                  1. Programming Language Connectors
                  2. Server Process
                    1. MySQL Daemon (mysqld)
                      1. Process Lifecycle
                        1. Resource Management
                        2. Communication Protocols
                          1. MySQL Protocol
                            1. TCP/IP Communication
                              1. Named Pipes and Sockets
                            2. MySQL Server Processes
                              1. The mysqld Daemon
                                1. Process Architecture
                                  1. Startup Sequence
                                    1. Configuration Loading
                                      1. Storage Engine Initialization
                                        1. Network Interface Setup
                                        2. Shutdown Process
                                          1. Graceful Shutdown
                                            1. Emergency Shutdown
                                              1. Cleanup Operations
                                            2. Process Management
                                              1. Process Monitoring
                                                1. Resource Allocation
                                                  1. Thread Management
                                                2. Logical Architecture Layers
                                                  1. Connection Layer
                                                    1. Connection Handling
                                                      1. Client Authentication
                                                        1. Connection Pooling
                                                          1. Session Management
                                                          2. Threading Model
                                                            1. One Thread Per Connection
                                                              1. Thread Pool Plugin
                                                                1. Connection Limits
                                                              2. SQL Layer
                                                                1. SQL Parser
                                                                  1. Syntax Analysis
                                                                    1. Semantic Analysis
                                                                      1. Parse Tree Generation
                                                                      2. Query Optimizer
                                                                        1. Cost-Based Optimization
                                                                          1. Query Execution Plans
                                                                            1. Statistics Usage
                                                                            2. Query Execution
                                                                              1. Execution Engine
                                                                                1. Result Set Generation
                                                                                  1. Error Handling
                                                                                2. Storage Engine Layer
                                                                                  1. Storage Engine API
                                                                                    1. Handler Interface
                                                                                      1. Engine Registration
                                                                                        1. Plugin Architecture
                                                                                        2. Engine Selection
                                                                                          1. Per-Table Engine Choice
                                                                                            1. Default Engine Configuration
                                                                                              1. Engine Capabilities
                                                                                          2. Caching and Buffering
                                                                                            1. Query Cache
                                                                                              1. Cache Mechanism
                                                                                                1. Cache Invalidation
                                                                                                  1. Performance Considerations
                                                                                                  2. Buffer Pool (InnoDB)
                                                                                                    1. Memory Management
                                                                                                      1. Page Caching
                                                                                                        1. LRU Algorithm
                                                                                                        2. Key Buffer (MyISAM)
                                                                                                          1. Index Caching
                                                                                                            1. Buffer Management
                                                                                                              1. Configuration Options
                                                                                                              2. Other Caches
                                                                                                                1. Table Cache
                                                                                                                  1. Thread Cache
                                                                                                                    1. Hostname Cache
                                                                                                                  2. Storage Engines
                                                                                                                    1. Storage Engine Overview
                                                                                                                      1. Pluggable Architecture
                                                                                                                        1. Engine Comparison Matrix
                                                                                                                          1. Selection Criteria
                                                                                                                          2. InnoDB Storage Engine
                                                                                                                            1. ACID Compliance
                                                                                                                              1. Transaction Support
                                                                                                                                1. Crash Recovery
                                                                                                                                  1. Redo and Undo Logs
                                                                                                                                  2. Locking Mechanisms
                                                                                                                                    1. Row-Level Locking
                                                                                                                                      1. Multi-Version Concurrency Control
                                                                                                                                        1. Deadlock Detection
                                                                                                                                        2. Foreign Key Support
                                                                                                                                          1. Referential Integrity
                                                                                                                                            1. Cascade Operations
                                                                                                                                              1. Constraint Validation
                                                                                                                                              2. Performance Features
                                                                                                                                                1. Clustered Indexes
                                                                                                                                                  1. Adaptive Hash Index
                                                                                                                                                    1. Change Buffering
                                                                                                                                                  2. MyISAM Storage Engine
                                                                                                                                                    1. Table Structure
                                                                                                                                                      1. Table-Level Locking
                                                                                                                                                        1. Non-Transactional Nature
                                                                                                                                                          1. Compact Storage Format
                                                                                                                                                          2. Indexing Features
                                                                                                                                                            1. B-Tree Indexes
                                                                                                                                                              1. Full-Text Search Indexes
                                                                                                                                                                1. Spatial Indexes
                                                                                                                                                                2. Use Cases and Limitations
                                                                                                                                                                  1. Read-Heavy Workloads
                                                                                                                                                                    1. Bulk Data Loading
                                                                                                                                                                      1. Concurrency Limitations
                                                                                                                                                                    2. Alternative Storage Engines
                                                                                                                                                                      1. MEMORY Engine
                                                                                                                                                                        1. In-Memory Storage
                                                                                                                                                                          1. Temporary Tables
                                                                                                                                                                            1. Performance Characteristics
                                                                                                                                                                            2. CSV Engine
                                                                                                                                                                              1. CSV File Storage
                                                                                                                                                                                1. Data Exchange
                                                                                                                                                                                  1. Limitations and Use Cases
                                                                                                                                                                                  2. ARCHIVE Engine
                                                                                                                                                                                    1. Compressed Storage
                                                                                                                                                                                      1. Insert-Only Tables
                                                                                                                                                                                        1. Archival Applications
                                                                                                                                                                                        2. FEDERATED Engine
                                                                                                                                                                                          1. Remote Table Access
                                                                                                                                                                                            1. Distributed Queries
                                                                                                                                                                                              1. Configuration Requirements

                                                                                                                                                                                        Previous

                                                                                                                                                                                        2. Installation and Configuration

                                                                                                                                                                                        Go to top

                                                                                                                                                                                        Next

                                                                                                                                                                                        4. SQL Fundamentals: Data Definition Language (DDL)