MySQL性能优化,合适的数据类型

Anonymity | | 访问(276)

  优化数据类型提高性能的主要原理在于以下几个方面:

  (1)通过选用更“小”的数据类型减少存储空间,使查询相同数据需要的IO资源降低;

  (2)通过合适的数据类型加速数据的比较。

  1、数字日期类型

  先来看看存放长度基本固定的一些数据类型的存储长度和取值范围

  对于数字的存储,一般使用到浮点型数据的场合不应太多。主要出于两个原因,一个是浮点型数据本身不是一个精确的数字,只是一个近似值;另一个原因就是完全可以通过乘以一个固定的系数转换为整型数据来存放。这样不仅可以解决数据不精确的问题,同时也能让数据的处理更为高效。

  时间存储格式种类并不是太多,常用的主要就是DATE-TIME、DATE和TIMESTAMP这三种。从存储空间来看TIMES-TAMP最少,4个字节,而其他两种数据类型都是8个字节,多了一倍。而TIMESTAMP的缺点在于它只能存储1970年之后的时间,另外两种时间类型可以存放从1001年开始的时间。如果须要存放早于1970年的时间,就必须放弃TIMESTAMP类型,但若不须要使用1970年之前的时间,最好尽量使用TIMESTAMP来减少存储空间的占用。

  上面所列出的主要是存放固定长度,和我们平时可能常用的一些类型。通过这个对照表格,可以很直观的看出哪种类型占用的存储空间大,哪种占用的空间小。这样,在选择数据类型时,可以结合各种类型的存储范围及业务中可能存在的数据来选择存储空间最小的类型使用。

  2、字符存储类型

  CHAR[(M)]属于静态长度类型,存放长度完全以字符数来计算,所以最终的存储长度是基于字符集的,如latin1其最大存储长度为255字节,但是如果使用gbk则最大存储长度为510字节。CHAR类型的存储特点是不管实际存放的数据多长,在数据库中都会存放M个字符,不够的通过空格补上,M默认为1。虽然CHAR会通过空格补齐存放的空间,但是在访问数据时,MySQL会忽略最后的所有空格,所以如果实际数据在最后确实需要空格,则不能使用CHAR类型来存放。在MySQL 5.0.3之前的版本中,如果定义CHAR时M值超过255,MySQL会自动将CHAR类型转换为可以存入对应数据量的TEXT类型,如CHAR(1000)会自动转换为TEXT,CHAR(10000)则会转为MEDIUMTEXT。而从MySQL 5.0.3开始,所有超过255的定义MySQL都会直接拒绝并给出错误信息,不再自动转换。

  VARCHAR[(M)]属于动态存储长度类型,仅存储占用实际存储数据的长度。其存放的最大长度与MySQL版本有关,在5.0.3之前的版本VARCHAR以字符数控制存储的最大长度,最大只能存放255个字符,占用存储空间的实际大小与字符集有关。但是从5.0.3开始,VARCHAR的最大存储限制已经更改为字节数限制了,扩展到可以存放65535 bytes的数据,不同的字符集可能存放的字符数并不一样。也就是说,在MySQL 5.0.3之前的版本,M所代表的是字符数,而从5.0.3版本开始,M代表的是字节数了。VARCHAR的存储特点是不管设定M为多大的值,真正占用的存储空间只有存入的实际数据大小,和CHAR不同的是VARCHAR会保留存入数据最后的空格,也就是说我们存入什么,MySQL返回的就是什么。在VARCHAR类型字段的数据中,MySQL会在每个VARCHAR数据中使用1到2个字节来存放VARCHAR数据的实际长度,当实际数据在255字节之内时,会使用1字节来存放实际长度,而大于255字节时,则需要使用2字节来存放。

  TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT这4种类型同属于一种存储方式,即动态存储长度类型,不同的仅是最大长度的限制。4种类型的定义都是通过最大字符数来限制的,但它们的字符数限制实际上是可以理解为字节数限制的,因为当使用多字节字符集时,实际能存放的字符数并没最大字符数那么多,而是以单字节字符来计算的字符数。此外,由于是动态存储长度类型,所以和VARCHAR一样,每个字段数据之前都需要一个存放实际长度的空间。TINYTEXT需要1个字节来存放,TEXT需要2个字节,MEDIUMTEXT和LONGTEXT则分别需要3个和4个字节来存放实际数据长度。实际上,除了MySQL内嵌的最大长度限制之外,它们还受到客户端与服务器端的网络通信缓冲区最大值(max_allowed_packet)的限制。

  这4种TEXT类型和CHAR及VARCHAR在实际使用中存在几个不一样的地方:

  •不能设置默认值;

  •只有TEXT可以使用TEXT[(M)]这样的方式通过M设置大小;

  •基于这4种类型的索引必须指定前缀长度。

  3、其他常用类型

  对于BIT类型,M表示每个值的bits数目,默认为1,最大为64 bits。对于MySQL来说这是一个新的类型,因为从MySQL5.0.3才开始真正实现(在之前实际上是TINYINT(1)),而且仅支持MyISAM存储引擎,但是从MySQL 5.0.5开始,Mem-ory、InnoDB和NDB Cluster存储引擎也开始“支持”了。在MyISAM中,BIT的存储空间很小,是真正实现了通过bit来存储,但在其他的一些存储引擎中就不一样了,因为它们是转换为最小的INT类型存储的,所以占用的空间也没有节省,还不如直接使用INT类的数据类型存放来得直观。

  对于SET和ENUM类型,主要内容基本处于较少变化的状态且值为比较少的字段。虽然这两个字段所占用的存储空间都较少,但是由于在使用方面较其他数据类型要略为复杂一些,所以在实际环境中一般较少使用。

  谁都知道,数据量(这里主要指数据记录条数)的增加肯定会让数据库的检索查询效率降低。所以很多时候人们希望通过减少数据库中关键表的记录条数来获得数据库性能的提升。实际上,除了这种通过控制数据记录条数来控制数据总量的办法之外,还可以通过选择更小的数据类型来让数据库使用更小的空间存放相同的数据量,这对于检索同样的数据所带来的IO消耗自然会降低,性能也就很自然地得到了提升。

  此外,由于CPU对不同数据的处理方式不一样,会造成不同类型的数据在各种运算处理(如比较、排序等)的效率存在差异。所以,对于须要经常进行比较计算及排序等消耗CPU资源的字段,应该尽量选择处理更为迅速的字段类型。如通过整数类型代替浮点数或字符类型。