MySQL中對(duì)于索引與觸發(fā)器詳細(xì)說(shuō)明

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

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

2》索引的分類：
　　Mysql的索引類型有：普通索引，唯一性索引，全文索引，單列索引、多列索引和空間索引等；

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

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

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

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

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

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

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

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

2>建立唯一索
　　　　　　 唯一索引使用unique進(jìn)行約束
　　　　　　

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)指定索引的長(zhǎng)度

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

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

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

　　　　alter table 表名 ADD [unique fulltext spatial ] index 索引名
　　　　(屬性名 [(長(zhǎng)度)] [ 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》如何刪除索引：

　　如果沒(méi)有別名，+索引名稱
　　語(yǔ)法：drop index 索引名 ON 表名
　　　　 drop indexid on zytest;

　　如果有別名的話。直接+索引別名
　　語(yǔ)法：drop index 索引別名 ON 表名

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

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

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

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

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

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

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

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

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

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

　　3>創(chuàng)建只有單個(gè)執(zhí)行語(yǔ)句的觸發(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)建有多個(gè)執(zhí)行語(yǔ)句的觸發(fā)器

　　舉例一、
　　　　　　root@zytest 10:49>delimiter &&#告訴MYSQL該命令段下面的內(nèi)容在提示結(jié)束后再執(zhí)行分析。默認(rèn)是以分號(hào)(;)執(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é)束退出，注意分號(hào)要有空格　　　 
             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í)行刪除動(dòng)作看看觸發(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)一個(gè)學(xué)生的信息。就觸發(fā)一次統(tǒng)計(jì)。統(tǒng)計(jì)結(jié)果存入aac表里面；
　　　　首先創(chuàng)建一個(gè)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);

　　　　然后開(kāi)始創(chuàng)建一個(gè)觸發(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');開(kāi)始測(cè)試
　　　　root@zytest 16:24>select * from aac;查看觸發(fā)器統(tǒng)計(jì)的結(jié)果。

3》查看觸發(fā)器：

　　1> 查看所有觸發(fā)器，提前要進(jìn)入某庫(kù)
　　　　#: 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庫(kù)中的triggers表里面，在使用select 查詢單個(gè)觸發(fā)器的時(shí)候�？梢愿鶕�(jù)triggers表里面的字段名稱
　　　　　　Trigger_name字段進(jìn)行查詢。
　　　　root@information_schema 11:24>select * from triggers where trigger_name='alvin1'\G;

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

　　語(yǔ)法：
　　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)

以上就是MySQL中關(guān)于索引與觸發(fā)器詳解的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！