mysql深入之视图和索引

末蓝、 2022-09-24 08:29 5阅读 0赞

**注：求职季，巩固下mysql知识！**

**1.视图**

视图又叫虚表。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
    
    视图主要有以下作用：
    1、安全，权限控制。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
     2、性能，快。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接（JOIN）。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
    
    3、灵活，抽取即将废弃表，产生有用价值。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。
    
    关于视图的学习，我建议你参考mysql手册。在网上找一些示例，很快就入门了。

**2.分类**

视图在SQL中可以分为三类

1 普通视图(Regular View)

sql模板

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
    [ WITH <view_attribute> [ ,...n ] ] 
    AS select_statement 
    [ WITH CHECK OPTION ] [ ; ]
    <view_attribute> ::= 
    {
        [ ENCRYPTION ]
        [ SCHEMABINDING ]
        [ VIEW_METADATA ]     }

解释：

参数还是比较少的，现在解释一下上面的参数：

**   ENCRYPTION：**视图是**加密的**，如果选上这个选项，则无法修改.创建视图的时候需**要将脚本保存，否则再也不能修改了**

**SCHEMABINDING：**和底层引用到的表进行定义绑定。这个选项选上的话，则视图所引用到的表不能随便更改构架(比如列的数据类型)，如果需要更改底层表构架，则先drop或者alter在底层表之上绑定的视图.

**VIEW\_METADATA：**这个是个很有意思的选项.正如这个选项的名称所指示，如果不选择，返回给客户端的metadata是View所引用表的metadata,如果选择了这个选项，则返回View的metadata.再通俗点解释，VIEW\_METADATA可以让视图看起来貌似表一样。View的每一个列的定义等直接告诉客户端，而不是所引用底层表列的定义。

WITH Check Option：这个选项用于更新数据做限制

限制条件

*    在View中，除非有TOP关键字，否则不能用Order By子句
 *     View在每个Schema中命名必须独一无二
 *     View不要嵌套尽量
 *     Compute,compute by,INTO关键字不允许出现在View中
 *     View不能建立在**临时表**上
 *     View不能对**全文索引进行查询**

实例

建立视图

CREATE VIEW v_Test
    AS
    SELECT TOP 10 * FROM table1

查询视图

SELECT * FROM v_Test

2索引视图(Indexed View)----会自动同步（好也坏）

索引视图可以看作是一个和表(Table)等效的对象！，是真实存在于物理数据中。

要求严格：（部分）

*  索引视图涉及的基本表必须ANSI\_NULLS设置为ON
 *  索引视图只能引用基本表

CREATE VIEW v_Test_Index
    WITH SCHEMABINDING
    AS
    SELECT Name,ID
    FROM CUSTOMER join NAME="PAUL"
    ADN ID>5
    GO
    --在视图上建立索引
    CREATE UNIQUE CLUSTERED INDEX index
    ON v_Test_Index

底层直接聚集索引扫描----通过hash匹配，索引扫描，性能好

但是**mysql没有**

**注：对索引的操作类似于CML可以使用ALTER,UPDATE,DELETE**

附录：

**mysql索引**

##     ##

### 1、什么是索引 ###

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据库表里所有记录的引用指针。

MySQL中索引的存储类型有两种：BTREE（树）和 HASH（哈希），具体和表的存储引擎有关。MyISAM和InnoDB存储引擎只支持**BTREE索引**。

### 2、索引的好处 ###

适当使用索引能提升数据库查询速度！

### 3、实例： ###

在创建表的时候创建索引

语法：

CREATE   TABLE  表名  [  列名称  数据类型 ]  
            [  UNIQUE  |  FULLTEXT  ]   [  INDEX   |   KEY    ]    [  索引名称  ]   ( 列名称  [  length  ]  )  [  ASC | DESC ]

说明：  UNIQUE  、 FULLTEXT  为可选参数，分别表示**唯一索引、全文索引**；INDEX 与 KEY为同义词，两者的作用相同，用来指定索引；

（1）、普通索引（index）： 普通索引是[MySQL][]的基本索引类型，允许在定义索引的列中插入重复值和空值

例：

CREATE  TABLE book
    (
    bookid   INT  NOT  NULL,
    bookname VARCHAR(100)  NOT  NULL,
    authors  VARCHAR(100)  NOT  NULL,
    info  VARCHAR(500) NULL,
    year_publication   YEAR  NOT  NULL,
    INDEX(year_publication)
    );

（2）、唯一索引(unique)：唯一索引列的值必须**唯一**，**但允许有空值**。*主键索引是一种特殊的唯一索引，不允许有空*值。

例：

CREATE TABLE  book
    (
      id  INT  NOT  NULL,
      name  CHAR(50)  NOT  NULL,
      UNIQUE  INDEX   UniqueIdx(id)
    );

(3)、联合索引：组合索引即是在**多个列上创建索引**。查询时，只有在查询条件中使用了这些字段（创建组合索引的时候指定的哪些列）的最左边字段时，索引才会被使用。

CREATE TABLE student
    (
      id  INT  NOT  NULL,
      name  CHAR(50)  NOT  NULL,
      age  INT  NOT  NULL,
      info  VARCHAR(200),
    
      INDEX   MultiIdx(id,name,age)
    );

(4)、全文索引：MySQL只有**MyISAM存储引擎**支持FULLTEXT索引，并且类类型为CHAR、TEXT、VARCHAR。 并且需要指定表的存储引擎为MyISAM。

例：

CREATE TABLE t4
    (
      id  INT  NOT  NULL,
      name  CHAR(50)  NOT  NULL,
      age  INT  NOT  NULL,
      info  VARCHAR(200),
      FULLTEXT INDEX   FullindexName(info)
    ) ENGINE = MyISAM ;

###  在已经存在的表上创建索引： ###

语法：

ALTER  TABLE  table_name  ADD   [  UNIQUE  |  FULLTEXT  ]   [  INDEX   |   KEY    ] 
    [  inex_name  ]  (col_name [ length ] ,...)  [ASC | DESC ]

(1)、普通索引：ALTER  TABLE  book  ADD  INDEX   indexName( bookname(30) );
    (2)、唯一索引：ALTER  TABLE  book  ADD UNIQUE INDEX  UniqueIdx( bookid );
    (3)、组合索引：ALTER  TABLE  book  ADD  INDEX  BkAndInfoIdx( authors(20), info(50) );
    (4)、全文索引：ALTER TABLE t6  ADD  FULLTEXT  INDEX infiIdx(info);（前提是这个表的存储引擎为 MyISAM）

### 使用 create  index  创建索引： ###

** 语法：**

CREATE    [  UNIQUE  |  FULLTEXT  ]   [  INDEX   |   KEY    ]  INDEX  index_name
    ON   table_name ( col_name[ length ] , ... )  [ASC | DESC ]
     例：在表book的bookname字段上建立名为BkNameIdx的索引。
    CREATE INDEX  BkNameIdx   ON   book (bookname);
      例： 在book表的bookId字段上建立唯一索引。
    CREATE   UNIQUE    INDEX  UniqueIdx    ON   book (bookId);

### 4、删除索引： ###

###  ###

（1）、ALTER   TABLE  table_name   DROP   INDEX  index_name;
    （2）、DROP    INDEX     index_name   ON  table_name ;

[MySQL]: http://lib.csdn.net/base/14