MYSQL資料庫學習----索引和觸發器

標籤：

一：索引

索引是建立在資料庫表上，其作用是提高對錶中資料的查詢速度。

假設資料庫中有一張1000條記錄的表格，如果沒有建立索引的話，使用者想通過查詢條件查詢，實際上是把整個資料庫中1000條記錄都讀取一遍，滿足查詢條件的就加入結果集中，這樣效率很低，如果表中建立了針對查詢條件欄位的索引，查詢的時候會立即找到滿足條件的記錄加入結果集，不需要遍曆過程，這樣大大提高了資料庫查詢效率。

建立索引雖然提高了資料庫表格的查詢效率，但是也增加了資料庫維護速度，因為索引需要佔用物理空間，其次每次使用者插入，更改，刪除記錄的時候都要同步操作索引，這樣就造成資料維護速度降低，所以使用索引需要綜合考慮索引的優點和缺點。

使用索引的幾個建議

1.對經常作為查詢條件的欄位使用索引

2.如果需要用到索引盡量在整型欄位上面使用索引

3.盡量使用唯一性的欄位來建立索引

4.限制索引的數目，因為索引是佔用物理空間的，並且資料更新需要同步操作索引，所以在索引的數量上面需要綜合考慮

5.刪除不再使用或者很少使用的索引

6.使用者也可以在插入，更改，刪除記錄的時候先刪除掉索引，在操作資料庫，然後增加索引來使用

建立索引有幾種方法：

1.建立表格的時候建立索引

 

建立普通索引

INDEX(欄位名);

任意欄位都可以

 

建立唯一索引

UNIQUE INDEX 索引名稱(欄位名);

唯一性欄位可以，比如主鍵就是唯一欄位或者約束屬性是unique的欄位

 

建立全文索引

FULLTEXT INDEX 索引名稱(欄位名);

CHAR、VARCHAR、TEXT類型欄位上面可以

 

建立單列索引

INDEX 索引名稱(欄位名(限制長度));

對於字元型資料不同判斷全部字元，只需要判斷前面限制長度的字元就型了，目的還是提高查詢速度

 

建立多列索引

INDEX 索引名稱(欄位名1， 欄位名2...);

只有查詢條件中使用了第一個欄位名才會生效

比如建立多列索引：INDEX INDEX_TABLE(username, password, time);

這裡建立了三個欄位的索引，實際上MYSQL建立了三個索引分別是username,password。username,time。username,password,time。這樣就能很清楚看到如果查詢條件中使用WHERE PASSWORD="" AND TIME=""這種的話，是不會用到這個索引的，查詢條件中沒有索引第一個欄位USERNAME。

 

建立空間索引

這個很少用到，這裡就暫時不說。。

 

2.在已有的表格中建立索引 

CREATE [UNIQUE/FULLTEXT/SPATIAL] INDEX 索引名 ON 表名 (欄位名(限制長度));

 

3.用ALTER TABLE語句來建立索引 

ALTER TABLE 表名 ADD [UNIQUE/FULLTEXT/SPATIAL] INDEX 索引名 (欄位名(限制長度));

 

刪除索引：

DROP INDEX 索引名 ON 表名;

 

二：觸發器

觸發器，顧名思義就是通過某個特定的事件來觸發另外一個事件，我們把”通過某個特定的事件“稱為觸發條件，這個條件有INSERT,UPDATE,DELETE，最終觸發另外一個事件就是我們具體要資料庫操作的步驟了，試下下如果有兩張表，表A中記錄學生和對應年齡的資料，表B中記錄學生和體重的資料，現在有個學生退學了，那麼我們要把表A中這個學生的記錄刪除掉，表B中這個學生的記錄也要同時刪除，這樣就起到一個資料同步的作用，這裡用觸發器的話，就是當表A中該學生記錄被DELETE掉，觸發事件來DELETE掉表B中該學生的記錄，所以觸發器在實際運用中還是有存在的意義的。

注意：觸發條件和觸發事件不能作用於同一張表中！

建立一個一條執行語句的觸發器：

CREATE TRIGGER 觸發器名 [BEFORE/AFTER] [INSERT/UPDATE/DELETE] ON 表名 FOR EACH ROW 執行語句;

建立一個多條執行語句的觸發器：

CREATE TRIGGER 觸發器名 [BEFORE/AFTER] [INSERT/UPDATE/DELETE] ON 表名 FOR EACH ROW BEGIN 執行語句列表 END;

[BEFORE/AFTER]:    說明是在觸發之前就執行語句還是觸發之後執行。

[INSERT/UPDATE/DELETE]:    說明觸發條件是INSERT或UPDATE或DELETE。

執行語句列表:    以”;“號分開的執行語句，但是MYSQL預設是以”;“號來做結束的，所以可以用”DELIMITER &&“把資料庫結束符換成&&，等觸發器建立完後在改回”;“。

查看觸發器：

SHOW TRIGGERS;

 

刪除觸發器：

DROP TRIGGER 觸發器名;

MYSQL資料庫學習----索引和觸發器