华山资源网

MySQL中索引与视图的用法与区别详解
数据库 2024/12/25 佚名
3 1

前言

本文主要给大家介绍了关于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视图可以用索引吗"external nofollow" target="_blank" href="https://book.douban.com/subject/25817684/">唐汉明等著，《深入浅出MySQL》，人民邮电出版社，2014

总结

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

华山资源网 Design By www.eoogi.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
华山资源网 Design By www.eoogi.com
www.eoogi.com 华山资源网
120,135影音资源
344,641技术资源
22,817软件资源
435,032站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
华山资源网 Design By www.eoogi.com