mysql set schema_Mysql数据库优化学习之一 Schema优化

引用

mysql> CREATE TABLE enum_test(

->

e ENUM('fish', 'apple', 'dog') NOT NULL

-> );

mysql> INSERT INTO enum_test(e) VALUES('fish'), ('dog'), ('apple');

mysql> SELECT e + 0 FROM enum_test;

+-------+

| e + 0 |

+-------+

|

1 |

|

3 |

|

2 |

+-------+

ENUM字段排序是按照内部存储的整数排的，你可以显示的使用FIELD来指定排序的方式。

引用

mysql> SELECT e FROM enum_test ORDER BY FIELD(e, 'apple', 'dog', 'fish');

+-------+

| e

|

+-------+

| apple |

| dog

|

| fish |

+-------+

ENUM最大的缺点是字符串列表是固定大小的，添加和删除一个字符串需要使用ALTER TABLE，所以如果你表示的字符串有可能在将来改变，那么使用ENUM并不是一个好的主意。

如果ENUM需要和char,varchar进行join操作，那么会慢一些，这时候最好同一类型，

但是使用ENUM能够比使用char,varchar节省不少空间的。

日期和时间类型：

MySQL有各种日期和时间的类型，比如YEAR,DATE，MySQL能存储的最小时间粒度是秒，但是可以在微妙级别做临时的运算。

DATETIME：

这个类型能够保存更大范围的值，从1001到9999，精度为1秒，他将日期和时间存储为整数格式为YYYYMMDDHHMMSS，和时区无关，使用了8个字节。

TIMESTAMP：

TIMESTAMP按照字面意思，存储了从1970.1.1流失了多少秒，和Unix的timestamp一样。

TIMESTAMP仅使用了4个自己，所以要比DATETIME小很多：从1970到2038. MySQL提供了

FROM_UNIXTIME()和UNIX_TIMESTAMP()函数来将Unix时间戳和日期转换。

TIMESTAMP是和时区相关的。

时间戳有一些DATETIME没有的功能，他没在插入数据的时候，如果该列为空，则自动将当前时间插入。当更新一行数据时，这行数据的TIMESTAMP列默认会跟新为当前时间，除非显式的指定值，TIMESTAMP列默认是NOT NULL的。

一般来说你应该使用TIMESTAMP，因为他比DATETIME占用更少的空间。有一些人喜欢存储

Unix时间戳为一个整数，但常常没有给你带来任何好处，因为这种格式不方便处理，我们并不推荐这么用。

Bit压缩类型(Bit-packed Data Types)

MySQL提供了一些存储类型，他每一个bit存储一个值来的更紧凑的存储数据方式。所有的

这些类型都是字符串类型，不管底层的存储格式和操作：

BIT 在MySQL5.0之前，BIT是TINYINT的同义词。但是在5.0之后，它是一个完全不同的类型，具有特殊的特性：你可以使用BIT列来存储true/false值。BIT(1)定义了包含了单个比特，BIT(2)存储了两个bit，BIT字段的最大值是64bit。

BIT类型的行为在不同的存储引擎中不同，MyISAM将这些列一起压缩存储，所以17个BIT列只需要17bit的存储空间(没有列是NULL的)。其他的存储引擎比如Memory和InnoDB，使用足够大的integer类型来存储，所以使用BIT并没有节省多少空间。

MySQL将BIT类型视为字符串类型而不是数字类型。如果使用BIT(1)，查询出来的是一个字符串，内容是二进制的0或者1，但不是ASCCII码的‘0’，’1‘的值。

但是在数字的场景下使用，其值是字符串二进制表示的值，比如b'00111001'为57.

这可能会比较让人费解，所以我们建议谨慎的使用BIT，在大多数的应用中，避免使用。

如果想存储true/false中使用单个bit的存储空间，那么另外的选择是使用varchar(0),

这个列可以存储NULL或者空字符串。

SET

如果你想存储很多的true/false值，那么考虑将很多列组合成1列，使用SET数据类型，内部表示为压缩的bit集合。MySQL有函数FIND_IN_SET()和FIELD()，让在查询中使用

更容易。主要的缺点是，修改需要ALTER TABLE.

在整数列使用bit操作

另一种对于SET的选择是使用整数作为压缩的比特集合，比如你可以在TINYINT类型使用

8个bit，然后通过bit操作。这种方式相对于SET的最大有点是修改这个字段不需要ALTER TABLE。缺点是比较拿写和理解，位操作，有人喜欢有人不喜欢，所以完全看你的口味了。

选择主键：

选择一个主键的正确类型是非常重要的。你经常使用这些列来和其他的值比较，或者使用它来查找，在其他的表中作为外键。选择主键不仅仅药考虑存储的类型，还要考虑比较和计算的性能。比如MySQL存储ENUM和SET类型的内部形式是整数，但是在字符串的环境下会转化为字符串。

一旦选择好一个类型，要确保所有关联的表对应的外键具有相同的类型。类型要精确的匹配比较好，除了UNSIGNED。混用不同的类型可能导致性能问题，并且隐式的类型转换可能会带来错误。

选择能够包含数据区间的最小的类型，比如你想用state_id字段来存储美国的州，那么TINYINT已经足够。

整数类型：

通常是最佳的主键类型的选择，因为它速度快并且可以自增。

ENUM和SET：

通常是一个比较糟糕的选择，他们比较适合作为状态，类型的值。

String类型：

尽可能避免使用字符串类型的主键，因为占用较多的空间并且比整数慢。使用MyISAM的表更应该特别谨慎使用字符串类型的主键，因为他会默认压缩字符串索引，这会导致查询非常慢。

使用packed索引大约要慢六倍。

使用完全随机的字符串，比如MD5(),SHA1(),UUID()，要特别小心，每一个新生成的值都会

在一个很大空间的一个随机的值，这将会降低插入和部分查询语句。

INSERT慢的原因是插入的值在索引的随机位置，这会导致页的分裂或者磁盘的随机访问，对于聚集存储引擎会引起聚集索引碎片。

SELECT慢的原因逻辑相邻的行将会在磁盘和内存分散。

随机的值也会导致所有类型查询的缓存表现很差。

特殊类型数据：

比如很多人使用VARCHAR(15)来表示IP，其实IP是一个无符号的32为整数，所以存储IP地址为整数是一个比较好的方法。MySQL提供了INET_ATON()和INET_NTOA函数来转换这两种表示。

参考《高性能MySQL》

分享到：

2011-05-12 23:32

浏览 3789

分类:数据库

评论