mysql--索引和视图

素颜马尾好姑娘i 2022-10-23 08:28 164阅读 0赞

索引和视图  
什么是索引?有什么用?  
索引就相当于一本书的目录,通过目录可以快速的找到对应的资源.  
在数据库方面,查询一张表的时候有两种查询方式:  
第一种方式:全表扫描.  
第二种方式:根据索引检索(效率远高于遍历查询)

索引为什么可以提高检索效率呢?
    		其实最根本的原理是缩小了扫描的范围.
    
    	索引虽然可以提高检索效率,但是不能随意的添加索引,因为索引也是数据库当中的对象,也需要数据库对其不断的维护,是有维护成本的.
    	比如,表中的数据经常被修改,这样就很不适合添加索引,因为数据一旦修改,索引就要重新排序,进行维护.
    
    	添加索引是给某一个字段或者说某些字段添加索引.
    
    	select ename, sal from emp where ename = 'smith';
    	当ename字段上没有添加索引的时候,以上sql语句会进行全表扫描,扫描ename字段中的所有数据.
    	当ename字段上添加索引的时候,以上sql语句会根据索引扫描,快速定位.
    
    怎么创建和删除与索引?
    	给emp表,sal字段添加索引
    	create index emp_sal_index on emp(sal);
    
    	删除索引:
    	drop index emp_sal_index on emp;
    什么时候考虑给给段添加索引?(满足什么条件)
    	数据量庞大(根据客户的反应来看,有时机器nb,即使不添加索引也可以,有的机器拉垮,即使小表也得添加索引)
    	该字段很难少的DML操作
    	该字段经常出现在where子句中.
    
    注意:主键和具有unique约束的字段会自动添加索引
    
    查看sql语句的执行计划(只有mysql才有)
    explain select ename, sal from emp where sal = 5000;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    
    注意查询结果中的type的值为ALL,说明是对全表扫描.
    rows为14说明扫了14条记录.
    
    若使用索引来检索
    +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
    
    索引的底层采用的数据结构: B Tree
    
    索引的实现原理:
    	当执行创建索引命令之后,可能在内存也有可能在硬盘(具体看存储引擎)生成一个索引表.
    
    	数据库中表的任何一条数据都有一个物理地址(物理地址在Oracle当中叫做rowid)
    
    	当根据索引找到目标记录之后,会得到该记录的物理地址,相应的查询语句发生变化.
    
    	select ename, sal from emp where 物理地址 = ... ;	//这条语句不再走表,直接查询存储位置.
    
    	原理总结: 通过B Tree缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的"物理地址",
    	最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率是最高的.
    
    索引为分类:
    	单一索引
    	符合索引
    	主键索引
    	唯一索引
    
    索引什么时候失效?
    	模糊查询的时候,第一个通配符使用的是%,这个时候索引是失效的,还有其他情况.
    	select ename from emp where ename like '%A%' 	//索引不知道第一个字母是什么,无法定位分区,最终还是遍历查询
    
    视图(view):
    	什么是视图?
    		站在不同的角度去看数据(同一张表的数据,通过不同的角度去看待)
    
    	如何创建视图?怎么删除视图?
    		create view myview as select empno, ename from emp;
    		drop view myview;
    
    	注意只有DQL语句才能以视图对象的方式创建出来,
    	但是可以对视图进行CRUD操作.
    
    	对视图进行CRUD会影响到原表数据.(通过视图影响原表数据的,不是直接操作的原表)
    
    	为什么要使用视图?
    		像一些公司不希望在外包程序的时候,把数据库的具体信息公布出来,所以创建出视图,让外包公司对视图进行操作.
    		视图可以隐藏表的实现细节.保密级别较高的系统,可能只对外提供相关的视图,Java程序员只对视图对象进行CRUD,甚至连数据也不公布