Mysql -- 视图/触发器/存储过程/函数/事务/索引

一、视图

视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。

--格式：CREATE VIEW 视图名称 AS  SQL语句
CREATE VIEW view_name AS 
SELET field,field,.. 
FROM table_name WHERE field > 10

--格式：DROP VIEW 视图名称

DROP VIEW view_name

-- 格式：ALTER VIEW 视图名称 AS SQL语句

ALTER VIEW view_name AS SELECT * FROM table WHERE table.id>300

使用视图时，将其当作表进行操作即可，由于视图是虚拟表，所以无法使用其对真实表进行创建、更新和删除操作，仅能做查询用。

SELECT * FROM view_name

二、触发器

对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。

# 插入前
CREATE TRIGGER 触发器名称 BEFORE INSERT ON 表名 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER 触发器名称 AFTER INSERT ON 表名 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER 触发器名称 BEFORE DELETE ON 表名 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER 触发器名称 AFTER DELETE ON 表名 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER 触发器名称 BEFORE UPDATE ON 表名 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER 触发器名称 AFTER UPDATE ON 表名 FOR EACH ROW
BEGIN
    ...
END

# 插入前
delimiter // # delimiter修改；终止符为//
CREATE TRIGGER 触发器名称 BEFORE INSERT ON 表名 FOR EACH ROW
BEGIN
    # 操作语句；每次触发新增数据前修改计数
    update user_tag set tag=tag+1;
END//
delimiter ; # delimiter修改回来为；

# 动态数据
delimiter //
CREATE TRIGGER 触发器名称 BEFORE INSERT ON users FOR EACH ROW
BEGIN
    INSERT INTO users_copy (username_copy) VALUES (NEW.username); # NEW.username == users.username
END//
delimiter ;

DROP TRIGGER 触发器名称

SHOW TRIGGERS

三、存储过程

-- 创建存储过程

delimiter //
create procedure 存储过程名称()
BEGIN
    select * from t1; # sql语句
END//
delimiter ;



-- 执行存储过程

call 存储过程名称()

# 传入参数 in 返回参数 out 即传入又有返回值的参数 inout 
# (in,out,inout)
delimiter //
CREATE PROCEDURE p1(in n1 INT,out n2 INT)
BEGIN
    SET n2 = 123456;
    SELECT * FROM users WHERE id = n1;
END //
delimiter ;

# 执行p1这个存储过程
SET @n2 = 1;
CALL p1(3,@n2);
SELECT @n2;

# 删除p1这个存储过程
DROP PROCEDURE p1

#!/usr/bin/python3
import pymysql

# 打开数据库连接
db = pymysql.connect(
    host="localhost",
    port=3306,
    user='root',
    password='123456',
    database='jd',
    charset='utf8'
)

# 使用cursor()方法获取操作游标
cursor = db.cursor()
try:
    """
    存储过程示例：
        # 传入参数 (in,out,inout)
        delimiter // 
        CREATE PROCEDURE p1(in n1 INT,out n2 INT)
        BEGIN
            SET n2 = 123456;
            SELECT * FROM users WHERE id = n1;
        END //
        delimiter ;
        
        # 执行p1这个存储过程
        SET @n2 = 1;
        CALL p1(3,@n2);
        SELECT @n2;
        
        # 删除p1这个存储过程
        DROP PROCEDURE p1
    """
    # 执行存储过程p1()
    cursor.callproc('p1',(1,2))
    # 提交操作
    db.commit()
    # 获取第一个结果集
    r1 = cursor.fetchall()
    # 执行结果集1 ((1, 'admin', '895025041@qq.com', None),)
    print(r1)

    # 获取存储过程参数
    cursor.execute('select @_p1_0,@_p1_1')
    # 提交操作
    db.commit()
    # 获取第二个结果集 @_p1_1 = 123456
    r2 = cursor.fetchall()
    # 执行结果集2 ((1, 123456),)
    print(r2)
except:
    # 发生错误时回滚
    db.rollback()

# 关闭连接
db.close()

delimiter \\
create PROCEDURE p1(
        OUT return_status tinyint
)
BEGIN 
    # 如果出现错误
    DECLARE exit handler for sqlexception 
    BEGIN 
        -- ERROR 
        set return_status = 1; 
        rollback; 
    END; 
 # 如果出现警告
    DECLARE exit handler for sqlwarning 
    BEGIN 
        -- WARNING 
        set return_status = 2; 
        rollback; 
    END; 
 # 开始事物
    START TRANSACTION; 
        DELETE from tb1;
        insert into tb2(name)values('小明');
    COMMIT; 
 # 正常处理
    -- SUCCESS 
    set return_status = 0; 
 
END\\
delimiter ;




###mysql客户端###
set @i =0; # 设置参数
call p1(@i); # 执行存储过程
select @i; # 查看执行结果 return_status

表结构：A表，B表 -- 目的：我们要让B表的bnum = A表的aint + anum
table A
aint anum
1     100
2     100
3     100


table B
bint bnum
***  ***
***  ***
***  ***

****************************************************
delimiter //
-- 创建存储过程p3
create procedure p3()
begin 
        declare row_id int; -- 自定义变量1  
        declare row_num int; -- 自定义变量2  
        declare temp int; -- 自定义变量2  

        DECLARE done INT DEFAULT FALSE; -- 声明变量用于检查是否还有数据   类似于done = Flase
        DECLARE my_cursor CURSOR FOR select aid,anum from A;  -- 声明游标 从那个表获取  A表aid,anum
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- NOT FOUND检测是否还有数据 如果没有则 done = True
        
        open my_cursor; -- 打开游标
                ele: LOOP  -- 开始循环 forloop[随便写]
                        fetch my_cursor into row_id,row_num; -- 赋值给 row_id = aid ,row_num = anum
                        if done then -- 如果循环完毕
                                leave ele; -- 离开循环 break
                        END IF; -- 结束

                        SET temp = row_id + row_num; -- 从A表获取得 row_id + row_num = temp
                        insert into B(bnum) values(temp);
                end loop ele;
        close my_cursor; -- 关闭游标
end  //
delimter ;


CALL p3()  -- 执行存储过程p3

delimiter \\
CREATE PROCEDURE p4 (
    in arg1 int, -- 参数1
    in arg2 int -- 参数2
)
BEGIN
    # 检测sql语句的合法性
    # sql = 格式化sql_msg + arg
    # 执行sql语句
    
    PREPARE xxx FROM 'INSERT INTO A(anum,anum2) VALUES(?,?)'; -- 占位符？
    SET @arg1 = arg1;
    SET @arg2 = arg2;
    EXECUTE xxx USING @arg1,@arg2; -- 格式化arg 只能是session级别的变量
    DEALLOCATE prepare xxx; -- 执行sql
END\\
delimiter ;

# 执行存储过程
CALL p4(11,12)

-----------------------------------------------------------------------------------------------------------------------------------

删除存储过程
drop procedure proc_name;

查询数据库中的存储过程

方法一：
select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'
 
方法二：
show procedure status;

查看存储过程或函数的创建代码
show create procedure proc_name;
show create function func_name;

执行存储过程的方式：

-- 无参数
call proc_name()

-- 有参数，全in
call proc_name(1,2)

-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()


print(result)

四、函数

1、内置函数

　官方文档

2、自定义函数

delimiter \\
create function 函数名(参数1 类型(int,varchar,..),参数2 类型(int,varchar,..)) returns 类型(int,varchar,..)
BEGIN
    declare num int; # declare声明变量
    set num = 参数1 + 参数2;
    return(num);
END \\
delimiter ;

drop function 函数名;

SELECT 函数名(1,100)

五、索引

作用：1、约束；2、加速查找

索引类型：

　　- 普通索引：加速查找

　　- 主键索引：加速查找+不能为空+不能重复

　　- 唯一索引：加速查找+不能重复

　　- 联合索引：联合主键索引、联合唯一索引、联合普通索引

索引种类：

一.hash索引：应用较少，创建索引表，将数据转换为hash值+数据行的hash存储地址
查找：根据hash表内存地址查找数据
缺点：由于hash存储的是无序的会打乱原有数据的顺序所以找范围数据的时候速度慢
优点：查找单值的时候速度非常快

二.btree索引：应用多，使用二叉树数据结构排列数据
查找：按照二叉树层级查找数据
优点：范围快
缺点：单查比hash慢

建立索引：
    - a.额外的文件保存特殊的数据结构
    - b.查询快；插入更新删除慢
    - c.命中索引
        例：
        SELECT * FROM s1 WHERE email = 'zhangsan1902982@qq.com'   -- 命中索引
        SELECT * FROM s1 WHERE email LIKE '%zhangsan1902982@qq.com%' -- 无法命中
    - d.后天创建索引：
         - 普通索引
             -- create index 索引名称 on 表名(列名)    
             -- drop index 索引名称 on 表名
         - 唯一索引
             -- create unique index 索引名称 on 表名(列名)    
             -- drop unique index 索引名称 on 表名    
         - 联合索引
             -- create  index 索引名称 on 表名(列名,列名,列名...)    
             -- drop  index 索引名称 on 表名        
         - 联合唯一索引
             -- create unique index 索引名称 on 表名(列名,列名,列名...)    
             -- drop unique index 索引名称 on 表名

------------------------------------------------------------普通索引-------------------------------------------------------------

创建方法：使用关键字index 别名xxx(字段)

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

create index 别名 on 表名(字段)

drop 索引名 on 表名;
解析：删除某个表的索引

show index from 表名;

对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length
create index 索引名 on 表名(字段(长度));

------------------------------------------------------------唯一索引-------------------------------------------------------------

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)

create unique index 索引名 on 表名(列名)

drop unique index 索引名 on 表名

------------------------------------------------------------主键索引-------------------------------------------------------------

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

OR

create table in1(
    nid int not null auto_increment,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    primary key(ni1),
    index ix_name (name)
)

alter table 表名 add primary key(列名);

alter table 表名 drop primary key;
alter table 表名  modify  列名 int, drop primary key;

------------------------------------------------------------组合索引-------------------------------------------------------------

create table in3(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text
)

create index ix_name_email on in3(name,email);

其应用场景为：频繁的同时使用n列来进行查询，如：where name = 'xxx' and email = 'xxx.qq.com'。

如上创建组合索引之后，查询：

name and email  -- 使用索引
name                 -- 使用索引
email                 -- 不使用索引

注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。

频繁查找的列
    - 创建索引
    - 命中索引
数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。
即使建立索引，索引也不会生效：

索引：nid name
- like '%xx'
    select * from tb1 where name like '%cn';
- 使用函数
    select * from tb1 where reverse(name) = '海绵';
- or
    select * from tb1 where nid = 1 or email = 'xxx@qq.com';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'xxx';
            select * from tb1 where nid = 1 or email = 'xxx@qq.com' and name = 'xxx'
- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where name = 999;
- !=
    select * from tb1 where name != 'alex'
    特别的：如果是主键，则还是会走索引
        select * from tb1 where nid != 123
- >
    select * from tb1 where name > 'alex'
    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
- order by
    select email from tb1 order by name desc;
    当根据索引排序时候，选择的映射如果不是索引，则不走索引
    特别的：如果对主键排序，则还是走索引：
        select * from tb1 order by nid desc;
 
- 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

六、注意事项

- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量用 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合

 七、执行计划

explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化
如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;

　　+----+-------------+-------+------+---------------+------+---------+------+------+-------+

　　| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |

　　+----+-------------+-------+------+---------------+------+---------+------+------+-------+

　　|  1 | SIMPLE      | tb2   | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |

　　+----+-------------+-------+------+---------------+------+---------+------+------+-------+

1 row in set (0.00 sec)

特别的：如果使用union连接气值可能为null

1）、id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行，id列为null的就表是这是一个结果集，不需要使用它来进行查询。

 

2）、select_type列常见的有：

A：simple：表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个

B：primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个

C：union：union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union

D：dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响

E：union result：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null

F：subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery

G：dependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响

H：derived：from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select

3）、table

显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的<derived N>就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

4）、type

依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引

A：system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index

B：const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描

C：eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref

D：ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。

E：fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

F：ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。

G：unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值

H：index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

I：range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

J：index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range

K：index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

L：all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

5）、possible_keys

查询可能使用到的索引都会在这里列出来

6）、key

查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

7）、key_len

用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

8）、ref

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

9）、rows

这里是执行计划中估算的扫描行数，不是精确值

10）、extra

这个列可以显示的信息非常多，有几十种，常用的有

A：distinct：在select部分使用了distinc关键字

B：no tables used：不带from字句的查询或者From dual查询

C：使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。

D：using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中

E：using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。

F：using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。

G：using sort_union，using_union，using intersect，using sort_intersection：

using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集

using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集

using sort_union和using sort_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。

H：using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。

I：using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition

J：firstmatch(tb_name)：5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个

K：loosescan(m..n)：5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个

除了这些之外，还有很多查询数据字典库，执行计划过程中就发现不可能存在结果的一些提示信息

 

11）、filtered

使用explain extended时会出现这个列，5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

八、limit分页性能

使用id定位法来获取用户当前页码返回数据
下一页：

    select * from tb1 
    where 
        nid < (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc limit 每页数据 *【页码-当前页】) A order by A.nid asc limit 1)  
    order by 
        nid desc 
    limit 10;


上一页：

    select * from tb1 
    where 
        nid < (select nid from (select nid from tb1 where nid > 当前页最大值 order by nid asc limit 每页数据 *【当前页-页码】) A order by A.nid asc limit 1)  
    order by 
        nid desc 
    limit 10;

九、慢日志

a、配置MySQL自动记录慢日志
    - 配置文件方式
        slow_query_log = OFF                   是否开启慢日志记录
        long_query_time = 2                    时间限制，超过此时间，则记录
        slow_query_log_file = /usr/slow.log    日志文件
        log_queries_not_using_indexes = OFF    为使用索引的搜索是否记录
    - 改变mysql内存方式
        查看当前配置信息：show variables like '%query%'
        修改当前配置：set global 变量名 = 值

b、查看MySQL慢日志

mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log   

"""
--verbose    版本
--debug      调试
--help       帮助
 
-v           版本
-d           调试模式
-s ORDER     排序方式
             what to sort by (al, at, ar, c, l, r, t), 'at' is default
              al: average lock time
              ar: average rows sent
              at: average query time
               c: count
               l: lock time
               r: rows sent
               t: query time
-r           反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)
-t NUM       显示前N条just show the top n queries
-a           不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-g PATTERN   正则匹配；grep: only consider stmts that include this string
-h HOSTNAME  mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard),
             default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           总时间中不减去锁定时间；don't subtract lock time from total time