Chinaunix首页 | 论坛 | 博客
  • 博客访问: 187106
  • 博文数量: 52
  • 博客积分: 120
  • 博客等级: 民兵
  • 技术积分: 1189
  • 用 户 组: 普通用户
  • 注册时间: 2011-08-03 15:41
个人简介

MySQL DBA

文章分类

全部博文(52)

文章存档

2013年(51)

2011年(1)

分类: Mysql/postgreSQL

2013-03-07 13:09:37

字段选型及属性优化

1.设计问题

在新建一个表或者优化表的过程中,我们的思路一般是:存储什么样的数据,需要多大的空间,不同的定义对性能的影响。

1.       列中将存储什么样的数据。这个应该是最开始就需要弄明白的问题,我们经常遇到的数值类型,字符型,时间类型,可以很明确的区分出来。

2.       选择适当的数据类型。在确定数据大类后,就得从同一类型中进行选择,如数值型,是tinyint,smallint,mideumnint,int,bigint。就得清晰得明确这一列的数据的取值范围,从何选择适当的数据类型。对于字符型,使用varchar还是char,还是text,根据不同的需求以及存储引擎来抉择。

3.       定义适当的长度。对于每一种数据类型,长度的控制可能会带来空间以及性能的大幅提升,比如char(M),另:数值类型的长度定义只影响显示长度,实际存储以及应用程序的调用的取值范围由类型取值范围决定。

2.理论知识:

分类:数值、日期时间、字符、空间

数值类型改为usigned,可增加取值范围

mysql中所有运算采用双精度。

尽量避免null

数值型默认为0,如果在anto_increment 为增长序列中下一个值。


         各类型取值范围:(
为总长度,d为小数位数)

bit                     m为每一个值所占的位数,范围1-64,默认1

tinyint                unsigned 取值0 to 255,signed 取值-128 to 127

bool,Boolean       同tinyint(1),0为false,非0为true

smallint               unsigned 取值0 to 65535,signed取值-32768 to 32767

medumint           unsigned 取值 0 to 16777215.,signed 取值 -8388608 to 8388607.

int                     unsigned 取值 0 to 4294967295,signed:-2147483648 to 2147483647.

bigint                 unsigned:  0 to18446744073709551615,signed -9223372036854775808 to9223372036854775807.

decimal(m,d)       m为值的总长度,默认为10,最大65;d为小数位数,默认为0,最大30

float(m,d)        signed   -3.402823466E+38 to -1.175494351E-38, 0, unsigned 1.175494351E-38 to3.402823466E+38

double(m,d)     signed:1.7976931348623157E+308 to -2.2250738585072014E-308, 0, unsigned:2.2250738585072014E-308 to 1.7976931348623157E+308.

float(p)            p为0-24 将以float处理,25-53将以double处理



时间日期   默认以0格式表示,第一行timestamp默认为当前时间


date              '1000-01-01' to '9999-12-31'

datetime        '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

timestamp      '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

time              '-838:59:59' to '838:59:59'.

YEAR[(2|4)]   2: 70 to 69     4:  1901 to 2155,

      
         
字符串   默认为空,enum列默认为第一个值

char(m)            定长,m最大值255,默认为1。可设为char(0):存放null和空值

varchar(m)      不定长,m最大值65535

binary(m)        定长二进制

varbinary(m)   不定长二进制

tinyblob          最大值255字节

blob(m)      最大65535字节,使用2字节来记录字节数

mediumblob  最大1677725字节,3字节记录长度

longblob        最大4G字节,由参数和可用内存控制,使用4字节记录长度

tinytext           最大255字符,由具体字符集控制,使用一个字节来记录字节数

text                 最大65535字符,由具体字符集控制,使用2个字节来记录字符数

mediumtext   最大1677725字符,由具体字符集控制,使用3个字节来记录字符数

longtext         最大4G字符,由具体字符集控制,使用4个字节来记录字符数

enum             mysql内部用数值存储,最大可存储65535个值,每个值有且只能有enum中一个值

set                 每个值为set的真子集,也可为0。最大可以有64个成员。mysql 内部使用数值存储


3.存储空间:

数值类型中的m,如int(m),只对显示有效,和该列的存储范围无关。

Type

Bytes

Tinyint

1

smallint

2

Mediumint

3

Int

4

Bigint

8

Decimal

M+2

float

4

double

8

时间日期

Type

Bytes

Time

3

Date

3

Date-time

8

Year

1

Timestamp

4

字符串

Type

Bytes

Char

M*w, w字符集控制 +1

Varchar

L+1<255> L+2

Text

L+2

longtext

L+4

Enum

1 or2

Set

1/2/3/4 8

4.实验及用例

    实践证明一切,通过对比实际性能来说明问题。

        select * from table procedure analyse();
    这个语句将返回表中各列的数据分布情况,并提优化建议,相当不错的工具。

1). 为何要选择适当的数据类型。

毋庸置疑的是,计算机处理数值比处理字符来得快。如果在一列实际为数值的列中选择字符类型,得到的结果是这样的:

  1. mysql> desc t1;
  2. +-------+------------+------+-----+---------+-------+
  3. | Field | Type | Null | Key | Default | Extra |
  4. +-------+------------+------+-----+---------+-------+
  5. | id | tinyint(1) | YES | | NULL | |
  6. | uid | char(4) | NO | | 0 | |
  7. | name | char(10) | YES | | NULL | |
  8. +-------+------------+------+-----+---------+-------+

如上,t1的结构为3列,其中uid存放数值类型的数据,如果选用字符类型,char或者varchar();插入一条数据后,占用的空间为:

  1. *************************** 1. row ***************************
  2.            Name: t1
  3.          Engine: MyISAM
  4.         Version: 10
  5.      Row_format: Fixed
  6.            Rows: 1
  7.  Avg_row_length: 44
  8.     Data_length: 44
  9. Max_data_length: 12384898975268863

共占用44个字节,其中id列1个字节,uid为4*3=12个字节,name为10*3=30字节,再加上一个字节的结束符。

现在我们做一个优化,将uid列改为tinyint型。

  1. alter table t1 change uid uid tinyint not null default 0;

再看看空间占用情况:

  1. *************************** 1. row ***************************
  2.           Name: t1
  3.          Engine: MyISAM
  4.         Version: 10
  5.   Row_format: Fixed
  6.            Rows: 1
  7. Avg_row_length: 33
  8.     Data_length: 33
  9. Max_data_length: 9288674231451647

共33B,减少11B,差值=4*3-1,也就是说改变这一列的属性,在每一行我们节约了11B的空间。11B的空间很小,但是如果数据量大了呢?我们来算一算:

在这个表结构前提下,不同数据量带来的存储空间需求:

Rows

Char

Tinyint

Sub

1

4*3=12B

1B

11B

100

12*100=1200B

100B

1100B

1w

12*1w=120K

10K

110k

100w

12*100W=12M

1M

11M

1000w

12*1000W=120M

10M

110M

    可见,修改uid列的属性后,对数据的存储空间节省了较大的空间,可见选择适当的数据类型将带来性能上的优化,因为,空间的减少不只是磁盘空间,也包含缓存空间,节省出来的空间我们可以存放更多的数据到缓存中,减少磁盘io,提升语句执行效率。

从上面的测试中,我们可以看出,使用用占用更小空间的数据类型来定义列属性,能有效提升性能。

2). 选择适当的长度。

确定使用什么数据类型后,下面来看看长度的问题。

对于int系列的类型,m只会影响到交互工具的显示长度。

这里以char(m)为例。

仍然使用上面的表。

  1. -------+------------+------+-----+---------+-------+
  2. | Field | Type | Null | Key | Default | Extra |
  3. +-------+------------+------+-----+---------+-------+
  4. | id | tinyint(1) | YES | | NULL | |
  5. | uid | tinyint(4) | NO | | 0 | |
  6. | name | char(10) | YES | | NULL | |
  7. +-------+------------+------+-----+---------+-------+

插入一条数据后

  1. *************************** 1. row ***************************
  2.            Name: t1
  3.          Engine: MyISAM
  4.         Version: 10
  5.      Row_format: Fixed
  6.            Rows: 1
  7.  Avg_row_length: 33
  8.     Data_length: 33
  9. Max_data_length: 9288674231451647

占用33个字节,分别为:tinyint列1+1=2,char列10*3=30,和一个字节的结束符。

暂且不分析存储引擎对char和varchar的操作效率,单从存储空间角度分析,现在将name列属性改为varchar类型。

  1. alter table t1 change name name varchar(10) not null;

占用空间:

  1. *************************** 1. row ***************************
  2.            Name: t1
  3.          Engine: MyISAM
  4.         Version: 10
  5.      Row_format: Dynamic
  6.            Rows: 1
  7.  Avg_row_length: 20
  8.     Data_length: 20
  9. Max_data_length: 281474976710655

占用20个字节,减少空间13个字节。类似于上诉的方法,在数据量增长的情况下,使用varchar类型将节约更多的空间。

下面将以一个实际的表来进行进一步分析列属性对mysql性能的影响。

先看表结构:

  1.  CREATE TABLE `login` (
  2.   `Id` int(11) NOT NULL DEFAULT '0',
  3.   `utbno` int(11) unsigned DEFAULT NULL,
  4.   `logintype` varchar(2) CHARACTER SET latin1 DEFAULT NULL COMMENT '00:usrname, 01:phone, 02:email, 03:vip, 04:mac',
  5.   `sid` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  6.   `logintime` datetime DEFAULT NULL,
  7.   `logouttime` datetime DEFAULT NULL,
  8.   `logoutreason` char(2) CHARACTER SET latin1 DEFAULT NULL COMMENT '00 normal 01 sessiontimeout',
  9.   `logtype` varchar(2) CHARACTER SET latin1 DEFAULT NULL COMMENT '00 login 01 logout',
  10.   `loginipstr` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  11.   `loginipvalue` bigint(20) DEFAULT NULL,
  12.   `caeversion` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  13.   `mac` varchar(35) CHARACTER SET latin1 DEFAULT NULL
  14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

数据量:

  1. +----------+
  2. | count(*) |
  3. +----------+
  4. | 5610156 |
  5. +----------+

    分析:这个表记录的是用户登录网站后进行的操作记录。我们的优化工作大部分情况时面对有数据量有的时候还很大的情况,所以对当前数据的分析也很重要。

    开始优化之前,先看看表的数据情况(结果太长就不贴了):

  1. select * from login procedure analyse();

列属性的分析:

id   `Id` int(11) NOT NULL DEFAULT '0',
        
作为innodb的主键,此列通常是这样定义,我这里是复制了真实表,中间有改变引擎,主键没有了。

`utbno` int(11) unsigned DEFAULT NULL,

        这一列是其他表的外键,索引要保证类型一致,当前看来定义mediumint就可以满足需求,但为防止数据量的增长,设置为int比较妥当。

logintype` varchar(2) CHARACTER SET latin1 DEFAULT NULL COMMENT '00:usrname, 01:phone, 02:email, 03:vip, 04:mac',

         从comment中可以看出,这个列的值其实是一个有限的范围,且实际存储值为1、2、3、4,使用varchar(2)类型,每行将占用2个字节,此时可考虑2种方式来优化:

    a.  修改为tinyint类型,节省1个字节,但可能会到应用程序代码的更改

    b.  修改为enum类型,节省1个字节,对应用程序没有影响

由于登录类型肯定有一个值,所以加上not null属性

优化之后对字符集就没有影响了,不需要指定字符集了

    综合上面的分析,优化语句为:

  1. alter table login change logintype logintype enum('0','1','2','3','4') not null default '1';

`sid` varchar(50) CHARACTER SET latin1 DEFAULT NULL,

         这一列存放的为加密值,最长字符49个,修改掉其null属性即可

  1. alter table login change sid sid varchar(50) not null default '';

`logintime` datetime DEFAULT NULL,

`logouttime` datetime DEFAULT NULL,

        这两列记录用户登录登出得详细时间,未采用timestamp值,不过可修改掉null属性。

  1. alter table login change logintime logintime datetime not null;
  2. alter table login change logouttime logouttime datetime not null;

`logoutreason` char(2) CHARACTER SET latin1 DEFAULT NULL COMMENT '00 normal 01 sessiontimeout',

        这一列的情况和logintype一样,修改为enum类型。

  1. alter table login change logoutreason logoutreason enum('0','1') not null

`logtype` varchar(2) CHARACTER SET latin1 DEFAULT NULL COMMENT '00 login 01 logout'

       同样,修改为enum类型,not null;            

  1. alter table login change logtype logtype enum('0','1') not null;

`loginipstr` varchar(50) CHARACTER SET latin1 DEFAULT NULL,

       这一列存放ip,ip上限为15,由于长度可变,则考虑varchar(20)not null

  1. alter table login change loginipstr loginipstr varchar(20) character set latin1 not null; 

`loginipvalue` bigint(20) DEFAULT NULL,
        此列定义bigint有些过大,使用usigned即可满足,修改其null属性。

  1. alter table login change loginipvalue loginipvalue int unsigned not null;

`caeversion` varchar(100) CHARACTER SET latin1 DEFAULT NULL,

        这一列记录版本信息,目前最长长度不超过15,考虑修改为varchar(20)

  1. alter table login change caeversion caeversion varchar(20) character set latin1 not null;

`mac` varchar(35) CHARACTER SET latin1 DEFAULT NULL

        这一列存放mac加密字符,只需修改其null属性。

  1. alter table login change mac mac varchar(35) character set latin1 not null;

完成优化后,首先看看在存储空间上的变化:

改动前:   

  1. Row_format: Compact
  2.            Rows: 5577259
  3.  Avg_row_length: 98
  4.     Data_length: 550502400
  5. Max_data_length: 0
  6.    Index_length: 318799872
  7.       Data_free: 592445440

改动后:

  1. Row_format: Compact
  2.            Rows: 5759065
  3.  Avg_row_length: 96
  4.     Data_length: 553648128
  5. Max_data_length: 0
  6.    Index_length: 0
  7.       Data_free: 592445440
从结果来看,修改主要为:类型,长度以及null值得控制,对空间的优化为每行的平均长度减少2B,500W节约10M的空间,节约出来的空间,可在内存中存储更多的数据和索引。 在结构一定的情况下,为列属性选择恰当的数据类型以及定义适当的数据长度将为我们的mysql整体性能带来提升。

  

阅读(2572) | 评论(0) | 转发(0) |
0

上一篇:Mysql-select 语法

下一篇:Innodb status

给主人留下些什么吧!~~