MySQL中索引、视图、触发器详解

最新推荐文章于 2022-11-03 15:50:35 发布

Black1499

最新推荐文章于 2022-11-03 15:50:35 发布

阅读量712

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/Black1499/article/details/82776818

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

一、索引

1.概述

所有 MySQL 列类型都可以被索引，对相关列使用索引是提高 SELECT 操作性能的最佳途
径。根据存储引擎可以定义每个表的最大索引数和最大索引长度，每种存储引擎（如 MyISAM、
InnoDB、BDB、MEMORY 等）对每个表至少支持 16 个索引，总索引长度至少为 256 字节。
大多数存储引擎有更高的限制。
一个表的索引，就好比一本书的目录。有了目录，查询速度自然会大大加快。

2.语法

创建：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
	[USING index_type]
	ON tbl_name (index_col_name,...)

index_col_name:
	col_name [(length)] [ASC | DESC]

删除：

DROP INDEX index_name ON tbl_name

3. 案例实现

创建：

create index index_book on book_info(book_name);

删除：

drop index index_book on book_info;

4：使用原则

搜索的索引列，不一定是所要选择的列。 换句话说，最适合索引的列是出现在 WHERE
子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。
使用惟一索引。 索引的列的基数越大，索引的效果越好。例如，存放出生日期的列具有不同值，很容易区分各行。
使用短索引。 如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应
该这样做。这样做比对整个列进行索引的速度更快。
利用最左前缀。 在创建一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。
多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为
最左前缀。
不要过度索引。 每个索引都会占用你的磁盘空间，每个表跟新的时候也需要更新索引。索引太多时，mysql可能会选择不到适合的索引，只保留有用的索引，才可优化查询速度。

二、视图

1.概述

视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并
不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时
动态生成的。简单来说，视图就是呈现给用户数据的一个虚拟表，只能查看，不会对原表产生任何影响。

2.不可更新的视图

MySQL 视图的定义有一些限制，例如，在 FROM 关键字后面不能包含子查询，这和其他数据库是不同的，如果视图是从其他数据库迁移过来的，那么可能需要因此做一些改动，可以将子查询的内容先定义成一个视图，然后对该视图再创建视图就可以实现类似的功能了。视图的可更新性和视图中查询的定义有关系，以下类型的视图是不可更新的。

包含以下关键字的 SQL 语句：聚合函数（SUM、MIN、MAX、COUNT 等）、DISTINCT、GROUP
BY、HAVING、UNION 或者 UNION ALL。
常量视图。
SELECT 中包含子查询。
JION。
FROM 一个不能更新的视图。
WHERE 字句的子查询引用了 FROM 字句中的表。

3.语法

创建:

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
	VIEW view_name [(column_list)]
	AS select_statement
	[WITH [CASCADED | LOCAL] CHECK OPTION]

修改:

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
	VIEW view_name [(column_list)]
	AS select_statement
	[WITH [CASCADED | LOCAL] CHECK OPTION]

删除:

DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]

4.案例

创建:

create view book_view as
	select * from book_info b 
	inner join press_info p
	on b.press_id = p.press_id

删除:

drop view book_view

三、触发器

1.概述

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

2.语法

创建：

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

删除：

DROP TRIGGER [schema_name.]trigger_name

查看触发器：

-- 第一种，系统自带
show triggers \G;
-- 第二种，查看方式是查询系统表的 information_schema.triggers 表
desc triggers;

注意： 触发器只能创建在永久表（Permanent Table）上，不能对临时表（Temporary Table）创建触发器。

其中 triggertime 是触发器的触发时间，可以是 BEFORE 或者 AFTER，BEFORE 的含义指在检
查约束前触发，而 AFTER 是在检查约束后触发。
而 triggerevent 就是触发器的触发事件，可以是 INSERT、UPDATE 或者 DELETE。

3.案例

-- 每更新一次press_info表的数据，都向t_log表插入一条操作数据
create trigger tri_updinfo
after update on press_info for each row
	insert into t_log (notes) values ('update press_info');
end;

4.触发器的使用

触发器执行的语句有以下两个限制。

触发程序不能调用将数据返回客户端的存储程序，也不能使用采用 CALL 语句的动态 SQL
语句，但是允许存储程序通过参数将数据返回触发程序。也就是存储过程或者函数通过 OUT
或者 INOUT 类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。
不能在触发器中使用以显式或隐式方式开始或结束事务的语句，如 START TRANSACTION、
COMMIT 或 ROLLBACK。

MySQL 的触发器是按照 BEFORE 触发器、行操作、AFTER 触发器的顺序执行的，其中任何一
步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作，那么会整个作为
一个事务被回滚（Rollback），但是如果是对非事务表进行的操作，那么已经更新的记录将无
法回滚，这也是设计触发器的时候需要注意的问题。