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)
?
