6.数据类型的选择

1.varchar和char

varchar

可变长度的字符类型

• 优点: 节省存储空间
• 缺点: 处理速度较慢,但随着MySQL的不断升级,varchar类型的性能也在不断改善.

char

固定长度字符类型,在检索时会去除尾部空格

• 优点; 在进行处理时速度要比varchar类型快的多
• 缺点: 占用存储空间大.

不同存储引擎下varchar和char的选择

MyISAM

在MyISAM存储引擎下应该选用char类型的数据代替varchar类型的数据

MEMORY

目前都使用char类型的数据行存储,因为无论是char类型还是varchar类型,都会当做char类型看待.

InnoDB

使用varchar类型,对于InnoDB数据表,内部的行存储,没用区分固定成都和可变长度列(所有数据行都使用指向数据列值的头指针),因此使用char不一定要比使用varchar的性能好,因此影响性能的主要因素是数据行使用的存储总量,因此使用可变长度的varchar可以提高磁盘的I/O性能.

2.TEXT与BLOB

在存储较少字符串的数据时使用char或varchar类型里存储,但是当列的数据是一个较长的文本时,就需要使用TEXT和BLOB,他们之间的主要区别是:BLOB可以保存二进制的数据(比如图片),而 TEXT只能保存字符串文本(比如一片文章).

TEXT

TEXT包括TEXT 、MEDIUMTEXT、LONGTEXT三种类型，他们之间的区别是存储字节和存储长度的不同，在实际应用中因该根据具体的使用场景来选择满足需求的最小类性。

BLOB

BLOB包括BLOB、MEDIUMBLOB、LONGBLOB，他们之间的区别同样也是存储i字节和存储长度的不同，根据具体的业务场景选择最小的数据类性。

性能问题

在TEXT和BLOB进行大量的删除操做时，会引起性能问题。在数据删除后，会在表中留下数据空洞，在以后进行插入操作时会因为这些空洞影响性能，所以为了提高性能，应该定期使用 OPTIMIZE TABLE来对这些表进行碎片整理，这样可以避免因为空洞问题影响性能。

示例

• 创建表blog

create  table blog(id int,context text);
--插入数据
insert into blog values(1,repeat('bjfftyfyft',100));
insert into blog values(2,repeat('efgdghfghf',100));
insert into blog values(3,repeat('retrthhgg',100));
insert into blog values(4,repeat('grgr425ghn',100));
insert into blog select * from blog;

• 查看表blog的物理文件大小

• 删除表中id为1的数据

delete from blog where id = 1;
--Query OK, 131072 rows affected (1 min 21.03 sec)

• 再次查看blog表物理文件大小

可以看到明明已经删除了部分数据，但是表的物理存储空间却没有变化

• 使用 OPTIMIZE TABLE来整理磁盘碎片

OPTIMIZE TABLE blog;
--+-----------+----------+----------+-------------------------------------------------------------------+
--| Table     | Op       | Msg_type | Msg_text                                                          |
--+-----------+----------+----------+-------------------------------------------------------------------+
--| test.blog | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
--| test.blog | optimize | status   | OK                                                                |
--+-----------+----------+----------+-------------------------------------------------------------------+
--2 rows in set (1 min 26.01 sec)

• 再次查看表物理存储空间大小

可以看到磁盘占用空间已经减少。

查询性能的优化

精确匹配

可以使用合成的索引(Synthteic)来提高大文本字段的查询性能,但是这种技术只能由于精确匹配的查询,总的来说就是将大文本字段通过MD5,或其它函数生成散列值,如果散列值末尾有空格,不应该将散列值存储在char或varchar类型中,因为会受到尾部自动去除空格的影响.通过查询散列值比直接查询文本的效率要高的多.

示例

• 创建表

create table textdemo(id int(4),context text,hash_code varchar(40));

--mysql> create table textdemo(id int(4),context text,hash_code varchar(40));
--Query OK, 0 rows affected (0.26 sec)

• 插入数据

insert  into textdemo values(1,repeat('d是大V大V把法国人',2),MD5(context));
--Query OK, 1 row affected (0.05 sec)
insert into textdemo values(2,repeat('是大VSV分为非',4),MD5(context));
--Query OK, 1 row affected (0.07 sec)
insert into textdemo values(3,repeat('如果热合各个个',3),MD5(context));
--Query OK, 1 row affected (0.07 sec)
select * from textdemo;
--+------+--------------------------------------------------------------------------+----------------------------------+
--| id   | context                                                            | hash_code                        |
--+------+--------------------------------------------------------------------------+----------------------------------+
--|    1 | d是大V大V把法国人d是大V大V把法国人                                     | b59cafc02cc52ee35066ad558ee53004 |
--|    2 | 是大VSV分为非是大VSV分为非是大VSV分为非是大VSV分为非                     | 11e3260d402ae5896afe098540a1d482 |
--|    3 | 如果热合各个个如果热合各个个如果热合各个个                               | 0bfd676ee34323f36a2a206e85b75ae6  |
--+------+--------------------------------------------------------------------------+----------------------------------+
--3 rows in set (0.00 sec)

• 通过散列值来查询数据

select * from textdemo where hash_code = MD5('d是大V大V把法国人d是大V大V把法国人');

--+------+--------------------------------------------------+----------------------------------+
--| id   | context                                          | hash_code                        |
--+------+--------------------------------------------------+----------------------------------+
--|    1 | d是大V大V把法国人d是大V大V把法国人                   | b59cafc02cc52ee35066ad558ee53004 |
--+------+--------------------------------------------------+----------------------------------+
--1 row in set (0.00 sec)

模糊匹配

上面例子只展示了精确匹配,如果想要对TEXT等字段进行模糊查询,可以对字段的前缀建立散列索引

create index idx_text on textdemo(context(100));
--Query OK, 0 rows affected (0.35 sec)
--Records: 0  Duplicates: 0  Warnings: 0

--进行模糊查询
select * from textdemo where context like '如果热%';
--+------+-----------------------------------------------------------------+----------------------------------+
--| id   | context                                                         | hash_code                        |
--+------+-----------------------------------------------------------------+----------------------------------+
--|    3 | 如果热合各个个如果热合各个个如果热合各个个                      		 | 0bfd676ee34323f36a2a206e85b75ae6 |
--+------+-----------------------------------------------------------------+----------------------------------+

%不能放在最前面

在不必要时避免检索大型的TEXT、BLOB值

如果不确定具体需要查询哪一行，而是满屋目的的查询。只会在网络间徒劳的传输大量数据。

把TEXT或BLOB列分离到单独的表中

在某些环境中，如果把这些数据列移动到第二张数据表中，可以把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，可以得到固定长度数据行的性能优势。它还可以使主数据表在运行 SELECT * 查询的时候不会通过网络传输大量的 BLOB 或 TEXT 值。

3.浮点数与定点数

浮点数一般保存含有小数位的数值，当字段被定义位浮点类型后，如过插入的数据超过字段定义的长度，那么就会保存四舍五入后的数据，四舍五入的过程不会报错。

定点数与浮点数不同，在数据表中实际是以字符串的方式保存的，相对于浮点类型更加精确的保存数据。在保存的数据数据列定义的长度时，会发出警告（默认的SQLMode下），但是数据还是会按四射五入后的结果保存；但是如果SQLMode是在TRANDITIONAL下，则会直接报错，数据也不会保存。

浮点数示例

• 创建表

create table float_demo(f float(4,2));
--Query OK, 0 rows affected (0.25 sec)

• 插入超出字段定义长度的数据

insert into float_demo values(4.54647);
--Query OK, 1 row affected (0.09 sec)

• 查看数据

select * from float_demo;
--+------+
--| f    |
--+------+
--| 4.55 |
--+------+
--1 row in set (0.00 sec)

可以看到实际保存的数据是四舍五入后的数据，并且没有发出警告。

定点数示例

• 创建表

create table decimal_demo(d decimal(4,2));
--Query OK, 0 rows affected (0.21 sec)

• 插入数据

insert into decimal_demo values(2.254872);
--Query OK, 1 row affected, 1 warning (0.07 sec)

• 查看数据

select * from  decimal_demo;
--+------+
--| d    |
--+------+
--| 2.25 |
--+------+
--1 row in set (0.00 sec)

可以看到数据可以正常保存，但是在数据插入的时候发出了一个警告。

选用问题

浮点数的保存和计算都会存在产生误差的问题，所以在涉及到货币等对精确性要求比较高的应用是应该选用定点数类型，在浮点数和定点数的选择则问题上应该遵循以下几个原则

• 浮点数存在误差问题
• 对货币等精度敏感的信息，应该用定点数表示或存储
• 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较
• 要注意浮点数中一些特殊值的处理

4.日期类型选择

MySQL提供的常用数据类型有DATE、TIME、DATETIME、TIMESTAMP等.以下是日期类型的选择原则:

• 根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年份”，那么用 1 个字节来存储的 YEAR 类型完全可以满足，而不需要用 4 个字节来存储的 DATE 类型。这样不仅仅能节约存储，更能够提高表的操作效率。
• 如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用 DATETIME，而不要使用 TIMESTAMP。因为 TIMESTAMP 表示的日期范围比 DATETIME 要短得多。
• 如果记录的日期需要让不同时区的用户使用，那么最好使用 TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。