索引與視圖是我們?cè)谌粘Ｊ褂胢ysql必不可少的一部分，最近在學(xué)習(xí)中看到一本書(shū)中關(guān)于這方法寫(xiě)的不錯(cuò)，所以這篇文章主要給大家介紹了關(guān)于MySQL中索引與視圖的使用與區(qū)別的相關(guān)資料，需要的朋友可以參考借鑒，下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧。

前言

本文主要給大家介紹了關(guān)于MySQL中索引與視圖的使用與區(qū)別的相關(guān)內(nèi)容，分享出來(lái)供大家參考學(xué)習(xí)，下面話不多說(shuō)了，來(lái)一起看看詳細(xì)的介紹吧。

索引

一、概述

所有的Mysql列類(lèi)型都可以被索引。

mysql支持BTREE索引、HASH索引、前綴索引、全文本索引(FULLTEXT)【只有MyISAM引擎支持，且僅限于char,varchar,text列】、空間列索引【只有MyISAM引擎支持，且索引的字段必須非空】，但不支持函數(shù)索引。

MyISAM和InnoDB存儲(chǔ)引擎的表默認(rèn)創(chuàng)建BTREE索引，
MEMORY存儲(chǔ)引擎的表默認(rèn)創(chuàng)建HASH索引。

二、創(chuàng)建索引

create index語(yǔ)法為：

1 2 3 4 5 6

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增加索引，語(yǔ)法為：

1 2 3 4 5 6 7

ALTER [IGNORE] TABLE tbl_name ?alter_specification [, alter_specification] ... ? alter_specification: ?... ?ADD INDEX [index_name] [index_type] (index_col_name,...) ?...

例如：為city表創(chuàng)建10個(gè)字節(jié)的前綴索引

1 2	mysql> create index cityName on city(Name(10)); mysql> alter table city add index cityName(Name(10));

三、查看索引

可以使用show index from table;查看table表的當(dāng)前所有索引。

四、刪除索引

1	drop index index_name on tbl_name;

五、BTREE索引和HASH索引

MEMORY存儲(chǔ)引擎的表可以選擇使用BTREE索引和HASH索引

BTREE索引：

• 當(dāng)使用>、<、=、>=、<=、between、!=、<>或者like xxx(xxx不以通配符開(kāi)始)操作符時(shí)，都可以使用相關(guān)列上的BTREE索引。

HASH索引使用注意事項(xiàng)：(和HASH表的局限性有關(guān))

• 只能用于使用=或<=>操作符之間的等式比較。
• 優(yōu)化器不能使用HASH索引來(lái)加速order by操作。
• mysql不能確定在兩個(gè)值之前大約有多少行，會(huì)影響查詢(xún)一定的效率。
• 只能使用整個(gè)關(guān)鍵字搜索一行。

六、設(shè)計(jì)索引的原則

搜索的索引列，不一定是要選擇的列。最適合索引的列是出現(xiàn)在where子句中的列，而不是出現(xiàn)select后中的列。

使用唯一索引。選擇容易數(shù)值容易區(qū)分的列進(jìn)行索引。例如對(duì)生日的索引要比對(duì)性別的索引要好，因?yàn)樯盏牧芯哂胁煌闹?#xff0c;比較容易區(qū)分，而性別列只有M和F，此時(shí)索引用處不大，每次索引都得出大約一半的行。

使用短索引。對(duì)字符串的前綴索引中通常會(huì)指定一個(gè)前綴長(zhǎng)度，如果在前10到20個(gè)字符內(nèi)，多數(shù)值是唯一的，那么就可以不必對(duì)整個(gè)列進(jìn)行索引，而是對(duì)前10到20個(gè)字符進(jìn)行索引。這樣能夠節(jié)省索引空間，減少I(mǎi)/O時(shí)間，提高查詢(xún)效率。

不要過(guò)度索引。每個(gè)額外索引都會(huì)占用額外的空間，降低寫(xiě)操作的性能，表修改時(shí)需要更新索引，甚至可能會(huì)重構(gòu)，因此索引越多，花費(fèi)的時(shí)間越長(zhǎng)。另外MySQL在生成執(zhí)行計(jì)劃的時(shí)候會(huì)考慮到各個(gè)索引，多余的索引讓查詢(xún)優(yōu)化的工作變得更加繁重。

視圖

一、概述

mysql從5.0.1版本開(kāi)始提供視圖功能。

視圖是一種虛擬表，在數(shù)據(jù)庫(kù)中并不實(shí)際存在，行和列數(shù)據(jù)來(lái)自于自定義視圖的查詢(xún)中所使用的表，并且是在使用視圖是動(dòng)態(tài)生成的。

二、創(chuàng)建或修改視圖
創(chuàng)建視圖需要由create view權(quán)限，并且對(duì)查詢(xún)涉及的表和列要有select權(quán)限。

如果使用create or replace或者alter權(quán)限修改視圖，還需要有該視圖的drop權(quán)限。

創(chuàng)建視圖語(yǔ)法為：

1 2 3 4

create [or replace][algorithm = {undefined|merge|temptable}] ?view view_name[(column_list)] ?as select_statement ?[with [cascade|local] check option]

修改視圖語(yǔ)法為：

1 2 3 4

alter [algorithm = {undefined|merge|temptable}] ?view view_name[(column_list)] ?as select_statement ?[with [cascade|local] check option]

mysql對(duì)視圖的定義有一些限制，例如from關(guān)鍵字后不能包含子查詢(xún)，這和其他數(shù)據(jù)庫(kù)不同。

三、視圖的可更新性

視圖的可更新性和視圖中查詢(xún)的定義有關(guān)，以下類(lèi)型的視圖為不可更新的。

• 包含聚合函數(shù)(sum,min,max,count等)、distinct、group by、having、union或者union all。
• 常量視圖。
• select中包含子查詢(xún)。
• join。
• from一個(gè)不能更新的視圖。
• where語(yǔ)句的子查詢(xún)中引用了from語(yǔ)句中的表。

例如：以下的視圖都是不可更新的

1 2 3 4 5 6 7 8 9 10 11 12 13

-- 包含聚合函數(shù) 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中包含子查詢(xún) mysql > create view city_view as ?-> select ( select city from city where city_id = 1);

with[cascaded|local] check option選項(xiàng)決定了是否允許更新數(shù)據(jù)使記錄不再滿(mǎn)足視圖的條件，默認(rèn)為cascaded。這個(gè)選項(xiàng)與Oracle數(shù)據(jù)庫(kù)中的選項(xiàng)是類(lèi)似的。

• local：只要滿(mǎn)足本視圖的條件就可以更新
• cascaded：必須滿(mǎn)足所有針對(duì)該視圖下的所有視圖的條件才可以更新。

四、刪除視圖

可以一次刪除一個(gè)或多個(gè)視圖，但必須要有該視圖的drop權(quán)限。

1	drop view [if exists] view_name [,view_name] ... [restrict|cascaded]

例如刪除視圖pay_view

1 2	mysql> drop view pay_view1,pay_view2; Query OK, 0 rows affected (0.00 sec)

五、查看視圖

MySQL從5.1版本開(kāi)始，show tables命令不僅會(huì)顯示表的名字，還會(huì)顯示視圖的名字，而不存在單獨(dú)顯示視圖的show views命令。

同樣，可以也可以通過(guò)下面的命令查看：

1	show table status [from db_name] [like 'pattern']

例子

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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)

如果要查看某個(gè)視圖的定義，可以使用show create view查看。

例子

1 2 3 4 5 6 7

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)

最后，還可以通過(guò)查看系統(tǒng)表information_schema.views來(lái)查看視圖的相關(guān)信息。

例子

1 2 3 4 5 6 7 8 9 10 11 12 13

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視圖可以用索引嗎?

我想答案是肯定的,其索引是建立在視圖后面的真實(shí)表上,而不是建立在視圖上.

索引是存放在模式（schema）中的一個(gè)數(shù)據(jù)庫(kù)對(duì)象，索引的作用就是提高對(duì)表的檢索查詢(xún)速度，索引是通過(guò)快速訪問(wèn)的方法來(lái)進(jìn)行快速定位數(shù)據(jù)，從而減少了對(duì)磁盤(pán)的讀寫(xiě)操作。索引是數(shù)據(jù)庫(kù)的一個(gè)對(duì)象，它不能獨(dú)立存在，必須對(duì)某個(gè)表對(duì)象進(jìn)行依賴(lài)。

視圖就是一個(gè)表或多個(gè)表的查詢(xún)結(jié)果，它是一張?zhí)摂M的表，因?yàn)樗⒉荒艽鎯?chǔ)數(shù)據(jù)。

參考資料

唐漢明等著，《深入淺出MySQL》，人民郵電出版社，2014

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。

?

總結(jié)

以上是生活随笔為你收集整理的MySQL中索引与视图的用法与区别详解的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。