分区表

MySQL分区表的底层由多个物理子表组成，每个分区表都有一个使用#分隔命名的表文件。分区表对SQL层是透明的，，对分区表的请求会通过分区表的句柄（Handler Object）转化成对存储引擎接口的调用。每个分区表有自己的独立索引，整个表没有全局索引。查询时，优化器根据分区定义过滤不包含查询数据的分区。分区表是一种粗粒度的索引策略，单表最多1024个分区（最好不超过150），分区表无法使用外键约束。

分区表适用于以下场景：

在没有合适索引时，对其中几个分区进行全表扫描
表太大，无法放入内存
部分分区是热点域（例如按时间分区时，只有最近的记录是热点域）
需要对分区表进行独立操作（清除、检查、优化、备份、恢复或者使用不同的物理设备）
使用分区表规避特殊限制（如InnoDB的单个索引互斥访问，ext3文件系统的inode锁竞争等）

分区表使用方法

create table sales {
    order_date DATATIME NOT NULL,
    ...
}ENGINE=InnoDN PARTITION BY RANGE(YEAR(order_date)) {
    PARTITION p2012 VALUES LESS THEN(2012),
    PARTITION p2013 VALUES LESS THEN(2013),
}

视图

视图是一种虚拟的数据表，它的行为和数据表一样，但是并不真正包含数据，但是不能对视图创建索引和触发器。实现视图的两种方法：

将SELECT语句的结果放到临时表中，即临时表算法（TEMPTABE），使用这种算法会有明显的性能问题。
重写使用视图的查询语句，将视图定义SQL合并到查询SQL中，即合并算法（MERGE）。

如果视图中包含GROUP BY，DISTINCT，聚合函数，UNION，子查询等，原表记录和视图记录中无法建立一一映射，MySQL将使用临时表算法来实现视图。可以通过视图来更新视图相关数据表的视图成为可更新视图，更新视图查询也可以是关联语句，但是被更新的列必须来自同一个表，所有使用临时表算法的视图都无法被更新。

MySQL不支持物化视图（将视图结果数据存放在一个可以查看的表中，并定期从原始数据表中刷新数据到这个表中）。

外键约束

InnoDB是MySQL唯一支持外键的内存存储引擎，外键通常要求每次修改数据时在另外一个表中多执行一次检查操作。如果向子表中写入一条记录，需要对父表中对应记录进行加锁操作，以确保这条记录在事务完成之前没有被删除。这回导致额外的锁等待，甚至导致一些死锁。如果要维护两表之间数据的一致性，使用外键比在应用程序中检查一致性性能要高得多。

MySQL内部存储代码

有四种在MySQL内部存储代码的方法：存储过程、存储函数、触发器和事件。存储过程和存储函数都可以接受参数然后返回值，触发器和事件都不可以。使用内部存储代码优点如下：

在服务器内部执行，离数据最近，可以节省数据传输带宽和延迟
这是一种代码复用，保证某些行为保持一致，提供一定的安全性
可以简化代码的维护和版本更新

触发器

触发器是指在执行INSERT、UPDATE或者DELETE时，执行一些特定的操作。可以指定触发器在SQL执行前触发还是执行后触发。触发器本身没有返回值，不过它们可以读取或改变触发SQL语句所影响的数据。可以使用触发器实现一些强制限制，或者某些业务逻辑。

对于每个表上的每一个时间，最多只能定义一个触发器（不能在AFTER INSERT上定义两个触发器）。MySQL只支持基于行的触发，触发器始终是针对一条记录的，而不是针对整个SQL语句的。

CREATE TRIGGER trigger
BEFORE INSERT ON table
FOR  EACH ROW
    DECLEAR rowCnt INT DEFAULT ROW_COUNT();
    IF rowCnt <> 1 THEN
        ...
    END IF;
END;

使用触发器可以减少客户端和服务器之间的通信，可以简化应用逻辑还可以提高性能。在InnoDB上的触发器是在同一个事务中完成的，它们执行的操作是原子性的。

事件

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

事件在一个独立的线程中被初始化，它与处理连接的线程没有任何关系，也不接受任何参数，没有任何返回值。事件的典型应用包括定期地维护任务、重建缓存、构建汇总表来模拟物化视图或者用于监控和诊断数据库状态等。

绑定变量（prepared statement）

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

绑定变量的SQL使用问号标记可以接受参数的位置，当真正需要执行具体查询时，使用具体值代替这些问号。客户端通过向服务器发送各个问号的取值和这个SQL句柄来执行一个具体的查询。MySQL在使用绑定变量的时候可以更加高效地执行执行大量的重复语句。

在服务器端只需要解析一次SQL语句
在服务器端某些优化器的工作只需要执行一次，因为它会缓存一部分的执行计划
以二进制的方式只发送参数和句柄比起每次发送ASCII码文本效率更高，绑定变量的形式可以分块传输。
使用绑定变量网络开销更小而且可以节省将数据从存储原始格式转换成文本格式的开销。

字符集和校对规则

MySQL的字符集和校对规则可以分为两类：创建对象时的默认值、在服务器和客户端通信时的设定值。

创建数据库时，将根据服务器上的character_set_server设置来设定该数据库的默认字符集。创建表和列时也可以针对表和列设置特定的字符集。实际采用的字符集按照数据库->表->列的顺序，如果没有为表指定字符集，则采用它所属数据库的字符集，如果没有为列设置字符集，则采用表设置的字符集。

服务器端总是假设客户端按照character_set_client设置的字符集来传输数据和SQL语句。当服务器收到客户端的SQL语句时，先转换成字符集character_set_connection。

从服务器取数据时将数据从character_set_server转换成character_set_connection，再从character_set_connection转换成character_set_result。

选择字符集和校对规则的正确方法是：先为服务器或数据库选择一个合理的字符集。然后根据特殊情况为特定表或列指定字符集和校对规则。_cs, _ci和_bin分别表示按照大小写敏感、大小写不敏感或者直接比较字节的方式比较字符串。

为了保证缓存中有足够的空间存储字符串，MySQL使用一个定长的空间来存储字符串。例如，utf-8的char(10)需要30个字节，索引一个utf-8字符集的列时，每个字符都是三个字节，最大索引前缀的限制缩短为1/3。变长字段类型（varchar text）存储时不会采用这种方法，但是当它们存储在临时表中用来处理或排序时，也会分配最大可能长度。

查询缓存

MySQL查询缓存保存查询返回的完整结果，当查询命中该缓存，MySQL会立刻返回结果，跳过了解析、优化和执行阶段。查询缓存系统会跟踪查询涉及的每个表，如果这些表发生变化，那么和这个表相关的所有缓存数据都将失效。

缓存存放在一个引用表中，通过一个哈希值引用，这个哈希值包括了查询本身、当前要查询的数据库、客户端协议的版本等可能会影响返回结果的信息。

当查询语句中有一些不确定的数据时，则不会被缓存。例如包含NOW(),CURRENT(),DATE()的查询不会被缓存。如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表或者包含列级别权限的表都不会被缓存。查询缓存存在的问题：

打开查询缓存对读和写操作都会带来额外的消耗，读查询在开始之前必须先检查是否命中缓存
如果读查询可以被缓存，执行完成后，如果检查到缓存中没有这个结果，则会存入查询缓存
当向某个表写入数据时，必须将所有缓存都设置失效，如果查询缓存非常大或者碎片很多，该操作可能带来很大的开销
查询缓存操作是一个加锁排他操作，锁的开销也会相当可观。如果查询缓存使用了大量的内存，对全局锁的竞争可能导致整个系统僵死。