【数据库】MySQL 索引视图详解
赞
踩
目录
MySQL索引视图
视图
一，什么是视图
视图通过以定制的方式显示来自一个或多个表的数据
视图是一种数据库对象，用户可以像查询普通表一样查询视图
视图内其实没有存储任何数据，它只是对表的一个查询
视图的定义保存在数据字典内，创建视图所基于的表称为“基表”
二，为什么需要视图
例如经常要对emp和dept表进行连接查询，每次都要做表的连接，写同样的一串语句，同时由于工资列队数据比较敏感，对外要求不可见。对这样的问题就可以通过视图来解决。
三，视图的作用和优点
作用：
- 控制安全
- 保存查询数据
优点：
- 提供了灵活一致级别安全性。
- 隐藏了数据的复杂性
- 简化了用户的SQL指令
- 通过重命名列，从另一个角度提供数据
四，创建视图
CREATE [OR REPLACE] VIEW 视图名 [(列名[, 列名]...)]
AS select statement
[WITH [CASCADED | LOCAL] CHECK OPTION];
说明：
（1）as select statement表示select语句；
（2）WITH [CASCADED | LOCAL] CHECK OPTION表示视图在更新时，保证在视图的权限范
围之内，CASCADED为默认值，表示更新视图时要满足所有相关视图和表的条件，local表
示更新视图时满足该视图本身定义的条件即可。
案例：
表employee和表dept 的所有信息如下：
1，创建视图view1（视图的列名与employee表中的列名一致）
create view view1 as select name,e_id,salary from employee where dept_id=2;
查看视图view1的结果
mysql8.0.30 [chap04]>select * from view1;
2，创建视图view2（视图的列名与employee表中的列名不一致）
create view view2(姓名,编号,工资) as select name,e_id,salary from employee where dept_id=2;
查看视图2的结果
mysql8.0.30 [chap04]>select * from view2;
3，创建视图view2,（视图的列名与employee表中的列名不一致）
create view view3 as select name 姓名,e_id 编号,salary 工资 from employeewhere dept_id=2;
查看视图view3的效果如下：
select * from view3;
五，视图使用规则
- 视图必须有唯一命名
- 在mysql中视图的数量没有限制
- 创建视图必须从管理员那里获得必要的权限
- 视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图
- 在视图中可以使用OREDR BY，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的 ORDER BY。
- 视图不能索引，也不能关联触发器或默认值
- 视图可以和表同时使用
六， 修改视图
使用CREATE OR REPLACE VIEW 语句修改emp_view_2 视图，为每个列指定列名。
或在子查询中指定别名使用ALTER VIEW 语句修改EMP_V_10 视图，为每个列指定列名，
也可以在select查询中指定别名。在以上语句中字段与子查询中的字段必须一一对应，否则就别指定。
1，修改列名
修改列名前的视图view1如下所示：
方法一：
使用create or replace view 语句修改 view1视图的列名
create or replace view view1(name,id,salary) as select name,e_id,salary from employee where dept_id=2;
修改列名后的视图view1如下所示：
方法二：
使用alter view 语句修改 view1视图的列名
alter view view1(姓名,编号,工资) as select name,e_id,salary from employee where dept_id=2;
修改列名后的视图view1如下所示：
2，创建复杂视图，
创建一个从两个表中查询数据，并进行分组计算的复杂视图。
在数据库chap05里面创建表employee;
- create table employee(
-
- id int,
-
- name char(30),
-
- dept_id int,
-
- salary int
-
- );
向表employee中插入数据
- insert into employee values
-
- (100,'xiaoming',1,2),
-
- (102,'xiaohong',1,3),
-
- (102,'xiaohei',2,2),
-
- (102,'xiaobai',3,4),
-
- (104,'xiaolv',5,1),
-
- (105,'xiaolan',4,3),
-
- (106,'xiaozi',4,4);
查看employee表下的所有信息
mysql8.0.30 [chap05]>select * from employee;
在数据库chap05里面创建表dept
- create table dept(
-
- id int,
-
- name char(20)
-
- );
向表dept中插入数据
- insert into dept values
-
- (1,'sale'),
-
- (2,'hr'),
-
- (3,'tech'),
-
- (4,'finance'),
-
- (5,'boss'),
-
- (6,'houqin');
查看dept表下的所有信息
mysql8.0.30 [chap05]>select * from dept;
按部门查询所有员工姓名，部门的平均工资，最高工资，最低工资
select d.name 部门,group_concat(e.name) 员工,avg(salary) 平均工资,max(salary) 最高工资,min(salary) 最低工资 fromm employee e,dept d where d.id=e.dept_id group by d.name;
（1）创建视图
create view view1 as select d.name 部门,group_concat(e.name) 员工,avg(salary) 平均工资,max(salary) 最高工资,min(salary) 最低工资 from employee e,dept d where d.id=e.dept_id group by d.name;
（2）查看视图：
七， 删除视图
删掉视图不会导致数据的丢失，因为视图是基于数据库的表之上的一个查询定义.
DROP VIEW view_name;
案例演示：
1、在数据库example下创建college表。
（1）创建数据库example
create database example;
（2）在数据库example下面创建college表
- create table college(
-
- number int primary key,
-
- name char(20) not null,
-
- major char(30) not null,
-
- age int
-
- );
2、在college表上创建视图college_view。视图的字段包括student_num、student_name、student_age和student_major。
mysql8.0.30 [example]>create view college_view(student_num,student_name,student_major,student_age) as select * from college;
3、查看视图college_view的信息
方法一：mysql8.0 [example]>show create view college_view\G
方法二：mysql8.0 [example]>desc college_view;
方法三：
mysql8.0.30 [example]>select * from college;
4、 更新视图。向视图中插入3条记录。记录内容如下表所示
- insert into college_view values
-
- (2003001,'zhangsan','english',18),
-
- (2003002,'lisi','IT',19),
-
- (2003003,'wangwu','IT',17);
查看视图college_view
mysql8.0.30 [example]>select * from college_view;
表college也会更新，
5 、修改视图，使其显示专业为计算机的信息，其他条件不变
方法一：
mysql8.0.30 [example]>alter view college_view as select * from college where major='IT';
方法二：
mysql8.0.30 [example]>create or replace view college_view as select * from college where major='IT';
二者结果均如下：
mysql8.0.30 [example]>select * from college_view;
6、删除视图college_view
mysql8.0.30 [example]>drop view college_view;
索引
索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。
一，什么是索引
模式(schema)中的一个数据库对象
在数据库中用来加速对表的查询
通过使用快速路径访问方法快速定位数据，减少了磁盘的I/O
与表独立存放，但不能独立存在，必须属于某个表
由数据库自动维护，表被删除时，该表上的索引自动被删除。
索引的作用类似于书的目录，几乎没有一本书没有目录，因此几乎没有一张表没有索引。
二，索引优缺点
索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。
索引的缺点是创建和维护索引需要耗费时间，耗费的时间随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。
三，索引分类
按物理存储分类，索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。
按字段特性，MySQL的索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等。
四，索引的设计原则
为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。索引的设计原则：
- 选择惟一性索引
- 为经常需要排序、分组和联合操作的字段建立索引
- 为常作为查询条件的字段建立索引
- 限制索引的数目
- 尽量使用数据量少的索引
- 尽量使用前缀来索引
- 删除不再使用或者很少使用的索引
五，创建索引
创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有三种方式，这三种方式分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER TABLE语句来创建索引。
创建表的时候可以直接创建索引，这种方式最简单、方便。其基本形式如下：
CREATETABLE表名 (
列名 数据类型 [完整性约束条件],列名 数据类型 [完整性约束条件],…,
[UNIQUE| FULLTEXT| SPATIAL] [INDEX| KEY] [index_name] (key_part,...) [index_option]
);
在已经存在的表上，可以直接为表上的一个或几个字段创建索引。基本形式如下：
CREATE[ UNIQUE| FULLTEXT| SPATIAL] INDEX索引名
ON表名 (属性名 [ (长度) ]);
在已经存在的表上，可以通过ALTER TABLE语句直接为表上的一个或几个字段创建索引。基本形式如下：
ALTER TABLE表名 ADD [ UNIQUE| FULLTEXT| SPATIAL] INDEX[索引名] (属性名 [ (长度) ]);
1、创建普通索引
(1)创建表的时候同时创建索引
给index1表中的id字段创建的索引index_id
- create table index1(
-
- id int,
-
- name char(30),
-
- gender char(1),
-
- index index_id(id)
-
- );
查看创建表的信息
show create table index1;
查看表的索引信息：show index from 索引名;
mysql8.0.30 [example]>show index from index1;
在表里面插入数据
- insert into index1 values
-
- (1,'xiaoming','M'),
-
- (2,'xiaohong','F'),
-
- (3,'xiaohei','M'),
-
- (4,'xiaobai','M'),
-
- (5,'xiaolan','F'),
-
- (6,'xiaolv','F');
查看MySQL如何在内部执行查询
explain select * from index1;
explain select * from index1\G
方法一 ：
通过索引，mysql只需要扫描一行数据就扫描到了id=1的行
explain select * from index1 where id=1\G
方法二：
通过索引，mysql只需要扫描一行数据就扫描到了id=1的行
mysql8.0.30 [example]>explain select * from index1 where id=1;
创建一张和index1一样的表t1;
create table t1 select * from index1;
查看创建表t1的指令
show create table t1;
查看表t1的所有信息
表t1没有索引，mysql必须扫描包含6行数据的整个表数据才能查找到id=1的数据
explain select * from t1 where id=1\G
(2)给已经存在的index1表创建索引:CREATE INDEX 索引名 ON 表名
(属性名 [ (长度) ]);
给已经存在的表t1创建索引id
create index id on t1(id);
此时查看创建表t1的指令，多了创建索引的指令
show create table t1;
此时，通过索引，mysql只需要扫描一行数据就扫描到了id=1的行
explain select * from t1 where id=1\G
（3）在已经存在的index表上创建索引:ALTER TABLE table_name ADD INDEX
[index_name] (column(length))
给index1表中的name属性加一个没有命名的索引
查看创建表index1的的指令，发现多了一条给属性(name)创建索引的指令，
且默认索引名与属性名相同
show create table index1;
此时，通过索引，mysql只需要扫描一行数据就扫描到了name='xiaoming'的行
explain select * from index1 where name='xiaoming'\G
删除索引：DROP INDEX index_name ON table
删除index1表中的name索引
drop index name on index1;
删除索引后，mysql必须扫描包含6行数据的整个表数据才能查找到name=‘xiaoming的数据
explain select * from index1 where name='xiaoming'\G
查看创建表index1的指令，发现给属性创建的索引指令被删除了
show create table index1;
2、创建唯一性索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
（1）创建表时创建唯一性索引
创建表index2
- create table index2(
-
- id int unique,
-
- name char(20),
-
- unique index (id)
-
- );
查看创建index2的创建指令，发现多了一行给属性id创建的唯一性索引
show create table index2;
查看表index2有几个索引
show index from index2;
查看表index2的表结构
desc index2;
（2）在已经存在的表上创建索引：CREATE UNIQUE INDEX 索引名 ON 表名(column(length));
给表index2的name属性创建一个唯一性索引
create unique index name on index2 (name(2));
查看创建index2的创建指令，发现多了一行给属性name创建的唯一性索引
show create table index2;
（3）在已经存在的表上创建索引：ALTER TABLE 表名 ADD UNIQUE [index] [indexName] (column(length));
表college没有索引
show create table college;
给表college的member属性创建一个member唯一索引
alter table college add unique index number(number);
查看创建college的创建指令，发现多了一行给属性number创建的唯一性索引number
show create table college;
3、创建全文索引（FULLTEXT）
全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建；
全文索引允许在索引列中插入重复值和空值；
对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更快；
不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
（1）只能创建在char,varchar或text类型的字段上
创建index3表
show create table index3;
（2）为已有表添加全文索引：CREATE FULLTEXT INDEX 索引名称 ON 表名(column(length));
（3）为已有表添加全文索引：ALTER TABLE 表名 add fulltext index [索引名](column(length));
4、创建单列索引
（1）
create table index4( id int, name char(30), index(name) );
（2）为已有表创建单列索引
（3）为已有表创建单列索引：ALTER TABLE 表名 add index [索引名](column(length));
5、创建多列索引
使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。 也就是说多个单列索引与单个多列索引的查询效果不同，因为执行查询时， MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。
创建表index5
- create table index5(
-
- id int,
-
- name char(30),
-
- gender char(1),
-
- age int,
-
- major char(10)
-
- );
-
-
创建单个多列索引
create index name_gender on index5(name,gender);
创建多个单个索引
向表index5中插入记录
- insert into index5 values
-
- (1,'xiaoming','M',18,'english'),
-
- (2,'xiaohei','M',17,'chinese'),
-
- (3,'xiaohong','F',18,'math'),
-
- (4,'xiaobai','F',19,'IT');
explain select * from index5 where id=2\G
组合左边的索引才能用
6、创建空间索引
空间索引主要是对地理空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行创建。创建空间索引时，索引字段必须有非空约束。
空间数据类型包括geometry,point,linestring和polygon类型等。geometry字段类型可以存储坐标点信息并进行一系列的关系计算。平时很少用到。
六，删除索引
删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。
（1）对应已经存在的索引，可以通过DROP语句来删除索引。基本形式如下：
DROP INDEX索引名 ON表名 ;
alter table index5 drop index major;
Alter table 表名 drop index 索引名
drop index id on index5;
七， 聚簇索引和非聚簇索引
聚簇索引：将索引与数据放在一起，当你找到索引后，也就找到对应的数据了。每张表只能建立一个聚簇索引，但是该索引可以包含多个列（一般使用的是主键等不经常更新的列）。
非聚簇索引：数据储存与索引分开，叶节点指向了对应的数据行。myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。innodb中，在聚簇索引之上创建的索引称之为辅助索引，需要二次查找，辅助索引也是非聚簇索引。
非聚簇索引
索引节点的叶子页面就好比一片叶子。叶子头便是索引键值。
先创建一张表：
CREATETABLE`user`(
`id`INTNOTNULL,
`name`VARCHARNOTNULL,
`class`VARCHARNOTNULL);
对于MYISAM引擎，如果创建 id 和 name 为索引。对于下面查询：
select* fromuserwhereid = 1
会利用索引，先在索引树中快速检索到 id，但是要想取到id对应行数据，必须找到该行数据在硬盘中的存储位置，因此MYISAM引擎的索引，叶子页面上不仅存储了主键id还存储着数据存储的地址信息。如图：
像这样的索引就称为非聚簇索引。
非聚簇索引的二级索引与主键索引类似。假设我们对name添加索引，那么name的索引树叶子将是如下结构：
聚簇索引
对于非聚簇索引来说，每次通过索引检索到所需行号后，还需要通过叶子上的磁盘地址去磁盘内取数据（回行）消耗时间。为了优化这部分回行取数据时间，InnoDB 引擎采用了聚簇索引。
聚簇索引，即将数据存入索引叶子页面上。对于 InnoDB 引擎来说，叶子页面不再存该行对应的地址，而是直接存储数据：
这样便避免了回行操作所带来的时间消耗。 使得 InnoDB 在某些查询上比 MyISAM 还要快！
关于查询时间，一般认为 MyISAM 牺牲了功能换取了性能，查询更快，但事实并不一定如此。多数情况下，MyISAM 确实比 InnoDB 查的快 。但是查询时间受多方面因素影响。InnoDB 查询变慢得原因是因为支持事务、回滚等等，使得 InnoDB的叶子页面实际上还包含有事务id（换句话说就是版本号） 以及回滚指针。
在二级索引方面， InnoDB 与 MyISAM 有很大区别。
InnoDB默认对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，InnoDB 会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。
假设对 InnoDB 引擎上name字段加索引，那么name索引叶子页面则只会存储主键id：
检索时，先通过name索引树找到主索引id，再通过id在主索引树的聚簇索引叶子页面取出数据。
八， MySQL使用索引的场景
1) 快速查找符合where条件的记录。若like语句的条件是不以通配符开头的常量串，MySQL也会使用索引。比如，SELECT * FROMtbl_name WHEREkey_col LIKE'Patrick%'或SELECT * FROMtbl_name WHEREkey_col LIKE'Pat%_ck%'可以利用索引，而SELECT * FROMtbl_name WHEREkey_col LIKE'%Patrick%'（以通配符开头）和SELECT * FROMtbl_name WHEREkey_col LIKEother_col（like条件不是常量串）无法利用索引。对于形如LIKE '%string%'的sql语句，若通配符后面的string长度大于3，则MySQL会利用Turb Boyer-Moore algorithm算法进行查找。
若已对名为col_name的列建了索引，则形如"col_name is null"的SQL会用到索引。
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会使用索引。
MySQL索引的优化
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件则会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。下面是一些总结以及收藏的MySQL索引的注意事项和优化方法。
何时使用聚集索引或非聚集索引
