MySQL进阶设计与优化：从新手到专业者指南

需积分: 13 81 浏览量更新于2024-07-17 收藏 15.48MB PDF 举报

"《初学者到专业者的MySQL数据库设计与优化指南》是一本专为已经掌握了MySQL基础知识但希望进一步提升技能的用户打造的实用书籍。该书详细介绍了如何充分利用MySQL内置功能，减少数据检索后的需求处理，从而提高效率。作者深入浅出地展示了如何编写并运用高级复杂的查询，如嵌套子查询和虚拟表（自MySQL 4.1版本引入），这些技巧有助于简化应用逻辑，降低中间件处理的负担。书中不仅注重实践技巧，还强调了数据库设计的最佳实践与应避免的陷阱，通过实例分析，读者能够了解到不良数据库设计的真实案例及其改进过程。此外，作者还回顾了MySQL的基础知识和核心理论，如命名规范和连接机制，以便读者在需要时快速查阅。评论者们对本书给予了高度评价。Mike Hillyer赞赏其内容全面且结构清晰，没有冗余，让读者既能掌握足够的细节，又不会感到压力过大或对未深入讨论的话题感到困惑。Mary Norbury-Glaser认为这本书非常适合MySQL开发者，无论经验水平如何，都能从中获益。Jack Herrington也表示，对于所有使用MySQL的人来说，这是一本非常有价值的参考书籍。《 Beginning MySQL Database Design and Optimization》是一本极具价值的专业教材，无论是初学者还是希望在MySQL领域深化的专业人士，都能在其中找到从基础到进阶的知识和实践经验，帮助他们在数据库设计和优化方面提升技能，更好地驾驭MySQL技术。"

Introduction

xviii

Many application developers, including

some

considerable experience,

not

realize

just

how powerful a language

SQL

really is. A great deal

applica-

tion logic

can

subsumed

into

SQL

logic. We'll look

how to write complex

joins

and

make effective use

•

MySQL

string

and

math

functions

• User variables

•

CASE

• • •

WHEN

• • •

THEN

and

clauses

• Temporary tables

• Logical

and

mathematical operators

All

these features will help to

cut

down

the

number

of queries

that

are

needed

get

the

data

you're looking

for.

Knowing

when

and

how to use these

techniques

your queries

can

often help reduce

the

amount

overhead

your application by reducing multiple queries into

one

structured query.

Overuse

Under-Use

and

Misuse

Indexes

The

proper

use

indexes

can

greatly

enhance

performance

MySQL

and

lower

the

processing times for queries. However,

improper

use

indexes

can

cause problems with your application. For example,

many

developers make

the

assumption

that

every

column

should

indexed

and

do so automatically,

not

realizing

that

they're simply using

memory

for indexes

that

don't offer

any

advantage. Others simply don't

understand

the

role

that

indexes play

and

end

writing unnecessarily complex code that,

essence, reinvents

the

wheel.

Beyond

Design:

Performance

Tuning

Basics

Optimization

database

and

table schemas, while extremely important, is by

means

the

only area

which improvements

can

made

the

performance

MySQL

and

applications making use

it. A great deal

useful information for

pinpointing choke points

and

other

inefficiencies

can

obtained from

MySQL

logs

and

from server

and

status variables. Unanticipated bottlenecks

and

other

problems

can

uncovered,

and

knowledge

these

can

help solve performance

issues

that

are

not

apparent

the

surface of

the

application.

Chapter

we'll

look

these

depth

and

provide

some

suggestions for making changes

MySQCs

configuration files to improve performance

number

of different

scenarios.

Looking

Ahead

(and

Behind)

There have

been

quite a few changes

MySQL

with

the

release

version 4.0;

more are

store for

the

4.1.x series

releases (version 4.1-alpha having

been

available for several

months

the

time

this writing),

and

even more

new

fea-

tures are anticipated

MySQL

5.0

when

it's released sometime

2005

2006.

Planning ahead

and

being aware of these changes

can

ease upgrade issues

when

you move your application to newer versions

MySQL.

Our "target" version

MySQL

this book is 4.0/ 4.1, as version 4.0

has

been

production release for nearly a year as

this writing,

and

4.1

is expected to

reach production status before

the

end

of 2004. However, as there are

many

commercial applications

and

web hosts still using

MySQL

3.23, we'll

try

to indi-

cate where a feature is

new

exhibits significantly

new

behavior

MySQL

4.0

and

4.1.

As for what's ahead, we'll look

subqueries (also known as nested queries)

MySQL

4.1, as well as index caching

and

some

changes

column types. The

introduction

stored procedures

and

views

MySQL

5.0 represents a

quantum

leap for

MySQL

developers

and

administrators. We'll give you a solid grounding

these features, explain

what

they'll

mean

for you

and

your projects,

and

pro-

vide examples

that

you

can

try

out

for yourself using

the

pre-release versions

already available. We'll also give you

our

best guess as to

what

you might expect

to see

MySQL

5.1

once development

that

series begins.

Who

This

Book

For

You

should

able to derive

some

benefit from reading this book

you fit into

one

or more

the following categories:

• Current

MySQL

users who

can

write basic

SELECT,

DELETE,

and

UPDATE

queries

and

who

want

to design their own databases

make improve-

ments

the

ones they're already using

• Developers who've worked with database-enabled applications

and

want

to improve

the

efficiency of their MySQL-based applications

• Developers who haven't worked with database applications before

and

are seeking to get started

understanding databases

and

how

to write

for

them

• Users

other

databases who are looking to

add

MySQL

to their repertoire

or to

port

existing applications to

MySQL

from

another

database

Introduction

xix

CHAPTER

Review

MySQL

Basics

BEFORE

WE DELVE

into

database

design principles, we're

going

review

some

MySQL basics,

just

make

sure

that

we're all roughly

the

same

page.

We'll

start

looking

how

you

can

connect

the

MySQL

database

server,

because

that's

the

first

thing

you'll

need

you

want

able

work

with

MySQL. While it's

not

our

intention

cover

administration

issues

this

book,

want

make

sure

that

you're aware

MySQL's

basic

requirements

this

regard,

because

some

other

databases

are

pot

as rigorous

MySQL

when

comes

authentication.

Once

you've

connected

MySQL, you'll

working

with

databases, tables,

and

columns;

course, you'll

need

able

identify

these

quickly

and

eas-

ily.

Because

there

appears

lot

disparity

naming

conventions

for

these

items, we'll

talk

about

MySQL's rules regarding identifiers,

including

allowed characters, case-sensitivity,

and

use

reserved keywords. We'll also sug-

gest

some

guidelines for

you

naming

your

tables,

columns,

aliases,

and

forth.

The

rest

this

chapter

will

consist

a review

MySQL's

syntax

and

other

rules for

writing

basic

queries. Even

you've

used

MySQL before,

you

might

want

scan

this

syntax

review section,

there

have

been

a few

changes

MySQL 4.0

and

4.1

that

you

might

not

aware

and

can

use

your

advan-

tage,

such

new

syntax

for

deleting

rows from

multiple

tables.

We'll

look

how

create

and

delete

tables

using

the

CREATE

and

DROP

com-

mands.

Next, we'll go over

how

retrieve

data

from

tables

using

SELECT

queries.

our

discussion

SELECT,

we'll also

show

you

how

you

can

order

results

using

ORDER

clause

and

how

group

records

together

with

GROUP

BY.

Then

we'll cover

the

basics for

getting

data

into

database

tables

with

INSERT.

Sometimes,

you

need

change

records

that

are

already

present

a table; we'll

discuss

how

this

can

done

using

the

UPDATE

command.

Finally, we'll review

how

you

can

delete

one

records

from

a table,

which

can

done

with

DELETE.

each

case, we'll provide a

formal

definition

the

command,

including

its

most

common

and

useful

variants

where

applicable,

and

then

provide

some

examples so

you

can

see

just

how

it's used.

any

the

information

this

chapter

completely

new

you,

you

should

probably

consult

good

introductory

book

tutorial.

One

the

better

books

that

can

suggest is

Martin

Kotler's

The

Definitive

Guide

MySQL,

Second

Edition

(Apress,

2003),

which

covers MySQL 4.0.

剩余515页未读，继续阅读

alex6801

粉丝: 64
资源: 64

MySQL进阶设计与优化：从新手到专业者指南

Beginning MySQL Database Design and Optimization.pdf

Beginning_MySQL_Database_Design__Optimization

Near space airship conceptual design and optimization

Design and Optimization of Passive UHF RFID Systems

Navicat Connecting to MySQL Database: Performance Optimization Secrets to Boost Database Access ...

Navicat Connecting to MySQL Database: Performance Optimization Tips to Make Database Access Soar

Improving Query Efficiency: Index Design and Optimization in Doris Database

Building Efficient Data Tables: Table Design and Optimization in Doris Database

高希峰-Design and Optimization of Conforming Lattice Structures

Low Stray Inductance Busbar Design and Optimization for SiC-Base

最新资源