MySQL 基本数据类型选择要合适

背景介绍:在实际项目中,我发现很多人对数据类型选择不当,比如:本来只是表示简单的五六种状态用的类型是int(实际上tinyint就可以满足要求)、字符串vacharh和char的区别搞不清楚、如果不可能为负数时又没有设置为unsigned、以及int(1)和int(11)实际上占用空间是一样的等等。

选择的原则是:在满足使用的前提,要小而简单。

(1)整形比字符操作代价更低。

(2)保存日期格式时,要选用MySQL内置的日志格式(TimeStamp, DateTime),而不使用字符串。PS:TimeStamp使用4个字节进行存储(存储的时间从1970到2034),而DateTime使用8个字节存储(时间从1001到9999),上述两种时间格式推荐使用TimeStamp. 上述的两种日期格式的精度都只是到秒,如果要存储到毫秒或者更高的精度,可以考虑用BIGINT来保存。

(3)小数点的保存如何有涉及到运算,用float和double可能会精度不准,可以考虑将小数乘于相应的倍数,然后用BIGINT来存储。

(4)整数的几种类型

TINYINT、SMALLINT、MEDIUMINT、BIGINT分别使用8,16,32,64位。保存范围为-2^N到2^N-1(这里N=位数-1),如果加入UNSIGNED则保存的范围数(在正数范围内)提升一倍。

注意:对于INT(1)和INT(20)来说存储的空间和计算是一样的,因此指定位数是没有意义的。

(5)关于存储字符串的几种类型的选择

主要有CHAR, VARCHAR, BLOB, TEXT

关于CHAR和VARCHAR

其中CHAR(10)是定长为10(只需要10个字节)的,而VARCHAR(10)是变长的(不仅需要10个字节,还需要记录长度,如果长度小于255则额外需要1个字节,如果大于255则需要两个字节)。

在使用的时候,如果是定长字符推荐CHAR(N),比如定长的MD5加密的字符串。

CHAR的优势是经常更新不会产生太多的碎片。

对于VARCHAR中的变长个数如果定义的太长也会浪费存储空间的。比如VARCHAR(5)和VARCHAR(100)存储“hello”存储的空间都是一样的,但是更长的varchar的列会消耗更多的内存。

关于BLOB和TEXT

两个都是存储长的字符串,而BLOB存储的格式是二进制,TEXT是字符串。

上述的两种类型都有TINYBLOB,SMALLBLOB,BOLOB,MEDIUMBLOB,LONGBLOB,BLOB

以及TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT

(6)关于保存ip地址

对于通常的ipv4,比如:192.168.1.123,我们会用15位char来存储这种是不推荐的,实际上ipv4是32位的无符号整数,推荐将ipv4转成整数进行保存。

在MySQL中提供了INET_ATON()将ipv4转成整形以及INET_NTOA()将整形转成ipv4

一个示例的用法如下:

create table tbl_ip(ip int unsigned, name char(1))

insert into tbl_ip(inet_aton(‘192.168.1.122’),’d’);

select * from tbl_ip where ip=inet_aton(‘192.168.1.122’);

select inet_ntoa(ip) from tbl_ip;

(7)默认值的缺省值的设置

尽量不用NULL,可以设置为“”或者0。

8)在正式环境如何更改表的结构

方法一:创建影子表,当完成后,通过一个原子的重命名操作切换影子表和原表

MySQL语句的例子:

DROP IF EXISTS tbl_channel_new, tbl_channel_old;

CREATE TABLE tbl_channel_new LIKE tbl_channel;(注意:这里只是复制了表的结果,其数据集仍然是空的)

…(执行修改操作)

RENAME TABLE tbl_channel TO tbl_channel_old, tbl_channel_new TO tbl_channel;

最后新表就是我们修改的表,并保存了一份之前的表,如果新表有问题可以快速回滚。

方法二:在更改表的时候可能会遇到表很大,而内存又不足,以及索引又很多,此时如果执行ALTER TABLE操作可能需要花费数个小时甚至数天的时间才能完成。

通过.frm文件的替换完成,但是实际实验中不成功。

方法三:通常表的修改应该在备机中执行ALTER并在完成后把它切换成主库。

(9)MySQL中的计数器

使用的用法:

UPDATE tbl_stat SET cnt=cnt+1;

上面的操作在计数的时候可以不用取得当前的值,而直接通过指定的列和要增加的值来计算统计结果。

在数据统计中的问题,对于某一行的计数由于会有事务,因此都会有一个全局的互斥锁,使得并发性能不高。

改进方法:

将计算器保存在多行中,每次随机选择一行进行更新,示例代码如下

CREATE TABLE hit_counter(

slot tinyint unsigned not null primary key,

cnt int unsigned not null

)ENGINE=InnoDB;

然后预先在这张表上增加100行数据。随机选择一个槽(slot)进行更新:

UPDATE hit_counter SET cnt=cnt+1 WHERE slot=RAND()*100;

进一步改进,如果需要每隔一段时间开始一个新的计数器(比如:每天一个),则修改表为:

CREATE TABLE daily_hit_counter(

day date not null,

slot tinyint unsigned not null,

cnt int unsigned not null,

primary key(date, slot)

)ENGINE=InnoDB;

在这个场景中,可以不用预先增加行,而用ON DUPLICATE KEY UPDATE代替(即如果不存在指定的主键KEY则插入,存在则执行更新操作),语句如下:

INSERT INTO daily_hit_counter(day, slot, cnt) VALUES(CURRENT_DAY, RAND()*100, 1) ON DUPLICATE KEY UPDATE cnt=cnt+1;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值