Expert MySQL.pdf

Expert MySQL.pdfBell741-9FRONTfm Page ii Friday, December 22 2006 2: 40 PMExpert MySQLCopyright o 2007 by Charles A BellAll rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,clemechanical, including photocopying,information stolsystem, without the prior written permission of the copyright owner and the publisherISBN-13(pbk):978-1-59059-741-5ISBN-10(pbk):1-59059-741-9Printed and bound in the united states of america 987654321Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrenceof a trademarked name, we use the names only in an editorial fashion and to the henefit of the trademarkowner, with no intention of infringement of the trademarkLead Editor: Jason GilmoreTechnical Reviewers: Mike Kruckenberg, Lorraine Parker, Mikael RonstromEditorial Board: Steve Anglin, Ewan Buckingham, Gary Cornell, Jason Gilmore, Jonathan GennickJonathan Hassell, James Huddleston, Chris Mills, Matthew Moodie, Dominic Shakeshaft, Jim SumserKeir Thomas. Matt WadeMaBrown collCopy Edit Manager: Nicole FloresCopy Editor: Liz WelchAssistant Production Director: Kari Brooks-CoponyProduction editor: Katie stenceCompositor: Susan GlinertProofreader Nancy riddioughIndexer: Valerie PerryArtist: April milneCover Designer: Kurt KramesManufacturing Director: Tom DebolskiDistributed to the book trade worldwide by springer-Verlag New York, Inc, 233 Spring Street, 6th Floor,NewYorkNy10013.Phone1-800-springer,fax201-348-4505,e-mailorders-ny@springer-sbm.com,orvisithttp://www.sprIngeronline.comFor information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley, CA94710.Phone510-549-5930,fax510-549-5939,e-mailinfo@apress.comorvisithttp://www.aprEss.comThe information in this bookis distributed on an"as is"basis, without warranty. Although every precautionhy the information contained in this worf in e shas been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability toany person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectlyThesourcecodeforthisbookisavailabletoreadersathttp://www.apress.comintheSourceCude/Bell 741-9FRONT im Page iii Friday, December 22, 2006 2: 40 PMContents at a GlanceAbout the author。,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.XlAcknowledgmentsIntroductionPART 1 Getting Started withMySQL DevelopmentCHAPTER 1 MySQL and the open Source Revolution3CHAPTER 2 The Anatomy of a Database System25CHAPTER 3 A Tour of the MySQL Source CodeCHaPter 4 Test-Driven MySQL DevelopmentParT 2 EXtending mysQlCHAPTER 5DebuggingCHAPTER 6 Embedded my SQL.193CHAPTER 7 Building Your Own Storage Engine.....,,.......255CHAPTER 8 Adding Functions and Commands to MySQL,,357part 3 Advanced database internalsCHAPTER9 Database System Internals393CHAPTER 10 Internal Query Representation................403CHAPTER 11 Query Optimization439CHAPTER 12 Query Execution..,.,....,489APPENDIX,,,,,,,,,,,,,,,,,,,,,,,,,,,,535INDEX..545iIiBell_ 741-9FRONT fm Page iv Friday, December 22, 2006 2: 40 PM●Bell 741-9FRONT m Page v Friday, December 22, 2006 2: 40 PMContentsAbout the author..XAcknowledgmentsIntroductionPART 1 Getting Started withMySQL DevelopmentCHaPTER 1 MySQL and the open Source Revolution.........3What ls Open Source Software4Why Use Open Source Software?is open source really a threat to commercial sottware?8Legal Issues and the GNU Manifesto10Let the revolution continue!12Developing with MySQLWhy Modify mySQL15What Can You modify in mySQL? Are There limits?MySQL Licensing Explained17So, Can You Modify MysQl or Not?19Guidelines for Modifying MySQL20A Real-World Example: TiVoSummarCHAPTER 2 The anatomy of a Database SystemDatabase System Architectures25Types of Database SystemsObject-Oriented Database Systems26Object-Relational Database Systems26Relational Database Systems28Bell_ 741-9FRONT fm Page vi Friday, December 22, 2006 2: 40 PMCONTENTSRelational Database System ArchitectureClient applicationsQuery InterfaceQuery ProcessingQuery Optimizer .............,,36Internal Representation of Queries....,,,....,,..39Query ExecutionFile accesQuery ResultsRelational database architecture summarThe mySQL Database SystemMySQL System Architecture45SQL Interface46ParserQuery optimizer48Query ExecutionQuery cacheCache and BuffersFile access via Pluggable storage engines53SummarCHAPTER 3 A Tour of the my SQL Source Code...63Getting StartedUnderstanding the licensing Options.63Getting the Source Code64The mysQL Source Code70Getting startedThe mainl function73Handling connections and creating Threads.. ........ 76Parsing the QueryPreparing the Query for Optimization90Optimizing the Query........1EXecuting the QuerySupporting Libraries99Important classes and structuresBell741-9FRONT im Page vii Friday, December 22, 2006 2: 40 PMCONTENTSCoding GuidelinesGeneral guidelines106Documentation.106Functions and parametersNaming Conventions,109Spacing and Indenting ....................................110Documentation UtilitiesKeeping an Engineering Logbook..113Tracking your changes114Building the System for the First Time116Summary119CHAPTER 4 Test-Driven MySQL DevelopmentBackgroundWhy Test?121Benchmarking ..............Profiling126Introducing Software Testing,,,,,,,128Functional Testing VS. Defect Testing128MySQL Testing...,,,,,.133Using the MySQL Test Suite133MySQL BenchmarkingMySQL ProfilingSummary150PART 2 Extending mysQLCHAPTER 5 Debugging153Debugging Explained,,,,,,,,,,153Debugging techniques154Basic Process...155Inline Debugging Statements,,,,,157Error HandlersExternal Debuggers161Bell 741-9FRONT. m Page viii Friday, December 22, 2006 2: 40 PMCONTENTSDebugging MySQL170Inline Debugging statements171Error handlersDebugging in Linux.....177Debugging in Windows,187Summary ...................................................192CHAPTER 6 Embedded MySQL193Building embedded applications11What is an embedded system?Types of Embedded SystemsEmbedded database SystemsEmbedding MysQl195Methods of Embedding MySQL197Resource Requirements....,....,..,.....198Security Concerns,,,,,,,,199Advantages of MySQL EmbeddingLimitations of MySQL Embedding,,,199The MySQL C APl200Getting Started201Most Commonly Used FunctionsCreating an Embedded Server,,,,,,,202Initializing the Server...................203Setting Options.......,...........,. 204Connecting to the Server205Running Qi206Retrieving Results.207Clean208Disconnecting from and Finalizing the server......... 208Putting It All Together..209Error Handling210Building Embedded MySQL Applications.............210Compiling the Library (ibmysqld.211What about Debugging212What about the data?.214Creating a Basic Embedded Server215What about Error Handling?............... 224Embedded Server Application,,,,225Summary.…,,,,,,,..252Bell 741-9FRONT. m Page ix Friday, December 22, 2006 2: 40 PMCONTENTSCHAPTER 7 Building Your Own Storage Engine255MySQL Pluggable Storage Engine Overview...,. .......... 255Basic process....257Source files needed量重,,258Unexpected help,,,,259The handlerton,,,,,259The handler classA Brief tour of a mysQL Storage Engine267The spartan Storage Engi268LoW-Level l o classesGetting sta29Stage 1: Stubbing the Engine................ 296Stage 2: Working with tables309Stage 3: Reading and Writing Data.....,....,..317Stage 4: Updating and Deleting Data322Stage 5: Indexing the data330Stage 6: Adding Transaction Support.Summar,,,,,356CHAPTER 8 Adding functions and commands to mySQL..,..357Adding User-Defined Functions ................. 357CREATE FUNCTION SyntaxDROP FUNCTION Syntax....,,.........,,.. 358Creating a User-Defined Library,,,358Adding a New User-Defined Function■■■■Adding Native Functions369Generating the Lexical hash on Windows372Generating the Lexical Hash on Linux...,.......373Compiling and Testing the New Native Function.......373Adding SQL CommandsAdding to the Information Schema.......,,...... 383Summary...∴.,....389