阅读《高性能mysql第三版》笔记（三）

阅读《高性能mysql第三版》笔记（三）

前言：全文笔记都是阅读书籍记录的，请各位大佬多多指教，有不对的地方指点一番。谢谢！

（1）Sechema和数据类型优化

良好的设计和物理设计是高性能的基石。以下几种原则选择：
* 更小的类型通常更好，占用的磁盘，内存和CPU都更少；
* 简单数据类型更好，简单数据类型操作通常更少CPU周期；
* 尽量避免null。通常情况下最好指定列not null；因为列值可以为null，使得索引更复杂。但是并不是一定要设置为not null，提升性能很小，我们应该对需要建立索引的列设置为not null；

（2）数据类型优化

2.1 整数类型

有tinayint,smallint,mediumint,int,bigint,分别使用8，16，24，32,64为存储空间。存储的范围值为 -2^(N-1) 到 2^(N-1)-1,其中N为存储空间的位数。
整数类型可选unsigned属性，表示不允许为负值，这大致可以使得正数的上限提高一倍。例如：tinyint unsigned的范围是0-255。

2.2 实数类型

实数是带有小数点的数。MYSQL可以有精度类型和非精度类型。MYSQL4.1之前的版本不建议使用decimal，会发生精度丢失现象。对小数进行精确计算时，才能使用decimal，例如金钱计算，财务相关业务。

2.3 字符串类型

varchar和char类型

varchar类型用于存储可变长字符串，是常见的字符串数据类型。varchar需要使用1或者2个额外的字节记录字符串的长度，和字符集有关。varchar节省了存储空间，虽然对性能有帮助，但是update时可能使得行变得比原来的长，需要做额外的工作，MyISAM拆成不同片段存储，InnoDB需要分裂页使得可以放进页内。合适使用varchar的情况：
（1）字符串列的最大长度比平均长度大很多；（2）列更新的少，所以碎片不是问题；（3）使用了向UTF-8这样的复杂字符集。等等
varchar（5）和varchar（200）存储同一个字符串空间开销是一样的，但是更长的列消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。
char类型是定长的；MySQL总是根据定义的字符串长度分配空间；char适合存储较短的字符串，或者所有制都接近同一个长度。比如适合存储MD5的密码值，枚举值等等；定长的char不容易产生碎片，对较短的数据，char比varchar更有效率。《注意》由于存储引擎的关系，char类型存储是会把字符串末尾的空格去掉，但是varchar不会。
与char和varchar类似的有binary和varbinary，他们存储的是二进制字符串，存储的是字节码不是字符，mysql填充binary采用\0（零字节）而不是空格，在检索是也不会去掉。

blob和text类型

BLOB和TEXT都是存储很大的数据设计的字符串类型，分别采用二进制和字符串存储；

MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。

枚举（ENUM）代替字符串类型

create table enum_test（e ENUM（‘cat’,‘apple’,‘dog’））;
在实际存储时，数据存储为整数，而不是字符串。可以通过在数字上下文环境检索看到双重属性。（SELECT e + 0 FROM enum_test）如果使用数字作为ENUM枚举常量，这种双重性很容易导致混乱。转换为ENUM以后，QPS会提高。

日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，例如YEAR和DATE。
DATETIME
这个类型保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节存储空间。默认情况下，是可以排序的。
TIMESTAMP
TIMETAMP类型保存了从1970年1月1日午夜以来的秒数，和unix时间戳相同。它使用4个字节的存储空间，因此它的范围比DATETIME小很多：只能表示从1970年到2038年。TIMESTAP显示的值依赖时区。timestamp的效率比datatime高很多，但是由于区间小，不适于作为年龄字段。
有一些特殊的比秒更小粒度的日期和时间怎么办？MySQL目前没有提供合适的数据类型，但是可以使用自己的存储格式，可以使用BIGINT类型存储微妙级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。

位数据类型

MySQL有少数几种存储类型使用紧凑的位存储数据。所有这些位类型，不管层存储格式和处理方式如何，从技术上来说都是字符串类型。
bit

set

选择标识符 identifier

为标识列选择合适的数据类型很重要。实际开发中，通常使用整数类型的id，并使用AUTO_INCREAMENT自增长。
整数类型
整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREAMENT。
字符串类型
如果可能，应该避免使用字符串类型作为标识列，因为他们很消耗空间，并且通常比数字类型慢。尤其是在MyISAM表里使用字符串作为标识列时要特别小心。MyISAM默认对字符串使用压缩索引，这会导致查询慢很多。在我们的测试中，我们注意到最多有6倍的性能下降。在MyISAM默认对字符串使用压缩索引，导致查询很慢。

特殊类型数据

有一个IPv4的例子，使用VARCHAR(15)列存储IP地址。然而，它实际上是32位无符号整数，而不是字符串。用小数点将地址分为四段表示方法只是为了人们阅读。所以应该用无符号整数存储IP地址。Mysql提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。

（3）MySQL 表设计陷阱

太多的列

MySQL存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，代价比较高。定长结构不需要转换，但是边长需要转换的，转换代价取决于列的数量和长度。

太多的关联

MySQL限制了每个关联操作最多只能61张表，建议单个查询最好在12表内做关联，这是原文的话。

全能的枚举和变相枚举

枚举太多会导致设计非常凌乱，一般会设计成一个字典表和字典数据表。

（4）范式和反范式

经常被建议对Scama进行范式化设计，尤其是写密集的场景。范式化的Schema缺点是通常需要关联，稍微复杂一些的查询语句在符合范式的表上都可能需要至少一次关联，也许更多。反范式化的表因为所有数据都在一张表中，可以避免关联。一般情况下我们都是混用的，对于昂贵的笛卡尔积关联查询有时候可以反范式化。原文中有对范式化和反范式进行优缺点区分，可以阅读原文。

（5）计数器表

如果应用在表中保存计数器，则在更新计数器可能碰到并发问题，计数器表在Web中经常使用，比如存储pv量，或者一个持续增长的id值。
例如需求要取值持续增长的id值，解决思路有：
（1）使用乐观锁修改值
（2）首先id是1~100，增加100行数据，修改时候随机取一条修改，update hit_counter set number = number+1 where id= floor(1+（RAND() * 100）),然后统计结果.测试过程中这方案可行的。
还有一个需求是每隔一段时间开始一个新的计数器（例如，每天一个）。使用 on duplicate key update,理解该关键字链接https://www.jianshu.com/p/78ea17c6d190，以时间作为主键，判断是否存在今天日期，存在则更新，不存在则插入，并且还是原子操作。表的行数渐渐变大，可以周期删除。

加快alter table操作的操作速度

当数据量大时，大部分的alter table会导致系统中断。一下是原文的两种操作。