选择合适的数据类型

一，char和varchar

    不同点比较：

 1，  char和varchar 类型类似，都用来存储字符串，但他们保存和检索的方式不同。char属于固定长度的字符类型，而varchar属于可变长度的字符类型。

 2，   char和varchar的检索值值也并不相同，因为检索时从char列删除了尾部空格，而varchar会保留。

 3，  由于char 是固定长度，所以他的处理速度比varchar要块的多，但其缺点是浪费存储空间，程序需要对尾行空格进行处理。

   简单概况，怎么选择：

1，myisam存储引擎：建议使用固定长度的数据列代替可变长度的数据列

2，memory存储引擎：目前都是使用固定长度的数据行存储，无论使用char还是varchar都没有关系

3，innodb存储引擎：建议使用varchar类型。对于innodb数据表，内部的行存储格式没有区分固定长度和可变长度列（所以数据行都是使用指向数据列的头指针），因此在本质上，使用固定长度的char不一定比使用可变长度的varchar列性能要好。

二，text和blob

     一般我们保存少量字符串的时候会使用char或者varchar,而在保存大量文本时，通常选择text或者blob。两者最主要的区别是：blob能用来保存二进制数据，比如照片，而text只能保存字符数据。

    1，blob和text值会引起一些性能问题，特别是在执行大量的删除操作时。

   删除操作会在数据表中留下很大的空洞，以后要填入这些空洞的记录在插入的性能上会有影响。为了提高性能，建议定期使用optimize table功能对这类表进行碎片整理，避免因为空洞导致的性能问题。

   例子：

       创建表t10,id bigint(20),content text，并用插入大量数据：

mysql> create table t10(id bigint(20),content text);Query OK, 0 rows affected (0.29 sec)mysql> insert into t10(id,content)values(1,repeat('haha',100));Query OK, 1 row affected (0.04 sec)mysql> insert into t10(id,content)values(2,repeat('haha2',100));Query OK, 1 row affected (0.04 sec)mysql> insert into t10(id,content)values(3,repeat('haha3',100));Query OK, 1 row affected (0.04 sec)mysql> insert into t10 select * from t10;Query OK, 3 rows affected (0.05 sec)Records: 3  Duplicates: 0  Warnings: 0...mysql> insert into t10 select * from t10;Query OK, 6144 rows affected (0.42 sec)Records: 6144  Duplicates: 0  Warnings: 0 

退到操作系统，查看表t10的物理文件

root@fuzhongyu-Lenovo-G400:/var/lib/mysql/test1# du -sh t10.*12Kt10.frm15Mt10.ibd

接下来我们删除一部分数据

mysql> delete from t10 where id=1;Query OK, 4096 rows affected (0.53 sec)

再次查看t10的物理内存

root@fuzhongyu-Lenovo-G400:/var/lib/mysql/test1# du -sh t10.*12Kt10.frm15Mt10.ibd

文件并没有因为数据删除而减少，接下来对表进行optimize(优化)操作：

mysql> optimize table t10;+-----------+----------+----------+-------------------------------------------------------------------+| Table     | Op       | Msg_type | Msg_text                                                          |+-----------+----------+----------+-------------------------------------------------------------------+| test1.t10 | optimize | note     | Table does not support optimize, doing recreate + analyze instead || test1.t10 | optimize | status   | OK                                                                |+-----------+----------+----------+-------------------------------------------------------------------+2 rows in set (5.77 sec)

再查看t10的内存文件大小

root@fuzhongyu-Lenovo-G400:/var/lib/mysql/test1# du -sh t10.*12Kt10.frm12Mt10.ibd

发现表的数据文件减少，空洞空间被回收。

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

     合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据了。但是，这种技术只能用于精确匹配的查询。可以使用md5()函数生成散列值，数值型散列值可以很高效的存储，如果散列算法生成的字符串带有尾部空格，就不要用char列，他会受到尾部去空格的影响。

     例子：

     创建表t11，字段：id varchar(100),context blob,hash_value varchar(40)（hash_value用来存储context列的md5值）

mysql> create table t11(id varchar(100),context blob,hash_value varchar(40));Query OK, 0 rows affected (0.31 sec)mysql> insert into t11(id,context,hash_value)values(1,repeat('abc',2),md5(context));Query OK, 1 row affected (0.05 sec)mysql> insert into t11(id,context,hash_value)values(2,repeat('abc2',2),md5(context));Query OK, 1 row affected (0.09 sec)mysql> insert into t11(id,context,hash_value)values(3,repeat('abc3',2),md5(context));Query OK, 1 row affected (0.04 sec)mysql> select * from t11;+------+----------+----------------------------------+| id   | context  | hash_value                       |+------+----------+----------------------------------+| 1    | abcabc   | 440ac85892ca43ad26d44c7ad9d47d3e || 2    | abc2abc2 | 23f52bc2cf5617a1f323fc10413e399f || 3    | abc3abc3 | 7029bb59e4b3a0bd01e23edd47b1e93e |+------+----------+----------------------------------+3 rows in set (0.00 sec)mysql> select * from t11 where hash_value=md5(repeat('abc',2));+------+---------+----------------------------------+| id   | context | hash_value                       |+------+---------+----------------------------------+| 1    | abcabc  | 440ac85892ca43ad26d44c7ad9d47d3e |+------+---------+----------------------------------+1 row in set (0.01 sec)

      上面的例子只能用于精确匹配，如果需要对blob或clo字段进行模糊查询，mysql提供了前缀索引，也就是只为字段的前n列创建索引。

例子：对context 前100个字符进行模糊查询，就可以用到前缀索引。

mysql> create index idx_blob on t11(context(100));Query OK, 0 rows affected (0.46 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from t where context like 'abc2%' \g;ERROR 1146 (42S02): Table 'test1.t' doesn't existERROR: No query specifiedmysql> select * from t11 where context like 'abc2%' \g;+------+----------+----------------------------------+| id   | context  | hash_value                       |+------+----------+----------------------------------+| 2    | abc2abc2 | 23f52bc2cf5617a1f323fc10413e399f |+------+----------+----------------------------------+1 row in set (0.01 sec)

    注：这里查询条件中的%不能放在最前面，否则索引将不会被使用。

3，在不必要的时候避免检索大型的blob或text值

    如：select * 查询就不是很好的想法，除非能够确定作为约束条件where字句只有一句，否则，很可能毫无目的的在网络上传输大量的值。解决方法：可以搜索索引列，决定需要哪些数据行，然后从符合条件的数据行中检索blob或text的值。

4，把blob或text列分离到单独的表中

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

三，浮点数和定点数

    

mysql> create table t12(c1 float(10,2),c2 decimal(10,2));Query OK, 0 rows affected (0.30 sec)mysql> insert into t12(c1,c2)values(131072.32,121072.32);Query OK, 1 row affected (0.04 sec)mysql> select c1,c2 from t12;+-----------+-----------+| c1        | c2        |+-----------+-----------+| 131072.31 | 121072.32 |+-----------+-----------+1 row in set (0.00 sec)

  所以，从上面的例子中我们可以看到c1列由131072.32变成了131071.31，这是使用单精度浮点数表示时产生了误差。

  浮点数和定点数使用的几个原则：

 1，浮点数存在误差

2，对货币等对精度敏感的数据，应用定点数表示或存储

3，在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较

4，要注意浮点数中一些特殊值的处理

四，日期的选择

 
     原则：

  1，根据实际需要选择能够满足应用的最小存储的日期类型，如果应用只需要记录“年”，那么用1个字节来存储的year类型完全可以满足，而不需要用4字节来存储的date，这样不仅能满足需求，也能提高运行效率。

  2，如果要记录年月日十分秒，并记录的年份比较久远，那么最好使用datetime，而不要使用timestamp（日期范围短）。

 3，需要考虑时区时使用timestamp。

四，