MySQL索引视图

索引

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引，MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），他们包含着对数据表里所有记录的引用指针，更通俗的说，数据库索引好比是一本书的目录，能加快数据库的查询速度。（注意：一般数据库默认都会为主键生成索引）

什么是索引

模式（schema）中的一个数据库对象
在数据库中用来加速对表的查询
通过使用快速路径访问方法快速定位数据，减少了磁盘的I/O
与表独立存放，当不能独立存在，必须属于某个表
由数据库自动维护，表被删除时，该表上的索引自动被删除。
索引的作用类似于书的目录，几乎没有一本书没有目录，因此几乎没有一张表没有索引。

索引的原理
就是把无序的数据变成有序的查询
1、把创建的索引的列的内容进行排序
2、对排序结果生成倒排表
3、在倒排表内容上拼上数据地址链
4、在查询的时候，先拿到倒排表内容，再取出数据地址链，从来拿到具体数据

索引的优缺点

优点： 是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。

缺点： 是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。

索引的分类

索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

MySQL的索引包括主键索引,普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等。

索引的设计原则

为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引:

1、选择惟一性索引
2、为经常需要排序、分组和联合操作的字段建立索引
3、为常作为查询条件的字段建立索引
4、限制索引的数目
5、尽量使用数据量少的索引
6、尽量使用前缀来索引
7、删除不再使用或者很少使用的索引

创建索引

创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。

创建索引有三种方式，这三种方式分别是
1、创建表的时候创建索引
2、在已经存在的表上创建索引
3、使用ALTER TABLE语句来创建索引
创建表的时候可以直接创建索引，这种方式最简单、方便。
语法：

CREATE TABLE 表名 (
    属性名 数据类型 [完整性约束条件],
    属性名 数据类型 [完整性约束条件],
    …
   属性名 数据类型
   [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
   [别名](属性名1 [(长度)] [ASC | DESC])
);

普通索引

1、直接创建索引

CREATE INDEX index_name ON table(columns(length)

2、创建表的时候同时创建索引

Create table table_1(
    Id int,
    Name varchar(20),
    Sex boolean,
    index(id),
);

3、修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length)) USING BTREE;

4、查询某张表中索引情况

SHOW INDEX FROM table_name;

5、删除索引

DROP INDEX index_name ON table;

唯一性索引

Create table index2(
    Id int unique,
    Name varchar(20),
    Unique index index2_id(id asc)
);

全文索引

只能创建在char、varchar或text类型的字段上。

create table index3(
    Id int,
    Info varchar(20),
    Fulltext index index3_info(info)
);

多列索引

使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。
如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。
也就是说多个单列索引与单个多列索引的查询效果不同，因为执行查询时，
MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

Create table index5(
    Id int,
    Name varchar(20),
    Sex char(4),
    Index index5_ns(name,sex)
);

空间索引

建空间索引时，表的存储引擎必须是myisam类型，而且索引字段必须有非空约束。空间数据类型包括geometry,point,linestring和polygon类型等。平时很少用到。

Create table index6(
    Id int,
    Space geometry not null,
    Spatial index index6_sp(space)
)engine=myisam;

CREATE INDEX

在已经存在的表上，可以直接为表上的一个或几个字段创建索引。基本形式如下：help create index

CREATE [UNIQUE | FULLTEXT |SPATIAL] INDEX 索引名 ON 表名 (属性名 [ (长度) ] [  ASC | DESC] );

1、创建普通索引

CREATE INDEX index_name ON table(column(length))

2、创建唯一性索引

CREATE UNIQUE INDEX indexName ON table(column(length))

3、创建全文索引

CREATE FULLTEXT INDEX index_content ON article(content)

4、创建单列数据

CREATE INDEX index3_name on index3 (name(10));

5、创建多列索引

CREATE INDEX index3_name on index3 (name(10)，sex);

6、创建空间索引

CREATE SPATIAL INDEX index3_name on index3 (name(10));

ALTER TABLE

用ALTER TABLE语句来创建索引，也是存在表的情况下。
在已经存在的表上，可以通过ALTER TABLE语句直接为表上的一个或几个字段创建索引。基本形式如下：

ALTER  TABLE 表名  ADD  [ UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名（属性名 [ (长度) ] [ ASC | DESC]）;

1、创建普通索引

ALTER TABLE table_name ADD INDEX index_name (column(length))

2、创建唯一性索引

ALTER TABLE table_name ADD UNIQUE indexName (column(length))

3、创建全文索引

ALTER TABLE index3 add fulltext index index3_name(name);

4、创建单列数据

ALTER TABLE index3 add index index3_name(name(10));

5、创建多列索引

ALTER TABLE index3 add index index3_name(name(10),SEX);

6、创建空间索引

ALTER SPATIAL TABLE index3 add index index3_name(name(10));

删除索引

删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。
对于这样的索引，应该将其删除。本节将详细讲解删除索引的方法。
对应已经存在的索引，可以通过DROP语句来删除索引。基本形式如下：

DROP  INDEX 索引名  ON 表名 ;

视图

什么是视图？

视图通过以定制的方式显示来自一个或多个表的数据
视图是一种数据库对象，用户可以像查询普通表一样查询视图
视图内其实没有存储任何数据，它只是对表的一个查询
视图的定义保存在数据字典内，创建视图所基于对表称为“基表”

为什么需要视图？

例如经常要对某些表进行连接查询，每次都要做表的连接，写同样的一串语句,过于繁琐,由此我们可以通过视图的方式来解决此问题。

视图的作用和优点

作用：
控制安全
保存查询数据
优点：
提供了灵活一致级别安全性。
隐藏了数据的复杂性
简化了用户的SQL指令
通过重命名列，从另一个角度提供数据

视图的使用规则

1、视图必须有唯一命名
2、在mysql中视图的数量没有限制
3、创建视图必须从管理员那里获得必要的权限
4、视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图
5、在视图中可以使用OREDR BY，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的ORDER BY。
6、视图不能索引，也不能关联触发器或默认值
7、视图可以和表同时使用

创建视图

语法：

CREATE [OR REPLACE] VIEW 视图名 [(alias[, alias]...)]--为视图字段指定别名 AS SELECT.........

例如：查询1号班级的所有学生信息、学号、姓名、电话、邮箱、班级名
步骤1：创建对应的SQL语句

SELECT
student.StudentNo,student.StudentName,student.Phone,student.Email,grade.GradeName
FROM student INNER JOIN grade ON student.GradeId = grade.GradeId
WHERE student.GradeId = 1

步骤2：创建目标视图

CREATE VIEW StudentMessage (学号,姓名,电话,邮箱,班级名)
AS SELECT student.StudentNo,student.StudentName,student.Phone,student.Email,grade.GradeName
FROM student INNER JOIN grade ON student.GradeId = grade.GradeId
WHERE student.GradeId = 1;

或者

CREATE OR REPLACE VIEW StudentMessage AS SELECT student.StudentNo,student.StudentName
FROM student INNER JOIN grade ON student.GradeId = grade.GradeId
WHERE student.GradeId = 1;

删除视图

DROP VIEW 视图名;