MySQL必备知识视图和索引

提高MySQL查询效率

查询语句也有查询的效率问题，比如说交叉连接与内外链接的区别，交叉连接需要产生一个笛卡尔积的临时表，这样效率比较低。或者在查询数据比较大的表时。那怎么提高查询效率呢？
1.选择有效的表名顺序， select语句一般优先处理写在from子句最后的表，将记录条数最少或者父表（被参考表）放在最后可提高查询效率。
2.select 尽量避免使用 *，如果不需要查询所有属性的话，尽量把需要查询的字段一一写出来，而不是用*代替。
3.整合简单的数据库查询语句，能一次性查询出来的属性就不要分为两次或者多次查询。
4.尽可能使用where替换having，能使用where解决的就不要使用分组查询的having子句，因为having是查询所有记录之后，再进行分组过滤，其中可能还需呀排序和统计等，比较繁琐。
5. 尽量多用内部函数，少用运算符的自己进行的操作。
6. 多使用表的别名，以减少解析时间。
7. 尽量使用exists代替in，not exists代替not in。
8. 尽量用>=代替>等。如：有时候>10和>=11所查询的效果是一样的，但是一个是先找10，一个是先找11. 多找了一个数。

视图

语句:view

什么是视图

视图可以用于代替查询，多次使用同一个查询语句时，可以为此查询创建一个视图，方便后续使用。
视图就相当于为一个查询结果集（虚拟表）创建一个快捷方式。
1.为什么要建立视图？建立视图有什么好处和优点？
由于有些查询语句又长又麻烦，并且经常使用，这时可以给他创建一个视图，以便于后续操作。
(1)用户只用关心数据，而不用关心表与表之间的连接操作，面向结果，而不用面向过程了。
(2)数据的安全性，视图可以单独设置权限，有些表中的某些数据是不方便给用户看的，视图能使用户只看到表中的部分数据，而没办法看到表中的全部数据和表的结构以及表与表之间的联系，相当于起到一个封装和保密的作用。(如用户查找他人账号信息，就只能看到一些基本信息，而不能看到账号密码等私密信息了)
(3)数据独立，一旦视图确定，原表中其他数据的修改很难影响到视图，除非修改或者删除了视图中所涉及的字段或属性值。

特点

1.简单
2.安全
3.数据独立

视图的使用

视图的创建
一般格式：create view 视图名 as 查询语句;

create view <视图名称> as (select * from <表名>);

可以查询表中全部创建视图，也可查询表中部分创建视图
视图的基本使用
select * from <视图名>;
视图与上面的查询语句结果一致
视图的修改
alter view 视图名 as 查询语句; # 将视图更改为其他查询
删除视图
drop view 视图名;

索引（index）

什么是索引？
所谓索引，可以将其理解成为书的目录一样，相当于一个快捷查询。
索引是一种高效获取数据的存储结构，例：hash、 二叉搜索树、 红黑树。
索引的作用
用于提高查询的效率，需要频繁的查询某些字段时建立，比普通的查询速度要快一些，除此之外索引并没有其他用处。
使用索引有什么好处
提高查询的速度

索引与存储引擎

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

索引的分类

普通索引
允许定义索引的字段重复和空值
唯一索引
允许定义索引的字段空值

create unique index index_name on 表格名(列名(length));

主键索引
不允许定义索引的字段重复和空值
定义主键的时候,会自动添加上去
** 全文索引**
用于查询文本中的关键字，只有MyISAM支持
只有char、varchar、text类型才可以定义
空间索引
对空间数据类型建立的索引

查询索引

1.显示索引

show index from <表名称>;

2.查询索引键值

show keys from <表名>

index是显示索引,key显示索引,和键值

建立索引

索引的优点

1.通过创建唯一索引，可以保证数据库每一行数据的唯一性
2.可以大大提高查询速度
3.可以加速表与表的连接
4.可以显著的减少查询中分组和排序的时间。
查询速度快

索引的缺点

1.创建索引和维护索引需要时间，而且数据量越大时间越长
2.创建索引需要占据磁盘的空间，如果有大量的索引，可能比数据文件更快达到最大文件尺寸
3.当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度
维护效率低
最适合创建索引的属性,常常是要通过where 进行筛选的列
常常用select 进行查找的数据

普通索引

作用: 加快查询速度
普通索引的建立

create index index_name on 表格名(列名(length));

修改索引

  alter table 表名 add index 索引名(字段1[,字段2,…,字段n]);

删除索引

  drop index 索引名 on 表名;

如果你有添加主键或外键等完整性约束的话，是会默认给你创建唯一索引的。

唯一索引

create unique index <索引名称> on <表格>(属性名,属性名);

作用: 加快查询速度，这些列可以唯一确定一条表记录
创建唯一约束时会自动建立唯一索引，所以查询主键(primary key)或者外键时速度会快一些，唯一索引无法手动创建。

主键索引

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

联合索引

多列组成的索引，查询效率提升高于多个单列索引合并的效率

全文索引

作用: 主要针对文本的内容进行分词，加快查询速度
论文查询,文书查询
建立全文索引

create fulltext index index_name 表格名(字段名(length));

添加全文索引

alter table 表名 add fulltext(字段1,字段2,…,字段n);

总结

1 查询的优化
2.视图的使用
3.索引的建立和索引的特点(增加索引后,查询速度会变快,但是增删改会变慢);