【数据库-mysql】索引、视图与触发器

第七章 索引、视图与触发器

7.1 索引

7.1.1 索引概述

索引的简历是为了加快查询速度。暴力查询的平均时间复杂度为O(N)，一般使用树索引LOGn或者散列索引O(1)。索引之多一能加快查询速度，可以认为是对数据进行了重组。

有两种创建索引的方法：
1二叉树
2哈希：利用设计的散列函数将数据作为输入地址作为输出，查询的时候再次用散列函数拿到地址然后直接去改地址即可。
优点：
1加快了查询的速度
2 加快表之间的连接，在实现数据的参会完整性方面有意义
3 在order by group by字句中，减少分组排序的时间
缺点：降低了增删改的速度，索引文件有可能比数据文件还打。
情景：很多数据，15个字段，10个字段加了索引，现在需要换服务器，如何加快速度？
先把索引删除，然后导数据，最后重新加上索引。

7.1.2 索引分类

1、普通索引：仅仅是为了加快查询速度 KEY INDEX
2、唯一索引：行上的值不能重复UNIQUE
主键索引：主键必唯一，但是唯一索引不一定是主键，一张表上只能有一个主键，但是 可以有多个唯一索引。
3、全文索引 FULLTEXT
4、空间索引 SPATIAL
5、单列索引
6、多列索引

7.1.3 索引的设计原则

1索引并非越多越好，不过度索引
2 避免对经常更新的表建立索引
3 数据量小的时候不要建立索引
4过于集中的值不要添加索引，因为添加了也没有意义。例如100个用户，50个男50个女，为性别添加索引后，查询女，还是要面向50个用户。
5 对经常排序、分组和连接查询的字段创建索引。

7.1.4 创建索引

1 创建表的时候创建索引
2有两种方法在已经建过的表上创建索引
用 create index on
用alter table

ALTER TABLE 表名ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX [索引名](字段名);
ALTER TABLE 表名ADD PRIMARY KEY (字段名);  # 添加主键
在创建非普通索引的时候 index可以省略

3删除索引

ALTER TABLE 表名 DROP INDEX 索引名;
ALTER TABLE 表名 DROP PRIMARY KEY;

在查询语句前添加EXPLAIN可以查看是否使用了索引。

4全文索引与停用词

语法：mysql> select 列名1, match(全文索引)against("检索词") from 表名;
例子：mysql> select name, match(intro)against("Knight") from userinfo;
查看匹配度：mysql> select 列名1, match(全文索引)against("检索词") from 表名;

停用词是经常使用的次，如果用停用词作为被检索的词语，返回为空。

7.2视图

7.2.1 视图的基本概念

假如有一个查询结果集，需要频繁的使用，那么我们可以把这个查询结果存起来，即视图。视图是由查询结果组成的供其他查询使用的虚拟表。

7.2.2 视图的优点与使用场景

使用视图的三种背景：
1、筛选表中的记录，简化查询；
2、权限控制，视图开放部分列；
3、大数据分表时可以用到：假如一张表非常大，可以把数据分在4张表里面，例如按照求模分配：id % 4+1=[1, 2, 3, 4]，现在想get一个id=17的商品，17%4+1=2,那么直接去表2里面去查id=17的即可。第二种方法就是可以使用视图：将不同表的结果Union起来作为视图，构成大表用于查询。

7.2.3 使用视图

创建：CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名 [(字段名列表)]
AS SELECT 语句
[WITH [CASCADED|LOCAL] CHECK OPTION];

7.2.4 查看视图

SHOW TABLE STATUS LIKE "视图名";
DESC 视图名;
SHOW CREATE VIEW 视图名;

7.2.5 修改和删除视图

1已有则覆盖，没有则创建：

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名 [(字段名列表)]
AS SELECT 语句
[WITH [CASCADED|LOCAL] CHECK OPTION];

2修改

ALTER [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名 [(字段名列表)]
AS SELECT 语句
[WITH [CASCADED|LOCAL] CHECK OPTION];

3删除

DROP VIEW [IF EXISTS] 视图名;

7.2.6 更新视图

1视图可以像基表那样做增删改查操作
2 视图的更新针对通过merge算法创建的视图，temptable算法无法更改。
3 视图是表的查询结果，表改变视图也会变，另一方面视图成修改也会影响到表，但是视图的内容并不是总是能正删改的，当且仅当视图与表的数据一一对应才可以同步修改，并且当对视图insert时候，视图必须包含所有没有默认值的列。

增加： mysql> insert into 视图名 字段名 values 值
删除： mysql> delete from 视图名 where expression
修改： mysql> update 视图名 set expression where expression

7.2.7视图的Algorithm：

1 Merge分析出查询视图与创建视图的语句，叠加构成最终的语句，去查基础表。
2 TEMPTABLE根据创建语句创建出一张临时表，然后从临时表进行查询。
假如两条命令是向东和向西各10米，那Merge就是把两个语句整合，原地不动；TEMPTABLE是真的先执行第一条命令，再执行第二条命令。

例如：我们直接对基表操作如下：先根据生日排序，在根据成绩排序。

先对成绩排序生成一个视图prefer
mysql> create view prefer as
-> select * from user order by score;

mysql> select * from prefer
-> group by YEAR(birthday),score;

我们发现这个时候生日排序是乱的，为什么呢，因为迷人使用MERGE算法，相当于把两个查询语句合并然后查询，合并后的语句变成：

但是如果我们创建视图的时候用temptable算法，然后再查询，就会得到：

7.3 触发器

7.3.1 触发器概念

什么时候适合使用触发器呢？如果发生某个操作，必然导致另外一个操作。
监视某表的变化，当发生某种变化时，触发某个操作。
可以监视的操作：增删改，同样可以触发增删改。

触发器四要素：
1 监视地点：table
2 监视事件：insert|delete|update
3 监视时间：before|after
4 触发事件：insert|delete|update

修改分界符：delimiter

7.3.2创建触发器：

CREATE TRIGGER NAME AFTER|BEFORE INSERT|UPDATE|DELETE ON 表名
FOR EACH ROW  # 固定不变
BEGIN
sql语句;
END;

7.3.3 触发器的使用

1新增订单：对于insert而言，新增的行用new来表示，new.列名为字段
2 取消订单：对于delete而言，删除的行用old表示，old.列名为字段
3 修改订单：被修改的修改前的数据用old表示，old.列名为字段，修改后的用new表示
4 限制订单：对所下的订单做判断，订单数量大于5则修改成5

例子 商品(库存)表goods：

before vs. after
after：先完成增删改再触发
before：触发的语句先于被监视的增删改，有机会判断修改即将要发生的操作。
note：删除不用的触发器，否则可能会触发多个触发器。

1 购买商品时，库存减少：

delimiter %
CREATE TRIGGER afterbug AFTER INSERT ON order222
FOR EACH ROW 
BEGIN
update goods set num=num-new.counts where id = new.gid;
END%

2 对于delete而言，删除的行用old表示，old.列名为字段

CREATE TRIGGER afterdelete AFTER DELETE ON order222
FOR EACH ROW 
BEGIN
update goods set num=num+old.counts where id = old.gid;
END%

3 对于而言，被修改的修改前的数据用old表示，old.列名为字段，修改后的用new表示

CREATE TRIGGER afterupdate AFTER UPDATE ON order222
FOR EACH ROW 
BEGIN
update goods set num=num+old.counts-new.counts where id = new/*or old*/.gid;
END%

4 对所下的订单做判断，订单数量大于5则修改成5

CREATE TRIGGER beforebuy BEFORE INSERT ON order222
FOR EACH ROW
BEGIN
	IF new.counts  > 5
		THEN SET new.counts = 5;
	END IF;/*中间有空格*/
update goods set num=num-new.counts where id = new.gid;
END%

7.3.4 查看触发器

SHOW TRIGGERS [LIKE"%"]\G /*当前数据库中的触发器*/
SELECT * FROM information_schema.triggers\G /*所有数据库中所有触发器的信息*/

7.3.5 删除触发器

删除触发器：DEOP TRRIGER 触发器的名字;