首页 > 代码库 > mysql 深入视图和索引

mysql 深入视图和索引

 

目录(?)[+]

 

注:求职季,巩固下MySQL知识!

1.视图

 

    视图又叫虚表。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。

视图主要有以下作用:
1、安全,权限控制。一些数据表有着重要的信息。有些字段是保密的,不能让用户直接看到。这时就可以创建一个视图,在这张视图中只保留一部分字段。这样,用户就可以查询自己需要的字段,不能查看保密的字段。

2、性能,快。关系数据库的数据常常会分表存储,使用外键建立这些表的之间关系。这时,数据库查询通常会用到连接(JOIN)。这样做不但麻烦,效率相对也比较低。如果建立一个视图,将相关的表和字段组合在一起,就可以避免使用JOIN查询数据。

3、灵活,抽取即将废弃表,产生有用价值。如果系统中有一张旧的表,这张表由于设计的问题,即将被废弃。然而,很多应用都是基于这张表,不易修改。这时就可以建立一张视图,视图中的数据直接映射到新建的表。这样,就可以少做很多改动,也达到了升级数据表的目的。

关于视图的学习,我建议你参考mysql手册。在网上找一些示例,很快就入门了。
2.分类

 

 

      视图在SQL中可以分为三类

      1 普通视图(Regular View)

sql模板

 

[sql] view plain copy
 
 技术分享技术分享
  1. CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]   
  2. WITH <view_attribute> [ ,...n ] ]   
  3. AS select_statement   
  4. WITH CHECK OPTION ] [ ; ]  
  5. <view_attribute> ::=   
  6. {  
  7.     [ ENCRYPTION ]  
  8.     [ SCHEMABINDING ]  
  9.     [ 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不能对全文索引进行查询
实例

 

建立视图

[sql] view plain copy
 
 技术分享技术分享
  1. CREATE VIEW v_Test  
  2. AS  
  3. SELECT TOP 10 * FROM table1  
查询视图

 

 

[sql] view plain copy
 
 技术分享技术分享
  1. SELECT * FROM v_Test  

 

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

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

 要求严格:(部分)

 

  • 索引视图涉及的基本表必须ANSI_NULLS设置为ON
  • 索引视图只能引用基本表

 

 

[sql] view plain copy
 
 技术分享技术分享
  1. CREATE VIEW v_Test_Index  
  2. WITH SCHEMABINDING  
  3. AS  
  4. SELECT Name,ID  
  5. FROM CUSTOMER join NAME="PAUL"  
  6. ADN ID>5  
  7. GO  
  8. --在视图上建立索引  
  9. CREATE UNIQUE CLUSTERED INDEX index  
  10. ON v_Test_Index  
底层直接聚集索引扫描----通过hash匹配,索引扫描,性能好

 

但是mysql没有

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


附录:

mysql索引

 

1、什么是索引

 

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

MySQL中索引的存储类型有两种:BTREE(树)和 HASH(哈希),具体和表的存储引擎有关。MyISAM和InnoDB存储引擎只支持BTREE索引

 

2、索引的好处

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

3、实例:

   在创建表的时候创建索引

    语法:
[sql] view plain copy
 
 技术分享技术分享
  1. CREATE   TABLE  表名  [  列名称  数据类型 ]    
  2.         [  UNIQUE  |  FULLTEXT  ]   [  INDEX   |   KEY    ]    [  索引名称  ]   ( 列名称  [  length  ]  )  [  ASC | DESC ]  
       说明:  UNIQUE  、 FULLTEXT  为可选参数,分别表示唯一索引、全文索引;INDEX 与 KEY为同义词,两者的作用相同,用来指定索引;

 

(1)、普通索引(index): 普通索引是MySQL的基本索引类型,允许在定义索引的列中插入重复值和空值

 

例:
[sql] view plain copy
 
 技术分享技术分享
  1. CREATE  TABLE book  
  2. (  
  3. bookid   INT  NOT  NULL,  
  4. bookname VARCHAR(100)  NOT  NULL,  
  5. authors  VARCHAR(100)  NOT  NULL,  
  6. info  VARCHAR(500) NULL,  
  7. year_publication   YEAR  NOT  NULL,  
  8. INDEX(year_publication)  
  9. );  

 

(2)、唯一索引(unique):唯一索引列的值必须唯一但允许有空值主键索引是一种特殊的唯一索引,不允许有空值。

例:

[sql] view plain copy
 
 技术分享技术分享
  1. CREATE TABLE  book  
  2. (  
  3.   id  INT  NOT  NULL,  
  4.   name  CHAR(50)  NOT  NULL,  
  5.   UNIQUE  INDEX   UniqueIdx(id)  
  6. );  

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

[sql] view plain copy
 
 技术分享技术分享
  1. CREATE TABLE student  
  2. (  
  3.   id  INT  NOT  NULL,  
  4.   name  CHAR(50)  NOT  NULL,  
  5.   age  INT  NOT  NULL,  
  6.   info  VARCHAR(200),  
  7.   
  8.   INDEX   MultiIdx(id,name,age)  
  9. );  

 

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

例:

 

[sql] view plain copy
 
 技术分享技术分享
  1. CREATE TABLE t4  
  2. (  
  3.   id  INT  NOT  NULL,  
  4.   name  CHAR(50)  NOT  NULL,  
  5.   age  INT  NOT  NULL,  
  6.   info  VARCHAR(200),  
  7.   FULLTEXT INDEX   FullindexName(info)  
  8. ) ENGINE = MyISAM ;  

 

 在已经存在的表上创建索引:

      语法:

[sql] view plain copy
 
 技术分享技术分享
  1. ALTER  TABLE  table_name  ADD   [  UNIQUE  |  FULLTEXT  ]   [  INDEX   |   KEY    ]   
  2. [  inex_name  ]  (col_name [ length ] ,...)  [ASC | DESC ]  


[sql] view plain copy
 
 技术分享技术分享
  1. (1)、普通索引:ALTER  TABLE  book  ADD  INDEX   indexName( bookname(30) );  
  2. (2)、唯一索引:ALTER  TABLE  book  ADD UNIQUE INDEX  UniqueIdx( bookid );  
  3. (3)、组合索引:ALTER  TABLE  book  ADD  INDEX  BkAndInfoIdx( authors(20), info(50) );  
  4. (4)、全文索引:ALTER TABLE t6  ADD  FULLTEXT  INDEX infiIdx(info);(前提是这个表的存储引擎为 MyISAM)  

使用 create  index  创建索引:

  语法:

[sql] view plain copy
 
 技术分享技术分享
  1. CREATE    [  UNIQUE  |  FULLTEXT  ]   [  INDEX   |   KEY    ]  INDEX  index_name  
  2. ON   table_name ( col_name[ length ] , ... )  [ASC | DESC ]  
  3.  例:在表book的bookname字段上建立名为BkNameIdx的索引。  
  4. CREATE INDEX  BkNameIdx   ON   book (bookname);  
  5.   例: 在book表的bookId字段上建立唯一索引。  
  6. CREATE   UNIQUE    INDEX  UniqueIdx    ON   book (bookId);  

4、删除索引:

[java] view plain copy
 
 技术分享技术分享
  1. 1)、ALTER   TABLE  table_name   DROP   INDEX  index_name;  
  2. 2)、DROP    INDEX     index_name   ON  table_name ;  
技术分享

 

mysql 深入视图和索引