Understanding MySQL Internals (e-book)

Understanding MySQL Internals (e-book) - Opis

Although MySQL's source code is open in the sense of being publicly available, it's essentially closed to you if you don't understand it. In this book, Sasha Pachev -- a former member of the MySQL Development Team -- provides a comprehensive tour of MySQL 5 that shows you how to figure out the inner workings of this powerful database. You'll go right to heart of the database to learn how data structures and convenience functions operate, how to add new storage engines and configuration options, and much more.
The core of Understanding MySQL Internals begins with an Architecture Overview that provides a brief introduction of how the different components of MySQL work together. You then learn the steps for setting up a working compilable copy of the code that you can change and test at your pleasure. Other sections of the book cover:Core server classes, structures, and APIThe communication protocol between the client and the serverConfiguration variables, the controls of the server; includes a tutorial on how to add your ownThread-based request handling -- understanding threads and how they are used in MySQLAn overview of MySQL storage enginesThe storage engine interface for integrating third-party storage enginesThe table lock managerThe parser and optimizer for improving MySQL's performanceIntegrating a transactional storage engine into MySQLThe internals of replicationUnderstanding MySQL Internals provides unprecedented opportunities for developers, DBAs, database application programmers, IT departments, software vendors, and computer science students to learn about (...) więcej the inner workings of this enterprise-proven database. With this book, you will soon reach a new level of comprehension regarding database development that will enable you to accomplish your goals. It's your guide to discovering and improving a great database. Spis treści:
Understanding MySQL Internals
SPECIAL OFFER: Upgrade this ebook with OReilly
A Note Regarding Supplemental Files
Preface
How This Book Is Organized
Who This Book Is For
Conventions Used in This Book
Using Code Examples
Comments and Questions
Safari Enabled
Acknowledgments
1. MySQL History and Architecturel
1.1. MySQL History
1.2. MySQL Architecture
1.2.1. Core Modules
1.2.2. Interaction of the Core Modules
1.2.3. Detailed Look at the Core Modules
1.2.3.1. Server Initialization Module
1.2.3.2. Connection Manager
1.2.3.3. Thread Manager
1.2.3.4. Connection Thread
1.2.3.5. User Authentication Module
1.2.3.6. Access Control Module
1.2.3.7. Parser
1.2.3.8. Command Dispatcher
1.2.3.9. Query Cache Module
1.2.3.10. Optimizer
1.2.3.11. Table Manager
1.2.3.12. Table Modification Modules
1.2.3.13. Table Maintenance Module
1.2.3.14. Status Reporting Module
1.2.3.15. Abstracted Storage Engine Interface (Table Handler)
1.2.3.16. Storage Engine Implementations (MyISAM, InnoDB, MEMORY, Berkeley DB)
1.2.3.17. Logging Module
1.2.3.18. Replication Master Module
1.2.3.19. Replication Slave Module
1.2.3.20. Client/Server Protocol API
1.2.3.21. Low-Level Network I/O API
1.2.3.22. Core API
2. Nuts and Bolts of Working with the MySQL Source Code
2.1. Unix Shell
2.2. BitKeeper
2.3. Preparing the System to Build MySQL from BitKeeper Tree
2.4. Building MySQL from BitKeeper Tree
2.5. Building from Source Distribution
2.6. Installing MySQL into a System Directory
2.7. Source Code Directory Layout
2.8. Preparing the System to Run MySQL in a Debugger
2.9. Debugger-Guided Source Tour
2.10. Basics of Working with gdb
2.11. Finding Things in the Source
2.12. Interesting Breakpoints and Variables
2.13. Making a Source Modification
2.14. Coding Guidelines
2.14.1. Stability
2.14.2. Portability
2.14.3. Performance
2.14.4. Style and Ease of Integration
2.15. Keeping Your BitKeeper Repository Up to Date
2.16. Submitting a Patch
3. Core Classes, Structures, Variables, and APIs
3.1. THD
3.2. NET
3.3. TABLE
3.4. Field
3.5. Utility API Calls
3.6. Preprocessor Macros
3.7. Global Variables
4. Client/Server Communication
4.1. Protocol Overview
4.2. Packet Format
4.3. Relationship Between MySQL Protocol and OS Layer
4.4. Authenticating Handshake
4.4.1. Authentication Protocol Security
4.4.2. Protocol Capabilities Bit Mask
4.5. Command Packet
4.6. Server Responses
4.6.1. Data Field
4.6.2. OK Packet
4.6.3. Error Packet
4.6.4. EOF Packet
4.6.5. Result Set Packets
5. Configuration Variables
5.1. Configuration Variables Tutorial
5.1.1. Configuration File and Command-Line Options
5.1.2. Internals of the Configuration Option Parsing
5.1.3. Example of Adding a New Configuration Option
5.2. Interesting Aspects of Specific Configuration Variables
5.2.1. big-tables
5.2.2. concurrent-insert
5.2.3. core-file
5.2.4. default-storage-engine
5.2.5. delay-key-write
5.2.6. ft_stopword_file
5.2.7. innodb_buffer_pool_size
5.2.8. innodb_flush_log_at_trx_commit
5.2.9. innodb_file_per_table
5.2.10. innodb_lock_wait_timeout
5.2.11. innodb_force_recovery
5.2.12. init-file
5.2.13. key_buffer_size
5.2.14. language
5.2.15. log
5.2.16. log-bin
5.2.17. log-isam
5.2.18. log-slow-queries
5.2.19. max_allowed_packet
5.2.20. max_connections
5.2.21. max_heap_table_size
5.2.22. max_join_size
5.2.23. max_sort_length
5.2.24. myisam-recover
5.2.25. query_cache_type
5.2.26. read_buffer_size
5.2.27. relay-log
5.2.28. server-id
5.2.29. skip-grant-tables
5.2.30. skip-stack-trace
5.2.31. slave-skip-errors
5.2.32. sort_buffer_size
5.2.33. sql-mode
5.2.34. table_cache
5.2.35. temp-pool
5.2.36. transaction-isolation
6. Thread-Based Request Handling
6.1. Threads Versus Processes
6.1.1. Advantages of Using Threads
6.1.2. Disadvantages of Using Threads
6.1.3. Advantages of Using Forked Processes
6.1.4. Disadvantages of Using Forked Processes
6.2. Implementation of Request Handling
6.2.1. Structures, Variables, Classes, and API
6.2.2. Execution Walk-Through
6.3. Thread Programming Issues
6.3.1. Standard C Library Calls
6.3.2. Mutually Exclusive Locks (Mutexes)
6.3.3. Read-Write Locks
6.3.4. Synchronization
6.3.5. Preemption
7. The Storage Engine Interface
7.1. The handler Class
7.1.1. handlerton
7.2. Adding a Custom Storage Engine to MySQL
7.2.1. Integration Instructions for Version 4.1
7.2.2. Integration Instructions for Version 5.1
8. Concurrent Access and Locking
8.1. Table Lock Manager
8.1.1. Read Lock Request
8.1.2. Write Lock Request
8.1.2.1. Storage engine interaction with the table lock manager
8.1.3. InnoDB Locking
8.1.3.1. Lock types
8.1.3.2. Record locking
8.1.3.4. Dealing with deadlocks
9. Parser and Optimizer
9.1. Parser
9.1.1. Lexical Scanner
9.1.2. Grammar Rules Module
9.1.3. Parse Tree
9.2. Optimizer
9.2.1. Basics of the Optimizer Algorithm
9.2.2. Using EXPLAIN to Understand the Optimizer
9.2.2.1. Understanding the output of EXPLAIN
9.2.2.2. Select types
9.2.2.3. Record access types
9.2.2.4. Extra field
9.2.3. Range Optimizer
9.2.3.1. Range
9.2.3.2. Index_merge
9.2.3.3. Range_desc
9.2.3.4. Fulltext
9.2.3.5. ROR_intersect
9.2.3.6. ROR_union
9.2.3.7. Group_min_max
9.2.4. Subquery Optimization
9.2.5. Core Optimizer Classes and Structures
9.2.5.1. JOIN
9.2.5.2. JOIN_TAB
9.2.5.3. select_result
9.2.6. SELECT Parse Tree
9.2.6.1. Execution of a SELECT on the code level
10. Storage Engines
10.1. Shared Aspects of Architecture
10.1.1. MyISAM
10.1.2. MyISAM Architecture
10.1.2.1. Datafile
10.1.2.2. Index file
10.1.3. MyISAM Key Types
10.1.3.1. B-tree keys
10.1.3.2. Full-text keys
10.1.3.4. Spatial keys
10.2. InnoDB
10.3. Memory (Heap)
10.4. MyISAM Merge
10.5. NDB
10.6. Archive
10.7. Federated
11. Transactions
11.1. Overview of Transactional Storage Engine Implementation
11.2. Implementing the handler Subclass
11.3. Defining the handlerton
11.4. Working with the Query Cache
11.5. Working with the Replication Binary Log
11.6. Avoiding Deadlocks
12. Replication
12.1. Overview
12.2. Statement-Based Versus Row-Based Replication
12.3. Two-Threaded Slave
12.4. Multi-Master
12.5. SQL Commands to Help Understand Replication
12.6. Binary Log Format
12.7. Creating a Custom Replication Utility
About the Author
Colophon
SPECIAL OFFER: Upgrade this ebook with OReilly mniej