没有合适的资源?快使用搜索试试~ 我知道了~
首页MySQL进阶设计与优化:从新手到专业者指南
MySQL进阶设计与优化:从新手到专业者指南
需积分: 13 0 下载量 161 浏览量
更新于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
of
considerable experience,
do
not
realize
just
how powerful a language
SQL
really is. A great deal
of
applica-
tion logic
can
be
subsumed
into
SQL
logic. We'll look
at
how to write complex
joins
and
to
make effective use
of
•
MySQL
string
and
math
functions
• User variables
•
CASE
• • •
WHEN
• • •
THEN
and
IN
clauses
• Temporary tables
• Logical
and
mathematical operators
All
of
these features will help to
cut
down
on
the
number
of queries
that
are
needed
to
get
the
data
you're looking
for.
Knowing
when
and
how to use these
techniques
in
your queries
can
often help reduce
the
amount
of
overhead
in
your application by reducing multiple queries into
one
structured query.
Overuse
1
Under-Use
1
and
Misuse
of
Indexes
The
proper
use
of
indexes
can
greatly
enhance
performance
of
MySQL
and
lower
the
processing times for queries. However,
improper
use
of
indexes
can
cause problems with your application. For example,
many
developers make
the
assumption
that
every
column
should
be
indexed
and
do so automatically,
not
realizing
that
they're simply using
up
memory
for indexes
that
don't offer
any
advantage. Others simply don't
understand
the
role
that
indexes play
and
end
up
writing unnecessarily complex code that,
in
essence, reinvents
the
wheel.
Beyond
Design:
Performance
Tuning
Basics
Optimization
of
database
and
table schemas, while extremely important, is by
no
means
the
only area
in
which improvements
can
be
made
to
the
performance
of
MySQL
and
applications making use
of
it. A great deal
of
useful information for
pinpointing choke points
and
other
inefficiencies
can
be
obtained from
MySQL
logs
and
from server
and
status variables. Unanticipated bottlenecks
and
other
problems
can
be
uncovered,
and
knowledge
of
these
can
help solve performance
issues
that
are
not
apparent
on
the
surface of
the
application.
In
Chapter
6,
we'll
look
at
these
in
depth
and
provide
some
suggestions for making changes
in
MySQCs
configuration files to improve performance
in
a
number
of different
scenarios.
Looking
Ahead
(and
Behind)
There have
been
quite a few changes
in
MySQL
with
the
release
of
version 4.0;
more are
in
store for
the
4.1.x series
of
releases (version 4.1-alpha having
been
available for several
months
at
the
time
of
this writing),
and
even more
new
fea-
tures are anticipated
in
MySQL
5.0
when
it's released sometime
in
2005
or
2006.
Planning ahead
and
being aware of these changes
can
ease upgrade issues
when
you move your application to newer versions
of
MySQL.
Our "target" version
of
MySQL
in
this book is 4.0/ 4.1, as version 4.0
has
been
in
production release for nearly a year as
of
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
or
exhibits significantly
new
behavior
in
MySQL
4.0
and
4.1.
As for what's ahead, we'll look
at
subqueries (also known as nested queries)
in
MySQL
4.1, as well as index caching
and
some
changes
in
column types. The
introduction
of
stored procedures
and
views
in
MySQL
5.0 represents a
quantum
leap for
MySQL
developers
and
administrators. We'll give you a solid grounding
in
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
in
MySQL
5.1
once development
on
that
series begins.
Who
This
Book
Is
For
You
should
be
able to derive
some
benefit from reading this book
if
you fit into
one
or more
of
the following categories:
• Current
MySQL
users who
can
write basic
SELECT,
DELETE,
and
UPDATE
queries
and
who
want
to design their own databases
or
make improve-
ments
in
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
in
understanding databases
and
how
to write
for
them
• Users
of
other
databases who are looking to
add
MySQL
to their repertoire
or to
port
existing applications to
MySQL
from
another
database
Introduction
xix
Introduction
XX
There
are
some
things
that
won't
be
covered
in
this
book:
• Beginner-level
"What
is a
database/table/query?"
sort
of
questions. You
don't
necessarily
have
to
be
experienced
with
MySQL
in
order
to
derive
some
benefit
from
this
book;
experience
with
another
RDBMS will do.
But
you
should
be
comfortable
with
basic
database
concepts
and
SQL syntax,
or
be
ready
to
do
a little
supplemental
reading
on
your
own
to
familiarize
yourself
with
these.
• MySQL
installation-we
assume
that
you
already
have
access
to
a
working
MySQL installation,
or
are
prepared
to
handle
the
setup
on
your
own.
If
you're
new
to
MySQL,
this
is
not
nearly
as
heartless
as
it
might
sound:
as
we've
noted,
MySQL
AB
provides
binaries
and
installers for a
number
of
platforms,
and
basic
installation
and
setup
is
quite
easy
if
you're
using
Wmdows, Linux,
or
Mac
OS
X.
•
User
and
privilege
administration
issues.
Most
of
the
examples
in
this
book
assume
that
the
user
already
has
the
necessary
privileges
to
run
the
queries
shown.
Some
of
the
issues covered
with
respect
to
logs,
system
and
status
variables,
and
configuration
assume
that
you
have
root
or
superuser
access.
Probably
the
simplest
way
to
deal
with
all
of
these
issues
for
purposes
of
testing
the
examples
provided
is
to
install a "throwaway"
copy
of
MySQL
on
your
desktop
and
work
with
that.
• Transactions
and
replication. While
both
of
these
are
important
for
build-
ing
robust
MySQL-enabled
applications-and
it
has
been
argued
that
no
"real"
database
doesn't
make
use
of
transactions-they
really fall
outside
the
scope
that
we
set
for ourselves
when
planning
this
book. We
do
dis-
cuss
transactions
in
Chapter
5
and
provide
some
programming
examples
using
them
in
Chapter
7,
but
they
aren't
part
of
our
main
focus.
We
hope
that
you'll
find
this
book
useful
in
getting
the
most
out
of
MySQL.
CHAPTER
1
Review
of
MySQL
Basics
BEFORE
WE DELVE
into
database
design principles, we're
going
to
review
some
MySQL basics,
just
to
make
sure
that
we're all roughly
on
the
same
page.
We'll
start
by
looking
at
how
you
can
connect
to
the
MySQL
database
server,
because
that's
the
first
thing
you'll
need
to
do
if
you
want
to
be
able
to
work
with
MySQL. While it's
not
our
intention
to
cover
administration
issues
in
this
book,
we
do
want
to
make
sure
that
you're aware
of
MySQL's
basic
requirements
in
this
regard,
because
some
other
databases
are
pot
as rigorous
as
MySQL
when
it
comes
to
authentication.
Once
you've
connected
to
MySQL, you'll
be
working
with
databases, tables,
and
columns;
of
course, you'll
need
to
be
able
to
identify
these
quickly
and
eas-
ily.
Because
there
appears
to
be
a
lot
of
disparity
in
naming
conventions
for
these
items, we'll
talk
about
MySQL's rules regarding identifiers,
including
allowed characters, case-sensitivity,
and
use
of
reserved keywords. We'll also sug-
gest
some
guidelines for
you
to
follow
in
naming
your
tables,
columns,
aliases,
and
so
forth.
The
rest
of
this
chapter
will
consist
of
a review
of
MySQL's
syntax
and
other
rules for
writing
basic
queries. Even
if
you've
used
MySQL before,
you
might
want
to
scan
this
syntax
review section,
as
there
have
been
a few
changes
in
MySQL 4.0
and
4.1
that
you
might
not
be
aware
of
and
can
use
to
your
advan-
tage,
such
as
new
syntax
for
deleting
rows from
multiple
tables.
We'll
look
at
how
to
create
and
delete
tables
using
the
CREATE
and
DROP
com-
mands.
Next, we'll go over
how
to
retrieve
data
from
tables
using
SELECT
queries.
In
our
discussion
of
SELECT,
we'll also
show
you
how
you
can
order
results
using
an
ORDER
BY
clause
and
how
to
group
related
records
together
with
GROUP
BY.
Then
we'll cover
the
basics for
getting
data
into
database
tables
with
INSERT.
Sometimes,
you
need
to
change
records
that
are
already
present
in
a table; we'll
discuss
how
this
can
be
done
using
the
UPDATE
command.
Finally, we'll review
how
you
can
delete
one
or
more
records
from
a table,
which
can
be
done
with
DELETE.
In
each
case, we'll provide a
formal
definition
of
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.
If
any
of
the
information
in
this
chapter
is
completely
new
to
you,
you
should
probably
consult
a
good
introductory
book
or
tutorial.
One
of
the
better
books
that
we
can
suggest is
Martin
Kotler's
The
Definitive
Guide
to
MySQL,
Second
Edition
(Apress,
2003),
which
covers MySQL 4.0.
1
Chapter 1
2
How
to
Connect
to
MySQL
Connecting to
MySQL
via
the
command
line is easy. Simply type
in
the
following
command, where
username
indicates
the
account usemame:
>
mysql
-u
<username>
-p
If
the
MySQL
server is running
on
a different machine
than
the
one
you're
actually using, you
can
add
the
-h option, followed by
the
server
name
or
IP
address to your login:
>
mysql
-h dbserver -u
dbuser
-p
NOTE
Throughout this
book,
we'll
indicate the
system
command
prompt using
shell>_.
The
MySQL
command prompt
is
always indi-
cated
using
mysql>
_.
In either case, you should
then
be
prompted
with:
Enter
password:
After successfully entering your password, you should see something similar to
this:
Welcome
to the
MySQL
monitor.
Commands
end
with
; or \g.
Your
MySQL
connection
id
is
42465
to server version: 4.0.15-standard
Type
'help;' or
'\h'
for help.
Type
'\c'
to clear the buffer.
If
you see something like this:
ERROR
1045
(28000):
Access
denied
for user •••
That
means
there's a problem with your
usemame
and/
or password. Most likely
you've entered
one
of these incorrectly.
NOTE
Remember that
on
a
fresh
MySQL
installation, the
passwords
for the
root
and anonymous
user
accounts
are
blank.
剩余515页未读,继续阅读
alex6801
- 粉丝: 63
- 资源: 64
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
最新资源
- zlib-1.2.12压缩包解析与技术要点
- 微信小程序滑动选项卡源码模版发布
- Unity虚拟人物唇同步插件Oculus Lipsync介绍
- Nginx 1.18.0版本WinSW自动安装与管理指南
- Java Swing和JDBC实现的ATM系统源码解析
- 掌握Spark Streaming与Maven集成的分布式大数据处理
- 深入学习推荐系统:教程、案例与项目实践
- Web开发者必备的取色工具软件介绍
- C语言实现李春葆数据结构实验程序
- 超市管理系统开发:asp+SQL Server 2005实战
- Redis伪集群搭建教程与实践
- 掌握网络活动细节:Wireshark v3.6.3网络嗅探工具详解
- 全面掌握美赛:建模、分析与编程实现教程
- Java图书馆系统完整项目源码及SQL文件解析
- PCtoLCD2002软件:高效图片和字符取模转换
- Java开发的体育赛事在线购票系统源码分析
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功