MySQL -- 视图和索引

视图和索引

一、提高sql查询效率

• 选择有效的表名顺序，select语句一般优先处理写在from子句最后面的表（从最后往前查询），所以将记录条数最少或者父表（被参考表）放在最后可以提高查询效率。
• select 尽量避免使用*。
• 整合简单的数据查询语句，能一次查询出来的就不要分两次或多次查询。
• 尽可能使用where代替having，能使用where解决的就不要使用分组查询的having子句，因为having是查询所有记录后，在进行分组过滤，其中还需要排序、统计等，比较繁琐。
• 尽量多使用内部函数，少用运算符自己进行操作，因为内部函数的处理优化得比较好。
• 多使用表的别名，以减少解析时间
• 尽量使用exists代替 in,not exists 代替 not in.
• 尽量使用>= 代替 > 等。 如：有时候 > 5 和 >= 6 所查询得效果是一样的，但一个是先找5，一个 是先找6， >5多找了一个数。

视图

1、视图的使用

• 视图关键字： view

  一般格式：create view<视图名> as <查询语句>;

  create view <视图名> as <selelct <属性名> from <表名>>;

• 视图的基本使用

  select * from <视图名>; 视图与查询语句结果一致。

  create database db_1;
  use db_1;
  create table teacher(
  t_id int primary key auto_increment,
    t_name varchar(10) not null,
    t_abe int ,
    t_sex varchar(4) default'男'
  );
  insert into teacher(t_id, t_name,t_age)
  values(null,'allter',18),(null,"banan",20);
  create view v_teacher as select t_id,t_name from teacher;
  select * from v_teacher;

• 视图得修改

  alter view <视图名> as <查询语句> # 将视图更改为其他查询语句。

• 删除视图

  drop view <视图名>;

• 视图的作用

  用视图名代替查询语句，方便较长查询语句的重复查询

  如：

  create view v_student as 
  select s_id ,s_name, c_name ,t_name from student s 
  inner join class as c on s.s_cid = c.c_id
  left join teacher as t on c.c_tid = t.t_id;
  

• 查询视图的部分内容（某几个字段）用给属性取的别名进行查询。

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

​ 视图就相当于为一个查询结果集（虚拟集）创建一个快捷方式 。

​ 视图可以用于代替查询，多次使用同一个查询语句时，可以为此查询创建一个视图，方便后续使用。

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

• 由于有些查询语句又长又麻烦，并且经常使用，这时就可以给他创建一个视图，以便后续操作。
• 用户只关心数据，而不关心数据是如何查询的，表与表之间的连接操作是怎样的用户并不关心，用户是面向结果的，而不是面向过程。
• 数据的安全性，视图可以单独设置权限，有些表中的某些数据是不方便给用户看的，视图能使用户只看到表中的部分数据，而没办法看到表中的全部数据和表的结构以及表与表之间的联系，相当于起到一个封装和保密的作用。（如： 用户查找到他人的账号信息，就只能看到一些基本信息，而不能看到账号的密码等私人信息。）
• 数据独立，一旦视图确定，原表中其他数据的修改很难影响到视图，除非修改或者删除视图中所涉及的字段或属性值。
• 视图不会占用额外的内存空间

索引

1、什么是索引

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

2、索引用来做什么？

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

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

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

索引与存储引擎

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

不同存储引擎的索引是不一样的

• 普通索引
• 唯一索引
• 主键索引
• 全文索引

1、 创建索引

create index ind_sname on student(s_name)

alter table student add index ind_stu(s_age);

数据量越大，数据索引的速度越明显。

2、删除索引

drop index <ind_sname> on

# 查看表中存在的索引

show index from<表名>;

show keys from<表名>;

3、 联合索引

对多个列（字段、属性）同时添加一个索引，

在查询语句中多列同时查询时，添加多列联合索引比多个单独索引效率要高。

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

create index <ind_lhsy> on <stludent(s_id,s_name ,s_age)>;

4、唯一索引

• 创建唯一索引的基本格式：

create unique index <ind_lhsy> on <stludent(s_id)>;

alter table student add unique(s_name);

5、主键索引 primary key

对表格创建主键时，成为主键的字段会自动添加一个主键索引

# 创建主键索引

create table student(

s_id int primary key;

)

alter table student add primary key(s_id);

6、全文索引，

主要针对文本的内容进行分词，加快查询速度，如：资料查询、论文查询、文书查询、

只能对文本类型（字符串、文本）添加的索引，

用于提高文本类型的查询效率，如：字符串的模糊匹配，查询关键字

• 创建全文索引的基本格式：

create fulltext index <索引名> on <表名（字段名)>；

• 添加全文索引

alter table <> add fulltext (字段[，字段1，~~，字段n]);

空间索引

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

索引的优缺点

• 优点

1、索引能够极大的提高查询效率。

• 缺点

1、索引需要占用额外的存储空间。

2、从数据结构的角度出发 索引虽然利于查询，但是对于插入、删除、修改操作而言，索引会成为一种负担，DMS会对建立索引的字段进行动态维护。

3、索引会降低数据库的数据维护速度，降低了数据库的可维护性。

以上是我在【六星教育】的学习笔记，仅供参考。如有任何错误，敬请指正。欢迎大家的评价与点评。谢谢！