高性能Mysql-Mysql高级特性

1、分区表

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。实现分区的代码实际上是对一组句柄对象的封装。对分区表的请求， 都会通过句柄对象转化成对存储引擎的接口调用。 MySQL实现分区表的方式一一对底层表的封装一一意味着索引也是按照分区的子表定义的， 而没有全局索引。 这和Oracle不同，在Oracle中可以更加灵活地定义索引和表是否进行分区。

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

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

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

•    如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

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

•    一个表最多能有1024个分区。

•    在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表达式。 在MySQL5.5中， 某些场景中可以直接使用列来进行分区。
•    如果分区字段中有主键或者唯一索引的列， 那么所有主键列和唯一索引列都必须包含进来。
•    分区表中无法使用外键约束。

1.1 分区表原理

如前所述， 分区表由多个相关的底层表实现， 这些底层表也是由句柄对象（Handler object） 表示， 所以我们也可以直接访问各个分区。 存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎）， 分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看， 底层表和一个普通表没有任何不同， 存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

分区表上的操作按照下面的操作逻辑进行：

SELECT 查询

当查询一个分区表的时候， 分区层先打开井锁住所有的底层表， 优化器先判断是否可以过滤部分分区， 然后再调用对应的存储引擎接口访问各个分区的数据。

INSERT 操作
当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写人对应底层表。

DELETE 操作

当删除一条记录时，分区层先打开 并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。

UPDATE 操作

当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL先确定需要更新 的记录在哪个分区，然后取出数据井更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，井对原数据所在的底层表进行删除操作。

1.2 分区表的类型

MySQL支持多种分区表。 我们看到最多的是根据范围进行分区，每个分区存储落在某个范围的记录，分区表达式可以是列，也可以是包含列的表达式。 例如，下表就可以将每一年的销售额存放在不同的分区里：
CREATE TABLE sales {
order date DATETIME NOT NULL,
) 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 MAXVALUE );

PA RTITION 分区子句中可以使用各种函数。但有一个要求，表达式返回的值要是一个确定的整数，且不能是一个常数。

MySQL还支持键值、哈希和列表分区，这其中有些还支持子分区，不过我们在生产环境中很少见到。

1.3 如何使用分区表

假设我们希望从一个非常大的表中查询出一段时间的记录，而这几个表中包含了很多年的历史数据，数据是按照时间排序的，例如，希望查询最近几个月的数据，这大约有10亿条记录。你打算如何查询这个表？如何才能更高效？

首先很肯定：因为数据量巨大，肯定不能在每次查询的时候都扫描全表。考虑到索引在空间和维护上的消耗，也不希望使用索引。即使真的使用索引，你会发现数据并不是按照想要的方式聚集的，而且会有大量的碎片产生，最终会导致一个查询产生成千上万的随机I/O，应用程序也随之僵死。这时候只有两条路可选：所有的查询都只在数据表上做顺序扫描， 或者将数据表和索引全部都缓存在内存里。

这正是分区要做的事情。 理解分区时还可以将其当作索引的最初形态， 以代价非常小的 方式定位到需要的数据在哪一片 “区域”。在这片 “区域” 中， 你可以做顺序扫描， 可以建索引， 还可以将数据都缓存到内存， 等等。 因为分区无须额外的数据结构记录每个分区有哪些数据一一分区不需要精确定位每条数据的位置， 也就无须额外的数据结构一一 所以其代价非常低。 只需要一个简单的表达式就可以表达每个分区存放的是什么数据。

为了保证大数据量的可扩展性，一般有两个策略：全量扫描数据， 不妥任何索引；索引数据， 并分离热点。

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会检查两个分区，而不是一个。

② 分区列和索引列不匹配

如果定义的索引列和分区列不匹配， 会导致查询无住进行分区过滤。 

③ 选择分区的成本可能会很高

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

⑤ 维护分区的成本可能很高

1.5 查询优化

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

MySQL只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式就是分区函数也不行。例如：EXPLAIN PARTITIONS SELECT* FROM sales_by_day WHERE  YEAR(day) = 2010。一个很重要的原则是：即便在创建分区时可以使用表达式，但在查询时却只能根据列来过滤分区。

1.5 合并表

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

合并有很多的限制和行为，下面列举的这几点需要在使用的时候时刻记住。

① 在使用 CREATE 语句创建一个合并表的时候，井不会检查各个子表的兼容性。如果子表的定义稍有不同，那么MySQL就可能创建出一个后面无战使用的合并表。

② 根据合并表的特性，不难发现，在合并表上无法使用 REPLACE 语法，无撞使用自增字段。

③ 如果一个查询访问合井表，那么它需要访问所有子表。这会让根据键查找单行的查询速度变慢，如果能够只访问一个对应表，速度肯定将更快。

2、视图

视图本身是一个虚拟表，不存放任何数据，在使用SQL语句访问视图的时候，它返回的数据是mysql从其他表生成的。视图和表实在同一个命名空间，MySQL在很多地方对于视图和表是同样对待的。不过视图和表也有不同，例如，不能对视图创建触发器，也不能使用DROPTABLE命令删除视图。

MySQL可以使用这两种办战中的任何一种来处理视图。 这两种算法分别称为合并算法和临时表算怯，如果可能，会尽可能地使用合并算怯。 MySQL甚至可以嵌套地定义视图，也就是在一个视图上再定义另一个视图。 

2.1 可更新视图

可更新视图是指可以通过更新这个视图来更新视图涉及的相关表。 只要指定了合适的条件， 就可以更新、 删除甚至向视图中写人数据。 如果视图定义中包含了GROUP BY、 UNION、 聚合函数， 以及其他一些特殊情况， 就不能被更新了。

2.2 视图对性能的影响

多数人认为视图不能提升性能，实际上，在MySQL中某些情况下视图也可以帮助提升性能。而且视图还可以和其他提升性能的方式叠加使用。例如，在重构schema的时候可以使用视图，使得在修改视图底层表结构的时候，应用代码还可能继续不报错的运行。可以使用视图实现基于列的权限控制，却不需要真正的在系统中创建列权限，因此没有额外的开销。

2.3 视图的限制

在其他的关系数据库中你可能使用过物化视圈，MySQL还不支持物化视图（物化视图是指将视图结果数据存放在一个可以查看的表中，并定期从原始表中刷新数据到这个表中）。 MySQL也不支持在视图中创建索引。 不过，可以使用构建缓存表或者汇总表的办法来模拟物化视图和索引。 

MySQL视图实现上也有一些让人烦恼的地方。 例如，MySQL并不会保存视图定义的原 始SQL语句，所以如果打算通过执行SHOW CREATE VIEW后再简单地修改其结果的方式来重新定义视图，可能会大失所望。

3、外键约束

InnoDB是目前MySQL中唯一支持外键的内置存储引擎，所以如果需要外键支持那选择就不多了（PBXT也有外键支持）。使用外键是有成本的。比如外键通常都要求每次在修改数据时都要在另外一张表中多执行一次查找操作。虽然InnoDB强制外键使用索引，但还是无法捎除这种约束检查的开销。如果外键列的选择性很低，则会导致一个非常大且选择性很低的索引。

不过，在某些场景下，外键会提升一些性能。如果想确保两个相关表始终有一致的数据，那么使用外键比在应用程序中检查一致性的性能要高得多，此外，外键在相关数据的删除和更新上，也比在应用中维护要更高效，不过，外键维护操作是逐行进行的，所以这样的更新会比批量删除和更新要慢些。

4、在Mysql内部存储代码

MySQL允许通过触发器、 存储过程、 函数的形式来存储代码。 从MySQL5.1开始，还可以在定时任务中存放代码，这个定时任务也被称为 “事件”。存储过程和存储函数都被统称为 “存储程序”。

MySQL中使用存储代码的优点：

•    它在服务器内部执行，离数据最近，另外在服务器上执行还可以节省带宽和网络延迟。
•    这是一种代码重用。 可以方便地统一业务规则，保证某些行为总是一致，所以也可以为应用提供一定的安全性。
•    它可以简化代码的维护和版本更新。

•    它可以帮助提升安全， 比如提供更细粒度的权限控制。一个常见的例子是银行用于转移资金的存储过程z这个存储过程可以在一个事务中完成资金转移和记录用于审计的日志。应用程序也可以通过存储过程的接口访问那些没有权限的表。

•    服务器端可以缓存存储过程的执行计划， 这对于需要反复调用的过程， 会大大降低消艳。
•    因为是在服务器端部署的， 所以备份、 维护都可以在服务器端完成。所以存储程序的维护工作会很简单。它没什么外部依赖， 例如， 不依赖任何Perl包和其他不想在 服务器上部署的外部软件。
•    它可以在应用开发和数据库开发人员之间更好地分工。不过最好是由数据库专家来开发存储过程， 因为不是每个应用开发人员都能写出高效的SQL查询。

MySQL中使用存储代码的缺点：

•    MySQL本身没有提供好用的开发和调试工具， 所以编写MySQL的存储代码比其他的数据库要更难些。
•    较之应用程序的代码， 存储代码效率要稍微差些。例如， 存储代码中可以使用的函数非常有限， 所以使用存储代码很难编写复杂的字符串维护功能， 也很难实现太复杂的逻辑。

•    存储代码可能会给应用程序代码的部署带来额外的复杂性。

•    因为存储程序都部署在服务器内， 所以可能有安全隐患。

•    存储过程会给数据库服务器增加额外的压力， 而数据库服务器的扩展性相比应用服务器要差很多。

•    MySQL井没有什么选项可以控制存储程序的资源消耗， 所以在存储过程中的一个小错误， 可能直接把服务器拖死。

5、游标

游标概念：有数据缓冲的思想：游标的设计是一种数据缓冲区的思想，用来存放SQL语句执行的结果。 先有数据基础：游标是在先从数据表中检索出数据之后才能继续灵活操作的技术。 类似于指针：游标类似于指向数据结构堆栈中的指针，用来pop出所指向的数据，并且只能每次取一个。

游标的优点：

因为游标是针对行操作的，所以对从数据库中select查询得到的每一行可以进行分开的独立的相同或不同的操作，是一种分离的思想。可以满足对某个结果行进行特殊的操作。 游标与基于游标位置的增删改查能力。 MySQL数据库中没有专门描述一行的表达形式，但这是需要的，所以，个人理解的话，我觉得游标是在关系数据库这种面向集合的系统中抽离出来，单独针对行进行表达（也可以理解成网上资料说的：游标是面向集合与面向行的设计思想之间的一种桥梁）

游标缺点

游标的缺点是针对有点而言的，也就是只能一行一行操作，在数据量大的情况下，是不适用的，速度过慢。这里有个比喻就是：当你去ATM存钱是希望一次性存完呢，还是100一张一张的存，这里的100一张一张存就是游标针对行的操作。 数据库大部分是面对集合的，业务会比较复杂，而游标使用会有死锁，影响其他的业务操作，不可取。 当数据量大时，使用游标会造成内存不足现象。

游标的使用场景

针对游标的优缺点，我总结游标的使用场景，主要用在循环处理、存储过程、函数中使用，用来查询结果集，就比如：我们需要从表中循环判断并得到想要的结果集，这时候使用游标操作很方便速度也很快。

6、全文索引

通过数值比较、 范围过滤等就可以完成绝大多数我们需要的查询了。 但是， 如果你希望通过关键字的匹配来进行查询过滤， 那么就需要基于相似度的查询， 而不是原来的精确数值比较。 全文索引就是为这种场景设计的。

全文索引可以支持各种字符内容的搜索（包括 CHAR、 VARCHAR 和 TEXT 类型）， 也支持自然语言搜索和布尔搜索。 

MyISAM 的全文索引是一类特殊的 B-Tree 索引，共有两层。 第一层是所有关键字，然后对于每一个关键字的第二层，包含的是一组相关的 “文档指针”。全文索引不会索引文档对象中的所有词语，它会根据如下规则过滤一些词语：

•    停用词列表中的词都不会被索引。 默认的停用词根据通用英语的使用来设置，可以使用参数ft_stopword_file 指定一组外部文件来使用自定义的停用词。
•    对于长度大于 ft_min_word_length的词语和长度小于 ft_max_word_length的词语，都不会被索引。

7、分布式（XA）事物

XA事务中需要有一个事务协调器来保证所有的事务参与者都完成了准备工作（第一阶段）。 如果协调器收到所有的参与者都准备好的消息，就会告诉所有的事务可以提交了，这是第二阶段。 MySQL在这个XA事务过程中扮模一个参与者的角色，而不是协调者。

实际上，在MySQL中有两种XA事务。 一方面，MySQL可以参与到外部的分布式事务中；另一方面，还可以通过XA事务来协调存储引擎和二进制日志。

7.1 内部XA事物

MySQL本身的插件式架构导致在其内部需要使用XA事务。 MySQL中各个存储引擎是完全独立的，彼此不知道对方的存在，所以一个跨存储引擎的事务就需要一个外部的协调者。 如果不使用XA协议，例如，跨存储引擎的事务提交就只是顺序地要求每个存储引擎各自提交。 如果在某个存储提交过程中发生系统崩愤，就会破坏事务的特性（要么就全部提交，要么就不做任何操作）。

如果将MySQL记录的二进制日志操作看作一个独立的 “存储引擎”，就不难理解为什么即使是一个存储引擎参与的事务仍然需要XA事务了。 在存储引擎提交的同时， 需要将 “提交 ” 的信息写入二进制日志， 这就是一个分布式事务， 只不过二进制日志的参与者是MySQL本身。XA事务为MySQL带来巨大的性能下降。从MySQL5.0开始，它破坏了MySQL内部的“批提交” （一种通过单磁盘I/O操作完成多个事务提交的技术）， 使得MySQL不得不进行多次额外的fsync（）调用。

7.2 外部XA事物

MySQL能够作为参与者完成一个外部的分布式事务。 但它对XA协议支持并不完整， 例如， XA协议要求在一个事务中的多个连接可以做关联，但目前的MySQL版本还不能支持。
因为通信延迟和参与者本身可能失败， 所以外部XA事务比内部消耗会更大。 如果在广域网中使用XA事务， 通常会因为不可预测的网络性能导致事务失败。 如果有太多不可控因素， 例如， 不稳定的网络通信或者用户长时间地等待而不提交，则最好避免使用 XA事务。 任何可能让事务提交发生延迟的操作代价都很大， 因为它影响的不仅仅是自己本身， 它还会让所有参与者都等待。