《高性能mysql》读书笔记(第四章)

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计schema。

一、选择优化的数据类型

    选择正确的数据类型对于获得高性能至关重要。有以下几个原则：

     a.更小的通常会更好

        占用比较少的磁盘、内存和cpu缓存，处理时需要的cpu周期更少。

     b.简单就好

        需要更少的CPU周期。

     c.尽量避免NULL

       通常情况下，最好设置列为NOT NULL。如果查询中包含可为NULL的列，更难优化，因为可为NULL的列使得索引，索引统计和值比较非常复杂，可会使用更多的存储空间，当可谓NULL的列被索引时，每个索引记录需要一个额外的字节。但是把可为NULL的列改成NOT NULL带来的性能提升也比较小，但如果计划在列上创建索引，就应该避免设计成可为NULL的列。

1.1整数类型

有两种类型：整数和实数。

整数类型 存储空间 范围

TINYINT 8位 -2^7~2^7-1

SMALLINT 16位 -2^15~2^15-1

MEDIUMINT 24位 -2^23~2^23-1

INT 32位 -2^31~2^31-1

BIGINT 64位 -2^63~2^63-1

整数可选的属性

       UNSIGNED 不允许出现负值

       SIGNED 允许出现负值

   这两种类型使用相同的存储空间，相同的性能。mysql可以为整数烈性指定宽度，例如INT(12)

,但是注意，此种设置对大多数应用是无意义的，不限制值的合法范围，知识规定了MySQL的交互工具用来显示字符的个数(可以理解为次数必须填写一个数值，其实不限制整型的长度)；

结论：int(1)和int(10)没有任何区别。

1.2实数类型

     分类： 浮点类型和DECIMAL

     带有小数的部分，也可使用DECIMAL存储较大的整数。

      DECIMAL 类型适用于小数的精度运算，占用空间较大，例如：DECIMAL(18,9)小数点左右个存储9个数字，那么将要占用9个字节，因为DECIMAL每四个存9个数字，小数点站一个字节

      浮点类型在相同的数值时占用空间更小，FLOAT使用4个字节，DOUBLE使用8个字节

结论：需要高精度运算且数值较大，推荐使用BIGINT类型，即将小数乘10000等转换成整数来避免存储浮点存储计算不精确和DECIMAL计算代价高昂的问题。

1.3字符串类型

     支出很多数据类型，在mysql4.1和5.0版本发生了很大的变化，4.1开始，每隔字符串列可以自定义自己的字符集和排序规则或者说校对规则，这些很影响性能。

[1] varchar和char类型

      数据在磁盘和内存中的存储方式与使用引擎有关系。

       varchar类型存储可变长字符串，更节省空间，除非建表使用了ROW_FORMAT=FIXED，那么每一行都是用定长存储，浪费空间。varchar使用额外的字节来记录字符串的长度，列长度小于等于255个字节，则使用一个字节记录，超出使用两个字节记录。eg:使用Latin1字符集，一个varchar(10)的列需要11个字节的空间。

      varchar类型节省了存储空间，但是更新后可能使行变长，不同的存储引擎处理方式不同，MYISAM会将行拆分成不同的片段存储，INNODB会分裂页使行可以放进业内。

      char是定长存储。mysql根据定义长度直接分配足够的空间。适合存储短的字符串。不易产生碎片，存储空间也更有效率。eg:只需要存储 Y和N的时候，char占用一个字节，varchar占用两个字节。

[2]BLOB和TEXT类型

    存储很大的数据的字符串数据类型，分别采用二进制和字符方式存储。

   两种不同的数据类型家族：

    TINYTEXT,SAMLLTEXT，TEXT,MEDIUMTEXT，LONGTEXT

    TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB，LONGBLOB

mysql将每个BLOB或TEXT当做一个独立的对象处理。数据过大会有专门的外部存储区域来处理，此时每个值在行内需要1~4个字节存储一个指针，然后外部存储实际的值。

区别：

BLOB：二进制存储，没有排序规则和字符集。

TEXT：字符存储，有排序规则和字符集。

[3]使用枚举代替字符串类型

1.4日期和时间类型

mysql可存储的最小时间粒度是秒。

    日期类型：

    datetime 保存范围更大。从1001年到9999年。将日期和时间封装成个YYYYMMDDHHMMSS的整数中，与时区无关。

   timestamp 保存了从1970年1月1日午夜以来的秒数，和UNIX时间戳相同，只使用4个字节的存储空间，范围小只能表示1970到2038年

    函数FROM_UNIXTIME()把Unix时间戳转为日期，并提供了UNIX_TIMESTAMP()函数将日期转为Unix时间戳。

timestamp依赖时区。使用的时候优先选择timestamp，空间效率更高。

1.5位数据类型

     技术上来说都是字符串类型。

      BIT 5.0之前BIT和TINYINT是同义词。5.0版本后就不一样了，BIT(1)存储一个位，最大列的长度是64个位。

     谨慎使用此数据类型。

SET 在MySQL内部以一系列打包的位的集合来表示的。有效利用空间，有FIND_IN_SET()和FIELD()用于查询。缺点是改变列的定义代价高。需要ALTER TABLE。

1.6 选择标识符

      尽量避免使用字符串作为标识列，消耗空间，通常还比数字类型慢。对于随机的字符串也要注意，向UUID(),MD5()或SHA1()产生的字符串。会任意分布很大的空间导致INSERT或一些SELECT语句很慢。

1.7特殊数据类型

    某些类型的数据不予内置的类型一致，例如低于秒级精度的时间戳。