高性能mysql：MySQL高级特性

最新推荐文章于 2024-07-06 09:18:11 发布

weixin_33877885

最新推荐文章于 2024-07-06 09:18:11 发布

阅读量349

点赞数

文章标签：数据库操作系统 c/c++

原文链接：https://my.oschina.net/u/4008390/blog/2885680

版权

2019独角兽企业重金招聘Python工程师标准>>>

分区表

分区表是一个独立的逻辑表，底层由多个物理子表构成。实现分区的代码实际上是对一组底层表的句柄对象的封装。对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用。

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

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

分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。

使用场景：

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

分区表本身也有一些限制：

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

分区表的原理

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

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

SELECT查询

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

INSERT操作

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

DELETE操作

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

UPDATE操作

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

有些操作是支持过滤的。MySQL先确定需要更新的记录在哪个分区，再将记录写入对应的底层分区表，无需对任何其他分区进行操作。

虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁，则会在分区释放对应表锁。

分区表的类型

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 MAXVALUE
);

PARTITION分区子句中可以使用各种函数。但有一个要求，表达式返回的值是一个确定的整数，且不能是一个常数。在MySQL5.5中，还可以使用RANGE COLUMNS类型的分区，这样即使是基于时间的分区也无须在将其转化成一个整数。

按时间分区的InnoDB表，系统通过子分区可降低索引的互斥访问的竞争。最近一年的分区的数据会被非常频繁地访问，这会导致大量的互斥量的竞争。使用哈希子分区可以将数据切成多个小片，大大降低互斥量的竞争问题。

其他的分区技术包括：根据键值进行分区，来减少InnoDB的互斥量竞争。使用数学模函数来进行分区，然后将数据轮询放入不同的分区。假设表有一个自增的主键列id，希望根据时间最近的热点数据集中存放。那么必须将时间戳包含在主键当中才行，这和主键本身的意义相矛盾。这种情况可以这样达到目的HASH(id DIV 1000000)

如何使用分区表

假设从一个非常大的表中查询出一段时间的记录，因为数据量巨大，肯定不能在每次查询的时候都扫描全表。考虑到索引在空间和维护上的消耗，也不希望使用索引。即使真的使用索引，你会发现数据并不是按照想要的方式聚集的，而且会有大量的碎片产生，最终会导致一个查询产生成千上万的随机I/O，应用程序也随机僵死。情况好一点的时候，也许可以通过一两个索引解决一些问题。不过多数情况下，索引不会有任何作用。这时候只有两条路可选：让所有的查询都只在数据表上做顺序扫描，或者将数据表和索引全部都缓存在内存里。

在数据量超大的时候，B-Tree索引就无法起作用了。除非是索引的覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录，如果数据量巨大，这将产生大量随机I/O，数据库的响应时间将大到不可接手的程度。

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

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

全量扫描数据，不要任何索引可以使用简单的分区存放表，不要任何索引，根据分区的规则大致定位需要的数据位置。只要能够使用WHERE条件，将需要的数据限制在少数分区中，则效率是很高的。这个策略适用于以正常的方式访问大量数据的时候。但是必须将查询需要扫描的分区个数限制在一个很小的数量。
索引数据，并分离热点如果数据有明显的"热点"，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中。这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效地使用缓存。

什么情况下会出问题

上面介绍的两个分区策略都基于两个非常重要的假设：查询都能够过滤掉很多额外分区、分区本身并不会带来很多额外的代价。而事实证明，这两个假设在某些场景下会有问题。

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值，那么这个范围的值可能会返回NULL而被存放到第一个分区了。

如果第一个分区非常大，特别是当使用"全量扫描数据，不要任何索引"的策略时，代价会非常大。而且扫描两个分区来查找列也不是我们使用分区的初衷。

分区列和索引列不匹配

如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。假设在列a上定义了索引，而在列b上进行了分区。因为每个分区都有其独立的索引，所以扫描列b上的索引就需要扫描每一个分区对应的索引。

选择分区的成本可能很高

分区有很多类型，不同类型分区的实现方式也不同，所以他们的性能也各不相同。尤其是范围分区，对于回答"这一行属于哪个分区"、"这些符合查询条件的行在哪些分区"这样的问题成本可能会非常高，因为服务器需要扫描所有的分区定义的列表来找到正确答案。类似这样的线性搜索的效果不高，所以随着分区数的增长，成本会越来越高。

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

当查询访问分区表的时候，MySQL需要打开并锁住所有的底层表，这是分区表的另一个开销。这个操作在分区过滤之前发生，所以无法通过分区过滤降低此开销，并且改开销也和分区类型无关，会影响所有的查询。

维护分区的成本可能很高

目前分区实现中的一些其他限制:
所有分区都必须使用相同的存储引擎。
分区函数中可以使用的函数和表达式也有一些限制。
某些存储引擎不支持分区。
对于MyISAM的分区表，不能再使用LOAD INDEX INTO CACHE操作
对于MyISAM表，使用分区表时需要打开更多的文件描述符。虽然看起来是一个表，其实背后有很多独立的分区，每一个分区对于存储引擎来说都是一个独立的表。这样即使分区表只占用一个表缓存条目，文件描述符还是需要多个。因此，即使已经配置了合适的表缓存，以确保不会超过操作系统的单个进程可以打开的文件描述符的个数，但对于分区表而言，还是会出现超过文件描述符限制的问题。

查询优化

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

对于访问分区表来说，很重要的一点是要在WHERE条件中带入分区列，有时候即使看似多余的也要带上，这样就可以让优化器能够过滤无须访问的分区。

这个查询访问所有的分区。

MySQL只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式就是分区函数也不行。

这里写的WHERE条件中带入的是分区列，而不是基于分区列的表达式，所以优化器能够利用这个条件过滤部分分区。一个很重要的原则是：即便在创建分区时可以使用表达式，但在查询时却只能根据列来过滤分区。

合并表

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

合并表相当于一个容器，里面包含了多个真实表。可以在CREATE TABLE中使用一种特别的UNION 语法来指定包含哪些真实表。

最后建立的合并表和前面的各个真实表字段完全相同，在合并表中有的索引各个真实子表也有，这是创建合并表的前提条件。各个子表在对应列上都有主键限制，但是最终的合并表中仍然出现了重复值。

INSERT_METHOD=LAST告诉MySQL,将所有的INSERT语句都发送给最后一个表。指定FIRST或者LAST关键字是唯一可以控制行插入到合并表的哪一个子表的方式。

INSERT语句的执行结果可以在最终的合并表中看到，也可以在对应的子表中看到：

删除一个合并表，它的子表不受任何影响，而如果直接删除其中一个子表则可能会有不同的后果，这要视操作系统而定。

在使用CREATE语句创建一个合并表的时候，并不会检查各个子表的兼容性。如果子表的定义稍有不同，那么MySQL就可能创建出一个后面无法使用的合并表。
根据合并表的特性，不难发现，在合并表上无法使用REPLACE语法，无法使用自增字段。
如果一个查询访问合并表，那么它需要访问所有子表。这会让根据键查找单行的查询速度变慢，如果能够只访问一个对应表，速度肯定将更快。限制合并表中的子表数量特别重要，特别是当合并表是某个关联查询的一部分的时候，因为这时访问一个表的记录数可能会将比较操作传递到关联的其他表中，这时减少记录的访问就是减少整个关联操作。
- 执行范围查询时，需要在每一个子表各执行一次，这比直接访问单个表的性能要差很多，而且子表越多，性能越差。
- 全表扫描和普通表的全表扫描速度相同。
- 在合并表上做某一键和主键查询时，一旦找到一行数据就会停止。
- 子表的读取顺序和CREATE TABLE语句中的顺序相同。

合并表的各个子表可以直接被访问，它还具有一些MySQL5.5分区所不能提供的特性：

一个MyISAM表可以是多个合并表的子表。
可以通过直接复制.frm、.MYI、.MYD文件，来实现在不同的服务器之间复制各个子表。
在合并表中可以很容易的添加新的子表：直接修改合并表的定义就可以了。
可以创建一个合并表，让它只包含需要的数据。
如果想对某个子表做备份、恢复、修改、复制或者别的操作时，可以先将其从合并表中删除，操作结束后再将其加回去。
可以使用myisampack来压缩所有的子表。

视图

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

CREATE VIEW Oceania AS SELECT * FROM Country WHERE Continent = 'Oceania' WITH CHECK OPTION;

实现视图最简单的方法是将SELECT 语句的结果存放到临时表中。当需要访问视图的时候，直接访问这个临时表就可以了。

SELECT Code,Name FROM Oceania WHERE Name ='BeiJing';

CREATE VIEW Oceania AS SELECT * FROM Country WHERE Continent = 'Oceania' WITH CHECK OPTION;
SELECT Code,Name FROM Oceania WHERE Name ='BeiJing';

这样做会有明显的性能问题，优化器也很难优化在这个临时表上的查询。实现视图更好的方法是，重写含有视图的查询，将视图的定义SQL直接包含进查询的SQL中。

SELECT Code,Name FROM Country WHERE Continent = 'Oceania' AND Name ='BeiJing';

MySQL使用合并算法和临时表算法处理视图。

如果视图中包含GROUP BY、DISTINCT、任何聚合函数、UNION、子查询等，只要无法在原表记录和视图记录中建立一一映射的场景中，MySQL都将使用临时表算法来实现视图。

视图的实现算法是视图本身的属性，和作用在视图上的查询语句无关。

可更新视图

可更新视图是指可以通过更新这个视图来更新这个视图来更新视图涉及的相关表。只要指定了合适的条件，就可以更新、删除甚至向视图写入数据。

UPDATE Oceania SET Population = Population * 1.1 WHERE Name = 'BeiJing';

如果视图定义中包含了GROUP BY、UNION、聚合函数，以及其他一些特殊情况，就不能更新了。视图更新的查询也可以是一个关联语句，但是有个一限制，被更新的列必须来自同一个表中。所有使用临时表算法实现的视图都无法被更新。

视图对性能的影响

在MySQL中某些情况下视图也可以帮助提升性能。而且视图还可以提升性能的方式叠加使用。例如，在重构schema的时候可以使用视图，使得在修改视图底层表结构的时候，应用代码还可能继续不报错的运行。

可以使用视图实现基于列的权限控制，却不需要真正的在系统中创建列权限，因此没有额外的开销。

CREATE VIEW public.employeeinfo AS SELECT firstname,lastname FROM private.employeeinfo;
GRANT SELECT ON public.* TO pulic_user;

我们这里使用连接ID作为视图名字的一部分来避免冲突。

MySQL先执行视图的SQL生成临时表，然后再将sales_per_day和临时表关联。这里的WHERE子句中的BETWEEN条件并不能下推到视图中，所以视图在创建的时候仍需要将所有的数据都放到临时表中。而且临时表中不会有索引。

如果打算使用视图来提升性能，需要做比较详细的测试。即使是合并算法实现的视图也可能会有额外的开销，而且视图的性能很难预测。

视图的限制

MySQL还不支持物化视图(物化视图是指将视图结果数据放在一个可以查看的表中，并定期从原始表中刷新数据到这个表中)。MySQL也不支持在视图中创建索引。

可以找到定义视图原始SQL语句

外键约束

InnoDB是目前MySQL中唯一支持外键的内置存储引擎。

使用外键是有成本的。比如外键通常都要求每次在修改数据时都要在另外一张表中多执行一次检查操作。在某些场景下，外键会提升一些性能。如果想确保两个相关表始终有一致的数据，那么使用外键比在应用程序检查一致性的性能要高得多，外键在相关数据的删除和更新上，比应用在维护要更高效。

外键约束使得查询需要额外访问一些特别的表，意味着需要额外的开销。如果向子表中写入一条记录，外键约束会让InnoDB检查对应的父表的记录，也就需要对父表对应记录进行加锁操作，来确保这条记录不会在这个事物完成之时就被删除了。这会导致额外的锁等待，甚至会导致一些死锁。

对于相关数据的同时更新外键更合适，但是如果外键只是用作数值约束，那么触发器或者显式地限制取值会更好些。

如果只是使用外键约束，那通常在应用程序里实现该约束会更好。外键会带来很大的额外消耗。

在MySQL内部存储代码

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

不同类型的存储代码的主要区别在于其执行的上下文——也就是输入和输出。存储过程和存储函数都可以接收参数然后返回值，但是触发器和事件不行。

存储代码的优点：

它在服务器内部执行，离数据最近，另外在服务器上执行还可以节省宽带和网络延迟。
这是一种代码重用。可以方便地统一业务规则，保证某些行为总是一致，所以也可以为应用提供一定的安全性。
它可以简化代码的维护和版本更新。
他可以帮助提升安全，比如提供更细粒度的权限控制。
服务器端可以缓存存储过程的执行计划，这对于需要反复调用的过程，会大大降低消耗。
在服务器端部署的，备份、维护都可以在服务器端完成。
它可以在应用开发和数据库开发人员之间更好地分工。

存储代码的缺点：

MySQL本身没有提供好用的开发和调试工具，所以编写MySQL的存储代码比其他数据库要更难些。
较之应用程序的代码，存储代码效率要稍微差些。存储代码中可以使用的函数非常有限，使用存储代码很难编写复杂的字符串维护功能，也能难实现太复杂的逻辑。
存储代码可能会给应用程序代码的部署带来额外的复杂性。
因为存储程序都部署在服务器内，所以可能有安全隐患。如果将非标准的加密功能放在存储过程中，那么若数据库被攻破，数据也就泄漏了。
存储过程会给数据库服务器增加额外的压力，而数据库服务器的性能扩展性相比应用服务器要差很多。
MySQL并没有什么选项可以控制存储过程的资源消耗，所以在存储过程中的一个小错误，可能直接把服务器拖死。
MySQL的存储代码功能还非常非常弱。
调试MySQL的存储过程是一件很困难的事情。
它和基于语句的二进制日志复制合作得并不太好。

存储过程和函数

MySQL的架构本身和优化器的特性使得存储代码有一些天然限制，他的性能也一定程度受限于此。

优化器无法使用关键字DETERMINISTIC来优化单个查询中多次调用存储函数的情况。
优化器无法评估存储函数的执行成本。
每个连接都有独立的存储过程的执行计划缓存。如果有多个连接需要调用同一个存储过程，将会浪费缓存空间来反复缓存同样的执行计划。
存储程序和复制是一组诡异的组合。最好不要复制对存储程序的调用。

我们通常会希望存储程序越小、越简单越好。希望将更加复杂的处理逻辑交给上层的应用实现，通常这样会使代码更易读、易维护，也会更灵活。

存储过程要快很多，很大程度因为它无须网络通信开销、解析开销和优化器开销等。

触发器

触发器可以让你在执行INSERT、UPDATE、或者DELETEA的时候，执行一些特定的操作。可以在MySQL中指定是在SQL语句执行之前触发还是在执行后触发。触发器本身没有返回值，不过他们可以读取或者改变触发SQL语句所影响的数据。

因为触发器可以减少客户端和服务器之间的通信，所以触发器可以简化应用逻辑，还可以提高性能。

MySQL触发器的实现非常简单，所以功能也有限。

对每一个表的每一个事件，最多只能定义一个触发器。
MySQL只支持"基于行的触发"——也就是说，触发器始终是针对一条记录的，而不是针对整个SQL语句的。如果变更的数据集非常大的话，效率会很低。
触发器可以掩盖服务器背后的工作，一个简单的SQL语句背后，因为触发器，可能包含了很多看不见的工作。
触发器的问题也很难排查，如果某个性能问题和触发器相关，会很难分析和定位。
触发器可能导致死锁和锁等待。如果触发器失败，那么原来的SQL语句也会失败。

因为性能的原因，很多时候无法使用触发器来维护汇总和缓存表。使用触发器而不是批量更新的一个重要原因就是，使用触发器可以保证数据总是一致的。

触发器并不能一定保证更新的原子性。一个触发器在更新MyISAM表的时候，如果遇到什么错误，是没有办法做回滚操作的。

在InnoDB表上的触发器是在同一个事物中完成的，所以它们执行的操作是原子的，原子操作和触发器操作会同时失败或者成功。不过，如果在InnoDB表上建触发器去检查数据的一致性，需要特别小心MVCC，稍不小心，你可能会获得错误的结果。你想实现外键约束，但是不打算使用InnoDB的外键约束。若打算编写一个BEFOREN INSERT触发器来检查写入的数据对应列在另一个表中是否是存在的，但若你在触发器中没有使用SELECT FOR UPDATE，那么并发的更新语句可能会立刻更新对应记录，导致数据不一致。

触发器非常有用，尤其是实现一些约束、系统维护任务、以及更新反范式化数据的时候。还可以使用触发器来记录数据变更日志。这对实现一些自定义的复制会非常方便。

事件

事件指定MySQL在某个时候执行一段SQL代码，或者每隔一个时间间隔执行一段SQL代码。通常，会把复杂的SQL都封装到一个存储过程中，这样事件在执行的时候只需要做一个简单的CALL调用。

事件在一个独立事件调度线程中被初始化，这个线程和处理连接的线程没有任何关系。它不接受任何参数，也没有任何的返回值。可以在MySQL的日志中看到命令的执行日志。还可以在表INFORMATION_SCHEMA.EVENTS中看到各个事件状态。

事件实现机制本身的开销并不大，但是事件需要执行SQL，则肯能会对性能有很大的影响。更进一步，事件和其他的存储程序一样，在和基于语句的复制一起工作时，可也能会触发同样的问题。事件的一些典型应用包括定期地维护任务、重建缓存、构建汇总表来模拟物化视图，或者存储用于监控和诊断的状态值。

在存储程序中保留注释

存储过程、存储函数、触发器、事件通常都会包含大量的重要代码，在这些代码中加上注释非常有必要。

一个将注释存储到存储程序中的技巧就是使用版本相关的注释，因为这样的注释可能被MySQL服务器执行。服务器和客户端都知道这不是普通的注释，所以也就不会删除这些注释。

游标

MySQL在服务器端提供只读、单向的游标，而且只能在存储过程或者更底层的客户端API中使用。因为MySQL游标中指向的对象都是存储在临时表中而不是实际查询到的数据，所以MySQL游标总是只读的。它可以逐行指向查询结果，然后让程序做进一步的处理。在一个存储过程中，可以有多个游标，也可以在循环中"嵌套"地使用游标。

因为是使用临时表实现的，所以它在效率上给开发人员一个错觉。当你打开一个游标的时候需要执行整个查询。

Oracle或者SQL Server的用户不会认为这个存储过程有什么问题，但是在MySQL中，这会带来很多不必要的额外操作。

游标也会让MySQL执行一些额外的I/O操作，而这些操作的效率可能非常低。因为临时内存表不支持BLOB和TEXT类型，如果游标返回的结果包含这样的列的话，MySQL就必须创建临时磁盘表来存放。当临时表大于tmp_table_size的时候，MySQL也还是会在磁盘上创建临时表。

绑定变量

当创建一个绑定变量SQL时，客户端向服务器发送了一个SQL语句的原型。服务器端收到这个SQL语句框架后，解析并存储这个SQL语句的部分执行计划，返回给客户端一个SQL语句处理句柄。以后每次执行这类查询，客户端都指定使用这个句柄。

绑定变量的SQL，使用问好标记可以接收参数的位置，当真正需要执行具体查询的时候，则使用具体值代替这些问号。

MySQL在使用绑定变量的时候可以更高效地执行大量的重复语句：

在服务器端只需要解析一次SQL语句。
在服务器端某些优化器的工作只需要执行一次，因为他会缓存一部分的执行计划。
以二进制的方式只发送参数和句柄，比起每次都发送ASCII码文本效率更高，一个二进制的日期字段只需要三个字节，但如果是ASCII码则需要十个字节。
仅仅是参数——而不是整个查询语句——需要发送到服务器端，所以网络开销会更小。
MySQL在存储参数的时候，直接将其存放到缓存中，不再需要再内存中多次复制。

绑定变量相对也更安全。无须再应用程序中处理转义，一则更简单了，二则也大大减少了SQL注入和攻击的风险。

绑定变量的优化

对使用绑定变量的SQL，MySQL能够缓存其部分执行计划，如果某些执行计划需要根据传入的参数来计算时，MySQL就无法缓存这部分的执行计划。

在准备阶段服务器解析SQL语句，移除不可能的条件，并且重写子查询。

在第一次执行的时候

如果可能的话，服务器先简化嵌套循环的关联，并将外关联转化成内关联。

在每次SQL语句执行时

服务器做以下事情

过滤分区
如果可能的话，尽量移除COUNT()、MIN()和MAX()
移除常数表达式
检测常量表
做必要的等值传播
分析和优化ref，range和索引优化等访问数据的方法
优化关联顺序。

SQL接口的绑定变量

MySQL支持了SQL接口的绑定变量。不使用二进制传输协议也可以直接以SQL的方式使用绑定变量。

最主要的用途就是在存储过程中使用。在MySQL5.0版本中，就可以在存储过程中使用绑定变量，其语法和前面介绍的SQL接口和绑定变量类似。这意味着，可以在存储过程中构建并执行"动态"的SQL语句，这里的"动态"是指可以通过灵活的拼接字符串等参数构建SQL语句。

编写存储过程时，SQL接口的绑定变量通常可以很大程度地帮助我们调试绑定变量，如果不是在存储过程中，SQL接口的绑定变量就不是那么有用了。因为SQL接口的绑定变量，它既没有使用二进制传输协议，也没有能够节省带宽，相反还总是需要增加至少一额外网络传输才能完成一次传输。

绑定变量的限制

绑定变量是会话级别的，所以连接之间不能共用绑定变量句柄。同样地，一旦连接断开，则原来的句柄也不能再使用了。
在MySQL5.1版本之前，绑定变量的SQL是不能使用查询缓存的。
并不是所有的时候使用绑定变量都能获得更好的性能。如果只是执行一次SQL，那么使用绑定变量方式无疑比直接执行多了一次额外的准备阶段消耗，而且还需要一次额外的网络开销。
当前版本下，还不能在存储函数中使用绑定变量。
如果总是忘记释放绑定变量资源，则在服务器端很容易发生资源"泄漏"。绑定变量SQL总数的限制是一个全局限制，所以某一个地方的错误可能会对所有其他的线程都产生影响。
有些操作，如BEGIN，无法在绑定变量中完成。

三种绑定变量的区别：

客户端模拟的绑定变量

客户端的驱动程序接收一个带参数的SQL，在将指定的值带入其中，最后将完整的查询发送到服务器端。

服务器端的绑定变量

客户端使用特殊的二进制协议将带参数的字符串发送到服务器端口，然后使用二进制协议将具体的参数值发送给服务器端并执行。

SQL接口的绑定变量

客户端先发送一个带参数的字符串到服务端，这类似于使用PREPARE的SQL语句，然后发送设置参数的SQL，最后使用EXECUTE来执行SQL。所有这些都使用普通的文本传输协议。

用户自定义函数

MySQL支持用户自定义函数(UDF)。存储过程只能使用SQL来编写，而UDF没有这个限制，你可以使用支持C语言调用约定的任何编程语言来实现。

UDF必须事先编译好并动态链接到服务器上，这种平台相关性使得UDF在很多方面都很强大。UDF速度非常快，而且可以访问大量操作系统的功能，还可以使用大量函数库。

能力越大，责任越大。所以在UDF中的一个错误很可能会让服务器直接崩溃，甚至扰乱服务器的内存或者数据，另外，所有C语言具有的潜在风险，UDF也都有。

插件

存储过程插件存储过程插件可以帮你在存储过程运行后在处理一次运行结果。
后台插件后台插件可以让你的程序在MySQL中运行，可以实现自己的网络监听、执行自己的定期任务。
INFORMATION_SCHEMA插件这个插件可以提供一个新的内存INFORMATION_SCHEMA表。
全文解析插件这个插件提供一种处理文本的功能，可以根据自己的需求来对一个文档进行分词，所以如果给定一个PDF文档目录，可以使用这个插件对这个文档进行分词处理。也可以用此来增强查询执行过程中的词语匹配功能。
审计插件审计插件在查询执行的过程中的某些固定点被调用，所以它可以用作记录MySQL的事件日志。
认证插件认证插件既可以在MySQL客户端也可以在它的服务端，可以使用这类插件来扩展MySQL的认证功能。

字符集和校对

字符集是指一种从二进制编码到某类字符符号的映射，可以参考如何使用一个字节表来表示英文字母。"校对"是指一组用于某个字符集的排序规则。MySQL4.1和之后的版本中，每一类编码字符都有其对应的字符集和校对规则。

#####MySQL如何使用字符集

每种字符集都可能有多种校对规则，并且都有一个默认的校对规则。每个校对规则都是针对某个特定的字符集的，和其他的字符集没有关系。校对规则和字符集总是一起使用。

只有基于字符的值才真正的"有"字符集的概念。对于其他类型的值，字符集只是一个设置，指定用哪一种字符集来做比较或者其他操作。

MySQL的设置可以分为两类：创建对象时的默认值、在服务器和客户端通信时的设置。

创建对象时的默认设置

MySQL服务器有默认的字符集和校对规则，每个数据库也有自己的默认值，每个表也有自己的默认值。这是一个逐层继承的默认设置，最终最靠底层的默认设置将影响你创建的对象。

创建数据库的时候，将根据服务器上的character_set_server，设置来设定该数据库的默认字符集。
创建表的时候，将根据数据库的字符集设置指定这个表的字符集设置。
创建列的时候，将根据表的设置指定列的字符集设置。

真正存放数据的是列，更高"阶梯"的设置只是指定默认值。一个表的默认字符集设置无法影响存储在这个表中某个列的值。只有当创建列而没为列指定字符集的时候，如果没有指定字符集，表的默认字符集才有作用。

服务器和客户端通信时的设置

当服务器和客户端通信的时候，它们可能使用不同的字符集。服务器端将进行必要的翻译转换工作：

服务器端总是假设客户端是按照character_set_client设置的字符来传输数据额SQL语句的。
当服务器收到客户端的SQL语句时，它先将其转换成字符集character_set_connection。它还使用这个设置来决定如何将数据转换成字符串。
当服务器端返回数据或者错误信息给客户端时，它会将其转换成character_set_result。

根据需要，可以使用SET NAMES或者SET CHARACTER SET语句来改变上面的设置。不过在服务器上使用这个命令只能改变服务器端的设置。客户端程序和客户端的API也需要使用正确的字符集才能避免在通信时出现问题。

MySQL如何比较两个字符串的大小

如果比较的两个字符串的字符集不同，MySQL会先将其转成同一个字符集再进行比较。如果两个字符集不兼容的话，则会抛出错误。MySQL5.0和更新的版本经常会做这样的瘾式转换。

一些特殊情况

诡异的character_set_database设置 character_set_database设置的默认值和默认数据库的设置相同。当改变默认数据库的时候，这个变量也会跟着变。
LOAD DATA INFINE 当使用LOAD DATA INFINE的时候，数据库总是将文件中的字符按照字符集character_set_database来解析。在MySQL5.0和更新版本中，可以在LOAD DATA INFINE中使用子句CHARACTER SET来设定子集，不过最好不要依赖这个设定。我们发现制定字符集最好的方式是先使用USE指定数据库，在执行SET NAMES来设定字符集，最后再加载数据。MySQL在加载数据的时候，总是以同样的字符集处理所有数据，而不管表中的列是否有不同的字符集设定。
SELECT INTO OUTFILE MySQL会将SELECT INTO OUTFILE的结果不做任何转码地写入文件。
嵌入式转义序列 MySQL会跟据character_set_client的设置来解析转义序列，即使是字符串中包含前缀或者COLLATE子句也一样。这是因为解析器在处理字符串的转义字符时，完全不关心校对规则——对解析器来说，前缀并不是一个指令，它只是一个关键字而已。

选择字符集和校对规则

对于校对规则通常需要考虑的一个问题是，是否以大小写敏感的方式比较字符串，或者是以字符串编码的二进制值来比较大小。他们对应的校对规则的前缀分别是_cs、_ci和_bin，根据需要很容易选择。大小写敏感和二进制校对规则的不同之处在于，二进制校对规则直接使用字符的字节进行比较，而大小写敏感的校对规则在多字节字符集时，有更复杂的比较规则。

字符集和校对规则如何影响查询

某些字符集和校对规则可能会需要更多的CPU操作，可能会消耗更多的内存和存储空间，甚至还会影响索引的正常使用。

全文索引

全文索引可以支持各种字符内容的搜索(包括CHAR、VARCHAR和TEXT类型)，也支持自然语言搜索和布尔搜索。标准的MySQL中，只有MyISAM引擎支持全文索引。在MySQL5.6中，InnoDB已经实验性质的支持全文索引了。MyISAM对全文索引的支持有很多限制，例如表级别锁对性能的影响、数据文件的崩溃、崩溃后的恢复等。

MyISAM的全文索引是一类特殊的B-Tree索引，共有两层。第一层是所有关键字，然后对于每一个关键字的第二层，包含的是一组相关的"文档指针"。

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

自然语言的全文索引

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。

全文索引的语法和普通查询略有不同。可以根据WHERE子句中的MATCH AGAINST来区分查询是否使用全文索引。

函数MATCH()将返回关键词匹配的相关度，是一个浮点数字。在一个查询中使用两次MATCH()函数并不会有额外的消耗，MySQL会自动识别并只进行一次搜索。如果将MATCH()函数放到ORDER BY子句中，MySQL将会使用文件排序。

在MATCH()函数中指定的列必须和在全文索引中指定的列完全相同，否则就无法使用全文索引。这是因为全文索引不会记录关键字是那一列的。

布尔全文索引

短语搜索的速度会比较慢。只使用全文索引是无法判断是否精确匹配短语的，通常还需要查询原文确定记录中是否包含完整的短语。由于需要进行回表过滤，所以速度会很慢。

MySQL5.1中全文索引的变化

在MySQL5.1Z中引入了一些和全文索引相关的改进，包括一些性能上的提升和新增插件式的解析。

全文索引的限制和替代方案

MySQL全文索引中只有一种判断相关性的方法：词频。索引也不会记录索引词在字符串中的位置，所以位置也就无法用在相关性上。

数据量大小也是一个问题。MySQL的全文索引只有全部在内存中的时候，性能才非常好。如果内存无法装载全部索引，那么搜索速度可能会非常慢。当你使用精确短语搜索时，想要好的性能，数据和索引都需要在内存中。相比其他的索引类型，当INSERT、UPDATE和DELETE操作进行时，全文索引的操作代价都很大：

修改一段文本中的100个单词，需要100次索引操作，而不是一次。
一般来说列长度并不会太影响其他的索引类型，但是如果是全文索引，三个单词的文本和10000个单词的文本，性能可能会相差几个数量级。
全文索引会有更多的碎片，可能需要做更多的OPTIMIZE TABLE操作。

全文索引还会影响查询优化器的工作。索引选择、WHERE子句、ORDER BY都有可能不是按照你所预想的方式来工作

如果查询中使用了MATCH AGAINST子句，而对应列上又有可用的全文索引，那么MySQL就一定会使用这个全文索引。即使有其他的索引，MySQL不会去比较哪个索引的性能更好。所以，即使这时有更合适的索引可以使用，MySQL仍会置之不理。
全文索引只能用作全文搜索匹配。任何其他操作，如WHERE条件比较，都必须在MySQL完成全文搜索返回记录后才能进行。
全文索引不存储索引列的实际值。也就不可能用作索引覆盖扫描。
除了相关性排序，全文索引不能用作其他的排序。如果查询需要做相关性以外的排序操作，都需要使用文件排序。

假如有一百万个文档记录，在文档的作者author字段上有一个普通的索引，在文档内容字段content上有全文索引。现在我们要搜索作者是123，文档中又包含特定词语的文档。很多人可能会按照下面的方式来写查询语句：

...... WHERE MATCH(content) AGAINST('High Performance MySQL') AND author = 123;

而实际上，这样做的效率非常低。因为这里使用了MATCH AGAINST，而且恰好上面有全文索引，所以MySQL优先选择使用全文索引，即先搜索所有的文档，查找是否有包含关键词的文档，然后返回记录看看作者是否是123。所以这里也就没有使用author字段上的索引。

一个代替方案是将author列包含到全文索引中。可以在author列的值前面附上一个不常见的前缀，然后将这个带前缀的值存放到一个单独的filters列中，并单独维护该列。

...... WHERE MATCH(content,filters) AGAINST('High Performance MySQL + author_id_123' IN BOOLEAN MODE);

如果author列的选择性非常高，那么MySQL能够根据作者信息很快地将需要过滤的文档记录限制在一个很小的范围内，这个查询的效率也就非常好。如果author列的选择性很低，那么这个替代方案的效率会比前面那个更糟。

使用全文索引的时候，通常会返回大量结果并产生大量随机I/O，如果和GROUP BY一起使用的话，还需要通过临时表或者文件进行排序分组，性能会非常非常糟糕。

全文索引的配置和优化

全文索引的日常维护能够大大提升性能。"双B-Tree"的特殊结构、在某些文档中比其他文档要包含多得多的关键字，这都使得全文索引比起普通索引有更多的碎片问题。所以需要经常使用OPTIMIZE TABLE来减少碎片。如果应用是I/O密集型的，那么定期的进行全文索引重建可以让性能提升很多。

如果希望全文索引能够高校地工作，还需要保证索引缓存足够大，从而保证所有的全文索引都能缓存在内存中。通常，可以为全文索引设置单独的键缓存，保证不会被其他的缓存缓存挤出内存。

提供一个好的停用词。默认的停用词表对常用英语来说可能很不错，但是如果是其他语言或者某些专业文档就不合适了，例如技术文档。

忽略一些太短的单词也可以提升全文索引的效率。索引单词的最小长度可以通过参数ft_min_word_len配置。修改该参数可以过滤更多的单词，让查询速度更快，但是也会降低精确度。

当向一个有全文索引的表中导入大量数据的时候，最好先通过命令DISABLE KEYS来禁用全文索引，然后在导入结束后使用ENABLE KEYS来建立全文索引。因为全文索引的更新是一个消耗很大的操作，所以上面的细节会帮你节省大量时间。

如果数据集特别大，则需要对数据进行手动分区，然后将数据分布到不同的节点，再做并行的搜索。

分布式(XA)事物

存储引擎的事物特性能够保证在存储引擎级别实现ACID，而分布式事务则让存储引擎级别的ACID可以扩展到数据库层面，甚至可以扩展到多个数据库之间——这需要通过两阶段提交实现。MySQL5.0和更新版本的数据库已经开始支持XA事务了。

XA事务中需要一个事务协调器来保证所有的事务参与者都完成了准备工作。如果协调器收到所有的参与者都准备好的消息，就会告诉所有的事务可以提交了。

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

内部XA事物

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

XA事务为MySQL带来巨大的性能下降。从MySQL5.0开始，它破坏了MySQL内部的"批量提交"，使得MySQL不得不进行多次额外的fsync()调用。

外部XA事物

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

查询缓存

MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存，MySQL会立刻返回结果，跳过了解析、优化和执行阶段。

查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生变化，那么和这个表相关的所有的缓存数据都将失效。这种机制效率看起来比较低，因为数据表变化时很有可能对应的查询结果并没有更改，但是这种简单实现代价很小，而这点对于一个非常繁忙的系统来说非常重要。

查询缓存对应用程序是完全透明的。应用程序无须关系MySQL是通过查询缓存返回的结果还是实际执行返回的结果。

随着现在的通用服务器越来越强大，查询缓存被发现是一个影响服务器扩展性的因素。它可能成为整个服务器的资源竞争单点，在多核服务器上还可能导致服务器僵死。

MySQL如何判断缓存命中

MySQL判断缓存命中的方法很简单：缓存存放在一个引用表中，通过一个哈系值引用，这个哈希值包括了如下因素，即查询本身、当前要查询的数据库、客户端协议的版本等一些其他可能会影响返回结果的信息。

当判断缓存是否命中时，MySQL不会解析、"正视化"或者参数化查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。任何字符上的不同都会导致缓存的不命中。

当查询语句中有一些不确定的数据时，则不会被缓存。如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，或者任何包含列级别权限的表，都不会被缓存。

在检查查询缓存的时候，还没有解析SQL语句，所以MySQL并不知道查询语句中是否包含这类函数。在检查查询缓存之前，MySQL只做一件事情，就是通过一个大小写不敏感的检查看看SQL语句是不是以SEL开头。

如果查询语句中包含任何的不确定函数，那么在查询缓存中是不可能找到缓存结果的。

打开查询缓存对读和写操作都会带来额外的消耗：

读查询在开始之前必须先检查是否命中缓存。
如果这个读查询可以被缓存，那么当完成执行后，MySQL若发现查询缓存中没有这个查询，会将其结果存入查询缓存，这会带来额外的系统消耗。
这对写操作也会有影响，因为当向某个表写入数据的时候，MySQL必须将对应表的所有缓存都设置失效。

如果查询缓存使用了大量的内存，缓存失效操作就可能成为一个非常严重的问题瓶颈。如果缓存中存放了大量的查询结果，那么缓存失效操作时整个系统都可能会僵死一会儿。因为这个操作是靠一个全局锁操作保护的，所有需要做该操作的查询都要等待这个锁，而且无论是检测是否命中缓存、还是缓存失效检测都需要等待这个全局锁。

查询缓存如何使用内存

MySQL用于查询缓存的内存被分成一个个的数据快，数据块是变长的。没一个数据块中，存储了自己的类型、大小和存储的数据本身，还外加指向前一个和后一个数据块的指针。数据块的类型有：存储查询结果、存储查询、和数据表的映射、存储查询文本等等。

当有查询结果需要缓存的时候，MySQL先从大的空间块中申请一个数据块用于存储结果。这个数据块需要大于参数query_cache_min_res_unit的配置，即使查询结果远远小于此，仍需要至少申请query_cache_min_res_unit空间。因为需要在查询开始返回结果的时候就分配空间，而此时是无法预知查询结果到底多大，所以MySQL无法为每一个查询结果精确分配大小恰好匹配的缓存空间。

当需要缓存一个查询结果的时候，它先选择一个尽可能小的内存块，然后将结果存入其中。如果数据块全部用完，但仍有剩余数据需要存储，那么MySQL会申请一块新数据块——仍然是尽可能小的数据块——继续存储结果数据。当查询完成时，如果申请的内存空间还有剩余，MySQL会将其释放，并放入空闲内存部分。

分配内存块不是指通过函数malloc()向操作系统申请内存，这个操作只在初次创建查询缓存的时候执行一次。分配内存块是指空闲块列表中找到一个合适的内存块，或者从正在使用的、待淘汰的内存块中回收再使用。也就是说，这里MySQL自己管理一大块内存，而不依赖操作系统的内存管理。

什么情况下查询缓存能发挥作用

并不是什么情况下查询缓存都会提高系统性能的。缓存和失效都会带来额外的消耗，所以只有当缓存带来的资源节约大于其本身的资源消耗时才会给系统带来性能提升。

理论上，可以通过观察打开或者关闭查询缓存时候的系统效率来决定是否需要开启查询缓存。关闭查询缓存时，每个查询都需要完整的执行，每一次写操作执行完成后立刻返回；打开查询缓存时，每次读请求先检查缓存是否命中，如果命中则立刻返回，否则就完整的执行查询，每次写操作则需要检查查询缓存中是否需要失效的缓存，然后在返回。

评估打开查询缓存是否能够带来性能提升却并不容易。还有一些外部的因素需要考虑，例如查询缓存可以降低查询执行的时间，但是却不能减少查询结果传输的网络消耗，如果这个消耗是系统的主要瓶颈，那么查询缓存的作用也很小。

MySQL在SHOW STATUS中只能提供一个全局的性能标准，所以很难根据此来判断查询缓存是否能够提升性能。

对于那些需要消耗大量资源的查询通常都是非常适合缓存的。不过需要注意的是，涉及的表上UPDATE、DELETE和INSERT操作相比SELECT来说要非常少才行。

一个判断查询缓存是否有效的直接数据就是命中率，就是使用查询缓存返回结果占总查询的比率。Qcache_hits/(Qcache_hits+Com_select)。

查询语句无法被缓存，可能是因为查询中包含一个不确定的函数，或者查询结果太大而无法缓存。
MySQL从未处理这个查询，所以结果也从不曾被缓存过。
之前缓存了查询结果，但是由于查询缓存的内存用完了，MySQL需要将某些缓存"逐出"，或者由于数据表被修改导致缓存失效。
查询缓存还没有完成预热。也就是说，MySQL还没有机会将查询结果都缓存起来。
查询语句之前从未执行过。如果你的应用程序不会重复执行一条查询语句，那么即使完成预热仍然会有很多缓存未命中。
缓存失效操作太多了缓存碎片、内存不足、数据修改都会造成缓存失效。如果配置了足够的缓存空间，而且query_cache_min_res_unit设置也合理的话，那么缓存失效应该主要是数据修改导致的。

"命中率"和"INSERTS和SELECT比率"都无法直观地反应查询缓存的效率。另一个指标："命中和写入"比率，即Qcache_hits和Qcache_inserts的比值。根据经验来看，这个比值大于3:1的通常查询缓存是有效的，不过这个比率最好能够达到10：1。

通常可以通过观察查询缓存内存的实际使用情况，来确定是否需要缩小或者扩大查询缓存。如果查询缓存空间长时间都有剩余，那么建议缩小；如果经常由于空间不足而导致查询缓存失效，那么则需要增大查询缓存。另外还需要和系统的其他缓存一起考虑。

最好的判断查询缓存是否有效的办法还是通过查看某类查询时间消耗是否增大或者减少来判断。

如何配置和维护查询缓存

query_cache_type 是否打开查询缓存。可以设置成OFF、ON或DEMAND。DEMAND表示只有在查询语句中明确写明SQL_CACHE的语句才放入查询缓存。
query_cache_size 查询缓存使用的总内存空间，单位是字节。这个值必须是1024的整数倍。
query_cache_min_res_unit 在查询缓存中分配内存块时的最小单位。
query_cache_limit MySQL能够缓存的最大查询结果。
query_cache_wlock_invalidate如果某个数据表被其他的连接锁住，是否仍然从查询缓存中返回结果。

减少碎片 提高查询缓存的使用率