day040 数据库索引补充 存储过程 事务等

1.正确使用索引

视图: 关键词 view

视图是体格虚拟表

　　创建视图 : create view 视图名称 as sql语句;

　　　　　　例: create view t_view as select * from teacher where id =2;

　　使用视图: 如果修改了视图,那么原表也会更改

往视图中插入数据 :  insert into t_view values (......);

　　 修改视图 : alter view 视图名称 as sql语句

　　　　　  例: alter view v_view as select * from t2;

　　删除视图: drop view v_view;

触发器(trigger)

用户对某一个表进行操作(增删改查)的时候,触发某个操作(增删改查),成为触发器.

创建触发器: 

　　插入前创建 : create trigger t_trigger before insert on s1 for each row

　　插入后创建 : create trigger t_trigger after insert on s1 for each row

　　删除前 : create trigger t_trigger before delete on s1 for each row 

　　删除后: create trigger t_trigger after delete on s1 for each row

　　更新前: create trigger t_trigger before update on s1 for each row 

　　更新后: create trigger t_trigger after update on for each row

插入后触发触发器:

实际中创建触发器

#准备表
CREATE TABLE cmd (  #这是一张指令信息表，你在系统里面执行的任何的系统命令都在表里面写一条记录
    id INT PRIMARY KEY auto_increment,  #id
    USER CHAR (32),  #用户
    priv CHAR (10),  #权限          
    cmd CHAR (64),   #指令
    sub_time datetime, #提交时间  
    success enum ('yes', 'no') #是否执行成功，0代表执行失败
);

CREATE TABLE errlog ( #指令执行错误的信息统计表，专门提取上面cmd表的错误记录
    id INT PRIMARY KEY auto_increment, #id
    err_cmd CHAR (64),  #错误指令
    err_time datetime   #错误命令的提交时间
);
#现在的需求是：不管正确或者错误的cmd，都需要往cmd表里面插入，然后，如果是错误的记录，还需要往
errlog表里面插入一条记录
#若果没有触发器，我们会怎么实现，我们完全可以通过咱们的应用程序来做，根据cmd表里面的success这个
字段是哪个值（yes成功，no表示失败），在给cmd插入记录的时候，判断一下这个值是yes或者no，来判断一
下成功或者失败，如果失败了，直接给errlog来插入一条记录
#但是mysql说，你的应用程序可以省事儿了，你只需要往cmd表里面插入数据就行了，没必要你自己来判断
了，可以使用触发器来实现，可以判断你插入的这条记录的success这个字段对应的值，然后自动来触发触发
器，进行errlog表的数据插入

#创建触发器
delimiter //      （或者写$$，其他符号也行，但是不要写mysql不能认识的，知道一下就行了），
delimiter 是告诉mysql，遇到这句话的时候，就将sql语句的结束符分号改成delimiter后面的//
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW            
   #在你cmd表插入一条记录之后触发的。
BEGIN           #每次给cmd插入一条记录的时候，都会被mysql封装成一个对象，叫做NEW，里面的字段
都是这个NEW的属性
    IF NEW.success = 'no' THEN           #mysql里面是可以写这种判断的，等值判断只有一个等
号，然后写then
            INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ;
     #必须加分号，并且注意，我们必须用delimiter来包裹，不然，mysql一看到分号，就认为你的sql
结束了，所以会报错
      END IF ;       #然后写end if，必须加分号  
END//      #只有遇到//这个完成的sql才算结束
delimiter ;        #然后将mysql的结束符改回为分号


#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('chao','0755','ls -l /etc',NOW(),'yes'),
    ('chao','0755','cat /etc/passwd',NOW(),'no'),
    ('chao','0755','useradd xxx',NOW(),'no'),
    ('chao','0755','ps aux',NOW(),'yes');


#查询错误日志，发现有两条
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2017-09-14 22:18:48 |
|  2 | useradd xxx     | 2017-09-14 22:18:48 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)

删除触发器

　　drop frigger 触发器名;　　

　　事务

#原子操作
start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='chao'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元
commit;  #只要不进行commit操作，就没有保存下来，没有刷到硬盘上
 
#出现异常，回滚到初始状态
start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='chao'; #中介拿走10元
uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到
rollback;  #如果上面三个sql语句出现了异常，就直接rollback，数据就直接回到原来的状态了。但是执行了commit之后，rollback这个操作就没法回滚了
#我们要做的是检测这几个sql语句是否异常，没有异常直接commit，有异常就rollback，但是现在单纯的只是开启了事务，但是还没有说如何检测异常，我们先来一个存储过程来捕获异常，等我们学了存储过程，再细说存储过程。
commit;

事物的四大特性：

    - 原子性
    - 一致性
    - 持久性
    - 隔离性

事务用于将某些操作的多个SQL作为原子性操作，也就是这些sql语句要么同时成功，要么都不成功，事务的其他特性在我第一篇博客关于事务的介绍里面有，这里就不多做介绍啦，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

　　　　简单来说：我给一个姑娘转账，姑娘那儿收到了200，你的账户上扣了200，这两个操作是不是两个sql语句，这两个sql语句是你的应用程序发给mysql服务端的，并且这两个sql语句都要一起执行，不然数据就错了，你想想是不是。并且如果你通过应用程序发送这两条sql的时候，由于网络问题，你只发送了一个sql过来，那只有一个账户改了数据，另外一个没改，那数据是不是就出错了啊。这就是事务要完成的事情。

索引,创建的时候添加,添加索引的时候要注意,给字段里面数据大小比较小的字段添加,给字段里面的数据区分度高的字段添加

1) 索引未命中

　　如果我们在id字段加上了主键索引,只有select * from t1 where id=100;

　　where 后面的条件中没有用到id,那么就没有用到主键索引,这种情况就是索引未命中.

如果你有300w条数据,你的where 条件是id>100 ,我们会发现,随着你搜索的范围的增大,速度会越来越慢,会成倍的体现出来.

1.范围问题,或者说条件不明确,条件中出现了这些符号或者关键字' >, <, >= ,!= ,like ,between..and...'等

2.写索引的时候尽量选择区分度高的列作为索引,区分度公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录越少,唯一键的区分度是1,而一些状态,性别字段可能在大数据面前区分度就是0.使用场景不同,这个值很难确定,一般需要join的字段我们都要求是0.1以上,即平均1秒扫描10条记录.

3.=和in可以乱序,比如a=1 and b=2 and c=3 建立a,b,c索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式.

4.索引列不能参与计算,比如:where id*10=10000;原因 ,b+树种存的都是数据表中的字段值,但进行检索时,需要把所有的元素都应用函数才能比较,显然成本太大,所以在写语句的时候,不要用索引进行 '' + - * / '' 操作 .

5.and 和 or

6.最左前缀匹配原则 ,对于组合索引mysql会一直向右匹配知道遇到范围查询(> < between like)就停止(指的是范围大了,有索引速度也慢)

普通索引 ,联合索引和唯一索引

普通索引:create index 索引名 on 表名(列名,)

　　 　　drop index 索引名 on 表名

唯一索引: create unique index 索引名 on 表名(列名)

　　　　  drop unique index 索引名 on 表名

联合索引: create unique index 索引名 on 表名(列名.列名)

　　　　  drop unique index 索引名称 on 表名

建立所联合索引的一个原则: 索引是有个最左匹配的原则的,所以建联合索引的时候,将区分度高的放在最左边,依次排下来,范围查询的条件尽可能的往后面放.

联合索引的第二个好处是在第一个键相同的情况下,已经对第二个键进行了排序处理,例如再很多情况下应用程序都需要查询某个用户的购物情况,并按照时间进行排序,最后取出最近三次的购买记录,这是使用联合索引可以帮我们避免多一次的排序操作,因为索引本身在叶子节点已经排序了.

覆盖索引

　　innodb储存引擎支持覆盖引擎,即从辅助索引中就可以得到查询记录,二不需要查询聚集索引中记录.

唯一索引

create table t1(
    id int unique,
    );

create table t1(
    id int,
    unique key unq_id(id)
    );

#表创建好之后添加唯一索引:
alter table t1 add unique key u_name(name);

#删除
alter table t1 drop index u_name;

普通索引

index 
#创建
create table t1(
    id int,
    index ind_id(id)
    );

alter table t1 add index ind_id(id);

create index ind_id on t1(id);

#删除
alter table t1 drop index ind_id(id);
drop index ind_id on t1;

　　

存储过程

使用存储过程的优点:

1.用于代替程序写的sql语句,实现程序与sql解耦

2.基于网络传输,传别名的数据量小,而直接传sql数据量大

创建简单存储过程(无参)

delimiter //
create procedure p1()
begin
    select * from blog;
    insert into blog(name,sub_time) values('xxx',now());

end //

delimiter ;

call p1()
#类似于mysql的函数,但不是函数,mysql的函数都是放在mysql语句里面用的,不能单独的使用,存储过程是可以直接调用的 call 名字+() 调用;
#mysql 的视图啊 触发器啊等等都能在存储过程里面写,这是一大堆的sql的集合体,都可以综合到这里面

在python 中基于 pymysql 调用
cursor.callproc('p1')
print(cursor.fetchall())

　　储存过程中是可以传参数的

#对于储存过程,可以接收参数,参数有三种:
# in     仅用于传入参数
# out     仅用于返回值用
# inout    既可以用于传入也可以当做返回值

　　in 传入参数:

delimiter //
create procedure p2(
    in n1 int, # n1参数是需要传入的,也就是接收外部数据,并且这个数据必须是int类型
    in n2 int
    )
begin

    select * from blog where id>n1 ;
end //
delimiter ;

# 调用存储过程的两种方式,
call p2(1,2)
#在python中基于pymysql调用
cursor.callproc('p2',(1,2))
print(cursor.fetchall())

　　out 返回值:

#查看存储过程的一些信息：show create procedure p3; #查看视图啊、触发器啊都这么看，还可以
用G，show create procedure p3G;G的意思是你直接查看表结构可能横向上显示不完，G是让表
给你竖向显示，一row是一行的字段
delimiter //
create procedure p3(
    in n1 int,
    out res int
)
BEGIN
    select * from blog where id > n1;  
    set res = 1;  #我在这里设置一个res=1，如果上面的所有sql语句全部正常执行了，那么这一句肯
定也就执行了，那么此时res=1，如果我最开始传入的时候，给res的值设置的是0，
#那么你想，最后我接收到的返回值如果是0，那么说明你中间肯定有一些sql语句执行失败了
#注意写法：out的那个参数，可以用set来设置，set设置之后表示这个res可以作为返回值，并且不需要像
python一样写一个return，你直接set之后的值，就是这个存储过程的返回值
END //
delimiter ;

#在mysql中调用
set @res=0; #这是MySQL中定义变量名的固定写法(set @变量名=值)，可以自己规定好，0代表假（执行
失败），1代表真（执行成功），如果这个被改为1了，说明存储过程中的sql语句执行成功了
call p3(3,@res);#注意：不要这样写：call p3（3，1），这样out的参数值你写死了，没法确定后面
这个1是不是成功了，也就是说随后这个out的值可能改成0了，也就是失败了，但是这样你就判断不了了，你
后面查看的这个res就成1了，所以这个参数应该是一个变量名昂，定义变量名就是上一句,如果你直接传一个
常量数字，会报错的，写法不对。
select @res; #看一下这个结果，就知道这些sql语句是不是执行成功了，大家明白了吗~~~

#在python中基于pymysql调用，在python中只需要知道存储过程的名字就行了
cursor.callproc('p3',(3,0)) #0相当于set @res=0，为什么这里这个out参数可以写常数0啊，因为
你用的pymysql,人家会帮你搞定，pymysql其实会帮你写成这样：第一个参数变量名：@_p3_0=3，第二
个：@_p3_1=0，也就是pymysql会自动帮你对应上一个变量名，pymysql只是想让你写的时候更方便
#沿着网络将存储过程名和参数发给了mysql服务端，比咱们发一堆的sql语句肯定要快对了，mysql帮你调
用存储过程
print(cursor.fetchall()) #查询select的查询结果

cursor.execute('select @_p3_0,@_p3_1;') #@_p3_0代表第一个参数，@_p3_1代表第二个参数，
即返回值
print(cursor.fetchall())
#别忘了关掉：
cursor.close()
conn.close()

　　删除存储过程

　　drop procedure proc_name;