高性能MYSQL（学习笔记）-MySQL高级特性1

MySQL高级特性

分区表

分区表是一个独立的逻辑表，但是底层由多个物理字表组成。实现分区的代码实际上是对一组底层表的句柄对象的封装，对于分区表的请求，都会通过句柄对象转化成存储引擎的接口调用。所以分区对于SQL层来说是一个完全封装底层实现的黑盒子，对应用是透明的，但是从底层来看每个分区表都有一个使用#分隔命名的表文件。

MySQL实现分区表的方式——对底层表的封装——意味着索引也是按照分区的字表定义，没有全局索引。

MySQL在创建表时用PARTITION BY 子句定义每个分区存放的数据。在执行查询时，优化器根据分区定义过滤那些没有我们需要数据的分区，这样查询无须扫描所有分区，只需要找到包含需要数据的分区就可以了。

使用场景和优点：

1、  表非常大以至于无法全部存放在内存中，获取的数据比较少

2、  数据更容易维护，删除、更新方便

3、  分区表的数据可以存放在不同的物理设备上。

4、  备份和恢复独立的分区。

限制：

1、  一个表最多分区1024个

2、  分区字段中有主键或者唯一索引的列，所有主键列和唯一索引必须包含进来

3、  分区表无法使用外键约束

分区表原理

分区表由多个相关的底层表实现，这些底层表是由句柄对象来表示，可以直接访问分区，分区表的索引只是在各个底层表上各自加上一个完全相同的索引，从存储引擎上看，分区表和普通表没有什么不同。

SELECT查询
打开并锁住所有的底层表，优化器判断是否会过滤部分分区，然后调用对应的存储引擎接口访问各个分区的数据。

INSERT操作：

打开并锁住所有底层表，确定哪个分区接收记录，写入底层表

DELETE操作：

打开并锁住所有底层表，确定数据对应的分区，删除操作

UPDATE操作：

打开并锁住所有的底层表，确定更新的分区，取出更新，判断更新后的数据应该存放的分区位置，写入操作。

分区表的类型

    MySQL支持多种分区类型，最多的是根据范围进行分区，每个分区存储落在某个范围记录，分区表达式可以是列、列表达式。PARTITION分区子句可以使用各种函数，但是返回值一定要是一个确定的整数，且不能是一个常数，根据时间分区是最常见的分区形式，其他也可以根据键值减少INNODB互斥量竞争、哈希、列表，数学模型函数来进行分区，将数据轮询方式放入不同的分区，例如对日期做模7的运算，或者更简单返回周几的函数

如何使用分区

数据量大，按时间来查询，每次查询需要扫描全表、索引在空间和维护较高，当使用索引时，数据并不是我们想要的聚集，而且会产生大量碎片，查询时候产生成千上万的随机I/O，容易导致程序崩溃。

所有的查询只在数据表上做顺序扫描，或者将数据表和索引全部缓存在内存中。当数据量超大时，B-Tree索引无法起作用，除非是索引覆盖扫描，否则数据服务器需要根据索引扫描的结果回表，查询所有符合条件的记录，如果数据量大，这将产生大量的随机I/O操作，数据库的响应时间也将会达到不可接受的程度。索引维护（磁盘空间、I/O操作）代价也非常高。

分区是通过更粗的颗粒度但消耗更少的方式检索数据，通过定位需要的数据在哪个“区域”，再通过顺序扫描、索引、将数据放到缓存或者内存等方式快速查询到数据。

   为保证大数据里量的可扩展性，一般有下面两个策略：

1、  全量扫描数据，不要任何索引，通过WHERE条件将需要的数据限制在少数分区中

2、  索引数据，并分离热点，将热点数据单独放置在一个分区中，让这个分区的数据有机会缓存到内存中，这样查询可以访问很少的分区表，能够使用索引，也能够有效地使用缓存。

分区可能出现的问题

    NULL值会使分区过滤无效

当NULL值或者非法值时候，记录会放到第一个分区中，导致第一个分区非常大 ，特别是当使用“全量扫描数据，不要任何索引”策略时，代价非常大。可以创建一个无用的第一个分区，用来存放无效的数据，这样就算检测到第一个分区代价也非常小，但是MySQL5.5直接使用PARTITION BY RANGE COLUMNS(orderdate)就可以了。

分区列和索引不匹配

如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。例如列a定义了索引，列b上进行了分区，当扫描列b时候就需要扫描每个分区对应的索引，当每个分区对应的索引非叶子节点都在内存中，或者能跳过某些分区那么速度能接受。但是为避免这个问题，要避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件。

选择分区的成本可能更高

范围分区中随着分区数增长，成本会越来越高，每写入一行数据到范围分区的表时，都需要扫描分区定义列表来找到合适的目标分区，通过限制分区数量建议100左右是没有问题的。

打开并锁住所有底层表的成本可能更高

当查询分区表时，在分区过滤之前会锁住所有的底层表，会影响所有的查询，比如主键查找单行会带来额外开销。通过批量操作来降低开销，同时限制分区的个数

维护分区的成本可能很高

像重组分区成本很高，需要创建临时的分区，然后将数据复制到其中，最后再删除原分区。

其他：所有分区都必须使用相同的存储引擎、分区函数中可以使用的函数和表达式有限制、某些存储引擎不支持分区、mysiam 分区表不能使用LOAD INDEX INTO CACHE操作、mysiam需要打开更多的文件描述符。

查询优化

分区最大优点就是优化器可以根据分区函数来过滤一些分区，根据粗粒度索引的优势，通过分区可以让查询扫描更少的数据。

访问分区表中最重要一点是要在where条件中加入分区列，就算是多余的，可以让优化器过滤掉无需访问的分区，如果没有加入，MySQL需要让对应存储引擎去访问这个表的所有分区。MySQL只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值过滤分区，即使这个表达式就是分区函数也不行。