事物处理、存储过程和索引

事务处理    

事务处理是一种机制，用来管理必须成批执行的SQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行。如果没有错误发生，整组语句提交给数据数据表。如果发生错误，则进行回退，以恢复数据库到某个已知且安全的状态。——MySQL必知必会

关于事务需要知道的几个术语：

    事务（transacation）指一组SQL语句;

    回退（rollback）指撤销指定SQL语句的过程;

    提交（commit）指将未存储的SQL语句结果写入数据库表中;

    保留点（savepoint）指事务处理中设置的临时占位符（placeholder),你可以对它发布回退（与回退整个事务处理不同）。

    事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退。

    哪些语句可以回退？INSERT, UPDATE, 和DELETE语名。你不可能回退SELECT语名，因为这样做没有意义。也不能回退CREAT或DROP操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。

    为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样，如果需要回退，可以回退到某个占位符。这些占位符称为保留点。为了创建占位符，这样使用SAVEPOINT语句：savepoint xxx; 每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要退回到何处。如：rollback to xxx;保留点越多，你就越能按自己的意愿灵活地进行回退。

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('miao',1000),
('monicx',1000),
('heben',1000);

start transaction;
update user set balance=900 where name='maio'; 
update user set balance=1010 where name='monicx'; 
update user set balance=1090 where name='heben';

存储过程

存储过程包含了一系列可执行的SQL语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆SQL.

以teacher表为例：

在MySQL创建存储过程

delimiter $$
create procedure p1(
    in m int,
    in n int,
    out res int
)
begin
    select tname from teacher where tid > m and tid < n;
    set res=0;
end $$
delimiter ;

如何用存储过程
1、直接在mysql中调用

set @res=10,
@m=2,
@n=4;
call p1(@m,@n,@res);

或是在python中使用：

import pymysql
conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    charset='utf8',
    database='db5'
)
cursor=conn.cursor(pymysql.cursors.DictCursor)
cursor.callproc('p1',(2,4,10)) #@_p5_2=10
print(cursor.fetchall())
cursor.execute('select @_p1_2;')
print(cursor.fetchone())

cursor.close()
conn.close()

存储过程在事务中的使用：

以user表为例在MySQL创建事务的存储过程：

delimiter //
create PROCEDURE p5(
    OUT p_return_code tinyint
)
BEGIN
    DECLARE exit handler for sqlexception
    BEGIN
        -- ERROR
        set p_return_code = 1;
        rollback;
    END;

    DECLARE exit handler for sqlwarning
    BEGIN
        -- WARNING
        set p_return_code = 2;
        rollback;
    END;

    START TRANSACTION;
        update user set balance=900 where id =1;
        update user123 set balance=1010 where id = 2;
        update user set balance=1090 where id =3;
    COMMIT;

    -- SUCCESS
    set p_return_code = 0; #0代表执行成功

END //
delimiter ;

在Python中的使用：

import pymysql

conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    charset='utf8',
    database='day42'
)

cursor=conn.cursor(pymysql.cursors.DictCursor)
cursor.callproc('p5',(122,)) #@_p5_0=122
print(cursor.fetchall())
cursor.execute('select @_p5_0;')
print(cursor.fetchone())

cursor.close()
conn.close()

索引

1、为什么要用索引
    对于一个应用来说，对数据库的读写比例基本上是10:1,即读多写少。
    而且对于写来说极少出现性能问题，大多数性能问题都是慢查询。
    提到加速查，就必须用到索引。
2、什么是索引
    索引就相当于书的目录，是mysql中一种专门的数据结构，称为key，
    索引的本质原理就是通过不断地缩小查询范围，来降低IO次数从而提升查询性能
    强调：一旦为表创建了索引，以后的查询都会先查索引，再根据索引定位的结果去找数据
3 、索引的影响
    1、在表中有大量数据的前提下，创建索引速度会很慢，
    2、在索引创建完毕后，对表的查询性能会大幅度提升，但是写性能会降低。

聚集索引（primary key）
  特点：叶子节点存放的一整条数据
辅助索引（unique，index）
  特点：如果是按照这个字段创建的索引，那么叶子节点存放的是：{名字：名字所在那条记录的主键的值}
覆盖索引：只在辅助索引的叶子节点中就已经找到了所有我们想要的数据