7、MySQL高级特性

7.1、分区表

对用户来说分区表是一个独立的逻辑，但是底层由多个物理子表组成。实现分区的代码实际上是一组底层的句柄对象的封装。对分区表的请求，都会通过句柄对象转换成对存储引擎的接口调用。所以分区对于mysql来说是一个完全封装底层实现的黑盒子，对应用是透明的，但是从底层文件系统来看就很容易发现，每一个分区表都有一个使用#分割命名的表文件

分区表解决那些问题或者说分区表的有点：

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

7.1.1分区表原理

分区表由多个相关的底层表来实现，这些底层表由句柄对象表示，所以我们可以访问每个区，存储引擎管理分区的各个底层表和管理普通表一样，分区表的索引只是在各个底层表上各自加上一个完全相同的索引。

从存储引擎的角度上来看，底层表和一个普通标一样。存储引擎也无需知道这是一个普通标还是一个分区表。

当对分区表增删改查的时候、分区层先打开并锁住所有的底层表，mysql内部优化器会对这一方面进行专门的优化；

7.1.2分区表的类型

a、根据范围分区
b、根据时间间隔分区
c、根据键值、哈希和列表分区，有些还支持子分区
d、5.5以后可以根据RANGE Columns进行分区

 

案例一、对于一个类似于我们设计的innoDB表，系统通过子分区可以降低索引的互斥访问竞争。

案例二、最近一年的分区的数据会被非常频繁的访问，这回导致大量的互斥量的竞争。使用哈希子分区可以将数据切分为多个小片，大大降低互斥量的竞争问题。

其他分区：

7.1.3、如何使用分区表

全量扫描数据，不要任何索引
索引数据，并分离热点

7.1.4、分区应该注意的问题

分区列和索引列不匹配
选择分区成本很高——范围分区类型 在插入数据的时候需要扫描对应的分区列表来找到合适的目标分区（一般通过限制分区数量来缓解此类问题）
打开并锁住影响应能
维护分区的成本——新增或者删除分区很快，但是重组分区或者类似alter语句的操作，这类操作需要复制数据。重组分区原理是创建一个临时分区，然后将数据复制到
其中，最后在删除原分区。

分区的限制

1).所有的分区必须使用相同的存储引擎。

2).分区函数中可以使用的函数和表达式也有一些限制。

3).某些存储引擎不支持分区。

4).对于MyISAM的分区表，不能再使用LOAD、INDEX、INTO、CACHE

5).对于MyISAM表、使用分区需要打开更多的文件描述符。虽然看起来是一个表，其实背后有很多独立的分区.每一个分区对于存储引擎来说都是一个独立的表。这样即使分区表只占用一个表缓存条目，文件描述符还是需要多个。因此即使已经配置了合适的缓存表，以确保不会超过操作系统的单个进程可以打开的文件描述符的个数，但对于分区表而言，还是会出现超过文件描述符限制的问题。

7.1.5、查询优化

过滤分区

7.1.6、合并表

7.2、视图

视图是一个虚拟表、不存方任何数据、视图不能使用触发器；视图处理方式有两种：合并算法和临时表算法

7.2.1、可更新视图

1、可以更新视图来更新视图涉及的相关表。只要制定了合适的条件，就可以更新、删除甚至想视图中插入一条数据。
2、如果视图定义中包含GROUP BY、uniou、聚合函数，以及其他一些特殊情况，就不能被更新了。
3、更新视图的查询也可以是一个关联语句，但是有一个限制，被更新的列必须来自同一个表。另外，所有临时表算法实现的视图都无法被更新。

7.2.2、视图对性能的影响
能提升，也能影响

7.2.3、视图的限制
不是物化视图
不支持索引

7.3、外键约束

innoDB目前是MySQL中唯一支持外键的内置存储引擎
外键的成本：每次修改数据时都要在另外一张表中多执行一次查询操作。虽然InnoDB强制外键使用索引，但还是无法消除这种约束检查的开销。

如果外键列额的选择性很低，则会导致一个非常大，且选择性很低的一个索引。

7.4、在Mysql内部存储代码

7.4.1、存储过程和函数

7.4.2、触发器

触发器只是针对单行的触发，而且只能是一个事件

MyISAM触发器不是原子性的，不支持回滚

7.4.3、事件

7.5、游标

游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。

7.6、绑定变量

7.6.1、绑定变量的优化

对使用绑定变量的sql，mysql能够缓存部分执行计划，如果某些执行计划
在准备阶段
服务器解析sql,移除不可能的条件，并且重写子查询

在第一次执行的时候
服务器先简化循环嵌套的关联，并将外关联，转换成内关联

在每次sql语句执行时
过滤分区、如果可能尽量移除count()/min()/max()
移除常量表达式
检测长量表
作必要的等值传播
分析和优化ref、range和索引优化等访问数据的方法
优化关联顺序

7.6.2、sql接口的变量绑定（忽略）
7.6.3、绑定变量的限制（忽略）

7.7、用户自定义函数
7.8、插件
7.9、字符集和校对
7.9.1、mysql如何使用字符集
创建对象是默认设置（库表字段）——只有字符的字段才算有字符集
服务器和客户端通讯的设置

7.10、全文索引

基于相似度的查询
全文索引的对象是“全文集合”，可以是某个数据表的一列，也可能是多列，对数据库某一个记录，mysql会将需要索引的列全部拼接成一个字符创，然后再进行索引
MyISAM的全文索引是一类特殊的B-Tree索引，共有两层。第一层是所有关键字，然后对于每一个关键字的第二层，包含的是一组相关的文档指针。全文索引不会索引对象中的所有语句；他会根据配置过滤；

7.10.1自然语言的全文索引
7.10.2布尔全文索引
7.10.3、全文索引限制和替代

 mysql索引不会记录索引词所在的位置，所以位置上也就无法用在相关性上；
全文索引只有在内存中的时候性能才非常好，如果内存无法装在全部索引，那么搜索速度会非常慢
全文索引会产生很多的碎片，可能需要更多的OPTIMIZE TABLE操作
全文索引不存储索引列的实际值，也就不可能作用索引覆盖扫描

7.10.5、全文索引的配置和优化
定期对索引进行重建
如果希望全文索引高效工作，需要保证索引缓存足够大，从而保证所有的全文索引能够缓存在内存中
设置停用词
7.11、分布式事务（XA事务）
7.11.1、内部事务
7.11.2、外部事务

7.12、查询缓存[查询缓存从MySQL 5.7.20开始已被弃用，并在MySQL 8.0中被删除]
缓存完整的select结果，如果命中缓存则直接返回结果、跳过解析，优化，执行阶段；
7.12.1、如何判断缓存命中
根据sql语句、数据库名、客户端协议版本做成一个hash引用；如果有字符，空格上的不同则不走缓存；
语句中有不确定搞得因素时则不会缓存、比如函数、变量、临时表；
7.12.2、查询缓存怎么使用内存
Mysql启动的时候根据配置参数申请一个大内存属于mysql的，然后根据前段查询从大内存中申请小的内存块、来缓存查询结果；
7.12.3、什么情况下查询缓存有作用
需要消耗大量资源的查询通常都是非常适合缓存的；例如count(*),join查询，涉及select很复杂但是在相同的表上很少有update、delete，delete
7.12.4、配置和维护查询缓存
query_cache_type:是否打开缓存
query_cache_size:大内存
query_cache_min_res_unit在查询中分配内存块的最小单位。
query_cache_limit mysql能够缓存的额最大查询结果。过大则不缓存
query_cache_wlock_invalidate 如果某个表被其他链接锁住，是否仍然从查询缓存中返回结果。
减少碎片
提高查询缓存使用率

7.12.5、InnoDB和查询缓存
所有有加锁的操作的事务都不使用查询缓存；

7.12.6、通用查询缓存优化
多个小表替代一个大表，是的实效策略能够在一个更合适的粒度上进行
批量写入只需要重置缓存一次；相比多次插入好很多
如果缓存空间太大，做过期操作会僵死，故控制大小
对于写密集型的应用来说，直接禁用查询缓存会提高性能
7.12.7、查询缓存的替代方案

 

题外：事务是什么？怎么实现的？