MySQL由浅至深(七).ppt

,MySQL课程,高级2第七章,要做就做最好，要不就不做！,2023/6/28,MySQL视图,ALTER VIEW语法 CREATE VIEW语法 DROP VIEW语法 SHOW CREATE VIEW语法,在5.1版MySQL服务器中提供了视图功能（包括可更新视图）。本章讨论了下述主题：使用CREATE VIEW或ALTER VIEW创建或更改视图。使用DROP VIEW销毁视图。使用SHOW CREATE VIEW显示视图元数据。,2023/6/28,MySQL-CREATE VIEW语法,CREATE OR REPLACE ALGORITHM=UNDEFINED|MERGE|TEMPTABLE VIEW view_name(column_list)AS select_statement WITH CASCADED|LOCAL CHECK OPTION该语句能创建新的视图，如果给定了OR REPLACE子句，该语句还能替换已有的视图。select_statement是一种SELECT语句，它给出了视图的定义。该语句可从基表或其他视图进行选择。该语句要求具有针对视图的CREATE VIEW权限，以及针对由SELECT语句选择的每一列上的某些权限。对于在SELECT语句中其他地方使用的列，必须具有SELECT权限。如果还有OR REPLACE子句，必须在视图上具有DROP权限。,2023/6/28,MySQL创建视图,视图属于数据库。在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图，创建时，应将名称指定为db_name.view_name。mysql CREATE VIEW test.v AS SELECT*FROM t;表和视图共享数据库中相同的名称空间，因此，数据库不能包含具有相同名称的表和视图。视图必须具有唯一的列名，不得有重复，就像基表那样。默认情况下，由SELECT语句检索的列名将用作视图列名。要想为视图列定义明确的名称，可使用可选的column_list子句，列出由逗号隔开的ID。column_list中的名称数目必须等于SELECT语句检索的列数。SELECT语句检索的列可以是对表列的简单引用。也可以是使用函数、常量值、操作符等的表达式。对于SELECT语句中不合格的表或视图，将根据默认的数据库进行解释。通过用恰当的数据库名称限定表或视图名，视图能够引用表或其他数据库中的视图。能够使用多种SELECT语句创建视图。视图能够引用基表或其他视图。它能使用联合、UNION和子查询。SELECT甚至不需引用任何表。在下面的示例中，定义了从另一表选择两列的视图，并给出了根据这些列计算的表达式：,mysql CREATE TABLE t(qty INT,price INT);mysql INSERT INTO t VALUES(3,50);mysql CREATE VIEW v AS SELECT qty,price,qty*price AS value FROM t;mysql SELECT*FROM v;,2023/6/28,MySQL视图的定义,视图定义服从下述限制：SELECT语句不能包含FROM子句中的子查询。SELECT语句不能引用系统或用户变量。SELECT语句不能引用预处理语句参数。在存储子程序内，定义不能引用子程序参数或局部变量。在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。在视图定义中命名的表必须已存在。不能将触发程序与视图关联在一起。,2023/6/28,MySQL-WITH CHECK OPTION,对于可更新视图，可给定WITH CHECK OPTION子句来防止插入或更新行，除非作用在行上的select_statement中的WHERE子句为“真”。在关于可更新视图的WITH CHECK OPTION子句中，当视图是根据另一个视图定义的时，LOCAL和CASCADED关键字决定了检查测试的范围。LOCAL关键字对CHECK OPTION进行了限制，使其仅作用在定义的视图上，CASCADED会对将进行评估的基表进行检查。如果未给定任一关键字，默认值为CASCADED。请考虑下述表和视图集合的定义：mysql CREATE TABLE t1(a INT);mysql CREATE VIEW v1 AS SELECT*FROM t1 WHERE a CREATE VIEW v2 AS SELECT*FROM v1 WHERE a0 WITH LOCAL CHECK OPTION;mysql CREATE VIEW v3 AS SELECT*FROM v1 WHERE a0 WITH CASCADED CHECK OPTION;,注：这里，视图v2和v3是根据另一视图v1定义的。v2具有LOCAL检查选项，因此，仅会针对v2检查对插入项进行测试。v3具有CASCADED检查选项，因此，不仅会针对它自己的检查对插入项进行测试，也会针对基本视图的检查对插入项进行测试。在下面的语句中，介绍了这些差异：mysql INSERT INTO v2 VALUES(2);mysql INSERT INTO v3 VALUES(2);ERROR 1369(HY000):CHECK OPTION failed test.v3,2023/6/28,MySQL-LOCAL与CASCADED,这里，视图v2和v3是根据另一视图v1定义的。v2具有LOCAL检查选项，因此，仅会针对v2检查对插入项进行测试。v3具有CASCADED检查选项，因此，不仅会针对它自己的检查对插入项进行测试，也会针对基本视图的检查对插入项进行测试。在下面的语句中，介绍了这些差异：mysql INSERT INTO v2 VALUES(2);mysql INSERT INTO v3 VALUES(3);ERROR 1369(HY000):CHECK OPTION failed test.v3,注：视图的可更新性可能会受到系统变量updatable_views_with_limit的值的影响。,2023/6/28,MySQL-ALTER VIEW语法,ALTER ALGORITHM=UNDEFINED|MERGE|TEMPTABLE VIEW view_name(column_list)AS select_statement WITH CASCADED|LOCAL CHECK OPTION 该语句用于更改已有视图的定义。其语法与CREATE VIEW类似。mysql alter view v3 as select*from t1 where a 1 with local check option;,注：CREATE VIEW和DROP权限，也需要针对SELECT语句中引用的每一列的某些权限。,2023/6/28,MySQL视图关联性,某些视图是可更新的。也就是说，可以在诸如UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。更具体地讲，如果视图包含下述结构中的任何一种，那么它就是不可更新的：聚合函数（SUM(),MIN(),MAX(),COUNT()等）。DISTINCT GROUP BY HAVING UNION或UNION ALL 位于选择列表中的子查询Join FROM子句中的不可更新视图WHERE子句中的子查询，引用FROM子句中的表。mysql alter view v3 as select count(*)from t1 where a 1 with local check option;,ERROR 1368(HY000):CHECK OPTION on non-updatable view test.v3,2023/6/28,MySQL-DROP VIEW语法,DROP VIEW IF EXISTS view_name,view_name.RESTRICT|CASCADE DROP VIEW能够删除1个或多个视图。必须在每个视图上拥有DROP权限。可以使用关键字IF EXISTS来防止因不存在的视图而出错。给定了该子句时，将为每个不存在的视图生成NOTE。,2023/6/28,MySQL-SHOW CREATE VIEW语法,SHOW CREATE VIEW view_name 该语句给出了1个创建给定视图的CREATE VIEW语句。mysql SHOW CREATE VIEW v;mysql SHOW CREATE VIEW vG;,2023/6/28,MySQL分区,分区概述 分区类型分区管理,2023/6/28,MySQL分区概述,SQL语言的使用独立于它所使用的任何数据结构或图表、表、行或列下的介质。但是，大部分高级数据库管理系统已经开发了一些根据文件系统、硬件或者这两者来确定将要用于存储特定数据块物理位置的方法。在MySQL中，InnoDB存储引擎长期支持表空间的概念。分区又把这个概念推进了一步，它允许根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。函数根据用户指定的分区类型来选择，把用户提供的表达式的值作为参数。该表达式可以是一个整数列值，或一个作用在一个或多个列值上并返回一个整数的函数。这个表达式的值传递给分区函数，分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。这个函数不能是常数，也不能是任意数。它不能包含任何查询，但是实际上可以使用MySQL 中任何可用的SQL表达式，只要该表达式返回一个小于MAXVALUE（最大可能的正整数）的正数值。可以通过使用SHOW VARIABLES命令来确定MySQL是否支持分区，例如：mysql SHOW VARIABLES LIKE%partition%;,2023/6/28,MySQL-PARTITION,要为某个分区表配置一个专门的存储引擎，必须且只能使用STORAGE ENGINE 选项，这如同为非分区表配置存储引擎一样。但是，必须记住STORAGE ENGINE（和其他的表选项）必须列在用在CREATE TABLE语句中的其他任何分区选项之前。下面的例子给出了怎样创建一个通过HASH分成6个分区、使用InnoDB存储引擎的表：mysql CREATE TABLE t2(id INT,amount DECIMAL(7,2),tr_date DATE)ENGINE=INNODB PARTITION BY HASH(MONTH(tr_date)PARTITIONS 6;,注：分区适用于一个表的所有数据和索引；不能只对数据分区而不对索引分区，反之亦然，同时也不能只对表的一部分进行分区。,2023/6/28,MySQL分区的优点,分区的一些优点包括：与单个磁盘或文件系统分区相比，可以存储更多的数据。对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL 分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。涉及到例如SUM()和 COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如“SELECT salesperson_id,COUNT(orders)as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。,2023/6/28,MySQL分区类型,RANGE 分区：基于一个给定连续区间的列值，把多行分配给分区。LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。,注：无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录，记住这一点非常重要。当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确的分区。例如，如果你的表使用4个分区，那么这些分区就编号为0,1,2,和3。对于RANGE和LIST分区类型，确认每个分区编号都定义了一个分区，很有必要。对HASH分区，使用的用户函数必须返回一个大于0的整数值。对于KEY分区，这个问题通过MySQL服务器内部使用的 哈希函数自动进行处理。,2023/6/28,MySQL分区命名名称,分区的名字基本上遵循其他MySQL 标识符应当遵循的原则，例如用于表和数据库名字的标识符。但是应当注意，分区的名字是不区分大小写的。例如，下面的CREATE TABLE语句将会产生如下的错误：mysql CREATE TABLE t2(val INT)PARTITION BY LIST(val)(PARTITION mypart VALUES IN(1,3,5),PARTITION MyPart VALUES IN(2,4,6);错误1488(HY000):表的所有分区必须有唯一的名字。这是因为MySQL认为分区名字mypart和MyPart没有区别。,2023/6/28,MySQL-RANGE分区,按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。在下面的几个例子中，假定你创建了一个如下的一个表，该表保存有20家音像店的职员记录，这20家音像店的编号从1到20。mysql CREATE TABLE temp(id INT NOT NULL,name VARCHAR(30)PARTITION BY RANGE(id)(PARTITION p0 VALUES LESS THAN(6),PARTITION p1 VALUES LESS THAN(11),PARTITION p2 VALUES LESS THAN(16),PARTITION p3 VALUES LESS THAN(21);,注：按照这种分区方案，在商店1到5工作的雇员相对应的所有行被保存在分区P0中，商店6到10的雇员保存在P1中，依次类推。注意，每个分区都是按顺序进行定义，从最低到最高。这是PARTITION BY RANGE 语法的要求；在这点上，它类似于C或Java中的“switch.case”语句。,2023/6/28,MySQL-RANGE分区,按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。在下面的几个例子中，假定你创建了一个如下的一个表，该表保存有20家音像店的职员记录，这20家音像店的编号从1到20。,但是如果增加了一个编号为第21的商店，将会发生什么呢？,2023/6/28,MySQL-RANGE分区,按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。在下面的几个例子中，假定你创建了一个如下的一个表，该表保存有20家音像店的职员记录，这20家音像店的编号从1到20。mysql CREATE TABLE temp(id INT NOT NULL,name VARCHAR(30)PARTITION BY RANGE(id)(PARTITION p0 VALUES LESS THAN(6),PARTITION p1 VALUES LESS THAN(11),PARTITION p2 VALUES LESS THAN(16),PARTITION p3 VALUES LESS THAN(21);,注：按照这种分区方案，在商店1到5工作的雇员相对应的所有行被保存在分区P0中，商店6到10的雇员保存在P1中，依次类推。注意，每个分区都是按顺序进行定义，从最低到最高。这是PARTITION BY RANGE 语法的要求；在这点上，它类似于C或Java中的“switch.case”语句。,但是如果增加了一个编号为第21的商店，将会发生什么呢？MAXVALUE,2023/6/28,MySQL-LIST分区,MySQL中的LIST分区在很多方面类似于RANGE分区。和按照RANGE分区一样，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN(value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。假定有20个音像店，分布在4个有经销权的地区，如下表所示：mysql CREATE TABLE temp(id INT NOT NULL,name VARCHAR(30)PARTITION BY LIST(id)(PARTITION p0 VALUES IN(3,5,6,9,17),PARTITION p1 VALUES IN(1,2,10,11,19,20),PARTITION p2 VALUES IN(4,12,13,14,18),PARTITION p3 VALUES IN(7,8,15,16);,注：LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。,2023/6/28,MySQL-HASH分区,HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。例如，下面的语句创建了一个使用基于“id”列进行 哈希处理的表，该表被分成了4个分区：mysql CREATE TABLE temp(id INT NOT NULL,name VARCHAR(30)PARTITION BY HASH(id)PARTITIONS 4;MySQL还支持线性哈希LINEAR HASH分区功能。,注：如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。例外：对于NDB Cluster（簇）表，默认的分区数量将与簇数据节点的数量相同，这种修正可能是考虑任何MAX_ROWS 设置，以便确保所有的行都能合适地插入到分区中。,2023/6/28,MySQL-KEY分区,按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的 哈希函数，这些函数是基于与PASSWORD()一样的运算法则。mysql CREATE TABLE temp(id INT NOT NULL,name VARCHAR(30)PARTITION BY LINEAR KEY(id)PARTITIONS 4;,注：在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。,2023/6/28,MySQL分区管理,RANGE和LIST分区的管理 HASH和KEY分区的管理 分区维护 获取关于分区的信息,注：要改变一个表的分区模式，只需要使用带有一个“partition_options”子句的ALTER TABLE 的命令。这个子句和与创建一个分区表的CREATE TABLE命令一同使用的子句有相同的语法，并且总是以关键字PARTITION BY 开头。例如，假设有一个使用下面CREATE TABLE语句建立的按照RANGE分区的表：现在，要把这个表按照使用id列值作为键的基础，通过KEY分区把它重新分成两个分区，可以使用下面的语句：ALTER TABLE trb3 PARTITION BY KEY(id)PARTITIONS 2；这和先删除这个表、然后使用“CREATE TABLE trb3 PARTITION BY KEY(id)PARTITIONS 2；”重新创建这个表具有同样的效果。,2023/6/28,MySQL-分区查看,计划用于分区表的、两个附加的SHOW命令是：目前还不能使用计划中,等待MySQL 升级。SHOW PARTITIONSSHOW PARTITION STATUS目前可以使用以下命令查看分区信息mysql show create table temp;/分区表信息mysql explain partitions select*from temp;/查看分区情况mysql select*from information_schema.partitions where table_schema=schema()and table_name=temp;/字典查看分区,