未加星标

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

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二04 | 时间 | 作者 红领巾 ] 0人收藏点击收藏

前言

本文主要给大家介绍了关于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: [email protected]
SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: gbk
COLLATION_CONNECTION: gbk_chinese_ci
1 row in set (0.03 sec)
Q&A:

MySQL视图可以用索引吗?

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

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

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

参考资料
唐汉明等著,《深入浅出MySQL》,人民邮电出版社,2014

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: SQLMySQL数据InnoDBOracle数据库TI删除CURY数据库
tags: 视图,索引,view,pay,gt,name,NULL,index,select,mysql,city,HASH,show
分页:12
转载请注明
本文标题:MySQL中索引与视图的用法与区别详解
本站链接:http://www.codesec.net/view/569342.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(72)