六、MySQL之视图与索引

最新推荐文章于 2024-10-12 12:22:40 发布

acro_09

最新推荐文章于 2024-10-12 12:22:40 发布

阅读量1.3k

点赞数 23

分类专栏：数据库文章标签： mysql 数据库 database sql

本文链接：https://blog.csdn.net/qq_65017742/article/details/135870136

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

6.1 视图

6.1.1 什么是视图

视图通过定制的方式显示来自一个表或多个表的数据。

视图的本质是数据库对象，如果成功的创建了视图，用户可以像操作普通表一样查询甚至修改视图内的数据。

视图的本身是没有任何的数据的，它只是对表的一个查询结果进行了实体化的反馈。

有关于视图所有的定义，保存在数据字典内，创建视图所基于的表称为"基表"。

6.1.2 为什么要使用视图

例如经常要对 student和grade表进行连接查询，每次都要做冗余的连接，才能完成后续的检索或分析过于麻烦。同时对于一些私密的信息，并不想让其他的操作人访问，我们就可以实现准备好一个定制化的视图供他去使用。

6.1.3 视图的作用和特点

作用

保存查询数据
控制安全

优点

提供了灵活一致级别的安全性
隐藏了数据原有的复杂性
简化了用户对于SQL指令的编辑
通过重命名列，从另一角度提供数据

6.1.4 视图的创建

#语法：
	CREATE [OR REPLACE] VIEW 视图名
		[别名1，别名2，.....]
		AS 必要的查询语句
		[额外的声明 ===>  WITH READ ONLY];
#如：创建视图，Student_V_1 包含了一号班级编号的所有学生的学号 姓名 班级名称 生日 住址
CREATE VIEW Student_V_1
AS SELECT student.StudentNo,student.StudentName,grade.GradeName,student.BornDate,student.Address
FROM student INNER JOIN grade ON student.GradeID = grade.GradeID
WHERE student.GradeID = 1;

CREATE VIEW Student_V_2
(学号,姓名,班级名,生日,邮箱)
AS SELECT student.StudentNo,student.StudentName,grade.GradeName,student.BornDate,student.Address
FROM student INNER JOIN grade ON student.GradeID = grade.GradeID
WHERE student.GradeID = 1;

#查询当前数据库内所有的视图
SHOW FULL TABLES WHERE Table_type = 'VIEW';

6.1.5 视图的使用规则

视图必须有唯一命名
mysql中对于视图的数量没有限制
创建视图的权限，必须要进行授权
视图支持嵌套,也就是说可以利用其他视图检索出来的数据创建新视图
视图内支持分组查和排序查，但是会覆盖掉原有的排序或分组机制
视图不能创建索引，也不能关联触发器
视图和表可以出现在同一查询语境内

6.1.6 视图的修改

#如替换原有视图信息
CREATE OR REPLACE Student_V_1
.......................
#比如XXX人创建视图时Email赋值赋的是生日 通过ALTER关键字可以实现视图中字段值的重新赋予
ALTER VIEW Student_V_1
(Email)
AS SELECT Email 
FROM student;

ALTER VIEW Student_V_2
(邮箱)
AS SELECT student.Email
FROM student INNER JOIN grade ON student.GradeID = grade.GradeID
WHERE student.GradeID = 1;

6.1.7 视图的删除

DROP VIEW 视图名;

6.2 索引

索引是一种很特殊的数据库结构，可以用来快速查询数据库表中的特定记录。它也是提高数据库性能的重要手段。从理论上分析，在MySql中，所有的数据类型，都可以被索引。常见的索引:普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等。

6.2.1 什么是索引

模式(schema)中的一个数据库对象
在数据库中用来加速对表的查询
通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
与表独立存放，但不能独立存在，必须属于某个表
由数据库自动维护，表被删除时，该表上的索引自动被删除。
索引的作用类似于书的目录，几乎没有一本书没有目录，因此几乎没有一张表没有索引。

索引的原理

将无序的数据变成有序的查询

把创建了索引的列的内容进行排序
对排序的结果生成相应的倒排表
在倒排表的内容上拼上数据地址链
在进行查询的时候，先拿到倒排表内容，再取出数据地址链，从而得到具体的数据

6.2.2 索引的优缺点

索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因。对于有依赖关系的子表和父表之间的
联合查询时，可以提高查询速度。使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。
索引的缺点是创建和维护索引需要耗费时间；耗费时间的数量随着数据量的增加而增加；索引需要占用物理空
间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。

6.2.3 索引分类

索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了。

聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快

常见的索引:普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等。

6.2.4 索引设计原则

为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。

尽量去选择唯一性索引。
为经常需要去排序、分组、联合操作的字段建立索引。
为经常作为查询条件的字段建立索引。
索引并不是越多越好，严格限制索引的数目。
尽量使用数据量小的索引。
及时的去删除一些使用很少或不再使用的索引。

6.2.5 创建索引

创建索引是指在某个表的一列或多列上建立索引的行为。有三种方式创建索引：创建表的时候（声明字段）时创建索引，在已经存在的表上创建索引，在使用ALTER TABLE语句来创建索引。

创建表的时候可以直接创建索引,这种方式是最为简单和方便的，语法如下:
CREATE TABLE 表名(
  字段名  字段类型 [约束条件]
  字段名  字段类型 [约束条件]
  字段名  字段类型 [约束条件]    
  [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
  [别名](属性名1 [(长度)] [ASC | DESC])
);
#示例1：创建表的时候创建索引
CREATE TABLE index1(
	id INT,
    name VARCHAR(20),
    Sex BOOLEAN,
    INDEX(id)
);
#修改表结构的方式添加索引
ALTER TABLE 表名 ADD INDEX 索引名(字段名);
#查询某表内索引的使用情况
SHOW INDEX FROM 表名;
#删除索引
DROP INDEX 索引名 ON 表名;

#示例2：创建唯一性索引
CREATE TABLE index2(
	id INT unique,            #通过申明约束去创建===>自动来的
    name VARCHAR(20),
    Unique INDEX index2_id(id asc)    #通过专门去做索引创建===>
);

ALTER TABLE index2 ADD INDEX index2_name(name);

#示例3：创建单列索引
CREATE TABLE index3(
    id int,
    Subject varchar(30),
    INDEX index3_st(subject(10))
); 

#示例4：创建多列索引
CREATE TABLE index4(
	id int,
    name VARCHAR(20),
    sex CHAR(4),
	INDEX index4_ns(name,sex)
);
使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。
如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。
也就是说多个单列索引与单个多列索引的查询效果不同，因为执行查询时，
MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。


#还可以通过 CREATE INDEX 去创建索引
首先要保证已经存在表，才能够使用这个命令去创建索引.
在已经存在的表上，可以直接为表上的一个或几个字段创建索引。
CREATE [ UNIQUE | FULLTEXT | SPATIAL ]  INDEX 索引名 ON 表名 (属性名 [ (长度) ] [  ASC | DESC] );

CREATE TABLE index5(
	id int,
    name VARCHAR(20),
    sex CHAR(4)
);
#为 name 字段创建普通索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index5_name ON index5 (name(10));

#还可以通过ALTER TABLE语句创建索引
首先要保证已经存在表，才能够使用这个命令去创建索引.
ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (属性名 [ (长度) ] [  ASC | DESC] );

CREATE TABLE index6(
	id int,
    name VARCHAR(20),
    sex CHAR(4)
);

ALTER TABLE index6 ADD INDEX index6_name (name(10));

6.2.6 删除索引

删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。

DROP INDEX 索引名 ON 表名;

6.2.7 索引的使用场景和索引的优化

1) 快速查找符合where条件的记录。

2) 快速确定候选集。若where条件使用了多个索引字段，则MySQL会优先使用能使候选记录集规模最小的那
个索引，以便尽快淘汰不符合条件的记录。

3) 如果表中存在几个字段构成的联合索引，则查找记录时，这个联合索引的最左前缀匹配字段也会被自动作
为索引来加速查找。
例如:若为某表创建了3个字段(c1, c2, c3)构成的联合索引，则(c1), (c1, c2), (c1, c2, c3)均
会作为索引，(c2, c3)就不会被作为索引，而(c1, c3)其实只利用到c1索引。

4) 多表做join操作时会使用索引（如果参与join的字段在这些表中均建立了索引的话）。

5) 若某字段已建立索引，求该字段的min()或max()时，MySQL会使用索引。

6) 对建立了索引的字段做sort或group操作时，MySQL会使用索引。

索引的优化

动作描述	使用聚簇索引	使用非聚簇索引
列经常被分组查询	1	1
返回某范围内的数据	1	0
一个或极少的范围值	0	0
小数目的不同值	1	0
大数目的不同值	0	1
频繁更换的列	0	0
外键列	1	1
主键列	1	1
频繁修改的列	0	0.5

6.2.8 聚餐索引和非聚簇索引

非聚簇索引

索引节点的叶子页面就好比一片叶子。叶子头便是索引键值

会利用索引，先在索引树中快速检索到 id，但是要想取到id对应行数据，必须找到该行数据在硬盘中的存储位置。

因此MYISAM引擎的索引，叶子页面上不仅存储了主键id 还存储着数据存储的地址信息。

像这样的索引就称为非聚簇索引。它的二级索引与主键索引类似。

聚簇索引

对于非聚簇索引来说，每次通过索引检索到所需行号后，还需要通过叶子上的磁盘地址去磁盘内取数据（回行）消耗时间。为了优化这部分回行取数据时间，InnoDB
引擎采用了聚簇索引。

聚簇索引，即将数据存入索引叶子页面上。对于 InnoDB 引擎来说，叶子页面不再存该行对应的地址，而是直接存储数据。

这样就可以避免回行操作所带来的时间消耗。

关于查询时间，一般认为 MyISAM 牺牲了功能换取了性能，查询更快。但事实并不一定如此。多数情况下，MyISAM 确实比 InnoDB
查的快。但是查询时间受多方面因素影响。InnoDB 查询变慢得原因是因为支持事务、回滚等等，使得
InnoDB的叶子页面实际上还包含有事务id（换句话说就是版本号）以及回滚指针。

在二级索引方面 InnoDB与MyISAM 有很大的区别

InnoDB默认对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。