MySQL中索引与视图的用法与区别详解
前言
本文主要给大家介绍了关于MySQL中索引与视图的使用与区别的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧。
索引
一、概述
所有的Mysql列类型都可以被索引。
mysql支持BTREE索引、HASH索引、前缀索引、全文本索引(FULLTEXT)【只有MyISAM引擎支持,且仅限于char,varchar,text列】、空间列索引【只有MyISAM引擎支持,且索引的字段必须非空】,但不支持函数索引。
MyISAM和InnoDB存储引擎的表默认创建BTREE索引,
MEMORY存储引擎的表默认创建HASH索引。
二、创建索引
createindex语法为:
create[unique|fulltext|spatial]indexindex_name [usingindex_type] ontbl_name(index_col_name,...); index_col_name: col_name[(length)][asc/desc]
也可使用altertable增加索引,语法为:
ALTER[IGNORE]TABLEtbl_name alter_specification[,alter_specification]... alter_specification: ... ADDINDEX[index_name][index_type](index_col_name,...) ...
例如:为city表创建10个字节的前缀索引
mysql>createindexcityNameoncity(Name(10)); mysql>altertablecityaddindexcityName(Name(10));
三、查看索引
可以使用showindexfromtable;查看table表的当前所有索引。
四、删除索引
dropindexindex_nameontbl_name;
五、BTREE索引和HASH索引
MEMORY存储引擎的表可以选择使用BTREE索引和HASH索引
BTREE索引:
- 当使用>、<、=、>=、<=、between、!=、<>或者likexxx(xxx不以通配符开始)操作符时,都可以使用相关列上的BTREE索引。
HASH索引使用注意事项:(和HASH表的局限性有关)
- 只能用于使用=或<=>操作符之间的等式比较。
- 优化器不能使用HASH索引来加速orderby操作。
- mysql不能确定在两个值之前大约有多少行,会影响查询一定的效率。
- 只能使用整个关键字搜索一行。
六、设计索引的原则
搜索的索引列,不一定是要选择的列。最适合索引的列是出现在where子句中的列,而不是出现select后中的列。
使用唯一索引。选择容易数值容易区分的列进行索引。例如对生日的索引要比对性别的索引要好,因为生日的列具有不同的值,比较容易区分,而性别列只有M和F,此时索引用处不大,每次索引都得出大约一半的行。
使用短索引。对字符串的前缀索引中通常会指定一个前缀长度,如果在前10到20个字符内,多数值是唯一的,那么就可以不必对整个列进行索引,而是对前10到20个字符进行索引。这样能够节省索引空间,减少I/O时间,提高查询效率。
不要过度索引。每个额外索引都会占用额外的空间,降低写操作的性能,表修改时需要更新索引,甚至可能会重构,因此索引越多,花费的时间越长。另外MySQL在生成执行计划的时候会考虑到各个索引,多余的索引让查询优化的工作变得更加繁重。
视图
一、概述
mysql从5.0.1版本开始提供视图功能。
视图是一种虚拟表,在数据库中并不实际存在,行和列数据来自于自定义视图的查询中所使用的表,并且是在使用视图是动态生成的。
二、创建或修改视图
创建视图需要由createview权限,并且对查询涉及的表和列要有select权限。
如果使用createorreplace或者alter权限修改视图,还需要有该视图的drop权限。
创建视图语法为:
create[orreplace][algorithm={undefined|merge|temptable}] viewview_name[(column_list)] asselect_statement [with[cascade|local]checkoption]
修改视图语法为:
alter[algorithm={undefined|merge|temptable}] viewview_name[(column_list)] asselect_statement [with[cascade|local]checkoption]
mysql对视图的定义有一些限制,例如from关键字后不能包含子查询,这和其他数据库不同。
三、视图的可更新性
视图的可更新性和视图中查询的定义有关,以下类型的视图为不可更新的。
- 包含聚合函数(sum,min,max,count等)、distinct、groupby、having、union或者unionall。
- 常量视图。
- select中包含子查询。
- join。
- from一个不能更新的视图。
- where语句的子查询中引用了from语句中的表。
例如:以下的视图都是不可更新的
--包含聚合函数 mysql>createorreplaceviewpayment_sumas ->selectstaff_id,sum(amount) ->frompayment ->groupbystaff_id; --常量视图 mysql>createorreplaceviewpias ->select3.1415926aspi; --select中包含子查询 mysql>createviewcity_viewas ->select(selectcityfromcitywherecity_id=1);
with[cascaded|local]checkoption选项决定了是否允许更新数据使记录不再满足视图的条件,默认为cascaded。这个选项与Oracle数据库中的选项是类似的。
- local:只要满足本视图的条件就可以更新
- cascaded:必须满足所有针对该视图下的所有视图的条件才可以更新。
四、删除视图
可以一次删除一个或多个视图,但必须要有该视图的drop权限。
dropview[ifexists]view_name[,view_name]...[restrict|cascaded]
例如删除视图pay_view
mysql>dropviewpay_view1,pay_view2; QueryOK,0rowsaffected(0.00sec)
五、查看视图
MySQL从5.1版本开始,showtables命令不仅会显示表的名字,还会显示视图的名字,而不存在单独显示视图的showviews命令。
同样,可以也可以通过下面的命令查看:
showtablestatus[fromdb_name][like'pattern']
例子
mysql>showtablestatuslike'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 1rowinset(0.00sec)
如果要查看某个视图的定义,可以使用showcreateview查看。
例子
mysql>showcreateviewpay_view\G ***************************1.row*************************** View:pay_view CreateView:CREATEALGORITHM=UNDEFINEDDEFINER=`root`@`localhost`SQLSECURITYDEFINERVIEW`pay_view`ASselect`pay`.`pid`AS`pid`,`pay`.`amount`AS`amount`from`pay`where(`pay`.`amount`<10)WITHCASCADEDCHECKOPTION character_set_client:gbk collation_connection:gbk_chinese_ci 1rowinset(0.00sec)
最后,还可以通过查看系统表information_schema.views来查看视图的相关信息。
例子
mysql>select*frominformation_schema.viewswheretable_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 1rowinset(0.03sec)
Q&A:
MySQL视图可以用索引吗?
我想答案是肯定的,其索引是建立在视图后面的真实表上,而不是建立在视图上.
索引是存放在模式(schema)中的一个数据库对象,索引的作用就是提高对表的检索查询速度,索引是通过快速访问的方法来进行快速定位数据,从而减少了对磁盘的读写操作。索引是数据库的一个对象,它不能独立存在,必须对某个表对象进行依赖。
视图就是一个表或多个表的查询结果,它是一张虚拟的表,因为它并不能存储数据。
参考资料
唐汉明等著,《深入浅出MySQL》,人民邮电出版社,2014
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对毛票票的支持。