MySQL高级

一、索引

1.1 索引概述

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找宣发的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。如下图所示：

左边是数据表，一共有2列7条记录，最左边的是数据记录的物理地址（注意：逻辑上相邻的记录在磁盘上野并不一定是物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应的数据。

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

1.2 索引的优势和劣势

优势：

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

劣势：

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

1.3 索引的数据结构

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。MySQL目前提供了以下4种索引：

• BTREE索引：最常见的索引类型，大部分索引都支持B树索引（默认）
• HASH索引：只有Memory引擎支持，使用场景简单
• R-tree索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型
• Full-text（全文索引）：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从MySQL5.6开始支持全文索引

我们平常所说的索引，如果没有特别指明，都是B+树（MySQL中的BTREE索引就是B+树，多路搜索树）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+tree索引，统称为索引。

1.3.1 BTREE结构

BTREE又叫多路平衡搜索树，一颗m叉的BTREE特性如下：

• 树种每个节点最多包含m个孩子
• 除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子
• 若根节点不是叶子节点，则至少有两个孩子
• 所有的叶子节点都在同一层
• 每个非叶子节点（叶子节点没有指针，因为没有子节点）由n个key（我们实际存入的数据）与n+1个指针（指向该节点的子节点）组成，其中[ceil(m/2)-1] <= n <= m-1

以5叉BTREE为例，每个节点key的数量：公式推导[[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <= 4，当n>4时，中间节点分裂到父节点，两边节点分裂。

插入 C N G A H E K Q M F W L T Z D P R X Y S为例，演变过程如下：

1. 插入前四个字母 C N G A

2. 插入H，此时因为插入H后n>4，所以中间元素G向上分裂到父节点

3. 插入E K Q不需要分裂

4. 插入M，此时M成为左子节点的中间元素，需要向上分裂到父节点，因为M比G大，所以在G的右边

5. 插入F W L T 不需要分裂

6. 插入Z，此时T成为最右边的子节点的中间元素，需要向上分裂到父节点中

7. 插入D，此时D成为最左边子节点的中间元素，需要向上分裂到父节点中。然后插入P R X Y不需要分裂

8. 最后插入S，S需要插入到NPQR节点，此时Q元素成为中间元素，需要向上分裂到父节点，Q分裂到父节点后，父节点为DGMQT，此时M为中间元素，需要继续向上分裂到新的父节点

到此，该BTREE就已经构建完成了，B树和二叉树相比，查询数据的效率更高，因为对于相同的数据量来说，B树的层级结构比二叉树小，因此搜索速度更快

1.3.2 B+TREE结构

B+树为B树的变种，B+树和B树的区别为：

1. n叉B+树最多含有n个key，而B树最多含有n-1个key
2. B+树的叶子节点保存所有的key信息，依key大小顺序排列
3. 所有的非叶子节点都可以看作是key的索引部分

1.3.3 MySQL中的B+TREE

MySQL索引数据结构对经典的B+树进行了优化。在原B+树的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+树，提高区间访问性能。链表指针的作用是便于区间查找，比如要查找主键为9~15之间的数据，从磁盘块4有链表指针指向磁盘块5，在查找完主键9后，就不用再从磁盘块1开始找索引，直接根据链表指针找到磁盘块5即可。

MySQL中的B+树示意图：

1.4 索引分类

• 单值索引（普通索引）：即一个索引只包含单个列，一个表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但允许有空值；如果是组合唯一索引则组合值必须唯一
• 主键索引：与唯一索引的区别是不允许有空值
• 复合索引：即一个索引包含多个列
• 全文索引：对文本的内容进行分词，进行搜索（全文索引主要用于解决模糊查询效率低的问题）

1.5 索引语法

索引在创建表的时候，可以同时创建，也可以随时增加新的索引。

准备表：

CREATE TABLE city (
    city_id int(11) NOT NULL AUTO_INCREMENT,
    citi_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(50) 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, '纽约', 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, city_name) values(1, '中国');
INSERT INTO country (country_id, city_name) values(2, '美国');

1.5.1 创建索引

基本语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 # CREATE后可以指定索引的类型（可以不指定，默认普通索引）、索引的名称
[USING index_type] # 可以指定索引的数据结构，如果不指定默认使用的是B+树索引
ON 表名(表中的列名, ...) # 然后指定对哪张表的哪些字段创建索引

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

实例：为city表中的city_name字段创建索引

CREATE INDEX idx_city_name ON city(city_name);

注意：在MySQL中如果一个字段是主键，则该字段默认为主键索引，索引名称就是PRIMARY，不用再为该字段创建索引

1.5.2 查看索引

基本语法：

SHOW INDEX FROM 表名; # 查看指定表的所有索引
# 这条语句的查询结果一行就表示这张表的一个索引

1.5.3 删除索引

基本语法：

DROP INDEX 索引名 ON 表名;

1.5.4 ALTER指令

通过ALTER指令修改表的同时也可以指定索引

# 1. 为该表添加一个主键，主键默认创建主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(列名);

# 2. 为该表创建一个唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名(列名); # 这里的列名可以是多个，多个列名即为组合唯一索引

# 3. 为该表创建一个普通索引
ALTER TABLE 表名 ADD INDEX 索引名(列名); # 这里的列名可以是多个，多个列名即为组合索引

# 4. 为该表创建一个全文索引
ALTER TABLE 表名 ADD FULLTEXT 索引名(列名); # 这里的列名可以是多个，多个列名即为组合全文索引

1.6 索引设计原则

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

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

# 创建组合索引
CREATE INDEX idx_name_email_status ON tb_seller(name, email, status);

# 根据最左前缀原则就相当于：
#   对name字段创建索引；
#   对name，email字段创建组合索引；
#   对name，email，status字段创建组合索引；
# 如果WHERE子句中使用了上面三种组合都会使用到索引查询，如果WHERE子句使用了name、status的组合（或email、status的组合）（或单独email、单独status），则不会用到索引

二、存储过程和函数

2.1 存储过程和函数概述

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的（比如存储过程中封装了N条SQL语句，单独执行这N条SQL语句的话，就需要和数据库服务器进行N次交互，而执行封装了这N条SQL语句的存储过程的话只需要和数据库服务器进行1次交互即可）。

存储过程和函数的区别在于：函数必须有返回值，而存储过程没有。

可以这样理解：函数是一个有返回值的存储过程，存储过程是一个没有返回值的函数。

2.2 创建存储过程

CREATE PROCEDURE 存储过程名([参数[,...]])
BEGIN
    -- SQL语句
END;

tips：由于MySQL中默认的分隔符为分号";"，在begin和end中间的SQL语句以分号结尾后就不会运行到END，这时我们可以用DELIMITER关键字来修改MySQL的分隔符，等存储过程创建完毕后再修改回分号。

例：

DELIMITER $  # 将分隔符替换为$

CREATE PROCEDURE pro_test1()
BEGIN
    SELECT * FROM city_table;
END$

DELIMITER ; # 替换回分号

2.3 调用存储过程

CALL 存储过程名();

2.4 查看存储过程

-- 1. 查询某数据库中的所有存储过程
SELECT name FROM mysql.proc WHERE db='test_db';

-- 2. 查询存储过程的状态信息
SHOW PROCEDURE status;

-- 3. 查询某个存储过程的定义
SHOW CREATE PROCEDURE pro_test1;

2.5 删除存储过程

DROP PROCEDURE [IF EXISTS] 存储过程名;

2.6 存储过程的语法

存储过程是可以编程的，这就意味着可以使用变量、表达式、控制结构，使SQL语句拥有过程化语言的特点，来完成比较复杂的功能。

2.6.1 变量

• DECLARE

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

DECLARE var_name[,...] TYPE [DEFAULT value]  # 可以同时声明多个变量

示例：

DELIMITER $  # 将分隔符替换为$

CREATE PROCEDURE pro_test2()
BEGIN
    DECLARE num int default 1; # 在BEGIN ... END块中定义一个名为num的变量，数据类型为int，默认值为1
    SELECT * FROM city_table WHERE city_id = num;
END$

DELIMITER ; # 替换回分号

• SET

直接赋值使用SET，可以将常量或者表达式赋值给变量。

SET var_name = expr [, var_name2 = expr2 ...]  # 可以同时为多个变量赋值

示例：

DELIMITER $  # 将分隔符替换为$

CREATE PROCEDURE pro_test3()
BEGIN
    DECLARE name varchar(20);
    SET name = '北京';
    SELECT * FROM city_table WHERE city_name = name;
END$

DELIMITER ; # 替换回分号

也可以通过SELECT ... INTO 的方式进行变量赋值操作。表示将查询得到的结果赋值给变量。

示例：

DELIMITER $  # 将分隔符替换为$

CREATE PROCEDURE pro_test3()
BEGIN
    DECLARE num int;
    SELECT COUNT(*) INTO num FROM city_table;
    SELECT CONCAT('city表中记录数为：', num);
END$

DELIMITER ; # 替换回分号

注意：如果数据库中表的数据有变化，则通过SELECT ... INTO方式赋的变量值也会有变化

2.6.2 if条件判断

语法结构：

IF 判断条件 THEN 判断条件成立执行的SQL语句
[ELSEIF 判断条件2 THEN 判断条件2成立执行的SQL语句]
[ELSE 以上判断条件都不成立执行的SQL语句]
END IF;

示例：

DELIMITER $  # 将分隔符替换为$

CREATE PROCEDURE pro_test4()
BEGIN 
    DECLARE height int;
    DECLARE description varchar(50) default '';
    SET height = 175;
    IF height >= 180 THEN
        SET description = '身材高挑';
    ELSEIF height >= 170 AND height < 180 THEN  # 多个判断条件之间用 AND、OR 连接
        SET description = '标准身材';
    ELSE
        SET description = '一般身材';
    END IF;
    SELECT CONCAT('身高：', height, ' 对应的身材类型为：', description);
END$

DELIMITER ; # 替换回分号

2.6.3 传递参数

在创建存储过程的时候指定传递的参数，语法格式：

CREATE PROCEDURE 存储过程名([IN/OUT/INOUT] 参数名 参数的数据类型)

# IN: 表示该参数可以作为输入，也就是需要调用存储过程时传入值，存储过程的参数默认为IN
# OUT：表示该参数作为输出，也就是该参数可以作为返回值
# INOUT：表示该参数既是传入值，也是返回值

• IN - 输入参数

示例：

DELIMITER $  # 将分隔符替换为$

CREATE PROCEDURE pro_test5(IN height int)  # height作为输入参数传递进存储过程
BEGIN 
    DECLARE description varchar(50) default '';
    SET height = 175;
    IF height >= 180 THEN
        SET description = '身材高挑';
    ELSEIF height >= 170 AND height < 180 THEN  # 多个判断条件之间用 AND、OR 连接
        SET description = '标准身材';
    ELSE
        SET description = '一般身材';
    END IF;
    SELECT CONCAT('身高：', height, ' 对应的身材类型为：', description);
END$

DELIMITER ; # 替换回分号

• OUT - 输出参数

示例：

DELIMITER $  # 将分隔符替换为$

CREATE PROCEDURE pro_test5(IN height int, OUT description)  # height作为输入参数传递进存储过程 
                        # description作为输该存储过程的返回值，注意存储过程的返回值不需要用return来返回
BEGIN 
    SET height = 175;
    IF height >= 180 THEN
        SET description = '身材高挑';
    ELSEIF height >= 170 AND height < 180 THEN  # 多个判断条件之间用 AND、OR 连接
        SET description = '标准身材';
    ELSE
        SET description = '一般身材';
    END IF;
END$

DELIMITER ; # 替换回分号

# 调用存储过程，获取返回值
# mysql中使用@来定义用户会话变量，代表在整个会话过程中都有用的一个变量
# 这里定义一个@description变量来接收存储过程pro_test5的返回值
call pro_test5(178, @description);

# 使用该变量
select @description;

注意：

1. mysql中使用@来定义用户会话变量，代表在整个会话过程中都有用的一个变量，当此次用户会话关闭（即SQL连接断开，则用户会话变量失效）。
2. mysql中还可以使用@@来定义系统变量，即用户会话关闭后依然有效的变量。
3. 在mysql中（不是存储过程中）定义变量必须使用@或者@@，如果直接使用SET name = 'aaa'; 会报错

• INOUT - 输入输出参数

示例：

DELIMITER $  # 将分隔符替换为$

CREATE PROCEDURE pro_test6(INOUT height int)  # height作为输入输出参数传递进存储过程
BEGIN 
    SET height = height + 10;
END$

DELIMITER ; # 替换回分号


# 这时候不能直接传常量进存储过程，因为存储过程需要一个输入输出参数
# 必须先定义一个变量，用来接收存储过程的返回值
# 同时定义这个变量的值，来传递进存储过程
SET @height = 165;
# 调用存储过程
CALL pro_test6(@height)
# 查看存储过程的返回结果
SELECT @height;

2.6.4 case结构

语法结构：

# 方式一：
CASE `值`
    WHEN `等于的值`THEN `SQL语句`
    [WHEN `等于的值` THEN `SQL语句`]
    [ELSE `SQL语句`]
END CASE;

# 方式二：
CASE
    WHEN `条件表达式` THEN `SQL语句`
    [WHEN `条件表达式` THEN `SQL语句`]
    [ELSE `SQL语句`]
END CASE;

示例：

-- 给定一个月份，计算出所在的季度

DELIMITER $  # 将分隔符替换为$

CREATE PROCEDURE pro_test7(in mon int)
BEGIN
    DECLARE result varchar(10);
    -- 这里用方式二，因为方式一只能判断等值，这里需要判断不等值
    CASE 
        WHEN mon >= 1 AND mon <= 3 THEN
            SET result = '第一季度';
        WHEN mon >= 4 AND mon <= 6 THEN
            SET result = '第二季度';
        WHEN mon >= 7 AND mon <= 9 THEN
            SET result = '第三季度';
        ELSE 
            SET result = '第四季度';
    SELECT CONCAT('传递的月份为：', mon, ' 所在的季度为：', result) AS content;
END$

DELIMITER ; # 替换回分号

2.6.5 循环结构

2.6.5.1 while循环

语法结构：

-- 只要条件表达式成立，就执行DO里面的SQL语句
WHILE `条件表达式` DO
    `SQL语句`
END WHILE;

示例：

-- 计算从1加到n的值

DELIMITER $  # 将分隔符替换为$

CREATE PROCEDURE pro_test8(in n int)
BEGIN
    DECLARE total int default 0;
    DECLARE num int default 1;
    
    WHILE start <= n DO 
        SET total = total + num;
        SET num = num + 1;
    END WHILE;
    
    SELECT CONCAT('输入的值为：', n, ' 计算的结果为：', total);
END$

DELIMITER ; # 替换回分号

2.6.5.2 repeat循环

语法结构：

-- 只要条件表达式成立就退出循环
REPEAT
    `SQL语句`
    UNTIL `条件表达式`
END REPEAT;

示例：

-- 计算从1加到n的值

DELIMITER $  # 将分隔符替换为$

CREATE PROCEDURE pro_test8(in n int)
BEGIN
    DECLARE total int default 0;
    DECLARE num int default 1;
    
    REPEAT
        SET total = total + num;
        SET num = num + 1;
    UNTIL num > n  # 注意：UNTIL后不加分号
    END REPEAT;
    
    SELECT CONCAT('输入的值为：', n, ' 计算的结果为：', total);
END$

DELIMITER ; # 替换回分号

2.6.5.3 loop循环

语法结构：

-- 退出循环的条件需要使用其他语句来定义，通常使用LEAVE语句实现
[begin_label:] LOOP # 声明当前LOOP循环的别名
    `SQL语句`
END LOOP [end_label] 

-- 如果不在SQL语句中增加退出循环的语句，则可以使用LOOP语句来实现简单的死循环

2.6.6 leave语句

用来从标注的流程构造中退出，通常和BEGIN ... END或者循环一起使用。

示例：

-- 计算从1加到n的值

DELIMITER $  # 将分隔符替换为$

CREATE PROCEDURE pro_test8(in n int)
BEGIN
    DECLARE total int default 0;
    DECLARE num int default 1;
    
    c: LOOP # 给循环起的别名为c
        SET total = total + num;
        SET num = num + 1;
        IF num > n THEN
            LEAVE c;  # LEAVE c; 表示退出循环c
        END IF;
    END LOOP c;
    
    SELECT CONCAT('输入的值为：', n, ' 计算的结果为：', total);
END$

DELIMITER ; # 替换回分号

2.6.7 游标/光标

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

声明游标：

# 如果不输入游标类型默认局部游标
DECLARE 游标名 CURSOR [游标类型] FOR 查询语句;

OPEN游标（想要遍历游标就得先打开游标）：

OPEN 游标名;

FETCH游标（即遍历游标）：

# 调用一次FETCH就获取到查询语句的一行结果
FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n] FROM 游标名 INTO 变量名1, 变量名2, ...
# 如果不输入FETCH的类型，则默认为NEXT
# NEXT：下一行；PRIOR：上一行；FIRST：第一行；LAST：最后一行；ABSOLUTE n：第n行
# INTO后面跟着变量名表示，把查询语句的查询结果放到对于的变量中（按照查询结果从左到右的顺序对应）

CLOSE游标（即关闭/释放游标）：

CLOSE 游标名;

示例：

-- 查询emp表中的数据，并逐行获取进行展示

DELIMITER $  # 将分隔符替换为$

CREATE PROCEDURE pro_test9()
BEGIN
    # 定义变量，用来接收游标的结果
    DECLARE e_id int(11);
    DECLARE e_name varchar(50);
    DECLARE e_age int(11);
    DECLARE e_salary int(11);
    # 这里声明一个变量用作循环退出的条件判断
    DECLARE has_data int DEFAULT 1;
    
    # 声明游标
    DECLARE emp_result CURSOR FOR SELECT * FROM emp;
    # 这里定义一个句柄，即游标查询不到数据时触发（这句话必须写在声明游标的下面，否则无法表示这个句柄对应哪个游标）
    # 如果不这样做的话，当游标拿不到数据的时候会报错
    # 还有一种思路是先用 SELECT COUNT(*) FROM emp; 获取到查询语句数据的总条数
    DECLARE EXIT HANDLER FOR NOT FOUND SET has_data = 0;
    # 打开游标
    OPEN emp_result;
    
    REPEAT
        FETCH emp_result into e_id, e_name, e_age, e_salary;
        SELECT CONCAT('ID：', e_id, '，姓名：', e_name, '，年龄：', e_age, '，薪资：'); # 使用游标获得的结果
        UNTIL has_data = 0 # 定义循环退出条件
    END REPEAT;
    
    #关闭游标
    CLOSE emp_result;
END$


DELIMITER ; # 替换回分号

2.7 存储函数

存储函数就是有返回值的存储过程，存储过程就是没有返回值的存储函数。存储函数虽然没有返回值，但是有out变量可以输出结果，所以存储函数可以做的事，存储过程也能做。

语法结构：

CREATE FUNCTION 存储函数名([参数名 参数类型])
RETURNS 返回值类型
BEGIN
    ...
END;

示例：

-- 定义一个存储函数，返回满足条件的总记录数

DELIMITER $  # 将分隔符替换为$

CREATE FUNCTION function_test1(countryId int)
RETURNS int
BEGIN
    DECLARE cnum int;
    # 这里用INTO 将一行一列的查询结果直接赋值给变量cnum
    SELECT COUNT(*) INTO cnum FROM city WHERE country_id = countryId;
    
    # 直接返回变量
    return cnum;
END$


DELIMITER ; # 替换回分号


-- 存储函数的调用（不再使用call，因为call调用的是存储过程，而存储函数有返回值，直接使用SELECT语句调用即可）

SELECT function_test1();

三、触发器

3.1 介绍

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

使用别名NEW和OLD来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。MySQL的触发器还只支持行级触发，不支持语句级触发，ORACLE两者都支持。

5.2 创建触发器

语法结构：

CREATE TRIGGER 触发器名
BEFORE或AFTER INSERT或UPDATE或DELETE
ON 表名
[FOR EACH ROW] -- 加上这句则表示创建的是行级触发器

BEGIN
    触发器中的SQL语句;
END;

示例：

通过触发器记录emp_t表的数据变更日志，包含增、删、改；

step1：创建一张日志表

CREATE TABLE emp_t_logs(
    id int(11) primary key auto_increment,
    operation varchar(20) not null comment '操作类型， insert/update/delete',
    operation_time datetime not null comment '操作时间',
    operation_id int(11) not null comment '操作表的ID',
    operation_params varchar(500) comment '操作参数，记录插入后数据、更新前后数据、删除前数据'
)engine=innodb default charset=utf8;

step2：创建insert型触发器，完成插入数据后的日志记录

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

DELIMITER $  # 将分隔符替换为$

create trigger emp_t_insert_trigger
after insert
on emp_t
for each row
begin
    insert into emp_t_logs(operation, operation_time, operation_id, operation_params)
    values('insert', current_time, NEW.id, 
          concat('插入后（id: ', NEW.id,', name: ', NEW.name,
                 ', age: ', NEW.age,', salary: ', NEW.salary,'）')); # 这里的NEW是插入后的那一行数据
end$

DELIMITER ; # 替换回分号

step3：创建update型触发器，完成修改数据后的日志记录

-- 创建update型触发器，完成修改数据后的日志记录

DELIMITER $  # 将分隔符替换为$

create trigger emp_t_update_trigger
after update
on emp_t
for each row
begin
    insert into emp_t_logs(operation, operation_time, operation_id, operation_params)
    values('update', current_time, 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,'）')); # 这里的OLD是修改前的那一行数据, NEW是修改后的那一行数据
end$

DELIMITER ; # 替换回分号

step4：创建delete型触发器，完成删除数据后的日志记录

-- 创建delete型触发器，完成删除数据后的日志记录

DELIMITER $  # 将分隔符替换为$

create trigger emp_t_delete_trigger
after delete
on emp_t
for each row
begin
    insert into emp_t_logs(operation, operation_time, operation_id, operation_params)
    values('delete', current_time, NEW.id, 
          concat('删除前（id: ', OLD.id,', name: ', OLD.name,
                 ', age: ', OLD.age,', salary: ', OLD.salary,'）')); # 这里的OLD是删除前的那一行数据
end$

DELIMITER ; # 替换回分号

step4：测试

insert into emp_t(name, age, salary) values('小明', 18, 10000);
update emp_t set name='小红', age=19, salary=8000 where id=1;
delete from emp_t where id=1;
# 操作后查询日志表
select * from emp_t_logs;

5.3 删除触发器

语法结构：

drop trigger [数据库名.]触发器名;

-- 如果没有指定数据库名，则默认当前数据库

5.4 查看触发器

语法结构：

-- 查看数据库中所有的触发器
show triggers;

四、存储引擎

4.1 存储引擎概述

和大多数数据库不同，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库的。所以存储引擎也可以被称为表类型。

Oracle、SqlServer等数据库只有一种存储引擎，MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应的引擎，或者编写存储引擎。

MySQL5.0支持的存储引擎包含：InnoDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB支持事务、有行级锁、支持外键。

可以通过指令 show engines ，来查询当前数据库支持的存储引擎。

创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后是InnoDB。

4.2 各种存储引擎特性

4.2.1 InnoDB的特性

InnoDB是MySQL默认的存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

InnoDB存储引擎不同于其他存储引擎的特点：

• 事务控制
• 外键约束：InnoDB是所有MySQL引擎中唯一支持外键的存储引擎，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候，也会自动的创建对应的索引。

下面两张表中，country_innodb是父表，country_id为主键索引，city_innodb是子表，country_id字段为外键，对应于country_innodb表的主键country_id。

-- 父表
create table country_innodb (
    country_id int primary key auto_increment,
    country_name varchar(100)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 子表
create table city_innodb (
    city_id int primary key auto_increment,
    city_name varchar(50),
    country_id int,
    key idx_fk_country_id(country_id),
    # 定义了外键country_id和另一张表country_innodb的country_id相关联
    constraint 'fx_city_country' foreign key(country_id) references country_innodb(country_id) 
    # 定义删除主表数据时，如果子表有关联记录则不删除
    on delete restrict
    # 定义更新主表数据时，如果子表有关联记录，更新子表记录
    on update cascade
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入数据
insert into country_innodb values(null, 'China'), (null, 'America'), (null. 'Japan'); # 批量插入写法
insert into city_innodb values(null, 'Xian', 1), (null, 'NewYork', 2), (null, 'Beijing', 1);

-- 验证删除
# 会提示删除失败，因为设置了外键关联的删除限制
delete from country_innodb where country_id=2; 

-- 验证更新
# 子表原来country_id=1的值也会被更新为country_id=100，因为设置了外键关联的更新
update country_innodb set country_id = 100 where country_id = 1; 

• 存储方式

Linux中MySQL数据默认存放在 /var/lib/mysql 目录下。InnoDB存储表和引擎有以下两种方式：

1. 使用共享表空间存储，这种方式创建的表其表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。
2. 使用多表空间存储，这种方式创建的表其表结构仍然保存在.frm文件中，但是每个表的数据和索引单独保存在.ibd文件中

4.2.2 MyISAM

MyISAM不支持事务、也不支持外键，其优势是访问的速度快，度事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。有以下两个比较重要的特点：

• 不支持事务
• 文件存储方式

每一个使用MyISAM为引擎创建的表在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是：

.frm（存储表结构）

.MYD（存储数据）

.MYI（存储索引）

4.3 存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行结合。以下是几种常用的存储引擎的使用环境。

• InnoDB：是MySQL默认的存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作处理插入和查询以外，还包含很多更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似于计费系统或财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
• MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
• MEMORY：将所有数据缓存在内存中，在需要快速定位记录和其他类似数据环境下，可以提供极快的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后，表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
• MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。

五、优化SQL步骤

在应用的开发过程中，由于初期数据量小，开发人员写SQL语句的时候更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多SQL语句开始逐渐显露出性能的问题，对生产的影响也越来越大，此时这些有问题的SQL语句就成为整个系统性能的瓶颈，因此我们必须对它们进行优化。

5.1 查看SQL执行频率

MySQL客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数"session"或者"global"来显示session级（当前连接）的统计结果和global级（自数据库上次启动至今）的统计结果。如果不写，默认使用的参数是"session"

-- 例：下面的命令可以得到当前session中所有操作的统计值（统计的是所有存储引擎的表）

show status like 'Com_______';
# 比如查询结果中的"Com_insert"表示当前连接的插入次数， "Com_select"表示当前连接的查询次数

-- 例：下面的命令可以得到Innodb行级相关的信息

show status like 'Innodb_rows_%';
# 比如查询结果中的"Innodb_rows_read"表示已经从以Innodb为引擎的表中读取的数据行数
# "Innodb_rows_inserted"表示以Innodb为引擎的表已经插入了多少条数据

我们通常比较关心的是以下的统计参数

5.2 定位低效率执行的SQL语句

可以通过以下两种方式定位执行效率较低的SQL语句

• 慢查询日志：通过慢查询日志定位那些执行效率较低的SQL语句，用 --log-slow-queries[=file_name] 选项启动时，MySQL会写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。

• show processlist：慢查询日志在查询结束以后才记录，所以在应用反应执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。

  show processlist的各字段含义：

  id：用户登录MySQL时，系统分配的"connection_id"，可以使用函数connection_id()查看

  user：显示当前用户。如果不是root用户，这个命令就只显示用户权限范围的SQL语句

  host：显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户

  db：显示这个进程目前连接的是哪个数据库

  command：显示当前连接的执行命令，一般取值为Sleep（休眠：表示客户端无任何操作）、Query（表示正在查询）、Connect（表示客户端正在连接）等

  time：显示截止到此次 show processlist 执行的时候，这条语句已经执行了多少秒

  state：显示使用当前连接的SQL语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个SQL语句，以查询为例，可能需要经过copy to tmp table、sorting result、sending data等状态才可以完成

  info：显示具体的SQL语句，是判断问题语句的一个重要依据

5.3 explain分析执行计划

通过以上步骤查询到效率低的SQL语句后，可以通过 explain SQL语句 或者 desc SQL语句 命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。

查询SQL语句的执行计划：

explain select * from tb_item where id = 1;

explain查询结果字段含义：

5.3.1 explain的id字段

id字段是select查询的序列号，是一组数字，表示的是查询中执行的select子句或者是操作表的顺序。

id的情况有三种：

1. id都是相同的表示加载表的顺序是从上到下

explain select * from t_role r, t_user u, user_role ur where r.id=ur.role_id and u.id=ur.user_id;

2. id不同，则id值越大，优先级越高，越先被执行

explain select * from t_role where id = 
    (select role_id from user_role where user_id = 
        (select id from t_user where username = 'stu1')
    );

3. id有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行

explain select * from t_role r, 
    (select * from user_role ur where ur.user_id = '2') a
where r.id = a.role_id;

5.3.2 explain的select_type字段

表示SELECT的类型，常见的取值如下（从上到下，效率越来越低）

-- SIMPLE
explain select * from t_user;

-- PRIMARY、SUBQUERY(子查询在where或select列表中)
explain select * from t_user where id = (select id from user_role where role_id = '9');

-- PRIMARY、DERIVED（子查询在from列表中，这时候子查询的结果存放在一张临时表中）
explain select a.* from (select * from t_user where id in ('1','2')) a;

-- PRIMARY、UNION、UNION RESULT(这时候还有一个id是NULL的UNION RESULT)
explain select * from t_user where id = '1' union select * from t_user where id = '2';

5.3.3 explain的table字段

table指的是当前这条语句查询的数据来自于哪张表

-- PRIMARY、SUBQUERY(子查询在where或select列表中，这时候SUBQUERY对应的table是user_role)
explain select * from t_user where id = (select id from user_role where role_id = '9');

-- PRIMARY、DERIVED（子查询在from列表中，这时候DERIVED对应的table是一张临时表derived2）
-- 这里的2表示是explain结果中id=2的临时表
explain select a.* from (select * from t_user where id in ('1','2')) a;

5.3.4 explain的type字段

type字段显示的是访问类型，是较为重要的一个指标，可取值为：

（从上到下，效率越来越低，一般来说我们需要保证查询至少达到range级别，最好达到ref ）

-- const
explain select * from t_user where id = '1'; # 主键索引
explain select * from t_user where username = 'stu1'; # 唯一索引

-- eq_ref
explain select * from t_user u, t_role r where u.id = r.id; # 主键索引关联，只返回一条数据

-- ref
explain select * from t_user where name = 'a'; # 非唯一索引

-- index 
explain select id from t_user; # 查询索引的id，即遍历了整个索引树

-- ALL
explain select * from t_user where psw = 'a1a1a'; # 非索引的查询，遍历了全表

5.3.5 explain中key相关的字段

explain中key相关的字段有三个

• possible_keys：显示这条查询语句可能用到的索引
• key：实际用到的索引，如果为NULL，则表示这条查询语句没有走索引
• key_len：表示索引中使用的字节数，该值为索引字段最大可能的长度，并非实际使用的长度，在不损失精确性的前提下，长度越短越好

5.3.6 explain的rows字段

表示扫描行的数量

-- rows的值为1，因为根据主键索引查询，只扫描了一行数据
explain select * from t_user where id = '1'; 
-- rows的值为6，因为没有走索引查询，需要全表扫描
explain select * from t_user where psw = 'aaaaa';

5.3.7 explain的Extra字段

显示其他的额外的执行计划信息。通常需要关注的取值如下：

（出现了前面两个通常要考虑性能优化）

-- Using filesort
explain select * from t_user order by psw; 
# 因为psw字段不是索引，所以这里不通过索引排序，而是通过扫描整个数据文件进行排序
#（如果这里通过索引排序则Extra为NULL）
# 优化方法：对排序字段加索引

-- Using temporary
explain select * from t_user group by psw;
# 优化方法：对分组依据字段加索引

5.4 show profile分析SQL

MySQL从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过 have_profiling 参数，能够看到当前MySQL是否支持profile。

select @@have_profiling;
# 返回 YES 则表示支持, @@是用来获取系统变量

默认profiling是关闭的，可以通过set语句在Session级别开启profiling。

-- 查看profiling是否开启
select @@profiling;
# 返回 0 表示关闭

-- 开启profiling
set profiling=1;

开启profiling后，可以通过 show profiles 命令查看在此次Session中查询语句的耗时，即开启profiling后会记录每条查询语句的查询时间。

select * from t_user;
select count(*) from tb_item;
select * from tb_item where title = 'abc';
# 执行完上面三条语句后再执行show profiles, 就可以得到上面三条语句的查询耗时
show profiles;

通过 show profiles 查询到所有查询语句的耗时后，返回结果中有一个Query_ID的字段，可以通过

show profile for query Query_ID 来查看指定Query_ID的查询语句再每个阶段耗时分别是多少

show profile for query 2;

show profile 语句返回两个字段，Status表示阶段，Duration表示阶段的耗时

Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回给客户端。由于在 Sending data 状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是查询的各状态中耗时最长的状态。

在获取到最耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间：

show profile cpu for query 2;

5.5 trace分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A计划，而不是选择B计划。

打开trace，设置格式为JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能完整展示。

set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

执行SQL语句：

select * from tb_item where id <= 4;

最后，检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的：

select * from information_schema.optimizer_trace;

六、索引的使用

索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

6.1 验证索引提升查询效率

在准备好的表结构tb_item中，一共存储了300万条记录。

A. 根据ID查询

select * from tb_item where id = 1999;

查询速度很快，接近0s，主要的原因是因为id是主键，有索引。

B. 根据NAME查询

select * from tb_item where name='abc';

查询速度很慢，10s，是因为name不是主键，需要走全表扫描查询

查看SQL语句的执行计划可知

explain select * from tb_item where name='abc';

处理方案，针对name字段，创建索引：

create index idx_item_name on tb_item(name);
# 此时数据库底层在对300万条记录进行重构索引，耗时较久

创建好name的索引后再次查询：

select * from tb_item where name='abc';

此时查询速度很快，接近0s，因为name也是索引，不用再走全表扫描

6.2 索引的使用

创建索引不一定能提高查询的效率，如何正确的使用索引，最主要的是要避免索引失效。

环境准备：

-- 先对tb_seller表的 name, status, address字段创建联合索引
create index idx_seller_name_sta_addr on tb_seller(name, status, address);

避免索引失效的方法：

1. 全值匹配。对索引中所有列都指定具体值，即查询条件中联合索引的所有列都指定值。

explain select * from tb_seller where name='小米' and status='1' and address='北京市';

2. 最左前缀法则。如果索引是联合索引，要遵循最左前缀法则。即如果查询条件只需要联合索引的部分列，一定要从创建联合索引时最左边的列开始使用

-- 匹配了最左前缀法则，索引生效
explain select * from tb_seller where name='小米';

explain select * from tb_seller where name='小米' and status='1';

explain select * from tb_seller where name='小米' and address='北京市';


-- 违反最左前缀法则，索引失效
explain select * from tb_seller where status='1';

explain select * from tb_seller where address='北京市';

explain select * from tb_seller where status='1' and address='北京市';

3. 范围查询右边的列不走索引（范围查询条件之后的字段索引失效）。即查询条件中如果有指定范围的条件应该放在最后，不然该条件后面的条件就不能走索引

explain select * from tb_seller where name='小米' and status > '1' and address='北京市';
# 此时走的是name和status两个字段的联合索引，而address因为在范围条件之后，所以address这个条件没有走索引

4. 不要在索引列上进行运算操作，否则索引失效

explain select * from tb_seller where substring(name, 3, 2) = '科技';
# 字符串操作也属于运算

5. 字符串不加单引号会造成索引失效

explain select * from tb_seller status = 1;
# 虽然status列的类型是字符串，但如果都是数字不加单引号MySQL底层也会将1做隐式类型转换然后再进行匹配
# 但是这样做会造成索引失效，原因是隐式类型转换实际上是对字段进行运算操作

6. 尽量使用覆盖索引，避免 select *，即只查询索引中包含的列。超出了索引覆盖的情况虽然也走了索引，但效率会降低，原因是使用覆盖索引的情况可以直接拿到索引对应的数据，但超出了覆盖索引时，就需要通过索引到数据表中去取值

-- 使用覆盖索引
explain select name from tb_seller where name='小米' and status='1' and address='北京市';

explain select name, status from tb_seller where name='小米' and status='1' and address='北京市';

explain select name, status, address from tb_seller where name='小米' and status='1' and address='北京市';

# 此时Extra列的结果是Using where; Using index 表示直接从索引中取值

-- 超出索引覆盖
explain select name, status, address, password 
from tb_seller where name='小米' and status='1' and address='北京市';

explain select * from tb_seller where name='小米' and status='1' and address='北京市';

# 此时Extra列的结果是Using index condition 表示只是用了索引作为查询条件，但具体的数据还是得通过索引到数据表中获取

7. 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及索引的列都不会被用到

-- 用or分割，没有走索引
explain select * from tb_seller where name='小米科技' or password='aaa';

-- 用and分割，走查询条件中的索引字段会走索引，只有非索引字段不走索引
explain select * from tb_seller where name='小米科技' and password='aaa';
# 此时name条件走索引，password条件不走索引

8. 以%开头的 like 模糊查询会导致索引失效。

   如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效。

-- 尾部模糊匹配，索引不会失效
explain select * from tb_seller where name like '小米%';

-- 带了头部模糊匹配的，索引失效
explain select * from tb_seller where name like '%小米';

explain select * from tb_seller where name like '%小米%'

解决方法：使用覆盖索引来进行查询

explain select sellerid, name, status, address from tb_seller where name like '%小米%';
# sellerid虽然不属于联合索引中的列，但sellerid是主键，主键自带索引，所以可以用作覆盖索引查询的列
# 此时虽然用了头部模糊匹配，但还是会走索引

9. 如果MySQL评估，此时使用索引的效率要比走全表扫描更慢，则不会使用索引

-- 走全表扫描
explain select * from tb_seller where name='苹果';

-- 走索引
explain select * from tb_seller where name='小米科技';

# 同样的SQL语句，只有查询条件的值不同，MySQL判定一个走了全表扫描一个走了索引
# 这是和数据表中的数据有关，因为此时数据表中一共有12条数据数据
# 而name='苹果'的数据占了11条，这种情况下MySQL判定走全表扫描比走索引更快