5. MySQL高级特性

5.1 分区表

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

5.1.1 分区表的原理

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

5.1.2 分区表的类型

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

5.1.3 如何使用分区表

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

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

5.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语句的操作，速度会比较慢，因为需要复制数据。

分区实现中的一些其他限制：

所有分区都必须使用相同的存储引擎
分区函数中可以使用的函数和表达式也有一些限制
某些存储引擎不支持分区
对于MyISAM的分区表，不能再使用LOAD INDEX INTO CACHE操作
对于MyISAM表，使用分区表时需要打开更多的文件描述符。每一个分区对于存储引擎来说都是一个独立的表，即使分区表只占用一个表缓存条目，文件描述符还是需要多个。

5.1.5 查询优化

访问分区表，需在WHERE条件中带入分区列，即使有时候看似多余，这样就可以让优化器过滤掉无须访问的分区。
- MySQL只能在使用分区函数的列的本身进行比较才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式就是分区函数也不行。这和查询中使用独立的列才能使用索引的道理是一样的。
```
-- 无法使用分区
mysql> EXPLAIN PARTITIONS SELECT * FROM sales_by_day WHERE YEAR(day) = 2010\G;
-- 可使用分区
mysql> EXPLAIN PARTITIONS SELECT * FROM sales_by_day
-> WHERE day BETWEEN '2010-01-01' AND '2010-12-31'\G;
```
优化器在处理查询的过程中总是尽可能聪明地去过滤分区。例如，若分区表是关联操作中的第二张表，且关联条件是分区键，MySQL就只会在对应的分区里匹配行。(EXPLAIN无法显示这种情况下的分区过滤，因为这是运行时的分区过滤，而不是查询优化阶段的)

5.1.6 合并表

合并表是一种早期的、简单的分区实现，和分区表相比有一些不同的限制，并且缺乏优化。合并表允许用户单独访问各个子表。分区表是未来的发展趋势，合并表是一种将被淘汰的技术，在未来版本可能会被删除，在这里不做过多阐述。

5.2 视图

视图本身是一个虚拟表，不存放任何数据。在使用SQL语句访问视图的时候，它返回的数据是MySQL从其他表生成的。
- 视图和表是在同一个命名空间，MySQL在很多地方和表是同样对待的。不过不能对视图创建触发器，也不能使用DROP TABLE命令删除视图。
- MySQL5.0版本之后开始引进。
- 个人理解：视图不会对查询产生任何优化，只是对结果进行一个更好的展示，因为其底层的原理是查询原有的表。某些情况下可以帮助提升性能。
工作原理:
```
-- 实现视图最简单的办法是将SELECT语句的结果存放到临时表中。
mysql> CREATE VIEW Oceania AS
-> SELECT * FROM Country WHERE Continent = 'Oceania'
-> WITH CHECK OPTION;
-- 当需要访问视图的时候，可直接访问这个临时表
mysql> SELECT Code, Name FROM Oceania WHERE Name = 'Australia';
-- MySQL使用的并算法：重写含有视图的查询，将视图的定义SQL直接包含进查询的SQL中：
mysql> SELECT Code, Name FROM Country
-> WHERE Continent = 'Oceania' AND Name = 'Australia';
-- MySQL 使用的临时表算法，以下SQL是为展示用的。这样做会有明显的性能问题，优化器也很难优化在这个临时表上的查询。
mysql> CREATE TEMPORARY TABLE TMP_Oceania_123 AS
-> SELECT * FROM Country WHERE Continent = 'Oceania';
mysql> SELECT Code, Name FROM TMP_Oceania_123 WHERE Name = 'Australia';
```
MySQL使用合并算法 和临时表算法 来处理视图。如果可能，尽可能使用合并算法。
- MySQL甚至可以嵌套地定义视图，也就是在一个视图上再定义另一个视图。
- 可以在EXPLAIN EXTENDED之后使用SHOW WARNINGS来查看使用视图的查询重写的结果
  - 如果是采用临时表算法实现的视图，EXPLAIN中的select_type会显示为派生表(DERIVED)。如果产生的底层派生表很大，那么执行EXPLAIN可能会非常慢。因为在5.5及之前的版本中，EXPLAIN是需要实际执行并产生派生表的。
两种算法的实现细节：

视图的两种实现

使用临时表算法实现视图的场景：
- 视图中包含GROUP BY、DISTINCT、任何聚合函数、UNION、子查询等，只要无法在原表记录和视图记录中建立一一映射的场景。
视图的实现算法是视图本身的属性，和作用在视图上的查询语句无关。例如，可以为一个基于简单查询的视图制定使用临时表算法：CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM sakila.actor;，这样无论基于执行什么样的查询，视图都会生成一个临时表。

5.2.1 可更新视图

可更新视图(updatable view)是指可以通过更新这个视图来更新视图涉及的相关表
- 只要指定了合适的条件，就可以更新、删除甚至向视图中写入数据
- 更新视图的查询也可以是一个关联语句，但是被更新的列必须来自同一个表中
- 所有使用临时表算法实现的视图都无法被更新
- CHECK OPTION子句表示任何通过视图更新的行，都必须符合视图本身的WHERE条件定义。所以不能更新视图以外的列
- MySQL不支持在视图上建任何触发器。某些关系数据库允许在视图上简历INSTEAD OF触发器来精确控制在修改视图数据时做些什么。

5.2.2 视图对性能的影响

某些情况下视图也可以帮助提升性能，而且视图还可以和其他提升性能的方式叠加使用。

提升性能的应用场景：
- 在重构schema的时候，使得在修改视图底层表结构的时应用代码还可能继续不报错的运行
- 实现基于列的权限控制，却不需要真正的系统中创建权限，因此没有任何额外的开销
- 使用伪临时视图：
  - MySQL虽然不能创建只在当前链接中存在的真正的临时视图，但是可以建一个特殊名字的视图，然后在连接结束的时候删除该视图。这样在连接过程中就可以在FROM子句中使用这个视图，MySQL处理视图和子查询的代码路径完全不同，所以它们的性能也不同
  - 可以使用连接ID作为视图名字的一部分来避免冲突。在应用发生崩溃和别的意外导致未清理临时视图的时候，这个技巧使得清理临时视图变得更简单。
- 使用临时表算法实现的视图，在某些时候性能查询会很糟糕(虽然可能比直接使用等效查询语句更好一点)
  - MySQL以递归的方式执行这类视图，先会执行外层查询，即使外层查询优化器将其优化得很好，但是MySQL优化器可能无法像其他的数据库那样做更多的内外结合的优化。外层查询的WHERE条件无法“下推”到构建视图的临时表的查询中，临时表也无法建立索引。
注意视图背后的复杂性，可能它引用了很多表。如果打算使用视图来提升性能，需要做比较详细的测试。即使是合并算法实现的视图也会有额外开销，而且视图性能很难预测。因为在MySQL的优化器中，视图的代码执行路径也完全不同，这部分代码测试还不够全面，可能会有一些隐藏缺陷和问题，所以目前的视图还不是那么成熟。

5.2.3 视图的限制

MySQL还不支持物化视图(指视图结果数据存放在一个可以查看的表中，并定期从原始表刷新数据到这个表)，也不支持在视图中创建索引。可以使用构建缓存表或者汇总表的办法来模拟物化视图和索引
MySQL并不会保存视图定义的原始SQL语句，所以不能通过执行SHOW CREATE VIEW后再简单地修改其结果的方式来重新定义视图。
- 如果打算修改视图，并且没法找到视图的原始的创建语句的话，可以通过使用视图.frm文件最后一行获取一些信息。如果有FILE权限，甚至可直接使用LOAD_FILE()来读取.frm中的视图创建信息，在加上一些字符处理工作。
```
mysql> SELECT
-> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
-> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
-> SUBSTRING_INDEX(LOAD_FILE('/var/lib/mysql/world/Oceania.frm'),
-> '\nsource=', −1),
-> '\\_','\_'), '\\%','\%'), '\\\\','\\'), '\\Z','\Z'), '\\t','\t'),
-> '\\r','\r'), '\\n','\n'), '\\b','\b'), '\\\"','\"'), '\\\'','\''),
-> '\\0','\0')
-> AS source;
```

5.3 外键约束

InnoDB是目前MySQL中唯一支持外键的内置存储引擎。
使用外键是有成本的。
- 比如外键通常都要求每次在修改数据时都要在另外一张表中多执行一次查找操作。虽然InnoDB强制外键使用索引，但还是无法消除这种约束检查的开销。如果外键列的选择性很低，则会导致一个非常大且选择性很低的索引。
某些场景下外键会提升一些性能：
- 如果想确保两个相关表始终有一致的数据，那么使用外键比在应用程序中检查一致性的性能要高得多。
- 外键在相关数据的删除和更新上，也比在应用中维护要更高效，不过，外键维护操作是逐行进行的，所以这样的更新会比批量删除和更新要慢。
外键约束使得查询需要额外访问一些别的表，这也意味着需要额外的锁。
- 如果向子表中写入一条记录，外键约束会让InnoDB检查对应的父表的记录，也就需要对父表对应记录进行加锁操作，来确保这条记录不会在这个事务完成之时就被删除了。这会导致额外的锁等待，甚至会导致一些死锁。因为没有直接访问这些表，所以这类死锁问题往往很难排除。
有时，可以使用触发器来代替外键，对于相关数据的同时更新外键更合适，但是如果外键只是用作数值约束，那么触发器或者显式地限制取值会更好些(这里，可以直接使用ENUM类型)
如果只是用外键做约束，那通常在应用程序里实现该约束会更好。外键会带来很大的额外消耗。

5.4 在MySQL内部存储代码(暂时无用，简单介绍)

在未来一段时间还不会用到，需要用到再看，感觉更适合DBA，这里只列举常用的方式。

5.4.1 存储过程和函数

5.4.2 触发器

可以在执行INSERT、UPDATE或者DELETE的时候，执行一些特定的操作。可以在MySQL中指定是在SQL语句执行前触发还是在执行后触发。

5.4.3 事件

类似于LINUX的定时任务，不过完全是在MySQL内部实现。

5.4.4 在存储过程中保留注释

5.5 游标(暂时无用，简单介绍)

MySQL在服务器中提供只读的、单向的游标，而且只能在存储过程或者更底层的客户端API中使用。因为游标中指向的对象都是存储在临时表中而不是实际查询到的数据，所以MySQL游标总是可读的。

5.6 绑定变量

绑定变量的SQL语句：INSERT INTO tbl(col1, col2, col3) VALUES (?, ?, ?);。绑定变量的SQL，使用问号标记可以接收参数的位置，当真正需要执行具体查询的时候，则使用具体值代替这些问号。
当创建一个绑定变量SQL时，客户端(如C或JAVA等)向服务器发送了一个SQL语句的原型。服务器端收到这个SQL语句的框架后，解析并存储这个SQL语句的部分执行计划，返回给客户端一个SQL语句处理句柄。以后每次执行这类查询，客户端都制定使用这个句柄。
MySQL在使用绑定变量的时候可以更高效地执行大量重复语句的原因：
- 在服务器端只需要解析一次SQL语句。
- 在服务器端某些优化器的工作只需要执行一次，因为它会缓存一部分的执行计划。
- 以二进制的方式只发送参数和句柄，比起每次发送ASCII码文本效率更高。不过最大的节省还是来自于BLOB和TEXT字段，绑定变量的形式可以分块传输，而无须一次性传输。二进制协议在客户端也可以节省很多内春，减少了网络开销，还节省了将数据从存储原始格式转换成文本格式的开销。
- 仅仅是参数而不是整个查询语句需要发送到服务器端，所以网络开销会更小。
- MySQL在存储参数的时候，直接将其存放到缓存中，不再需要在内存中多次复制。
绑定变量相对也更加安全。无须在应用程序中处理转义，一则更简单明了，二则也大大减少了SQL注入和攻击的风险。

5.6.1 绑定变量的优化

理论上有些优化器只需要做一次，但实际上，下面的操作还是都会被执行。根据优化器什么时候工作，可以将优化分为三类：

在准备阶段：服务器解析SQL语句，移除不可能的条件，并重写子查询
在第一次执行的时候：如果可能的话，服务器先简化嵌套循环的关联，并将外关联转换成内关联
在每次SQL语句执行时，服务器做如下事情：
- 过滤分区
- 如果可能的话，尽量移除COUNT()、MIN()和MAX()
- 移除常数表达式
- 检测常量表
- 做必要的等值传播
- 分析和优化ref、range和索引优化等访问数据的方法。
- 优化关联顺序。

5.6.2 SQL接口的绑定变量(暂时无用，简单介绍)

最主要的用途就是在存储过程中使用。

5.6.3 绑定变量的限制

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

三种绑定变量类型的部分区别：

客户端模拟的绑定变量：客户端的驱动程序接收到一个带参数的SQL，再将指定的值带入其中，最后将完整的查询发送到服务器端。
服务器端的绑定变量：客户端使用特殊的二进制协议将带参数的字符串发送到服务器端，然后使用二进制协议将具体的参数值发送给服务器端执行。
SQL接口的绑定变量：客户端先发送一个带参数的字符串到服务器端，这类似与使用PREPARE的SQL语句，然后发送设置参数的SQL，最后使用EXECUTE来执行SQL。所有这些都是用普通的文本传输协议。

5.7 用户自定义函数(暂时无用，简单介绍)

使用支持C语言调用约定的任何编程语言来实现用户自定义函数(UDF)。UDF必须事先编译后并动态链接到服务器上。

5.8 插件(暂时无用，简单介绍)

插件类型：

存储过程插件
后台插件
INFORMATION_SCHEMA插件
全文解析插件
审计插件
认证插件

5.9 字符集和校对

字符集是指一种从二进制编码到某类字符符号的映射，可以参考如何使用一个字节来表示英文字符。校对是指一组用于某个字符集的排序规则。

5.9.1 MySQL如何使用字符集

每种字符集都可能有多种校对规则，并且都有一个默认的校对规则，每个校对规则都是针对某个特定的字符集，因此把字符集和校对规则统称为字符集。

MySQL有很多选择用于控制字符集，这些选项和字符集很容易混淆。只有基于字符的值才真正的有字符集的概念。对于其他类型的值，字符集只是一个设置，指定用哪种字符集来做比较或者其他操作。

MySQL的设置：

创建对象时的默认设置：
- 创建数据库的时候，将根据服务器上character_set_server设置来设定该数据库的默认字符集
- 创建表的时候，将根据数据库的字符集设置指定这个表的字符集设置
- 创建列的时候，将根据表的设置指定列的字符集设置。
- 以上三个阶层，每一层都只是指定一个默认值，当这一层没有指定字符集的时候，默认值才会生效。
服务器和客户端通信时的设置：
- 服务器和客户端通信的时候，他们可能使用不同的字符集。这时，服务器端将进行必要的翻译转换工作：
  - 服务器端总是假设客户端是按照character_set_client设置的字符来传输数据和SQL语句的。
  - 当服务器收到客户端的SQL语句时，它先将其转换成字符集character_set_connection。它还是用这个设置来决定如何将数据转换成字符串。
  - 当服务器端返回数据或者错误信息给客户端时，它会将其转换成character_set_result。
  客户端和服务器的字符集
- 根据需要，可以使用SET NAMES或者SET CHARACTER语句来改变上面的设置。不过在服务器上使用这个命令只能改变服务器端的设置。客户端程序和客户端的API也需要使用正确的字符集才能避免在通信时出现问题。

MySQL比较两个字符串的大小时，通过将其转换成同一个字符集再进行比较，如果两个字符集不兼容的话，则会抛出错误。MySQL还会为每个字符串设置一个“可转换性”，这个设置决定了值的字符集的优先级，因而会印象MySQL做字符集隐式转换后的值。

还可以使用前缀和COLLATE子句来指定字符串的字符集或者校对字符集。
```
mysql> SELECT _utf8 'hello world' COLLATE utf8_bin;
```

一些特殊情况：

诡异的character_set_database设置：当改变默认数据库的时候，这个变量也会跟着改变。
LOAD DATA INFILE：数据库总是将文件中的字符按照字符集character_set_database来解析
SELECT INTO OUTFILE：将结果不做任何转码地写入文件
嵌入式转义序列：MySQL会根据character_set_client的设置来解析转义序列，即使字符串中包含前缀或者COLLATE子句也一样。因为对解析器来说，前缀并不是一个指令，只是一个关键字。

5.9.2 选择字符集和校对规则

可以使用命令SHOW CHARACTERSET和SHOW COLLATION来查看MYSQL支持的字符集和校对规则。
极简原则：最好先为服务器或者数据库选择一个合理的字符集，然后根据不同的实际情况，让某些列选择合适的字符集。
对于校对规则通常需要考虑的一个问题是，是否以大小写敏感的方式比较字符串，或者是以字符串编码的二进制值来比较大小。二进制校对规则直接使用字符的字节进行比较，而大小写敏感的校对规则在多字节字符集时如德语有更复杂的比较规则。
MySQL如何选择字符集和校对规则:

MySQL如何选择字符集和校对规则

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

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

不同的字符集和校对规则之间的转换可能会带来额外的系统开销。
- 只有排序查询要求的字符集与服务器数据的字符集相同的时候，才能使用索引来排序。索引根据数据列的校对规则进行排序。
MySQL会在需要的时候进行字符集转换：
- 当时用两个字符集不同的列来关联两个表的时候，MySQL会尝试转换其中一个列的字符集。
UTF-8是一种多字节编码，它存储一个字符会使用变成的字节数。在MySQL内部，通常使用一个定长的时间来存储字符串，在进行相关操作，这样的目的是希望总是保证缓存中有足够的空间来存储字符串。
- 在多字节字符集中，一个字符不再是一个字节。可以用LENGTH()和CHAR_LENGTH()来计算字符串的长度。在多字节字符集中，两者返回的结果会不同，因此要使用后者
- 如果要索引一个UTF-8字符集的索引，MySQL会假设每一个字符都是三个字节，索引最长索引前缀的限制一下缩短到原来的三分之一。对MySQL使用索引有一些影响，比如无法使用索引覆盖扫描。
- 如果全部直接使用UTF-8字符集，从性能角度来并不好，只会消耗更多的存储空间，因为很多应用无须使用该字符集。
考虑字符集需要根据存储的具体内存来决定：
- 存储的内容主要是英文字符，可以使用UTF-8，因为其只占用一个字节
- 存储一些非拉丁语系的字符，可以使用cpl256
- 存储别的语言，使用UTF-8。
- 当从某个具体的语种编码转成UTF-8时，存储空间的使用会相对增加。如果使用的是InnoDB表，那么字符集的改变可能会导致数据的大小超过可以在页内存储的临界值，需要保存在额外的外部存储区，这会导致严重的空间浪费和空间碎片。
有时候根本不需要使用任何的字符集。通常只有在做大小写无关的比较、排序、字符串操作的时候才需要使用字符集。如果数据库不关心字符集，那么可以直接将所有东西存储到二进制列中，包括UTF-8编码数据。这么做可能还需要一个列记录字符的编码集，导致很多难以排除的错误。因此如果可能建议尽量不要这么做。

5.10 全文索引(暂时无用，简单介绍)

全文索引有着自己独特的语法，没有索引也可以工作，如果有索引效率会更高。
全文索引可以支持各种字符内容的搜索，也支持自然语言搜索和布尔搜索。
只有在MyISAM引擎支持，5.6版本后的InnoDB也已经实验性质的支持
- MyISAM的全文索引是一种特殊的B-Tree索引，共有两层。第一层是所有关键字，然后对于每一个关键字的第二层，包含的是一组相关的“文档指针”

5.10.1 自然语言的全文索引

计算每一个文档对象和查询的相关度。相关度是基于匹配的关键词个数，以及关键词在文档中出现的个数。在整个索引中出现次数越少的词语，匹配的相关度就越高，相反非常常见的单词就不会被搜索。

5.10.2 布尔全文索引

可以在查询中自定以某个被搜索词语的相关性。布尔搜索通过停用词列表过滤掉那些噪声词，另外还要求搜索的关键词长度必须大于ft_min_word_len并小于ft_max_word_len。搜索返回的结果是未经排序的。

5.10.3 MySQL5.1中全文索引的变化

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

限制：
- 只有一种影响相关性的方法：词频
- 数据量大小
- 还会影响优化器的工作。索引选择、WHER子句、ORDER BY都有可能不是按照预计的方式工作。

5.10.5 全文索引的配置和优化

定期地进行全文索引重建等日常维护可提升性能
保证索引缓存足够大，从而保证所有的全文索引都能缓存在内存中
提供一个好的停用词列表
忽略一些太短的单词可以提升全文索引的效率
停用词表和允许最小词长都可以减少索引词语来提升全文索引效率，但同时会降低搜索的精确度。
当向一个全文索引的表中导入大量数据的时候，最后先DISABLE KEYS来禁用全文索引，然后在导入数据后再ENABLE KEYS来建立全文索引。
如果数据集非常大，则需要对数据进行手动分区，然后将数据分布到不同的节点，再做并行的搜索。

5.11 分布式（XA）事务

存储引擎的事务特性能勾保证在存储引擎级别实现ACID，而分布式事务则让存储引擎级别的ACID扩展到数据库层面，甚至扩展到多个数据库之间，这需要两个阶段提交实现：

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

5.11.1 内部XA事务

作用：协调内部存储引擎和二进制日志
MySQL中各个存储引擎是完全独立的，彼此不知道对方的存在，所以一个跨存储引擎的事务就需要一个外部的协调者。如果不使用XA协议，例如，跨存储引擎的事务提交就只是顺序地要求每个存储引擎格子提交，如果在某个存储提交过程中发生系统崩溃，就会破坏事务的特性。
如果将MySQL记录的二进制日志操作看作是一个独立的存储引擎，在存储引擎提交的同时，需要将提交的信息写入二进制文件，这就是一个分布式事务，只不过二进制日志的参与者是MySQL本身。
XA事务为MySQL带来巨大的性能下降。从MySQL5.0开始，它破坏了MySQL内部的“批量提交”(一种通过单磁盘IO操作完成多个事务提交的技术)，使得MySQL不得不进行多次额外的fsync()调用。

5.11.2 外部XA事务

作用：MySQL可以参与到外部的分布式事务中
MySQL能够作为参与者完成一个外部的分布式事务。但它对XA协议支持并不完整，例如，XA协议要求在一个事务中的多个连接可以做关联，但目前版本的MySQL还不能支持。
因为通信延迟和事务参与者本身可能失败，所以外部XA事务比内部消耗会更大。
- 如果在广域网中使用XA事务，通常会因为不可预测的网络性能导致事务失败。
- 如果有太多不可控因素，例如，不稳定的网络通信或者用户长时间地等待而不提交，则最好避免使用XA事务。任何可能让事务提交发生延迟的操作代价都很大，因为它影响的不仅是自己本身，还会让所有参与者都在等待。
还可以使用别的方式实现高性能的分布式事务。例如，可以在本地写入数据，并将其放入队列，然后在一个更小、更快的事务中自动分发。还可以使用MySQL本身的复制机制来发送数据。很多应用程序都可以完全避免使用分布式事务。
XA事务是一种在多个服务器之间同步数据的办法。如果由于某些原因不能使用MySQL本身的复制，或者性能不是瓶颈的时候，可以尝试使用。

5.12 查询缓存

MySQL的缓存类型：

某些场景下实现缓存查询的执行计划，对于相同类型的SQL就可以跳过SQL解析和执行计划生成阶段
缓存完整的SELECT查询结果，也就是“查询缓存”

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

查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生变化，那么和这个表相关的所有的查询缓存数据都将失效。这种机制效率看起来很低，但是实现代价很小，而这点对于一个非常繁忙的系统来说非常重要。
查询缓存对应用是完全透明的。应用程序无须关心MySQL是通过查询缓存返回还是实际执行返回的结果。

随着现在的通用服务器越来越大，查询缓存被发现是一个影响服务器扩展性的因素。它可能成为整个服务器的资源竞争单点，在多核服务器上还可能导致服务器僵死。建议默认关闭查询缓存，如果查询缓存作用很大的话，那就配置一个很小的查询缓存空间(如几十兆)。

5.12.1 MySQL如何命中查询缓存

缓存存放在一个引用表中，通过一个哈希值引用，这个哈希值包括了如下因素：即查询本身、当前要查询的数据库、客户端协议的版本等一些其他可能会影响返回结果的信息。
- 当判断缓存是否命中时，MySQL不会解析、“正规化”或者参数化查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。任何字符上的不同，例如空格、注释，都会导致缓存不命中。
- 当查询语句中有一些不确定的数据时，则不会被缓存。例如包含函数NOW()或者CURRENT_DATE的查询都不会被缓存。
  - 包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，或者任何包含级别权限的表，都不会被缓存。
  - 在检查查询缓存之前，MySQL通过一个大小写不敏感的检查看看SQL语句是否以SEL开头。
  - 而检查查询缓存的时候，MySQL还不会解析SQL语句，所以MySQL并不知道查询语句中是否包含有返回不确定数据的函数。但是MySQL在任何时候只要发现不能被缓存的部分，就会禁止这个查询被缓存。
```
-- 如果希望换成一个带日期的查询，那么最好将其日期提前计算好，而不要直接使用函数
... DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) -- Not cacheable!
... DATE_SUB('2007-07-14’, INTERVAL 1 DAY) -- Cacheable
```
- 子查询和存储过程都没办法使用查询缓存，另外5.1版本之前，绑定变量也无法使用。因为查询缓存是在完整的SELECT语句基础上的，而且只是在刚收到SQL语句的时候才检查。
- 查询缓存在很多时候可以提升查询性能，但本身是一个加锁排他操作，另外打开查询缓存对读和写都会带来额外的消耗：
  - 读查询在开始之前必须先检查是否命中缓存
  - 如果这个读查询可以被缓存，那么当完成执行后，MySQL若发现查询缓存中没有这个查询，会将其结果存入查询缓存，这会带来额外的系统消耗。
  - 这对写操作也会有影响，因为当向某个表写入数据的时候，MySQL必须将对应表的所有缓存都设置失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大系统消耗(设置了很多的内存给查询缓存用的时候)
- 对InnoDB来说，事务的一些特性会限制查询缓存的作用。当一个语句在事务中修改了某个表，MySQL会将这个表的对应的查询缓存都设置失效，而事实上，InnoDB的多版本特性会暂时将这个修改对其它事务屏蔽。
  - 在这个事务提交之前，这个表的相关查询是无法被缓存的，所以所有在这个表上面的查询，内部或外部的事务，都只能在该事务提交后才被缓存。因此，长时间运行的事务，会大大降低查询缓存的命中率。
- 如果查询缓存使用了很大量的内存，缓存失效操作就可能会成为一个非常严重的问题瓶颈。
  - 如果缓存中存放了大量的查询结果，那么缓存失效操作时整个系统都可能会僵死一会。因为这个操作是靠一个全局锁操作保护的，所有需要做该操作的查询都要等待这个锁，而且无论是检测是否命中缓存，还是缓存失效检测都需要等待这个全局锁。

5.12.2 查询缓存如何使用内存

查询缓存是完全存储在内存中。
- 除了查询结果，需要缓存的还有很多别的维护相关的数据。这些基本的管理维护数据结构大概需要40KB的内存资源。
- 用于查询缓存的内存被分成一个个的数据块，数据块是变长的。
  - 每一个数据块中，存储了自己的类型、大小和存储的数据本身，还外加指向前一个和后一个数据块的指针。数据块的类型有：存储查询结果、存储查询和数据表的映射、存储查询文本等等。
理想流程：
- 当服务器启动的时候，它先初始化查询缓存需要的内存。这个内存池初始是一个完整的空闲块，大小就是所配置的查询缓存再减去用于维护元数据的数据结构锁消耗的空间。
  - 通过函数malloc()向操作系统申请内存，在整个流程只在初次创建查询缓存的时候执行一次。
- 当有查询结果需要缓存的时候，MySQL先从大的空闲块中申请一个数据块用于存储结果。
  - 这个数据块需要大于参数query_cache_min_res_unit的配置，即使查询结果远远小于此，仍需要至少申请query_cache_min_res_unit空间。因为需要在查询开始返回结果的时候就分配空间，而此时是无法预知查询结果到底有多大的，所以MySQL无法为每一个查询结果精确分配大小恰好匹配的缓存空间。
  - 这个内存块会尽可能小(也可能选择较大的，这里不介绍细节)，然后将结果存入其中。因为需要先锁住数据块，然后找到合适大小的数据块，所以相对来说，分配内存块是一个非常慢的操作，MySQL尽量避免这个操作的次数。
  - 这里的分配内存块，是指在空闲块列表中找到一个合适的内存块，或者从正在使用的、待淘汰的内存块中回收再使用。也就是说，MySQL自己管理内存而不依赖与操作系统的内存管理。
- 如果数据块全部用完，但仍有剩余数据需要存储，MySQL会申请一块新数据块(仍然是尽可能小)继续存储结果数据。
- 当查询完成时，如果申请的内存空间仍有剩余，MySQL会将其释放，并放入空闲内存部分。
查询缓存如何分配内存来存储结果数据
实际流程：
- 假设平均查询结果非常小，服务器在并发地向不同的两个连接返回结果，返回完结果后MySQL回收剩余数据块空间时会发现，回收的数据块小于query_cache_min_res_unit，所以不能够直接在后续的内存块分配中使用。考虑到这种情况，数据块的分配就更复杂些。
  - 在收缩第一个查询结果使用的缓存空间时，就会在第二个查询结果之间留下一个“空隙”——非常小的空闲空间，因为小于query_cache_min_res_unit而不能再次被查询缓存使用。这类空隙称为碎片，在内存管理、文件系统管理上都是经典问题。
  - 有很多情况下都会导致碎片，例如缓存失效时，可能导致留下太小的数据块无法在后续缓存中使用。
  查询缓存中存储查询结果后剩余的碎片

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

只有当缓存带来的资源节约大于其本身的资源消耗时才会给系统带来性能提升。
任何SELECT语句没有从查询缓存中返回都称为“缓存未命中”，缓存未命中的原因：
- 查询语句无法被缓存，可能是因为查询中包含一个不确定的函数，或者查询结果太大。这都会导致状态值Qcache_not_cached增加
- MySQL从未处理这个查询，所以结果也从不曾被缓存过
- 之前缓存了查询结果，但由于查询缓存的内存用完，需要将某些缓存清除，或者由于数据表被修改导致缓存失效
服务器上有大量缓存未命中，但实际上最大多数查询都被缓存了，一定是有如下情况发生：
- 查询缓存还没有完成预热。也就是说，MySQL还没有将查询结果都缓存起来。
- 查询语句之前从未被执行过。如果应用程序不会重复执行一条查询语句，那么即使完成预热仍然会有很多缓存未被命中。
- 缓存失效的操作太多。
  - 缓存碎片、内存不足、数据修改都会导致缓存失效。
  - 如果配置了足够的缓存空间，而且query_cache_min_resunit设置也合理的话，那么缓存失效应该主要是数据修改导致的。可以通过Com*查看数据修改的情况(包括Com_update，Com_delete)，也可以通过Qcache_lowmem_prunes来查看有多少次失效是由于内存不足导致的。
评估是否使用查询缓存的方法：
- 理论上，可以通过打开或者关闭缓存时候的系统效率来决定是否需要开启查询缓存。但是很难评估查询缓存是否能够带来性能提升。
  - SHOW STATUS只能提供一个全局的性能指标，也很难评估性能的提升
- 对于那些需要消耗大量资源的查询通常都是非常适合缓存。
  - 一些汇总计算查询，如COUNT()
  - 复杂的SELECT语句，如多表JOIN后还需要排序和分页，这类查询每次执行消耗都很大，但是返回的结果集却很小，非常适合查询缓存。不过需要注意，涉及表上的UPDATE、DELETE和INSERT相比SELECT来说要非常少。
- 判断查询是否有效的直接数据是命中率，就是使用查询缓存返回结果占总查询的比率。
  - 当MySQL接收到一个SELECT查询时，要么增加Qcache_hints的值，要么增加Com_select的值。
  - 查询缓存命中率是一个很难判断的值。命中率多大才是好的？只要查询缓存带来的效率提升大于它的消耗，即使只有30%的命中率也可以；缓存了哪些查询也很重要，例如，被缓存的查询本身消耗非常大，即使缓存命中率低也可以接受
- 考虑缓存命中率的同时，通常还需要考虑缓存失效带来的额外消耗。
  - 极端的办法，对某一个表先做一次只有查询的测试，并且所有的查询都命中缓存，另一个相同的表只做修改操作。这时，查询缓存的命中率是100%，但因为会给更新操作带来额外的消耗，所以查询缓存并不一定会带来总体效率提升。这里，所有的更新语句都会做一次缓存失效检查，而检查的结果都是相同的，这会给系统带来额外的资源浪费。
- MySQL中如果更新操作和带缓存的操作混合，查询缓存带来的好处很难衡量。
  - 如果缓存的结果在失效前没有被任何其他的SELECT语句使用，那么这次缓存操作就是浪费时间和内存。
  - 可以通过查看Qcache_select和Qcache_inserts的相对值来查看。如果每次查询操作都是缓存未命中，然后需要将查询结果放到缓存中，这两个值应该差不多。所以在缓存完成预热后，最好的情况是Query_inserts远远小于Query_select
- 命中率和“INSERT和SELECT比例”都无法直观地反应缓存的效率，还有另一个直观的办法：命中和写入的比例，即Qcache_hints和Qcache_inserts的比率
  - 根据经验，这个比值大于3:1时通常查询缓存是有效的，最好能够达到10:1.
  - 如果应用没有达到这个比率，可以考虑禁用查询缓存，除非能够通过精确的计算得知：命中带来的性能提升大于缓存失效的消耗，并且查询缓存并没有成为系统的瓶颈。
- 观察查询缓存内存的实际使用情况，来确定是否需要缩小或者扩大查询缓存。
  - 如果查询缓存达到几十兆这样的数量级，是有潜在风险的。(这和硬件以及系统大小有关)
- 需要和系统的其他缓存一起考虑，如InnoDB的缓存池，或者MyISAM的索引缓存。
- 最好的判断查询缓存是否有效的办法还是通过查看某类查询时间消耗是否增大或者减少来判断。

5.12.4 如何配置和维护查询缓存·

配置：
- query_cache_type:是否打开查询缓存。可以是ON、OFF或者DEMAND。DEMAND表示只有在查询语句中明确写明SQL_CACHE的语句才放入查询缓存。这个变量可以是会话级别也可以是全局级别。
- query_cache_size：查询缓存使用的总内存空间，单位是字节，必须是1024的整数倍，否则MySQL实际分配的数据可能会有不同。
- query_cache_min_res_unit：在查询缓存中分配内存块时的最小单位。
- query_cache_limit：MySQL能够缓存的最大查询结果。
  - 如果查询结果大于这个值，则不会被缓存。因为查询缓存在数据生成的时候就开始尝试缓存数据，所以只有当结果全部返回后，MySQL才知道查询结果是否超出限制
  - 如果超出，MySQL则增加状态值Qcache_not_cached，并将结果从查询缓存中删除。如果事先知道有很多这样的情况发生，那么建议在查询语句中加入SQL_NO_CACHE来避免查询缓存带来的额外消耗。
- query_cache_wlock_invalidate：如果某个数据表被其他的连接锁住，是否仍然从查询缓存中返回结果。默认是OFF。
减少碎片
- 没有什么办法能够完全避免碎片，但是合适的query_cache_min_res_unit可以减少由碎片导致的内存空间浪费。
  - 设置合适的值可以平衡每个数据块的大小和每次存储结果时内存块的申请次数，其实是在平衡内存浪费和CPU消耗。
  - 这个值太小，则浪费的空间更少，但是会导致更频繁的内存块申请操作。如果太多，则碎片会很多。
- 这个参数的最合适大小和应用程序的查询结果的平均大小直接相关。
  - 可以通过内存实际消耗(query_cache_size-Qcache_free_memory)除以Qcache_queries_in_cache计算单个查询的平均缓存大小。
  - 如果查询结果大小很不均匀，那么碎片和反复的内存块分配可能无法避免。
  - 如果发现缓存了一个非常大的结果，可以通过参数query_cache_limit限制可以缓存的最大查询结果。
- 可以通过参数Qcache_free_blocks来观察碎片，反映了查询缓存中内存块的多少。
  - 如果Qcache_free_blocks恰好达到Qcache_total_blocks/2，那么查询缓存就有严重的碎片问题。
  - 如果还有很多空闲块，而状态值Qcache_lowmem_prunes还不断增加，则说明由于碎片导致了过早的删除查询缓存结果。
- 可以使用FLUSH QUERY CACHE完成碎片整理。这个命令会将所有的查询缓存重新排序，并将所有的空闲空间都聚集到查询缓存的一块区域上。
  - 会访问所有的查询缓存，在这期间任何其他的连接都无法访问查询缓存，从而导致服务器僵死一段时间。因此，建议保持查询缓存空间足够小
  - 清空缓存由RESET QUERY CACHE完成
提高查询缓存的使用率
- 如果查询缓存不再有碎片问题，但命中率仍然很低，还可能是查询缓存内存空间太小导致的。如果MySQL无法为一个新的查询缓存结果的时候，则会删除某个老的查询缓存
- 当删除老的查询缓存时，会增加状态值Qcache_lowmem_prunes。如果这个值增长的很快，可能是由以下两个原因导致的：
  - 如果还有很多空闲块，那么碎片可能就是罪魁祸首
  - 如果这时没什么空闲块，就说明在这个系统压力下，分配的查询缓存空间不够大。可以通过检查状态值Qcache_free_memory来查看还有多少没有使用的内存。
如何分析和配置查询缓存

5.12.5 InnoDB和查询缓存

因为InnoDB有自己的MVVC机制，InnoDB会控制在一个事务中是否可以使用查询缓存，InnoDB会同时控制对查询缓存的读和写操作。
- 事务是否可以访问查询缓存决定于当前的事务ID，以及对应的数据表上是否有锁。每一个InnoDB表的内存数据字典都保存了一个事务ID，如果当前事务ID小于该事务ID，则无法访问查询缓存。
- 如果表上有任何锁，那么对这个表的任何查询语句都是无法被缓存的。
InnoDB下的查询缓存：
- 所有大于该表计数器的事务才可以直接使用(读和写)查询缓存。
- 该表的计数器并不是直接更新为对该表进行加锁操作的事务ID，而是被更新成一个系统事务ID。所以，会发现该事务自身后续的更新操作也无法读取和修改查询缓存。
查询缓存存储、检索和失效操作都是在MySQL层面完成，InnoDB无法绕过或者延迟这个行为。
- 但是InnoDB可以在事务中显式地告诉MySQL何时应该让某个表的查询缓存都失效。在有外键限制的时候这是必须的，例如某个SQL有ON DELETE CASCADE。
原则上，在InnoDB的MVVC架构下，当某些修改不影响其他事务读取一致的数据时，是可以使用查询缓存的。但是这样实现起来会很负责，InnoDB做了简化，让所有有加锁操作的事务都不使用任何查询缓存，这个限制不是必须的。

5.12.6 通用查询缓存优化

用多个小表代替一个大表对查询缓存有好处。这个设计将会使得失效策略能够在一个更合适的粒度上进行。当然，不要让这个原则过分影响设计，毕竟其它的一些优势很容易就能弥补。
批量写入时只需要做一次缓存失效，所以相比单条写入的效率要高。注意不要同时做延迟写和批量写，否则可能会导致服务器僵死较长时间。
因为缓存空间太大，在过期操作的时候可能会导致服务器僵死。一个简单的办法就是控制缓存空间的大小，或者直接禁用查询缓存。
无法在数据库或则表级别控制查询缓存，但是可以通过SQL_CACHE和SQL_NO_CACHE来控制某个SELECT语句是否需要进行缓存。还可以修改会话级别的query_cache_type来控制查询缓存。
对于写密集型的应用来说，直接禁用查询缓存可能会提高系统性能。关闭查询缓存可以移除所有相关的消耗，例如将query_cache_size设置为0
因为对互斥信号量的竞争，有时直接关闭查询缓存对读密集型的应用也会有好处。
如果不想所有的查询都进入查询缓存，可以将query_cache_type设置为DEMAND，然后在希望缓存的查询中加上SQL_CACHE

5.12.7 查询缓存的替代方案

查询缓存的工作原则是：执行查询最快的方式就是不去执行。但是查询仍然要发送到服务器端，服务器端还需要做一点点工作。因此可以直接在客户端进行缓存。

5.13 总结

分区表：分区表是一种粗粒度的、简易的索引策略，适用于大数据量的过滤场景。最适合的场景是，在没有合适的索引时，对其中几个分区进行全表扫描，或者是只有一个分区和索引是热点，而且这个分区和索引都能够在内存中；限制单表分区数不要超过150个，并且注意某些导致无法做分区过滤的细节，分区表对于单条记录的程序并没有什么优势，需要注意这类查询的性能。
视图：对好几个表的复杂查询，使用视图有时候会大大简化问题。当视图使用临时表时，无法将WHERE条件下推到各个具体的表，也不能使用任何索引，需要特别注意这类查询的性能。如果为了遍历，使用视图是很合适的。
外键：外键限制会将约束放到MySQL中，这对于必须维护外键的场景，性能会更高。不过这也会带来额外的复杂性和额外的索引消耗，还会增加多表之间的交互，会导致系统中有更多的锁和竞争。外键可以被看作是一个确保系统完整性的额哇的特性，但是如果设计的是一个高性能的系统，那么外键就会显得很臃肿了。很多人在更在意系统的性能的时候都不会使用外键，而是通过应用程序来维护。
存储过程
绑定变量
插件
字符集：字符集是一种字节到字符之间的映射，而校对规则是指一个字符集的排序方法。很多人都使用Latin1(默认字符集，对英语和某些欧洲语言有效)或者UTF-8。如果使用的是UTF-8，那么在使用临时表和缓冲区的时候需要注意：MySQL会按照每个字符三个字节的最大占用空间来分配存储空间，这可能消耗更多的内存或者磁盘空间。注意让字符集和MySQL字符集配置相符，否则可能会由于字符集转换让某些索引无法正常工作。
全文索引
XA事务：很少会有人用MySQL的XA事务特性。除非你真正明白参数innodb_support_xa的意义，否则不要修改这个参数的值，并不是只有显示使用XA事务时才需要设置这个参数。InnoDB和二进制日志也是需要使用XA事务来做协调的，从而确保在系统崩溃的时候，数据能够一致地恢复。
查询缓存：完全相同的查询在重复执行的时候，查询缓存可以立即放回结果，而无须在数据库中重新执行一次。根据经验，在高并发压力环境中查询缓存会导致系统性能的下降，甚至僵死。如果一定要使用查询缓存，那么不要设置太大内存，而且只有在明确收益的时候才使用。查询缓存是一个非常方便的缓存，对应用程序完全透明，无须任何额外的编码，但是如果希望有更高效的查询缓存，建议使用memacched等其他缓存方案。