精华内容
下载资源
问答
  • 数据类型优化

    2018-07-07 11:04:10
    Mysql支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。选择数据类型的基本原则: 更小的通常更好,一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为它们...

    背景

    Mysql支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。选择数据类型的基本原则:

    1. 更小的通常更好,一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为它们占用更少的磁盘,内存,和cpu缓存,并且处理时需要的CPU周期也更少。但是要确保没有低估需要存储值得范围,因为在schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。
    2. 简单就好,简单数据类型的操作通常需要更少的CPU周期。例如整型比字符串操作代价更低,因为字符集和校对规则使字符比整型比较更复杂。
    3. 尽量避免null,如果查询中包含可为null的列,对mysql来说更难优化,因为可为null的列使的索引、索引统计和值比较都更复杂。

    整数类型

    TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT.分别占用16,24,32,64位存储空间。它们可以存储的值的范围从-2的(n-1)次方到2的(N-1)次方-1,其中N是存储空间的位数。整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使正整数的上限提高一倍。

    实数类型

    实数是带有小数部分的数字。然而,它们不只是为了存储小数部分;也可以使用DECIMAL存储比BIGINT还大的整数。Mysql既支持精确类型,也支持不精确类型。

    字符串类型

    mysql支持多种字符串类型,每种类型还有很多变种。Varchar和char类型是两种最主要的字符串类型。

    varchar

    varchar:用于存储可变长的字符串,是最常见的字符串数据类型。它比定长类型更节省空间,因为它仅使用必要的空间。但是Mysql表使用ROW_FORMAT=FIXED创建的话,每一行都会使用定长存储,这会很浪费空间。同时它需要使用1或2个额外的字节记录字符串的长度,如果列的最大长度小于或等于255字节,则使用1个字节表示,否则使用2个字节。如varchar(10)需要11个字节的存储空间。varchar(1000)的列则需要1002个字节,因为需要2个字节存储长度信息。
    varchar节省了存储空间,所以对性能也有帮助。但是,由于行是变长的,在update时可能使行变得比原来更长,这就导致需要额外的工作。如果一个行占用的空间增长,并且在页内没有更多的空间可以存储,在这种情况下,不同的存储引擎的处理方式是不一样的。MyISam会将行拆成不同的片段存储,InnoDB则需要分裂页来使行可以放进页内。

    char

    先通过一个例子来了解下:
    这里写图片描述
    从图中可以看出string3的末尾的空格被截断了。
    这里写图片描述
    从图中可以看出使用varchar(10)字段存储相同的值,string3结尾的空格没有被截断。

    Blob和Text类型

    都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。
    实际上,它们分别属于两组不同的数据类型家族:字符类型是TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT;对应的二进制类型TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB。BLOB是SMALLBLOB的同义词,TEXT是SMALLTEXT的同义词。mysql把每个blob和text值当作一个独立的对象处理。当blob和text值太大时,InnoDB会使用专门的“外部”存储区域来进行存储,此时每个值在行内需要1~4个字节存储一个指针,然后在外存储区域存储实际的值。Blob和text不同是blob存储的是二进制数据。没有排序规则或字符集,而text类型有字符集合排序规则。

    ENUM代替字符串类型

    有时候可以使用枚举代替常用的字符串类型。枚举可以吧一些不重复的字符串存储成一个预定义的集合。

    日期和时间类型

    DATATIME

    这个类型能保存大范围的值,从1001年到9999年,精确度为秒。他把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关。使用8个字节的存储空间。默认情况下,mysql以一种可排序的、无歧义的格式显示DAETIME值,例如“2008-01-16 22:37:08“。这是ansi标准定义的日期和时间表示方法。

    TIMESTAMP

    类型保存了从1970年1月1日1日午夜以来的秒数,它和unix时间戳相同。它只使用4个字节的存储空间,因此它的范围比datatime小的多:只能表示从1970年到2038年。它显示的值也依赖于时区。

    位数据类型

    bit

    可以使用bit列在一列中存储一个或多个true/false值。bit(1)定义一个包含单个位的字段。bit(2)存储2个位,一次类推。最大长度是64个位。它的行因存储引擎而异。

    选择标识符

    特殊类型数据

    展开全文
  • schema和数据类型优化之选择优化的数据类型 文章目录schema和数据类型优化之选择优化的数据类型**更小的通常更好**简单就好尽量避免null选择合适的数据类型整数类型【[参考]...

    schema和数据类型优化之选择优化的数据类型

    更小的通常更好

    尽量使用可正确存储数据的最小的数据类型,更小的数据类型,通常更快。因为它们占更少的磁盘内存 CPU缓存,处理时需要CPU周期更少),但是要确保没有低估存储的值得范围。

    简单就好

    简单类型的操作通常需要更少CPU周期。例如整形比字符型操作代价更低,因为字符集和校对规则(排序规则)使字符比较比整形比较更为复杂。使用MySQL内建类型存时间,整型存IP。

    尽量避免null

    最好指定列为not null

    • null列使用更多的存储空间,MySQL里需要特殊处理
    • null使索引、索引统计和值比较更复杂;可为null的列被索引时,每个索引记录需额外的字节。在MyISAM可能会导致固定大小的索引变成可变大小。
    • 通常把可为NULL的列改为NOT NULL带来的性能提升比较小,一般没有说必须优化,除非确定这会导致问题。
    • 例外:InnoDB使用单独位(bit)存储NULL,so对于稀疏数据(很多值为null)有很好的空间效率,不适用于MyISAM。

    选择合适的数据类型

    TIMESTAMP只使用DATETIME一半的存储空间,并且会根据时区变化。但是TIMESTAMP允许的时间范围更小

    关于TIMESTAMP,DATETIME如何选择推荐阅读【https://segmentfault.com/a/1190000017393602】

    整数类型【参考

    整数(whole number)

    • TINYINT(8位)、SMALLINT(16)、MEDIUMINT(24)、 INT(32)、 BIGINT(64)存储值的范围:-2(N-1)-2(N-1) N是存储空间的位数
    • 有无符号使用相同的存储空间,相同的性能
    • 选择不同的整数类型决定MySQL怎么在内存和磁盘中保存数据。但是整数计算一般使用64位BIGINT整数(一些聚合函数例外,他们使用DECIMAL或DOUBLE进行计算)
    • 可为整型指定宽度,例如INT(11),对于大多数应用无意义,不会限制值的合法范围,只是规定了交互工具显示字符的个数,对于存储和计算,INT(1)和INT(20)是相同的;

    实数(real number)

    • 可以·使用DECIMAL存储比BIGINT大的整数
    • MySQL既支持不精确类型也支持精确类型
    • FLOAT和DOUBLE支持使用标准的浮点运算进行近似计算。mysql使用duble作为内部浮点计算的类型
    • decimal:存储精确的小数。MySQL5.0上DECIMAL类型支持精确计算。CPU不支持DECIMAL的直接计算,所以MySQL5.0以上服务器自身实现了高精度计算。相对而言,CPU直接支持原生浮点计算,所以浮点运算个更快。尽量只在对小数进行精确计算时才使用,如财务数据数据量大时,考虑使用bigint代替,将需要存储的货币单位据小数的位数乘以相应的倍数,可以避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。
    • FLOAT和DECIMAL都可以指定精度。MySQL5.0以上将数字打包保存到一个二进制字符串中(每4个字节存9个数字)DECIMAL(18,9)一共使用9个字节,前4后4,小数点1.
    • MySQL5.0以上DECIMAL允许最多65个数字。早期是254。
    • 浮点列建议:只指定类型、不定精度,这些精度非标准,mysql会悄选类型、或存时对值取
    • 浮点类型存储同样范围的值时,比decimal更少的空间,float4字节, double8字节

    字符串类型

    VARCHAR和CHAR

    前提:innoDB和MyISAM引擎,最主要的字符串类型

    磁盘存储:存储引擎存储的方式与在内存、磁盘上的不能不一样,所以MySQL服务器从引擎取值需转格式

    VARCHAR:
    • 存储可变字符串,比定长节省空间(仅使用必要的空间,越短使用更小的空间),但如果表使用row_format=fixed,行会定长存储。
    • 需使用1或2个额外字节记录字符串长度;列max长度<=255字节,1字节表示,否2字节。采用latinl字符集,varchar(10)列需11个字节的存储空间,varchar(1000)1002字节,2字节存储长度信息。
    • 节省存储空间,利于性能;但在update可能使行变得比原来更长、需做额外工作
    合适的情况:
    • 字符串列最大长度比平均长度大很多;
    • 列的更新少(不担心碎片);
    • 使用UTF-8字符串,每个字符均使用不同的字节数存储
    CHAR:
    • 定长,据长度分配空间,删除末尾空格;长度不够、空格填充
    • 存储空间上更有效率,char(1)来存储只有Y, N的值 1个字节 ,varchar2字节,还有一个记录长度
    适合的情况:
    • 适合存储很短的字符串;
    • 或所有值接近同一个长度;
    • 经常变更的数据,存储不易碎片

    对应空格、存储:

    char类型存储时末尾空格被删;数据如何存储取决于存储引擎,Memory引擎只支持定长的行(最大长度分配空间)

    binary,varbinary:存储二进制字符串字节码,长度不够、\0来凑(不是空格)检索时不会去掉填充值

    二进制比较比字符简单很多,所以也快。

    varchar(5)和varchar(100)存储‘hell’空间开销一样,长的列消耗更多内存,因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或者操作时,磁盘临时表也一样。

    关于磁盘临时表和内存临时表的差异推荐阅读【https://blog.csdn.net/a925907195/article/details/52279035】

    blob和text

    分别用二进制和字符方式存储,分别属于两组不同的数据类型:字符类型:TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT,对应的二进制类型是TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGTEXT。两类仅有的不同:BLOB类型存储的是二进制,无排序规则或字符集,TEXT有字符串,排序规则;

    MySQL会把每个BLOB和TEXT当做独立的对象处理,存储引擎存储时会做特殊处理,当值太大,InnoDB使用专门的外部存储区域进行存储,此时每个值在行内需要1~4个字节存储一个指针,然后在外部存储实际的值;

    mysql对他们的列排序:只对每列前max_sort_length字节排序;且不能将列全部长度的字符串进行索引,也不能使用这些索引消除排序;如果只需要排序前面一小部分字符,则可以减小max_sort_length的配置,或者使用order by sustring(column,length)

    磁盘临时表和文件排序

    因为Memory引擎不支持BLOB和TEXT类型,所以,如果查询了BLOB或TEXT列并且需要使用隐式临时表,将不得不使用MyISAM磁盘临时表。即使只有几行数据也是如此。

    最好的解决方案是尽量避免使用这些类型,如果实在无法避免就在使用到BLOB的字段的地方都是使用 sustring(column,length)将列值转换为字符串,这样就可以使用内存临时表了。但是要确保截取的子字符串足够短,不会使临时表的大小超过max_heap_table_size或tmp_table_size,超过以后会转换为磁盘临时表。

    如果explain执行计划的extra包含using temporary:这个查询使用了隐式临时表

    使用enum代替字符串类型

    定义时指定取值范围,对1~255个成员的枚举需要1个字节存储;对于256~65535个成员,需要2个字节存储。最多可以有65535个成员,ENUM类型只能从成员中选择一个

    可把不重复的固定的字符串存储成一个预定义的集合,MySQL在存储枚举时会据列表值的数量压缩到1-2字节中,在内部会将每个值在列表中的位置保存为整数(从1开始,必须进行查找才能转换为字符串,开销、列表小,可控,且在表的.frm文件中保持“数字-字符串”映射关系的“查找表”。

    将一个数字存储到一个 ENUM 中,数字被当作为一个索引值,并且存储的值是该索引值所对应的枚举成员: 在一个 ENUM字符串中存储数字是不明智的,因为它可能会打乱思维;ENUM 值依照列规格说明中的列表顺序进行排序。(ENUM 值依照它们的索引号排序。)举例来说,对于 ENUM("a", "b") "a" 排在 "b" 后,但是对于 ENUM("b", "a")"b" 却排在 "a" 之前。空字符串排在非空字符串前,NULL 值排在其它所有的枚举值前。为了防止意想不到的结果,建议依照字母的顺序定义 ENUM列表。也可以通过使用GROUP BY CONCAT(col) 来确定该以字母顺序排序而不是以索引值。

    排序时安装创建表时的顺序排序的(应该是);枚举最不好的地方:字符串列表是固定的,添加删除字符串须使用alter table;在‘查找表’时采用整数主键避免基于字符串的值进行关联。

    使用show data_length可以看到:转换成ENUM可以让表的大小缩小1/3.转换后的主键也只有原来的一半大小。

    日期和时间

    通常尽量使用timestamp,因为空间效率更高。

    可以使用bigint类型存储微妙级别的时间戳,或double存秒之后的小数部分,或使用MariaDB代替MySQL;

    bit

    mysql5.0之前与tinyint同义词。

    可以使用BIT列在一列中存储一个或多个true或false。

    bit(1)单个位的字段,bit(2)2个位,最大长度64个位。

    行为因存储引擎而异,MyISAM打包存储all的BIT列(17个单独的bit列只需要17个位存储,myisam3字节ok),其他引擎Memory和innoDB为每bit列使用足够存储的最小整数类型来存放,不节省存储空间。

    mysql把bit当做字符串类型,检索bit(1)值、结果是包含二进制0/1的字符串,数字上下文的场景检索,将字符串转成数字,大部分应用,最好避免使用。

    在这里插入图片描述

    set

    如果需要保存喝多true和false,可以考虑合并这些列到一个SET数据类型,它在MySQL内部是以一系列打包的位的集合来表示的。缺点是改变列的代价高,需要alter table;无法在SET列上通过索引查找。

    替代SET的方式是使用一个整数包装一系列的位。例如可以把8个位包装到一个TINYINT中,并且按位使用。但是这样查询语句较难写且难理解。

    创建表时,就指定SET类型的取值范围 :属性名 SET(‘值1’,‘值2’,‘值3’…,‘值n’),“值n”参数表示列表中的第n个值,这些值末尾的空格将会被系统直接删除,字段元素顺序 系统自动按照定义时的顺序显示 重复 只存一次。

    其基本形式与ENUM类型一样。SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时,不同元素之间用逗号隔开。SET类型的值最多只能是有64个元素构成的组合,根据成员的不同,存储上也有所不同。

    1~8成员的集合,占1个字节。
    9~16成员的集合,占2个字节。
    17~24成员的集合,占3个字节。
    25~32成员的集合,占4个字节。
    33~64成员的集合,占8个字节。
    

    选择标识符identifier

    标识列:自增长列

    • 可不用手动插入值,系统提供默认序列值;
    • 不要求和主键搭配 ;
    • 要求是unique key;
    • 一个表最多一个;
    • 类型只能是数值;
    • 可通过set auto_increment_increment=3;

    选择标识列类型时

    考虑存储类型、mysql对这种类型怎么执行计算和比较,确定后确保在all关联表中使用same类型,类型间要精确匹配;

    技巧:

    1. 整数类型:整数通常最好的选择,很快且可使用auto_increment

    2. enum和set类型,存储固定信息

    3. 字符串:避免,耗空间较数字慢,MyISAM表特别小心(默认对字符串使用压缩索引、查询慢),测试有6倍的性能下降。

    4. 完全“随机”字符串MD5/SHA1/UUID函数生成的新值 会任意分布在很大的空间内,导致insert及部分的select变慢:插入值随机的写到索引的不同位置,insert变慢(页分裂 磁盘随机访问 聚簇索引碎片);select变慢、逻辑上相邻的行分布在磁盘和内存不同的地方;随机值导致缓存对所有类型的查询语句效果都变差(使缓存赖以工作的访问局部性原理失效)

    聚簇索引,实际存储的循序结构与数据存储的物理结构一致,通常来说物理顺序结构只有一种,一个表的聚簇索引也只能有一个,通常默认都是主键,设置了主键,系统默认就为你加上了聚簇索引;

    非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系,与数据的存储物理结构没有关系;一个表对应的非聚簇索引可以有多条,根据不同列的约束可以建立不同要求的非聚簇索引;

    存储uuid,移除-符号,或者用unhex转换uuid值为16字节的数字,且存储在binary(16)列中,检索时通过hex函数格式化为16进制格式;

    UUID生成的值与加密散列函数(sha1)生成的值不同特征:uuid分布不均匀,有一定顺序,不如递增整数

    当心自动生成的schema:

    严重性能问题,很大的varchar、关联列不同的类型;

    orm会存储任意类型的数据到任意类型的后端数据存储中,并没有设计使用更优的类型存储,有时为每个对象每个属性使用单独行,设置使用基于时间戳的版本控制,导致单个属性会有多个版本存在;权衡

    特殊类型数据

    有些数据类型并不直接与内置类型一致。比如低于秒级别的时间戳。还有IPv4地址,应该使用无符号整数存储,而不是使用VARCHAR(15),因为他们本质是32位无符号整数,用小数点分割是为了容易阅读。MySQL提供了INET_ATON()(把 ip 转换为数字)和INET_NTOA()函数在这两种表示方法之间转换。

    展开全文
  • mysql数据类型优化

    2018-09-10 16:13:00
    mysql数据类型优化 一、选择优化的数据类型 良好的逻辑设计和物理设计是高性能的基础,而选择正确的数据类型对于高性能至关重要。在数据库优化方面,数据类型优化只是一方面,还有查询语句优化,...

    mysql数据类型优化

    一、选择优化的数据类型

    良好的逻辑设计和物理设计是高性能的基础,而选择正确的数据类型对于高性能至关重要。在数据库优化方面,数据类型优化只是一方面,还有查询语句优化,主键选择,索引建立,命名规则等。本文只要探讨数据类型方面的选择。一般在选择数据类型的时候,下面的几条原则有助于做出更好的选择:

    • 选择可以正确存储数据的最小数据类型

      更小的数据类型通常处理更快,占用更小的磁盘,内存容量,如果需要网络传输的时候,也占用更小的带宽。

    • 选择简单的数据类型

      简单类型需要更少的cpu处理时间,例如整型比字符串操作代价更低,因为字符集和校对规则使得字符比较比整型比较更麻烦,例如,使用mysql内建的时间类型存储日期和时间,而不是选择字符串。存ip地址的时候,我们可以使用无符号整型进行存储。在mysql中,有2个函数可以把ip地址和整数互相转换。例如:select inet_aton('255.255.255.255') 的结果是4294967295,select inet_ntoa(4294967295) 的结果是255.255.255.255,它的底层是这样算的,255 * 224 + 255 * 216 + 255 * 2+ 255 = 4294967295,整型的处理速度要比字符串快。

    • 避免设置null列

      null是列的默认属性,设置可为null的列会占用更多的空间,而且使得索引和值比较更复杂。

    二、常用数据类型介绍

    •  整数类型:5种类型,tinyint 1字节,smallint 2字节,mediumint 3字节,int 4字节,bigint 8字节,存储范围为:-2(n-1)至2(n-1)-1,n为数据类型位数。当存储的整数不包含负数的时候(无符号整数),可以使用unsinged属性,这样可以使得正数的上限提高一倍。
    • 实数类型:float 4字节,double 8字节,它们都是使用标准的浮点运算进行近似计算。如果想更精准的计算小数,可以使用decimal,例如存储一些财务数据。因为cpu不支持对decimal的直接运算,所以只是mysql本身实现了decimal的高精度计算而已,相比而言,原生浮点类型计算更快,但是不精准,而decimal计算精准,但是计算代价高,并且存储同样范围值的时候,空间占用也更多。如果没有特殊要求,建议采用原生浮点类型。如果是涉及到财务数据需要精准计算,那么可以使用bigint代替decimal,只要根据小数的位数乘以相应的倍数即可,这样既避免了浮点存储计算的不精确,又避免了decimal计算代价高的问题。
    • 字符串类型:主要是varchar和char两种类型,不同的存储引擎对它们在磁盘和内存的存储实现不同,下面主要介绍InnoDB和MyISAM。
      • varchar:存储可变长字符串,它比定长类型更节约空间,因为它仅仅使用必要的空间(例如越短的字符串使用越少的空间,也有例外,如果表使用row_format=fixed创建的话,每一行都会使用定长存储,这会浪费空间)。varchar需要1或者2个字节来存储字符串长度:如果最大长度小于或者255字节,则需要1个字节,否则使用2个字节。对于单字节字符集,varchar(10)的列需要11个字节的存储空间,varchar(300)的列需要302个字节,因为需要2字节存储长度信息。虽然varchar节省了空间,对性能有帮助,但是由于是变长的,一旦使用update更新可能导致行变得比原来长,那么如果一个行占用的空间增长,并且在页内没有更多的空间进行存储。对于InnoDB来说,则需要页分裂来使行可以放进页内,MyISAM会把行进行拆成不同的片段存储,另外在存储和检索的时候会保存字段值末尾的空格。什么情况下使用varchar

        ①字符串列的最大长度比平均长度大很多

        ②列很少更新,所以碎片不是问题

        ③使用UTF-8这样复杂的字符集,每个字符使用的字节数不同。

      • char:存储定长字符串,会删除字段值末尾的空格,例如存的字段s='aaa   ',那么存入的时候其实存的是'aaa',后面的空格会去掉。它适合存储很短的字符串,或者所有的值都接近同一长度,char非常适合存储密码的MD5值,因为这是一个定长的值。对于经常变更的数据,char也比varchar更好,因为定长的char类型不容易产生碎片。对于非常短的列,char比varchar在存储空间上也更有效率,例如char(1)来存储只有Y和N的值,如果采用单字节字符集只需要一个字节,但是varchar(1)却需要两个字节,因为还有一个记录长度的额外字节。

    • blob和text:都是用来存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。Mysql把每个blob和text当作一个独立的对象进行处理。当他们的值太大的时候,InnoDB会使用专门的“外部”存储区域来进行存储,此时每个值在行内需要1-4个字节存储一个指针,然后在外部存储区域存储实际的值。对他们进行排序的规则也与其他类型不同,mysql只对每个列的最前max_sort_length字节而不是整个字符串做排序,如果只需要排序前面小部分字符,则可以减少max_sort_length的配置,或者使用order by sustring(column,length)。Mysql也不能将他们的全部长度的字符串进行索引。如果没有必要,应该尽量避免使用blob和text。
    • 使用枚举(enum)代替字符串类型:枚举列可以把一些不重复的字符串存储成一个预定义的集合。Mysql在存储枚举的时候会根据列表值的数量压缩到一个或者两个字节中,mysql内部会将每个值在列表中的位置保存为整数,并且在表的.frm文件中保存”数字-字符串”映射关系。例如:

        create table t_enum(

              e enum(‘fish’,’apple’,’dog’) not null

        );

       这三行数据实际存储的为整数,而不是字符串。

    • 时间和日期类型:datetime和timestamp,datetime能保存从1001年至9999年,精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,使用8字节的存储空间。timestamp保存了从1970-1-1 00:00:00开始至今的秒数,它和unix时间戳相同,它使用4字节的存储空间,范围比datetime小的多,只能表示从1970年到2038年。Mysql提供了from_unixtime()函数把unix时间戳转为日期,并提供了unix_timestamp()函数把日期转为unix时间戳。
    posted @ 2018-09-10 16:13 neu_张康 阅读(...) 评论(...) 编辑 收藏
    展开全文
  • 目录为什么要进行数据类型优化?选取数据类型的的原则各种数据类型的优化1. 整数类型2.实数类型3.字符串类型4.BLOB 和 TEXT 类型 为什么要进行数据类型优化? mysql支持的数据类型非常多,选择正确的类型对获取高...

    为什么要进行数据类型优化?

    mysql支持的数据类型非常多,选择正确的类型对获取高性能至关重要。

    选取数据类型的的原则

    1. 更小的通常更好
      一般情况下使用正确存储数据的最小数据类型,因为它们占取更小的磁盘,内存和CPU缓存
    2. 简单就好
      例如整型比字符操作代价更低,如采用MySQL内建类型(date,time,datatime)存储时间和日期而不是字符串,另一个是用整型存储ip地址。
    3. 尽量避免NULL
      通常情况下最好使用 NOT NULL 存储,除非真的需要 NULL:
      • 可为 NULL 的列会占用更多存储空间
      • 可为 NULL 的列使索引,索引统计,和值比较都更加复杂
      • 当可为 NULL 的列被索引时,每个索引记录都需要额外的空间。
    4. 不同子数据类型的特殊行为
      例如,DATETIME 和 TIMESAMP 可以存储相同类型的数据,时间和日期精确到秒,然而 TIMESTAMP 只使用 DATETIME 一半的存储空间,并且会根据时区变化拥有自动更新能力。

    各种数据类型的优化

    1. 整数类型

    1. 数字的类型有两种:整数,实数。整数有下列几种类型:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间,它们可以存储的值的范围是 -2 的 N-1 次方到 2 的 N-1 次方减 1 。其中 N 是 存储空间位数。

    2. 整数类型有可选的 UNSIGNED 属性,表示不允许为负值,同时整数上线提高一倍。例如 TINYINT 的存储范围是 -128 ~ 127 ,而 TINYINT UNSIGNED 的存储范围是 0 ~ 255。有无符号都使用相同的存储空间,有相同的性能。

    3. MySQL可以指定整数宽度,如 INT(20) ,但它实际上是没有用的,它不会限定取值的范围,只是规定了 mysql 的交互工具(如 mysql命令行等)显示字符的个数。对于存储和计算 INT(1) 和 INT(20) 是没有区别的。

    2.实数类型

    1. 实数是即带有小数的数字,分为不精确类型 FLOAT 和 DOUBLE 进行近似运算,精确类型 DECIAML 进行精确运算。

    2. DECIAML 可以指定精度:DECIAML(a,b) 中 a代表该数字最多允许的位数,位数是小数部分和整数部分的位数之和,b表示小数部分的最大位数,b<=a。例如 DECIAML(5,4) 代表最多只能有5位,整数部分最对只能有1位,小数部分最多只能有4位。mysql5.0及更高版本将数字打包存储到一个二进制字符串里(每4个字节存9个数字)。

    3. MySQL 5.0 及更高版本 DECIAML 类型最多允许 65个数字。

    3.字符串类型

    1. 从 mysql4.1 开始,每个字符串可以自定义字符集和排列顺序,或者校对规则。将会很大程度影响性能。

    2. VARCHAR

      • varchar 存储可变长字符串,比定长字符串更节省空间,越短的字符串使用越少的空间。
      • varchar 使用 1 或 2个额外字节记录字符串长度,如果列长度小于或等于 255 用 1 个字节,否则用 2 个字节。
      • 适合使用 varchar 的情况
        1. 字符串列的最大长度比平均长度大得多
        2. 列更新很少
    3. CHAR

      • char 是定长的:MySQL 总是根据定义的字符串长度分配足够空间
      • 适合使用 char 的场景
        1. 所有值接近同一长度,如 MD5 。
        2. 很短字符串,如 CHAR(1) 存储 Y 和 N的值只需要 1 个字节,而 VARCHAR(1) 却需要两个字节,还有一个需要记录长度的字节。
    4. 注意
      使用 VARCHAR(5) 和 VARCHAR(200) 存储 'hello’的空间开销是一样的,但是更长的列或消耗更多的内存。

    4.BLOB 和 TEXT 类型

    1. 为了存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。BLOB 采用二进制数据没有排序规则和字符集,而 TEXT 有。

    2. mysql 不能将 BLOB 和 TEXT 的全部长度字符串作为索引,只能使用前面一小部分。

    展开全文
  • mysql支持非常多的数据类型,在设计表的时候需要精心的为每个列选择合适的数据类型以提高数据库的性能,这篇文章回顾了数据库中常用的几种数据类型,并总结了一些数据类型优化的技巧。 1.选择优化的数据类型 mysql...
  • Schema与数据类型优化

    2019-04-10 10:23:55
    ##Schema与数据类型优化 1. 选择优化的数据类型 1)更小的通常更好:占用更少的磁盘、内存和cpu缓存 2)简单就好:简单数据类型的操作通常需要更少的cpu周期 3)尽量避免NULL: NULL更难优化,使用索引、索引统计和值...
  • 一、数据类型优化 更小的通常更好。一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为它们占用更少的磁盘、内存和CPU缓存,处理时需要的CPU周期也更少。(但是要确保没有低估...
  • MySQL支持的数据类型很多,选择正确的数据类型对于高性能至关重要。下面几个简单的原则都有助于做出更好的选择。 更小的通常更好 应该尽量使用可以正确储存数据的最小数据类型。更小的数据类型通常更快,因为它们...
  • 数据类型优化 2.数据库设计优化 3.SQL查询优化 ,细心的观众会觉得少了点什么,索引,对当然得有索引,索引忒重要,这个最后说。 这样既有宏观设计层面的,还有微观数据类型层面的,这就是俗话说的:上得了厅堂,下...
  • 优秀的设计和逻辑是高性能的基本保障,我们来看下关于mysql使用schema和数据类型优化的一些建议和原则。 1.选择优化的数据类型的常用规则 更小的通常更好 一般选择更小的数据类型能满足业务场景的需求,小的数据...
  • 本系列文章参考书籍《高性能MySQL》 ...基础数据类型优化数据类型优化常用于建表之初 一): 1)尽量明确字段的数据类。比如: 日期,整数等要明确其数据类型,不能都赋予字符串类型。 2)经常作为条件查询的字段尽
  • 数据类型优化原则 1.尽量选择可以存储数据的最小数据类型,因为更小的占用内存和cpu更少 2.尽量避免null ,因为在后期优化更复杂 3.选用枚举代替字符串 4.范式和反范式的应用,在项目中根据实际情况可以混用,...
  • Schema与数据类型优化 选择优化的数据类型 1.更小的通常更好 更小的数据类型通常更快,因为它们占用更少的磁盘,内存和CPU缓存 2.简单就好 简单数据类型的操作通常需要更少的CPU周期.例如:整型比字符操作代价更低,因为...
  • MySQl数据类型优化

    2020-04-14 11:56:25
    文章目录1.... 尽量选择使用可以正确存储数据的最小数据类型。更小的数据类型通常更快, 因为它们占用更少的磁盘、内存和 CPU 缓存,并且处理时需要的 CPU 周期也更少; // 越简越好 整型比字...
  • 高性能Mysql之Schema 与数据类型优化选择优化的数据类型MySQL schema设计中的陷阱 选择优化的数据类型 MySQL支持的数据类型非常多,选择正确的数据类型对于获取高性能至关重要,不管存储那种类型的数据,有几个原则...
  • 本文就简单从数据表优化介绍(数据类型优化) 如何选择好的数据类型 数据类型的选择的要点有哪些? 更小的类型通常更好 简单就好 尽量避免NULL 以上三点看起来很简单,应该是大多数时设计数据库也会去遵循的规则...
  • Schema与数据类型优化 良好的逻辑设计和物理设计是高性能的基石,应该根据系统将要执行的查询语句来设计SCHEMA,这往往需要权衡各种因素。如:反范式设计可以加快某些类型的查询,但同时可能会使另一些查询变慢和...
  • 数据库与数据类型优化数据类型优化选择优化的数据类型选择原则数据库优化数据库设计陷阱 MySQL架构与特性(二) 数据类型优化 选择优化的数据类型 mysql支持的数据类型非常多,选择正确的数据类型对于获得高性能至关...
  • MySQL数据类型优化

    2018-10-09 07:37:38
    mysql支持的数据类型很多,良好的数据类型有助于获得更高的性能。 schema优化
  • Mysql Scheme和数据类型优化 良好的逻辑设计和物理设计是高性能的基石,应该根据系统将要执行的查询语句来设计scheme。 选择优化的数据类型 数据类型小的通常更好。 尽量避免NULL,如果计划在该列上创建索引,就...
  • 文章目录Schema与数据类型优化一、选择优化的数据类型更小的通常更好简单就好尽量避免NULL二、数值类型字符串枚举日期和时间类型三、 MySQL schema设计中的陷阱四、范式和反范式1.范式的优点:五、缓存表和汇总表六、...
  • Schema与数据类型优化 更小的通常好 尽量使用可以正确存储数据的最下数据类型,更小的数据类型通常更快,占用更小的磁盘,内存和cpu缓存,并且处理时需要的cpu周期更少 但是要确保没有低估需要存储的值的范围,因为...
  • schema和数据类型优化: 1、数据类型的优化 2、合理使用范式和反范式 3、主键的选择 4,字符集的选择 5、存储引擎的选择 6、适当的数据冗余——是要join回降低查询速度 7、适当拆分-表中有的字段非常大,且不...
  • MySQL调优(一)写在前面性能监控数据类型优化1. 简单数据类型的操作需要更少的CPU周期:2. 尽量避免null3. 整形类型数据类型优化实际细则1. 字符和字符串类型2. MySQL把每个 BLOB 和 TEXT 值当作一个独立的对象处理...
  • Mysql数据类型优化可以使我们在设计之初,就考虑到时间反应,以及空间的节省等方面的优化,使得Mysql数据库从设计之初就趋于完美。那么接下来就一起来什么了解一下我们可以从哪些方面进行优化吧! 二、分析 1. ...
  • 良好的逻辑设计和物理设计是高性能的基石,应该根据系统将要执行的查询语句来设计Schema,这往往需要权衡各种因素。...选择优化数据类型 MySQL支持的数据类型非常多,选择正确的数据类型对于...
  • mysql 数据类型优化

    2018-05-16 21:30:47
    选择正确的数据类型

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 17,113
精华内容 6,845
关键字:

数据类型优化