MySQL优化之数据表的处理永利网址

摘要:本文介绍了有关数据表的优化技巧,主要内容有,选择表的类型,打开尽量少的表,锁定表与查询速度的关系以及如何优化表以达到提高查询速度的目的。由于数据的录入和清除操作,很容易在表的数据文件重产生碎片,对于拥有大型表的数据库,应该经常使用
myisamchk维护程序来清除碎片,优化表时要选择一个好的时机,尽量在没有用户访问时优化表。

MySQL还允许影响语句的调度特性,这样会使来自几个客户机的查询更好地协作,从而单个客户机不会被锁定太长的时间。更改调度特性还能保证特定的查询处理得更快。我们先来看一下MySQL的缺省调度策略,然后来看看为改变这个策略可使用什么样的选项。出于讨论的目的,假设执行检索
SELECT)的客户机程序为读取程序。执行修改表操作 DELETE,INSERT,REPLACE
或UP DATE)的另一个客户机程序为写入程序。

选择合适的表的类型,防止数据文件中产生碎块,同样可以大大优化检索的速度,是数据库的性能最大化。

MySQL的基本调度策略可总结如下:
写入请求应按其到达的次序进行处理。
写入具有比读取更高的优先权。

选择一种表类型

在表锁的帮助下实现调度策略。客户机程序无论何时要访问表,都必须首先获得该表的锁。可以直接用LOCK
TABLES
来完成这项工作,但一般服务器的锁管理器会在需要时自动获得锁。在客户机结束对表的处理时,可释放表上的锁。直接获得的锁可用UNLOCK
TABLES 释放,但服务器也会自动释放它所获得的锁。

用MySQL,当前(版本 3 .23.5)你能从一个速度观点在4可用表的格式之间选择。

执行写操作的客户机必须对表具有独占访问的锁。在写操作进行中,由于正在对表进行数据记录的删除、增加或更改,所以该表处于不一致状态,而且该表上的索引也可能需要作相应的更新。如果表处于不断变化中,此时允许其他客户机访问该表会出问题。让两个客户机同时写同一个表显然不好,因为这样会很快使该表不可用。允许客户机读不断变化的表也不是件好事,因为可能在读该表的那一刻正好正在对它进行更改,其结果是不正确的。执行读取操作的客户机必须有一把防止其他客户机写该表的锁,以保证读表的过程中表不出现变化。不过,该锁无需对读取操作提供独占访问。此锁还允许其他客户机同时对表进行读取。读取不会更改表,所有没必要阻止其它客户机对该表进行读取。

·静态MyISAM

MySQL允许借助几个查询限修饰符对其调度策略施加影响。其中之一是DELETE、INSERT、LOAD
DATA、REPLACE 和UP DATE 语句的LOW_PRIORITY 关键字。另一个是SELECT
语句的HIGH_PRIORITY 关键字。第三个是INSERT 和REPLACE 语句的DELAYED
关键字。

永利网址,这种格式是最简单且最安全的格式,它也是在磁盘格式最快的。速度来自于数据能在磁盘上被找到的难易方式。当所定有一个索引和静态格式的东西时,它很简单,只是行长度乘以行数量。而且在扫描一张表时,用每次磁盘读取来读入常数个记录是很容易的。安全性来自于如果当写入一个静态MyISAM文件时,你的计算机崩溃,myisamchk能很容易指出每行在哪儿开始和结束,因此它通常能回收所有记录,除了部分被写入的那个。注意,在MySQL中,所有索引总能被重建。

LOW_PRIORITY
关键字按如下影响调度。一般情况下,如果某个表的写入操作在表正被读取时到达,写入程序被阻塞,直到读取程序完成,因为一旦某个查询开始,就不能中断。如果另一读取请求在写入程序等待时到达,此读取程序也被阻塞,因为缺省的调度策略为写入程序具有比读取程序高的优先级。在第一个读取程序结束时,写入程序继续,在此写入程序结束时,第二个读取程序开始。

·动态MyISAM

如果写入请求为LOW_PRIORITY
的请求,则不将该写入操作视为具有比读取操作优先级高的操作。在此情形下,如果第二个读取请求在写入程序等待时到达,则让第二个读取操作排在等待的写入操作之前。仅当没有其他读取请求时,才允许写入程序执行。这种调度的更改从理论上说,其含义为LOW_PRIORITY
写入可能会永远被阻塞。当正在处理前面的读取请求时,只要另一个读取请求到达,这个新的请求允许排在LOW_PRIORITY
写入之前。

这种格式有点复杂,因为每一行必须有一个头说明它有多长。当一个记录在更改时变长时,它也可以在多于一个位置上结束。你能使用
OPTIMIZE
table或myisamchk整理一张表。如果你在同一个表中有象某些VARCHAR或BLOB列那样存取/改变的静态数据,将动态列移入另外一个表以避免碎片可能是一个好主意。

SELECT 查询的HIGH_PRIORITY 关键字作用类似。它使SELECT
插在正在等待的写入操作之前,即使该写入操作具有正常的优先级。INSERT
的DELAYED 修饰符作用如下,在表的一个INSERT DELAYED
请求到达时,服务器将相应的行放入一个队列,并立即返回一个状态到客户机程序,以便该客户机程序可以继续执行,即使这些行尚未插入表中。如果读取程序正在对表进行读取,那么队列中的行挂起。在没有读取时,服务器开始开始插入延迟行队列中的行。服务器不时地停下来看看是否有新的读取请求到达,并进行等待。如果是这样,延迟行队列将挂起,并允许读取程序继续。在没有其他的读取操作时,服务器再次开始插入延迟行。这个过程一直进行到延迟行队列空为止。

·压缩MyISAM

此调度修饰符并非出现在所有MySQL版本中。下面的表列出了这些修饰符和支持这些修饰符的MySQL版本。可利用此表来判断所使用的MySQL版本具有什么样的功能:

这是一个只读类型,用可选的myisampack工具生成。

永利网址 1

·内存(HEAP 堆)

INSERT DELAYED 在客户机方的作用

这种表格式对小型/中型查找表十分有用。对拷贝/创建一个常用的查找表(用联结)到一个(也许临时)HEAP表有可能加快多个表联结。假定我们想要做下列联结,用同样数据可能要几倍时间。

如果其他客户机可能执行冗长的SELECT
语句,而且您不希望等待插入完成,此时INSERT DELAYED 很有用。发布INSERT
DELAYED
的客户机可以更快地继续执行,因为服务器只是简单地将要插入的行插入。不过应该对正常的INSERT
和INSERT DELAYED 性能之间的差异有所认识。如果INSERT DELAYED
存在语法错误,则向客户机发出一个错误,如果正常,便不发出信息。例如,在此语句返回时,不能相信所取得的AUTO_INCREMENT
值。也得不到惟一索引上的重复数目的计数。之所以这样是因为此插入操作在实际的插入完成前返回了一个状态。其他还表示,如果INSERT
DELAYED 语句的行在等待插入中被排队,并且服务器崩溃或被终止用kill
-9),那么这些行将丢失。正常的TERM
终止不会这样,服务器会在退出前将这些行插入。

SELECT tab1.a, tab3.a FROM tab1, tab2, tab3
WHERE tab1.a = tab2.a and tab2.a = tab3.a and tab2.c != 0;

http://www.bkjia.com/Mysql/582264.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/582264.htmlTechArticleMySQL还允许影响语句的调度特性,这样会使来自几个客户机的查询更好地协作,从而单个客户机不会被锁定太长的时间。更改调度特性还能

为了加速它,我们可用tab2和tab3的联结创建一张临时表,因为用相同列( tab1.a
)查找。这里是创建该表和结果选择的命令。

CREATE TEMPORARY TABLE test TYPE=HEAP
        SELECT
                tab2.a as a2, tab3.a as a3
        FROM
                tab2, tab3
        WHERE
                tab2.a = tab3.a and c = 0;
SELECT tab1.a, test.a3 from tab1, test where tab1.a = test.a1;
SELECT tab1.b, test.a3 from tab1, test where tab1.a = test.a1 and something;

静态(定长)表的特点

这是缺省格式。它用在表不包含VARCHAR、BLOB或TEXT列时候。

所有的CHAR、NUMERIC和DECIMAL列充填到列宽度。

非常快。

容易缓冲。

容易在崩溃后重建,因为记录位于固定的位置。

不必被重新组织(用myisamchk),除非一个巨量的记录被删除并且你想要归还空闲磁盘空间给操作系统。

通常比动态表需要更多的磁盘空间。

动态表的特点

如果表包含任何VARCHAR、BLOB或TEXT列,使用该格式。

所有字符串列是动态的(除了那些长度不到4的列)。

每个记录前置一个位图,对字符串列指出哪个列是空的(”),或对数字列哪个是零(这不同于包含NULL值的列)。如果字符串列在删除尾部空白后有零长度,或数字列有零值,它在位图中标记并且不保存到磁盘上。非空字符串存储为一个长度字节加字符串内容。

通常比定长表占更多的磁盘空间。

每个记录仅使用所需的空间。如果一个记录变得更大,它按需要被切开多段,这导致记录碎片。

如果你与超过行长度的信息更新行,行将被分段。在这种情况中,你可能必须时时运行myisamchk
-r以使性能更好。使用myisamchk -ei tbl_name做一些统计。

在崩溃后不容易重建,因为一个记录可以是分很多段并且一个连接(碎片)可以丢失。

对动态尺寸记录的期望行长度是:

3
+ (number of columns + 7) / 8
+ (number of char columns)
+ packed size of numeric columns
+ length of strings
+ (number of NULL columns + 7) / 8

对每个连接有6个字节的惩罚。无论何时更改引起记录的增大,一个动态记录被链接。每个新链接将至少是20个字节,因此下一增大将可能在同一链连中。如果不是,将有另外一个链接。你可以用myisamchk
-ed检查有多少链接。所有的链接可以用 myisamchk -r 删除。

压缩表的特点

一张用myisampack实用程序制作的只读表。所有具有MySQL扩展电子邮件支持的客户可以为其内部使用保留一个myisampack拷贝。

解压缩代码存在于所有MySQL分发,以便甚至没有myisampack的客户能读取用myisampack压缩的表。

占据很小的磁盘空间,使磁盘使用量减到最小。

每个记录被单独压缩(很小的存取开销)。对一个记录的头是定长的(1-3
字节),取决于表中最大的记录。每列以不同方式被压缩。一些压缩类型是:

通常对每列有一张不同的哈夫曼表。

后缀空白压缩。

前缀空白压缩。

用值0的数字使用1位存储。

如果整数列的值有一个小范围,列使用最小的可能类型来存储。例如,如果所有的值在0到255的范围,一个BIGINT列(8个字节)可以作为一个TINYINT列(1字节)存储。

如果列仅有可能值的一个小集合,列类型被变换到ENUM。

列可以使用上面的压缩方法的组合。

能处理定长或动态长度的记录,然而不能处理BLOB或TEXT列。

能用myisamchk解压缩。

MySQL能支持不同的索引类型,但是一般的类型是ISAM。这是一个B树索引并且你能粗略地为索引文件计算大小为(key_length+4)*0.67,在所有的键上的总和。(这是对最坏情况,当所有键以排序顺序被插入时。)

字符串索引是空白压缩的。如果第一个索引部分是一个字符串,它也将压缩前缀。如果字符串列有很多尾部空白或是一个总不能用到全长的VARCHAR列,空白压缩使索引文件更小。如果很多字符串有相同的前缀,前缀压缩是有帮助的。

MySQL内存表的特点

堆表仅存在于内存中,因此如果mysqld被关掉或崩溃,它们将丢失,但是因为它们是很快,不管怎样它们是有用的。

MySQL内部的HEAP表使用没有溢出区的100%动态哈希并且没有与删除有关的问题。

你只能通过使用在堆表中的一个索引的用等式存取东西(通常用=操作符)。

堆表的缺点是: