一,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.*
12K t10.frm
15M t10.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.*
12K t10.frm
15M t10.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.*
12K t10.frm
12M t10.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: 0
mysql> select * from t where context like 'abc2%' \g;
ERROR 1146 (42S02): Table 'test1.t' doesn't exist
ERROR:
No query specified
mysql> 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。
四,