MySQL中關(guān)于索引與觸發(fā)器詳解

1》索引的含義和特點:

    索引是什么,索引相當(dāng)于字典里面的目錄序表,比如查詢一個“星”字,如果不按照拼音來找的話,那么我們需要把整個字典全部遍歷查詢一邊。才能查到這個字, ? ? ? ?如果按照拼音來找的,那么只需要在幾頁音序表中查詢。就可以通過音序就快速查到,這個字在字典的哪一頁。在數(shù)據(jù)庫中,索引是建立在表上面的,索引可以很大程
? ? ?度上提高數(shù)據(jù)庫的查詢,同時也提高了數(shù)據(jù)庫的性能,不同的存儲引擎定義了索引的最大長度和索引的數(shù)量,所有的存儲引擎對每個表最少支持16個索引,索引的長度 ? ? ?最少支持位256字節(jié);

? ? ? ?  索引優(yōu)點:
? ? ? ?    其優(yōu)點可以提高數(shù)據(jù)的檢索速度,針對于有依賴關(guān)系的子表和父表,在聯(lián)合查詢的時候可以提高查詢速度。
? ? ? ?  索引的缺點:
? ? ? ?    創(chuàng)建和維護索引需要消耗時間,索引需要占用物理空間,每一個索引都需要占用一定的物理空間,大量的索引會影響插入數(shù)據(jù),數(shù)據(jù)庫系統(tǒng)會按照索引進行排 ? ? ? ? ? ? ? ? ? 序,這樣降低了插入數(shù)據(jù)的速度;
? ? ? ?  解決辦法:在插入數(shù)據(jù)時,先臨時刪除表的索引,然后插入數(shù)據(jù),數(shù)據(jù)插入完成后,再創(chuàng)建索引。

2》索引的分類:
  mysql的索引類型有:普通索引,唯一性索引,全文索引,單列索引、多列索引和空間索引等;

? ? ?  1>普通索引
? ? ? ? ?    創(chuàng)建普通索引時,不附加任何限制條件,,這類索引可以創(chuàng)建在任何的數(shù)據(jù)類型上面,
? ? ?  2>唯一性索引
? ? ? ? ?    ?使用unique參數(shù)可以設(shè)置唯一索引,在創(chuàng)建唯一索引時,限制該索引的值必須是唯一的。比如在student表中,user_name 字段設(shè)置為唯一索引的話, ? ? ? ? ? ?那么此值必須是唯一的。
? ? ?  3>全文索引
? ? ? ? ?    ?使用fulltext參數(shù)可以設(shè)置為全文索引,全文索引只能創(chuàng)建char ?varchar或者Text類型的字段上。只有MyISAM的存儲引擎才支持此索引。Mysql5.6 ? ? ? ? ? ? ? ? ? ? ? innodb開始支持全文索引
? ? ?  4>單列索引
? ? ? ? ?    在表中的單個字段上創(chuàng)建索引,單列索引只根據(jù)該字段進行索引。單列索引可以是、普通索引、也可以是唯一索引,還可以是全文索引。只要保證該索 ? ? ? ? ? ? ? 引只對應(yīng)一個字段即可。

? ? ? ?5>多列索引
? ? ? ?     多列索引是在表的多個字段上創(chuàng)建一個索引,該索引指向創(chuàng)建時對應(yīng)的多個字段。可以通過這幾個字段進行查詢。但是使用了多列索引,只有查詢這些 ? ? ? ? 字段中的第一個字段時才會被使用索引。比如:在表中id、name和sex字段上建立一個多列索引,那么,只有查詢條件使用了id 字段時多列索引才會被使 ? ? ? ? ? 用;
?  ?6>空間索引
? ? ? ?    使用spatial參數(shù)可以設(shè)置為空間索引, 空間索引只能建立在空間數(shù)據(jù)類型上,目前只有使用MyISAM存儲引擎才支持空間索引。而且此索引的字段值不 ? ? ? ? ? ?能為空。
? ? ? ?  練習(xí):查詢一下是否有其它類型的索引,
? ? ? ?      Hash 索引主鍵索引 B-tree索引

3》如何設(shè)計索引:
? ?   為了讓索引使用效率更高,在創(chuàng)建索引時,必須考慮在那些字段上創(chuàng)建索引和創(chuàng)建什么類型的索引;
? ?   1>唯一索引的設(shè)置:
? ? ? ?     唯一索引的值是唯一的,可以更快速的通過該索引可以確定某條記錄;
? ? ? ?     比如:身份證號碼是唯一的,可以建立唯一索引,如果是名字的話,那么有可能出現(xiàn)同名的狀況,從而減低查詢速度。
? ?   2>為經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引:
? ? ? ? ?    經(jīng)常需要order by ? group by ?distinct和union等操作的字段,排序操作會浪費很多時間,如果為這些字段建立索引,可以有效地的避免排序操作;
? ?   3>為常作為查詢條件的字段建立索引:
? ? ? ? ?    如果某一個字段常用需要來查詢條件,那么該字段的查詢速度影響這個張表的速度,因此為這樣的字段建立索引,可以提高整張表的查詢速度;
? ?   4>限制索引的數(shù)目:
? ? ? ?     索引的數(shù)目不是越多越好,每個索引都需要占用磁盤空間。索引越多,需要的磁盤空間就越大,修改表時,所索引的重構(gòu)和更新麻煩,越多的索引更新 ? ? ? ? ?表就變得很浪費時間;
? ?   5>盡量使用數(shù)據(jù)量少的索引:
? ? ? ?     如果索引的值很長,那么查詢的速度會受到影響,比如對一個Char(100)類型的字段進行全文索引需要的時間肯定要比char(10)類型的字段需要的時間更 ? ? ? ? ?多;
? ?   6>刪除不再使用和很少使用的索引:
? ? ? ?     表中的數(shù)據(jù)被大量更新,或者數(shù)據(jù)的使用方式被改變后,原有的一些索引可能不在需要,DBA應(yīng)該定期的找出這些索引,將它們刪除,從而減少索引對 ? ? ? ? ? 更新操作的影響;

4》如何創(chuàng)建索引

? ? ?  語法:
? ? ?    [unique|fulltext|spatial] index |key
? ? ?    [別名] (屬性名1 ?[(長度)] ?[ASC|DESC] )
? ? ?    unique可選參數(shù),代表唯一索引
? ? ?    fulltext 可選參數(shù),代表全文索引
? ? ?    spatial 可選參數(shù),代表空間索引
? ? ?    index 和key 用來指定字段為索引兩者選擇其一。
? ?     別名可選參數(shù),給創(chuàng)建的索引取新的名稱。
? ?     長度可選參數(shù),給索引執(zhí)定長度,必須是字符類型的才可以指定長度。
? ?     ASC升序,DESC降序。

? ? ? ? ??? ? 1>創(chuàng)建普通索引
? ? ? ? ?     

 Mysql->create?table?aatest(  ???????????????????????          id?int,  ???????????????????????          name?varchar(20),  ???????????????????????          sexboolean,  ???????????????????????          index(id));

? ? ? ? ? ? ? ? ? ? ?       使用 index設(shè)置id為普通索引。
? ? ? ? ?     Mysql> show create table aatestG; ?查看一下表詳細結(jié)構(gòu)
? ? ? ? ?     ??Mysql>explain select * from aatest where id=1 G; 查看索引是否被使用。

? ? ? ? ? ? ? ?? 2>建立唯一索
? ? ? ? ?       ?唯一索引使用unique進行約束
? ? ? ? ?       

create?table?aatest2(  ?????????????????????????????             id?int?unique,  ?????????????????????????????            ???name?varchar(20),  ?????????????????????????????             unique?index?aatest_id(id?ASC));

     3>創(chuàng)建全文索引
? ? ? ? ? ? ?      

?create?table?aatest3(  ???????????????????????????????????????             id?int,  ???????????????????????????????????????             info?varchar(20),  ???????????????????????????????????????            ???fulltext?index?aatest3_info(info));

? ? ?        *******5.6版本已支持全文索引

    ?4>創(chuàng)建單列索引
? ? ? ? ? ? ?      

 create?table?aatest4(  ????????????????????????????????????????             id?int,  ????????????????????????????????????????            ??subject?varchar(30),  ????????????????????????????????????????            ?index?aatest4_st(subject(10)));subject(10)指定索引的長度

? ? ? ? ? ? ? ?5>創(chuàng)建多列索引
? ? ? ? ? ? ?      多列索引,是在表上多個字段創(chuàng)建一個索引。
? ? ? ? ? ? ?      

  create?table?aatest5(  ?????????????????????????????????????????              id?int,  ?????????????????????????????????????????             ???name?varchar(20),  ?????????????????????????????????????????              sex?char(4),  ?????????????????????????????????????????              index?aatest5_ns(name,sex));

5》在已經(jīng)有的表上建立索引:
? ? ?  語法:
? ? ?     create [unique | fulltext | spatial ] index 索引名
? ? ?     on ?表名 (屬性名 [(長度)] [ ASC | DESC]);

? ? ?     alter table 表名 ADD [unique | fulltext | spatial ] index 索引名
? ? ?     (屬性名 [(長度)] [ ASC | DESC]);

? ? ?     1>創(chuàng)建普通索引
? ? ? ? ?       

create?index?zytest_id??on?zytest(id);  ???????????      alter?table?zytest?add?index?zytest_id(id);

? ? ?     2>創(chuàng)建唯一索引? ? ? ? ?       

create?unique?index?zytest1_id?on?zytest1(id);  ???????????      alter?table?zytest1?add?unique?index?zytest1_id(id);

? ? ?     3>創(chuàng)建全文索引? ? ? ?     

 create?fulltext?index?zytest2_id?on?zytest2(info);  ???????????      alter?table?zytest2?add?fulltext?zytest_2(info);

? ? ? ? ? ? ??4>創(chuàng)建單列索引? ? ? ?       

create?index?zytest3_addr?on?zytest3(address(4));  ???????????      alter?table?zytest3?add?index?zytest3_addr(address(4));

? ? ?     5>創(chuàng)建多列索引
? ? ? ? ?       

create?index?zytest4_na?on?zytest4(name,address);  ???????????      alter?table?zytest4?add?index?zytest4_na(name,address);  6》如何刪除索引:

? ? ?  如果沒有別名,+索引名稱
? ? ?  語法:drop index 索引名 ON 表名
? ? ?      ??drop indexid on zytest;

? ? ?   如果有別名的話。直接+索引別名
? ? ?   語法:drop index 索引別名 ON 表名

================觸發(fā)器:

1》觸發(fā)器的含義與作用

? ? ? ?     觸發(fā)器(trigger)是由事件來觸發(fā)某個操作,主要是由insert update delete等事件來觸發(fā)某種特定的條件,滿足觸發(fā)器的觸發(fā)條件時,數(shù)據(jù)庫就會執(zhí)行觸 ? ? 發(fā)器定義的程序語句,比如:當(dāng)學(xué)生表當(dāng)中增加了一個學(xué)生記錄,學(xué)生的總數(shù)就必須同時改變。可以在這里創(chuàng)建一個觸發(fā)器,每次增加一個學(xué)生的記錄。
? ?就執(zhí)行一次計算學(xué)生的總數(shù)量的操作。這可以保證每次增加學(xué)生后的記錄統(tǒng)計一直保持最新;觸發(fā)器觸發(fā)的執(zhí)行語句可以只有一個。也可能有多個;

? ? ? ?? 語法:
? ? ? ?     create trigger 觸發(fā)器名稱 ?before|after 觸發(fā)事件
? ? ? ?     on 表名 for each row 執(zhí)行語句
? ? ? ?     berfore指觸發(fā)事件之前執(zhí)行的觸發(fā)語句。
? ? ? ?     After 表示在觸發(fā)事件之后執(zhí)行語句
? ? ? ?     觸發(fā)事件包括(insert update delete)等
? ? ? ?     on表名在XXX表之上
? ? ? ?     執(zhí)行語句指的是XXSQL語句和觸發(fā)事件類型要對應(yīng)

? ? ? ?   A ?觸發(fā)器 ?B存放A總記錄,
? ? ? ?   當(dāng)A表刪除一條數(shù)據(jù)之后—>觸發(fā)器將統(tǒng)計的最終結(jié)果寫入到B表當(dāng)中,用戶每次想要得到A表的結(jié)果,只需要去B表當(dāng)中查詢就行了。
? ? ? ? ?  select count(*) from A >B表當(dāng)中。

2》創(chuàng)建觸發(fā)器

? ? ? ?  1>創(chuàng)建一個表alvin
? ? ? ? ? ?     

create?table?alvin(  ?????????????        userid?int(10),  ?????????????        username?varchar(20),  ?????????????        old?int(4),  ?????????????        address?varchar(30));

? ? ? ?  2>創(chuàng)建一個表為trigger_time用來存放觸發(fā)后條件的結(jié)果
? ? ? ? ? ?      

create?table?trigger_time(  ?????????????          zhixing_time?time);  ?????????????     Query?OK,?0?rows?affected?(0.15?sec)

? ? ? ?  3>創(chuàng)建只有單個執(zhí)行語句的觸發(fā)器
? ? ? ? ? ?      

create?trigger?alvin1?before?insert  ?????????????          on?alvin?for?each?row  ?????????????      insert?into?trigger_time?values(now());  ?????????????    ??Query?OK,?0?rows?affected?(0.07?sec)

? ? ??? 4>創(chuàng)建有多個執(zhí)行語句的觸發(fā)器

? ? ? ?  舉例一、
? ? ? ?       root@zytest 10:49>delimiter &告訴MYSQL該命令段下面的內(nèi)容在提示結(jié)束后再執(zhí)行分析。默認是以分號(;)執(zhí)行
     

 root@zytest?10:53>create?trigger?alvin3?after?delete????            ->on?alvin?for?each?row????        ???   ->?begin????            ->insert?into?trigger_time?values('21:01:01');????            ->insert?into?trigger_time?values('22:01:01');????        ??   ->end    ????        ?   ->&&     Query?OK,?0?rows?affected?(0.05?sec)  ?????????????root@zytest?10:54>delimiter;#結(jié)束退出,注意分號要有空格   ?  ?????????????root@zytest?10:57>select?*?from?alvin;      ?????????????+--------+-------------+------+----------+      ?????????????|?userid?|?username????|?old??|?address??|      ?????????????+--------+-------------+------+----------+      ?????????????|????110?|?zengxiaohua?|???28?|?tianxing?|      ?????????????+--------+-------------+------+----------+      ?????????????1?row?in?set?(0.00?sec)  ?????????????root@zytest?11:07>delete?from?alvin?where?userid='110';#執(zhí)行刪除動作看看觸發(fā)器是否成功      ?????????????Query?OK,?1?row?affected?(0.05?sec)      ?????????????root@zytest?11:07>select?*?from?trigger_time;#:查看觸發(fā)器的執(zhí)行結(jié)果        ?????????????+--------------+        ?????????????|?zhixing_time?|        ?????????????+--------------+        ?????????????|?19:09:41?????|        ?????????????|?21:01:01?????|        ?????????????|?22:01:01?????|        ?????????????+--------------+      ?????????????3?rows?in?set?(0.00?sec)

? ?? 舉例二、
    alvin1表存放了學(xué)生的信息。每次增加(insert)一個學(xué)生的信息。就觸發(fā)一次統(tǒng)計。統(tǒng)計結(jié)果存入aac表里面;
    首先創(chuàng)建一個alvin1表結(jié)構(gòu)
      

create?table?alvin1(            user_id?int(10),            username?varchar(20),            old?tinyint(4),            address?varchar(30));        create?table?aac(            my_count?int);

    然后開始創(chuàng)建一個觸發(fā)器
      

delimiter&&        create?trigger?alvin123?before?insert?on        alvin1?for?each?row?begin        declare?ycount?int(10);#:申明變量類型        set?ycount=(select?count(*)?from?alvin1);#:給變量賦值        insert?into?aac(my_count)?values(ycount);#:調(diào)用變量        end&&        delimiter?;

??看看before和after的區(qū)別
    

create?trigger?alvin123?after?insert?on        zyalvin1?for?each?row        begin        declare?ycount?int(10);        set?ycount=(select?count(*)?from?zyalvin1);        insert?into?aac(my_count)values(ycount);        end&&      root@zytest?16:24>insert?into?alvin1?values('1001','zhangsan','18','China');開始測試      root@zytest?16:24>select?*?from?aac;查看觸發(fā)器統(tǒng)計的結(jié)果。

3》查看觸發(fā)器

? ?   1> 查看所有觸發(fā)器,提前要進入某庫
? ? ? ? ?    #: show triggers G;

? ?   2>在triggers表中查看觸發(fā)信息
? ? ? ? ?    root@zytest 11:20>use information_schema;
? ? ? ? ?    root@zytest 11:19>select * from information_schema.triggers G;
? ? ? ? ?  小技巧:所有觸發(fā)器的信息都存在information_schema庫中的triggers表里面,在使用select 查詢單個觸發(fā)器的時候。可以根據(jù)triggers表里面的字段名稱
? ? ? ? ?      Trigger_name字段進行查詢。
? ? ? ? ?    root@information_schema 11:24>select * from triggers where trigger_name=’alvin1’G;

? 4》刪除觸發(fā)器

  語法:
? ? ? ?  1>刪除alvin1觸發(fā)器
? ? ? ? ? ?    

 root@(none)?12:18>use?zytest;  ?????????????      Database?changed  ?????????????    root@zytest?12:18>drop?trigger?alvin1;  ?????????????    Query?OK,?0?rows?affected?(0.03?sec)

?

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點贊11 分享