开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> PHP知识库 -> 【读书笔记】MySQL存储引擎_第三天 -> 正文阅读

[PHP知识库]【读书笔记】MySQL存储引擎_第三天

视图

视图是一个命名的虚表，由一个SQL查询来定义，可以当作表使用
与持久表不同的是，视图中的数据没有实际的物理存储
数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中。使用视图查询数据时，数据库会从真实表中取出对应的数据。因此，视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。

一、视图的作用

作用一：
提高了重用性，就像一个函数。如果要频繁获取user的name和goods的name。就应该使用以下sql语言。示例：

select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id;

但有了视图就不一样了，创建视图other。示例

create view other as select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id;

创建好视图后，就可以这样获取user的name和goods的name。示例：

select * from other;

以上sql语句，就能获取user的name和goods的name了。

作用二：
对数据库重构，却不影响程序的运行。假如因为某种需求，需要将user拆分成表usera和表userb，该两张表的结构如下：
测试表:usera有id，name，age字段
测试表:userb有id，name，sex字段
这时如果php端使用sql语句：select * from user;那就会提示该表不存在，这时该如何解决呢。解决方案：创建视图。以下sql语句创建视图：

create view user as select a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name;

以上假设name都是唯一的。此时php端使用sql语句：select * from user;就不会报错什么的。这就实现了更改数据库结构，不更改脚本程序的功能了。

作用三：
提高了安全性能。可以对不同的用户，设定不同的视图。例如：某用户只能获取user表的name和age数据，不能获取sex数据。则可以这样创建视图。示例如下：

create view other as select a.name, a.age from user as a;

这样的话，使用sql语句：select * from other; 最多就只能获取name和age的数据，其他的数据就获取不了了。

作用四：
让数据更加清晰。想要什么样的数据，就创建什么样的视图。经过以上三条作用的解析，这条作用应该很容易理解了吧

二、视图的用法

创建视图:
语法格式如下：

CREATE VIEW <视图名> AS <SELECT语句>

实例：在 tb_students_info 表上创建一个名为 view_students_info 的视图，输入的 SQL 语句如下所示。

 CREATE VIEW view_students_info AS SELECT * FROM tb_students_info;

查看视图的字段信息：

DESC 视图名;

修改视图内容：
使用 UPDATE 语句更新视图 view_students_info，输入的 SQL 语句如下所示。

UPDATE view_students_info SET age=25 WHERE id=1;

删除视图：
删除 v_students_info 视图，输入的 SQL 语句如下所示。

DROP VIEW IF EXISTS v_students_info;

分区表

一、分区概述

分区功能并不是在存储引擎层完成的，因此不只有InnoDB支持分区，常见的MyISAM、NDB也支持分区
分区的过程是将一个表或索引分解为多个更小、更可管理的部分
每个分区都是独立的对象，可以独自处理，也可以作为一个更大的对象的一部分处理
?Mysql支持水平分区，不支持垂直分区。
?Mysql数据库的分区时局部分区索引，一个分区中即存放了数据又存放了索引。（全局索引指的是所有数据的索引放到一个对象中，数据存放到各个分区中）

1.1 查看数据库是否开启分区功能

mysql> SHOW PLUGINS\G;
……
*************************** 2. row ***************************
   Name: partition
 Status: ACTIVE
   Type: STORAGE ENGINE
Library: NULL
License: GPL
……

ps：

还可以用指令SHOW VARIABLES LIKE '%partutuib%'\G;进行查看；
在版本8.0移除了show plugins对partition的显示，但社区版本的表分区功能是默认开启的
不要有这样一个误区：只要启用了分区，数据库就肯定能运行的更快。始终记得，分区的主要目的是对数据库高可用性的管理，如果没有理解这个，那么盲目分区并不一定能给你的性能产生理想的提升

1.2 mysql支持的分区类型

类型	实现	版本支持
RANGE分区	行数据基于一个给定连续区间的列值被放入分区	MySQL5.5开始支持
LIST分区	同RANGE分区，但是LIST面向的是离散的值	MySQL5.5开始支持
HASH分区	根据用户自定义表达式来分区
KEY分区	根据MySQL数据库提供的hash函数来分区

注意：不论哪种分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

二、分区类型

2.1 RANGE分区

介绍：RANGE分区是最常用的一种分区
实现：

CREATE TABLE t(
id INT
) ENGINE=INNODB
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20));

查看表在磁盘上的物理文件，启用分区之后，表不再由一个ibd文件组成了，而是由分区时各个分区的ibd文件组成，如下面的t#P#p0.ibd，t#P#p1.ibd

mysql> system ls -lh t*
 驱动器 E 中的卷没有标签。
 卷的序列号是 4688-FA57

 E:\mysql-8.0.23-winx64\data\easychat 的目录

2022-05-06  22:48    <DIR>          .
2022-05-06  22:48    <DIR>          ..
2022-03-20  21:51           114,688 login_info.ibd
2022-03-18  15:27           114,688 message_record.ibd
2022-05-06  22:49           114,688 t#p#p0.ibd
2022-05-06  22:49           114,688 t#p#p1.ibd
2022-04-28  17:06           131,072 user.ibd
               5 个文件        589,824 字节
               2 个目录 291,984,044,032 可用字节

接着插入以下数据：

mysql> insert into t select 9;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select 10;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select 15;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

因为表t根据列id进行分区，所以数据根据列id的范围存放在不同物理文件中

mysql> SELECT * FROM information_schema.PARTITIONS
    -> WHERE table_schema=database() AND table_name='t'\G;
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: easychat
                   TABLE_NAME: t
               PARTITION_NAME: p0
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: `id`
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 10
                   TABLE_ROWS: 1
               AVG_ROW_LENGTH: 16384
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2022-05-06 22:48:58
                  UPDATE_TIME: 2022-05-06 22:49:20
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: easychat
                   TABLE_NAME: t
               PARTITION_NAME: p1
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: `id`
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 20
                   TABLE_ROWS: 2
               AVG_ROW_LENGTH: 8192
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2022-05-06 22:48:58
                  UPDATE_TIME: 2022-05-06 22:49:31
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
2 rows in set (0.02 sec)

ERROR:
No query specified

TABLE_ROWS列反映了每个分区中记录的数量。可见p0一条，p1两条

2.2 LIST分区

LIST分区和RANGE分区非常相似，只是分区列的值是离散的，而非连续的。
例如：

mysql> CREATE TABLE m (
    -> a INT,
    -> b INT) ENGINE=INNODB
    -> PARTITION BY LIST(b) (
    -> PARTITION p0 VALUES IN (1,3,5,7,9),
    -> PARTITION P1 VALUES IN (0,2,4,6,8)
    -> );
Query OK, 0 rows affected (0.07 sec)

不同于RANGE 分区中定义的VALUES LESS THAN语句，LIST分区使用VALUES IN。
向表m中插入一些数据

mysql> INSERT INTO m SELECT 1,1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO m SELECT 1,2;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO m SELECT 1,3;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO m SELECT 1,4;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

我们可以通过以下命令查看数据存入情况

mysql> SELECT table_name,partition_name,table_rows
    -> FROM information_schema.PARTITIONS
    -> WHERE table_name='m' AND table_schema=DATABASE()\G;
*************************** 1. row ***************************
    TABLE_NAME: m
PARTITION_NAME: p0
    TABLE_ROWS: 2
*************************** 2. row ***************************
    TABLE_NAME: m
PARTITION_NAME: P1
    TABLE_ROWS: 2
2 rows in set (0.02 sec)

如果插入的值不在分区的定义中，那么msyql会抛出异常

mysql> INSERT INTO m SELECT 1,10;
ERROR 1526 (HY000): Table has no partition for value 10

2.3 HASH分区

HASH分区的目的是将数据均匀的分布在预先定义的各个分区中，保证个分区的数据量大致是一样的。
RANGE和LIST分区必须明确指定一个给定的列值或列值集合应该保存在那个分区中，而HASH分区中MySQL自动完成了这些工作。

创建HASH分区：

mysql> CREATE TABLE t_hash (
    -> a INT,
    -> b DATETIME
    -> )ENGINE=InnoDB
    -> PARTITION BY HASH (YEAR (b) )
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.14 sec)

插入一条值然后查看分区情况：

mysql> INSERT INTO t_hash SELECT 1,'2010-04-01';
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT table_name,partition_name,table_rows
    -> FROM information_schema.PARTITIONS
    -> WHERE table_name='t_hash' AND table_schema=DATABASE()\G;
*************************** 1. row ***************************
    TABLE_NAME: t_hash
PARTITION_NAME: p0
    TABLE_ROWS: 0
*************************** 2. row ***************************
    TABLE_NAME: t_hash
PARTITION_NAME: p1
    TABLE_ROWS: 0
*************************** 3. row ***************************
    TABLE_NAME: t_hash
PARTITION_NAME: p2
    TABLE_ROWS: 1
*************************** 4. row ***************************
    TABLE_NAME: t_hash
PARTITION_NAME: p3
    TABLE_ROWS: 0
4 rows in set (0.00 sec)

MySQL数据库还支持一种称为LINEAR HASH的分区，他使用一个更加复杂的算法来确定新行插入到已经分区的表中的位置。它的语法和HASH分区语法类似，只是讲HASH改为LINEAR HASH
LINEAR HASH分区的优点在于：添加、删除、合并和拆分分区将变得更加快捷，这有利于处理含有大量数据的表

2.4 KEY分区

KEY分区和HASH分区类似，不同在于HASH分区使用用户自定义的函数进行分区，KEY分区使用MySQL数据库提供的函数进行分区

mysql> CREATE TABLE t_key (
    -> a INT,
    -> b DATETIME
    -> )ENGINE=InnoDB
    -> PARTITION BY KEY (b) 
    -> PARTITIONS 4;

2.5 COLUMNS分区

RANGE、LIST、HASH和KEY这四种分区的条件必须是整形。
COLIMNS分区是RANGE、LIST的一种进化。COLUMNS分区可以直接使用非整型的数据进行分区，分区直接根据类型比较得到，不需要转化为整型。
COLUMNS分区支持一下数据类型：
1. INT、SMALLINT、TINYINT、BIGINT。FLOAT和DECIMAL则不予支持
2. 日期类型只支持DATE和DATETIME
3. 字符串类型支持CHAR、VARCHAR、BINARY和VARBINARY

2.6 子分区

子分区是在分区的基础上再进行分区，这种分区也称“复合分区”

mysql> CREATE TABLE ts (a INT, b DATE)engine=innodb
    -> PARTITION BY RANGE( YEAR(b))
    -> SUBPARTITION BY HASH( TO_DAYS(b))
    -> SUBPARTITIONS 2 (
    -> PARTITION p0 VALUES LESS THAN (1990),
    -> PARTITION p1 VALUES LESS THAN (2000),
    -> PARTITION p2 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.12 sec)