【高性能mysql】-字段类型的选择

1. 优化数据类型

    1.0 几个简单的原则

             1.更小的通常更好：尽量选择可以正确存储数据的最小数据类型。如int-->tinyint, 

             2.简单就好:使用更少耗费cpu的类型。如整形比字符串操作代价更低；使用mysql内建的时间/日期类型而不是字符串存储时间；使用整数存储ip地址等等

           3.  尽量避免 null 。如果查询带有null的列，对mysql来说更难优化。null使索引、索引统计、值比较都更加复杂。

  

1.1 整数类型

      1尽量确定整数的范围并使用对应的整形，tinyint/int/smallint/bigint等。

      2 合理利用unsigned  ：如果只有正整数，不妨在之后加上unsigned 修饰

     3.  虽然int可以指定长度。如int（11），但这只是规定了显示的字符个数，实际上存储和计算的仍是int类型的数值。比如int(1)和int(20)只是显示的值不同。

1.2 实数类型

  1 使用decimal 可以用来存储比bigint还大的数据。

 2  使用bigint 来避免浮点型计算不精确以及decimal 精确计算代价高的问题。例如，存储财务数据，单位为万元时，存在小数的情况。可以直接使用单位为元或者/角/分的更小单位的bigint数值来替代。

1.3  字符串类型

先讲char和varchar 

varchar 用来存储不定长的字符串，它比定长类型更省空间（除非使用row_format=fixed创建，每一行都会使用定长字符串）。

varchar需要使用额外1或2字节存储字符长度。如果长度<=255,则使用1字节，否则使用2字节。

varchar由于更新变长时，myisam和innodb的处理方式不同。myisam会将行拆分成不同的片段存储，innodb则进行分裂页来使行可以放入页内。innodb还会将过长的varchar转换为blob

 

虽然VARCHAR数据类型是根据实际的需要来分配长度的，但我们仍然要始终坚持使用最小可用长度的标准。例如A股股票代码，

显然varchar(6)就足够，如果有数据记录的长度大于6，那这条数据明显是脏数据。

 虽然它们用来存储90个字符的数据，其存储空间相同，但是对于内存的消耗是不同的。更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值，尤其是使用内存临时表进行排列或者操作时会特别糟糕。所以我们在分配VARCHAR数据类型时仍然不能够太过于慷慨。还是要评估实际需要的长度，然后选择一个最长的字段来设置字符长度。如果为了考虑冗余，可以留10%左右的字符长度。千万不能认为VARCHAR是根据实际长度来分配存储空间，而随意的分配长度，或者说干脆使用最大的字符长度。

 

   适合varchar 的场景:

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

               字符串列更新少；

               使用了utf-8等复杂的字符集。

blob和text 

blob 存储二进制，text存储文本。

对blob和text进行排序时，只会对每列的前max_sort_length 字节进行排序。如果非要对其进行排序可以更改此变量值，或者使用 ` order by  sustring（coulmn，length）`。

 

1.4 日期和时间类型

    datetime和timestamp都可以存储相同类型的数据：时间和日期（精确到秒）。但是timestamp只花费前者一半的存储空间。但是其允许的时间范围稍小。

 

1.5 BIT 类型 

在mysql5.0 之前，bit相当于tinyint。在之后，bit在不同的存储引擎上表现不同，在myisam上 bit就是使用对应长度字节存储，而innodb和memorydb则使用足够存储bit位数的最小整数代替，无法减小开销。

对于bit ，更奇怪的是它在不同上下文场景下的表现。在上文是字符串的场景下，查询出的bit字段的 结果是一个 bit字段对应二进制值的ascii码字符。但是如果在数字上下文，查询出来的结果就会是数字。

例如

create table bittest (a bit(8));
insert into bittest a values(b'00111001');
select a ,a+0 from bittest;
最终会得出以下结果（字符9的ascii码为57）
|---------------|
| a     |a +0   |
-----------------
|  9    |  57   |

 

应尽量避免使用BIT。

 

1.6   主键  id 字段的 选用

   1.  数字 始终是最适合id的类型，因为他们足够快

  2.  字符串  

      避免字符串作为id标识符，因为它们比数字开销更大，且更慢。尤其是在myisam中，myisam默认会对字符串使用压缩索引，这会导致查询慢很多。

    对于完全随机的字符串，如uuid等，会任意分布在很大的空间内，使得insert以及一些select  变得更慢。

（优点在写的特别大的表可以消除热点）

如果存储uuid ，应该去除中间的 ‘-’ 字符。

一方面是由于主键字段过长，二是由于页分裂和碎片导致的。在innoDB中，会根据主键去做聚簇索引，把索引和对应的数据行存储的在叶子页上，使用uuid写入是无序的，InnoDB为了数据的写入，只能不断频繁的进行页分裂操作，以便为新的行分配空间。 由于频繁的页分裂，会导致页变得稀疏被不规则的填充，所以最终导致产生了大量的数据碎片。

uuid适合数据量庞大、分布式等特点的数据库设计。

 

 

  mysql 限制每个关联操作最多61个表