mysql資料庫索引和視圖，觸發器

索引簡介  跟存儲引擎有很大關系其實就是一種排序，生成一種算法，索引主要用在大資料量的時候使用，資料小根本沒必要

索引在mysql中也叫做鍵，是存儲引擎用于快速找到記錄的一種資料結構，索引對于良好的性能非常關鍵，尤其是當表中的資料量越來越大的時候，索引對于性能的影響越發重要。

索引優化應該是最查詢性能優化的最有效的手段了，索引能夠輕易将查詢性能提高好幾個數量級。

索引相當于字典的音序表，如果要查某個字，如果不使用音序表，則要從幾百頁中逐頁去查。

優點

加快通路速度

加強行的唯一性

缺點

帶索引的表在資料庫中需要更多的存儲空間

操縱資料的指令需要更長的處理時間，因為它們需要對索引進行更新

按照下列标準選擇建立索引的列

頻繁搜尋的列

經常用作查詢選擇的列

經常排序、分組的列

經常用作聯接的列（主鍵/外鍵）

請不要使用下面的列建立索引

僅包含幾個不同值的列

表中僅包含幾行

查詢時減少使用*傳回全部列，不要傳回不需要的列

索引應該盡量小，在位元組數小的列上建立索引

WHERE子句中有多個條件表達式時，包含索引列的表達式應置于其他條件表達式之前

避免在ORDER BY子句中使用表達式

根據業務資料發生頻率，定期重新生成或重新組織索引，進行碎片整理

普通索引

唯一索引 ：主鍵本身就是一鐘特殊的唯一索引

全文索引

單列索引

多列索引

空間索引

準備實驗環境

批量插入記錄：mysql>delimiter $$  //定義分隔符

Mysql>Create procedure autoinsert1()   //建立存儲過程

Begin

Declare i int default 1;

While (1<200000)do

Insert into school.t2 values(i,’ccc’); //這個表必須是事先存在的，

Set i=i+1;

End while;

End$$

Mysql>delimiter;//把分隔符還原回來

Mysql>Call autoinsert1();  //調用存儲過程

建立索引(建立表時)注意事項：一開始就有索引會有個如果你批量導入資料的時候，因為某個字段上存在索引，是以速度會非常慢。

文法：create table 表名(

字段一 資料類型 [完整性限制條件.....],

[unique|fulltext|spatial] index |key [索引名] (字段名[長度] [asc |desc])

);

建立普通索引建立表時執行個體：

Create table department(

Dept_id int primary key,

Dept_name varchar(30),

Commnet varchar(50),

Index index_dept_name (dept_name)

檢視索引：desc 表名或者show create table表名 \G

建立唯一索引建立表時執行個體：

Unique Index index_dept_name (dept_name)

建立全文索引建立表時執行個體：

fulltext Index index_dept_name (dept_name)

)engin=myisam;

建立多列索引建立表時執行個體：

Index index_dept_name (dept_name,commnet)

模拟查詢分析查詢文法

Explain select * from dept 4 where commnet=’sale’ \G

複制表的内容和結構  将t2表的所有資料複制到t4表上

Create table t4 select * from t2;

隻複制表的結構  将t2表的結構複制到t4表上

Create table t4 select * from t2 where 1=2;

或者create table t5 like t2;

文法：create [unique|fullext|spatial] index 索引名 on 表名(字段名[(長度)][asc|desc]);

建立普通索引執行個體：

Create index index_dept_name on departmnet (dept_name);

建立唯一索引執行個體：

Create unique index index_dept_name on departmnet (dept_name);

建立全文索引執行個體：

Create fulltext index index_dept_name on departmnet (dept_name);

建立多列索引執行個體：

Create index index_dept_name on departmnet (dept_name,comment);

文法：alter table 表名 add [unique|fullext|spatial] index 索引名 (字段名[(長度)][asc|desc]);

Alter table department add  index index_dept_name (dept_name);

Alter table department add  unique index index_dept_name (dept_name);

Alter table department add  fulltext index index_dept_name (dept_name);

Alter table department add index index_dept_name (dept_name,comment);

檢視索引

Show create table 表名\G

測試示例

EXPLAIN select * from department where dept_name=’hr’;

删除索引

Drop index 索引名 on 表名;

Mysql視圖是一張虛拟表，其内容由查詢定義，同真實的表一樣，視圖包括一系列帶有名稱的列和行資料，但是視圖并不在資料庫中以存儲的資料值集形式存在，行和列資料來自由定義視圖的查詢所引用的表，并且在引用視圖的動态生成，對其中所引用的基礎表來說，mysql視圖的作用類似于篩選。定義視圖的篩選可以來自目前或其他資料庫的一個或多個表，或者其他視圖，通過視圖進行查詢沒有任何限制，通過它們進行資料修改時的限制也很少。

視圖是存儲在資料庫中的sql查詢語句，它主要出于兩種原因，安全原因，視圖可以隐藏一些資料，如一些敏感的資訊，另一原因是可以使複雜的查詢便于了解和使用

文法一

Create [algorithm={undefined |merge|temptable}]

View 視圖名[(字段1,字段2.....)]  as select語句

[with [caseaded|local] check option];

文法二

Create view 視圖名 as select 語句;

執行個體：

Create database view; //建立一個資料庫，建議建立

Use view

Create view view_user

As select user,host,password from mysql.user;

Select * from view_user;

Show tables 檢視視圖名

Use view;

Show tables;

Show tables status

執行個體：檢視view資料庫中視圖以及所有表詳細資訊

Show table status from view\G

執行個體：檢視view資料庫中視圖名view_user詳細資訊

Show table status from view like “view_user” \G

檢視視圖定義資訊

Show create view view_user \G

檢視視圖結構

Desc view_user;

方法一删除後建立

Drop view view_user;

Create view view_user as select user,host from mysqk.user;

Select * from view_user;

方法二：替換原有視圖

文法：create or replace view 視圖名 as select 語句;

執行個體：use view;

Create or replace view view_user as select user,password from mysql.user;

方法三：alter 修改視圖

文法：alter view 視圖名 as select 語句;

Alter view view_user as select user,password from mysql.user;

查詢資料 select    通常都是查詢操作

更新資料update

删除資料delect

Drop view 視圖名

觸發器(triggers)是一個特殊的存儲過程，它的執行不是由程式調用，也不是手工啟動，而是由時間來觸發，比如當對一個表進行操作(insert,delect,update)時就會激活它執行,觸發器經常用于加強資料的完整限制和業務規則等

例如：當學生表中增加一個學生的資訊時，學生的總數就應該同時改變，是以可以針對學生表建立一個觸發器，每次增加一個學生記錄時，就執行一次學生總數的計算操作，進而保障學生總數和記錄數一緻性。

文法：create trigger 觸發器名稱 before|after 觸發事件

On 表名 for each row

 觸發器程式體;

End

觸發器名稱：//最多64字元

for each row //觸發器的執行間隔,這句話的意思是指的是針對每一行，如果不加就是針對整張表

Insert|update|delete//觸發的事件

On表名//在哪張表上建立觸發器

觸發器程式體：//觸發器所觸發的sql語句，語句可以使用順序，判斷，循環等語句，實作一般程式需要的邏輯功能。

建立表

Create table student(

Id int unsigned auto_incremnet primary key not null,

Name varchar(50)

unsigned //無符号修飾符

往表裡插入資料

Insert into student values(1,’jack’);

建立表2總數表

Create table student_total (total int);

往數表中插入資料

Insert into student_total values(1);

建立觸發器執行個體一student_insert_trigger

Mysql>Delimiter && //定義結束符

Mysql>Create trigger student_insert_trigger after insert 

On student for each now

Upadte student_total set total=total+1;

End&&

Mysql>Delimiter ;

建立觸發器執行個體二

Mysql>Create trigger student_insert_trigger after delect

Upadte student_total set total=total-1;

測試效果，往學生表中插入資料，檢視總數表結果，

Insert into student values(2,’Alice’);

Select * from student_total ;

方法1通過show triggers語句檢視

案例：show triggers\G

方法2通過系統表triggers檢視

案例：use information_schema

Select * from triggers\G

SELECT * FROM TRIGGERS WHERE TGIGGER_NAME=’觸發器名稱’\G

Drop trigger 觸發器名稱

執行個體：Drop trigger student_insert_trigger;

案例一：增加tab1表記錄後自動将記錄增加到tab2中,能同步的字段的資料類型肯定要必須一緻才行。

建立tab1

Drop table if exists tab1;

Create table tab1(

Tab1_id varchar(11)

建立tab2

Drop table if exists tab2;

Create table tab2(

Tab2_id varchar(11)

觸發器：after_tab1_trigger

作用：增加tab1表記錄後自動将記錄增加到tab2中

Mysql>drop trigger if exists tab1_after_trigger;

Mysql>Create trigger tab1_after_trigger after insert

On tab1 for each now

Insert into tab2(tab2_id) values (new tab1_id);  new關鍵字指的是tab1插入以後的新增值，在删除的時候就應該是用old關鍵字

案例三：

當我們更新學生表學生資訊的時候，我們學生更新表也對應的改變。

Drop table if exists student;

student_id int auto_incremnet primary key not null,

Student varchar(30) not null,

Student_sex enum(‘m’,’f’) default ‘m’

插入資料：

Insert into student values

(1,’jack’,’m’),

(2,’robin’,’m’),

(3,’alice’,’f’);

Drop table if exists update_student;

Create table update_student(

Update_record int auto_incremnet primary key not null,

Student_id int not null,

Update_date date

Insert into update_student values

(1,1,now()),

(2,2,now()),

(3,3,now());

建立觸發器ustudent_trigger

Mysql>drop trigger if exists student_update_trigger;

Mysql>Create trigger student_update_trigger before update

On studnet for each now

If new.student_id!=old.student_id then

Update update_student

Set student_id=new.student_id

Where student_id=old.student_id;

End if;

改後的值叫new值，改之前叫做old值，這句話表示學生id被修改後，就會把學生id新值也會指派給update_student表

删除同步操作案例

Mysql>drop trigger if exists student_delete_trigger;

Mysql>Create trigger student_delete_trigger before delete

Delete from update_student

本文轉自    探花無情   51CTO部落格，原文連結:http://blog.51cto.com/983865387/1917419