数据类型优化_python 数据类型 内存优化 - CSDN
  • 数据类型优化

    2017-06-02 10:23:18
    选择数据类型 更小的通常更好 尽量使用可以正常存储数据的最小数据类型,占用更少的磁盘空间、内存和CPU缓存 简单就好 简单数据类型的操作通常需要更少的CPU周期 尽量避免NULL 如果查询中包含可为NULL的列,MySQL...

    选择数据类型

    • 更小的通常更好
      尽量使用可以正常存储数据的最小数据类型,占用更少的磁盘空间、内存和CPU缓存
    • 简单就好
      简单数据类型的操作通常需要更少的CPU周期
    • 尽量避免NULL
      如果查询中包含可为NULL的列,MySQL更难优化

    整数类型

    TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
    - 分别使用8,16,32,64,128位存储空间,存储的值范围:

    -2^{N-1} - 2^{N-1}-1
    • 整数有UNSIGNED属性,表示无符号整数
    • MySQL可以为整数类型指定宽度,例如INT(11),这个只是规定了MySQL的一些交互工具用来显示字符的个数, 对于 存储计算是没有意义的

    实数类型

    FLOAT, DOUBLE, DECIMAL
    - DOULEFLOAT使用标准的浮点运算进行近似计算,DECIMAL用于存储精确的小数
    - FLOAT占4个字节,DOULE占8个字节。DECIMAL可以指定小数点前后所允许的最大位数,会影响空间消耗,每4个字节存9个数字,小数点本身占一个字节,最多允许65个数字。
    - DECIMAL只是一种存储格式,在计算中会转换为DOUBLE类型。
    - 因为额外的空间和计算开销,尽量只在对小数进行精确计算时才使用DECIMAL,最好使用BIGINT代替DECIMAL

    字符串类型

    VARCHAR, CHAR, BLOB, TEXT

    VARCHAR和CHAR

    • VARCHAR存储可变字符串,比定长类型更节省空间,仅使用必要的空间。但如果MySQL使用ROW_FORMAT=FIXED建表的话,每一行都会使用定长存储
    • VARCHAR需要使用1 (最大长度小于255字符)或2(最大长大于255字节)个额外字节记录字符串长度。
    • 由于VARCHAR是变长,在UPDATE时可能使行变得比原本更长,导致需要做额外 的工作
    • 在字符串最大长度比平均长度大很多,列更新很少;使用UTF-8这种复杂的字符集的情况下,适合使用VARCHAR

    使用VARCHAR(5)和VARCHAR(200)存储一个5字节的字符串的空间开销是一样的,但是更长的列会消耗更多的内存,MySQL通常会分配固定的内存块内部值。在使用内存临时表或磁盘临时表进行排序时会消耗过多的不必要的空间。

    CHAR

    • CHAR类型是定长的,MySQL总是根据定义的字符串长度分配足够的空间
    • CHAR适合存储很短的字符串或者是所有值都接近同一长度
    • 对于经常变更的数据,CHAR也比VARCHAR更好,因为定长类型不容易产生碎片

    CHARVARCHAR类似的类型还有BINARYVARBINARY,它们存数的是二进制字符串。存储的是字节码而不是字符,使用\0而不是空格进行填充。

    BLOB和TEXT

    • 都是为了存储很大的数据,分别采用二进制和字符串方式存储
    • MySQL把每个BLOBTEXT值当作一个独立的对象处理。当值太大时,InnoDB会使用专门的外部存储区域进行存储,此时每个值在行内需要1~4个字节存储一个指针,外部存储实际值。
    • MySQL对这两种类型排序只对每个列的最前max_sort_length进行排序。

    因为Memory引擎不支持BLOB和TEXT,所以,如果查询使用了这两种类型列并且需要使用隐式临时表,将不得不使用MyISAM磁盘临时表,会导致严重的性能开销。尽量避免使用这两种类型,如果实在无法避免,在用到时,使用SUBSTRING(column,length)将列值转换为字符串(要足够短,不会使临时表的大小超过max_heap_table_size或tmp_table_size,超过以后会转为磁盘临时表),就中以使用内存临时表了。

    枚举

    • 使用枚举代替常用的字符串类型
    • MySQL在存储枚举时非常紧凑,会根据列表值的数量压缩到一个或两个字节中,MySQL在内部会将每个值在列表中的位置保存为整数,并在表的frm文件中保存映射关系
    • 枚举排序是按内部存储的整数而不是定义的字符串进行排序

    日期和时间类型

    MySQL能存储的最小时间粒度为秒,但是MysQL可以使用微秒级的粒度进行临时运算。

    DATETIME
    • 从1001年到9999年,精度为秒。把日期和时间封半到格式为YYYYMMDDHHMMSS的整数中,与时区无产
    • 使用8个字节的存储空间
    TIMESTAMP
    • 保存从1970年1月1日午夜以来的秒数(格林尼治标准时间)
    • 4个字节的存储空间,从1970年到2038年,显示的值依赖时区

    - 默认为NOT NULL,插入时如果字段为空为插入当前时间。

    范式与反范式

    数据库三范式

    • 第一范式(1NF):强调的是列的原子性,即列不能够再分成其他几列。
    • 第二范式(2NF):首先是 1NF,另外包含两部分内容,一是表必须有一个主键;二是没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的一部分。
    • 第三范式(3NF):首先是 2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖。即不能存在:非主键列 A 依赖于非主键列 B,非主键列 B 依赖于主键的情况。

    范式的优点和缺点

    • 更新操作通常比反范式要快
    • 由于只有很少或者没有重复的数据,所以只需要修改更少的数据
    • 表通常更小,可以更好地放在内存里,执行速度更快
    • 更少的DISTINCT或者GROUP BY语句
    • 复杂一点的查询语句关联太多,也可能使索引失效,影响效率

    反范式的优点和缺点


    • 避免关联
    • 更好的使用索引

    如果不需要关联表,对大部分最差的查询情况,如全表扫描,当数据比内存大时比关联要快得多,因为这样避免了随机I/O,全表扫描基本上是顺序I/O

    展开全文
  • 整形,如 int(11) // 11 指客户端显示的 **宽度** decimal(M,D) // D只小数点后的位数,四舍五入的结果,M 为总位数,**精度**varchar(n) //GBK编码,一个汉字占两个字节。UTF-16编码,通常汉字占两个字节,CJKV扩展B...
    4.Schema与数据类型优化
      
      4.1 选择优化的类型
      	原则:
      		1.更小的通常更好
      			一般情况下,应该尽量选择可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为它们占用更少的磁盘,内存
      		  和 cpu 缓存,并且处理时需要 cpu 周期也更少。
      		    但是要确保没有低估需要存储的值的范围。
      		2.简单就好
      			简单的数据类型的操作通常需要更少的 cpu 周期。例如,整型比字符串操作代价更低,因为字符集和校对规则(排序规则)使
      		  字符集比整型复杂。这里有2个例子,一个是应该使用 mysql 内建的类型而不是字符串来存储日期和时间,另外一个是应该使用
      		  整型来存储 IP 地址。
      		3.尽量避免 NULL
      			很多表都可以包含为 null 的列,即使应用程序并不需要保持 null 也是如此,这是因为 null 是列的默认属性。通常情况下,
      		  最好指定列为 not null。
      		    如果查询中包含可为 null 的值,对 mysql 来说更难优化,因为可为 null 的列使得索引,索引统计和值都变的复杂。可为null
      		  的列会使用更多的存储空间,在 mysql 里面也需要特殊处理。当可为 null 的列被索引时,每个索引记录需要一个额外的字节,在
      		  MyISAM 里面甚至还可能导致固定大小的索引编程可变大小的索引.
      		    通常把可为 null的列改为 not null 带来的性能提升比较小,所以(调优时)没有必要首先在现有的 schema 中查找并修改掉这种情况。
      		    当然也有例外,例如值得一提的是,InnoDB 使用单独的位(bit)存储 null值,所以对于稀疏数据有更好的空间效率。
    
        4.1.1 整数类型
        	类型:tinyint(8),samllint(16),mediumint(24),int(32),bigint(64)
        	可选的属性 unsigned 属性,表示不允许负值。
        	mysql 可以为整数类型指定宽度,例如 int(11)。对存储来说,int(1)和 int(11)是一样的。
    
        4.1.2 实数类型
        	实数是带小数部分的数字。然后,它们不只是为了存储小数部分,也可以使用 decimal 存储比 bigint 还大的整数。
        	float 和 double 类型都支持使用标准的浮点进行近似的计算。
        	decimal 用于存储精确的小数。
        	因为 cpu 不支持对 decimal 的直接计算,所以在 mysql 5.0 以及更高的版本中,mysql 服务器自身实现了decimal 的高精度计算。
          相对而言,cpu 直接支持原生浮点运算,所以浮点运算明显更快。
    
            浮点和 decimal 都可以指定精度。对于 decimal 而言,可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。mysql 5.0 和
          更高版本将数字打包保存到一个二进制字符中(每4个字节存9个数字)。例如,decimal(18,9) 小数两边各存储9个数字,一共使用9个字节:小数
          前的数字用4个字节,小数后的数字用4个字节,小数本身占用一个字节.
    
            DECIMAL(P,D);
            P是表示有效数字数的精度。 P范围为1〜65。
            D是表示小数点后的位数。 D的范围是0~30。MySQL要求D小于或等于(<=)P。
            DECIMAL(P,D)表示列可以存储D位小数的P位数。十进制列的实际范围取决于精度和刻度。
            例如,DECIMAL(19,9)对于小数部分具有9位数字,对于整数部分具有19位= 10位数字,小数部分需要4个字节。 整数部分对于前9位数字需要4个字节,1个剩余字节需要1个字节。DECIMAL(19,9)列总共需要9个字节。
    
            mysql 5.0 和更高的版本中的 decimal类型允许最多65个数字。而早起的mysql 版本中这个限制是 254个数字,并且保存为未压缩的字符串(每个
          数字一个字节)。然后,这些版本实际上并不能在计算中使用这么大的数字,因为 decimal 只是一种存储格式,在计算中 decimal 会转换为 double 类型。
    
            浮点数在存储同样范围的值时,通常比 decimal 使用更少的空间。float 使用4个字节,double 使用8个字节,相比 float 有更高的精度和更大的范围。
          和整数类型一样,能选择的只是存储类型;mysql 使用 double 作为内部浮点计算的类型。
            因为需要额外的空间和计算开销,所有应该尽量只在对小数进行精确计算时才使用 decimal --- 例如存储财务数据。但在数据较大时,可以考虑使用 bigint
          替代 decimal,将需要存储的货币单位根据小数的位数乘以相应的倍数即可。这样可以同时避免浮点存储计算不精确和 decimal 精确计算代价高的问题。
    
            float和double都是浮点型,而decimal是定点型;
    		MySQL 浮点型和定点型可以用类型名称后加(M,D)来表示,M表示该值的总共长度,D表示小数点后面的长度,M和D又称为精度和标度,如float(7,4)的 可显示为-999.9999,MySQL保存值时进行四舍五入,如果插入999.00009,则结果为999.0001。
    		FLOAT和DOUBLE在不指 定精度时,默认会按照实际的精度来显示,而DECIMAL在不指定精度时,默认整数为10,小数为0。
    
    		超过精度时,浮点数不会警告,定点数会警告。
    
        4.1.3 字符串类型
        	从 mysql 4.1 开始,每个字符串可以定义自己的字符集和排序规则,或者说校对规则。这些东西很大程度上影响性能。
    
        	varchar 和 char 类型:
    
        	varchar:
        		varchar : 用于存储可变长字符串,它比定长类型更省空间,因为它仅适用必要的空间。有一种情况例外,如果 mysql 表使用 ROW_FORMAT=FIXED 创建
        	  的话,每一行都会使用定长存储,这会很浪费空间。
        	    varchar 使用1到2个额外字节存储字符串的长度:如果列的最大长度小于或者等于255字节,则使用1个字节表示;否则使用2个字节表示。
        	    varchar 节省了空间,所以对性能有帮助。但是,由于行是变长的,在 update 时可能使行变的比原来长,这就导致了额外的工作。如果一个行占用的空间增长,
        	  并且在页内没有更多的空间可以存储,在这种情况下,不同的存储引擎的处理方式是不一样的。例如,MyISAM 会将行拆成不同的片段存储,InnoDB则需要分裂页来使得
        	  行可以放进页内。
        	    下面情况下使用 varchar 是合适的:字符串的最大长度比平均长度大很多,列的更新很少,所以碎片不是问题;使用了像 utf-8 这样复杂的字符集,每个字符都使用
        	  不同的字节数进行存储。
        	    InnoDB 更灵活,它可以把过长的 varchar 存储为 blob。
    
        	char:
        	    char 类型是定长的:mysql 总是根据定义的字符串长度分配足够的空间。当存储char值时,mysql 会删除末尾的空格。char 值会根据需要采用空格进行填充以方便比较。
        	    char 很适合存储很短的字符串,或者所有的值都接近一个长度。例如, char 非常适合存储密码的 md5 值,因为这是一个定长的值。对于经常变更的数据,char 也比varchar 
        	  好,因为定长的 char 不容易产生碎片。对于非常短的列,char 比 varchar 在存储空间上也更有效率。例如,使用 char(1) 存储只有 Y 和 N 的值,如果采用单单字节字符集
        	  只需要一个字节,但是 varchar(1)却需要2个字节,因为还有一个记录长度的额外字节。
    
        	    记住,字符长度定义不是字节数,是字符数。多字节字符集会需要更多的空间存储单个字符。
    
        	    数据如何存储取决于存储引起,并非所有的存储引起都会按照相同的方式处理定长和变长的字符串。memory 引擎只支持定长的行,即使有变长字段也会根据最大长度分配最大空间。
        	  不过,填充和截取空格的行为在不同的存储引擎都是一样的,因为这是在mysql 服务器层进行处理的。
    
        	    与 char 跟 varchar 类似的还有 binary 和 varbinary ,它们存储的是二进制字符串。二进制字符串跟常规字符串非常相似,但是二进制字符串存储的是字节码而不是字符。
    
        	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 类型是有字符集和排序规则的。
    
                mysql 对 blob 和 text列进行排序和其他类型是不同的:它只对每个列的最前 max_sort_length 字节而不是整个字符串做排序。如果只需要排序前面的一小部分字符,
              则可以减少 max_sort_length 的配置,或者使用 order by substring(column, length);
                mysql 不能将 blob 和 text 列全部长度的字符串进行索引,也不能使用这些索引消除排序。
    
    
            磁盘临时表和文件排序:
            	因为 memory 不支持 blob 和 text 类型,所以,如果查询使用了 blob 或者 text 列并且需要使用隐式临时表,将不得不使用 MyISAM 磁盘临时表,即使只有几行数据
              也是如此。这会导致严重的性能开销,即使配置了 mysql 将临时表存储在内存块设备上(RAM Disk),依然需要许多昂贵的系统调用。
                最好的解决办法是尽量避免使用 blob 和 text 类型。如果实在无法避免,有一个技巧是所有用到 blob 字段的地方都使用 substring(column, length)将列值转换为字符串,
              这样就可以使用内存临时表了。但是需要确保截取的子字符串足够短,不会使用临时表的大小超过 max_heap_table_size 或 tmp_table_size,超过以后 mysql 会将内存临时表
              转换为 MyISAM 磁盘临时表。
                最坏的情况下长度分配对于排序的时候也是一样的,所以这一招对于内存中创建大临时表和文件排序,以及在磁盘上创建大临时表和文件排序这2种情况是很有帮助的。
    
            使用枚举类型(enum)替换字符串类型:
            	有时候可以使用枚举类型替换常用的字符串类型。枚举类型可以把一些不重复的字符串存储成一个预定义的集合。mysql 在存储枚举时非常紧凑,会根据列表值的数量压缩到一个或者
              2个字节中。mysql 会在内部将每个值在列表中的位置保持为整数,并且在表的 .frm 文件中保存 '数字---字符串'映射关系的 '查找表'.
                实际存储的是整数,而不是字符串。
                select e + 0 from enum_test;
                枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。
                一种绕过这种限制的方式按照需要的顺序来定义枚举列。另外也可以在查询中使用 field() 函数显示的指定排序顺序,但这样会导致 mysql 无法利用索引来消除排序。
                select e from enum_test order by field(e, 'apple', 'dog', 'fish');
                枚举最不好的地方是,字符串列表是固定的,添加或者删除字符串必须使用 alter table。因此,对于一些列未来可能会改变的字符串,使用枚举不是一个好主意,除非能
              接受只在列表末尾添加元素,这样在 mysql 5.1 中就可以不用重建整个表来完成修改。
    
                由于 mysql把枚举值保持为整数,并且必须进行查找才能转换为字符串,所以枚举列有一些开销。通常枚举列表都比较小,所以开销还是可以控制,但也不能保证一直如此。
              在特定情况下,把 char/varchar 列与枚举进行关联的可能会比直接关联 char/varchar 列更慢。
    
        4.1.4 日期和时间类型
        	mysql 能存储的最小时间粒度为秒。
        	2种日期类型:datetime 和 timestamp
        		datetime: 这个类型最大能保存的范围,从1001到9999年,精度为秒。它把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中,与时区无关。使用8个字节的存储空间。
        		默认情况下,mysql 以一种可排序,无歧义的格式显示 datetime 值。
    
        		timestamp: 保存了从 1970年1月1日午夜以来的描述,它和unix时间戳相同.timestamp 只是用4个字节的存储空间。因此它的范围比datetime 小的多:只能表示从
         	  1970年到2038年。mysql 提供了 from_unixtime() 函数把 unix 时间戳转换为日期,并提供了 unix_timestamp() 函数把日期转换为 unix 时间戳。
         	    timestamp 的显示的值依赖于时区。datetime 则保留文件表示的日期与时间。
         	    timestamp 也有datetime 没有的特殊属性。默认情况下,如果插入时没有指定第一个 timestamp 列的值,mysql 则设置这个列的值为当前的时间。
         	    除了特殊行为之外,通常也应该尽量使用 timestamp,因为它比 datetime 空间效率更高。
         	    如果需要存储比秒更小的粒度怎么办?可以使用 bigint 存储。
    
        4.1.5 位数据类型
        	bit:
        		在 mysql 5.0 之前,bit是和 tinyint 的同义词。可以使用 bit 列在一列中存储一个或者多个 true/false的值。bit(1)定义一个包含单个位的字段,bit(2)存储2个位。
        	  bit 列最大的长度是64个位。
        	    mysql 把 bit 当作字符串类型,而不是整数类型。当检索bit(1)的值时,结果是一个包含二进制0或者1的字符串,而不是 ASCII 码的 '0' 或者 '1'。
    
        	set:
        		如果需要保存很多true/false 值,可以考虑合并这些列到一个 set 数据类型,它在mysql内部是以一些列打包的位的集合来表示的。这样就能有效的利用了存储空间,并且
        	  mysql 有像 find_in_set() 和 field() 这样的函数,方便查询。它的主要缺点是改变列的定义的代价比较高,需要 alter table,这对大表来说是非常昂贵的操作。
    
        4.1.6 选择标识符
         	当选择标识列的类型时,不仅仅需要考虑存储类型,还需要考虑 mysql 对这种类型怎么执行计算和比较。比如,mysql 在内部使用整数存储 enum 和 set 类型,然后再比较
         操作时转换为字符串。
         	一旦选定了一种类型,要确保在所有关联表中都使用相同的类型。类型之间需要精确匹配,包括 unsigned这样的属性。混合使用不同数据类型可能导致性能问题,即使没有性能问题
         ,在比较操作时隐式转换也可能导致很难发现的错误。
         	在可以满足值的范围要求下,并且预留未来增长空间的前提下,应该选择最小的数据类型。
         	
         	整数类型:
         		整数类型通常是最好的标识符选择,因为它们很快且可以使用 auto_increment;
    
         	enum 和 set 类型:
         		enum,set 适合存储固定的信息,比如有序的状态,产品的类型,人的性别等。
    
         	字符串类型:
         		如果可能,应该避免使用字符串类型作为标识符,因为它们很耗空间,并且通常比数字类型慢。尤其是在 MyISAM 表里面使用字符串作为标识符时要小心,因为MyISAM 默认对
         	字符串使用压缩索引,这会导致查询变慢。
         	    对于完全随机的字符串也需要多加注意,例如 md5(), sha1()或者 uuid()产生的字符串。这些函数生成的新值会任意分布在很大的空间内,这会导致 insert 以及一些 
         	select 语句变的很慢。
         		1.因为插入值会随机的写到索引不同的位置,所以使得 insert 语句更慢。这会导致页分裂,磁盘随机访问,以及对于聚簇存储引擎产生聚簇索引碎片。
         		2.select 语句会变慢,因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
         		3.随机值会导致对所有类型的查询语句效果都很差。因为会使得缓存赖以工作的访问局部原理失效。
    
         	    uuid() 生成的值与加密散列函数例如 sha1()生成的值有不同的特征:uuid值虽然分布也不均匀,但还是有一定的顺序的。
    
        4.1.7 特殊类型数据
        	IPv4 地址,任何经常使用 varchar(15)列来存储 ip 地址。然后,它实际上是32位无符号整数,不是字符串。mysql 提供了 inet_aton(),inet_ntoa()函数来转换。
    
    
      4.2 MySQL schema 设计中的缺陷
      	  1.太多的列
      	  		mysql 存储引擎api在工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成
      	  	  行数据结构的操作代价是非常高的。MyISAM 的定长行结构实际上是与服务器层的行结构正好匹配,所以不需要转化。然后,MyISAM的变长行结构和InnoDB的行结构则
      	  	  总是需要转换。转换的代价依赖于列的数量。
    
     	  2.太多的关联
     	  		所谓的 '实体-属性-值'(EAV) 设计模式是一个常见的糟糕的设计。mysql 限制了每个关联操作最多只能有61张表。单个查询最好在12个表以内做关联。
    
     	  3.全能的枚举
     	  		在 mysql 中,当需要在枚举列表中增加一个新的时,就需要做一次 alter table 操作。
    
     	  4.变相的枚举
    
     	  5.非此发明的null
     	  		即使需要null时,可以用 0,空字符串,或者某个特殊值替代。
    
    
       4.3 范式和反范式
       		4.3.1 范式的优点和缺点
       			1.范式话的更新操作通常比反范式快
       			2.当数据较好的范式化时,就只有很少或者没有重复数据,所以只需要修改更少的数据
       			3.范式化的表通常更小,可以更好的存放在内存中。
       			4.很少有多余的数据意味着检索表数据时更少需要 distinct 或者 group by 语句。
    
       			缺点是通常需要关联。
    
       		4.3.2 反范式的优点和缺点
       			反范式可以很好的避免关联。
       			如果不需要关联,则对大部分查询最差的情况---即使表没有使用索引---是全表扫描。当数据比内存大时这可能比关联要快的多,因为这样避免了随机IO.
    
       		4.3.3 混用范式和反范式
       			最常见的反范式数据的方法是复制或者缓存,在不同的表中存储相同的特定列。也可以使用触发器更新缓存值。
    
       4.4 缓存表和汇总表
       		有时候提升性能最好的方法是在同一张表中保存衍生的冗余数据。然后,有时候也需要创建一张完全独立的汇总表或缓存表。
       		当重建汇总表和缓存表时,通常需要保证数据在操作时依然可用。这就需要通过使用 '影子表' 来实现,'影子表' 指的是在一张真实表的 '背后' 创建的表。
       	  当完成建表操作后,可以通过一个原子重命名操作切换影子表和原表。
       	    create table my_summary_new like my_summary;
       	    ...
       	    rename table my_summary to my_summary_old, my_summary_new to my_summary;
    
       	    4.4.1 物化视图
    
       	    4.4.2 计数器表
       	    	如果应用在表中保存计数器,则在更新的时候看你碰到并发问题。
       	    	如果表只有一行记录,对于任何想要更新这一行的事务来说,这条记录上都有一个全局的互斥锁,使得这些事务只能串行执行。要获得更高的性能,也可以将计数器
       	      保存在多行中,每次随机选择一行进行更新。
    
       4.5 加快 alter table 操作的速度
       		alter table 操作的性能对大表来说是个大问题。mysql 执行大部分修改表结构的操作的方法是用新的结构创建一个空表,从旧表查出所有数据插入新表,然后删除旧表。
       	  这样的操作困难需要花费很长的时间,如果内存不足而表又很大的话,而且还有很多索引的情况下尤其如此。
       	    一般而言,大部分 alter table 操作将导致 mysql 服务中断。
       	    对于常见的场景,使用的技巧有2种:
       	    	1.先在一台不提供服务的机器上执行 alter table 操作,然后和提供服务的主库进行切换;
       	    	2.另外一种是 '影子拷贝'。技巧是要求表结构创建一张和源表无关的新表,然后通过重命名和删除表的操作交换2张表。
    
       	    不是所有的 alter table 都会引起表重建。
    
       	    4.5.1 只修改 .frm 文件
       	    	1.创建一张具有相同结构的空表
       	    	2.执行 flush tables with read lock。这将会关闭所有正在使用的表,并且禁止任何表被打开
       	    	3.交换 .frm 文件
       	    	4.执行 unlock tables 来释放第二步的读锁
    
       	    4.5.2 快速创建 MyISAM 索引
       	    	为了高效的载入数据到 MyISAM 中,有一个常用的技巧是先禁用索引,载入数据,然后重新启用索引。
       	    	alter table test.load_data disable keys;
       	    	...
       	    	alter table test.load_data enable keys;
       	    	不幸的是,这个办法对唯一索引无效。因为 disable keys 只对非唯一索引有效。MyISAM 会在内存中构建唯一索引,并且为载入的每一行检查唯一性。一旦索引的
       	      大小超过了有效内存大小,载入操作就会变得越来越慢。
    
    整形,如 int(11) // 11 指客户端显示的 **宽度**
    decimal(M,D) // D只小数点后的位数,四舍五入的结果,M 为总位数,**精度**
    
    varchar(n) //GBK编码,一个汉字占两个字节。UTF-16编码,通常汉字占两个字节,CJKV扩展B区、扩展C区、扩展D区中的汉字占四个字节(一般字符的Unicode范围是U+0000至U+FFFF,而这些扩展部分的范围大于U+20000,因而要用两个UTF-16)。UTF-8编码是变长编码,通常汉字占三个字节,扩展B区以后的汉字占四个字节。null 也会占用一个字节 **字节数**
    
    datetime  // 8字节
    timestamp // 4字节,不设置这个字段,也会更新
    
    选择数据类型方式:
    1.更小的通常更好(更好的存储空间,更小的cpu)
    2.简单就好(整数存储ip,)
    3.避免null
    
    varchar 与 char
    varchar 省空间,但 update 会页分裂
    
    blog 与 text
    只针对前  max_sort_length 排序,
    Memory 不支持这个2中类型,所以只能用磁盘临时表
    
    枚举:
    create table enum_test(
        -> e enum('fish','apple','dog') not null);
    insert into enum_test(e) values('fisha');
    

    这里写图片描述
    这里写图片描述

    这里写图片描述

    1.选择优化的数据类型
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述

    2.整数类型
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述

    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述


    选择标识符:
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述

    特殊类型数据:
    这里写图片描述

    MySQL schema 设置中的缺陷:
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述

    范式和反范式:
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述

    缓存表和汇总表:
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述

    物化视图:
    这里写图片描述
    这里写图片描述

    计数器表:
    这里写图片描述
    这里写图片描述
    这里写图片描述

    加快 alter table 操作的速度:
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述


    https://zhidao.baidu.com/question/454436588167256525.html

    http://www.cnblogs.com/gomysql/p/3615897.html

    展开全文
  • Schema与数据类型优化

    2019-04-10 10:23:55
    ##Schema与数据类型优化 1. 选择优化的数据类型 1)更小的通常更好:占用更少的磁盘、内存和cpu缓存 2)简单就好:简单数据类型的操作通常需要更少的cpu周期 3)尽量避免NULL: NULL更难优化,使用索引、索引统计和值...

    ##Schema与数据类型优化

    1. 选择优化的数据类型

    1)更小的通常更好:占用更少的磁盘、内存和cpu缓存

    2)简单就好:简单数据类型的操作通常需要更少的cpu周期

    3)尽量避免NULL: NULL更难优化,使用索引、索引统计和值比较都更复杂。

    2. Schema设计陷阱

    1)大多的列

    2)大多的关联

    3.范式与反范式

    4.缓存表和汇总表

    1)计数器表

    展开全文
  • 数据类型优化

    2018-11-06 15:56:33
    数字有两种类型:整数(Whole Number)和实数(Real Number)。如果存储整数,就可以使用这几种整数类型:TINYINT、SMALLINT、 MEDIUMINT、INT 或BIGINT,它们分别需要8、16、24、32 和64位存储空间。它们的范围为 −2n−1...

    1.整数

    数字有两种类型:整数(Whole Number)和实数(Real Number)。如果存储整数,就可以使用这几种整数类型:TINYINT、SMALLINT、 MEDIUMINT、INT 或BIGINT,它们分别需要8、16、24、32 和64位存储空间。它们的范围为
    2n12n11 -2^{n-1}到2^{n-1}-1
    这里的n是所需存储空间的位数。

    整数类型有可选的UNSIGNED属性,它表示不允许负数,并大致把正,上限提高了一倍。例如,TINYINT UNS IGNED保存的范围为0到255,而不是-127到128。

    有符号(Signed)和无符号(Unsigned) 类型占用的存储空间是一样的,性能也一样。因此可以根据实际情况采用合适的类型。

    你的选择将会决定MySQL把数据保存在内存中还是磁盘上。然而,整数运算通常使用64位BIGINT整数,即使是32位架构也如此。(一些聚合函数是例外,它们使用DECIMAL或DOUBlE进行计算。)

    MySQL还可以对整数类型定义宽度,比如INT(11)。这对于大多数应用程序都是没有意义的:它不会限制值的范围,只规定了MySQL的交互工具(例如命令行客户端)用来显示字符的个数。对于存储和计算,INT(1)和INT (20)是一样的。

    提示: Falcon 存储引擎和MySQLAB提供的其他存储引擎内部存储整数的机制是不同的。用户不能控制存储数据的实际大小。第三方存储引擎,例如Brighthouse, 也有自己的存储格式和压缩方案。

    2.实数

    实数有分数部分。然而,它们并不仅仅是分数。可以使用DECIMAL保存比BIGINT还大的整数。MySQL同时支持精确与非精确类型。

    floAt和DOUBLE 类型支持使用标准的浮点运算进行近似计算。如果想知道浮点运算到底如何进行,则要研究平台浮点数的具体实现。

    DECIMAL类型用于保存精确的小数。在MySQL 5.0及以上版本,DECIMAL类型支持精确的数学运算。MySQL4.1和早期版本对DECIMAL值执行浮点运算,它会因为丢失精度而导致奇怪的结果。在这些MySQL版本中,DECIMAL仅仅是“存储类型”。

    在MySQL5.0及以上版本中,服务器自身进行了DECIMAL运算,因为CPU并不支持对它进行直接计算。浮点运算会快一点,因为计算直接在CPU上进行。

    可以定义浮点类型和DECIMAL类型的精度。对于DECIMAI列,可以定义小数点之前和之后的最大位数,这影响了所需的存储空间。MySQL 5.0和以上版本把数字保存到了一个二进制字符串中(每4个字节保存9个數字)。例如,DECIMAL (18, 9)将会在小数点前后都保存9位数字,总共使用9个字节:小数点前4个字节,小数点占1个字节,小数点后4个字节。

    MySQL 5.0及以上版本中的DECIMAL类型最多允许65个数字。在较早的版本中,DECIMAL 最多可以有254个数字,并且保存为未压缩的字符串(一个数字占一个字节)。然而,这些版本的MySQL根本不能在计算中使用如此大的数字,因为DECIMAL只是一种存储格式。DECIMAL 在计算时会被转换为DOUBLE类型。

    可以用多种方式定义浮点数列的精度,它会导致MySQL悄悄采用不同的数据类型,或者在保存的时候进行圆整。这些精度定义符不是标准的,因此我们建议定义需要的类型,而不是精度。

    比起DECIMAL类型,浮点类型保存同样大小的值使用的空间通常更少。flOAT 占用4个字节。DOUBLE占用8个字节,而且精度更高、范围更大。和整数一样,你选择的仅仅是存储类型。MySQL在内部对浮点类型使用DOUBLE进行计算。

    由于需要额外的空间和计算开销,只有在需要对小数进行精确计算的时候才使用DECIMAL,比如保存金融数据

    3.字符串类型

    VARCHAR和CHAR类型

    两种主要的字符串类型是VARCHAR和CHAR。不幸的是,很难确切地解释这两种类型是怎样破保存到磁盘或内存中的,因为具体实现依赖于存储引擎(例如,Falcon 几乎对每种数据类型都用了自己的存储格式)。这里假设你使用的是InnoDB和或MyISAM。如果不是,请参考所使用的存储引擎的文档。

    先看看VARCHAR和CHAR是如何被保存到磁盘上的。要知道存储引擎可能会使用不同的方式把CHAR和VARCHAR类型保存到内存中,并且服务器从存储引擎取回这些值的时候还可能会把它转换为其他存储格式。下面是两种类型的比较:

    VARCHAR

    **VARCHAR保存了可变长度的字符串,是使用得最多的字符串类型。**它能比固定长度类型占用更少的存储空间,因为它只占用了自己需要的空间(也就是说较短的值占用的空间就较少)。例外情况是使用ROW_ FORMAT-FIXED 创建的MyISAM表,它为每行使用固定长度的空间,可能会造成浪费。

    **VARCHAR使用额外的1到2字节来存储值的长度。**如果列的最大长度小于或等于255,则使用1字节,否则就使用2字节。假设使用latin1 字符集,VARCHAR(10)将会占用Il字节的存储空间。VARCHAR (1000)则会占用1002字节,因为需要2个字节来保存长度信息。

    **VARCHAR能节约空间,所以对性能有帮助。**然而,由于行的长度是可变的,它们在更新的时候可能会发生变化,这会引起额外的工作。如果行的长度增加并不再适合于原始的位置时,具体的行为则会和存储引擎相关。例如,MyISAM会把行拆开,InnoDB则可能进行分页。另外的存储引擎还可能不会在合适的位置更新数据。

    当最大长度远大于平均长度,并且很少发生更新的时候,通常适合使用VARCHAR。这时候碎片就不会成为问题。还有当你使用复杂的字符集,比如UTF-8时,它的每个字符都可能会占用不同的存储空间。

    在5.0及以上版本,无论是保存还是取值,MySQL都会保留字符串末尾的空格。但是在4.1及之前的版本,这些空格会被去掉。

    CHAR

    CHAR是固定长度的。MySQL总是为特定数量的字符分配足够的空间。当保存CHAR值的时候,MySQL会去掉任何末尾的空格。(在MySQL4.1及之前版本,VARCHAR也是如此。CHAR 和VARCHAR在逻辑上是一样的,只是存储格式不同。)进行比较的时候,空格会被填充到字符串末尾。

    **CHAR在存储很短的字符串或长度近似相同的字符串的时候很有用。**例如,CHAR适合用存储用户密码的MDS哈希值,它的长度总是一-样的。对于经常改变的值,CHAR 也好于VARCHAR,因为固定长度的行不容易产生碎片。对于很短的列,CHAR的效率也高于VARCHAR。CHAR(1) 字符串对于单字节字符集(注1)只会占用1个字节,但是VARCHAR(1)则会占用2个字节,因为有1个字节用来存储长度信息。

    4.BLOB和TEXT类型

    BLOB和TEXT分别別以二进制和字符形式保存大量数据

    事实上,它们各自有自己的数据类型家族:字符类型有ェ INYTEXT、 SMALLT2xT、TXT、 MEDIUMTEX和工 ONGTEXT,进制类型有 TINYBLOB、 SMALLBLOB、BLOB、 MEDIOMBLOB和 LONGBLOB。BLOB等同于 SMALLBLDB,TEx等同

    于MAL

    不同, MYSQL把BLOB和TEXT当成有实体的对象来处理。存储引擘通常会特别地保存它们。 INNODB的时候会使用单独的“外部”存储区域来进行保存。毎个值在行里面都需要1到4字节,并且还需

    BLOB和TEXT唯一的区别就是BLOB保存的是二进制数据,没有字符集和排序规则,但是TEXT有字符集和排序

    MySL对BLOB和TEXT列的排序方式和其他类型不同:它不会按照字符串的完整长度进行排序,而只是按照max_soxt_1 ength规定的前若干个字节进行排序。如果只按照开始的几个字符排序,就可以减少

    max_sort_1 ength的值或使用 ORDER BY SUBSTRING( column,length)

    MYSQL不能索引这些数据类型的完整长度,也不能为排序使用索引。(后文有这方面的更多内容。)

    5.日期和时间类型

    MYSQL提供了两种相似的数据类型: DATETIME和 TIMESTAMP。

    DATETIME

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

    在默认情况下, MYSQL以一种可排序的、清楚的格式显示 DATETIME值,例如2008-01-16 22:37208.这种表示日期和时间的方式符合ANSI标准

    TIMESTAMP

    就像它的名字一样, TIMESTAMP类型保持了自1970年1月1日午夜(格林尼治标准时间)以来的秒数 它和Unⅸ的时间数相同。 TIMESTAMP只使用了4字节的存储空间,因此它的范围比 DATETIME小得多。它表示只能从1970年到2038年。 MYSQL提供了FROM_ UNIXTIME()函数把Unix时间藏转换为日期,并提供录入UNIX_TIMESTAMP()函数,把日期转换为unix时间戳。

    较新的 MYSQL版本按照 DATETIME格式化 TIMESTAMP的值,但是较老的 MYSQL不会在各个部分之间显示任何标点符号。

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

    保存0值的 TIMESTAMP实际显示为美国东部时间1969-12-31 19:00:00,与格林尼治标准时间(GMT)相差5小时。

    TIMESTAMP也有 DATETIME没有的特殊性质。在默认情况下,如果插入的行没有定义 TIMESTAMP列的值,MYSQL就会把它设置为当前时间(注3),在更新的时候,如果没有显式地定义 TIMESTAMP列的值, MYSQL 也会自动更新它。可以配置 TIMESTAMP列的插入和更新行为。最后, TIMESTAMP.列默认是not null,这和其他的数据类型都不一样

    除了这些特殊行为之外,通常应该使用 TIMESTAMP,因为它比 DATETIME更节约空间。有时人们把Unⅸ的时间戳保存为整数值,但是这通常没有任何好处。因为这种格式处理起来不太方便,我们并不推荐它。

    如果需要以秒以下的精度保存日期和时间,应该怎么办? MYSQL当前没有适合的数据类型,但是可以使用自己的存储格式:可以使用 BIGINT类型井且把它以毫秒的精度保存为时间戳格式,或者使用 DOUBLE保存秒的分数部分。这两种办法都不错。

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

    mysql支持非常多的数据类型,在设计表的时候需要精心的为每个列选择合适的数据类型以提高数据库的性能,这篇文章回顾了数据库中常用的几种数据类型,并总结了一些数据类型优化的技巧。
    1.选择优化的数据类型
    mysql支持非常多的数据类型,选择正确的数据类型对优化性能非常重要,下面几个原则适用于所有的数据类型。
    1) 更小的类型通常更好
    一般情况下应该使用能够正确存储数据的最小数据类型。小的数据类型意味着更少的磁盘 更少的cpu时间,能获得更好的性能。
    2) 简单就好
    简单数据类型的操作通常需要更少的cpu周期,比如说整型比字符型的操作代价更低,因为字符串和校验规则(排序规则)使得字符串比较操作更加复杂;两个更具体的例子:应该使用mysql内建的类型来存储日期和时间而不是字符串;应该使用整型来存储IP地址。
    3) 尽量避免NULL
    NULL是列的默认属性,所以很多情况下即使应用程序不需要NULL值,列中存储的也是NULL.问题在于可为null的列使得索引 索引统计和值的比较都变得麻烦起来,另外可为null的列会使用更多的存储空间,在mysql里也需要特殊处理。当可为null的列被索引时,每个索引记录需要一个额外的字节。总的来说,如果将数据库中所有可为null的列改成not null,性能的提升也是有限的,主要需要考虑的其实还是如果计划在列上建索引,那么就应该避免这个列是可为null的。
    在为列选择数据类型的时候,显示要确定合适的大类型,比如数字 字符串 时间等,这一步通常是很直观的;第二步是选择具体类型,很多mysql都可以存储相同类型的数据,只是存储的范围 长度或精度不一样,需要磁盘存储空间不一样,相同大类型的不同子类型数据有时也会有些特殊的行为和属性。例如DATETIME 和TIMESAMP都可以用来存储相同的数据:比如时间和日期,精确到秒。但是TIMESAMP只需要DATETIME一半的存储空间,并且还能做到根据时区变化,但问题是TIMESAMP可以表示时间的范围比DATATIME小得多。下面来介绍各个具体的数据类型。
    整数类型
    有两种类型的数字:整数和实数。整数分为TINYINT SMALLINT MEDIUMINT INT BIGINT 分别使用8 16 24 32 64位存储空间;每种类型能表示的数字类型是 -2^(n-1)~2^(n-1)-1,其中n就是存储空间的位数。整数类型有可选的UNSIGNED,这大致可以使正数的表示范围扩大一倍,比如TINYINT可以表示的范围是-128-127,而UNSIGNED TINYINT可以表示的范围是0~255。有符号整型和无符号整型使用相同的存储空间,并具有相同的性能,所以可以视情况选择合适的类型。类型的定义可以决定mysql怎么在内存和磁盘中存储数据,然而整数计算一般使用64位的BIGINT的,即使是在32位的机器上也是如此。
    mysql可以为整数类型指定宽度,例如INT(11),但对于大多数应用这是没有意义的,它不会限制值的合法范围,只是规定了一些mysql交互工具(mysql客户端)显示字符的个数,对于存储和计算来说INT(1)和INT(11)是一样的效果。

    实数类型
    实数是带有小数部分的数字,但是也可以用来存储整数,比如可以用DECIMAL存储比BIGINT还要大的整数。表示小数的一共有三种类型:FLOAT DOUBLE和DECIMAL,前两者称之为浮点类型,这三者都可以指定浮点列所需要的精度。FLOAT占用4字节空间,DOUBLE占用8字节的空间,相比FLOAT来说可以表示更高的精度和更大的范围;而在mysql 5.0及其以上版本,DECIMAL允许最多表示65个字符。FLOAT和DOUBLE是存储类型,内部浮点计算默认使用的都是DOUBLE类型,并且cpu支持原生的浮点计算,所以这种计算比较快,但是会出现浮点误差;而DECIMAL可以实现精确的小数计算,这是通过mysql自己的机制实现的,性能比较低。对于需要进行精确计算的小数,比如说财务数据,可以将原先的小数乘以一个单位化成BIGINT进行存储,可以避免浮点数计算不精确和DECIMAL计算代价高的问题。

    字符串类型
    mysql支持多种字符串类型,每种类型还有很多变种,并且从mysql4.1开始每个字符串列可以定义自己的字符集和排序规则,这些很大程度上会影响性能。
    1) VARCHAR和CHAR类型
    VARCHAR和CHAR是最常见的两种字符串类型,但是不同存储引擎在内存或磁盘对他们进行存储的格式是不一样的,所以得区分不同存储引擎来研究它们,下面以InnoDB和MYISAM来对其进行研究。
    VARCHAR
    VARCHAR用来存储可变长的字符串,是最常见的字符串类型。它比定长的字符串更节省空间,因为它只使用必要的存储空间。为了实现长度可变,VARCHAR需要额外使用一到二个字节来记录字符串的长度:如果字符串的长度少于255,则使用一个字节;如果长度大于255,则需要两个字节来进行记录。VARCHAR节省了存储空间,对性能也有提升。但因为是变长的,如果update操作增加了列的长度,就需要额外的工作,如果某个列的数据长度增加而页内没有足够的存储空间,这种情况下不会的存储引擎会有不同的处理策略,比如InnoDB会分裂页使得数据可以放到页内。下面的几种情况可以认为是适合使用VARCHAR的:
    列的最大长度,比平均长度大很多;
    列的更新很少,所以碎片也会很少;
    使用了向utf-8这样复杂的字符集,每个字符都使用不同长度的字节进行存储。
    注意在mysql 5.0或更高版本时mysql在存储和检索VARCHAR类型数据的时候会保留末尾的空格。
    CHAR
    CHAR是定长的:总是会根据定义的字符串长度分配足够大的空间。在存储的时候CHAR会去掉所有末尾的空格,CHAR会根据需要填充空格以便于比较。
    CHAR适用于存储很短的字符串或长度都很接近的字符串。前者比如用CHAR来存储”Y”和”N”这两个字符,会比VARCHAR要少一个字节的空间(因为VARCHAR还需要一个额外的空间来存储长度),对于后者,CHAR比较适合用来存储密码的MD5值,因为他们长度都是一样的。对于经常需要更新的列,CHAR也会导致更少的碎片。

    对变长和定长的字符串的存储规则是由不同的引擎实现的,Memory引擎对变长的字符串也会使用定长的空间进行处理,但是对于空格的截取和填充,都是一样的,因为都是在mysql服务层进行的。虽然VARCHAR(5)和VARCHAR(255)存储”hello”的磁盘大小是一致的,但是考虑到mysql有时候需要创建内存临时表进行操作时,后者需要更大的内存空间,所以对于VARCHAR而言也不会是越大越好,分配真正需要的空间才是最好的策略。

    BLOB和TEXT
    BLOB和TEXT是为了存储很大的数据而设计的字符串数据类型,分别使用二进制和字符形式进行存储。实际上他们是不用的数据类型,字符类型是TINYTEXT SMALLTEXT TEXT MEDIUMTEXT LONGTEXT,对应的二进制类型是 TINYBLOB SMALLBLOB BLOB MEDIUMBLOB LONG。
    与其它类型不同mysql将每个BLOB和TEXT都当成一个独立的对象处理,存储引擎会对他们进行特殊处理。如果BLOB或TEXT太大,InnoDB会使用”外部的”存储空间进行存储,这时候会每个对象在行内存储一个1-4个字节的指针,然后再外部区域存储实际的值。BLOB与TEXT不同的地方仅仅在于BLOB存储的是二进制数据,没有字符集和排序规则,而TEXT有字符集和排序规则。
    mysql对BLOB和TEXT的排序和对其它类型的排序是不同的,它只对每个列的前max_sort_length字节进行排序,MYSQL也不能对BLOB和TEXT列的全部长度建立索引,并用索引来消除排序。

    使用枚举(ENUM)代替字符串类型
    可以使用枚举来代替一些常用的字符串类型。mysql可以将一组不重复的字符串存储成一个预定义的集合,mysql在存储枚举时非常紧凑,会根据列表的数量压缩到一个或两个字节中去。mysql在内部会将每个枚举列的值保持为整数,并且在.frm文件中保存”数字-字符串”的查找表。下面建立一个仅包含枚举值的表,并向其中插入三个值。

    create table enum_test( e ENUM('fish','apple','dog') not null );
    insert into enum_test values('fish','dog','apple');

    通过下面的语句可以看到列里面存储的是个整数的值:

    select e+0 from enum_test

    查询结果如下:
    这里写图片描述
    枚举类型还有个问题就是默认的排序会按列里存储的整数而不是其对应的字符串进行:

    select e from enum_test order by e

    这里写图片描述

    解决这个问题的方式是按照需要的序列来定义枚举的序列,比如上面可以创建表的时候就定义成 e ENUM(‘apple’,’dog’,’fish’)这种形式,那么整数的顺序和字符串的顺序就相同了。另一种方式是需要排序的时候,显式的使用FIELD函数指定顺序,如下所示,但这里的问题是会导致不能使用索引消除排序操作。

    select * from enum_test order by  FIELD(e,'apple','dog','fish');

    枚举最大的不好是字符串集合是固定的,如果想要改变字符串集合,删除或增加字符串都需要进行ALTER TABLE操作。
    枚举的显而易见的好处是可以明显的缩小表的大小,毕竟枚举列只需要存储一个整数值了。

    日期和时间
    mysql可以用很多类型来表示日期和时间,例如YEAR和DATE。mysql能存储的最小时间粒度为秒,但是可以用微秒粒度进行临时计算。大多数时间类型都没有什么替代品,所以唯一的问题是就是保存的时候需要怎么做。mysql提供了两种相似的日期类型:DATETIME和TIMESTAMP,对于很多场景两种都是适用的,但在一些情况下其中一个会工作的更好些。
    DATETIME
    这个能保存的时间范围是1001-9999,最小的精度是秒;它把日期和时间封装成YYYYMMDDHHMMSS的整数中,与时区无关,使用8个字节来存储。默认情况下mysql会以一种无歧义,可排序的格式显示DATETIME的值,例如”2018-01-01 12:00:00”,这是ANSI标准定义日期和时间的格式。
    TIMESTAMP
    这个类型保存了1970年1月1日午夜(格林威治标准时间)以来的秒数,使用4个字节进行存储,能够表示的最大时间是2038年。mysql提供了FROM_UNIXTIME()函数来将unix时间戳转换成日期,UNIX_TIMESTAMP()来将日期转换成时间戳。TIMESTAMP展示的值依赖于时区,而mysql服务器 客户端和操作系统都有时区设置,所以如何跨时区访问日期,那TIMESTAMP和DATETIME表现将不一样。
    TIMESTAMP还有一些DATETIME没有的特殊属性,如果在插入的时候没有指定第一个TIMESTAMP的值,那么mysql将会自动设置这个列的值为当前时间。在更新的时候,如果没有在update语句中指定相应的值,那也会自动更新第一个TIMESTAMP的值。插入和更新时候TIMESTAMP的行为是可以配置的,还有一点比较特殊的是TIMESTAMP默认是not null,这和其它类型是不一样的。
    如果一个表中有两个timestamp字段,那么就一定需要给第二个字段指定默认值,否则创建的时候就会报错。

    create table timestamp_test ( utime TIMESTAMP, ctime TIMESTAMP default CURRENT_TIMESTAMP);

    插入和更新timestamp时候都需要用标准展示格式,即’2014-12-08 12:08:02’这种形式,而不能传入一个unix_timestamp()函数中拿出来的整数。可以调用now()函数或表示当前时间的current_timestamp值。
    除了特殊情况,通常尽量应该使用TIMESTAMP因为它的存储空间效率比较高,并且也不要用整型来存储时间戳,因为不好进行处理。mysql默认最小精度是秒,但如果确实要存储毫秒或微秒等更小的时间,可以使用BIGINT来进行存储或者使用double来存储秒之后的部分。

    特殊的数据类型
    某些类型的数据并不直接与内置数据类型一致,低于秒级精度的时间戳就是一个例子;另外一个例子是一个IPV4地址,人们经常使用VARCHAR(15)来保存IP地址,然而它实际上是一个32位无符号的整数,用小数点分隔的字符串形式只是为了阅读方便,所以应该使用无符号整数存储IP地址,Mysql提供INET_ATON()和INET_NTOA函数在两种表现方法直接转换。

    2.mysql数据设计中一些陷阱
    上面讨论的都是通用性问题,但是一些问题是因为mysql的实现机制导致的,所以有必要特别拿出来讨论下。这里讨论的就是在mysql下设计数据库的一些问题。
    1) 太多的列
    mysql的存储引擎API工作时需要在服务层和存储引擎之间通过行缓冲格式拷贝数据,然后在服务层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。MYISAM的定长行结构正好与服务层的行结构吻合所以不需要转换,然后MYISAM的变长行结构和InnoDB的行结构则总是需要转换。而转换的代价依赖于列的数量,如果列的数量过多(几百甚至几千个字段),则性能会受到影响。
    2) 太多的关联
    所谓”实体-属性-值”(EAV)设置模式是一个常见的糟糕设计模式,尤其是在mysql下不能靠谱的工作。mysql限制了每个关联操作最多只能有61张表,但是很多使用EAV数据库需要许多自关联,所以很容易超过这个限制。实际上就算在关联表数少于61的情况下,解析和优化查询的代价也会成为mysql的性能问题。一般来说,如果希望查询执行的快且并发性好,单个查询最好在12个表内做关联。
    3) 全能的枚举
    避免枚举的滥用,主要考虑不要考虑使用枚举作为外键,整数才是最适合做外键的;另外,为枚举集合增加一个值属于alter table操作。在mysql 5.0之前,alter table是一种阻塞的操作;即使是在mysql 5.1之后,如果不是在集合的末尾增加一个值,也会是阻塞操作。
    4) null
    之前已经强调过不用null的好处(主要针对列可能被索引的情况),并且建议尽可能使用替代方案。比如说想要存储一个空值,也可以使用0 空字符等表示。但是如果有时候确实存在未知的值,还是可以使用null的。比如下面这种情况:

    create table .....(
    dt DATETIME NOTNULL DEFAULT '0000-00-00 00:00:00'
    )

    这种伪造的全0默认值可能会存在很多问题,还不如使用null来的好。

    展开全文
  • Schema与数据类型优化
  • 摘要: 读《高性能MySQL》之数据类型优化
  • 1.慢查询当一个资源变得效率低下的时候,应该了解一下为什么会这样。有如下可能原因:1.资源被过度使用,余量已经不足以正常工作。2.资源没有被正确配置3.资源已经损坏或者失灵因为慢查询,太多查询的实践过长而导致...
  • MySQL优化面试准备

    2018-11-28 21:42:18
    想了很久要不要发一篇关于MySql优化的文章最后还是决定把它写出来。...MaySql的数据类型优化与查询性能优化 MySql的特点与存储引擎 MySql的总体架构图 用户执行Mysql的流程图: 上层的一些服务...
  • 我们在使用Elasticsearch不免会遇到像int、double这种数值类型,Elasticsearch本身也是支持这些类型的,但并不意味着数字就一定要用数值类型,恰恰相反,用keyword有时候性能会更好,包括对数值进行range。...
  • 您可以到这里下载本篇博文所使用的数据库以及优化工具mysqldumpslow和pt-query-digest: ... 关于explain语句的结果参数...数据类型的选择,重点在于合适二字,如何确定选择的数据类型是否合适? 请记住以下...
  • Hive支持关系型数据库中的大多数基本数据类型,同时支持关系型数据库中很少出现的3种集合数据类型,下面我们将简短地介绍一下这样做的原因。    其中一个需要考虑的因素就是这些数据类型是如何在文本文件中进行...
  • 要了解一个数据库,我们也必须了解其支持的数据类型。 MySQL支持所有标准的SQL数据类型,主要分3类: ·数值类型 ·字符串类型 ·时间日期类型 另一类是几何数据类型,用的不多,也没多介绍。
  • mysql字段类型优化

    2016-09-20 17:08:32
    ①存储人的年龄应该使用哪种数据类型?答:使用tinyint类型(无符号型) ②存储乌龟的年龄应该使用哪种数据类型?答:使用smallinit类型(无符号型) ③存储一个1500万数据的数据表其主键id应该选择哪种数据类型?...
  • 概述 Redis作为一个分布式缓存实现,相对于Memecache,除了支持持久化之外,一个重要的特性是...Redis的每种数据类型都支持多种底层数据结构实现,即每种数据类型并不是绑定为一种数据结构的,而是可以多种。这...
  • python数据类型总结

    2020-04-03 17:54:42
    一、python中数据类型分类思维导图 二、值得注意的是: 1、Python中的变量都是指针,因此是没有类型限制的,且指针的内存空间大小是与类型无关的,其内存空间只是保存了所指向数据的内存地址。 2、值类型是不可变的...
  • 1、为表中的字段选择合适的数据类型当一个列可以选择多种数据类型时,应该优先考虑数字类型,其次是日期或二进制类型,最后是字符类型。对于相同级别的数据类型,应该优先选择占用空间小的数据类型。2、如何选择正确...
  • Hive支持关系型数据库中的大多数基本数据类型,同时也支持关系型数据库中很少出现的3种集合数据类型, 其中一个需要考虑的因素就是这些数据类型是如何在文本文件中进行表示的,同时还要考虑文本存储中为了解决各种...
  • 栈里创建的变量要比在堆创建的速度快得多== 可以比较基本数据类型 , 也可以比较引用数据类型 equals: 只能比较引用数据类型, 默认比较的是地址值*(string类中重写了eqals方法),如果我们想建立自己的比较方式, 需要...
  • 基本数据类型

    2018-11-18 00:26:54
    定义一个数据类型需要注意: 1)类型说明符 2)每种类型在计算机内存中占得字节 3)数据范围 4)表示方法 5)每种数据自身的运算方法 基本类型:构造简单,由系统实现定义 构造类型:复杂数据类型,一般由程序员将...
1 2 3 4 5 ... 20
收藏数 740,159
精华内容 296,063
关键字:

数据类型优化