MySQL高级特性的那些事

一、分区表

1. 一个独立的逻辑表，底层由多个物理子表组成
2.对分区表的请求---->(转化成)对存储引擎的接口调用
3.实现方式---对底层表的封装---意味着索引也是按照分区的子表定义的，而没有全局索引
4.在创建表时使用PARTITION BY子句定义每个分区存放的数据
5.目的：将相关的数据存放在一起

场景

 1. 表非常大以至于无法全部放在内存中
 2. 分区表的数据更容易维护
 3. 备份和恢复独立的分区

限制

 1. 最多有1024个分区
 2. 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引都必须包含进来
 3. 分区表达式必须是整数，或者是返回整数的表达式
 4. 分区表中无法使用外键约束

原理

 1. SELECT 查询
		分区层打开-->锁住所有底层表-->优化器判断是否可以过滤部分分区-->调用存储引擎接口访问数据
 2. INSERT 操作
		分区层打开-->锁住所有底层表-->确定哪个分区接收记录-->写入对应的底层表
 3. DELETE 操作
		分区层打开-->锁住所有底层表-->确定数据对应的分区-->对相应底层表进行删除
 4. UPDATE 操作
		分区层打开-->锁住所有底层表-->确定更新的数据分区-->取出并更新-->放入哪个分区-->对底层表写入-->对原数据底层表删除

查询优化

 1. 最大优点就是优化器可以根据分区函数来过滤一些分区
 2. 访问分区表，要在WHERE条件中带入分区列
 3. 即便在创建分区表时可以使用表达式，但在查询时却只能根据列来过滤分区

二、视图

虚拟表，不存放任何数据，返回的数据是从其他表生成的
MySQL可以使用合并算法和临时表算法来处理视图，会尽可能的使用合并算法

可更新视图

1. 通过更新这个视图来更新视图涉及的相关表

视图对性能的影响

视图的限制

1. 还不支持物化视图
2. 不支持在视图中创建索引

具体可看 视图内容

三、外键约束

InnoDB强制外键使用索引，使用外键是有成本的
外键维护操作是逐行进行的
使得查询需要额外访问一些别的表，意味着需要额外的锁
对于相关数据的同时更新外键更合适

四、在MySQL内部存储代码

MySQL允许通过触发器、存储过程、函数的形式来存储代码，还可以在定时任务中存放代码，这个定时任务也被成为事件

优点：

1. 在服务器内部执行，离数据近，节省宽带和网络延迟
2. 代码重用
3. 简化代码的维护和版本更新
4. 提升安全，提供更细粒度的权限控制
5. 服务器端可以缓存存储过程的执行计划，这对于需要反复调用的过程，会大大降低消耗
6. 因为是在服务器端部署的，所以备份、维护都可以在服务器端完成，所以存储程序的维护工作会很简单

缺点：

1. 编写难度比其他数据库难、调试困难
2. 较之应用程序的代码，存储代码效率要稍微差些
3. 可能给应用程序代码的部署带来额外的复杂性
4. 可能有安全隐患
5. 数据库服务器增加额外的压力
6. MySQL并没有什么选项可以控制存储程序的资源消耗

存储过程和函数

触发器

1. 触发器可以让你在执行INSERT、UPDATE或者DELETE的时候，执行一些特定的操作
2. 可以规定是在SQL执行前触发还是执行后触发
3. 触发器本身没有返回值
4. 可以减少客户端和服务器之间的通信，简化应用逻辑，提高性能
5. MySQL只支持‘’基于行的触发‘’

事件

1. 某个时间执行一段SQL代码，或者隔段时间执行一次

五、全文索引

六、查询缓存

MySQL如何判断缓存命中

缓存存放在一个引用表中，通过一个哈希值(查询本身、当前查询的数据库、客户端协议的版本等)引用

直接使用SQL语句和客户端发送过来的其他原始信息，任何不同都会导致缓存不中

在检查查询缓存之前，只做一件事，通过一个大小写不敏感的检查看看SQL语句是不是以SEL开头

缓存未命中

查询语句无法被缓存
从未处理过这个查询，所以结果也从未被缓存过
之前缓存过，但由于查询缓存的内存用完了，MySQL需要将某些缓存逐出，或者是由于数据表被修改导致缓存失效

缓存未命中（缓存过）

查询缓存还没有完成预热。MySQL还没有机会将查询结果都缓存起来
查询语句之前从未执行过
缓存失效操作过多（缓存碎片、内存不足、数据修改都会造成缓存失效）

查询缓存如何使用内存

1. 查询缓存是完全存储在内存中的

2. 基本的管理维护数据结构需要大概40KB的内存资源

3. MySQL用于查询缓存的内存被分为一个个的数据块（变长的）

4. 当服务器启动时，初始化查询缓存需要的内存（内存池是一个完整的空闲块，大小是你所配置的查询缓存大小减去用于维护元数据的数据结构所消耗的空间）

5. 当有查询结果需要缓存时，申请一个数据块用于存储结果，先锁住空间块，找到合适大小数据块，选择一个尽可能小的内存块，将结果存入其中，如果数据块全部用完，但仍有剩余数据需要存储，那么MySQL会申请一个新的数据块，继续存储结果数据。当查询完成时，申请的内存空间还有剩余，MySQL会将其释放，并放入空闲内存部分

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

当缓存带来的资源节约大于其本身的资源消耗时才会带来系统性能提升
判断查询缓存最有效的办法：通过查看某类查询时间消耗是否增大或减小

如何配置和维护查询缓存

1. query_cache_type 是否打开查询缓存
2. query_cache_size 查询缓存使用的总内存空间
3. query_cache_min_res_unit 查询缓存中分配内存块时的最小单位
4. query_cache_limit MySQL能够缓存的最大查询结果
5. query_cache_wlock_invalidate 如果某个数据表被其他的连接锁住，是否仍然能从查询缓存中返回结果

注释：
query_cache_min_res_unit太小会导致过多的更频繁的内存块申请操作，
太大会导致碎片太多，调整合适的值其实是在平衡内存浪费和CPU消耗

InnoDB和查询缓存

1. InnoDB会控制在一个事务中是否可以使用查询缓存，InnoDB会同时控制对查询缓存的读和写
2. 事务是否可以访问查询缓存取决于当前事务ID以及对应数据表上是否有锁。
	InnoDB表的内存数据字典都保存了一个事务ID号，如果当前事务ID号小于该ID号，则无法访问查询缓存
	如果表上有任何的锁，那么对这个表的任何查询语句都是无法被缓存的
3. InnoDB让所有有加锁操作的事务都不使用任何查询缓存

通用查询缓存优化

1. 用多个小表代替一个大表
2. 批量写入时只需要做一次缓存失效，所以相比单条写入效率更好
3. 缓存空间太大，在过期操作的时候可能回导致服务器僵死（控制缓存空间大小，或直接禁用查询缓存）
4. 无法在数据库或表级别控制查询缓存，但是可以通过SQL_CACHE和SQL_NO_CACHE来控制某个SELECT语句是否需要进行缓存

查询缓存的替代方案

MySQL的查询不管是否执行都要去发送到服务器，服务器都需要工作，如果对于某些查询完全不需要与服务器通信时，可以考虑客户端缓存（减小MySQL服务器的压力）