MySQL笔记7

提高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 vs_name
as select s_no,s_name,s_sex from student;
# 可以查询表中全部创建视图，也可查询表中部分创建视图
2、视图的基本使用
例如：select * from vs_name;
#视图与上面的查询语句结果一致
3、什么是视图？视图有什么作用？
了解了视图的创建和视图的基本使用方法后，大概会有这么一个概念，就是视图可以用于代替查询，多次使用同一个查询语句时，可以为此查询创建一个视图，方便后续使用。
视图就相当于为一个查询结果集（虚拟表）创建一个快捷方式。
4、视图的修改
alter view 视图名 as 查询语句; # 将视图更改为其他查询
5、删除视图
drop view 视图名;
6、为什么要建立视图？建立视图有什么好处和优点？
①由于有些查询语句又长又麻烦，并且经常使用，这时可以给他创建一个视图，以便于后续操作。
②用户只用关心数据，而不用关心表与表之间的连接操作，面向结果，而不用面向过程了。
③数据的安全性，视图可以单独设置权限，有些表中的某些数据是不方便给用户看的，视图能使用户只看到表中的部分数据，而没办法看到表中的全部数据和表的结构以及表与表之间的联系，相当于起到一个封装和保密的作用。(如用户查找他人账号信息，就只能看到一些基本信息，而不能看到账号密码等私密信息了)
④数据独立，一旦视图确定，原表中其他数据的修改很难影响到视图，除非修改或者删除了视图中所涉及的字段或属性值。
# 想加深理解可以参考操作系统下对文件夹创建快捷方式

索引

改变字段列的数据结构，改成树，哈希表等查询块的数据结构
1、什么是索引？
所谓索引，可以将其理解成为书的目录一样，相当于一个快捷查询。

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

3、在mysql中，索引与存储引擎是相关的，也就是说索引是存储引擎级别的概念，索引的数据也是需要存储在硬盘中的，不同的存储引擎对索引的实现方式是不同的，也就是存储的数据结构不同。
①MyISAM(默认)和InNODB 支持BTREE(B树、二叉查找树)索引，数据是以二叉树的形式存储的。
②Memory和Heap 支持BTREE和HASH(哈希表、数组)索引，数据是以哈希表的形式存储的(查询速度快)。

4、索引的分类
①普通索引
允许定义索引的字段重复和空值
②唯一索引
允许定义索引的字段空值
③主键索引
不允许定义索引的字段重复和空值
④全文索引
用于查询文本中的关键字，只有MyISAM支持
只有char、varchar、text类型才可以定义
⑤空间索引
对空间数据类型建立的索引

5、普通索引
①普通索引的建立
create index 索引名 on 表名(字段1[,字段2,…,字段n]);
# 注意：对于建立索引的字段，所有字段同时查效率才会有提高，只查其中一部分效率是和普通查询差不多的。
②修改索引
alter table 表名 add index 索引名(字段1[,字段2,…,字段n]);
③删除索引
drop index 索引名 on 表名;
④查看表中所有索引
show index from 表名; 或者 show keys from 表名;
# 注意：没有建立索引的表存不存在索引呢？
如果你有添加主键或外键等完整性约束的话，是会默认给你创建唯一索引的。

6、唯一索引
创建唯一约束时会自动建立唯一索引（BTREE结构），所以查询主键或者外键时速度会快一些，唯一索引无法手动创建。

7、主键索引
创建主键和外键约束时会自动建立主键索引（BTREE结构），无法自己手动创建。
为什么外键也会建立主键索引呢？
由于外键是关联其它表的主键，外键和主键是一个映射关系，所以它会有建立主键索引。

8、全文索引
①建立全文索引
alter table 表名 add fulltext(字段1,字段2,…,字段n);
全文索引很少用到，这里就不过多介绍了。

9、索引的优缺点
①优点：
使用索引可以大大提高查询的效率。
既然建立索引能提高查询速率，那可以对每个字段都建立么？
②缺点：
由于定义了索引的字段与普通字段相比，存储的数据结构有所差异，对数据的增加、删除、修改等操作时，建立了索引的字段就需要动态维护(这个维护不需要管理员手动维护，是DBMS自动维护的)，而普通字段直接对数据进行普通操作就行了。
动态维护就是要算出数据的位置在哪里，如：以哈希表形式存储的索引字段需要进行散列计算，算出数据的位置在哪，有可能还会存在哈希冲突。
所以建立索引会降低数据维护的速度。
由索引的优缺点看来：
建立索引方便了查询，而增加、修改和删除操作不方便。
# 对于数据量小和经常进行修改、删除等操作的表避免使用索引。
# 对于需要频繁查询的字段应该创建索引。
# 最适合创建索引的列是where子句中的列，而不是select里面的列，也就是建立索引要精准。
# 根据你具体的需求觉得是否建立索引。

10、空间索引
有涉及到空间类型，一般只有制作地图或者制作模型的软件有涉及到，这里不做过多介绍了。