高性能MySQL读书摘要（七）- MySQL高级特性

互联网项目中不建议使用分区表，而是使用分库分表替代。分区是数据库基于大的B+树，每个分区是一个小的B+树的思路，用于单机时很方便，基于MySQL自身实现，但如果是使用分库分表，是需要写代码的，并且要注意跨库JOIN的操作。当然可以两者结合使用，但是不建议这么做

7.1 分区表

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

MySQL实现分区表的方式—对底层表的封装—意味着索引也是按照分区的子表定义的，而没有全局索引。这和Oracle不同，在Oracle中可以更加灵活地定义索引和表是否进行分区。

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

分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起，另外，如果想一次批量删除整个分区的数据也会变得很方便。

在下面的场景中，分区可以起到非常大的作用：

1. 表非常大以致于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。
2. 分区表的数据更容易维护。例如，想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作。
3. 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
4. 可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等。
5. 如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

MySQL的分区实现非常复杂。更多细节请参考MySQL官方手册中的“分区”一节。另外还可以阅读CREATE TABLE、SHOW CREATE TABLE、ALTER TABLE和INFORMATION_SCHEMA.PARTITIONS、EXPLAIN关于分区部分的介绍。分区特性使得CREATE TABLE和ALTER TABLE命令变得更加复杂了。

分区表本身也有一些限制，下面是其中比较重要的几点：

1. 一个表最多只能有1024个分区。
2. 在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL5.5中，某些场景中可以直接使用列来进行分区。
3. 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
4. 分区表中无法使用外键约束。（几乎目前的所有涉及都是在应用程序中约束外键）。

7.1.1 分区表的原理

存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。
分区表上的操作按照下面的操作逻辑进行：
SELECT查询
​当查询一个分区表的时候，分区表先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。
INSERT操作
当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表。
DELETE操作
当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。
UPDATE操作
当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。

都需要锁住底层表？？？NO，这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁，例如InnoDB，则会在分区表层释放对应表锁。这个加锁和解锁过程与普通InnoDB上的查询类似。
后面我们会通过一些例子来看看，当访问一个分区表的时候，打开和锁住所有底层表的代价和带来的后果。

7.1.2分区表的类型

MySQL支持多种分区表。我们看到最多的是根据范围进行分区，每个分区存储落在某个范围的记录，分区表达式可以是列，也可以使包含列的表达式。例如，下表就可以将每一年的销售额存放在不同的分区里：

CREATE TABLE sales(
	order_date DATETIME NOT NULL,
	-- Other columns omitted
	) ENGINE = InnoDB PARTITION BY RANGE(YEAR(order_date)) (
	   PARTITION p_2010 VALUES LESS THAN (2010),
	   PARTITION p_2011 VALUES LESS THAN (2011),
	   PARTITION p_2012 VALUES LESS THAN (2012),
	   PARTITION p_catchall VALUES LESS THAN MAXVAULUE);
	);

PARTITION分区子句中可以使用各函数，但有一个要求，表达式返回的值要是一个确定的整数，且不能是一个常数。这里我们使用函数YEAR()，也可以使用任何其他函数，如TO_DAYS()。根据时间间隔进行分区，是一种很常见的分区方式。
MySQL还支持键值、哈希和列表分区，这其中有些还支持子分区，不过我们在生产环境中很少见到。
​在我们看过的一个子分区的案例中，对一个类似于前面我们设计的按时间分区的InnoDB表，系统通过子分区可降低索引的互斥访问的竞争。最近一年的分区的数据会被非常频繁地访问，这会导致大量的互斥量的竞争。使用哈希子分区可以将数据切成多个小片，大大降低互斥量的竞争问题。
我们还看到一些其他的分区技术包括：

1. 根据键值进行分区，来减少InnoDB的互斥量竞争。
2. 使用数学模函数来进行分区，然后将数据轮询放入不同的分区。例如，可以对日期做模7的运算，或者更简单地使用返回周几的函数，如果只想保留最近几天的数据，这样分区很方面。
3. 假设表有一个自增的主键列id，希望根据时间将最近的热点数据集中存放。那么必须将时间戳包含在主键当中才行，而这和主键本身的意义相矛盾。这种情况下也可以使用这样的分区表达式来实现相同的目的：HASH(id DIV 1000000),这将为100万数据建立一个分区。这样一方面实现了当初的分区目的，另一方面比起使用时间范围分区还避免了一个问题，就是当超过一定阈值时，如果使用时间范围分区就必须新增分区。

7.1.3 如何使用分区表

​当查询最近几个月有10亿条记录或者在原表中有10TB的数据，这个数据量远大于内存。可以使用分区表解决此问题。
首先很肯定：因为数据量巨大，肯定不能在每次查询的时候都扫描全表。考虑到索引在空间和维护上的消耗，也不希望建立索引。即使真的使用索引，你会发现数据并不是按照想要的方式聚集的，而且会有大量的碎片产生，最终会导致一个查询产生成千上万的随机I/O，应用程序也随之将死。这时候只有两条路可选：让所有的查询都只在数据表上做顺序扫描，或者将数据表和索引全部都缓存在内存里。
这里需要再陈述一遍：在数据量超大的时候，B-Tree索引就无法起作用了。除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录，如果数据量巨大，这将产生大量随机I/O，随之，数据库的响应时间将大到不可接受的程度。另外，索引维护（磁盘空间、I/O操作）的代价也非常高。有些系统，如Infobright，意识到这一点，于是就完全放弃使用B-Tree索引，而选择了一些更粗粒度的但消耗更少的方式检索数据，例如在大量数据上只索引对应的一小块元数据。
为了保证大数据量的可扩展性，一般有下面两个策略：

全量扫描数据，不要任何索引
​可以使用简单的分区方式存放表，不要任何索引，根据分区的规则大致定位需要的数据位置。只要能够使用WHERE条件，将需要的数据限制在少数分区中，则效率是很高的。当然，也需要做一些简单的运算保证查询的响应时间能够满足需求。使用该策略假设不用将数据完全放入到内存中，同时还假设需要的数据全都在磁盘上，因为内存相对很小，数据很快会被挤出内存，索引缓存起不了任何作用。这个策略适用于以正常的方式访问大量数据的时候。警告：后面我们会详细解释，必须将查询需要扫描的分区个数限制在一个很小的数量。

索引数据，并分离热点。
如果数据有明显的“热点”，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中。这样查询就可以访问一个很少的分区表，能够使用索引，也能够有效地使用缓存。

7.1.4 什么情况下会出问题

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

NULL值会使分区过滤无效
​第一个分区存放所有为NULL或者一个非法值的时候，记录都会存放到第一个分区。在MySQL5.5中就不需要这个优化技巧了，因为可以直接使用列本身而不是基于列的函数进行分区：PARTITION BY RANGE COLUMNS(order_date)。所以这个案例最好的解决方法是能够直接使用MySQL5.5的这个语法。

分区列和索引列不匹配
​如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。假设在列a上定义了索引，而在列b上进行分区。因为每个分区都有其独立的索引，所以扫描列b上的索引就需要扫描每一个分区内对应的索引。应该避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件。
选择分区的成本可能很高
打开并锁住所有底层表的成本可能很高
​会影响所有的查询，这一点对一些本身操作非常快的查询，比如根据主键查询单行，会带来明显的开销。可以用批量操作的方式来降低单个操作的此类开销，例如使用批量插入或者LOAD DATA INFILE、一次删除多行数据等等。。
维护分区的成本可能很高
​有些操作，例如重组分区或者类似ALTER语句的操作：这类操作需要复制数据。重组分区的原理与ALTER类似，先创建一个临时分区，然后将数据复制到其中，最后再删除原分区。
如上所述，分区实现中的一些其他限制如下：

1. 所有分区都必须使用相同的存储引擎。
2. 分区函数中可以使用函数和表达式也有一些限制。
3. 某些存储引擎不支持分区。
4. 对于MyISAM的分区表，不能再使用LOAD INDEX INTO CACHE操作。
5. 对于MyISAM表，使用分区表时需要打开更多的文件描述符。

7.1.5 查询优化

​分区最大的优点就是优化器可以根据分区函数来过滤一些分区。根据粗粒度索引的优势，通过分区过滤通常可以让查询扫描更少的数据（在某些场景下）。
​所以，对于访问分区表来说，很重要的一点是要在WHERE条件中带入分区列，有时候即使看上去多余也要带上，这样就可以让优化器能够过滤掉无须访问的分区。如果没有这些条件，MySQL就需要让对应存储引擎访问这个表的所有分区，如果表非常大的话，就可能会非常慢。

使用EXPLAIN PARTITION 可以观察优化器是否执行了分区过滤，下面是一个示例。

7.1.6 合并表

合并表是一种早期的、简单的分区实现，和分区表相比有一些不同的限制，并且缺乏优化。分区表严格来说是一个逻辑上的概念，用户无法访问底层的各个分区，对用户来说分区是透明的。但是合并表允许用户单独访问各个子表。合并表即将淘汰。

7.2 视图

​MySQL5.0版本之后开始引入视图。视图本身是一个虚拟表，不存放任何数据。在使用SQL语句访问视图的时候，它返回的数据是MySQL从其他表中生成的。视图和表是在同一个命名空间，MySQL在很多地方对于视图和表是同样对待的。不过视图和表也有不同，例如，不能对视图创建触发器，也不能使用DROP TABLE命令删除视图。

存储过程（互联网应用不建议使用）

一组为了完成特定功能的 SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

存储过程优化思路

1. 尽量利用一些 sql 语句来替代一些小循环，例如聚合函数，求平均函数等。
2. 中间结果存放于临时表，加索引。
3. 少使用游标。 sql 是个集合语言，对于集合运算具有较高性能。而 cursors 是过程运算。比如对一个 100 万行的数据进行查询。游标需要读表 100 万次，而不使用游标则只需要少量几次读取。
4. 事务越短越好。 sqlserver 支持并发操作。如果事务过多过长，或者隔离级别过高，都会造成并发操作的阻塞，死锁。导致查询极慢， cpu 占用率极地。
5. 使用 try-catch 处理错误异常。
6. 查找语句尽量不要放在循环内。