MySQL中关于索引与触发器详解
- 黄舟原创
- 2017-09-30 11:04:052207浏览
1》索引的含义和特点：
索引是什么，索引相当于字典里面的目录序表，比如查询一个“星”字，如果不按照拼音来找的话，那么我们需要把整个字典全部遍历查询一边。才能查到这个字， 如果按照拼音来找的，那么只需要在几页音序表中查询。就可以通过音序就快速查到，这个字在字典的哪一页。在数据库中，索引是建立在表上面的，索引可以很大程
度上提高数据库的查询，同时也提高了数据库的性能，不同的存储引擎定义了索引的最大长度和索引的数量，所有的存储引擎对每个表最少支持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 aatestG; 查看一下表详细结构
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中关于索引与触发器详解的详细内容。更多信息请关注PHP中文网其他相关文章！