高性能MySQL优化要点整合

本篇文章为《高性能MySQL》一书的读书笔记，主要提取了几点在开发中会用到的高性能的做法与一些MySQL的介绍

1. MySQL架构

MySQL最重要、最与众不同的特性是它的存储引擎架构。这种架构的设计将查询处理及其他系统任务和数据的存储/提取相分离。此时可以根据性能、特性以及其他需求来定制化数据存储的方式（存储引擎）

1.1 MySQL逻辑架构

服务器逻辑架构图，截取自《高性能MySQL》

• 中间那层架构为MySQL核心部分，负责查询解析、分析、优化、缓存以及内置函数（日期、时间、数学等），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等
• 最下面一层存储引擎层负责数据的存储和提取，每个存储引擎都有各自的优势和劣势。服务器通过API与存储引擎进行通信。存储引擎API包含了几十个底层函数，用于执行诸如“开始一个事务”等操作。存储引擎不会去解析SQL，只是简单的响应上层服务器的请求

2. Schema与数据类型优化

应该根据系统将要执行的查询语句来设计schema，需要权衡各种因素。例如反泛式的设计可以加快某些查询，但可能使另一个查询变慢。

2.1 数据类型的选择

2.1.1 整数类型

INT(1) 和 INT(20) 在存储与计算上是相同的，只不过其规定了显示字符的个数

存储整数，有如下类型（包括类型所占的存储空间，间接代表了其值的范围）

• TINYINT（8 bit）
• SMALLINT（16 bit）
• MEDIUMINT（24 bit）
• INT（32 bit）
• BIGINT（64 bit）

整数类型有可选的UNSIGNED属性，表示无符号，如无符号则存储的值范围扩大一倍（乘2）

2.1.2 字符串类型

VARCHAR

用于存储可变长字符串，需要多消耗1到2的额外字节来存储字符串（记录长度），节省了存储空间，对性能有帮助，但是如果UPDATE使得行变得比之前更长，InnoDB需要分裂页来使行可以放入页内，造成一定的碎片化

下面情况使用VARCHAR是合适的：

• 字符串列的最大长度比平均长度大很多，充分发挥了变长字符串的节省能力
• 列的更新很少，这样碎片化就少了

CHAR

MySQL总是根据定义的字符串长度分配空间。对于固定的列，存储空间也比VARCHAR小1到2个字节（记录长度）

下面情况使用CHAR是合适的：

• MD5值，固定的长度，更少的存储空间
• 很短的字符串，或者所有值都接近同一个长度
• 经常变更的值，不易产生碎片

VARCHAR(5) 和 VARCHAR(200) 存储的空间开销是一样的，但更长的列会消耗更多的内存（或磁盘），体现在使用内存临时表（或磁盘临时表）进行排序或操作时。

BLOB和TEXT

为存储很大的数据而设计的字符串数据类型

• MySQL把这两个当作一个独立的对象处理，如果太大，InnoDB会使用专门的外部存储区域来进行存储，此时每个值在行内就存储1-4个字节的指针
• 两者的不同是BLOB存储的是二进制数据，没有排序规则和字符集，而TEXT有
• 不能将这两列全部长度进行索引，进而没有覆盖索引和索引消除的排序

使用枚举代替字符串类型

枚举列把一些不重复的字符串存储成一个预定义的集合，MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一到两个字节中。

• MySQL在内部会将每个值在列表中的位置保存为整数，并且在.frm文件中保存数字-字符串的映射关系（查找表），所以在查找时有一些开销，在其与CHAR/VARCHAR列关联时体现，所以有与字符串类型做关联时最好使用字符串（或者枚举与枚举进行关联，会更加的快）
  • 有时候枚举会大大减少表的大小，所以有时候关联的开销也是值得的
• 缺点是字符串列表是固定的，添加元素只能在列表末尾添加

2.1.3 日期和时间类型

DATATIME

保存大范围的值，从1001年到9999年，精度为秒，格式为YYYYMMDDHHMMSS的整数，使用8个字节的存储空间

TIMESTAMP

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

2.1.4 位数据类型

少数几种存储类型使用紧凑的位存储数据，底层来讲都是字符串类型

BIT

在InnoDB中，为每个BIT列使用一个足够存储的最小整数类型来存放，所以其在InnoDB中不能节省空间，并且结果有时可能令人费解（存储的二进制查找出来的是字符码对应的字符串，例如57的二进制，查找出来会变为"9"），应该谨慎使用BIT类型，如果存储一个true/false值，可以使用CHAR类型，节省了空间又不会令人费解

SET

通过一系列打包的位集合，可以保存很多个true/false的值，有效利用存储空间，例如一条记录里有一列权限，其中有是否可读，是否可写，是否可删除，一系列的是否，通过指定位的0/1来达到

2.1.5 选择标识符

标识符即为一行数据中唯一标识此行的列，需要确保所有关联表中的此标识符都使用同样的类型，包括UNSIGNED这样的属性

• 整数类型
  • 通常是标识列的最好选择，它们很快并且可以使用AUTO_INCREMENT
• ENUM和SET
  • 糟糕的选择，此类型是固化的视图，如果确定表主键只有固定那么几个的话，也可以选择
• 字符串类型
  • 应该避免使用字符串类型作为标识列，它们很消耗空间，通常比数字类型慢
  • 多加注意“随机”的字符串，如MD5、SHA1、UUID等等
    • 插入时会随机写索引的不同位置，使得INSERT更慢，导致页分裂、磁盘随机访问
    • SELECT更慢，因为逻辑相邻的列分布在物理地址的不同位置

2.2 MySQL的schema设计

2.2.1 范式与反范式

范式化的优点：

• 更新操作比反范式快，没有冗余数据，只需要修改最少的数据
• 范式化的表通常更小，可以更好的放入内存，执行操作更快
• 几乎没有冗余数据代表着更少的DISTINCT或者GROUP BY语句

范式化的缺点：

• 查询时通常需要关联表，才能拿到一个完整需要的数据信息
• 有时候不同列在不同的表中，原本这些列只需要利用一次索引就可以完成查询

反范式化的优点：

• 所有数据都在一张表中，避免了关联操作，如果不需要关联表，最差情况下（全表扫描），当数据量比内存大时可能比关联快得多，因为是顺序IO
• 在一些查询中，使用一个索引就能得到所有的列，有些查询得益于此变得比关联快得多

反范式化的缺点：

• 列数据冗余存储，更新数据时可能需要更新多表，更新变慢变复杂
• 行信息不明确，例如用户表与用户发送的消息表合并在一起，通常是很糟糕的

2.2.2 混用范式化与反范式化

• 没有绝对的范式和反范式
• 有时候适当冗余某个列（反范式）ÿ