- 论坛徽章:
- 0
|
刚好这两天有人 有个100W 的表操作速度慢的问题，你可参考:
http://bbs.chinaunix.net/viewthr ... &extra=page%3D1
mysql 对于10~几十万 纪录的表查询最快，继续增加，速度下降明显。
且5.x刚刚实现的子查询和触发器 还不完善。
longvarchar 和 longtext 数据类型 最大只支持4GBytes.
Mysql 5.0.16 手册:
Storage Requirements for String Types
Column Type Storage Required
CHAR(M) M bytes, 0 <= M <= 255
VARCHAR(M) Prior to MySQL 5.0.3: L + 1 bytes, where L <= M and 0 <= M <= 255. MySQL 5.0.3 and later: L + 1 bytes, where L <= M and 0 <= M <= 256 or L + 2 bytes, where L <= M and 256 < M <= 65535 (see note below).
BINARY(M) M bytes, 0 <= M <= 255
VARBINARY(M) L+1 bytes, where L <= M and 0 <= M <= 255
TINYBLOB, TINYTEXT L+1 byte, where L < 28
BLOB, TEXT L+2 bytes, where L < 216
MEDIUMBLOB, MEDIUMTEXT L+3 bytes, where L < 224
LONGBLOB, LONGTEXT L+4 bytes, where L < 232
ENUM('value1','value2',...) 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET('value1','value2',...) 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)
VARCHAR and the BLOB and TEXT types are variable-length types. For each, the storage requirements depend on the actual length of column values (represented by L in the preceding table), rather than on the type's maximum possible size. For example, a VARCHAR(10) column can hold a string with a maximum length of 10. The actual storage required is the length of the string (L), plus 1 byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is 5 bytes.
For the CHAR, VARCHAR, and TEXT types, the values L and M in the preceding table should be interpreted as number of characters, and lengths for these types in column specifications indicate the number of characters. For example, to store a TINYTEXT value requires L characters + 1 byte.
To calculate the number of bytes used to store a particular CHAR, VARCHAR, or TEXT column value, you will need to take into account the character set in use for that column. In particular, when using Unicode, you must keep in mind that not all Unicode characters use the same number of bytes. For a breakdown of the storage used for different categories of Unicode characters, see Section 10.5, “Unicode Support”. You can also obtain this information from the Maxlen column in the output of SHOW CHARSET.
Note: In MySQL 5.0.3 and later, the effective maximum length for a VARCHAR column is 65,532 characters.
As of MySQL 5.0.3, the NDBCLUSTER engine supports only fixed-width columns. This means that a VARCHAR column from a table in a MySQL Cluster will behave as follows:
If the size of the column is 256 characters or fewer, then the column requires one extra byte of storage per row.
If the size of the column is geater than 256 characters, then the column requires 2 bytes extra storage per row.
Note that the number of bytes required per character varies according to the character set used. For example, if a VARCHAR(100) column in a Cluster table uses the utf-8 character set, then each character requires 3 bytes storage. This means that each record in such a column takes up 100 × 3 + 1 = 301 bytes for storage, regardless of the length of the string actually stored in any given record. For a VARCHAR(1000) column in a table using the NDBCLUSTER storage engine with the utf-8 character set, each record will use 1000 × 3 + 2 = 3002 bytes storage; that is, the column is 1,000 characters wide, each character requires 3 bytes storage, and each record has a 2-byte overhead because 1,000 > 256.
The BLOB and TEXT types require 1, 2, 3, or 4 bytes to record the length of the column value, depending on the maximum possible length of the type. See Section 11.4.3, “The BLOB and TEXT Types”.
TEXT and BLOB columns are implemented differently in the NDB Cluster storage engine, wherein each record in a TEXT column is made up of two separate parts. One of these is of fixed size (256 bytes), and is actually stored in the original table. The other consists of any data in excess of 256 bytes, which stored in a hidden table. The records in this second table are always 2,000 bytes long. This means that the size of a TEXT column is 256 if size <= 256 (where size represents the size of the record); otherwise, the size is 256 + size + (2000 – (size – 256) % 2000).
The size of an ENUM object is determined by the number of different enumeration values. One byte is used for enumerations with up to 255 possible values. Two bytes are used for enumerations having between 256 and 65,535 possible values. See Section 11.4.4, “The ENUM Type”.
The size of a SET object is determined by the number of different set members. If the set size is N, the object occupies (N+7)/8 bytes, rounded up to 1, 2, 3, 4, or 8 bytes. A SET can have a maximum of 64 members. See Section 11.4.5, “The SET Type”.
限制还是比较厉害的。
[ 本帖最后由 likuku 于 2006-2-21 13:02 编辑 ] |
|
免费注册
发表于 2006-02-21 12:11
ostgreSQL 的昨天今 ... 
