索引是什么,索引相当于字典内里的目次序表,比方查询一个“星”字,假如不依据拼音来找的话,那末我们须要把悉数字典悉数遍历查询一边。才查到这个字, 假如依据拼音来找的,那末只须要在几页音序表中查询。就能够经由过程音序就疾速查到,这个字在字典的哪一页。在数据库中,索引是竖立在表上面的,索引能够很大程
度上进步数据库的查询,同时也进步了数据库的机能,差别的存储引擎定义了索引的最大长度和索引的数量,一切的存储引擎对每一个表起码支撑16个索引,索引的长度 起码支撑位256字节;
索引长处:
其长处能够进步数据的检索速率,针对于有依靠关联的子表和父表,在团结查询的时刻能够进步查询速率。
索引的瑕玷:
竖立和保护索引须要斲丧时候,索引须要占用物理空间,每一个索引都须要占用一定的物理空间,大批的索引会影响插进去数据,数据库系统会依据索引举行排 序,如许降低了插进去数据的速率;
解决办法:在插进去数据时,先暂时删除表的索引,然后插进去数据,数据插进去完成后,再竖立索引。
2》索引的分类:
Mysql的索引范例有:一般索引,唯一性索引,全文索引,单列索引、多列索引和空间索引等;
1>一般索引
竖立一般索引时,不附加任何限定前提,,这类索引能够竖立在任何的数据范例上面,
2>唯一性索引
运用unique参数能够设置唯一索引,在竖立唯一索引时,限定该索引的值必需是唯一的。比方在student表中,user_name 字段设置为唯一索引的话, 那末此值必需是唯一的。
3>全文索引
运用fulltext参数能够设置为全文索引,全文索引只能竖立char varchar或许Text范例的字段上。只需MyISAM的存储引擎才支撑此索引。Mysql5.6 innodb最先支撑全文索引
4>单列索引
在表中的单个字段上竖立索引,单列索引只依据该字段举行索引。单列索引能够是、一般索引、也能够是唯一索引,还能够是全文索引。只需保证该索 引只对应一个字段即可。
5>多列索引
多列索引是在表的多个字段上竖立一个索引,该索引指向竖立时对应的多个字段。能够经由过程这几个字段举行查询。然则运用了多列索引,只需查询这些 字段中的第一个字段时才会被运用索引。比方:在表中id、name和sex字段上竖立一个多列索引,那末,只需查询前提运用了id 字段时多列索引才会被使 用;
6>空间索引
运用spatial参数能够设置为空间索引, 空间索引只能竖立在空间数据范例上,现在只需运用MyISAM存储引擎才支撑空间索引。而且此索引的字段值不 能为空。
演习:查询一下是不是有别的范例的索引,
Hash 索引主键索引 B-tree索引
3》怎样设想索引:
为了让索引运用效力更高,在竖立索引时,必需斟酌在那些字段上竖立索引和竖立什么范例的索引;
1>唯一索引的设置:
唯一索引的值是唯一的,能够更疾速的经由过程该索引能够一定某条纪录;
比方:身份证号码是唯一的,能够竖立唯一索引,假如是名字的话,那末有能够涌现同名的状态,从而减低查询速率。
2>为常常须要排序、分组和团结操纵的字段竖立索引:
常常须要order by group by distinct和union等操纵的字段,排序操纵会糟蹋许多时候,假如为这些字段竖立索引,能够有效地的防止排序操纵;
3>为常作为查询前提的字段竖立索引:
假如某一个字段经常使用须要来查询前提,那末该字段的查询速率影响这个张表的速率,因此为如许的字段竖立索引,能够进步整张表的查询速率;
4>限定索引的数量:
索引的数量不是越多越好,每一个索引都须要占用磁盘空间。索引越多,须要的磁盘空间就越大,修正表时,所索引的重构和更新贫苦,越多的索引更新 表就变得很糟蹋时候;
5>只管运用数据量少的索引:
假如索引的值很长,那末查询的速率会受到影响,比方对一个Char(100)范例的字段举行全文索引须要的时候一定要比char(10)范例的字段须要的时候更 多;
6>删除不再运用和很少运用的索引:
表中的数据被大批更新,或许数据的运用体式格局被转变后,原有的一些索引能够不在须要,DBA应当按期的找出这些索引,将它们删除,从而削减索引对 更新操纵的影响;
4》怎样竖立索引:
语法:
[unique|fulltext|spatial] index |key
[别号] (属性名1 [(长度)] [ASC|DESC] )
unique可选参数,代表唯一索引
fulltext 可选参数,代表全文索引
spatial 可选参数,代表空间索引
index 和key 用来指定字段为索引二者挑选其一。
别号可选参数,给竖立的索引取新的称号。
长度可选参数,给索引执定长度,必需是字符范例的才能够指定长度。
ASC升序,DESC降序。
1>竖立一般索引
Mysql->create table aatest( id int, name varchar(20), sexboolean, index(id));
运用 index设置id为一般索引。
Mysql> show create table aatest\G; 检察一下表细致构造
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>竖立全文索引
create table aatest3( id int, info varchar(20), fulltext index aatest3_info(info));
*******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中关于索引与触发器详解的细致内容,更多请关注ki4网别的相干文章!