MySQL选择一种表类型.docx

MySQL选择一种表类型用MySQL，当前(版本 3.23.5)你能从一个速度观点在4可用表的格式之间选择。 静态MyISAM 这种格式是最简单且最安全的格式，它也是在磁盘格式最快的。速度来自于数据能在磁盘上被找到的难易方式。当所定有一个索引和静态格式的东西时，它很简单，只是行长度乘以行数量。而且在扫描一张表时，用每次磁盘读取来读入常数个记录是很容易的。安全性来自于如果当写入一个静态MyISAM文件时，你的计算机崩溃，myisamchk能很容易指出每行在哪儿开始和结束，因此它通常能回收所有记录，除了部分被写入的那个。注意，在MySQL中，所有索引总能被重建。 动态MyISAM 这种格式有点复杂，因为每一行必须有一个头说明它有多长。当一个记录在更改时变长时，它也可以在多于一个位置上结束。你能使用OPTIMIZE table或myisamchk整理一张表。如果你在同一个表中有象某些VARCHAR或BLOB列那样存取/改变的静态数据，将动态列移入另外一个表以避免碎片可能是一个好主意。 压缩MyISAM 这是一个只读类型，用可选的myisampack工具生成。 内存(HEAP 堆） 这种表格式对小型/中型查找表十分有用。对拷贝/创建一个常用的查找表(用联结)到一个(也许临时)HEAP表有可能加快多个表联结。假定我们想要做下列联结，用同样数据可能要几倍时间。 SELECT tab1.a, tab3.a FROM tab1, tab2, tab3 WHERE tab1.a = tab2.a and tab2.a = tab3.a and tab2.c != 0;为了加速它，我们可用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;10.6.1 静态(定长)表的特点 这是缺省格式。它用在表不包含VARCHAR、BLOB或TEXT列时候。 所有的CHAR、NUMERIC和DECIMAL列充填到列宽度。 非常快。 容易缓冲。 容易在崩溃后重建，因为记录位于固定的位置。 不必被重新组织(用myisamchk)，除非一个巨量的记录被删除并且你想要归还空闲磁盘空间给操作系统。 通常比动态表需要更多的磁盘空间。 10.6.2 动态表的特点 如果表包含任何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 删除。 10.6.3 压缩表的特点 一张用myisampack实用程序制作的只读表。所有具有MySQL扩展电子邮件支持的客户可以为其内部使用保留一个myisampack拷贝。 解压缩代码存在于所有MySQL分发，以便甚至没有myisampack的客户能读取用myisampack压缩的表。 占据很小的磁盘空间，使磁盘使用量减到最小。 每个记录被单独压缩(很小的存取开销)。对一个记录的头是定长的(1-3 字节)，取决于表中最大的记录。每列以不同方式被压缩。一些压缩类型是： o 通常对每列有一张不同的哈夫曼表。 o 后缀空白压缩。 o 前缀空白压缩。 o 用值0的数字使用1位存储。 o 如果整数列的值有一个小范围，列使用最小的可能类型来存储。例如，如果所有的值在0到255的范围，一个BIGINT列(8个字节)可以作为一个TINYINT列(1字节)存储。 o 如果列仅有可能值的一个小集合，列类型被变换到ENUM。 o 列可以使用上面的压缩方法的组合。 能处理定长或动态长度的记录，然而不能处理BLOB或TEXT列。 能用myisamchk解压缩。 MySQL能支持不同的索引类型，但是一般的类型是ISAM。这是一个B树索引并且你能粗略地为索引文件计算大小为(key_length+4)*0.67，在所有的键上的总和。（这是对最坏情况，当所有键以排序顺序被插入时。）字符串索引是空白压缩的。如果第一个索引部分是一个字符串，它也将压缩前缀。如果字符串列有很多尾部空白或是一个总不能用到全长的VARCHAR列，空白压缩使索引文件更小。如果很多字符串有相同的前缀，前缀压缩是有帮助的。10.6.4 内存表的特点堆桌子仅存在于内存中，因此如果mysqld被关掉或崩溃，它们将丢失，但是因为它们是很快，不管怎样它们是有用的。MySQL内部的HEAP表使用没有溢出区的100%动态哈希并且没有与删除有关的问题。你只能通过使用在堆表中的一个索引的用等式存取东西(通常用=操作符)。堆表的缺点是： 1. 你要为你想要同时使用的所有堆表需要足够的额外内存。 2. 你不能在索引的一个部分上搜索。 3. 你不能顺序搜索下一个条目(即使用这个索引做一个ORDER BY)。 4. MySQL也不能算出在2个值之间大概有多少行。这被优化器使用来决定使用哪个索引，但是在另一方面甚至不需要磁盘寻道。 10.7 其他优化技巧对加快系统的未分类的建议是： 使用持久的连接数据库以避免连接开销。 总是检查你的所有询问确实使用你已在表中创建了的索引。在MySQL中，你可以用EXPLAIN命令做到。见7.22 EXPLAIN句法(得到关于SELECT的信息)。 尝试避免在被更改了很多的表上的复杂的SELECT查询。这避免与锁定表有关的问题。 在一些情况下，使得基于来自其他表的列的信息引入一个“ 哈希”的列有意义。如果该列较短并且有合理的唯一值，它可以比在许多列上的一个大索引快些。在MySQL中，很容易使用这个额外列：SELECT * from table where hash=calculated hash on col1 and col2 and col_1=constant and col_2=constant and . 。 对于有很多更改的表，你应该试着避免所有VARCHAR或BLOB列。只要你使用单个VARCHAR或BLOB列，你将得到动态行长度。见9.4 MySQL表类型。 只是因为行太大，分割一张表为不同的表一般没有什么用处。为了存取行，最大的性能命冲击是磁盘寻道以找到行的第一个字节。在找到数据后，大多数新型磁盘对大多数应用程序来说足够快，能读入整个行。它确实有必要分割的唯一情形是如果其动态行尺寸的表(见上述)能变为固定的行大小，或如果你很频繁地需要扫描表格而不需要大多数列。见9.4 MySQL表类型。 如果你很经常地需要基于来自很多行的信息计算(如计数)，引入一个新表并实时更新计数器可能更好一些。类型的更改UPDATE table set count=count+1 where index_column=constant是很快的！当你使用象MySQL那样的只有表级锁定(多重读/单个写)的数据库时，这确实重要。这也将给出大多数数据库较好的性能，因为锁定管理器在这种情况下有较少的事情要做。 11111111111111111111111 如果你需要从大的记录文件表中收集统计信息，使用总结性的表而不是扫描整个表。维护总结应该比尝试做“实时”统计要快些。当有变化而不是必须改变运行的应用时，从记录文件重新生成新的总结表(取决于业务决策)要快多了！ 如果可能，应该将报告分类为“实时”或“统计”，这里统计报告所需的数据仅仅基于从实际数据产生的总结表中产生。 充分利用列有缺省值的事实。当被插入值不同于缺省值时，只是明确地插入值。这减少MySQL需要做的语法分析并且改进插入速度。 在一些情况下，包装并存储数据到一个BLOB中是很方便的。在这种情况下，你必须在你的应用中增加额外的代码来打包/解包BLOB中的东西，但是这种方法可以在某些阶段节省很多存取。当你有不符合静态的表结构的数据时，这很实用。 在一般情况下，你应该尝试以第三范式保存数据，但是如果你需要这些以获得更快的速度，你应该不用担心重复或创建总结表。 存储过程或UDF(用户定义函数)可能是获得更好性能的一个好方法，然而如果你使用某些不支持它的数据库，在这种情况中，你应该总是有零一个方法(较慢的)做这些。 你总是能通过在你的应用程序中缓冲查询/答案并尝试同时做很多插入/更新来获得一些好处。如果你的数据库支持锁定表(象MySQL和Oracle)，这应该有助于确保索引缓冲在所有更新后只清空一次。 但你不知道何时写入你的数据时，使用INSERT /*! DELAYED */。这加快处理，因为很多记录可以用一次磁盘写入被写入。 当你想要让你的选择显得更重要时，使用INSERT /*! LOW_PRIORITY */。 使用SELECT /*! HIGH_PRIORITY */来取得塞入队列的选择，它是即使有人等待做一个写入也要完成的选择。 使用多行INSERT语句来存储很多有一条SQL命令的行(许多SQL服务器支持它）。 使用LOAD DATA INFILE装载较大数量的数据。这比一般的插入快并且当myisamchk集成在mysqld中时，甚至将更快。 使用AUTO_INCREMENT列构成唯一值。 当使用动态表格式时，偶尔使用OPTIMIZE TABLE以避免碎片。见7.9O PTIMIZE TABLE句法。 可能时使用HEAP表以得到更快的速度。见9.4 MySQL表类型。 当使用一个正常Web服务器设置时，图象应该作为文件存储。这仅在数据库中存储的一本文件的引用。这样做的主要原因是是一个正常的Web服务器在缓冲文件比数据库内容要好得多，因此如果你正在使用文件，较容易得到一个较快的系统。 对经常存取的不重要数据(象有关对没有cookie用户最后显示标语的信息）使用内存表。 在不同表中具有相同信息的列应该被声明为相同的并有相同的名字。在版本 3.23 前，你只能靠较慢的联结。尝试使名字简单化(在客户表中使用name而不是customer_name)。为了使你的名字能移植到其他SQL服务器，你应该使他们短于18 个字符。 如果你需要确实很高的速度，你应该研究一下不同SQL服务器支持的数据存储的底层接口！例如直接存取MySQL MyISAM，比起使用SQL 接口，你能得到2-5倍的速度提升。然而为了