09-索引和视图

合集 - mysql(11)

1.02-数据库和表的基本操作2023-07-302.03-简单查询2023-07-303.04-条件查询2023-07-304.05-分组汇总查询2023-07-305.06-连接查询和子查询2023-07-306.07-数据定义2023-07-307.08-存储引擎和事务2023-07-30

8.09-索引和视图2023-07-30

9.10-DBA命令和数据库设计的三范式2023-07-3010.11-MySQL数据库的存储过程2023-07-3011.12-MySQL数据库的触发器2023-07-30

收起

09-索引和视图

课程目标

掌握索引原理、索引的应用、 视图的概念、创建视图、修改视图、删除视图。

9.1索引原理

索引被用来快速找出在一个列上某一特定值的行。没有索引，MySQL不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行。表越大，花费时间越多。对于一个有序字段，可以运用二分查找（Binary Search），这就是为什么性能能得到本质上的提高。MyISAM和InnoDB都是用B+Tree作为索引结构

（主键，unique 都会默认的添加索引）

9.2 索引的应用

1.创建索引

如果未使用索引，我们查询 工资大于 1500的会执行全表扫描，如下图9-1所示：

图9-1

什么时候需要给字段添加索引：

表中该字段中的数据量庞大

经常被检索，经常出现在where子句中的字段

经常被DML操作的字段不建议添加索引

索引等同于一本书的目录

主键会自动添加索引，所以尽量根据主键查询效率较高。

如经常根据sal进行查询，并且遇到了性能瓶颈，首先查看程序是否存算法问题，再考虑对sal建立索引，建立索引如下，如下图9-2所示：
create unique index 索引名 on 表名(列名); 

create unique index u_ename on emp(ename);
alter table 表名 add unique index 索引名 (列名); 

create index test_index on emp(sal);

图9-2

2.查看索引，如下图9-3所示：

show index from emp;

图9-3

3.使用索引，如下图9-4所示：

注意一定不可以用select * … 可以看到type!=all了，说明使用了索引

explain select sal from emp where sal > 1500;

条件中的sal使用了索引

图9-4

如上图：假如我们要查找sal大于1500的所有行，那么可以扫描索引，索引时排序的，结果得出7行，我们知道不会再有匹配的记录，可以退出了。
如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描（如二分查找法）。
这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，通常这些技术都属于DBA的工作。

4.删除索引

drop index index_name on talbe_name

alter table table_name drop index index_name

alter table table_name drop primary key

其中，前两条语句是等价的，删除掉table_name中的索引index_name。

第3条语句只在删除primary key索引时使用，因为一个表只可能有一个primary key索引，

MySQL> alter table emp drop index test_index;

删除后就不再使用索引了，查询会执行全表扫描,如下图9-5所示：

图9-5

9.3 视图的概念

视图是一种根据查询（也就是select表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。

视图有时也被成为“虚拟表”。

视图可以被用来从常规表（称为“基表”）或其他视图中查询数据。

相对于从基表中直接获取数据，视图有以下好处：

访问数据变得简单

可被用来对不同用户显示不同的表的内容

用来协助适配表的结构以适应前端现有的应用程序

视图作用：

提高检索效率

隐藏表的实现细节【面向视图检索】

如下图9-6所示：

图9-6

9.4 创建视图

如下示例：查询员工的姓名，部门，工资入职信息等信息。

select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno= e.deptno and e.deptno = 10;

为什么使用视图？因为需求决定以上语句需要在多个地方使用，如果频繁的拷贝以上代码，会给维护带来成本，视图可以解决这个问题

create view v_dept_emp as select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno　= e.deptno and e.deptno = 10;

create view v_dept_avg_sal_grade as select a.deptno, a.avg_sal, b.grade from (select deptno, avg(sal) avg_sal from emp group by deptno) a, salgrade b

where a.avg_sal between b.losal and b.hisal; /*注意MySQL不支持子查询创建视图*/

9.5 修改视图

alter view v_dept_emp as select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno = 20;

9.6 删除视图

drop view if exists v_dept_emp;

9.7 本章小结

本章主要阐述了MySQL中的视图。