For Dummies SQL All-in-One 978-0-470-92996-4 用户手册
产品代码
978-0-470-92996-4
Chapter 1: Tuning the Database
In This Chapter
✓
Examining the work to be done
✓
Contemplating physical design considerations
✓
Choosing and clustering indexes
✓
Co-clustering two relations
✓
Indexing on multiple attributes
✓
Tuning indexes, queries, and transactions
✓
Query tuning in a high-concurrency environment
✓
Benchmarking
✓
Keeping user interactions separate from transactions
✓
Holding down traffic between application and server
✓
Creating a precompiled list of frequently used queries
T
he word tuning is generally taken to mean optimizing an existing system
that isn’t operating at top capacity. Tuning doesn’t do you much good,
that isn’t operating at top capacity. Tuning doesn’t do you much good,
however, if your initial design isn’t at least close to optimal in the first place.
Tuning can take you only so far from your starting point. It’s a lot easier to
tune a slightly off-pitch B string on your guitar to a perfect B than it is to
tune a G string up to a perfect B. (Also, you’re a lot less likely to break the
string.) Tuning for optimal performance should start in the initial design
stage of a database, not at some later time when design decisions have been
cast in concrete.
Tuning can take you only so far from your starting point. It’s a lot easier to
tune a slightly off-pitch B string on your guitar to a perfect B than it is to
tune a G string up to a perfect B. (Also, you’re a lot less likely to break the
string.) Tuning for optimal performance should start in the initial design
stage of a database, not at some later time when design decisions have been
cast in concrete.
The performance of a database management system (DBMS) is generally
judged by how fast it executes queries. Two types of operations are impor-
tant: the retrieval of data from a database and the updating of records in a
database. The speed at which records can be accessed is key to both types
of operations, because you must locate a record before you can retrieve or
update the data in it. The users’ data model on which you’ll base your data-
base design is almost certainly structured in a way that isn’t the best from a
performance standpoint. The users are primarily concerned with function-
ality and may have little or no idea of how the design of a database affects
how well it performs. You must transform the users’ data into a conceptual
schema that you actualize in the form of an Entity-Relationship (ER) model
diagram. Recall that the Entity-Relationship data model and its associated
diagrams are extensively covered in Book II.
judged by how fast it executes queries. Two types of operations are impor-
tant: the retrieval of data from a database and the updating of records in a
database. The speed at which records can be accessed is key to both types
of operations, because you must locate a record before you can retrieve or
update the data in it. The users’ data model on which you’ll base your data-
base design is almost certainly structured in a way that isn’t the best from a
performance standpoint. The users are primarily concerned with function-
ality and may have little or no idea of how the design of a database affects
how well it performs. You must transform the users’ data into a conceptual
schema that you actualize in the form of an Entity-Relationship (ER) model
diagram. Recall that the Entity-Relationship data model and its associated
diagrams are extensively covered in Book II.
40_9780470929964-bk07ch01.indd 579
40_9780470929964-bk07ch01.indd 579
2/24/11 3:45 PM
2/24/11 3:45 PM
COPYRIGHTED MATERIAL