首页 > 数据库 > MySQL >

mysql选择合适的数据类型详解

2017-12-26

mysql选择合适的数据类型详解。8 1 CHAR 与 VARCHAR

八. 选择合适的数据类型

\

8.1 CHAR 与 VARCHAR

保存方式:

char为固定长度 不足的将用空格 补齐

检索方式:

char 检索时将删除尾部的空格

优缺点:

由于char是固定长度的,所以char的处理速度更快,缺点是占用了更多的存储空间,程序需要对尾部进行空格补齐,对于长度变化不大,并对查询速度有较高要求的的可以使用char

随着mysql的升级,varchar的性能也不短的提高。

不同的引擎 char和varchar的使用规则

MyISAM:建议使用固定长度的列取代可变长度的列

MEMORY:目前使用固定长度数据进行存储,所以无论使用char和varchar 两者都当做char来处理

InnoDB:建议使用varchar 类型,对于InnoDB数据表,内部的行存储格式没有区分固定长度和可变长度列(所有数据行都指向数据列值得头指针)。因此本质上固定长度的char性能不一定比varchar的性能要好。

8.2 TEXT 与 BLOB

二者的主要差别

BLOB能用来保存二进制数据,比如照片等。

而TEXT只能保存字符数据。

TEXT 和 BLOB 中有分别包括TEXT、MEDIUMTEXT、LONGTEXT 和 BLOB、MEDIUMBLOB、LONGBLOB3 种不同的类型,它们之间的主要区别是存储文本长度不同和存储字节不同,用户应该根据实际情况选择能够满足需求的最小存储类型

TEXT和BLOB值会引起一些性能问题,特别是在执行大量的删除操作时。

删除会在数据表中留下很大的空洞,以后再填入这些空洞记录插入性能会有很大的影响。

建议定期使用 OPTIMIZE TABLE 的碎片整理功能

>OPTIMIZE TABLE tablename;

可以使用合成的(synthetic)索引来提高大文本字段的查询性能

是什么

简单来说 合成索引就是根据大文本字段的内容建立一个散列值,并把这个值单独的存储在数据列中,接下来就可以使用散列值来检索数据,但是这用技术只能用于精确匹配查询

前缀索引可用于模糊查询

是什么

合成索引的用法,由于这种技术只能用于精确匹配,在一定程度上减少 I/O,从而提高查询效率。如果需要对 BLOB 或者 CLOB 字段进行模糊查询,MySQL 提供了前缀索引,也就是只为字段的前 n 列创建索引

范例

mysql> create table t (id varchar(100),context blob,hash_value varchar(40));

Query OK, 0 rows affected (0.03 sec)

mysql> create index idx_blob on t(context(100));

Query OK, 3 rows affected (0.04 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> desc select * from t where context like 'beijing%' \G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t

type: range

possible_keys: idx_blob

key: idx_blob

key_len: 103

ref: NULL

rows: 2

Extra: Using where

1 row in set (0.00 sec)

在不必要的时候 避免检索BLOB或者TEXT值

例如,SELECT * 查询就不是很好的想法,除非能够确定作为约束条件的 WHERE 子句只会找到所需要的数据行。否则,很可能毫无目的地在网络上传输大量的值。这也是 BLOB 或 TEXT标识符信息存储在合成的索引列中对用户有所帮助的例子。用户可以搜索索引列,决定需要的哪些数据行,然后从符合条件的数据行中检索 BLOB 或 TEXT 值。

把BLOB或TEXT值放到单独的表中

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

8.3 浮点数与定点数

浮点数和定点数的区别

浮点数:一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型后,如果插入数据的精度超过该列定义的实际精度,则插入值会被四舍五入到实际定义的精度值,然后插入,四舍五入的过程不会报错。在 MySQL 中 float、double(或 real)用来表示浮点数。

定点数:不同于浮点数,定点数实际上是以字符串形式存放的,所以定点数可以更加精确的保存数据。如果实际插入的数值精度大于实际定义的精度,则 MySQL 会进行警告(默认的 SQLMode 下),但是数据按照实际精度四舍五入后插入;如果 SQLMode 是在 TRADITIONAL(传统模式)下,则系统会直接报错,导致数据无法插入。在 MySQL 中,decimal (或 numberic)用来表示定点数。

定点数和浮点数的应用原则:

1浮点数存在误差问题

2对货币等对精度敏感的问题应该应用定点数表示

3在编程中 用到浮点数要特别注意误差问题,避免用浮点数比较

4注意浮点数的一些特殊操作

8.4 日期类型选择

1 根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年份”,那么用 1 个字节来存储的 YEAR 类型完全可以满足,而不需要用 4 个字节来存储的 DATE 类型。这样不仅仅能节约存储,更能够提高表的操作效率。

2 如果要记录年月日时分秒,并且记录的年份比较久远,那么最好使用 DATETIME,而不要使用 TIMESTAMP。因为 TIMESTAMP 表示的日期范围比 DATETIME 要短得多。

3如果记录的日期需要让不同时区的用户使用,那么最好使用 TIMESTAMP,因为日期类型中只有它能够和实际时区相对应。

相关文章
最新文章
热点推荐