MySQL中關於索引與觸發器詳解

1》索引的意義與特點：

　 　　索引是什麼，索引相當於字典裡面的目錄序表，例如查詢一個「星」字，如果不按照拼音來找的​​話，那麼我們需要把整個字典全部遍歷查詢一邊。才能查到這個字，        如果依照拼音來找的​​，那麼只需要在幾頁音序表中查詢。就可以透過音序就快速查到，這個字在字典的哪一頁。在資料庫中，索引是建立在表上面的，索引可以很大程
     度上提高資料庫的查詢，同時也提高了資料庫的效能，不同的儲存引擎定義了索引的最大長度和索引的數量，所有的存儲引擎對每個表最少支持16個索引，索引的長度     最少支持位256字節；

       　　索引優點：
       　　　　其優點可以提高數據的檢索速度，針對於有依賴關係的子表和父表，在聯合查詢的時候可以提高查詢速度。
       　　索引的缺點：
       　　索引的缺點：
       　　　　建立和維護索引需要消耗時間，索引需要佔用實體空間，每個索引都需要佔用一定的實體空間，大量的索引會影響插入資料，系統會被佔用序，這樣降低了插入資料的速度；        　　解決方法：在插入資料時，先暫時刪除表格的索引，然後插入數據，資料插入完成後，再建立索引。

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

     　　1>普通索引
         　　　　創建普通索引時，不附加任何限制條件，這類索引 　　　  使用unique參數可以設定唯一索引，在建立唯一索引時，限制該索引的值必須是唯一的。例如在student表中，user_name 欄位設定為唯一索引的話，            則此值必須是唯一的。
     　　3>全文索引
         　　　  使用fulltext參數可以設定為全文索引，全文索引只能建立  varchar或Text類型的欄位上。只有MyISAM的儲存引擎才支援此索引。
Mysql5.6                       innodb開始支援全文索引      　　4>〠欄位索引
#      　　4>〠欄索引
##      　　4>根據該欄位進行索引。單列索引可以是、普通索引、也可以是唯一索引，也可以是全文索引。只要確保該索               引只對應一個欄位即可。

       5>多列索引
        　　　　多列索引是在資料表的多個欄位上建立索引，該索引指向創建時對應的多個欄位。可以透過這幾個字段進行查詢。但是使用了多列索引，只有在查詢這些         欄位中的第一個欄位時才會被使用索引。例如：在表格中id、name和sex欄位上建立多列索引，那麼，只有查詢條件使用了id 欄位時多列索引才會被使          用;
 　  6>空間索引 ##可以設定為空間索引, 空間索引只能建立在空間資料型別上，目前只有使用MyISAM儲存引擎才支援空間索引。且此索引的欄位值不            能為空。
       　　練習：查詢一下是否有其它類型的索引，
       　　　　　　Hash 索引主鍵索引 B-tree索引

3》如何設計索引：
    　　為了讓索引使用效率更高，在建立索引時，必須考慮在那些欄位上建立索引和建立哪種類型的索引；
    　　1>唯一索引的設定:
        　　　　唯一索引的值是唯一的，可以更快速的通過該索引可以確定某條記錄;##〠如果是名字的話，那麼有可能出現同名的狀況,從而減低查詢速度。
    　　2>為經常需要排序、分組和聯合操作的字段建立索引:
         　　　　經常需要order by   group by 固定操作字段，如果操作和設定為這些操作的時間字段，如果操作數可以有效的避免排序作業;
    　　3>為常作為查詢條件的欄位建立索引:
         　　　　如果某一個欄位常用需要來查詢條件，那麼該欄位的速度速度會影響這個張表的速度速度,為這樣的欄位建立索引，可以提高整張表的查詢速度;
    　　4>限制索引的數目:
        　　　　索引的數目不是越多，每個索引都需要佔用磁碟空間越好。索引越多，需要的磁碟空間越大，修改表時，所索引的重構和更新麻煩，越多的索引更新         表就變得很浪費時間;
    　　5>盡量使用數據量少的索引:
        　　　　如果索引的值很長，那麼查詢的速度會受到影響，例如對一個Char(100)類型的字段進行全文索引需要的時間肯定要比char(10)類型的字段需要的時間更     ;
    　　6>刪除不再使用和很少使用的索引:
        　　　　表中的資料被大量更新，或者資料的使用方式被改變後，原有的一些索引可能不在需要，DBA應該定期的找出這些索引，將它們刪除，從而減少索引對          更新作業的影響;

##4》 如何建立索引：

     　　語法：      　　　文法：
     　　　　[unique |fulltext|spatial] index |key
     　　　　[別名] (屬性名稱1  [(長度)]  [ASC|DESC] )
#      　　　　fulltext 選用參數，代表全文索引
     　　　　spatial 選用參數，代表空間索引
     來指定。
    　　　　別名選用參數，為已建立的索引取新的名稱。     　　　　長度可選參數，給索引執定長度，必須是字元類型的才可以指定長度。
    　　　　ASC升序，DESC降序。

#              1>建立一般指標#      # 　# 　　

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

                      　　　　　 使用設定為一般指標設定。          　　　　　Mysql> show create table aatest\G； 。

                》2> unique進行約束

          　　　　　　

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

]

      　　　　　　 *******5.6版本已支援全文索引

##

　　　　 4>创建单列索引
　　　　　　

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

5>创建多列索引
　　　　　　多列索引，是在表上多个字段创建一个索引。
　　　　　　

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

5》在已经有的表上建立索引：
　　语法：
　　　　create [unique | fulltext | spatial ] index 索引名
　　　　on 表名 (属性名 [(长度)] [ ASC | DESC]);

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

　　　　1>创建普通索引
　　　　　　

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

　　　　2>创建唯一索引 　　　　　　

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

　　　　3>创建全文索引 　　　　　

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

4>创建单列索引 　　　　　　

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

　　　　5>创建多列索引
　　　　　　

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 表名

================触发器：

1》触发器的含义与作用：

　　　　触发器(trigger)是由事件来触发某个操作，主要是由insert update delete等事件来触发某种特定的条件，满足触发器的触发条件时，数据库就会执行触 发器定义的程序语句，比如：当学生表当中增加了一个学生记录，学生的总数就必须同时改变。可以在这里创建一个触发器，每次增加一个学生的记录。
就执行一次计算学生的总数量的操作。这可以保证每次增加学生后的记录统计一直保持最新；触发器触发的执行语句可以只有一个。也可能有多个；

语法：
　　　　create trigger 触发器名称 before|after 触发事件
　　　　on 表名 for each row 执行语句
　　　　berfore指触发事件之前执行的触发语句。
　　　　After 表示在触发事件之后执行语句
　　　　触发事件包括(insert update delete)等
　　　　on表名在XXX表之上
　　　　执行语句指的是XXSQL语句和触发事件类型要对应

　　A 触发器 B存放A总记录,
　　当A表删除一条数据之后--->触发器将统计的最终结果写入到B表当中，用户每次想要得到A表的结果，只需要去B表当中查询就行了。
　　select count(*) from A >B表当中。

2》创建触发器

　　1>创建一个表alvin
　　　　

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

　　2>创建一个表为trigger_time用来存放触发后条件的结果
　　　　　

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

　　3>创建只有单个执行语句的触发器
　　　　　

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>创建有多个执行语句的触发器

　　举例一、
　　　　　　root@zytest 10:49>delimiter &&#告诉MYSQL该命令段下面的内容在提示结束后再执行分析。默认是以分号(;)执行
　　　　　

　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;#结束退出，注意分号要有空格　　　 
             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';#执行删除动作看看触发器是否成功　　　　
             Query OK, 1 row affected (0.05 sec)　　　　
             root@zytest 11:07>select * from trigger_time;#:查看触发器的执行结果　　　　　　
             +--------------+　　　　　　
             | zhixing_time |　　　　　　
             +--------------+　　　　　　
             | 19:09:41     |　　　　　　
             | 21:01:01     |　　　　　　
             | 22:01:01     |　　　　　　
             +--------------+　　　　
             3 rows in set (0.00 sec)

举例二、
　　　　alvin1表存放了学生的信息。每次增加(insert)一个学生的信息。就触发一次统计。统计结果存入aac表里面；
　　　　首先创建一个alvin1表结构
　　　　　　

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

　　　　然后开始创建一个触发器
　　　　　　

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);#:调用变量
　　　　　　end&&
　　　　　　delimiter ;

看看before和after的区别
　　　　

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;查看触发器统计的结果。

3》查看触发器：

　　1> 查看所有触发器，提前要进入某库
　　　　#: show triggers \G;

　　2>在triggers表中查看触发信息
　　　　root@zytest 11:20>use information_schema;
　　　　root@zytest 11:19>select * from information_schema.triggers \G;
　　小技巧：所有触发器的信息都存在information_schema库中的triggers表里面，在使用select 查询单个触发器的时候。可以根据triggers表里面的字段名称
　　　　　　Trigger_name字段进行查询。
　　　　root@information_schema 11:24>select * from triggers where trigger_name='alvin1'\G;

4》删除触发器：

　　语法：
　　1>删除alvin1触发器
　　　

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

 

以上是MySQL中關於索引與觸發器詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！