没有合适的资源?快使用搜索试试~ 我知道了~
首页Beginning MySQL Database Design and Optimization.pdf
资源详情
资源评论
资源推荐

Beginning MySQL Database
Design and Optimization:
From Novice to Professional
JON STEPHENS AND CHAD RUSSELL
3324FM.qxd 9/21/04 12:24 PM Page i

Beginning MySQL Database Design and Optimization: From Novice to Professional
Copyright © 2004 by Jon Stephens and Chad Russell
All rights reserved. No part of this work may be reproduced or transmitted in any form or by
any means, electronic or mechanical, including photocopying, recording, or by any information
storage or retrieval system, without the prior written permission of the copyright owner and
the publisher.
ISBN (pbk): 1-59059-332-4
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, we use the names only in an editorial fashion and to the
benefit of the trademark owner, with no intention of infringement of the trademark.
Lead Editors: Dominic Shakeshaft and Jason Gilmore
Technical Reviewer: Mike Hillyer
Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell, Tony Davis,
Jason Gilmore, Chris Mills, Dominic Shakeshaft, Jim Sumser
Project Manager: Tracy Brown Collins
Copy Edit Manager: Nicole LeClerc
Copy Editors: Ami Knox and Marilyn Smith
Production Manager: Kari Brooks-Copony
Production Editor: Katie Stence
Compositor: Dina Quan
Proofreader: Christy Wagner
Indexer: Kevin Broccoli
Artist: Kinetic Publishing Services, LLC
Cover Designer: Kurt Krames
Manufacturing Manager: Tom Debolski
Distributed to the book trade in the United States by Springer-Verlag New York, Inc., 233 Spring
Street, 6th Floor, New York, NY 10013, and outside the United States by Springer-Verlag GmbH &
Co. KG, Tiergartenstr. 17, 69112 Heidelberg, Germany.
In the United States: phone 1-800-SPRINGER, fax 201-348-4505, e-mail orders@springer-ny.com,
or visit http://www.springer-ny.com. Outside the United States: fax +49 6221 345229, e-mail
orders@springer.de, or visit http://www.springer.de.
For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219,
Berkeley, CA 94710. Phone 510-549-5930, fax 510-549-5939, e-mail info@apress.com, or visit
http://www.apress.com.
The information in this book is distributed on an “as is” basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall
have any liability to any person or entity with respect to any loss or damage caused or alleged to
be caused directly or indirectly by the information contained in this work.
The source code for this book is available to readers at http://www.apress.com in the
Downloads section. You will need to answer questions pertaining to this book in order
to successfully download the code.
3324FM.qxd 9/21/04 12:24 PM Page ii

Contents at a Glance
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .ix
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xi
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiii
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xv
Chapter 1 Review of MySQL Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1
Chapter 2 MySQL Column and Table Types . . . . . . . . . . . . . . . . . . . . . .45
Chapter 3 Keys, Indexes, and Normalization . . . . . . . . . . . . . . . .113
Chapter 4 Optimizing Queries with Operators,
Branching, and Functions
. . . . . . . . . . . . . . . . . . . . . . . . .171
Chapter 5 Joins, Temporary Tables, and Transactions . . . . . .239
Chapter 6 Finding the Bottlenecks . . . . . . . . . . . . . . . . . . . . . . . . . .273
Chapter 7 MySQL Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .315
Chapter 8 Looking Ahead . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .417
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .489
iii
3324FM.qxd 9/21/04 12:24 PM Page iii

Contents
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Chapter 1 Review of MySQL Basics . . . . . . . . . . . . . . . . . . . . . . . . . 1
How to Connect to MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2
Identifiers and Naming Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Queries Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43
What’s Next . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .44
Chapter 2 MySQL Column and Table Types . . . . . . . . . . . . . . . . 45
Why Datatypes Matter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .46
MySQL Column Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48
MySQL Table Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .103
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .110
What’s Next . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .112
Chapter 3 Keys, Indexes, and Normalization . . . . . . . . . 113
Beyond the Spreadsheet Syndrome . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .114
Rules for Relational Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .117
Normalization and Data Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .121
Keys, Indexes, and Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .134
Common Problems and Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .164
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .169
What’s Next . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .170
v
3324FM.qxd 9/21/04 12:24 PM Page v

Chapter 4 Optimizing Queries with Operators,
Branching, and Functions
. . . . . . . . . . . . . . . . . . . . 171
Replacing Program Logic with SQL Logic: A Demonstration . . . .172
MySQL Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .178
MySQL Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .193
Branching: Making Choices in Queries . . . . . . . . . . . . . . . . . . . . . . . . . .231
Our Demonstration Revisited . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .236
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .237
What’s Next . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .237
Chapter 5 Joins, Temporary Tables, and
Transactions
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .240
Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .263
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .267
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .270
What’s Next . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .271
Chapter 6 Finding the Bottlenecks . . . . . . . . . . . . . . . . . . . . . . 273
Configuration Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .274
Application Logic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .305
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .312
What’s Next . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .314
Chapter 7 MySQL Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
Overview of MySQL APIs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .316
PHP and the mysql Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .336
PHP 5 and mysqli . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .351
Perl-DBI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .401
Python and MySQLdb . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .407
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .413
What’s Next . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .415
Contents
vi
3324FM.qxd 9/21/04 12:24 PM Page vi
剩余49页未读,继续阅读













安全验证
文档复制为VIP权益,开通VIP直接复制

评论3