MySQL中索引与视图的用法与区别详解

青旅半醒 2022-05-26 13:13 160阅读 0赞

**MySQL中索引与视图的用法与区别详解**

索引与视图是我们在日常使用mysql必不可少的一部分，最近在学习中看到一本书中关于这方法写的不错，所以这篇文章主要给大家介绍了关于MySQL中索引与视图的使用与区别的相关资料，需要的朋友可以参考借鉴，下面随着小编来一起学习学习吧。

**前言**

本文主要给大家介绍了关于MySQL中索引与视图的使用与区别的相关内容，分享出来供大家参考学习，下面话不多说了，来一起看看详细的介绍吧。

**索引**

**一、概述**

所有的Mysql列类型都可以被索引。

mysql支持BTREE索引、HASH索引、前缀索引、全文本索引(FULLTEXT)【只有MyISAM引擎支持，且仅限于char,varchar,text列】、空间列索引【只有MyISAM引擎支持，且索引的字段必须非空】，但不支持函数索引。

MyISAM和InnoDB存储引擎的表默认创建BTREE索引，

MEMORY存储引擎的表默认创建HASH索引。

**二、创建索引**

create index语法为：

**create**  \[ **unique** |fulltext|spatial\]  **index**  index\_name

\[using index\_type\]

**on**  tbl\_name(index\_col\_name, ...);

index\_col\_name:

col\_name \[(length)\] \[ **asc** / **desc** \]

也可使用 alter table 增加索引，语法为：

**ALTER**  \[ **IGNORE** \]  **TABLE**  tbl\_name

alter\_specification \[, alter\_specification\] ...

alter\_specification:

...

**ADD**  **INDEX**  \[index\_name\] \[index\_type\] (index\_col\_name,...)

...

例如：为city表创建10个字节的前缀索引

mysql> create index cityName on city(Name(10));

mysql> alter table city add index cityName(Name(10));

**三、查看索引**

可以使用 show index from table; 查看table表的当前所有索引。

**四、删除索引**

drop index index\_name on tbl\_name;

**五、BTREE索引和HASH索引**

MEMORY存储引擎的表可以选择使用BTREE索引和HASH索引

BTREE索引：

*  当使用>、<、=、>=、<=、between、!=、<>或者like xxx(xxx不以通配符开始)操作符时，都可以使用相关列上的BTREE索引。

HASH索引使用注意事项：(和HASH表的局限性有关)

*  只能用于使用=或<=>操作符之间的等式比较。
 *  优化器不能使用HASH索引来加速order by操作。
 *  mysql不能确定在两个值之前大约有多少行，会影响查询一定的效率。
 *  只能使用整个关键字搜索一行。

**六、设计索引的原则**

搜索的索引列，不一定是要选择的列。最适合索引的列是出现在where子句中的列，而不是出现select后中的列。

使用唯一索引。 选择容易数值容易区分的列进行索引。例如对生日的索引要比对性别的索引要好，因为生日的列具有不同的值，比较容易区分，而性别列只有M和F，此时索引用处不大，每次索引都得出大约一半的行。

使用短索引。 对字符串的前缀索引中通常会指定一个前缀长度，如果在前10到20个字符内，多数值是唯一的，那么就可以不必对整个列进行索引，而是对前10到20个字符进行索引。这样能够节省索引空间，减少I/O时间，提高查询效率。

不要过度索引。 每个额外索引都会占用额外的空间，降低写操作的性能，表修改时需要更新索引，甚至可能会重构，因此索引越多，花费的时间越长。另外MySQL在生成执行计划的时候会考虑到各个索引，多余的索引让查询优化的工作变得更加繁重。

**视图**

**一、概述**

mysql从5.0.1版本开始提供视图功能。

视图是一种虚拟表，在数据库中并不实际存在，行和列数据来自于自定义视图的查询中所使用的表，并且是在使用视图是动态生成的。

**二、创建或修改视图**

创建视图需要由 create view 权限，并且对查询涉及的表和列要有select权限。

如果使用 create or replace 或者alter权限修改视图，还需要有该视图的drop权限。

创建视图语法为：

**create**  \[ or  replace \]\[algorithm = \{undefined|merge|temptable\}\]

**view**  view\_name\[(column\_list)\]

**as**  select\_statement

\[ **with**  \[ **cascade** | **local** \]  **check**  **option** \]

修改视图语法为：

**alter**  \[algorithm = \{undefined|merge|temptable\}\]

**view**  view\_name\[(column\_list)\]

**as**  select\_statement

\[ **with**  \[ **cascade** | **local** \]  **check**  **option** \]

mysql对视图的定义有一些限制，例如from关键字后不能包含子查询，这和其他数据库不同。

**三、视图的可更新性**

视图的可更新性和视图中查询的定义有关，以下类型的视图为不可更新的。

*  包含聚合函数(sum,min,max,count等)、distinct、group by、having、union或者union all。
 *  常量视图。
 *  select中包含子查询。
 *  join。
 *  from一个不能更新的视图。
 *  where语句的子查询中引用了from语句中的表。

例如：以下的视图都是不可更新的

\-- 包含聚合函数

mysql >  **create**  or  replace  **view**  payment\_sum  **as**

\->  **select**  staff\_id, sum (amount)

\->  **from**  payment

\->  **group**  **by**  staff\_id;

\-- 常量视图

mysql >  **create**  or  replace  **view**  pi  **as**

\->  **select**  3.1415926  **as**  pi;

\-- select中包含子查询

mysql >  **create**  **view**  city\_view  **as**

\->  **select**  (  **select**  city  **from**  city  **where**  city\_id = 1);

with\[cascaded|local\] check option 选项决定了是否允许更新数据使记录不再满足视图的条件，默认为cascaded。这个选项与Oracle数据库中的选项是类似的。

*  local：只要满足本视图的条件就可以更新
 *  cascaded：必须满足所有针对该视图下的所有视图的条件才可以更新。

**四、删除视图**

可以一次删除一个或多个视图，但必须要有该视图的drop权限

drop view \[if exists\] view\_name \[,view\_name\] ... \[restrict|cascaded\]

例如删除视图pay\_view

mysql>  **drop**  **view**  pay\_view1,pay\_view2;

Query OK, 0  **rows**  affected (0.00 sec)

**五、查看视图**

MySQL从5.1版本开始，show tables命令不仅会显示表的名字，还会显示视图的名字，而不存在单独显示视图的show views命令。

同样，可以也可以通过下面的命令查看：

show table status \[from db\_name\] \[like 'pattern'\]

例子：

mysql> show  **table**  status  like  'pay\_view'  \\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

**Name** : pay\_view

Engine:  NULL

Version:  NULL

Row\_format:  NULL

**Rows** :  NULL

Avg\_row\_length:  NULL

Data\_length:  NULL

Max\_data\_length:  NULL

Index\_length:  NULL

Data\_free:  NULL

Auto\_increment:  NULL

Create\_time:  NULL

Update\_time:  NULL

Check\_time:  NULL

Collation:  NULL

Checksum:  NULL

Create\_options:  NULL

Comment:  **VIEW**

1 row  in  **set**  (0.00 sec)

如果要查看某个视图的定义，可以使用 show create view 查看。

例子

mysql> show create view pay\_view \\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

View: pay\_view

Create View: CREATE ALGORITHM=UNDEFINED DEFINER=\`root\`@\`localhost\` SQL SECURITY DEFINER VIEW \`pay\_view\` AS select \`pay\`.\`pid\` AS \`pid\`,\`pay\`.\`amount\` AS \`amount\` from \`pay\` where (\`pay\`.\`amount\` < 10) WITH CASCADED CHECK OPTION

character\_set\_client: gbk

collation\_connection: gbk\_chinese\_ci

1 row in set (0.00 sec)

最后，还可以通过查看系统表 information\_schema.views 来查看视图的相关信息。

mysql>  **select**  \*  **from**  information\_schema.views  **where**  table\_name =  'pay\_view'  \\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

TABLE\_CATALOG: def

TABLE\_SCHEMA: mysqldemo

TABLE\_NAME: pay\_view

VIEW\_DEFINITION:  **select**  \`mysqldemo\`.\`pay\`.\`pid\`  **AS**  \`pid\`,\`mysqldemo\`.\`pay\`.\`amount\`  **AS**  \`amount\`  **from**  \`mysqldemo\`.\`pay\`  **where**  (\`mysqldemo\`.\`pay\`.\`amount\` < 10)

CHECK\_OPTION: CASCADED

IS\_UPDATABLE: YES

DEFINER: root@localhost

SECURITY\_TYPE: DEFINER

CHARACTER\_SET\_CLIENT: gbk

COLLATION\_CONNECTION: gbk\_chinese\_ci

1 row  in  **set**  (0.03 sec)

**Q&A:**

**MySQL视图可以用索引吗?**

我想答案是肯定的,其索引是建立在视图后面的真实表上,而不是建立在视图上.

索引是存放在模式（schema）中的一个数据库对象，索引的作用就是提高对表的检索查询速度，索引是通过快速访问的方法来进行快速定位数据，从而减少了对磁盘的读写操作。索引是数据库的一个对象，它不能独立存在，必须对某个表对象进行依赖。

视图就是一个表或多个表的查询结果，它是一张虚拟的表，因为它并不能存储数据。