空桑

Appearance

深入浅出 MySQL 读书笔记 ​

开发篇 ​

第 6 章 表类型（存储引擎）的选择 ​

6.1 MySQL 存储引擎概述 ​

插件式存储引擎是 MySQL 数据库的特性之一。在 5.5 版本前，默认引擎是 MySIAM、5.5 后是 InnoDB。

sql
show engines; -- 查看支持的引擎

表引擎可以后期切换

6.2 各种存储引擎的特性 ​

6.2.1 MyISAM ​

不支持事务和外键、在 5.5 版本以前在某些场景的访问速度比 InnoDB 快

支持三种存储格式

  • 静态表：默认格式，每个记录都是固定长度。存储时会按照列宽补齐空格，但是在访问的时候会去掉尾部的所有空格
  • 动态表：字段是变长的，频繁更新和删除会产生碎片
  • 压缩表：使用 myisampack 创建，非常小
6.2.2 InnoDB ​

有提交、回滚、崩溃恢复能力的事物安全保障，提供了更小的锁粒度和更强的并发能力

占用的磁盘空间比 MyISAM 更多，但是一般场景都更好

不同于其他引擎的特点

  1. 自动增长列：可以手动插入和通过语句更改默认值
  2. 外键约束：父表必须有对应的所有，子表在创建外键时也自动创建对应的索引，被创建外键后该表的对应索引或主键禁止被删除。可以手动关闭外键检查
  3. 主键和索引：InnoDB 数据文件以聚簇索引形式保存，所有 InnoDB 表必须包含主键。未显示指定主键则 InnoDB 引擎自动创建六个字节的 long 隐藏类型作为主键
  4. 存储方式：有共享表空间存储和多表空间存储两种方式。一般默认使用多表存储空间以避免随数据的增长带来的性能下降
6.2.3 MEMORY ​

MEMORY 存储引擎使用存在于内存中的内容来创建表。得益于数据放在内存中且默认使用 HASH 索引，MEMORY 表的访问速度非常快。但是一旦内存关闭，表中的数据就会丢失

MEMORY 引擎主要用于那些变化不频繁的代码表，或作为统计操作的中间存储表。对 MEMORY 表的更新需要谨慎，因为数据并没有实际写入磁盘中。需要考虑如何获取这些修改后的数据

6.2.4 MERGE ​

MERGE 也称 MRG_MyISAM，是一组 MyISAM 表的组合。这些 MyISAM 表必须结构完全相同，MERGE 表本身没有数据，操作实际上是对内部的 MyISAM 表进行的。对 MERGE 类型表的插入是通过 INSERT_METHOD 字句定义插入的表，可以通过FIRST、LAST、NO等三个值进行定义

MERGE 表无法智能的写入记录，这是和分区表的区别

6.2.5 TokuDB ​

除了前面 MySQL 自带的存储引擎，还有一些第三方引擎。如列式存储引擎 Infobright 和 TokuDB

TokuDB 有如下的特性

  • 使用 Fractal 树索引保证高效的插入性能
  • 优秀的压缩特性，比 InnoDB 高了 10 倍
  • Hot Schema Changes 特性支持在线创建索引和添加、删除属性列等 DDL 操作
  • 使用了 Bulk Loader 达到快速加载大量数据
  • 提供了主从延迟消除技术
  • 支持 ACID 和 MVCC

因此适用于如下场景

  • 日志数据：插入频繁且存储量大
  • 历史数据：不再写入，因此可以利用高压特性进行存储
  • 在线 DDL 比较频繁的场景：增加系统的可用性

6.3 如何选择合适的存储引擎 ​

  • MyISAM：以读和插入为主，只有极少的更新和删除操作，对事物的完整性没有要求、没有并发写操作
  • InnoDB：对事物的完整性有较高的要求、并发的条件下要求数据的一致性。数据除了插入和查询外还有很多的更新、删除操作
  • MEMORY：将所有数据保存在 RAM 中，能提供极快的访问速度。对表的大小有限制、且数据库异常终止后数据会消失
  • MERGE：将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用他们。优点在于可以突破对单个 MyIASM 表大小的限制，将不同的表分布到多个磁盘

第 7 章 选择合适的数据类型 ​

7.1 CHAR 与 VARCHAR ​

CHAR 与 VARCHAR 类似，都可以用来存储字符串。但是保存和检索的方式不同

在严格模式下，超过列长度的值将不会保存，并且出现错误提示。数据位小于 255 时，VARCHAR 用一个字节记录字节长度；大于 255 时，用两个字节记录长度

值CHAR(4)存储需求VARCHAR(4)存储需求
''' '4 个字节’‘1 个字节
'ab''ab '4 个字节'ab'3 个字节
'abcd''abcd'4 个字节'abcd'5 个字节
'abcdefg''abcd'4 个字节'abcd'5 个字节

由于 CHAR 长度固定，因此处理速度比 VARCHAR 快，但是浪费存储空间。适合长度变化不大且有速度要求的数据

VARCHAR 需要按需定义长度，避免影响效率和触发 BUG

不同的存储引擎对 CHAR 和 VARCHAR 的使用原则有所不同

  • MyISAM 存储引擎：建议使用固定长度的数据行
  • MEMORY 存储引擎：目前都使用固定长度的数据行进行存储，两者均作为 CHAR 处理
  • InnoDB 存储引擎：建议使用 VARCHAR 类型，InnoDB 数据表的内部行存储没有区分固定长度和可变长度列。CHAR 的性能不一定比 VARCHAR 好，性能瓶颈还是数据行的存储总量。因此建议使用 VARCHAR

7.2 TEXT 与 BLOB ​

少量数据选择 text，大量数据选择 TEXT 和 BLOB。两者的差距在于 BLOB 能保存二进制数据，TEXT 只能保存字符

该字段会存在如下问题：

  1. TEXT 和 BLOB 会在删除时引起性能问题，删除后会留下空洞，导致填入空洞的记录在插入的性能会有影响。需要定期使用OPTIMIZE TABLE以提高性能
  2. 可以使用合成索引来提高文本字段内容的查询性能。如，使用散列算法生成散列值，精确查询时直接查询散列值以提高效率
  3. 在不必要的使用避免检索大型 TEXT 和 BLBO 值。SELECT *不是一个好想法，网络传输资源有限
  4. 把 BLOB 或 TEXT 列分离到单独的表里，能减少主表的碎片并显著减小主表的数据量从而获得性能优势

7.3 浮点数与定点数 ​

浮点数一般表示含有小数部分的数值，当一个字段被定义为浮点类型后，如果插入数据的精度超过该列实际定义的精度则会被四舍五入到实际定义的精度值

定点数实际上以字符串形式存放。所以定点数可以更精确的保存数据。在默认模式下，如果插入值精度大于实际精度，则会警告并四舍五入后插入

  • 浮点数存在误差问题
  • 对货币等对精度敏感的数据，应该使用定点数表示或存储
  • 在编程中如果要用到浮点数，要特别注意误差问题并避免做浮点数比较
  • 要注意浮点数中的一些特殊值的处理

第 8 章 字符集 ​

字符集概述 ​

字符集是一套文字符号及其编码、比较规则的集合。20 世纪 60 年代初期，ANSI 发布了 ASCII，后演变为 ISO-646

8.2 Unicode 简述 ​

为了统一字符编码，ISO 在 1984 发起国际字符集标准，即（Universal Multiple-Octet Character Set）简称 UCS

由于部分美国公司反对，因此美国成立了 Unicode 委员会。但由于编码复杂度，因此 Unicode 并入 ISO-10646 中

8.3 汉字及一些常见字符集 ​

  • GB 2312-80：《信息交换用汉字编码字符集 基本集》1980 年发布，收录了 6763 个常用汉字符号
  • GB 13000：《信息技术 通用多八位编码字符集（USC）第一部分：体系结构与基本多文种平面》1993 年发布，几乎没有得到业界的支持
  • GBK：《汉字内码扩展规范》1.0 版。发布于 1995 年，收录了 GB 13000.1-1993 的全部 2092 个 CJK 统一汉字
  • GB 18030：《信息技术信息交换用汉字编码字符集、基本集的扩充》2000 年发布，为了解决 GBK 强制标准里不够
字符集是否定长编码方式其他说明
ASCII是单字节 7 位编码奠基性字符集
ISO-8859-1/latin1是单字符 8 位编码西欧字符集，常被用于转码
GB2312-80是双字节编码早期标准
GBK是双字节编码常见支持
GB18030否2 字节或 4 字节编码数据库支持的不多
UTF-32是4 字节编码USC-4 原始编码
UCS-2是2 字节编码Windows2000 内部用 USC-2
UTF-16否2 字节或 4 字节编码JAVA 和 Windows XP/NT 内部使用 UtF-16
UTF-8否1~4 字节编码互联网和 Linux 广泛支持的 Unicode 字符集

8.4 怎样选择合适的字符集 ​

  1. 满足应用支持语言的需求
  2. 如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性
  3. 如果数据库只需要支持一般中文，数据量大、性能要求也很高，那就应该选择双字节定长编码的中文字符集。可减少计算开销和传输要求
  4. 如果数据库需要大量的字符运算，最好选择定长字符集，因此处理速度要比变长字符集的处理速度快
  5. 如果现有客户端都支持相同的字符集，那就有限选择该字符集以避转换字符集带来的性能开销和数据损失

8.5 MySQL 支持的字符集简介 ​

MySQL 支持多种字符集，甚至在于同一个表的不同字段都可以使用不同的字符集

排序规则命名约定：_ci（大小写不敏感）、_cs（大小写敏感）、_bin（二元、比较是基于字符编码的值而与 Language 无关）

第 9 章 索引的设计与使用 ​

9.1 索引概述 ​

所有 MYSQL 列类型都可以被索引，对相关列使用索引是提高 SELECT 操作的性能的最佳途径

9.2 设计索引的原则 ​

  • 在条件列上创建索引。最适合索引的列式出现在 WHERE 字句中的列或连接子句中指定的列
  • 尽量使用高区分度的索引。考虑列中值的分布。索引的列的基数越大，索引的效果越好。如果男女对半开的列，不论查询什么都会得出一半的值
  • 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。短索引能大量节省索引、涉及的磁盘 IO 较少，高速缓存中能容纳更多的值
  • 利用最左前缀。在创建一个 n 列的索引时实际上相当于创建了 MySQL 可利用的 n 个索引，多列索引可起几个索引的作用，因为了可利用索引中最左边的列集来匹配行。如，以 a、b、c 的顺序在 3 列上创建一个组合索引后，利用 a=?、a=?、&b=?、a=?&b=?&c=?这三种条件的查询都能利用这个索引
  • 对于 InnoDB 存储引擎的表，尽量手工指定主键。记录默认会按照一定顺序保存，如果有明确的主键、则按照主键顺序保存，如果没有主键又没有唯一索引，那么表内自动生成一个内部列。尽量自己指定主键，选择唯一、最常见、尽可能短的列来创建

9.3 索引设计的误区 ​

  • 不是所有的表都需要创建索引，数据量小的表除了主键外再创建索引没有太大的意义，索引扫描和全表扫描性能不会有大的区别。而达标的操作尽可能通过索引，任何全表扫描对大表来说都是非常大的冲击，因此每个操作都尽可能通过索引进行
  • 不要过度索引。每个额外的索引都需要占用磁盘空间并降低写入性能，在更新操作时索引也需要更新。此外，MySQL 在生成执行计划时，要考虑各个索引。因此，只保持所需的索引有利于查询优化
  • 谨慎创建地选择度索引。对于选择度低且数据分布均匀的列，因为过滤的结果集大，创建的索引效果通常不好。但是如果列的选择度低且数据分布不均衡，对于占比较小的数据项索引的查询效率就较高
  • 慎重使用唯一索引。虽然能满足业务上唯一性的要求并在特定查询的速度上有所增长，再是在 MySQL5.6 后，除主键外的唯一索引会导致 Change Buffer 无法被使用

9.4 索引设计的一般步骤 ​

  1. 整理表上所有的SQL，重点包括where条件所用到的列的组合、关联查询的关联条件等
  2. 整理所有查询SQL的预期执行频率
  3. 整理所有涉及的列的选择度、列不同值相比总非空行数的比例越大，选择度越好
  4. 遵循之前提到设计原则，给表选择合适的主键，没有特别合适的列时，建议选择自增列作为主键
  5. 优先给执行频率最高的SQL创建索引
  6. 按执行频率、依次检查是否需要为每个SQL创建索引，可以复用之前已经创建的索引，除非SQL执行的频率很高，新创建的索引对选择度的提升也很大
  7. 索引合并，利用复合索引来降低索引的总数，充分利用最左前缀原则，把选择度跟高的列放到索引的最左侧
  8. 上线之后通过慢查询分析、执行计划分析、索引使用统计来确定索引的实际使用情况

9.5 BTREE索引与HASH索引 ​

MEMORY存储引擎的表可以选择使用BTREE索引和HASH索引，两种不同的索引各有其不同的适用范围。HASH索引有一些重要的特征需要特别注意

  • 只能用于=或<=>的等式比较
  • 优化器不能使用HASH来加速ORDER BY操作
  • MySQL不能确定两个值之间有多少行
  • 只能用着整个关键字来搜索一行

而对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=、<>、LIKE 'pattern'(不以通配符开始)都可以使用相关列上的索引

9.6 索引在MySQL 8.0中的改进 ​

9.6.1 不可见索引 ​

可以通过alter table命令来修改索引是否可见，使索引无法被查询优化器选择。避存在使用该索引SQL导致大量的全表扫描，能先设置不可见观察系统性能再决定是否删除索引。避免大表重建索引消耗的时间和资源

9.6.2 倒序索引 ​

借鉴自Oracle

第10章 开发常用数据库对象 ​

10.1 视图 ​

10.1.1 什么是视图 ​

视图是一种虚拟存在的表，对于使用的用户来说是透明的。视图并不在数据库汇总实际存在，行列数据来自自定义视图的查询中的表，并且是在使用视图时童泰生成的

视图的优势如下

  • 简单：使用视图的用户不需要关系对应的表的金钩
  • 安全：使用视图的用户只能访问他们被允许的结果集，通过视图可以简单地实现限制到某个行某个列
  • 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，原表增加列对视图没有影响；原表修改列名也可以通过修改视图来解决
10.1.2 视图操作 ​

创建、修改、删除、查看定义

10.1.3 创建和修改视图 ​

视图的可更新性和视图中查询的定义有关系，以下类型的视图是不可更新的

  • 包含以下关键字的SQL语句：聚合函数(SUM、MIN、MAX、COUNT等)、DISTINCT、GROUP BY、HAVING、UNION或者UNION ALL
  • 常量视图
  • JOIN
  • FROM一个不能更新的视图
  • WHERE字句的子查询引用了FROM字句中的查询

WITH CHECK OPTION决定了是否允许更新数据使数据不再满足视图的条件

  • LOCAL只要满足本视图的条件就可以更新
  • CASCADED则必须满足所有针对该视图的所有视图的条件才可以更新

默认是CASCADED

10.1.4 删除视图 ​

删除视图必须拥有该视图的DROP权限

10.1.5 查看视图 ​

从5.1版本开始，使用SHOW TABLES命令可以查看视图和表

sql
SHOW TABLES; -- 查看表和视图
SHOW TABLES STATUS LIKE %%; -- 查看表和视图信息
SHOW CREATE VIEW； -- 查看视图定义

10.2 存储过程和函数 ​

10.2.1 什么是存储过程和函数 ​

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化开发人员的很多工作

存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型的

10.2.10 事件调度器 ​

5.1后新增的功能，可以将数据库按自定义的时间周期触发某种操作，类似crontab

sql
CREATE EVENT eventname
	ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
	DO
		UPDATE myschema.mytable SET mycol = mycol + 1
sql
SHOW EVENTS; -- 查看调度器状态

时间调度器的优势、适用场景及注意事项

  • 优势：部署在数据库内，由DBA统一维护。避免操作系统管理员误操作，方便迁移
  • 适用场景：定期收集统计信息、清理历史数据、数据库检查
  • 注意事项：在繁忙的高性能要求的服务器上慎重使用调度器、过于复杂的请求适合用程序实现

10.3 触发器 ​

10.3.1 创建触发器 ​
sql
CREATE TRIGGER trigger_name trigger_time tirgger_event ON table_name FOR EACH ROW [trigger_order] trigger_body;

触发器只能在用永久表上创建

  • trigger_time: BEFORE, AFTER
  • trigger_event: INSERT, UPDATE, DELETE

使用别名 OLD 和NEW来引用触发器中发生变化的内容

10.3.2 删除触发器 ​

默认当前数据库

sql
DROP TRIGGER [schema_name.]trigger_name
10.3.3 查看触发器 ​

查看所有的触发器信息，不太方便

sql
SHOW TRIGGERS
10.3.4 触发器的使用 ​

限制

  • 触发程序既不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL语句，但是允许存储程序通过参数将数据返回触发程序
  • 不能在触发器中使用以显式或隐式开始或结束事物的语句

MySQL的触发器是按照BEFORE触发器 、行操作、AFTER触发器的顺序执行的，任何一步发生错误都不会执行剩下的操作。如果是对事务表的进行的操作，会整体回滚。对非事务表的操作无法回滚

第11章 事务控制和锁定语句 ​

MySQL支持对MyISAM和MEMORY存储引擎的表进行表级锁定，对BDB存储引擎的表进行页级锁定，对InnoDB存储引擎的表进行行级锁定。默认情况下，表锁和行锁都是自动获得的

11.1 LOCK TABLES 和 UNLOCK TABLES ​

LOCK TABLES用于锁定用于当前线程的表，如果表被其他线程锁定则会等待到可以获取所有的锁定为止

UNLOCK TABLES可以释放当前线程获取的任何锁定。当前线程执行另一个LOCK TABLES 或 与服务器的链接被关闭时，所有由当前线程锁定的表被隐含的解锁

11.2 事务控制 ​

MySQL通过SET AUTOCOMMIT、START TRANSACTION、COMMIT和ROLLBACK等语句支持本地事务

默认情况下，MySQL是自动提交的。如果需要通过明确的Commit和Rollback来提交和回滚事物，那么就需要通过明确的事物控制命令拉来开始事物

  • START TRANSACTION 或 BEGIN语句可以开始一项新的事务
  • COMMIT和ROLLBACK用来提交或回滚事物
  • CHAIN和RELEASE子句分别用来定义在事物提交或回滚之后的操作，CHAIN会立即启动一个和当前事物具有相同隔离级别的新事物，RELEASE则会断开和客户端的链接
  • SET AUTOCOMMIT 可以修改当前链接的提交方式

同一个事务下，最好使用相同存储引擎的表，否则rollback时需要对非事务类的表做特别的处理

和Oracle的事务管理相同，所有的DDL语句是不能回滚的

11.2 分布式事务 ​

pass

第12章 SQL中的安全问题 ​

12.1 SQL注入简介 ​

利用数据库的外部接口将用户数据插入到实际的数据库操作语言中

12.2 开发中可以采取的应对措施 ​

  • 整理数据使之变得有效
  • 拒绝已知的非法收入
  • 只接受已知的合法收入

过滤非法符号和十六进制形式

第13章 SQL Mode及相关问题 ​

13.1 MySQL SQL Mode简介 ​

  • 通过设置SQL Mode 可以完成不同严格程度的数据校验
  • 通过设置SQL Mode为ANSI模式，来保证大多数SQL语句符合SQL Mode以便迁移到其他数据库
  • 在不同数据库之间进行数据迁移之前，通过设置SQLmode使MySQL上的数据更方便地迁移到目标数据库中

在MySQL5.7中，SQL Mode 有了较大的变化，查询默认地SQL Mode（sql_mode 参数）为ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER 和 NO_ENGINE_SUBSTITUTION (不同的小版本可能略有差异)。这些SQL Mode 含义如下：

  • ONLY_FULL_GROUP_BY：在 group by 子句中没有出现的列，出现在select 列表、having 条件，order by 条件中时会被拒绝
  • STRICT_TRANS_TABLES：非法日期，超过字段长度的值插入时，直接报错，拒绝执行
  • NO_ZERO_IN_DATE：日期中针对月份和日期部分，如果为0，比如‘2018-00-00’，有不同的执行逻辑
    1. disable：可以正常插入，实际插入值还是‘2018-00-00’ 没有警告
    2. enable：可以正常插入，有警告；如果mode中包含 STRICT_TRANS_TABLES,则日期被拒绝写入，但可以通过ignore 关键字写入‘0000-00-00’；
  • NO_ZERO_DATE：针对日期‘0000-00-00’，执行逻辑如下：
    1. disable：可以正常插入，没有警告
    2. enable：可以正常插入，有警告；如果mode中包含STRICT_TRANS_TABLES,则日期拒绝被写入，但可以通过关键字 ignore 写入 ‘0000-00-00’，有警告
  • ERROR_FOR_DIVISION_BY_ZERO：除数为0（包括MOD(N,0)）,执行逻辑如下：
    1. disable：插入NULL，没有警告
    2. enable：插入NULL，有警告；如果mode中包含STRICT_TRANS_TABLES,则数据被拒绝写入，但可以通过加关键字 ignore 写入NULL，有警告
  • NO_AUTO_CREATE_USER：防止使用不带密码子句的grant 语句来创建一个用户
  • NO_ENGINE_SUBSTITUTION：执行create table 或者alter table语句时，如果指定了不支持（包括 disable 或未编译）的存储引擎，是否自动替换为默认的存储引擎
    1. disable：create table 会自动替换后执行，alter table 不会执行，两个命令都有警告
    2. enable：两个命令直接报错

13.3 常见的 SQL Mode ​

熟悉并了解经常使用的SQL Mode 会帮助用户更好的使用它。下面总结了常用的SQL Mode 值及其说明。

  • ANSI: 等同于 REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、ONLY_FULL_GROUP_BY 和 ANSI 组合模式，这种模式遇到异常时倾向于警告而不是立刻返回错误
  • STRICT_TRANS_TABLES：STRICT_TRANS_TABLES 适用于事务表和非事务表，它是严格模式，不允许非法日期，也不允许超过字段长度的值插入字段中，对于插入不正确的值给出错误而不是警告。MySQL5.7版本后添加到默认的SQL Mode中
  • TRADITIONAL：TRADITIONAL 模式等同于 STRICT_TRANS_TABLES 、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER 和 NO_ENGINE_SUBSTITUTION 的组合模式，所以它也是严格模式，对于插入不正确的值会给出错误而不是警告。可以应用在事务表和非事务表，用在事务表时只要出现错误就会立即回滚

第14章 MySQL分区 ​

MySQL从5.1版本开始支持分区的功能，分区是根据一定规则，数据库把一个表分解成多个更小的、更容易管理的部分。逻辑上只有一个表或一个索引，但实际上可能有数十个物理分区组成

14.1 分区概述 ​

分区有利于管理非常大的表

  • 和单个磁盘或文件系统分区相比，可以存储更多的数据
  • 优化查询。在Where字句包含分区条件时，可以只扫描部分分区提高查询效率；涉及聚合函数的查询时，可以在分区进行并行处理
  • 对于过期数据可以通过删除分区来快速删除
  • 跨多个磁盘来分散查询数据，已获得更大的查询吞吐量

MySQL大部分存储引擎支持创建分区表，但是MERGE、CSV、FREDERATED存储引擎除外。MySQL 5.7版本中同一个分区表的所有分区的引擎必须一致，且分区数量不能超过8192（NDB引擎除外）

14.2 分区类型 ​

MySQL 5.7中的分区类型主要如下

  • RANGE分区：基于一个给定连续范围区间
  • LIST分区：类似RANGE分区，区别在于LIST分区是基于枚举出的值列表分区
  • COLUMNS分区：类似RANGE、LIST分区，区别在于分区键既可以是多列，又可以是非整数
  • HASH分区：基于给定的分区个数，对数据取模分配到不同的分区
  • KEY分区：类似于HASH分区，但是用MySQL提供的HASH函数
  • 子分区：也叫做复合分区或者组合分区，即在主分区下再做一层分区，将数据再次分割

在MySQL5.7中，RANGE、LIST、HASH分区都要求分区键必须是INT类型，或者通过表达式返回INT类型。但KEY、COLUMNS分区可以用非TEXT、BLOB类型的列作为分区键

无论是哪种分区类型，要么分区表上没有主键，要么分区表的主键必须包含分区键。分区的命名基本遵循MySQL标识符的规则

14.2.1 RANGE分区 ​

RANGE分区的表是利用取值范围将数据分成分区，每个分区大小按顺序排序

14.2.2 LIST分区 ​

LIST分区是建立离散的值列表告诉数据库特定的值属于那个分区，LIST分区在很多方面类似于RANGE分区，区别在于是LIST分区是枚举的值和集合

14.2.3 COLUMNS分区 ​

MySQL可以根据多列进行分区，单列排序时支持整数、日期时间、字符类型。多列分区时使用多列排序

14.2.4 HASH分区 ​

HASH分区主要用来分离热点读，确保数据再预先确定个数和分区中尽可能平均分布。对一个表执行HASH函数时，MySQL会对分区键应用一个散列函数

MySQL 支持两种分区，常规MySQL分区和线性HASH分区。常规HASH分区采用取模算法、线性HASH分区采用线性的2的次幂运算法则

线性HASH分区是为了解决新增、删除常规HASH分区需要大量数据迁移而加入MYSQL的

14.2.5 KEY分区 ​

类似于HASH分区，KEY分区允许使用用户自定义的表达式，而KEY分区不允许；同时HASH分区只支持整数分区，而KEY分区支持除BLOB、TEXT以外的其他类型的列作为分区键

14.2.6 子分区 ​

对分区表中的每个分区再次分割，又称复合分区。MySQL5.7支持对已经通过了RANGE或者LIST分区的表再次进行子分区，子分区既可以使用HASH分区，也可以使用KEY分区

17.2.7 MySQL 分区上处理NULL值的方法 ​

MySQL不禁止在分区键值上使用NULL，分区键可能是一个字段或者是一个用户定义的表达式

注意：RANGE分区中，NULL值会被当做最小值来处理；LIST分区中，NULL值必须出现在枚举列表中，否则不被接受。HASH/KEY类型的分区中，NULL会被当做零值进行处理