MySQL Schema与数据类型的优化

数据库 MySQL
如果查询中包含可为null的列,对Mysql来说更难优化,因为可为null的列使得索引、索引统计和值都比较复杂。可为null的列会使用更多的存储空间,在mysql中也需要特别处理。当可为null的列被索引时,每个索引记录需要一个额外的字节。

[[207400]]

选择优化的数据类型:

1、 更小的通常更好:

一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为他们占用更少的磁盘,内存和cpu缓存,并且处理时需要的cpu周期也更少。

2、 简单就好

简单的数据类型操作通常需要更少的cpu周期。例如,整型比字符操作代价更低,因为字符集和校对规则(排序规则)使字符比较比整型更加复杂。注:应使用mysql内建的类型存储时间和日期,而不是字符串。

3、 尽量避免null

如果查询中包含可为null的列,对Mysql来说更难优化,因为可为null的列使得索引、索引统计和值都比较复杂。可为null的列会使用更多的存储空间,在mysql中也需要特别处理。当可为null的列被索引时,每个索引记录需要一个额外的字节。

通常把可为null的列改为not null带来的性能提升比较小。在调优时,没有必要首先在现有的shema中查找并修改掉这个情况,除非确定这会导致问题。但是如果列上建索引,就应尽量避免设计成可为null的列。

整数类型:

整数类型有TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间。他们可以存储的值的范围从-2的(n-1)次方到2的(n-1)次方-1,其中n是存储空间的位数。

整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以是正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0~255,而TINYINT的存储范围是-128~127。

实数类型:

实数是带有小数部分的数字。然后,他们不仅仅为了存储小数部分;也可以使用DECIMAL存储比BITINT还大的整数。MYSQL既支持精确类型,也支持不精确类型。

DECIMAL类型用于存储精确的小数,因为double类型和float类型在进行计算时,会因为精度损失导致一些数据的偏差。但是DECIMAL数据类型cpu不支持对其直接计算,cpu直接支持原生浮点计算,所以浮点运算明显更快。

因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DECIMAL-例如存储财务数据。但是在数据量比较大的时候,可以考虑使用BITINT代替DECIMAL,将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

字符串类型(varchar和char)

  • Varchar:

Varchar类型用于存储可变长字符串,是最常见的字符串类型。他比定长类型更节省空间,因为它仅使用必要的空间(例如,越短的字符串使用越少的空间)。所以,varchar节省了存储空间,对性能也有帮助。但是,由于行是边长的,在update是可能使行变得比原来更长,这就导致需要做额外的操作。如果一个行占用的空间增长,并且页内没有更多的空间存储。MyISAM会将行拆成不同的片段存储,InnoDB则需要分裂页来使行可以放进页内。

注:InnoDB更加灵活,它可以把过长的varchar存储为BLOB。

  • Char类型:

Char类型适合存储很短的字符串,或者所有值都接近同一个长度。例如,char类型非常适合存储密码的md5值,因为他是定长的值。还有用户的身份证号以及手机号码。对于经常变更的数据,char也比vachar更好,因为定长的char类型不容易产生碎片。对于非常短的列,char也比varchar在存储空间上更有效率。例如用char(1)存储Y和N的值,如果采用单字节字符集只需要一个字节,但是varchar(1)却需要两个字节,因为还有一个记录长度的额外字节。

Varchar(5)和varchar(200)存储‘hello’时空间开销是一样的,但是Varchar(5)会有很大的优势,因为更长的列会消耗更多的内存,因为Mysql通常会分配固定带下的内存块来保存内部值。尤其是使用内存临时表进行排序和操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。

时间类型

DateTime和TimeStamp

  • DateTime

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

  • TimeStamp:

存储1970年1月1日午夜以来的描述,他和Unix时间戳相同。TimeStamp只使用4个字节的存储空间,因此它的范围比DateTime小的多;只能表示从1970年到2038年。

TimeStamp显示的值也依赖于时区。Mysql服务器,操作系统,以及客户端连接都有时区设置。

TimeStamp也有DATETIME没有的特殊属性。默认情况下,如果插入时没有指定第一个TimeStamp列的值,Mysql会设置这个列的值为当前时间。在插入一行记录时,Mysql默认也会更新第一个TimeStamp列的插入和更新操作。最后,TimeStamp列默认为not null,这也和其他的数据类型不一样。 

责任编辑:庞桂玉 来源: 扬花落尽丶的博客
相关推荐

2020-10-26 07:16:10

MySQLSchema数据

2010-05-17 16:18:28

MySQL数据类型

2010-05-20 18:05:38

2011-05-11 10:39:01

SQL Serverdatetime数据类优化误区

2018-01-02 20:35:00

数据库MySQLDB2

2010-08-10 17:17:59

2017-07-10 13:38:07

MySQL数据类型整数类型

2010-10-08 14:04:44

MySQL数值数据类型

2010-11-29 10:09:26

SQL Server

2010-06-10 10:06:01

MySQL数据类型

2021-05-31 16:09:31

MySQLSchema设计

2010-06-04 11:15:23

MySQL自增主键

2010-10-15 13:28:34

MySql数据类型

2010-05-31 10:35:12

MySQL数据类型

2018-11-15 09:45:47

JavaScript数据类型变量

2010-08-11 09:14:33

DB2数据类型

2019-08-12 11:40:48

数据库SQLite3数据类型

2010-06-13 18:00:56

MySQL数据类型

2010-10-08 14:45:43

mysql中int

2010-07-22 17:47:32

SQL Server数
点赞
收藏

51CTO技术栈公众号