高性能MySQL 第七章

分区表：

从底层的文件系统来看很容易发现，每一个分区表都有一个使用#分隔命名的表文件

mysql'实现分区表的方式，意味着索引也是按照分区的子表定义的，因而没有全局索引。

如果分区字段中有主见或者唯一索引的列（意思应该是组合索引中的一列或几列），那么所有主键列和唯一索引列都必须包含进来。

分区表中无法使用外键约束

增删改查操作之前，分区层都会先打开并锁住所有底层表，然后判断使用到哪些分区。但这并不是说分区表在处理的过程中是锁住全表的。

mysql支持范围、键值、哈希、列表等分区。其中有些还支持子分区。

null值会使分区过滤无效：

分区表第一个分区是一个特殊分区，分区值为null或非法值时，存储在第一个分区。因此即使查询条件限制在了一个分区上，也会到第一个分区里面再查找一下看看。

此时如果第一个分区非常大，则代价会非常大。

为了避免这种情况，可以创建一个“无用”的第一个分区。可以减小检查第一个分区的代价。

应当避免建立和分区列不匹配的索引，防止扫描所有分区。除非查询中还同时包含了可以过滤分区的条件。

选择分区的成本可能很高：

比如范围分区，每次查询、插入等操作都需要扫面所有的分区定义的列表，以判断要扫描或者插入的行属于哪个分区。类似这样的现行扫面的效率不高，所以随着分区书的增长，成本会原来越高。尤其是一次写入大量数据的时候。可以通过限制分区数量来缓解此问题。

其他分区，如键分区，哈希分区则没有这样的问题。

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

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

explain partition

一个很重要的原则是：即便在创建分区时可以使用表达式，但在查询时却只能根据列来过滤分区。

视图：

mysql处理视图的算法： 合并算法  临时表算法

尽可能的使用合并算法。

可以嵌套视图

如果是使用临时表算法实现的使用到视图的查询（查询重写中的视图使用了临时表算法），explain中会显示派生表（derived）。

如果视图中包含group by ，distinct ，聚合函数 ，union ，子查询等，只要无法在原表记录和视图记录中建立一一映射的场景中，mysql都会将使用临时表算法来实现视图。

可更新视图：可以通过更新这个视图来更新视图设计的相关表。

可以是一个关联语句，但是被更新的列必须来自同一个表。

所有使用临时表算法实现的视图都无法被更新。

with check option子句

mysql不支持在视图上建任何触发器

视图可以帮助应用重构schema，控制列权限。

使用临时表算法实现的视图，在做表关联时，外层查询的where条件无法下推到构建临时表的查询中。临时表中也无法建立索引。

视图还不是那么成熟。

mysql不支持物化视图

外键约束：

innodb是目前mysql中唯一支持外键的内置存储引擎

mysql强制外键使用索引

外键有时会导致很大的性能问题

在MySQL内部存储代码：触发器，存储过程，函数，事件（定时任务）

相比于T-SQL和PL/SQL，mysql的存储代码功能还很弱。

优化器无法评估存储函数的执行成本。

触发器：

基于行的

针对每个事件，只能设置一个触发器

触发器锁等待，触发器失败导致事务回滚等问题。

事件

类似于linux的定时任务

典型应用：定期维护任务，重建缓存，构建汇总表来模拟物化视图等

事件调度线程，创建一个新的进程执行，可以并行执行。

在代码证保留注释： 99999

游标

mysql的游标都是只读的，单向的，因为游标中的对象都是存储在临时表中的（而不是实时查询到的数据，这点跟oracle和sql server不同）。

所以打开游标的时候，需要执行整个查询。

在一个存储过程中可以同时打开多个游标。

由于临时表不能存储BLOB或者TEXT类型的数据，所以当游标中包含这类数据时，需要创建临时磁盘表。性能会更糟。

即使没有这样的列，当临时表大于temp_table_size的时候，也会创建临时磁盘表。

绑定变量

可以通过向服务器端发送各个问号的取值和这个SQL的句柄来执行一个具体的查询。

对于使用绑定变量的SQL，mysql能够缓存其部分执行计划。

用户自定义函数

存储过程只能用SQL来编写，但UDF不是。

字符集和校对规则

每种字符集都可能有多种校对规则，且都有一个默认的校对规则。

每个校对规则都是针对某个特定的字符集的，和其他字符集没关系。

mysql服务器 《  数据库 《 表的字符集

列可以有自己的字符集。如果创建表时没有指定列的字符集，才使用表的字符集。

客户端   --------------------->   服务器

  character_set_client转换为 character_set_connection

服务器 ------------------------>  客户端

character_set_connection 转换为 character_set_result

character_set_database

character_set_server

分布式事务

存储引擎的事务特性能够保证在存储引擎级别实现ACID

分布式事务则让存储引擎级别的ACID扩展到数据库级别甚至多个数据库之间（这需要两阶段提交实现）

事务协调器

第一阶段：保证所有的事务参与者都完成了准备工作。

第二阶段：协调器收到所有的参与者都准备好的消息，就会告诉所有的事务都可以提交了。

mysql是参与者，而不是协调者。

mysql中有两种XA事务：

1.外部的分布式事务

2.内部XA事务协调存储引擎和二进制日志

跨存储引擎的事务需要外部协调者

通信延迟或者参与者失败等因素，所以外部XA事务比内部事务消耗会更大。

查询缓存：

查询缓存会跟踪查询涉及的每个表，当这些表发生变化，那么和这个表相关的所有的查询缓存数据都将失效。（效率低，但是实现代价很小。）

当查询语句中有一些不确定的数据时，则不会被缓存。

如果查询语句中包含任何的不确定函数，那么在查询缓存中是不可能找到缓存结果的。（因为根本就没缓存）。

而不是说，如果查询语句中好饱一个不确定的函数，Mysql则不会检查查询缓存。（因为这种说法本身就是错误的。）

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

无论是检测是否命中缓存还是缓存失效检测都需要全局锁。

除了查询结果之外，需要缓存的还有很多别的维护相关的数据。

服务器启动的时候，先初始化查询缓存需要的内存。

当有查询结果需要缓存的时候，从大的空闲块中申请一个数据块用于存储结果。

query_cache_min_res_unit

有些情况可以用SQL_CACHE来优化对缓存的使用，而不是query cache。

判断查询是否有效的直接数据时命中率。