高性能MySQL】第7章MySQL高级特性 中

7.3外键约束

有使用成本，修改时在另一张表中执行查找操作；加锁、慢

如果确保两个相关表数据一致的话，使用外键比在应用程序汇总检查一致性性能更高

 

7.4MySQL内部存储代码

通过触发器、存储过程、函数的形式存储代码，5.1开始，可在定时任务中存代码

不同类型的存储代码区别：执行上下文（输入输出），存储过程/函数都可以接收参数然后返回值，但是触发器和事件却不行；

优缺点：

1、服务器内执行、部署，离数据最近，备份、维护在服务器端完成，维护工作简单、服务器上执行还可节省带宽和网络延迟

2、缓存执行计划，代码重用降消耗，方便地统一业务规则、保证某些行为总是一致，为应用提供一定的安全性：更细粒度的权限控制

3、可简化代码的维护和版本更新，应用和数据库开发人员更好分工

• MySQL本身无好用的开发 调试工具
• 存储代码效率差些：可使用的函数有限
• 给程序代码部署带来额外复杂性：部署内部存储代码
• 安全隐患：非标准加密功能放在存储程序中，库被攻破数据泄露；如果加密函数放在程序代码中，必须同时攻破程序和数据库才能获得数据
• 存储过程会给数据库服务增加额外的压力，数据库服务器的扩展比应用差很多
• 不能控制资源消耗，调试困难，与基于语句的二进制日志复制合作地并不好

 

总的来说：存储代码帮应用隐藏复杂性，开发更简答，性能更低，复制有风险；1、问程序逻辑在数据库还是应用代码中实现，编写存储代码时明白这是将程序逻辑放在数据库中；

 

7.4.1存储过程和函数

优化器：

    无法使用关键字deterministic优化单个查询中多次调用存储函数的情况、无法评估存储函数执行成本；

存储程序越小越简单越好，但是当可代替很多小查询时、推荐用存储过程调

 

7.4.2触发器

无返回值，可改变读取、改变 触发SQL语句所影响的数据

减少客户端和服务器间的通信，简化应用逻辑、提高性能

注意：

每个表每个事件max有关触发器，mysql只支持“基于行的触发”针对一条记录

掩盖服务器背后的工作，问题难排查，可导致死锁和锁等待（触发器失败原SQL失败），并不一定保证更新的原子性

 

7.4.3事件

mysql5.1引入新的存储代码的方式，类似linux定时任务，在内部实现

在独立的事件调度线程中初始化（set global event_scheduler:=1设置调度线程）

创建事件意味着给服务器带来额外的工作、执行SQL 可能会对性能有很大影响

线程执行完会被销毁

可通show processlist的command查看，总是显示connect

 

 

7.4.4存储程序中保留注释

使用版本相关注释

 

7.5游标

服务器中只提供在存储过程或更底层的客户端API中使用的只读、单向的游标

    游标指向的对象all存在临时表中的，so只读，可逐行指向查询结果，让程序进一步处理

打开游标时需要执行整个查询：

如果只是访问一小部分，安排一些limit

 

7.6绑定变量

    4.1支持服务器端绑定变量prepared statement，提高了客户端和服务器端数据传输效率

过程：

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

     使用？标记可接受参数位置，执行时使用具体值代替这些问号

为什么高效：
      解析一次SQL语句；优化器工作只执行一次；二进制方式发送参数和句柄，效率高、省内存降网络开销，格式转换开销，仅传参数；直接将存储存缓存

 

7.6.1绑定变量的优化

如果执行计划需要据传入的参数做计算则mysql无法缓存这部分计划

三类优化：

在准备阶段：服务器解析SQL、移除不可能的条件、重写子查询

第一次执行：可能的话，先简化嵌套循环的关联、将外关联转化为内关联

每次SQL执行：服务器过滤分区、尽量移除count min  max 移除常量表达式 检查常量表   做必要的等值传播  分析和优化ref range 和索引优化等访问数据的方法  优化关联顺序

 

7.6.2SQL接口的绑定变量

4.1支持，以SQL方式使用绑定变量

存储过程中使用，构建并执行动态SQL

 

 

7.6.3绑定变量的限制

1. 会话级别，连接间不能共用绑定变量句柄，连接断开、原有句柄不能再用
2. 5.1后绑定变量的SQL不能使用查询缓存
3. 不是all时候使用绑定变量都获更好的性能：只执行一次SQL
4. 总是忘记释放绑定变量资源，则服务器容易资源‘泄漏’，绑定变量SQL总数的限制是全局的，某一个地方错误可对其他线程产生影响

 

7.7用户自定义函数UDF

可使用支持C语言 调用约定的编程语言来实现

需要预先编译好并动态链接到服务器上，速度快、可访问大量操作系统的功能，可使用大量库函数

但无法调用UDF线程中使用当前事务处理的上下文去读写数据：更适合计算或与外交互

MySQL版本升级时注意相应改变，是否需要重新编译，或修改UDF让其工作，确保UDF是线程安全的

 

7.8插件

存储过程插件：

      帮在存储过程运行后再处理一次运行结果

后台插件：

      让程序在MySQL中运行，实现自己的网络监听，执行自己的定时任务

INFORMATION_SCHEMA：

      提供一个新的内存INFORMATION_SCHEMA

全文解析插件：

      处理文本功能，据自己的需求来对文档分词、增强词语匹配功能

审计插件：

      在查询执行的过程中的某些固定点被调用，用作记录MySQL事件日志

认证插件：

       可在MySQL客户端也可在服务器端，使用这类插件扩展认证功能

 

7.9字符集和校对

字符集：一种从二进制编码到某类字符符号的映射

校对：一组用于每个字符集的排序规则

4.1后每类编码字符都有对应的字符集和校对规则

 

7.9.1MySQL如何使用字符集

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

2、每个校对规则都是针对某个特定的字符集的

3、校对规则和字符集总是一起使用，统称一个字符集

 只有基于字符的值才真正的有字符集的概念，对于其他类型的值，字符集只是一个设置：指定用哪一个字符集来做比较或其他操作

 

mysql的设置：创建对象时的默认值  在服务器和客户端通信时的设置

创建对象时的默认设置

     mysql服务器、每个库、每个表都有默认的字符集和校对规则，逐层继承的默认设置，最终靠底层的默认设置影响创建的对象，至上而下告诉MySQL使用什么字符集来存储某个列

 

各层可指定特定字符集或让服务器使用默认值

1. 创建库时，据服务器上的character_set_server设定库默认字符集
2. 建表，将据数据库的字符集设置指定表的字符集设置
3. 创建列时，据表的设置指定列的字符集设置

只有当创建列没有指定字符集时，表的默认才起作用，更高的设置只是指定默认

 

服务器和客户端通信时的设置：

不同的字符集，服务器端要翻译转换：

1、服务器假设客户端是按character_set_client设置的字符集传输数据和sql语句

2、服务器收到客户端SQL语句时，先将其转换成字符集character_set_connection，且使用这个设置决定如何将数据转成字符串

3、服务器端返回数据或错误信息给客户端，将其转换成character_set_result

据需要，使用set names 或set character set语句来改变上面的设置，客户端程序和API也需要使用正确的字符集才能避免在通信时出现问题

 

MySQL如何比较字符串大小：

如字符集不同，先转成同一个字符集再比较

     如果两个字符集不兼容，抛出错误，通过函数convert显示将其中一个转成一个兼容的字符集

mysql会为每个字符串设置“可转换性”：值的字符集的优先级，影响mysql字符集隐式转换后的值

• 可使用charset、collation、coercibility定位各字符集相关的错误
• 可使用前缀和collate子句指定字符串的字符集或校对字符集

指定utf8字符集，collate二进制校对规则

 

一些特殊情况：

诡异的character_set_database设置

       默认值同默认数据库的设置，当改变库时、跟着变，so当连接到MySQL实例上又没有指定要使用的数据库时，默认值和character_set_server相同

 

load data infile:

      数据库总是将文件中的字符按照字符集character_set_database来解析，5.0更新版中可在load data infile中使用character set 设定字符集但最好不要依赖这个设定

     指定字符集最好的方式是先使用USE指定数据库、执行set names设定字符集，最后在加载数据；

      mysql在加载数据时，总是以同样的字符集处理所有的数据，不管列是否有不同的字符集设定；

      LOAD DATA INFILE 语句以很高的速度从一个文本文件中读取行到一个表中。文件名必须是一个文字字符串；【源】

 

select into outfile：

      导出数据到指定目录下【源】不做任何转码地写入文件，可使用convert将all列做一次转码

 

嵌入式转义序列：

      MySQL据character_set_client的设置来解析转义序列

解析器在处理字符串的转义字符时，不关心校对规则、前缀只是一个关键字而已

 

7.9.2选择字符集和校对规则

使用show characterset 和show collation查看mysql支持的字符集和校对规则

极简原则：

alter table将对应列转成互相兼容的字符集

最好先为服务器、数据库选择合理的字符集，据不同情况、让某些列选择合适的字符集

 

校对规则

是否以大小写敏感的方式比较字符串，或以字符串编码的二进制值比较大小

    不同：二进制校对规则直接使用字符的字节进行比较

               大小写敏感的在多字节字符集时，有更复杂的比较规则

设置字符集：

不必同时指定字符集和校对规则的民名字，mysql会使用可能的默认值来填充

 

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

不同字符集和校对规则间的转换可能带来额外系统开销：

只有排序查询要求的字符集与服务器数据的字符集相同，才能使用索引进行排序

索引跟据数据列的交规规则（排序规则）进行排序

 

mysql在需要时会进行字符集转换为适应各种字符集，转换列的索引将无法使用

可在explain extended后使用show warnings 查看mysql是如何处理字符集

 

UTF-8

   多字节编码，存储一个字符会使用变长的字节数（1~3）

   在MySQL内部，通常使用一个定长的空间存储字符串再进行操作：希望总是保证缓存中有足够空间来存储字符串

    

多字节字符集中：一个字符不再是一个字节

   length和char_length计算字符串的长度，在多字节字符集中，两函数返回值不同

   确保在统计字符集时使用char_length

 

索引限制：

     要索引UTF-8字符集的列，mysql会假设每个字符集都是三个字节，最长索引前缀的限制一下缩短到原理三分之一

 

考虑使用什么字符集时需要据存储的具体内容来决定：

     存储英文字符，utf-8不会消耗太多空间（英文字符仍使用一个字节）

     存储非拉丁语系的字符：俄语、阿拉伯语区别会很大

       只存阿拉伯语可使用cp1256字符集 用一个字节标识all阿拉伯语字符

     还需要存别的语言，可使用utf-8，这时相同的阿拉伯语字符会消耗more空间

当具体语种编码转换为utg-8，存储空间的使用会相应增加，如果使用的是InnoDB字符集的改变可能导致数据大小超过可在页内存储的临界值，需保存在额外的外部存储区、严重空间浪费、带来空间碎片

 

有时候、有时候，我相信一切有尽头