牛刀小试MySQL学习-String Types

Storage Requirements for String Types

Data Type	Storage Required
CHAR(M)	M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set
BINARY(M)	M bytes, 0 <= M <= 255
VARCHAR(M), VARBINARY(M)	L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes
TINYBLOB, TINYTEXT	L + 1 bytes, 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)

char&varchar：

一个是定长字符类型，一个是可变长字符类型。char(M)（0~255），存储需求：M*w bytes.意思就是说：这个w嘛，它还得根据字符集，例如latin字符集

是1字节，gb2312是2字节，utf8是三字节。所以，create table t3(name char(255)) character set utf8;这name列存储的应该是255*3的字节。

varchar则是可变存储数据（0~65535），可以节省存储空间。但它有个前缀长度1字节，这个基本上可以猜测它用来定义可以长度的标识量。因为1字节=>8bit=>256

所以，varchar超过255字节后，前缀长度就得使用两个字节了。

以下表格，是官方给出的char和varchar对应的存储字节。

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
''	'    '	4 bytes	''	1 byte
'ab'	'ab  '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

char(4)就一直是固定长度

varchar(4)就是可变长度，前面有1字节的前缀长度，超过255字节，就是2字节的前缀长度

性能的比较：由于，char是固定长度的，所以是用空间来换取时间，所以char的查询速度是比varchar的查询速度要快的。

但是对于Innodb存储引擎来说：内部的行存储就本身没有区分固定长度和可变长度列（数据行是使用了头指针，来指向数据列值的），所以这里

使用varchar更好，性能也不比char差

BINARY&VARBINARY：

BINARY和VARBINARY类似于CHAR和VARCHAR，不同的是她们包含二进制字符串而不包含非二进制字符串。

mysql> create table t(C binary(3));

Query OK, 0 rows affected (0.15 sec)

mysql> insert into t values('a');

Query OK, 1 row affected (0.05 sec)

mysql> select * from t;

+------+

| C    |

+------+

| a    |

+------+

1 row in set (0.00 sec)

mysql> select *,hex(c),c='a',c='a\0',c='a\0\0' from t;

+------+--------+-------+---------+-----------+

| C    | hex(c) | c='a' | c='a\0' | c='a\0\0' |

+------+--------+-------+---------+-----------+

| a    | 610000 |     0 |       0 |         1 |

+------+--------+-------+---------+-----------+

1 row in set (0.02 sec)

从上面可以看出，它在保存a值的时候，会在最后默认填充'0x00'(零字节)，所以c='a\0\0' boolean返回的值就是1了。

TEXT&BLOB

TEXT保存较大的文本，BLOB保存二进制数据。存储的形式上面的表格有描述。

BLOB和TEXT的数据在被删除时，会引起"空洞"的记录。但是innodb可以阻止这个情况的发生

mysql> create table t8(id int,context text);

mysql> insert into t10 values(1,repeat('zsdzsd',100));（重复插入到，有几十万条数据）

mysql> insert into t10 select * from t10;

查看ibd

[root@localhost test]# du -sh t10.*

12K     t10.frm

269M    t10.ibd

和ibdata1

[root@localhost innodb_ts]# du -sh ./*

2.1G    ./ibdata1

再把数据删除。

mysql> delete from t10 where id = 1;

Query OK, 131072 rows affected (37.98 sec)

再次查看ibd和ibdata1，发现大小没变，这里就出现了空洞

在innodb存储引擎中，是无法使用optimize的，需要使用recreate和analyze代替。

mysql> optimize table t10;

+----------+----------+----------+-------------------------------------------------------------------+

| Table    | Op       | Msg_type | Msg_text                                                          |

+----------+----------+----------+-------------------------------------------------------------------+

| test.t10 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |

| test.t10 | optimize | status   | OK                                                                |

+----------+----------+----------+-------------------------------------------------------------------+

2 rows in set (2 min 36.33 sec)

虽然这样看起来，像是造成了空洞，但是如果你在插入一张表

mysql> create table t11(name varchar(20));

Query OK, 0 rows affected (0.08 sec)

mysql> insert into t11 values(repeat('zsd',10));

Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> insert into t11 select * from t11;（继续重复插入50w条数据）

Query OK, 1 row affected (0.04 sec)

Records: 1  Duplicates: 0  Warnings: 0

再去查看系统层面的大小：（发现，数据已经被释放了，t11正好用上了t10释放的空间，但是ibddata还是那么大）

[root@localhost innodb_ts]# du -sh ./*

2.1G    ./ibdata1

[root@localhost test]# du -sh t11.*

12K     t11.frm

61M     t11.ibd

[root@localhost test]# du -sh t10.*

12K     t10.frm

181M    t10.ibd

关于排序的问题：TEXT和BLOB排序的时候，会用上临时表，这样对性能来说是有下降的。

使用合成的(Synthetic)索引,可以提高大文本字段的查询性能，适合精确查询，不适合模糊查询。

create table t12(id int,context text,hash_value varchar(40));

insert into t12 values(1,  repeat ('zsd',2),md5(context));

insert into t12 values(2,  repeat ('zsd',3),md5(context));

insert into t12 values(3,  repeat ('zsd2008',3),md5(context));

mysql> select * from t12;

+------+-----------------------+----------------------------------+

| id   | context               | hash_value                       |

+------+-----------------------+----------------------------------+

|    1 | zsdzsd                | 0a8a135e9ae7d104ae7a81f4733b66cd |

|    2 | zsdzsdzsd             | 912d5bffb559146700698871e7730447 |

|    3 | zsd2008zsd2008zsd2008 | 93d60b10af275780ff14ce2de0e20445 |

+------+-----------------------+----------------------------------+

3 rows in set (0.07 sec)

mysql> select * from t12 where hash_value=md5('zsdzsd');

+------+---------+----------------------------------+

| id   | context | hash_value                       |

+------+---------+----------------------------------+

|    1 | zsdzsd  | 0a8a135e9ae7d104ae7a81f4733b66cd |

+------+---------+----------------------------------+

1 row in set (0.00 sec)

ENUM&SET

ENUM：对1~255各成员，存储一个字节，对于255~65535个成员，存储两个字节。

SET：1~8个成员：占1字节

          9~16成员:占2字节

          17~24成员：占3个字节

          25~32成员：占4个字节

          33~64成员：占8个字节

   

例子：create table t13(gender enum('M','F'));

           insert into t13 values('M'),('1'),('f'),(NULL);其他的字会默认变为M，NULL值还是NULL，忽略大小写

      

           create table t14(col set('a','b','c','d'));

           insert into t14 values('a,b'),('a,b,a'),('a,b'),('a,c'),('a'),('a,b,c,d');(就是可以组合自由搭配的存储到表中)