MySQL学习(16)︱分区表与视图

什么是分区表

分区表就是按照某种规则将同一张表的数据分段划分到多个位置存储。对数据的分区存储提高了数据库的性能，被分区存储的数据在物理上是多个文件，但在逻辑上仍然是一个表，对表的任何操作都跟没分区一样。在执行增、删、改、查等操作时，数据库会自动找到对应的分区，然后执行操作。

• MySQL从5.1.3开始支持分区
• 在MySQL8.0中，只有InnoDB和NDB两个存储引擎支持分区

分区表的好处

• 存储更多：与单个磁盘或文件系统分区相比，可以存储更多的数据
• 便于管理：很容易根据分区删除失去保存意义的历史数据
• 提升查询效率：一些查询可以极大地优化，查询仅从某个或某几个分区中获取数据
• 并行处理：涉及到sum()、count()等聚合函数的查询，可以很容易进行并行处理
• 提高查询吞吐：通过跨多个磁盘来分散数据查询，获得更大的查询吞吐量

分区表的四种类型

MySQL支持的分区类型包括Range、List、Hash和Key，其中Range最常用。

• Range分区：允许将数据划分不同的范围。例如可以将一个表通过年份划分成多个分区
• List分区：允许系统通过预定义的列表的值来对数据进行分割
• Hash分区：允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数据对应的数据区域进行分区
• Key分区：对Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的

Range分区

以最常用的Range分区，现给出语法示例：

CREATE TABLE user_range(
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(30),
	age INT,
	birthday DATE,
	province INT,
	PRIMARY KEY(id, age)//主键必须包含分区字段！！！
)
PARTITION BY RANGE(age)(
	PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY = 'c:/data/p1',
	PARTITION p2 VALUES LESS THAN (40) DATA DIRECTORY = 'c:/data/p2',
	PARTITION p3 VALUES LESS THAN (60) DATA DIRECTORY = 'c:/data/p3',
	PARTITION p4 VALUES LESS THAN MAXVALUE DATA DIRECTORY = 'c:/data/p4'
);

什么是视图

视图是一个虚拟表，其内容由SELECT查询语句定义。和真实的表一样，视图也包括行和列，对视图的操作与对表的操作基本一致。视图中的数据是在使用视图时动态生成，视图中的数据都存储在基表中。视图有3个性质：

• 可读性：简化了复杂的查询，使复杂的查询更易于理解和使用
• 重用性：视图是对复杂查询语句的封装，对数据库重构，不会影响程序的运行
• 安全行：视图可以隐藏一些敏感信息，可以把权限限定到行列级别

视图的基本操作

视图表的数据变化会影响到基表，基表的数据变化也会影响到视图。

• 创建视图：

CREATE VIEW view_name AS SELECT...;

• 修改视图：

ALTER VIEW view_name AS SELECT...;

• 查看视图创建语句：

SHOW CREATE VIEW view_name;

• 查看有哪些视图：

SHOW TABLE STATUS WHERE COMMENT = 'view';

• 删除视图：

DROP VIEW view_name;

例子

1. 登陆数据库，使用student数据库并查看所有的数据表

USE student;
SHOW TABLES;

效果图：

2. 查看stu和score数据表的所有记录

SELECT * FROM stu;
SELECT * FROM score;

效果图：

3. 根据第2步的select语句创建视图

SELECT * FROM student_view;

效果图：

4. 再次查看所有的数据表，会发现多了一个刚刚创建的视图

SHOW TABLES;

效果图：

5. 查看有哪些视图

SHOW TABLE STATUS WHERE COMMENT = 'view'\G;

效果图：

6. 将视图中学号为2021003的学生更名

UPDATE student_view SET name = '张晓燕2' WHERE stu_no='2021003';

效果图：

7. 再次查看视图和基表，发现数据都产生了变化

SELECT * FROM student_view;
SELECT * FROM stu;

效果图：


8. 将第7步的改动变回去

UPDATE stu SET name = '王五' WHERE stu_no='2021003';
SELECT * FROM student_view;
SELECT * FROM stu;

效果图：

9. 使用以前的知识，连接两张表

SELECT A.*,B.course, B.score FROM stu A LEFT JOIN score B ON(A.stu_no = B.stu_no);

效果图：

10. 根据第9步的语句创建视图，再与其对比，效果一致

CREATE VIEW stu_score_view AS SELECT A.*,B.course, B.score FROM stu A LEFT JOIN score B ON(A.stu_no = B.stu_no);

效果图：

注：本文是博主MySQL学习的总结，不支持任何商用，转载请注明出处！如果你也对MySQL学习有一定的兴趣和理解，欢迎随时找博主交流~