MySQL學習筆記（二）索引與視圖

01-30

索引

　　索引常用於快速找出某個列中有一特定值的列。如果不使用索引，MySQL必須從第一條記錄開始然後讀完整個表知道找出相關的行。表越大，花費的時間越多。（注意，如果需要訪問大部分行，則順序讀取要快得多，因為此時應該避免磁碟搜索）

設計索引的原則

　　最適合建立索引的列，是出現在where子句中的列，而非select關鍵字後面的列。

　　考慮某列中值的分布。索引的列的基數越大，索引的效果越好。比如，放置生日的列具有很多不同的值，就很容易區分各行，而用來記錄性別的列，只含有男女兩三個選項，在此設立索引就沒有什麼意義。

　　使用短索引。比如一個列是CHAR（200），但前面十幾二十多位可能基數就很大，那麼此時就僅對前20位建立索引。

　　不要過度索引。會佔用額外的磁碟空間，降低寫操作的性能，在修改表的同時，索引必須進行更新，有時候可能會重估。MySQL在生成一個執行計劃時，要考慮各個索引，這也要花費時間。

建立索引：

create [uniqefulltextspatial]index indexName n[USING index_type]non MyISAMTable(name);n

　　這裡還可以限定一些東西，比如列名name，如果寫name(2)[ASCDESC]就是只對前2位建立索引，按升序降序排列。

　　刪除　　

drop index indexName on MyISAMTable;n

　　針對不同表類型，主要分為BTREE和HASH索引

　　MyISAM和InnoDB默認創建的都是BTREE，MEMORY默認是使用HASH索引，但也支持BTREE。

HASH：

　　只用於使用 = 或者 <=> 操作符的等式比較。
　　優化器不能使用HASH索引來加速ORDER BY操作。
　　MySQL不能確定在兩個值之間大約有多少行。如果將一個MyISAM表改為HASH索引的MEMORY表，會影響一些查詢的執行效率。
　　只能使用整個關鍵字來搜索一行。

BTREE：則比較強大了，BETWEEN，！=，或者<>，或者LIKEpattern都可以使用相關列上的索引。

　　說實話這篇文章寫的不好，如果大家想深入了解索引，我推薦下面這篇，由美團編寫：

MySQL索引原理及慢查詢優化 -

視圖

　　視圖是一種虛擬存在的表，你可以通過這樣的語句來創建一個視圖：

CREATE [OR PLACE] [ALGORITHM ={UNDERFINEDMERGETEMPTABLE}]n VIEW viewName [(column_list)]n AS n selece語句n [WITH [CASCADEDLOCAL] CHECK OPTION ]n

　　比如一個簡單的例子，創建一個叫testView的視圖，來自MyISAMTable，里name以『ab』開頭的記錄：

mysql> nmysql> create view testView asn -> select * from myisamtable where name likeab%;nQuery OK, 0 rows affected (0.02 sec)n

mysql> show tables;n+--------------------+n| Tables_in_sampledb |n+--------------------+n| innodb_t |n| myisamtable |n| t_login_log |n| t_user |n| testview |n+--------------------+n5 rows in set (0.00 sec)nmysql> select * from testview;n+----+-------+n| i | name |n+----+-------+n| 1 | abcd |n| 2 | abcde |n| 5 | abcde |n| 8 | abcde |n| 11 | abcde |n| 14 | abcde |n| 17 | abcde |n| 20 | ab |n| 23 | ab |n+----+-------+n9 rows in set (0.00 sec)nmysql> n

　　可以看到，我之後show table的時候，這個視圖也會出現。視圖相對於普通表的優勢是：

簡單：使用視圖的用戶不需要關心後面對應的表的結構、關聯條件、篩選條件，這是一個過濾好的符合條件的結果集。

安全：使用一個視圖的用戶只能看到特定的行與列，這種許可權限制是表許可權管理無法做到的。

數據獨立：一旦視圖的結構確定，可以屏蔽表結構變化對用戶的影響，原表增加列對於視圖沒有影響。但當原表修改列名的時候，要修改視圖。原表增刪改數據，視圖也會自動更新。

　　 Mysql對於視圖的定義有一定的限制，比如，from關鍵字後不能跟子查詢。

　　同時，視圖的可更新性，跟視圖中查詢的定義有關係，以下類型的視圖就不可更新（不可對該視圖進行update操作）：

常量視圖
select包含子查詢
join
from一個不可更新的時候
where子句的子查詢引用了from字句中的表。

　　另外，在創建時，可以指定WITH LOCAL CHECK OPTION或者WITH CASCADED CHECK OPTION，前者的意思是，只要滿足本視圖的條件就可以更新，而後者要求滿足針對該視圖的，所有視圖的條件，才可以更新。默認是CASCADED

　　舉個例子，有一張表T，裡面放著2個班學生分數，從T中創建一個視圖V1，可以看到80分以上的同學（條件是>=80分）；再從V1中創建一個視圖V2，可以看到V1中，1班的同學的分數（條件是班級）。即，V1有2個班80分以上的同學，而V2有1班80分以上的同學（這個隱含的80分的限制來自於V1）。

　　假設V1是LOCAL的，V2是CASCADED的。

　　那麼，你不能把V1中某個同學的分數改到80分以下，這樣就會脫離這個視圖。對於V2，來說，你既不能改一個同學的班級，把他改為2班，也不能把他的成績改為80分以下，因為它是級聯的（CASCADED）

　　可以通過drop view viewName指令刪除視圖，show create view viewName G來查看視圖定義。

　　最後，關於許可權：

創建視圖需要有CREATE VIEW的許可權，並且對於查詢涉及的列具有SELECT許可權。
如果使用CREATE OR REPLACE 或者ALTER修改視圖，則還需要有該視圖的DROP許可權。
刪除視圖，也需要該視圖的DROP許可權。

　　希望對你有幫助。