MySQL详解6.高级特性

---

点击进入我的博客

1 分区表

分区表是一个独立的逻辑表，但底层是由多个物理字表构成的。

2 视图

视图本身是一个虚拟表，不存放任何数据。在使用SQL语句访问视图的时候，它返回的数据是 MySQL从其他表中生成的。视图和表是在同一个命名空间，MySQL在很多地方对于视图和表是同样对待的。

实现视图最简单的方法是将 SELECT 语句的结果存放到临时表中。当需要访问视图的时候，直接访问这个临时表就可以了。

CREATE VIEW view_name AS
SELECT * FROM tb_name [WHERE]
WITH CHECK OPTION

可更新视图

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

视图的限制

• MySQL还不支持物化视图（物化视图是指将视图结果数据存放在一个可以查看的表中，并定期从原始表中刷新数据到这个表中)。
• MySQL也不支持在视图中创建索引。
• MySQL并不会保存视图定义的原始SQL语句，所以如果打算通过执行 SHOW CREATEⅥEW后再简单地修改其结果的方式来重新定义视图，可能会大失所望。 分区表是一个独立的逻辑表，但是底层由多个物理字表组成。

3 外键

InnoDB是目前 MySQL中唯一支持外键的内置存储引擎。

外键的成本

• 外键通常都要求每次在修改数据时都要在另外一张表中多执行一次查找操作。虽然 InnoDB强制外键使用索引，但还是无法消除这种约束检查的开销。
• 外键约束使得查询需要额外访问一些别的表，这也意味着需要额外的锁。
• 外键维护操作是逐行进行的，所以这样的更新会比批量删除和更新要慢些。

外键的好处

• 如果想确保两个相关表始终有一致的数据，那么使用外键比在应用程序中检查一致性的性能要高得多。
• 外键在相关数据的删除和更新上，也比在应用中维护要更高效。

4 在MySQL内部存储代码

MySQL允许通过触发器、存储过程、函数的形式来存储代码。从 MySQL5.1开始,还可以在定时任务中存放代码，这个定时任务也被称为“事件”。

4.1 函数

函数存储着一系列SQL语句，调用函数就是一次性执行这些语句。

4.2 存储过程

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。

DELIMITER //
CREATE PROCEDURE insert_data(IN loops INT)
BEGIN
    DECLARE i INT;
    SET i = loops;
    WHILE i > 0 DO
        INSERT INTO tb_name (name, value) VALUES ('ZZX', '123');
        SET i = i - 1;
    END WHILE;
END;
//
DELIMITER ;

存储过程与自定义函数的区别：

• 存储过程实现的过程要复杂一些，而函数的针对性较强;
• 存储过程可以有多个返回值，而自定义函数只有一个返回值;
• 存储过程一般独立的来执行，而函数往往是作为其他SQL语句的一部分来使用;

4.3 触发器

• 触发器可以让你在执行 INSERT、 UPDATE或者 DELETE的时候，执行一些特定的操作。
• 可以在 MySQL中指定是在SQL语句执行前触发还是在执行后触发。
• 触发器本身没有返回值，不过它们可以读取或者改变触发SQL语句所影响的数据。
• 对每一个表的每一个事件，最多只能定义一个触发器。
• MySQL只支持“基于行的触发”——也就是说，触发器始终是针对一条记录的，而
  不是针对整个SQL语句的。

4.4 事件

• 事件是 MySQL5.1引入的一种新的存储代码的方式。它类似于 Linux的定时任务，不过是完全在 MySQL内部实现的。
• 可以创建事件，指定 MySQL在某个时候执行一段SQL代码，或者每隔一个时间间隔执行一段SQL代码。
• 通常，我们会把复杂的SQL都封装到一个存储过程中，这样事件在执行的时候只需要做一个简单的CALL调用。

5 游标

MySQL在服务器端提供只读的、单向的游标，而且只能在存储过程或者更底层的客户端API中使用。因为MySQL游标中指向的对象都是存储在临时表中而不是实际查询到的数据，所以 MySQL游标总是只读的。它可以逐行指向查询结果，然后让程序做进步的处理。

6 绑定变量

从 MySQL4.1版本开始，就支持服务器端的绑定变量(prepared statement)，这大大提高了客户端和服务器端数据传输的效率。

创建绑定变量

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

使用绑定变量

绑定变量的SQL，使用问号标记可以接收参数的位置，当真正需要执行具体查询的时候，则使用具体值代替这些问号。例如,下面是一个绑定变量的SQL语句，可以通过向服务器端发送各个问号的取值和这个SQL的句柄来执行一个具体的查询:
INSERT INTO tb (col1, col2, col3) VALUES (?,?, ?)

7 自定义函数

从很早开始，MySQL就支持用户自定义函数(UDF)。存储过程只能使用SQL来编写，而UDF没有这个限制，你可以使用支持C语言调用约定的任何编程语言来实现。UDF必须事先编译好并动态链接到服务器上,这种平台相关性使得UDF在很多方面都很强大。

8 插件

9 字符集和校对集

字符集是指一种从二进制编码到某类字符符号的映射，可以参考如何使用一个字节来表示英文字母。“校对”是指一组用于某个字符集的排序规则。

默认设置

• 创建数据库的时候,将根据服务器上的 character_set_server 设置来设定该数据库的默认字符集。
• 创建表的时候，将根据数据库的字符集设置指定这个表的字符集设置。
• 创建列的时候，将根据表的设置指定列的字符集设置

查看字符集和校对集

• SHOW CHARACTER SET：查看字符集
• SHOW COLLATION：查看校对集

10 全文索引

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询了。但是，如果你希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。
全文索引有着自己独特的语法。没有索引也可以工作，如果有索引效率会更高。
全文索引可以支持各种字符内容的搜索(包括CHAR、VARCHAR和TEXT类型)，也支持自然语言搜索和布尔搜索。

11 分布式事务

12 查询缓存

MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存，MySQL会立刻返回结果，跳过了解析、优化和执行阶段。查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生变化,那么和这个表相关的所有的缓存数据都将失效。
应用程序无须关心 MySQL是通过查询缓存返回的结果还是实际执行返回的结果，无论是 MySQL开启或关闭查询缓存，对应用程序都是透明的。
查询缓存无须使用任何语法。

如何判断缓存命中

• MySQL判断缓存命中的方法很简单：缓存存放在一个引用表中，通过一个哈希值引用，这个哈希值包括了如下因素——即查询本身、当前要查询的数据库、客户端协议的版本等。
• 当判断缓存是否命中时，MySQL不会解析、“正规化”或者参数化查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。
• 任何字符上的不同，例如空格、注释都会导致缓存的不命中。所以在编写SQL语句的时候，需要特别注意这点。通常使用统一的编码规则是一个好的习惯，在这里这个好习惯会让你的系统运行得更快。
• 当査询语句中有一些不确定的数据时，则不会被缓存。例如包含函数Now()或者 CURRENT DATE()的查询不会被缓存。

什么时候需要查询缓存

• 并不是什么情况下查询缓存都会提高系统性能的。缓存和失效都会带来额外的消耗，所以只有当缓存带来的资源节约大于其本身的资源消耗时才会给系统带来性能提升。
• 可以通过观察打开或者关闭查询缓存时候的系统效率来决定是否需要开启查询缓存。