数据库学习（mysql）----一些理论

关于数据库优化的一些理论  以下都是关于MYSQL中的。

一些简单的设计理论

一。字段越小越好。当然要确保满足需求。

二。字段越简单越好。有个例子。

MYSQL中：

1.应该用内建的日期和时间类型 不应该用 字符串类型。

2.应该用整形来保存ip地址。

ps：我在公司中 很多系统的id字段都是 varchar的，不过是oracle。 看书说（MYSQL，不知道其他数据库是不是也是）字符串的消耗要比整数类型大的多。所以id就应该是整形的！？

三。尽量避免null。

MYSQL难以优化null类型。空列会是 索引、索引统计和值更加复杂。需要更多空间。还会导致MyISAM中固定大小的索引变成可变大小。可以考虑用0或者特殊字符串来代替它。

一些常用的类型：

数值型：整数和实数

整数 tinyint samllint mediumint int bigint 分别用 8,16,24,32,64存储空间。 可以设置 unsigned 来设置无符号。 注意 整数的运算通常都是使用64位的bigint来运算

实数 filoat double decimat 

字符串类型

 varchar 和char

varchar 是可变长的字符串。 存储空间只比数据需要空间大1（或更多 取决于字符串长度）个字节用来存储字符串长度。不过当修改的时候变的很麻烦。并且消耗 大量资源。 当最大长度 远远大于平均长度 并且不经常修改的字段 适合 varchar。

char 不可变长的字符串。长度不变 注意 存储char的时候 末尾的空格会被去掉。 当 存储的字段 大小基本差不多。并且会经常修改的时候 会使用这个。比较典型的 例子是 密码的md5码。

binary和varbinary  

这两个类型是char和varchar的兄弟类型。 特性非常像。 只不过存储的是字节不是字符。填充是用 \0 而不是 char的空格来。 而且获取的时候 \0是不回去掉到；换 句话说 使用 binary 来操作。 存入的和获取的 是可能不一致的。 多出\0填充符

blob和text类型

这两个类型很特殊。MYSQL 对它的处理也很复杂。 建议不要轻易使用。 ps：本人亲身经历一个项目 需要将一些东西保存下来 很大。好像是一个复杂的个人简介一样的东西。要保存样式。就用blob 然后数据多了的时候。 关联查询的比较多东西。 加上这个字段就完蛋了。当时不知道怎么回事。反正就不查这个字段就OK了。排序的时候并不是说有的字符串都参与。只用max_head_length 或者max_table_length 来参与排序。或者使用 order by substring（）；

注意： MYSQL 不能索引这些数据的完整长度。也不能为排序使用索引。Memery引擎不支持他们 使用这些类型并且需要隐式临时表的查询会不得不使用磁盘上的MyISAM表。即使只有几列 也这样。会造成非常昂贵的开销。 不得不适用这些类型的时候。 要尽量让max_head_length 或者max_table_length 足够的小。

emun类型

在一些场合可以用emun类型代替字符串。emun类型可以保存65535个不同的字符串。其实对他的操作 就像对整数操作一样。 效率要高的多。 注意：对于emun类型排序不是按字符串排序 是按照数字排序（可以 select  emunColumn+0  from  table 看看 就知道数字的意思了）

缺点是值是固定的。

时间类型

datetime和timestamp

datetime：从1001年到9999年 精度为秒。日期封装成一个YYYYMMDDHHMMSS的整数中，使用的8字节存储空间

timestamp：从1970年到2038年 占8字节存储空间 显示 依赖时区。 而且默认不为空。

要精确到毫秒 需要另外处理 添加一个字段。bingint保存时间戳 或者 用int 保存毫秒部分

bit类型

最长64位。

等等。

标示符（identity column）

选择正确的类型很重要。首先整数类型就要好于字符串类型。

enum和set   这种类型 一般不用于 identity 。这种类型适合保存订单状态。产品类别等。

字符串类型   尽量避免使用字符串类型的主键。 这会占用更对的资源。速度要慢很多。特别是 MyISAM 表上使用 字符串主键。会使用压缩索引。会使性能降低6倍

注意：特别是不规律的字符串。比如md5（），sha1（），uuid（）等方法产生的字符串。无论查询还是插入。都会占用更多的资源。速度慢很多。

关于ip字段的误区。 很多人存 ip使用 varchar（15） 可实际上 ip是 无符号的32位数字。 mysql 本身就提供转换的方法 inet_aton() , inet_nton() 。将ip存成整数 会大大提高性能。