MySQL高级第一篇（共四篇）之索引、视图、存储过程和函数、触发器

╰半夏微凉° 2022-10-24 01:35 159阅读 0赞

# 1 索引 #

## 1.1 索引概述 ##

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。如下面的示意图所示 :![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWt1bmxvbmc_size_16_color_FFFFFF_t_70]  
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

## 1.2 索引优势劣势 ##

**优势**  
1） 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。  
2）通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

**劣势**  
1） 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间  
的。  
2） 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为  
更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所  
带来的键值变化后的索引信息。

## 1.3 索引结构 ##

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指  
针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。  
MySQL中的 B+Tree 索引结构示意图:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWt1bmxvbmc_size_16_color_FFFFFF_t_70 1]

## 1.4 索引分类 ##

1） 单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引  
2） 唯一索引 ：索引列的值必须唯一，但允许有空值  
3） 复合索引 ：即一个索引包含多个列

## 1.5 索引语法 ##

索引在创建表的时候，可以同时创建， 也可以随时增加新的索引。  
准备环境:

create database demo_01 default charset=utf8mb4;
    use demo_01;
    CREATE TABLE `city` (
    `city_id` int(11) NOT NULL AUTO_INCREMENT,
    `city_name` varchar(50) NOT NULL,
    `country_id` int(11) NOT NULL,
    PRIMARY KEY (`city_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE `country` (
    `country_id` int(11) NOT NULL AUTO_INCREMENT,
    `country_name` varchar(100) NOT NULL,
    PRIMARY KEY (`country_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);
    insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
    insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);
    insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);
    
    insert into `country` (`country_id`, `country_name`) values(1,'China');
    insert into `country` (`country_id`, `country_name`) values(2,'America');
    insert into `country` (`country_id`, `country_name`) values(3,'Japan');
    insert into `country` (`country_id`, `country_name`) values(4,'UK');

### 1.5.1 创建索引 ###

语法 ：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_name(index_col_name,...)
    index_col_name : column_name[(length)][ASC | DESC]

示例 ： 为city表中的city\_name字段创建索引 ；  
![在这里插入图片描述][20210128205605318.png]

### 1.5.2 查看索引 ###

语法：

show index from table_name;

示例：查看city表中的索引信息；  
![在这里插入图片描述][20210128205808801.png]

### 1.5.3 删除索引 ###

语法 ：

DROP INDEX index_name ON tbl_name;

示例 ： 想要删除city表上的索引idx\_city\_name，可以操作如下：  
![在这里插入图片描述][20210128210015485.png]  
可以看到索引只剩下主键索引了。

### 1.5.4 ALTER命令 ###

1). alter table tb_name add primary key(column_list);
    该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
    2). alter table tb_name add unique index_name(column_list);
    这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
    3). alter table tb_name add index index_name(column_list);
    添加普通索引， 索引值可以出现多次。
    4). alter table tb_name add fulltext index_name(column_list);
    该语句指定了索引为FULLTEXT， 用于全文索引

## 1.6 索引设计原则 ##

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

*  对查询频次较高，且数据量比较大的表建立索引。
 *  索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
 *  使用唯一索引，区分度越高，使用索引的效率越高。
 *  索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
 *  使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
 *  利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

创建复合索引:
    CREATE INDEX idx_name_email_status ON tb_seller(name,email,status);
    就相当于
    对name 创建索引 ;
    对name , email 创建了索引 ;
    对name , email, status 创建了索引 ;

# 2 视图 #

## 2.1 视图概述 ##

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。  
视图相对于普通的表的优势主要包括以下几项。

*  简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
 *  安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
 *  数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

## 2.2 创建或者修改视图 ##

创建视图的语法为：

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]

选项说明 :
    WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。
    LOCAL ： 只要满足本视图的条件就可以更新。
    CASCADED ： 必须满足所有针对该视图的所有视图的条件才可以更新。 默认值.

示例 , 创建city\_country\_view视图 , 执行如下SQL :

create or replace view city_country_view
    as
    select t.*,c.country_name from country c , city t where c.country_id = t.country_id;

查询视图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWt1bmxvbmc_size_16_color_FFFFFF_t_70 2]

## 2.3 查看视图 ##

从 MySQL 5.1 版本开始，使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的 SHOW VIEWS 命令。  
![在这里插入图片描述][202101282125261.png]  
如果需要查询某个视图的定义，可以使用 SHOW CREATE VIEW命令进行查看 ：  
![在这里插入图片描述][20210128212837456.png]

## 2.4 删除视图 ##

语法 :

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

示例 , 删除视图city\_country\_view :

DROP VIEW city_country_view ;

# 3. 存储过程和函数 #

## 3.1 存储过程和函数概述 ##

存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开  
发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。  
存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

*  函数 ： 是一个有返回值的过程 ；
 *  过程 ： 是一个没有返回值的函数 ；
 *

## 3.2 创建存储过程 ##

首先要学习关键字`DELIMITER`，该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。  
默认情况下，delimiter是分号`;`。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。  
在创建存储过程这种需要中途写分号的，需要临时替换掉默认的分隔符，比如把它换成$。

delimiter $
    CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
    begin
    -- SQL语句
    end ;
    delimiter ;

## 3.3 调用存储过程 ##

call procedure_name() ;

## 3.4 查看存储过程 ##

-- 查询db_name数据库中的所有的存储过程
    select name from mysql.proc where db='db_name';
    -- 查询存储过程的状态信息
    show procedure status;
    -- 查询某个存储过程的定义
    show create procedure test.pro_test1;

## 3.5 删除存储过程 ##

DROP PROCEDURE [IF EXISTS] sp_name ；

## 3.6 语法 ##

存储过程是可以编程的，意味着可以使用变量，表达式，控制结构 ， 来完成比较复杂的功能。

### 3.6.1 变量 ###

*  DECLARE：通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中。

DECLARE var_name[,...] type [DEFAULT value]

示例：

delimiter $
    create procedure pro_test2()
    begin
    declare num int default 5;
    select num+ 10;
    end$
    delimiter ;

*  SET：直接赋值使用 SET，可以赋常量或者赋表达式。  
    具体语法如下：

SET var_name = expr [, var_name = expr] ...

示例 :

DELIMITER $
    CREATE PROCEDURE pro_test3()
    BEGIN
    DECLARE NAME VARCHAR(20);
    SET NAME = 'MYSQL';
    SELECT NAME ;
    END$
    DELIMITER ;

也可以通过select … into 方式进行赋值操作 :

DELIMITER $
    CREATE PROCEDURE pro_test5()
    BEGIN
    declare countnum int;
    select count(*) into countnum from city;
    select countnum;
    END$
    DELIMITER ;

### 3.6.2 if条件判断 ###

语法结构 :

if search_condition then statement_list
    [elseif search_condition then statement_list] ...
    [else statement_list]
    end if;

需求：

根据定义的身高变量，判定当前身高的所属的身材类型
    180 及以上 ----------> 身材高挑
    170 - 180 ---------> 标准身材
    170 以下 ----------> 一般身材

示例 :

delimiter $
    create procedure pro_test6()
    begin
    declare height int default 175;
    declare description varchar(50);
    if height >= 180 then
    set description = '身材高挑';
    elseif height >= 170 and height < 180 then
    set description = '标准身材';
    else
    set description = '一般身材';
    end if;
    select description ;
    end$
    delimiter ;

### 3.6.3 传递参数 ###

语法格式 :

create procedure procedure_name([in/out/inout] 参数名 参数类型)
    ...
    IN : 该参数可以作为输入，也就是需要调用方传入值 , 默认
    OUT: 该参数作为输出，也就是该参数可以作为返回值
    INOUT: 既可以作为输入参数，也可以作为输出参数

示例(根据输入的身高，得出身材类型)：

delimiter $
    create procedure pro_test5(in height int , out description varchar(100))
    begin
    if height >= 180 then
    set description='身材高挑';
    elseif height >= 170 and height < 180 then
    set description='标准身材';
    else
    set description='一般身材';
    end if;
    end$
    delimiter ;

调用：  
![在这里插入图片描述][20210128215921954.png]  
**小知识**  
@description : 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。  
@@global.sort\_buffer\_size : 这种在变量前加上 “@@” 符号, 叫做系统变量。

### 3.6.4 case结构 ###

语法结构 :

方式一 :
    CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
    END CASE;
    方式二 :
    CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
    END CASE;

示例 :

delimiter $
    create procedure pro_test9(month int)
    begin
    declare result varchar(20);
    case
    when month >= 1 and month <=3 then
    set result = '第一季度';
    when month >= 4 and month <=6 then
    set result = '第二季度';
    when month >= 7 and month <=9 then
    set result = '第三季度';
    when month >= 10 and month <=12 then
    set result = '第四季度';
    end case;
    select concat('您输入的月份为 :', month , ' , 该月份为 : ' , result) as content ;
    end$
    delimiter ;

调用：  
![在这里插入图片描述][2021012822044815.png]

### 4.6.5 三种循环 ###

1.  while
2.  repeat
3.  loop

while语法：

while search_condition do
    statement_list
    end while;

repeat语法：

REPEAT
    statement_list
    UNTIL search_condition
    END REPEAT;

loop语法：

[begin_label:] LOOP
    statement_list
    END LOOP [end_label]

示例（计算从1加到n的和）：

delimiter $
    CREATE PROCEDURE `pro_test9`(n int)
    begin
    declare total int default 0;
    declare num int default 1;
    declare total2 int default 0;
    declare total3 int default 0;
    declare temp int default 0;
    
    -- 保存下n值，待会loop时用到
    set temp=n;
    
    while num<=n do
    	set total = total + num;
    	set num = num + 1;
    end while;
    
    repeat
    	set total2=total2+n;
    	set n=n-1;
    	-- 这里不能有分号
    	until n=0
    end repeat;
    
    bbb: loop
    	if temp<=0 then
    		-- loop需要使用leave才能退出循环
    		leave bbb;
    	end if;
    	set total3=total3+temp;
    	set temp=temp-1;
    end loop bbb;
    
    select total,total2,total3;
    end
    delimiter ;

三个循环结果一样：  
![在这里插入图片描述][20210128223527961.png]

### 3.6.9 游标/光标 ###

游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。  
声明游标：

DECLARE cursor_name CURSOR FOR select_statement ;

OPEN游标：

OPEN cursor_name ;

FETCH游标：

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE游标：

CLOSE cursor_name ;

示例 :  
初始化脚本:

create table emp(
    id int(11) not null auto_increment ,
    name varchar(50) not null comment '姓名',
    age int(11) comment '年龄',
    salary int(11) comment '薪水',
    primary key(`id`)
    )engine=innodb default charset=utf8 ;
    insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰 王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);

通过循环结构 , 获取游标中的数据 :

DELIMITER $
    create procedure pro_test12()
    begin
    DECLARE id int(11);
    DECLARE name varchar(50);
    DECLARE age int(11);
    DECLARE salary int(11);
    DECLARE has_data int default 1;
    DECLARE emp_result CURSOR FOR select * from emp;
    -- 这是一个句柄，当fetch为空后会执行到
    DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
    open emp_result;
    repeat
    -- 循环取出每一行数据
    fetch emp_result into id , name , age , salary;
    select concat('id为',id, ', name 为' ,name , ', age为 ' ,age , ', 薪水为: ',
    salary);
    until has_data = 0
    end repeat;
    close emp_result;
    end$
    DELIMITER ;

## 3.7 存储函数 ##

语法结构:

CREATE FUNCTION function_name([param type ... ])
    RETURNS type
    BEGIN
    ...
    END;

案例 :  
定义一个存储过程, 请求满足条件的总记录数 ;

delimiter $
    create function count_city(countryId int)
    returns int
    begin
    declare cnum int ;
    select count(*) into cnum from city where country_id = countryId;
    return cnum;
    end$
    delimiter ;

调用:  
![在这里插入图片描述][20210128225856380.png]  
可以看出存储函数和存储过程其实差不多。

# 4. 触发器 #

## 4.1 介绍 ##

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。  
![在这里插入图片描述][20210128230143873.png]

## 4.2 创建触发器 ##

语法结构 :

create trigger trigger_name
    before/after insert/update/delete
    on tbl_name
    [ for each row ] -- 行级触发器
    begin
    trigger_stmt ;
    end;

示例：  
首先创建一张日志表 :

create table emp_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型, insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作表的ID',
    operate_params varchar(500) comment '操作参数',
    primary key(`id`)
    )engine=innodb default charset=utf8;

创建 insert 型触发器，完成插入数据时的日志记录 :

DELIMITER $
    create trigger emp_logs_insert_trigger
    after insert
    on emp
    for each row
    begin
    insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
    values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));
    end $
    DELIMITER ;

创建 update 型触发器，完成更新数据时的日志记录 :

DELIMITER $
    create trigger emp_logs_update_trigger
    after update
    on emp
    for each row
    begin
    insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
    values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')'));
    end $
    DELIMITER ;

创建delete 行的触发器 , 完成删除数据时的日志记录 :

DELIMITER $
    create trigger emp_logs_delete_trigger
    after delete
    on emp
    for each row
    begin
    insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
    values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));
    end $
    DELIMITER ;

测试：

insert into emp(id,name,age,salary) values(null, '光明左使',30,3500);
    insert into emp(id,name,age,salary) values(null, '光明右使',33,3200);
    update emp set age = 39 where id = 3;
    delete from emp where id = 5;

查看日志表：  
![在这里插入图片描述][20210128230750319.png]

## 4.3 删除触发器 ##

语法结构 :  
drop trigger \[schema\_name.\]trigger\_name  
如果没有指定 schema\_name，默认为当前数据库 。

## 4.4 查看触发器 ##

可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。  
语法结构 ：

show triggers ；

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWt1bmxvbmc_size_16_color_FFFFFF_t_70]: /images/20221024/3ea0668162d74c309f7ab0b853f0839c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWt1bmxvbmc_size_16_color_FFFFFF_t_70 1]: /images/20221024/67064f6afac9446ca1d95302819f7bb1.png
[20210128205605318.png]: /images/20221024/31a79acd1acb41a3b9972b145c12fbae.png
[20210128205808801.png]: /images/20221024/ce401771013d48d39ee9d9ed2154fc92.png
[20210128210015485.png]: /images/20221024/41c78d4c79274d1d97a6297c882c7854.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWt1bmxvbmc_size_16_color_FFFFFF_t_70 2]: /images/20221024/2faa712095744f4db3a92c50b9f8ac2b.png
[202101282125261.png]: /images/20221024/39aedfb442864403a8ca3a7b2d5404c5.png
[20210128212837456.png]: /images/20221024/d71fac45570541b7a59a09ffadacb580.png
[20210128215921954.png]: /images/20221024/75923fc6a81e4ca2ba4a436a164ee583.png
[2021012822044815.png]: /images/20221024/03c25108db2b46cb9ee4b5e578acdee9.png
[20210128223527961.png]: /images/20221024/a0affb548ef44e8c80225458cd63bab9.png
[20210128225856380.png]: /images/20221024/3735991b287e40c4848c034a28c5b50f.png
[20210128230143873.png]: /images/20221024/5e1f4895885c41e3aef5fe22022f5f53.png
[20210128230750319.png]: /images/20221024/285c924ec2554aafa32a347ae903fa9b.png