澳门新浦京娱乐场网站-www.146.net-新浦京娱乐场官网
做最好的网站

澳门新浦京娱乐场网站:选择优化的数据类型,

先说一下mysql中DATETIME和TIMESTAMP的区别


  1. 选择最小的数据类型,因为它们占更少的磁盘,内存和CPU缓存;

  2. 选择简单的数据类型,如用整型来存储ip: 

  3. 避免null,因为null会占用存储空间,null在索引列上会使索引更复杂

     

整形

TIMESTAMP是标准的unix timestamp,它存储的是1970-1-1到现在经过的秒数,4字节存储。mysql用这个类型还蛮方便的,一个是有很多内置的函数和trigger来处理它,比如CURRENT_TIMESTAMP宏,最关键的是在取数据的时候mysql会自动帮你处理DST和时区的问题。

title: mysql-数据类型
date: 2016-12-28 17:42:45
tags:
categories: mysql

整数类型


DATETIME的范围更大,好像可以从0000-00-00 00:00:00到9999-12-31 23:59:59,8字节存储,当然mysql内部肯定也是用整数而不是字符串的(说了是8字节了),所以效率不是大问题。但DATETIME不带时区,比如我在程序里生成了一个2015-05-07 15:26:00的时间(实际上是 8时区的,但这个对象可能是timezone naive)的,存到mysql里,再从不同时区的地方拿出来,这个时间可能就混了。


  1. TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT 分别使用8,16,24,32,64 位存储空间;

  2. 选择unsigned属性,可以使正数的上限提高一倍;

  3. 使不使用unsigned属性,跟性能无关;

  4. int(1)和int(10)对存储和计算来说是相同的,只是规定一些mysql交互工具用来限制显示字符的个数;

  • INT                      4bytes      0-2^(4*8)
  • SMALLINT            2bytes      0-2^(2*8)
  • MEDIUMINT         3bytes      0-2^(3*8)
  • BIGINT                8bytes      0-2^(8*8)
  • TINYNIT              1bytes      0-2(1*8)

但TIMESTAMP也有两个很大的问题:

《高性能mysql》第4章读书笔记

实数类型

经典问题:

  1. 4字节长度限制,它只能到2038年
  2. 很多时候我们希望根据用户所在地的时区显示时间而不是光显示一个服务器时间

数字类型

mysql的数字大体分为两种整数类型和实数类型(既小数类型)

  1. 可以使用DECIMAL存储比BIGINT还大的整数;

  2. CPU不支持DECIMAL直接计算,MYSQL服务器自身实现了DECIMAL的高精度运算,相对而言,CPU直接支持浮点运算,所以浮点运算明显更快;

  3. float占用4个字节,double占用8个字节;

  4. DECIMAL需要额外的空间和计算开销,所以尽量少用它;

  5. DECIMAL可以使用BIGINT代替,把小数乘以数倍存储在BIGINT里面,可以避免浮点存储计算不准确和DECIMAL精度计算代价高的问题;

int(11) VS int(21)

所以比较好的做法是,数据库中使用DATETIME,然后存时间的时候一律用程序生成UTC时间(而不是local时区的时间)存进去,取出来的时候不管想显示服务器时间还是显示用户的时间都可以处理。

整数类型

TINYINT SMALLINT MEDIUMINT INT BIGINT
8位 16位 24位 32位 64位

mysql中按从小到大一个五种整数类型,对应在java应用中,我们一般常常使用int对应java的integer,bigint对应java的long,因为java的integer刚好是4个字节,long8个字节。

注意创建表的时候整数类型后面的括号,比如 int(3),并不是表示可以有最多3个字符的整数,既-999到999。它的真实作用是限制某些客户端工具显示数字的时候只显示……3个数!所以其实没啥用,带不带都可以

整数和小数类型都可以带上 unsigned,表示不不存负数,这样可以大一位。比如tinyint本来是-128~127,而unsigned tinyint表示 0~255

字符串类型

存储空间,还是存储范围是没有任何区别的。

顺便提一句,根据用户所在地时区显示时间有两种做法:

实数类型

float和double分别占用4个字节和8个字节。当超过位数后,按照四舍五入保存近似值。这在保存钱之类的高精度数字时是很危险的,所以就别用好了!

float和double同时也可以使用如float(5,2)这样的样式来强制规定,小数位 整数位一共只能5位,小数位只保留2位。当个位数超过时,mysql拒绝插入,当小数位超过时,4舍五入。但是这事实上及其让人迷惑。

而且当你用相关列做条件查询时,没有在括号中制定精度的float会导致查询失败,必须指定精度

比如

money字段 设置为float,

insert into table (num) values (0.12);

select * from table where num=0.12的话,查不出任何数据。

而如果指定了精度,比如money float(6,2) //超过6位就开始取近似值了

insert into table (num) values (0.12);

select * from table where num=0.12的话,得到数据。

相比之下decimal(m,n)更容易理解,就是最多m位,小数最多n位超过后四舍五入(据说有的3.0以下的版本因为是转换成double之类的类型,行为不相同)

decimal的优势在于不会莫名其妙的在整数位四舍五入,但是在任然要注意超过范围后,小数位的四舍五入是否是你想要的,或者直接用bigint存储你的数字好了,存取的时候乘除相应的小数位即可

  1. varchar需要一个或者两个额外的字节记录字符串长度,varchar列的长度小于或等于255个字节需要一个字节,大于255需要两个字节;

  2. varchar 用于存储可变长字符串,它比定长类型更节省空间;

  3. char适合存储很短的字符串,或者所有值都接近同一个长度;

  4. 对于经常变更的数据,char比varchar更好,因为定长的char类型不容易产生碎片;

  5. 对于存储非常短的列,char存储空间比varchar少,如存储“Y”或"N",采用单字节字符集,char(1)占用一个字节;varchar(1)占用两个字节;

  6. 当存储char值时,mysql会删除所有末尾空格,char值会根据需要采用空格进行填充比较;

  7. varchar(5)和varchar(200)存储"hello"的空间开销是一样的。但更长的列会消耗更多的内存,因为mysql通常会分配固定大小的内存块来保存内部值;

     

那么在mysql中两者实际存储会是怎样的一种情况呢?

  1. 当用户第一次访问网站的时候,用js获取时区发送到服务器上存到session里
  2. 用js处理时间的显示(我觉得这种比较方便一点,毕竟不用改服务端代码)

字符串类型

BLOB和TEXT类型

 

使用这种做法的唯一缺点是sqlite3没有internal的DATETIME类型,所以在ORM框架如sqlalchemy中,它会直接存字符串进去。(sqlite3的文档也说,你要么存成int要么real要么字符串)。尽管这可能带来一些不方便和性能的下降,但我认为还是符合“keep it simple and stupid”的原则。

char和varchar

前者是定长,后者是边长

通常在innodb引擎中我们都使用varchar。

但是偶尔在存的数据长度一定时,比如身份证。

或者值经常变化的字段使用char,省掉计算数据长度的时间,mysql会快点。

比较奇怪的是使用char的时候,字符串末尾如果是空格,会被截断,不被存下来。

另外varchar(2)代表的可以存2个字符,不论你是存的中文还是英文都是2个,跟oracle不同。

  1. BLOB和TEXT分别采用二进制和字符存储;

  2. MYSQL不能将BLOB和TEXT列全部长度字符串进行索引,也不能用这些索引消除排序;

create table t (a int(11) zerofill,b int(21) zerofill);
insert into t values (1,1);
mysql> select * from t;
 ------------- ----------------------- 
| a           | b                     |
 ------------- ----------------------- 
| 00000000001 | 000000000000000000001 |
 ------------- ----------------------- 
1 row in set (0.00 sec)

至于用INT存时间,是另一种可行的方法,参见http://www.liaoxuefeng.com/article/0014132675721847f569c3514034f099477...
我个人不是很喜欢这么做,因为这样你必须把模型中表示时间的成员声明为int类型。这样是比较不符合逻辑的(那些Date呀Datetime之类的类就没有用了呀,最多就有个Dateutil就好了),而且会使得程序不易读(卧槽这个publishedDate为什么是int,它到底表示的是时间吗?)。总之见仁见智。

blob和text

额,考虑用nosql来存吧。

日期和时间类型

如果不加zerofill:

时间类型

  1. MYSQL可以存储的最小时间是秒;

  2. DATETIME能保存大范围的值,从1001年到9999年,精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关,使用8个字节存储;

  3. 默认情况下,MYSQL以一种有顺序,无歧义的格式显示DATETIME值,例如"2014-12-12 12:12:12";

  4. TIMESTAMP 用4个字节存储,和UNIX时间戳相同,它只能表示1970年到2038年,它的显示格式例如"2014-12-12 12:12:12";;

  5. 澳门新浦京娱乐场网站:选择优化的数据类型,数据类型。MYSQL提供了FROM_UNIXTIME()函数把UNIX时间戳转换成日期,并提供UNIX_TIMESTAMP()函数把日期转为UNIX时间戳;

  6. TIMESTAMP 依赖于时区;

  7. 当插入或修改记录时,如果TIMESTAMP没有指定值,TIMESTAMP默认会保存当前时间;

  8. TIMESTAMP比DATETIME的空间效率更高,尽量选择TIMESTAMP;

  9. 可以使用BIGINT存储微妙级别的时间戳,或者使用DOUBLE保留秒之后的小数部分;

mysql> create table t (a int(11),b int(21));
mysql> insert into t values(1,1);
mysql> SELECT * FROM t;
 ------ ------ 
| a    | b    |
 ------ ------ 
|    1 |    1 |
 ------ ------ 
1 row in set (0.00 sec)

DATETIME和TIMESTAMP

2者在显示的精度上并无区别,都是精确到秒

不同的是datetime底层存的其实是格式为YYYYMMDDHHMMSS,使用8个字节存储的整数中,所以其实这个类型是没有时区的概念的,你存的多少就是多少。

但是timestamp是将你存的时间按照时区存为了unixtime,只占用4个字节。

所以其显示也依赖于mysql server上的时区,所以它只能存到2038年为上限,存储一些未来时间的时候要注意(不注意也没关系,反正你存不进去)。

特殊类型数据

则两者没有任何区别;所以int(11),int(21)本质上没有任何区别,仅仅是在某些场景的显示上有一些区别;

date和time

当你只想保存年月日或者时分秒时使用

1.IPv4地址经常被人用varchar(15)来存储,但是它32位无符号的整数,小数点将地址分四段只是为了让人容易阅读,所以整型来存储它,MYSQL用INET_ATON() 和INET_NTOA()用于这两种表示转换;

 

 

浮点型


  • FLOAT(M,D)      4byte   单精度         非精确
  • DOUBLE(M,D)   8byte    双精度        非精确(比float精度高)

M,代表小数点左右两边总的位数

D,小数点右边我们成为标度的位数

 

 

精度丢失问题:

  • 精度丢失:如果公司工资用了float类型,可能会出现工资四舍五入问题

例子:

mysql> CREATE TABLE t ( a int(11) DEFAULT NULL, b float(7,4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> INSERT INTO t VALUES (2,123.12345);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t ;
 ------ ---------- 
| a    | b        |
 ------ ---------- 
|    2 | 123.1235 |
 ------ ---------- 
1 row in set (0.00 sec)

此时float便出现了四舍五入的情况;

 

DECIMAL定点数:更精确的数字类型

  • 高精度数据类型,常用来存储交易相关的数据
  • DECIMAL(M,N),M代表总精度,N代表小数点右侧的位数(标度)
  • 1<M<254,0<N<60
  • 存储空间变长

例子

mysql> show create table aa;
 aa    | CREATE TABLE `aa` (
  `a` decimal(18,9) DEFAULT NULL,
  `b` decimal(9,4) DEFAULT NULL,
  `c` decimal(3,2) DEFAULT NULL,
  `f` float(3,2) DEFAULT NULL,
  `d` double(3,2) DEFAULT NULL

注意:根据存取的数据,decimal的存储空间自动变换;

mysql> select * from aa ;
 ------------- -------- ------ ------ ------ 
| a           | b      |  c   | f    | d    |
 ------------- -------- ------ ------ ------ 
| 5.500000000 | 5.5000 | 5.50 | 5.50 | 5.50 |
 ------------- -------- ------ ------ ------ 
2 rows in set (0.00 sec)

mysql> select length(a),length(b),length(c),length(f),length(d) from aa;
 ----------- ----------- ----------- ----------- ----------- 
| length(a) | length(b) | length(c) | length(f) | length(d) |
 ----------- ----------- ----------- ----------- ----------- 
|    11     |       6   |     4     |     4     |       4   |
 ----------- ----------- ----------- ----------- ----------- 

#每一个数字(包括标点)都占一个字符;小数部分没有达到位数的用0补齐,整数部分有多少算多少;

 

mysql> insert into aa values(5.5,5.5,55.5,55.5,55.5);
mysql> select * from aa;
 ------------- -------- ------ ------ ------ 
| a           | b      | c    | f    | d    |
 ------------- -------- ------ ------ ------ 
| 5.500000000 | 5.5000 | 9.99 | 9.99 | 9.99 |

#超出范围取范围中最大的值,比如总个3位,小数有2两位,则他们最大的值就是9.99; 

经验之谈:

  • 存储性别,省份,类型等分类信息的时候选择TINIYINT或者ENUM(占字节少)
  • BIGINT存储空间更大,INT和BIGINT之间通常选择BIGINT
  • 澳门新浦京娱乐场网站:选择优化的数据类型,数据类型。交易等高精度数据时选择使用DECIMAL

 

字符型


存储用户名的属性:

  • CHAR        字符
  • VARCHAR  字符
  • TEXT  

字符和字节的区别:

 

编码输入的字符串 网易 netease
gbk(双字节) varchar(2)/4字节 varchar(7)/7字节
utf8(三字节) varchar(2)/6字节 varchar(7)/7字节
utf8mb4(四字节) varchar(2)/? varchar(7)/7字节

 

 

 

 

 

utf8mb4虽然说每个汉字占4个字节,但是内部有规定,如果utf8可以存的下的则

还使用utf8标准,这里存储网易两个字显然utf8可以搞定,所以此时?中应该是6个

字节,如果说存储的多了utf8搞不定,则用每一个汉字4个字节来存储,就是8个字节。

 

emoji表情:

  • mysql>5.5.3
  • 库和表的字符集设为utf8mb4

CAHR与VARCHAR区别:

  • char存储定长,容易造成空间浪费
  • varchar存储变长,节省空间
  • char的处理速度比varchar快因为char是固定长度
  • char会对行尾空格进行处理

澳门新浦京娱乐场网站 1

例子:

mysql> show create table vc;
| vc    | CREATE TABLE `vc` (
  `v` varchar(5) DEFAULT NULL,
  `c` char(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
1 row in set (0.00 sec)

#注意AB后面都有一个空格
mysql> INSERT INTO vc VALUES('AB ','AB ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * from vc;
 ------ ------ 
| v    | c    |
 ------ ------ 
| AB   | AB   |
 ------ ------ 
1 row in set (0.00 sec)

#可以看到char会对行尾空格删掉,而varchar不会;
mysql> select concat(v,' '),concat(c,' ') from vc;
 --------------- --------------- 
| concat(v,' ') | concat(c,' ') |
 --------------- --------------- 
| AB            | AB            |
 --------------- --------------- 
1 row in set (0.00 sec)

 

 

TEXT与CHAR和varchar的区别:

  • CHAR和VARCHAR存储单位为字符
  • TEXT存储单位为字节,总大小为65535字节,约64kb
  • CHAR数据类型最大为255字符
  • VARCHAR存储类型为变长;
  • TEXT在数据库内部大多存储格式为溢出页,效率不如CHAR

 

存储头像:

  • BLOB
  • BINARY

 

虽然可以,但是非常不推荐;不推荐在数据库里直接存二进制文件;效率会非常低;那二进制如何存取?

 

blob和text值会引起一些性能问题,特别是在执行了大量的删除操作时,会产生”空洞“

所谓空洞,即虽然已经删除,但是系统还没有回收,要使用optimize table tbl 来整理碎片。

删除操作会在数据表中留下很大的空洞,以后填入这些空洞的记录在插入的性能上会有影响。建议定期使用 OPTIMIZE TABLE tbl 来整理碎片

 

 

经验之谈:

  • CHAR与VARCHAR定义的长度是字符长度不是字节长度
  • 存储字符串推荐选择使用VARCHAR(N),N尽量小
  • 虽然数据库可以存储二进制数据,但是性能低下,不要使用数据库存储文件音频等二进制数据

 

 

存储生日信息:

  • DATE                三字节,如2015-05-01
  • TIME                三字节,如11:12:00
  • DATETIME        八字节,如2015-05-01 11:12:00
  • TIMESTAMP      四字节,如2015-05-01 11:12:00
  • BIGINT

 

TIMESTAMP vs DATETIME

   存储范围的区别 :

   TIMESTAMP         1970-01-01 00:00:01 to 2038-01-19 03:14:07

   DATETIME           1000-01-01 00:00:00 to 9999-12-31 23:59:59

 

   字段类型与时区的关联:

   TIMESTAMP会根据系统时区进行转换,DATETIME则不会

 

 

把现在的时间转换成了BIGINT类型的数据来存储;

mysql> select UNIX_TIMESTAMP (now());
 ------------------------ 
| UNIX_TIMESTAMP (now()) |
 ------------------------ 
|             1461728217 |
 ------------------------ 
1 row in set (0.00 sec)

mysql> select from_unixtime(1461728217);
 --------------------------- 
| from_unixtime(1461728217) |
 --------------------------- 
| 2016-04-27 11:36:57       |
 --------------------------- 
1 row in set (0.00 sec)

 

 

 总结:

  1.   整形经典问题--int(11),int(21) 
  2.   浮点型精度丢失
  3.   DECIMAL变长存储空间
  4.   varchar,char区别
  5.   mysql,concat(v,' ')连接字段  
  6. BIGINT存时间:
    1. select UNIX_TIMESTAMP (now());
    2. select FROM_UNIXTIME (1468842905);  

本文由澳门新浦京娱乐场网站发布于数据库,转载请注明出处:澳门新浦京娱乐场网站:选择优化的数据类型,