python（11-4 数据库的事务 触发器 存储过程 慢查询及sql优化）

MySQL的事务处理

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

• 1、 事务的原子性： 一组事务，要么成功；要么撤回。
• 2、 稳定性 ： 有非法数据（外键约束之类），事务撤回。
• 3、 隔离性： 事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
• 4、 可靠性： 软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit 选项 决定什么时候吧事务保存到日志里。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

开启事务
BEGIN;
执行sql...

成功执行 事务提交
commit

失败执行 事务回滚
rollback

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

1. set autocommit = 0;
2. 增删改语句
3. savepoint aaa;
4. 继续执行语句
5. savepoint bbb;
6. rollback to aaa; 回滚到aaa;
7. commit
MySQL的触发器
格式：1、触发器的定义：
 CREATE TRIGGER trigger_name trigger_time trigger_event
   ON tbl_name FOR EACH ROW trigger_stmt

  说明：
  # trigger_name：触发器名称
  # trigger_time:触发时间，可取值：BEFORE或AFTER
  # trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE。
  # tb1_name：指定在哪个表上
  # trigger_stmt：触发处理SQL语句。

示例：
    mysql> delimiter $$
    mysql> create trigger del_stu before delete on stu for each row
        -> begin
        ->  insert into stu_bak values(old.id,old.name,old.sex,old.age,old.addtime);
        -> end;
        -> $$
    Query OK, 0 rows affected (0.05 sec)

    mysql> delimiter ;
MySQL存储过程
 
 

  
  
Mysql储存过程是一组为了完成特定功能的SQL语句集，经过编译之后存储在数据库中，
  
  
当需要使用该组SQL语句时用户只需要通过指定储存过程的名字并给定参数就可以调用执行它了，
  
  
简而言之就是一组已经写好的命令，需要使用的时候拿出来用就可以了
 
 
创建存储
\d //(修改语句结束符号)
create procedure p1()
begin
set @i=0;
while @i<10 do
insert into user(name) values(concat(‘user’,@i))；
set @i＝@i+1;
end while;
end;
//
执行储存：call p1
查看存储状态信息：show procedure status;
查看存储具体信息：show create procedure p1\G
SQL优化
  
  

   
   
优化sql步骤：
   
   
慢查询日志

找出执行慢的sql语句

进行具体语句优化或建立索引

  
  
有关慢查询操作：
-- 查看有关mysql数据库服务器的一些信息
show [session|global] status;
     session:当前连接
     global：数据库服务器启动之后
show global status;

show status like 'Com_%'  一般查看以com开头的

// 根据执行的sql语句（次数）
Com_select:查询
Com_update:修改次数
Com_insert:插入次数
Com_delete:删除的次数

// 此处是影响的行数（比如查询一次显示20行，那么行数增加20行）
InnoDB_rows_read:执行select操作的次数 
InnoDB_rows_updated:执行update的次数
InnoDB_rows_inserted:执行insert操作的次数
InnoDB_rows_deleted:执行delete操作的次数

connections：连接mysql的数量
Uptime:服务器已经工作的秒数

Slow_queries:慢查询的次数




-- // 查看“慢查询”的配置信息
show variables like "%slow%"; 
 -- // 查看“慢查询”的时间定义
show variables like "long%";  

 -- //设置“慢查询”的时间定义
set long_query_time=0.2;   
-- //开启慢日志
set global slow_query_log='ON';





mysql> show variables like '%slow%';
+---------------------------+--------------------------------------------+
| Variable_name             | Value                                      |
+---------------------------+--------------------------------------------+
| log_slow_admin_statements | OFF                                        |
| log_slow_slave_statements | OFF                                        |
| slow_launch_time          | 2                                          |
| slow_query_log            | OFF                                        |
| slow_query_log_file       | /var/lib/mysql/yc-virtual-machine-slow.log |
+---------------------------+--------------------------------------------+


mysql> show variables like "long%";  
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
定位执行效率较低的SQL语句
1. explain select * from table where id=1000;
2. desc select * from table where id=1000;
 *************************** 1. row ***************************
        id: 1
select_type: SIMPLE(简单查询，不是多表查询和复杂查询) 可能的值：simple,primary,union,dependent union,union result
     table: php114（表名）
      type: ALL（）     可能的值：system,const(最优化，使用了主键或者唯一健),eq_ref,ref.ref_or_null,index_merge
possible_keys: NULL         提示使用哪个索引会在该表中找到行
       key: NULL（）     mysql使用的索引，简单且重要
   key_len: NULL（）     mysql使用的索引长度
       ref: NULL          显示使用哪个列或者常数与key一起从表中选择行
      rows: 6          mysql执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引
     Extra:          包含mysql解决查询的详细信息
1 row in set (0.00 sec)
SQL语句优化
  
  
表结构优化
SQL语句优化 复杂查询简单化,简单查询少量化
索引的优化 适当添加索引,并使用索引,但是请注意索引过多导致的MySQL压力过大
http://blog.csdn.net/hzy38324/article/details/44903207    慢查询
http://blog.csdn.net/zhangwj0101/article/details/50945379   事务理解