高性能MySQL（第三版）第七章：MySQL高级特性_每次执行sql语句执行时,服务器做的事情不包括?-优快云博客

本文链接：https://blog.youkuaiyun.com/jinjiniao1/article/details/89953256

7. MySQL高级特性

7.1 分区表

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理字表组成。
- 实现分区的代码实际上是对一组底层表的句柄对象(Handler Object)的封装。
- 对分区表的请求，都会通过句柄对象转换成对存储对象的接口调用。
- 所以分区对于SQL层来说是一个完全封装底层实现的黑盒子，对应用是透明的，但是对底层的文件系统来看就很容易发现，每一个分区表都有一个使用#分隔命名的表文件。
MySQL实现分区表的方式——对底层表的封装——意味着索引也是按照分区的字表定义的，而没有全局索引。这和Oracle不同，在Oracle中可以更加灵活的定义索引和表是否分区
MySQL在创建表时使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无须扫描所有分区——只需要查找包含需要数据的分区。
分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起，另外，如果想一次批量删除整个分区的数据也会变得很方便。
分区起到非常大作用的场景：
- 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。
- 分区表的数据更容易维护。例如，想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作。
- 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
- 可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等。
- 如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。
分区表部分比较重要的限制：
- 一个表最多只能有1024个分区
- 在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL5.5中，某些场景中可以直接使用列来进行分区
- 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
- 分区表中无法使用外键约束。

7.1.1 分区表的原理

如前所述，分区表由多个相关的底层表实现，这些底层表也是由句柄对象(Handler object)标识，所以可以直接访问各个分区。
- 存储引擎管理分区的各个底层表和管理普通表一样(所有的底层表都必须使用相同的存储引擎)，分区表的索引只是在各个底层表上各自加上一个完全相同的索引
- 从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须直到这是一个普通表还是一个分区表的一部分。
分区表上的操作：
- SELECT查询：当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。
- INSERT操作：当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表
- DELETE操作：当删除一条记录，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。
- UPDATE操作：当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL先确定需要更新的记录在哪个分区，然后取出数据并更新，在判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。
有些操作是支持过滤的。
- 当删除一条记录时，MySQL需要先找到这条记录，如果WHERE条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉。这对UPDATE语句同样有效。
- 如果是INSERT操作，则本身就是只命中一个分区，其他分区都会被过滤掉。MySQL先确定这条操作属于哪个分区，再将记录写入对应的底层分区表，无须对任何其他分区进行操作。
虽然每个操作都会”先打开并锁住所有的底层表“，但这并不是说分区表在处理的过程中是锁住全表的。如果存储引擎能够自己实现行级锁，例如InnoDB，则会在分区层释放对应表锁。这个加锁和解锁过程与普通InnoDB上的查询类似。

7.1.2 分区表的类型

MySQL支持多种分区表。最多的是根据范围进行分区，每个分区存储落在某个范围的记录，分区表达式可以是列，也可以包含列的表达式。
PARTITION分区子句中可以使用各种函数，但是表达式返回的值必须是一个确定的整数，且不能是一个常数。
MySQL还支持键值、哈希和列表分区，这其中有些还支持子分区。在MySQL5.5中，还可以使用RANGE COLUMNS类型的分区，这样即使是基于时间的分区也无须再将其转换成一个整数。
- 按时间分区的InnoDB表，系统通过子分区可降低索引的互斥访问的竞争。最近一年的分区的数据会被非常频繁地访问，这会导致大量的互斥量的竞争。使用哈希子分区可以将数据切成多个小片，大大降低互斥量的竞争问题。
其他的分区技术：
- 根据键值进行分区，来减少InnoDB的互斥量竞争
- 使用数学模函数来进行分区，然后将数据轮询放入不同的分区。例如，可以对日期做模7的运算，或者更简单地使用返回周几的函数，如果只想保留最近几天的数据，这样分区很方便
- 假设表有一个自增的主键列id，希望根据时间将最近的热点数据集中存放。那么必须将时间戳包含在主键当中才行，而这和主键本身的意义相矛盾。这种情况下可以使用这样的分区表达式来实现同样的目的: HASH(id DIV 1000000)，这将为100万数据建立一个分区。一方面实现了当初分区的目的，另一方面比起使用时间范围分区还避免了一个问题，就是当超过一定阈值时，如果使用时间范围分区就必须新增分区。

7.1.3 如何使用分区表

假设从一个非常大有10亿条记录的表找出最近几个月的数据：
- 因为数据量巨大，肯定不能在每次查询的时候扫描全表
- 考虑到索引在空间和维护上的消耗，也不希望使用索引。
  - 除非是覆盖查询，否则服务器需要根据索引扫描的结果回表。
  - 如果真的使用索引，会发现数据不是按照想要的方式聚集的，而且会有大量的碎片产生，导致一个查询产生大量的随机IO
- 剩下的路：
  - 让所有查询都只在数据表上做顺序扫描
  - 将数据表和索引全部都缓存在内存里
  - 使用分区
理解分区时还可以将其当作索引的最初形态，以代价非常小的方式定位到需要的数据在哪一片区域。在这一片区域中，可以做顺序扫描，可以建索引，可以将数据缓存到内存中，等等。因为分区无须额外的数据结构记录每个分区有哪些数据——分区不需要精确定位每条数据的位置，也就无须额外的数据结构——因此代价非常低。

保证大数据量的可扩展性的策略：
- 全量扫描数据，不要任何索引：
  - 可以使用简单的分区方式存放表，不要任何索引，根据分区的规则大致定位需要的数据位置。只要能够使用WHERE条件，将需要的数据限制在少数分区中，则效率是很高的。当然，也需要做一些简单的运算保证查询的响应时间能够满足需求。
  - 使用该策略假设不用将数据完全放入到内存中，同时还假设需要的数据全部在磁盘上。因为内存相对很小，数据很快会被挤出内存，所以缓存起不了任何作用。
  - 这个策略适用于以正常的方式访问大量数据的时候。
  - 必须将查询需要扫描的分区个数限制在一个很小的数量。
- 索引数据，并分离热点：
  - 如果数据有明显的“热点”，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能有有机会都缓存在内存中。
  - 这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效地使用缓存。

7.1.4 什么情况下会出问题

上一节介绍的两个分区策略都基于两个很重要的假设：查询能够过滤掉很多额外的分区，分区本身并不会带来很多额外的代价。

可能会遇到问题的场景：

NULL值会使分区过滤无效

分区的表达式的值可以是NULL：第一个分区是一个特殊分区。
- 假设按照PARTITION BY RANGE YEAR(order_date)分区，那么所有order_date为NULL或者是一个非常值的时候，记录都会放到第一个分区。
  - 假设有如下查询:WHERE order_date BETWEEN '2012-01-01' AND '2012-01-31' ,实际上MySQL会检查两个分区，因为YEAR()在接收非法值时会返回NULL而把记录放到第一个分区。
- 如果第一个分区非常大，特别是当使用"全量扫描数据，不要任何索引"的策略时，代价会非常大。
- 优化技巧：
  - 创建一个无用的第一个分区，例如：PARTITION p_nulls VALUES LESS THAN (0)。这样即使需要检查第一个分区，代价也非常小
  - (最优)MySQL5.5以后不需要第一个优化技巧，因为可以直接使用列本身而不是基于列的函数进行分区。PARTITION BY RANGE COLUMNS(order_date)

分区列和索引列不匹配：
- 如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。
  - 假设在列a上定义了索引，而在列b上进行分区。因为每个分区都有其独立的索引，索引扫描列a上的索引就需要扫描每一个分区内对应的索引。
- 应该避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件：
  - 其他问题：如果在一个关联查询中，分区表在关联顺序中是第二个表，并且关联使用的索引和分区条件不匹配。那么关联时针对第一个表符合条件的每一个行，都需要访问并搜索第二个表的所有分区。
选择分区的成本可能很高
- 不同类型的分区，由于其实现方式不同，所以它们的性能也不同
  - 尤其是范围分区，对于回答“这一行属于哪个分区”这样的成本可能会非常高，因为服务器需要扫描所有的分区的列表来找到正确的答案。类似这样的线性搜索的效率不高，随着分区数的增长，成本会越来越高。
  - 其他的分区类型，如键分区和哈希分区，则没有这样的问题
- 对大多数系统来说，100个左右的分区是没有问题的。
打开锁并锁住所有底层表的成本可能很高
- 当查询访问分区表的时候，MySQL需要打开锁并锁住所有底层表，这是分区表的另一个开销。
  - 这个操作在分区过滤之前发生，因此无法通过分区过滤降低此开销，并且该开销也和分区类型无关，会影响所有的查询。
- 对一些本身操作非常快的查询，比如根据主键查找单行，会带来明显的额外开销。
- 优化技巧：
  - 用批量操作的方式来降低单个操作的此类开销，例如使用批量插入或者LOAD DATA INFILE、一次删除多行数据，等等
  - 限制分区的个数
维护分区的成本可能很高
- 某些分区维护操作的速度会非常快，例如新增或者删除分区(删除一个大分区可能会很慢，不过这是另一回事)
- 而有些操作，如重组分区或者类似ALTER语句的操作，速度会比较慢，因为需要复制数据。