mysql 字段优化

字段选型及属性优化

1.设计问题

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

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

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

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

2.理论知识：

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

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

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

尽量避免null。

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

    各类型取值范围：（m 为总长度，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整体性能带来提升。