视图与索引

一、提高SQL查询效率

查询语句也有查询的效率问题，比如说交叉连接与内外连接接的区别，交叉连接需要产生一个笛卡尔积的临时表，这样效率比较低。

那如何提高查询语句的效率呢？

1、 选择有效的表名顺序，select语句一般优先处理写在from子句最后的表，将记录条数最少或者父表（被参考表）放在最后可提高查询效率。
2、select 尽量避免使用 * ，如果不需要查询所有属性的话，尽量把需要查询的字段一一写出来，而不是用*代替。比如说一共有10个字段，你需要查询其中的9个字段，把9个字段都写上更好，在满足需求的情况下，能少查一个字段就少查一个字段。

3、整合简单的数据库查询语句，能一次性查询出来的数据就不要分为两次或者多次查询。
4、尽可能使用where替换having，能使用where解决的就不要使用分组查询的having子句，因为having是查询所有记录之后，再进行分组过滤，其中可能还需要排序和统计等，比较繁琐。

5、 尽量多用内部函数，少用运算符自己进行的操作，因为内部函数的处理优化得比较好。
6、多使用表的别名，以减少解析时间。
7、尽量使用exists代替in，not exists代替not in。
8、 尽量用>=代替>等。如：有时候>5和>=6所查询的效果是一样的，但是一个是先找5，一个是先找6，多找了一个数。

二、视图

1、视图的使用

-- ① 视图关键字： view
	一般格式：create view <视图名> as <查询语句>;
	create view <视图名称> as <select <属性名> from <表名>>;
	-- 可以查询表中全部数据创建视图，也可查询表中部分数据创建视图
	 
    CREATE VIEW V_S AS  SELECT* FROM student;
	-- 视图可以用于代替一条查询语句，相当于给查询语句取个别名
	-- 也可以用于代替复杂查询语句
	
	
-- ② 视图的基本使用
	select * from <视图名>;  -- 视图与上面的查询语句结果一致
    -- 使用视图V_S
 	SELECT* FROM V_S;
    -- 查询视图的部分字段
 	 SELECT sno,sname FROM V_S;
    -- 视图的条件查询
  	SELECT sno,sname FROM V_S WHERE sno = '20021512';  
	
-- ③ 视图的修改
	alter view 视图名 as 查询语句; -- 将视图更改为其他查询
	
-- ④ 删除视图
	drop view 视图名;

2、什么是视图？视图有什么作用？

了解了视图的创建和视图的基本使用方法后，大概会有这么一个概念，就是视图可以用于代替查询，多次使用同一个查询语句时，可以为此查询创建一个视图，方便后续使用。
视图就相当于为一个查询结果集（虚拟表）创建一个快捷方式。

视图用于代替一个查询语句，一般用于代替常用的并且复杂的查询语句

3、为什么要建立视图？建立视图有什么好处和优点？

Ⅰ、由于有些查询语句又长又麻烦，并且经常使用，这时可以给他创建一个视图，以便于后续操作。
Ⅱ 、用户只用关心数据，而不关心数据是如何查询的，表与表之间的连接操作是怎样的，是面向结果，而不是面向过程。

Ⅲ 、数据的安全性，视图可以单独设置权限，有些表中的某些数据是不方便给用户看的，视图能使用户只看到表中的部分数据，而没办法看到表中的全部数据和表的结构以及表与表之间的联系，相当于起到一个封装和保密的作用。(如用户查找他人账号信息，就只能看到一些基本信息，而不能看到账号密码等私密信息了)
Ⅳ、数据独立，一旦视图确定，原表中其他数据的修改很难影响到视图，除非修改或者删除了视图中所涉及的字段或属性值。

总的来说，视图使查询变得更加简单、安全，数据独立性得到了提高。想加深对视图的理解可以参考操作系统下对文件夹创建快捷方式。

三、索引

1、什么是索引？

所谓索引，可以将其理解成为书的目录一样，相当于一个快捷查询。
索引是以一种高效获取数据的存储结构来存储数据，例如：hash、 二叉搜索树、 红黑树等。

2、索引是用来做什么的？

索引用于提高查询的效率，对需要频繁查询的某些字段建立索引，比普通字段的查询速度要快一些，除此之外索引并没有其他用处。

3、使用索引有什么好处？

建立索引能提高查询速率。

索引与存储引擎

在MySQL中，索引与存储引擎是相关的，也就是说索引是存储引擎级别的概念，索引的数据也是需要存储在硬盘中的，不同的存储引擎对索引的实现方式是不同的，也就是存储的数据结构不同。

① MyISAM (默认)和 InNODB 支持 BTREE (B树、二叉查找树)索引，数据是以二叉树的形式存储的。

② Memory和Heap 支持 BTREE 和 HASH (哈希表、数组)索引，数据是以哈希表的形式存储的(查询速度快)。

索引分类

①普通索引
对普通字段建立的索引，允许定义索引的字段重复和空值。
②唯一索引
对唯一字段建立的索引，允许定义索引的字段空值，但不允许重复。
③主键索引
对主属性建立的索引，不允许定义索引的字段重复和空值。
④全文索引
对于字符串或文本类型建立的索引，提高查询文本关键字的速率，
只有char、varchar、text等数据类型才可以建立全文索引，也只有MyISAM存储引擎支持。
⑤空间索引
对空间数据类型建立的索引，只有空间数据类型支持。

1、普通索引

-- ①普通索引的建立
create index 索引名 on 表名<字段1[,字段2,…,字段n]>;
CREATE INDEX ind_sname ON student(sname); -- 学生姓名经常被查询，可以为它创建一个索引
-- 在数据量较小的情况下，索引的速度提升不会很明显
-- 数据量越大，查询效率提升的效果越明显

-- ②以修改的方式创建索引
alter table 表名 add index 索引名(字段1[,字段2,…,字段n]);
alter table student add index ind_sage(sage);
-- ③删除索引
drop index 索引名 on 表名;
drop index ind_sage on student;
-- ④查看表中所有索引
show index from 表名; 或者 show keys from 表名;

-- ⑤ 联合索引
	-- 对多个属性同时添加一个索引
	--联合索引：多列的联合索引，查询效率高于多个单列索引合并的效率。
CREATE INDEX ind_snoname ON student(Sno,sname); -- 创建多列联合索引

CREATE INDEX ind_sname ON student(sname); -- 创建多个单列索引
CREATE INDEX ind_sno ON student(sno);


 -- 注意：对于建立联合索引的字段，所有字段同时查效率才会有提高，只查其中一部分效率是和普通查询差不多的。

2、唯一索引

-- ① 创建唯一索引的基本格式：
create unique index <索引名> on <表名(属性名)>;
create unique index unid_name on student(sname);
	-- 创建唯一约束时会自动建立唯一索引，所以查询主键、外键或者带有唯一约束的字段时速度会快一些。

-- ② 以修改的方式添加唯一索引
alter table <表名> add unique(<属性名>);
alter table student add unique(sname);

-- 唯一索引和唯一约束是同一个东西，只是从不同角度来看

-- 思考一下：没有建立索引的表存不存在索引呢？
--如果你有添加主键或外键等完整性约束的话，会默认创建唯一索引。

3、主键索引和外键索引

-- 主键索引和外键索引与主键约束，外键约束是同一个东西

-- ① 创建主键/外键索引
  -- 对表格添加、创建主键时，成为主键的字段会自动添加一个主键索引
  CREATE TABLE student(
  id INT PRIMARY KEY,
  names VARCHAR(20)
  );
  
-- ② 以修改的方式添加主键索引
ALTER TABLE <表名> ADD PRIMARY KEY (<列名>);
ALTER TABLE student ADD PRIMARY KEY(id);
  
-- 为什么外键也会建立主键索引呢？
-- 由于外键是关联其它表的主键，外键和主键是一种映射关系，所以它会有建立主键索引。

4、全文索引

 -- 全文索引主要针对文本的内容进行分词，加快查询速度，如资料查询、论文查询、文书查询等。
 -- 用于提高文本类型的查询效率，如：字符串模糊匹配
 
-- ① 创建全文索引的基本格式：
	create fulltext index <索引名> <表名(字段名(length))>;
	CREATE FULLTEXT INDEX full_tname ON teacher(tname);
	
	
-- ② 以修改表的方式添加全文索引：
	alter table 表名 add fulltext(字段1[,字段2,…,字段n]);
    ALTER TABLE class ADD FULLTEXT(cname);

5、空间索引

对空间数据类型（点、线、面和立体图形等）建立的索引，一般只有制作地图或者制作模型等相关领域有涉及到，这里就不做过多介绍了。

索引的优缺点

1、优点：使用索引可以大大提高查询的效率，查询的数据量越大，速度提升越明显。
思考：既然建立索引能提高查询速率，那可以对每个字段都建立吗？

不可以

2、缺点：定义了索引的字段与普通字段相比，索引需要占用额外的磁盘空间，并且由于存储的数据结构有所差异，对数据进行增加、删除、修改等操作时，建立了索引的字段就需要动态维护（这个维护不需要管理员手动维护，而是DBMS自动维护），所以增加了DBMS的工作负担；而普通字段直接对数据进行普通操作就行了。

动态维护就是要算出数据的位置在哪里，如：以哈希表形式存储的索引字段需要进行散列计算，算出数据的位置在哪，有可能还会存在哈希冲突。
所以建立索引会影响数据库的可维护性，降低了数据维护的速度。

索引的注意事项

从索引的优缺点看来：

① 建立索引提高了查询效率，但是不利于增加、修改和删除等操作。
② 对于数据量小和经常进行修改、删除等操作的表避免使用索引。
③ 对于需要频繁查询的字段应该创建索引。
④ 最适合创建索引的列是where子句中的列，而不是select里面的列，也就是建立索引要精准。
⑤ 根据数据查询的具体需求决定是否建立索引。