一、MySQL数据类型选择原则
更小的通常更好:一般情况下选择可以正确存储数据的最小数据类型。越小的数据类型通常更快,占用磁盘,内存和CPU缓存更小。
简单就好:简单的数据类型的操作通常需要更少的CPU周期。例如:整型比字符操作代价要小得多,因为字符集和校对规则(排序规则)使字符比整型比较更加复杂。
尽量避免NULL:尽量制定列为NOT NULL,除非真的需要NULL类型的值。因为可能为NULL列使得索引,索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理。
二、选择具体数据类型
在选择列的数据类型时需要先选定合适的大类型,如:数字,字符串,时间等。
(1)、整数类型
数据类型
长度/字节
范围
TINYINT
1
-2^7—2^7-1
SMALLINT
2
-2^15—2^15-1
MEDIUMINT
3
-2^23—2^23-1
INT
4
-2^31—2^31-1
BIGINT
8
-2^64—2^64-1
区分开TINYINT / INT / BIGINT,能确定不会使用负数的字段,建议添加 unsigned定义。
能够用数字类型的字段尽量选择数字类型而不用字符串类型。
(2)、实数类型
实数是带有小数部分的数字。然而它们不只是存储小数部分;也可以是DECIMAL存储比BIGINT还大的整数。MYSQL即支持精确类型,也支持非精确存储。FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。具体计算规则取决于所使用平台的实现。
数据类型
长度/字节
范围
FLOAT
4
—
DOUBLE
8
—
DECIMAL
最多65数字(5.0以上版本)
—
MySQL使用DOUBLE作为内部浮点计算的类型。注意,DECIMAL需要额外的空间和计算开销,所以只要要求尽量对小数进行精确时才使用DECIMAL,例如在存储财务数据时,可以用BIGINT来替代它来实现,只需要将数据乘以相应的倍数即可。
FLOAT和DOUBLE选择(尽量选择FLOAT)
如何有涉及到运算,用float和double可能会精度不准,可以考虑将小数乘于相应的倍数,然后用BIGINT来存储。
(3)、字符串类型
MySQL支持多种字符串类型。从4.1版本开始,每个字符串列可以定义自己的字符集和校对规则(排序规则),这些东西会在很大程度上影响性能。
1、VARCHAR和CHAR类型
VARCHAR和CHAR时最主要的两种字符串类型。
VARCHAR:适用于存储变长的字符串,是最常见的字符串数据类型。它比定长更加节省空间,因为它仅需要使用必要的额外空间(如果MySQL表使用ROW_FORMAT=FIXED创建的话,会使用定长)。
适合使用该类型的情况:字符串列的最大长度比平均长度大很多;列的更新很少,所以碎片不是问题;使用了UTF-8这样复杂的字符集,每个字符的使用不同字节数进行存储。
注:5.0以上版本会保留末尾空格,更早的版本会删除末尾空格。InnoDB存储引擎会把过长的VARCHAR转换为BLOB类型。
CHAR:CHAR是定长字符串类型,MySQL总是根据定义的类型进行分配足够的空间。当存储CHAR时MySQL会默认删除行尾空格。因为CHAR值通常需要用空格进行填充以方便比较。
CHAR适合存储很短的字符串,或者所有长度都接近一个长度。
对于经常变更的数据CHAR比较VARCHAR要好,因为定长的CHAR类型不容易产生碎片。
对于更短的列CHAR比VARCHAR要更加节省空间,因为VARCHAR总是需要一个额外的字节来存储长度。
2、BLOB和TEXT类型
BLOB和TEXT是用来存储很大的数据来设计的字符串数据类型,分别采用二进制和字符方式进行存储。
实际上他们时属于两种不同数据类型家族:字符类型的时TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT;对应的二进制类型是TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB。
BLOB和TEXT的仅有的区别在于BLOB类型存储的时二进制数据,没有排序规则或字符集,而TEXT类型有字符集和排序规则。
char,varchar,TEXT的选择:非万不得已不要使用 TEXT 数据类型,定长字段,建议使用 CHAR 类型(填空格),不定长字段尽量使用 VARCHAR(自动适应长度,超过阶段),且仅仅设定适当的最大长度。
(4)、日期和时间类型
MySQL可以使用很多类型来保存日期和时间值,例如YEAR和DATE.MySQL能存储最小粒度为秒。但是可以使用微秒级的力度进行运算。
1、DATETIME
能保存1001到9999年,精度为秒。格式为YYYY-MM-DD HH:MM:SS与时区无关,使用八个字节的存储空间。
2、TIMESTAMP
时间戳,正如名字一样。它能保存从1970年1月1号午夜(格林尼治标准时间)。它只使用四个字节的存储空间只能表示1970到2038年。
TIMESTAMP显示的值依赖于时区。MYSQL服务器,操作系统,以及客户端连接都有时区设置。因此存储值为0时在不同的时区显示值会有差别。
注:通常情况下应尽量使用TIMESTAMP,因为它比DATETIME效率更高。如果需要存储更小粒度的时间,可以用BIGINGT或者转换成DOUBLE类型来进行存储。
(5)、ENUM数据类型
枚举数据类型在内存中非常紧凑,会根据列表值的数量压缩到一个或两个字节。MySQL自身会维护一个查找表。例如:
CREATE TABLE enum_test(
`gender` ENUM("男","女","其他") NOT NULL
);
INSERT INTO enum_test values("男"),("女"),("其他");
对于状态字段,可以尝试使用 ENUM 来存放。
避免使用NULL字段,很难查询优化且占用额外索引空间。
****************************************************华丽的分割线**************************************************************
一。char和varchar
char是固定长度的,查询速度比varchar速度快的多。char的缺点是浪费存储空间。
检索char列时,返回的结果会删除尾部空格,所以程序需要对为空格进行处理。
对于长度变化不大且对查询速度有较高要求的数据可以考虑使用char。
随着MySQL的不断升级,varchar的性能不断改进并提高。
存储引擎使用原则:
MyISAM:建议使用固定长度列代替可变长度列。
InnoDB:建议使用varchar类型
二。text和blob
在保存大文本时,通常选择text或者blob。
二者的差别是blob可以保存二进制数据,比如照片。
text和blob又包括text、mediumtext、longtext和blob、mediumblob、longblob,他们之间的区别是存储文本长度不同和存储字节不同。
应根据情况选择满足需求的最小存储类型。
1.blob和text执行大量删除操作时,产生数据“空洞”
删除id为7记录前后数据库所占内存,没有发生变化。
可以发现数据文件并没有因为数据删除而减少。
对表进行optimize(优化)操作:
optimize table t1;
可以发现表数据文件大大缩小,“空洞”空间已经被回收。
2.用合成(Synthetic)索引提高查询性能
根据大文本字段(text、blob)的内容建立一个散列值,并把这个值存储在单独的数据列中,然后通过散列值找数据行。
缺点:只能进行精确查询(=范围操作符是没有用处的)
可以通过MySQL自带函数md5()、sha1()、crc32()生成散列值,也可以通过编程语言计算散列值。
注:如果散列算法生成的字符串带有尾部空格,不要把他们存储在char、varchar列中,他们会受到尾部空格去除的影响。
如果需要对blob或clob字段进行模糊查询,可以用前缀索引:
1
2
3
4
#前缀索引:对context字段的前100个字符创建索引
create index idx_blobon t2(context(100));
#查询方法
select *from t2where contextlike 'drake%';注:%不能放在最前面
合成索引只能用于精确匹配,在一定程度上减少了I/O,从而提高了查询效率。
3.在不必要的时候避免检索大型的blob或text值。
4.把blob或text列分离到单独的表中。
三、浮点数和定点数
1.浮点数存在误差问题。
2.对货币等对精度敏感的数据,应该用定点数表示或存储。
3.在编程中,如果用到浮点数,要特别注意误差问题,并尽量避免做浮点数比较。
4.要注意一些特殊值的处理。
四、日期类型的选择
1.根据实际需要选择能够满足应用的最小存储日期类型。
2.如果记录年月日时分秒,并且记录年份比较久远,最好使用datetime,不要使用timestamp。
3.如果记录的日期需要让不同时区的用户使用,最好使用timestamp,因为日期类型中只有它能够和实际时区相对应。
谈谈如何选择合适的MySQL数据类型
MySQL数据类型选择 一 .选择原则 更小的通常更好:一般情况下选择可以正确存储数据的最小数据类型.越小的数据类型通常更快,占用磁盘,内存和CPU缓存更小. 简单就好:简单的数据类型的操作通常需要更 ...
(1.1)mysql 选择合适的数据类型
(1.1)mysql 选择合适的数据类型 1.char与varchar [1.1]char 在内容未满定义长度时,做空格填充,且字符串末尾空格会被截断:超出定义长度也会被截断. 如:char(4) ...
MySQL学习笔记(二):MySQL数据类型汇总及选择参考
本文主要介绍了MySQL 的常用数据类型,以及实际应用时如何选择合适的类型. ******几个通用的简单原则:******* 1. 更小的通常更好.但是要确保没有低估需要存储的值的范围,如果无法确定 ...
MySQL数据类型的选择
+++++++++++++++++++++++++++++++++++++++++++标题:MySQL数据类型的选择时间:2019年2月22日内容:MySQL数据类型的选择范式参考重点:主要讲述MyS ...
8.Mysql数据类型选择
8.选择合适的数据类型8.1 CHAR与VARCHAR CHAR固定长度的字符类型,char(n) 当输入长度不足n时将用空格补齐,char(n)占用n个字节,CHAR类型输出时会截断尾部的空格,即使 ...
为MySQL选择合适的备份方式
数据库的备份是极其重要的事情.如果没有备份,遇到下列情况就会抓狂: UPDATE or DELETE whitout where… table was DROPPed accidentally… IN ...
MySQL如何选择合适的引擎以及引擎的转换。
我们怎么选择合适的引擎?这里简单归纳一句话:"除非需要用到某些InnoDB不具备的特性,并且没有其他办法可以替代,否则都应该优先选择InnoDB引擎." 除非万不得已,否则不建议混 ...
为MySQL选择合适的备份方式[转]
原文链接:http://nettedfish.sinaapp.com/blog/2013/05/31/choose-suitable-backup-strategy-for-mysql/ 数据库的备份 ...
MySQL 数据类型的简单选择
选择合适的数据类型:char和varchar: +---------+------------+ | char(6) | varchar(6) | +---------+------------+ | ...
随机推荐
Learning in Two-Player Matrix Games
3.2 Nash Equilibria in Two-Player Matrix Games For a two-player matrix game, we can set up a matrix ...
【原】创建Hive表,分号分隔符“;”引起的异常
[障碍再现] 在创建支持Map数据结构的Hive表时,抛出如下异常 hive> create table tab_map(name string,info map
CH Round #58 - OrzCC杯noip模拟赛day2
A:颜色问题 题目:http://ch.ezoj.tk/contest/CH%20Round%20%2358%20-%20OrzCC杯noip模拟赛day2/颜色问题 题解:算一下每个仆人到它的目的地 ...
mac svn .a文件的上传方法
1.首先确认是否安装了Command Line Tools,如果没有,就Xcode-Preference-Downloads,选择Command Line Tools-install就可以了 2.打开 ...
如何从Linux系统中获取带宽、流量网络数据
引入 国外的云主机厂商,例如AWS提供的网络数据是以流量为单位的,例如下面的图片: 从上图来看,其取值方式为 每隔5分钟取值1次,(每次)每个点显示为1分钟内的流量字节数(Bytes) 带宽与流量 我 ...
redis相关问题
什么是Redis?Redis 是一个使用 C 语言写成的,开源的 key-value 数据库..和Memcached类似,它支持存储的value类型相对更多,包括string(字符串).list(链表 ...
java.lang.StringIndexOutOfBoundsException: String index out of range: 0
hibernet 报错 java.lang.StringIndexOutOfBoundsException: String index out of range: 0 处理方法 数据表字段为char ...
【BZOJ】【3931】【CQOI2015】网络吞吐量
最短路+最大流 思维难度并不高,其实题面几乎已经把算法讲完了…… 练习模板的好题= = 哦对了,求最短路和最大流的时候都得开long long……QwQ /********************** ...
Git 个人笔记
最近在看 Git ,顺便把这些常用命令记录下来,以备以后忘记能查看(未完): // 设置用户名和邮箱 使用global 表示这台主机上所有的Git仓库都会使用这个配置 也可以对某个仓库指定不 ...
[ActionScript 3.0] 对数组中的元素进行排序Array.sort()的方法
对数组中的元素进行排序. 此方法按 Unicode 值排序. (ASCII 是 Unicode 的一个子集.) 默认情况下,Array.sort()按以下方式进行排序: 1. 排序区分大小写(Z优先于 ...