目录
1.1 视图
1.1.1 什么是视图
视图通过 定制的方式显示来自一个表或者多个表的数据
视图的本质是 数据库对象， 如果成功的创建了视图，用户可以像操作普通表一样，查询甚至修改视图内的数据
视图的本身是没有任何的数据的，视图只是对表的一个查询结果进行了实体化的反馈
有关于视图所有的定义，保存在数据字典内，创建视图所基于的表称为 “基表”
1.1.2 为什么要使用视图
当我们经常对两个表进行连接查询的时候，每次都要做冗余的连接，才能完成后续的检索或分析过于麻烦，同时对于一些私密的信息，并不想让其他的操作人访问，我们就可以实现准备好一个定制化的视图去使用
1.1.3 视图的作用和特点
作用
1、保存查询数据
2、控制安全
优点
1、提供了灵活一致级别的安全性
2、隐藏了数据原有的复杂性
3、简化了用户对于SQL指令的编辑
4、通过重命名列，从另一角度提供数据
1.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;查询当前数据库内所有的视图
SHOW FULL TABLES WHERE Table_type = 'VIEW';1.1.5 视图的使用规则
| 视图必须有唯一命名 |
| MySQL中对于视图的数量没有限制 |
| 创建视图的权限，必须要进行授权 |
| 视图支持嵌套，也就是说可以利用其他视图检索出来的数据创新视图 |
| 视图内支持分组差和排序查，但是会覆盖掉原有的排序或分组机制 |
| 视图不能创建索引，也不能关联触发器 |
| 视图和表可以出现在同一查询语境内 |
1.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_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;1.1.7 视图的删除
语法
DROP VIEW 视图名;1.2 索引
索引：
是一种很特殊的数据库结构，可以用来快速查询数据库表中的特定记录
也是提高数据库性能的很重要手段
从理论上分析，在MySQL中，所有的数据类型，都可以被索引
常见的索引
| 普通索引 |
| 唯一性索引 |
| 全文索引 |
| 单列索引 |
| 多列索引 |
| ....... |
1.2.1 什么是索引
模式（schema）中的一个数据库对象
在数据库中用来加速对表的查询
通过使用快速路径访问方法快速定位数据，减少了磁盘的 I/O口
与表独立存放，但不能独立存在，必须属于某个表
由数据库自动维护，表被删除的时候，该表上的索引自动被删除
索引的作用类似于书的目录，几乎没有一本书没有目录，因此几乎没有一张表没有索引
索引的原理
将无序的数据变成有序的查询
1、把创建了索引的列的内容进行排序
2、对排序的结果生成相应的倒排表
3、在倒排表的内容上拼上数据地址链
4、在进行查询的时候，先拿到倒排表内容，再取出数据地址链，从而得到具体的数据
1.2.2 索引的优缺点
优点
可以提高检索数据的速度，这是创建索引的最主要的原因
对于有依赖关系的子表和父表之间的
联合查询时，可以提高查询速度，使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间
缺点
创建和维护索引需要耗费时间
耗费时间的数量随着数据量的增加而增加
索引需要占用物理空间，每一个索引要占一定的物理空间
增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了
1.2.3 索引分类
聚簇索引 和 非聚簇索引
聚簇索引是 按照数据存放的物理位置为顺序的，非聚簇索引不一样
聚簇索引 能提高多行检索的速度，而非聚簇索引对于单行的索引很快
1.2.4 索引设计原则
为了使索引的使用效率更高，在创建索引的时候 必须考虑在那些字段上创建索引 和 创建什么类型的索引
| 尽量去选择唯一性索引 |
| 为经常需要去排序、分组、联合操作的字段建立索引 |
| 为经常作为查询条件的字段建立索引 |
| 索引并不是越多越好，严格限制索引的数目 |
| 尽量使用数据量小的索引 |
| 及时的去删除一些使用很少或不再使用的索引 |
1.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);
创建单列索引和创建多列索引
#创建单列索引
CREATE TABLE index3(
id int,
Subject varchar(30),
INDEX index3_st(subject(10))
);
#创建多列索引
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));1.2.6 删除索引
删除索引
指将表中已经存在的索引删除掉
一些不再使用的索引会降低表的更新速度，影响数据库的性能
DROP INDEX 索引名 ON 表名;1.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 |
1.2.8 聚簇索引和非聚簇索引
非聚簇索引
索引节点的叶子页面就好比一片叶子。叶子头便是索引键值
会利用索引，先在索引树中快速检索到 id，但是要想取到id对应行数据，必须找到该行数据在硬盘中的存储位置。
因此MYISAM引擎的索引，叶子页面上不仅存储了主键id 还存储着 数据存储的地址信息。
像这样的索引就称为非聚簇索引。它的二级索引与主键索引类似。
聚簇索引
对于非聚簇索引来说，每次通过索引检索到所需行号后，还需要通过叶子上的磁盘地址去磁盘内取数据（回行）消耗时间。为了优化这部分回行取数据时间，InnoDB 引擎采用了聚簇索引。
聚簇索引，即将数据存入索引叶子页面上。对于 InnoDB 引擎来说，叶子页面不再存该行对应的地址，而是直接存储数据。
这样就可以避免回行操作所带来的时间消耗。
关于查询时间，一般认为 MyISAM 牺牲了功能换取了性能，查询更快。但事实并不一定如此。多数情况下，MyISAM 确实比 InnoDB 查的快 。但是查询时间受多方面因素影响。InnoDB 查询变慢得原因是因为支持事务、回滚等等，使得 InnoDB的叶子页面实际上还包含有事务id（换句话说就是版本号） 以及回滚指针。
在二级索引方面 InnoDB与MyISAM 有很大的区别
InnoDB默认对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。
扫一扫
2794
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
