荔园在线
荔园之美，在春之萌芽，在夏之绽放，在秋之收获，在冬之沉淀
[回到开始]
[上一篇][下一篇]
发信人: georgehill (清风浮云 人生), 信区: Linux
标 题: mysql0
发信站: BBS 荔园晨风站 (Fri Nov 17 15:56:48 2000), 站内信件
【 以下文字转载自 georgehill 的信箱 】
【 原文由 georgehill.bbs@fb2000.dhs.org 所发表 】
寄信人: deardragon.bbs@bbs.whnet.edu.cn
标 题: mysql0
发信站: 快意灌水站 BBS 信差
来 源: from bbs.whnet.edu.cn (bbs.whnet.edu.cn [202.112.20.132])
日 期: Fri Nov 17 01:06:06 2000
发信人: hzh (网络飞翔), 信区: PHP
标 题: mysql0
发信站: 武汉白云黄鹤站 (Tue May 30 18:17:11 2000), 站内信件
MySQL中文参考手册
译者：晏子 (clyan@sohu.com） 主页：http://linuxdb.yeah.net
----------------------------------------------------------------------------
----
第一章, 前一章, 下一章, 最后一章，目录.
----------------------------------------------------------------------------
----
10 从MySQL得到最大的性能
优化是一项复杂的任务，因为它最终需要对整个系统的理解。当用你的系统/应用的小知
识做一些局部优化是可能的时候，你越想让你的系统更优化，你必须知道它也越多。
因此，本章将试图解释并给出优化MySQL的不同方法的一些例子。但是记住总是有某些(
逐渐变难)是系统更快的方法留着去做。
10.1 优化概述
为了使一个系统更快的最重要部分当然是基本设计。你也需要知道你的系统将做这样的
事情，那就是你的瓶颈。
最常见的瓶颈是：
磁盘寻道。磁盘花时间找到一个数据，用在1999年的现代磁盘其平均时间通常小于10ms
，因此理论上我们能大约一秒寻道 1000 次。这个时间用新磁盘提高很慢并且很难对一
个表优化。优化它的方法是将数据散布在多个磁盘上。
当磁盘在我们需要读数据的正确位置时，磁盘读/写。用1999年的现代，一个磁盘传输类
似10-20Mb/s。这必寻道更容易优化，因为你能从多个磁盘并行地读。
CPU周期。当我们读数据进内存时，(或如果它已经在那里)我们需要处理它以达到我们的
结果。当我们有相对内存较小的表时，这是最常见的限制因素，但是用小表速度通常不
是问题。
内存带宽。当CPU需要超出适合cpu缓存的数据时，缓存带宽就成为内存的一个瓶颈。这
是对大多数系统的一个不常见的瓶颈但是你应该知道它。
10.2 系统/编译时和启动参数的调节
我们以系统级的东西开始，因为这些决策的某一些很早就做好了。在其他情况下，快速
浏览这部分可能就够了，因为它对大收获并不重要，但是有一个关于在这个层次上收获
有多大的感觉总是好的。
使用的缺省OS确实重要！为了最大程度地使用多CPU，应该使用Solaris(因为线程工作得
确实不错)或Linux(因为2.2本的核心又确实不错的SMP支持)。而且在32位的机器上，Li
nux缺省有2G的文件大小限制。当新的文件系统被释出时( XFS )，希望这不久被修正。
因为我们没在很多平台上运行生产MySQL，我们忠告你在可能选择它前，测试你打算运行
的平台。
其他建议：
如果你有足够的RAM，你能删除所有交换设备。一些操作系统在某些情况下将使用一个S
WAP设备，即使你有空闲的内存。
使用--skip-locking的MySQL选项避免外部锁定。注意这将不影响MySQL功能，只要它仅
运行在一个服务器上。只要在你运行myisamchk以前，记得要停掉服务器(或锁定相关部
分)。在一些系统上这个开关是强制的，因为外部锁定不是在任何情况下都工作。当用M
IT-pthreads编译时，--skip-locking选项缺省为打开(on)，因为flock()没在所有的平
台上被MIT-pthreads充分支持。唯一的情况是如果你对同一数据运行MySQL服务器(不是
客户)，你不能使用--skip-locking之时，否则对没有先清掉(flushing)或先锁定mysql
d服务器的表上运行myisamchk。你仍然能使用LOCK TABLES/ UNLOCK TABLES，即使你正
在使用--skip-locking。
10.2.1 编译和链接怎样影响MySQL的速度
大多数下列测试在Linux上并用MySQL基准进行的，但是它们应该对其他操作系统和工作
负载给出一些指示。
当你用-static链接时，你得到最快的可执行文件。使用Unix套接字而非TCP/IP连接一个
数据库也可给出好一些的性能。
在Linux上，当用pgcc和-O6编译时，你将得到最快的代码。为了用这些选项编译“sql_
yacc.cc”，你需要大约200M内存，因为gcc/pgcc需要很多内存使所有函数嵌入（inlin
e）。在配置MySQL时，你也应该设定CXX=gcc以避免包括libstdc++库(它不需要)。
只通过使用一个较好的编译器或较好的编译器选项，在应用中你能得到一个10-30%的加
速。如果你自己编译SQL服务器，这特别重要！
在Intel上，你应该例如使用pgcc或Cygnus CodeFusion编译器得到最大速度。我们已经
测试了新的 Fujitsu编译器，但是它是还没足够不出错来优化编译MySQL。
这里是我们做过的一些测量表：
如果你以-O6使用pgcc并且编译任何东西，mysqld服务器是比用gcc快11%（用字符串99的
版本)。
如果你动态地链接(没有-static)，结果慢了13%。注意你仍能使用一个动态连接的MySQ
L库。只有服务器对性能是关键的。
如果你使用TCP/IP而非Unix套接字，结果慢7.5%。
在一个Sun SPARCstation 10上，gcc2.7.3是比Sun Pro C++ 4.2快13%。
在Solaris 2.5.1上，在单个处理器上MIT-pthreads比带原生线程的Solaris慢8-12%。以
更多的负载/cpus，差别应该变得更大。
由TcX提供的MySQL-Linux的分发用pgcc编译并静态链接。
10.2.2 磁盘问题
正如前面所述，磁盘寻道是一个性能的大瓶颈。当数据开始增长以致缓存变得不可能时
，这个问题变得越来越明显。对大数据库，在那你或多或少地要随机存取数据，你可以
依靠你将至少需要一次磁盘寻道来读取并且几次磁盘寻道写入。为了使这个问题最小化
，使用有低寻道时间的磁盘。
为了增加可用磁盘轴的数量(并且从而减少寻道开销)，符号联接文件到不同磁盘或分割
磁盘是可能的。
使用符号连接
这意味着你将索引/数据文件符号从正常的数据目录链接到其他磁盘(那也可以被分割的
)。这使得寻道和读取时间更好(如果磁盘不用于其他事情)。见10.2.2.1 使用数据库和
表的符号链接。
分割
分割意味着你有许多磁盘并把第一块放在第一个磁盘上，在第二块放在第二个磁盘上，
并且第 n块在第(n mod number_of_disks)磁盘上，等等。这意味着，如果你的正常数据
大小于分割大小(或完美地排列过)，你将得到较好一些的性能。注意，分割是否很依赖
于OS和分割大小。因此用不同的分割大小测试你的应用程序。见10.8 使用你自己的基准
。注意对分割的速度差异很依赖于参数，取决于你如何分割参数和磁盘数量，你可以得
出以数量级的不同。注意你必须选择为随机或顺序存取优化。
为了可靠，你可能想要使用袭击RAID 0+1(分割+镜像)，但是在这种情况下，你将需要2
*N个驱动器来保存N个驱动器的数据。如果你有钱，这可能是最好的选择！然而你也可能
必须投资一些卷管理软件投资以高效地处理它。
一个好选择是让稍重要的数据(它能再生)上存在RAID 0磁盘上，而将确实重要的数据(像
主机信息和日志文件)存在一个RAID 0+1或RAID N磁盘上。如果因为更新奇偶位你有许多
写入，RAID N可能是一个问题。
你也可以对数据库使用的文件系统设置参数。一个容易的改变是以noatime选项挂装文件
系统。这是它跳过更新在inode中的最后访问时间，而且这将避免一些磁盘寻道。
10.2.2.1 为数据库和表使用符号链接
你可以从数据库目录移动表和数据库到别处，并且用链接到新地点的符号代替它们。你
可能想要这样做，例如，转移一个数据库到有更多空闲空间的一个文件系统。
如果MySQL注意到一个表是一个符号链接，它将解析符号链接并且使用其实际指向的表，
它可工作在支持realpath()调用的所有系统上(至少Linux和Solaris支持realpath())！
在不支持realpath()的系统上，你应该不同时通过真实路径和符号链接访问表！如果你
这样做，表在任何更新后将不一致。
MySQL缺省不支持数据库链接。只要你不在数据库之间做一个符号链接，一切将工作正常
。假定你在MySQL数据目录下有一个数据库db1，并且做了一个符号链接db2指向db1：
shell> cd /path/to/datadir
shell> ln -s db1 db2
现在，对在db1中的任一表tbl_a，在db2种也好象有一个表tbl_a。如果一个线程更新db
1.tbl_a并且另一个线程更新db2.tbl_a，将有问题。
如果你确实需要这样，你必须改变下列在“mysys/mf_format.c”中的代码：
if (!lstat(to,&stat_buff)) /* Check if it's a symbolic link */
if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff))
把代码改变为这样：
if (realpath(to,buff))
10.2.3 调节服务器参数
你能用这个命令得到mysqld服务器缺省缓冲区大小：
shell> mysqld --help
这个命令生成一张所有mysqld选项和可配置变量的表。输出包括缺省值并且看上去象这
样一些东西：
Possible variables for option --set-variable (-O) are:
back_log current value: 5
connect_timeout current value: 5
delayed_insert_timeout current value: 300
delayed_insert_limit current value: 100
delayed_queue_size current value: 1000
flush_time current value: 0
interactive_timeout current value: 28800
join_buffer_size current value: 131072
key_buffer_size current value: 1048540
lower_case_table_names current value: 0
long_query_time current value: 10
max_allowed_packet current value: 1048576
max_connections current value: 100
max_connect_errors current value: 10
max_delayed_threads current value: 20
max_heap_table_size current value: 16777216
max_join_size current value: 4294967295
max_sort_length current value: 1024
max_tmp_tables current value: 32
max_write_lock_count current value: 4294967295
net_buffer_length current value: 16384
query_buffer_size current value: 0
record_buffer current value: 131072
sort_buffer current value: 2097116
table_cache current value: 64
thread_concurrency current value: 10
tmp_table_size current value: 1048576
thread_stack current value: 131072
wait_timeout current value: 28800
如果有一个mysqld服务器正在运行，通过执行这个命令，你可以看到它实际上使用的变
量的值：
shell> mysqladmin variables
每个选项在下面描述。对于缓冲区大小、长度和栈大小的值以字节给出，你能用于个后
缀“K”或“M” 指出以K字节或兆字节显示值。例如，16M指出16兆字节。后缀字母的大
小写没有关系；16M和16m是相同的。
你也可以用命令SHOW STATUS自一个运行的服务器看见一些统计。见7.21 SHOW语法(得到
表、列的信息)。
back_log
要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求
，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_l
og值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只
有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/
IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 Unix li
sten(2)系统调用的手册页应该有更多的细节。检查你的OS文档找出这个变量的最大值。
试图设定back_log高于你的操作系统的限制将是无效的。
connect_timeout
mysqld服务器在用Bad handshake（糟糕的握手）应答前正在等待一个连接报文的秒数。
delayed_insert_timeout
一个INSERT DELAYED线程应该在终止之前等待INSERT语句的时间。
delayed_insert_limit
在插入delayed_insert_limit行后，INSERT DELAYED处理器将检查是否有任何SELECT语
句未执行。如果这样，在继续前执行允许这些语句。
delayed_queue_size
应该为处理INSERT DELAYED分配多大一个队列(以行数)。如果排队满了，任何进行INSE
RT DELAYED的客户将等待直到队列又有空间了。
flush_time
如果这被设置为非零值，那么每flush_time秒所有表将被关闭(以释放资源和sync到磁盘
)。
interactive_timeout
服务器在关上它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对mysq
l_real_connect()使用CLIENT_INTERACTIVE选项的客户。也可见wait_timeout。
join_buffer_size
用于全部联结(join)的缓冲区大小(不是用索引的联结)。缓冲区对2个表间的每个全部联
结分配一次缓冲区，当增加索引不可能时，增加该值可得到一个更快的全部联结。（通
常得到快速联结的最佳方法是增加索引。）
key_buffer_size
索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，
增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使
它太大，系统将开始换页并且真的变慢了。记住既然MySQL不缓存读取的数据，你将必须
为OS文件系统缓存留下一些空间。为了在写入多个行时得到更多的速度，使用LOCK TAB
LES。见7.24LOCK TABLES/UNLOCK TABLES语法。
long_query_time
如果一个查询所用时间超过它(以秒计)，Slow_queries记数器将被增加。
max_allowed_packet
一个包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增
加到max_allowed_packet个字节。缺省地，该值太小必能捕捉大的(可能错误)包。如果
你正在使用大的BLOB列，你必须增加该值。它应该象你想要使用的最大BLOB的那么大。
max_connections
允许的同时客户的数量。增加该值增加mysqld要求的文件描述符的数量。见下面对文件
描述符限制的注释。见18.2.4 Too many connections错误。
max_connect_errors
如果有多于该数量的从一台主机中断的连接，这台主机阻止进一步的连接。你可用FLUS
H HOSTS命令疏通一台主机。
max_delayed_threads
不要启动多于的这个数字的线程来处理INSERT DELAYED语句。如果你试图在所有INSERT
DELAYED线程在用后向一张新表插入数据，行将被插入，就像DELAYED属性没被指定那样
。
max_join_size
可能将要读入多于max_join_size个记录的联结将返回一个错误。如果你的用户想要执行
没有一个WHERE子句、花很长时间并且返回百万行的联结，设置它。
max_sort_length
在排序BLOB或TEXT值时使用的字节数(每个值仅头max_sort_length个字节被使用；其余
的被忽略)。
max_tmp_tables
（该选择目前还不做任何事情)。一个客户能同时保持打开的临时表的最大数量。
net_buffer_length
通信缓冲区在查询之间被重置到该大小。通常这不应该被改变，但是如果你有很少的内
存，你能将它设置为查询期望的大小。（即，客户发出的SQL语句期望的长度。如果语句
超过这个长度，缓冲区自动地被扩大，直到max_allowed_packet个字节。）
record_buffer
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做
很多顺序扫描，你可能想要增加该值。
sort_buffer
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY
操作。见18.5 MySQL在哪儿存储临时文件。
table_cache
为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每
个唯一打开的表需要2个文件描述符，见下面对文件描述符限制的注释。对于表缓存如何
工作的信息，见10.2.4 MySQL怎样打开和关闭表。
tmp_table_size
如果一张临时表超出该大小，MySQL产生一个The table tbl_name is full形式的错误，
如果你做很多高级GROUP BY查询，增加tmp_table_size值。
thread_stack
每个线程的栈大小。由crash-me测试检测到的许多限制依赖于该值。缺省队一般的操作
是足够大了。见10.8 使用你自己的基准。
wait_timeout
服务器在关闭它之前在一个连接上等待行动的秒数。也可见interactive_timeout。
MySQL使用是很具伸缩性的算法，因此你通常能用很少的内存运行或给MySQL更多的被存
以得到更好的性能。
如果你有很多内存和很多表并且有一个中等数量的客户，想要最大的性能，你应该一些
象这样的东西：
shell> safe_mysqld -O key_buffer=16M -O table_cache=128 \
-O sort_buffer=4M -O record_buffer=1M &
如果你有较少的内存和大量的连接，使用这样一些东西：
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
-O record_buffer=100k &
或甚至：
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
-O table_cache=32 -O record_buffer=8k -O net_buffer=1K &
如果有很多连接，“交换问题”可能发生，除非mysqld已经被配置每个连接使用很少的
内存。当然如果你对所有连接有足够的内存，mysqld执行得更好。
注意，如果你改变mysqld的一个选项，它实际上只对服务器的那个例子保持。
为了明白一个参数变化的效果，这样做：
shell> mysqld -O key_buffer=32m --help
保证--help选项是最后一个；否则，命令行上在它之后列出的任何选项的效果将不在反
映在输出中。
10.2.4 MySQL怎样打开和关闭数据库表
table_cache, max_connections和max_tmp_tables影响服务器保持打开的文件的最大数
量。如果你增加这些值的一个或两个，你可以遇到你的操作系统每个进程打开文件描述
符的数量上强加的限制。然而，你可以能在许多系统上增加该限制。请教你的OS文档找
出如何做这些，因为改变限制的方法各系统有很大的不同。
table_cache与max_connections有关。例如，对于200个打开的连接，你应该让一张表的
缓冲至少有200 * n，这里n是一个联结(join)中表的最大数量。
打开表的缓存可以增加到一个table_cache的最大值（缺省为64；这可以用mysqld的-O
table_cache=#选项来改变）。一个表绝对不被关闭，除非当缓存满了并且另外一个线程
试图打开一个表时或如果你使用mysqladmin refresh或mysqladmin flush-tables。
当表缓存满时，服务器使用下列过程找到一个缓存入口来使用：
不是当前使用的表被释放，以最近最少使用（LRU）顺序。
如果缓存满了并且没有表可以释放，但是一个新表需要打开，缓存必须临时被扩大。
如果缓存处于一个临时扩大状态并且一个表从在用变为不在用状态，它被关闭并从缓存
中释放。
对每个并发存取打开一个表。这意味着，如果你让2个线程存取同一个表或在同一个查询
中存取表两次(用AS)，表需要被打开两次。任何表的第一次打开占2个文件描述符；表的
每一次额外使用仅占一个文件描述符。对于第一次打开的额外描述符用于索引文件；这
个描述符在所有线程之间共享。
10.2.5 在同一个数据库中创建大量数据库表的缺点
如果你在一个目录中有许多文件，打开、关闭和创建操作将会很慢。如果你执行在许多
不同表上的SELECT语句，当表缓存满时，将有一点开销，因为对每个必须打开的表，另
外一个必须被关闭。你可以通过使表缓冲更大些来减少这个开销。
10.2.6 为什么有这么多打开的表？
当你运行mysqladmin status时，你将看见象这样的一些东西：
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
如果你仅有6个表，这可能有点令人困惑。
MySQL是多线程的，因此它可以同时在同一个表上有许多询问。为了是2个线程在同一个
文件上有不同状态的问题减到最小，表由每个并发进程独立地打开。这为数据文件消耗
一些内存和一个额外的文件描述符。索引文件描述符在所有线程之间共享。
10.2.7 MySQL怎样使用内存
下表指出mysqld服务器使用存储器的一些方式。在应用的地方，给出与存储器使用相关
的服务器变量的名字。
关键字缓冲区(变量key_buffer_size)由所有线程分享；当需要时，分配服务器使用的其
他缓冲区。见10.2.3 调节服务器参数。
每个连接使用一些线程特定的空间；一个栈(缺省64K，变量thread_stack)、一个连接缓
冲区(变量net_buffer_length)和一个结果缓冲区(变量net_buffer_length)。当需要时
，连接缓冲区和结果缓冲区动态地被扩大到max_allowed_packet。当一个查询正在运行
当前查询的一个拷贝时，也分配字符串。
所有线程共享同一基存储器。
目前还没有什么是内存映射的(除了压缩表，但是那是另外一个的故事)。这是因为4GB的
32位存储器空间对最大的数据库来所不是足够大的。当一个64位寻址空间的系统变得更
普遍时，我们可以为内存映射增加全面的支持。
每个做顺序扫描的请求分配一个读缓冲区(变量record_buffer)。
所有联结均用一遍完成并且大多数联结可以甚至不用一张临时表来完成。最临时的表是
基于内存的(HEAP)表。有较大记录长度(以所有列的长度之和计算)的临时表或包含BLOB
列的表在磁盘上存储。在MySQL版本3.23.2前一个问题是如果一张HEAP表超过tmp_table
_size的大小，你得到错误The table tbl_name is full。在更新的版本中，这通过必要
时自动将在内存的(HEAP)表转变为一个基于磁盘(MyISAM)的表来处理。为了解决这个问
题，你可以通过设置mysqld的tmp_table_size选项，或通过在客户程序中设置SQL的SQL
_BIG_TABLES选项增加临时表的大小。见7.25 SET OPTION句法。在MySQL 3.20中，临时
表的最大尺寸是record_buffer*16，因此如果你正在使用这个版本，你必须增加record
_buffer值。你也可以使用--big-tables选项启动mysqld以总将临时表存储在磁盘上，然
而，这将影响许多复杂查询的速度。
大多数做排序的请求分配一个排序缓冲区和一个或二个临时文件。见18.5 MySQL在哪儿
存储临时文件。
几乎所有的语法分析和计算都在一家本地存储器中完成。对小项目没有内存开销并且一
般的较慢存储器分配和释放被避免。内存仅为出乎意料的大字符串分配(这用malloc()和
free()完成)。
每个索引文件只被打开一次，并且数据文件为每个并发运行的线程打开一次。对每个并
发线程，分配一个表结构、对每列的列结构和大小为3 * n的一个缓冲区(这里n是最大的
行长度，不算BLOB列)。一个BLOB使用5 ～ 8个字节加上BLOB数据。
对每个有BLOB列的表，一个缓冲区动态地被扩大以便读入更大的BLOB值。如果你扫描一
个表，分配与最大BLOB值一样大的一个缓冲区。
对所有在用的表的表处理器被保存在一个缓存中并且作为一个FIFO管理。通常缓存有64
个入口。如果一个表同时被2个运行的线程使用，缓存为此包含2个入口。见10.2.4 MyS
QL如何打开和关闭数据库表。
一个mysqladmin flush-tables命令关闭所有不在用的表并在当前执行的线程结束时，标
记所有在用的表准备被关闭。这将有效地释放大多数在用的内存。
ps和其他系统状态程序可以报导mysqld使用很多内存。这可以是在不同的内存地址上的
线程栈造成的。例如，Solaris版本的ps将栈间未用的内存算作已用的内存。你可以通过
用swap -s检查可用交换区来验证它。我们用商业内存漏洞探查器测试了mysqld，因此应
该有没有内存漏洞。
10.2.8 MySQL怎样锁定数据库表
MySQL中所有锁定不会是死锁的。这通过总是在一个查询前立即请求所有必要的锁定并且
总是以同样的顺序锁定表来管理。
对WRITE，MySQL使用的锁定方法原理如下：
如果在表上没有锁，放一个锁在它上面。
否则，把锁定请求放在写锁定队列中。
对READ，MySQL使用的锁定方法原理如下：
如果在表上没有写锁定，把一个读锁定放在它上面。
否则，把锁请求放在读锁定队列中。
当一个锁定被释放时，锁定可被写锁定队列中的线程得到，然后是读锁定队列中的线程
。
这意味着，如果你在一个表上有许多更改，SELECT语句将等待直到有没有更多的更改。
为了解决在一个表中进行很多INSERT和SELECT操作的情况，你可在一张临时表中插入行
并且偶尔用来自临时表的记录更新真正的表。
这可用下列代码做到：
mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> insert into real_table select * from insert_table;
mysql> delete from insert_table;
mysql> UNLOCK TABLES;
如果你在一些特定的情况字下区分检索的优先次序，你可以使用LOW_PRIORITY选项的IN
SERT。见7.14 INSERT句法。
你也能改变在“mysys/thr_lock.c”中的锁代码以使用一个单个队列。在这种情况下，
写锁定和读锁定将有同样优先级，它可能帮助一些应用程序。
10.2.9 数据库表级锁定的问题
MySQL的表锁定代码是不会死锁的。
MySQL使用表级锁定(而不是行级锁定或列级锁定)以达到很高的锁定速度。对于大表，表
级锁定对大多数应用程序来说比行级锁定好一些，但是当然有一些缺陷。
在MySQL3.23.7和更高版本中，一个人能把行插入到MyISAM表同时其他线程正在读该表。
注意，目前只有在表中内有删除的行时才工作。
表级锁定使很多线程能够同时读一个表，但是如果一个线程想要写一个表，它必须首先
得到独占存取权。在更改期间，所有其他想要存取该特定表的线程将等到更改就绪。
因为数据库的更改通常被视为比SELECT更重要，更新一个表的所有语句比从一个表中检
索信息的语句有更高的优先级。这应该保证更改不被“饿死”，因为一个人针对一个特
定表会发出很多繁重的查询。
从MySQL 3.23.7开始，一个人可以能使用max_write_lock_count变量强制MySQL在一个表
上一个特定数量的插入后发出一个SELECT。
对此一个主要的问题如下：
一个客户发出一个花很长时间运行的SELECT。
然后其他客户在一个使用的表上发出一个UPDATE；这个客户将等待直到SELECT完成。
另一个客户在同一个表上发出另一个SELECT语句；因为UPDATE比SELECT有更高的优先级
，该SELECT将等待UPDATE的完成。它也将等待第一个SELECT完成！
对这个问题的一些可能的解决方案是：
试着使SELECT语句运行得更快；你可能必须创建一些摘要(summary)表做到这点。
用--low-priority-updates启动mysqld。这将给所有更新(修改)一个表的语句以比SELE
CT语句低的优先级。在这种情况下，在先前情形的最后的SELECT语句将在INSERT语句前
执行。
你可以用LOW_PRIORITY属性给与一个特定的INSERT、UPDATE或DELETE语句较低优先级。
为max_write_lock_count指定一个低值来启动mysqld使得在一定数量的WRITE锁定后给出
READ锁定。
通过使用SQL命令：SET SQL_LOW_PRIORITY_UPDATES=1，你可从一个特定线程指定所有的
更改应该由用低优先级完成。见7.25 SET OPTION句法。
你可以用HIGH_PRIORITY属性指明一个特定SELECT是很重要的。见7.12 SELECT句法。
如果你有关于INSERT结合SELECT的问题，切换到使用新的MyISAM表，因为它们支持并发
的SELECT和INSERT。
如果你主要混合INSERT和SELECT语句，DELAYED属性的INSERT将可能解决你的问题。见7
.14 INSERT句法。
如果你有关于SELECT和DELETE的问题，LIMIT选项的DELETE可以帮助你。见7.11 DELETE
句法。
10.3 使你的数据尽可能小
最基本的优化之一是使你的数据(和索引)在磁盘上(并且在内存中)占据的空间尽可能小
。这能给出巨大的改进，因为磁盘读入较快并且通常也用较少的主存储器。如果在更小
的列上做索引，索引也占据较少的资源。
你能用下面的技术使表的性能更好并且使存储空间最小：
尽可能地使用最有效(最小)的类型。MySQL有很多节省磁盘空间和内存的专业化类型。
如果可能使表更小，使用较小的整数类型。例如，MEDIUMINT经常比INT好一些。
如果可能，声明列为NOT NULL。它使任何事情更快而且你为每列节省一位。注意如果在
你的应用程序中你确实需要NULL，你应该毫无疑问使用它，只是避免缺省地在所有列上
有它。
如果你没有任何变长列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的记录格式。这比较快
但是不幸地可能会浪费一些空间。见10.6 选择一种表类型。
每张桌子应该有尽可能短的主索引。这使一行的辨认容易而有效。
对每个表，你必须决定使用哪种存储/索引方法。见9.4 MySQL表类型。也可参见10.6 选
择一种表类型。
只创建你确实需要的索引。索引对检索有好处但是当你需要快速存储东西时就变得糟糕
。如果你主要通过搜索列的组合来存取一个表，以它们做一个索引。第一个索引部分应
该是最常用的列。如果你总是使用许多列，你应该首先以更多的副本使用列以获得更好
的列索引压缩。
如果很可能一个索引在头几个字符上有唯一的前缀，仅仅索引该前缀比较好。MySQL支持
在一个字符列的一部分上的索引。更短的索引更快，不仅因为他们占较少的磁盘空间而
且因为他们将在索引缓存中给你更多的命中率并且因此有更少磁盘寻道。见10.2.3 调节
服务器参数。
在一些情形下，分割一个经常被扫描进2个表的表是有益的。特别是如果它是一个动态格
式的表并且它可能使一个能用来扫描后找出相关行的较小静态格式的表。
10.4 MySQL索引的使用
索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条
记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于
查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考
虑所有数据。如果一个表有1000行，这比顺序读取至少快100倍。注意你需要存取几乎所
有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。
所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。字符串是自动地压缩前缀和
结尾空间。见7.27 CREATE INDEX句法。
索引用于：
快速找出匹配一个WHERE子句的行。
当执行联结时，从其他表检索行。
对特定的索引列找出MAX()或MIN()值。
如果排序或分组在一个可用键的最左面前缀上进行(例如，ORDER BY key_part_1,key_p
art_2)，排序或分组一个表。如果所有键值部分跟随DESC，键以倒序被读取。
在一些情况中，一个查询能被优化来检索值，不用咨询数据文件。如果对某些表的所有
使用的列是数字型的并且构成某些键的最左面前缀，为了更快，值可以从索引树被检索
出来。
假定你发出下列SELECT语句：
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果一个多列索引存在于col1和col2上，适当的行可以直接被取出。如果分开的单行列
索引存在于col1和col2上，优化器试图通过决定哪个索引将找到更少的行并来找出更具
限制性的索引并且使用该索引取行。
如果表有一个多列索引，任何最左面的索引前缀能被优化器使用以找出行。例如，如果
你有一个3行列索引(col1,col2,col3)，你已经索引了在(col1)、(col1,col2)和(col1,
col2,col3)上的搜索能力。
如果列不构成索引的最左面前缀，MySQL不能使用一个部分的索引。假定你下面显示的S
ELECT语句：
mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
如果一个索引存在于(col1、col2、col3)上，只有上面显示的第一个查询使用索引。第
二个和第三个查询确实包含索引的列，但是(col2)和(col2、col3)不是(col1、col2、c
ol3)的最左面前缀。
如果LIKE参数是一个不以一个通配符字符起始的一个常数字符串，MySQL也为LIKE比较使
用索引。例如，下列SELECT语句使用索引：
mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";
在第一条语句中，只考虑有"Patrick" <= key_col < "Patricl"的行。在第二条语句中
，只考虑有"Pat" <= key_col < "Pau"的行。
下列SELECT语句将不使用索引：
mysql> select * from tbl_name where key_col LIKE "%Patrick%";
mysql> select * from tbl_name where key_col LIKE other_col;
在第一条语句中，LIKE值以一个通配符字符开始。在第二条语句中，LIKE值不是一个常
数。
如果 column_name 是一个索引，使用column_name IS NULL的搜索将使用索引。
MySQL通常使用找出最少数量的行的索引。一个索引被用于你与下列操作符作比较的列：
=、>、>=、<、<=、BETWEEN和一个有一个非通配符前缀象'something%'的LIKE的列。
任何不跨越的在WHERE子句的所有AND层次的索引不用来优化询问。
下列WHERE子句使用索引：
... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
/* optimized like "index_part1='hello'" */
这些WHERE子句不使用索引：
... WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */
... WHERE index=1 OR A=10 /* No index */
... WHERE index_part1=1 OR index_part2=10 /* No index spans all rows */
10.5 存取或更新数据的查询速度
首先，一件事情影响所有的询问。你有的许可系统设置越复杂，你得到更多的开销。
如果你不让任何GRANT语句执行，MySQL将稍微优化许可检查。因此如果你有很大量，值
得花时间来避免授权，否则更多的许可检查有更大的开销。
如果你的问题是与一些明显的MySQL函数有关，你总能在MySQL客户中计算其时间：
mysql> select benchmark(1000000,1+1);
+------------------------+
| benchmark(1000000,1+1) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.32 sec)
上面显示MySQL能在PentiumII 400MHz上以0.32秒执行1,000,000个+表达式。
所有MySQL函数应该被高度优化，但是以可能有一些例外并且benchmark(loop_count,ex
pression)是找出是否你的查询有问题的一个极好工具。
10.5.1 估计查询性能
在大多数情况下，你能通过计算磁盘寻道估计性能。对小的表，你通常能在1次磁盘寻道
中找到行(因为这个索引可能被缓冲)。对更大的表，你能估计它(使用 B++ 树索引)，你
将需要：log(row_count)/log(index_block_length/3*2/(index_length + data_point
er_length))+1次寻道找到行。
在MySQL中，索引块通常是1024个字节且数据指针通常是4个字节，这对一个有一个索引
长度为3(中等整数)的 500,000 行的表给你：log(500,000)/log(1024/3*2/(3+4)) + 1
= 4 次寻道。
象上面的索引将要求大约 500,000 * 7 * 3/2 = 5.2M，(假设索引缓冲区被充满到2/3(
它是典型的))，你将可能在内存中有索引的大部分并且你将可能仅需要1-2调用从OS读数
据来找出行。
然而对于写，你将需要 4 次寻道请求(如上)来找到在哪儿存放新索引并且通常需2次寻
道更新这个索引并且写入行。
注意，上述不意味着你的应用程序将缓慢地以 N log N 退化！当表格变得更大时，只要
一切被OS或SQL服务器缓冲，事情将仅仅或多或少地更慢。在数据变得太大不能被缓冲后
，事情将开始变得更慢直到你的应用程序仅仅受磁盘寻道限制(它以N log N增加)。为了
避免这个增加，索引缓冲随数据增加而增加。见10.2.3 调节服务器参数。
10.5.2 SELECT查询的速度
总的来说，当你想要使一个较慢的SELECT ... WHERE更快，检查的第一件事情是你是否
能增加一个索引。见10.4 MySQL 索引的使用。在不同表之间的所有引用通常应该用索引
完成。你可以使用EXPLAIN来确定哪个索引用于一条SELECT语句。见7.22 EXPLAIN句法(
得到关于一条SELECT的信息)。
一些一般的建议：
为了帮助MySQL更好地优化查询，在它已经装载了相关数据后，在一个表上运行myisamc
hk --analyze。这为每一个更新一个值，指出有相同值地平均行数（当然，对唯一索引
，这总是1。）
为了根据一个索引排序一个索引和数据，使用myisamchk --sort-index --sort-record
s=1（如果你想要在索引1上排序)。如果你有一个唯一索引，你想要根据该索引地次序读
取所有的记录，这是使它更快的一个好方法。然而注意，这个排序没有被最佳地编写，
并且对一个大表将花很长时间！
10.5.3 MySQL怎样优化WHERE子句
where优化被放在SELECT中，因为他们最主要在那里使用里，但是同样的优化被用于DEL
ETE和UPDATE语句。
也要注意，本节是不完全的。MySQL确实作了许多优化而我们没有时间全部记录他们。
由MySQL实施的一些优化列在下面：
删除不必要的括号：
((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)
常数调入：
(a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5
删除常数条件(因常数调入所需)：
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6
索引使用的常数表达式仅计算一次。
在一个单个表上的没有一个WHERE的COUNT(*)直接从表中检索信息。当仅使用一个表时，
对任何NOT NULL表达式也这样做。
无效常数表达式的早期检测。MySQL快速检测某些SELECT语句是不可能的并且不返回行。
如果你不使用GROUP BY或分组函数(COUNT()、MIN()……)，HAVING与WHERE合并。
为每个子联结(sub join)，构造一个更简单的WHERE以得到一个更快的WHERE计算并且也
尽快跳过记录。
所有常数的表在查询中的任何其他表前被首先读出。一个常数的表是：
一个空表或一个有1行的表。
与在一个UNIQUE索引、或一个PRIMARY KEY的WHERE子句一起使用的表，这里所有的索引
部分使用一个常数表达式并且索引部分被定义为NOT NULL。
所有下列的表用作常数表：
mysql> SELECT * FROM t WHERE primary_key=1;
mysql> SELECT * FROM t1,t2
WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
对联结表的最好联结组合是通过尝试所有可能性来找到:(。如果所有在ORDER BY和GROU
P BY的列来自同一个表，那么当廉洁时，该表首先被选中。
如果有一个ORDER BY子句和一个不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含
不是来自联结队列中的第一个表的其他表的列，创建一个临时表。
如果你使用SQL_SMALL_RESULT，MySQL将使用一个在内存中的表。
因为DISTINCT被
--
※ 来源:．武汉白云黄鹤站 bbs.whnet.edu.cn．[FROM: 211.69.195.74]
--
※ 转寄:．武汉白云黄鹤站 bbs.whnet.edu.cn．[FROM: 210.38.240.158]
--
※ 转寄:·快意灌水站 fb2000.dhs.org·[FROM: 210.39.3.50]
--
※ 转载:·BBS 荔园晨风站 bbs.szu.edu.cn·[FROM: 192.168.1.115]
[回到开始]
[上一篇][下一篇]
荔园在线首页 友情链接：深圳大学 深大招生 荔园晨风BBS S-Term软件 网络书店