mysql必知必会（六）

这章介绍数据库的事务和索引。

1.事务

• 在mysql中只有使用了InnoDB的数据库引擎的数据库或表才支持事务
• 事务处理可以用来维护数据库的完整性，保证成批的mysql操作要么完全执行，要么完全不执行
• 事务用来管理insert、update、delete语句
  事务的术语：
• 事务（transaction） 指一组sql语句
• 回滚（rollback）指撤销指定sql语句的过程
• 提交（commit）指将未存储的sql语句结果写入数据库中
• 保留点（savepoint） 指事务中设置的临时占位符，可以对它进行回滚
  回滚事务：

start transaction;
insert into user(name,password)values('tom','123asd');
rollback;

start transaction;表示开始事务。
提交事务

start transaction;
insert into user(name,password)values('tom','123asd');
commit;

保留点

start transaction;
insert into user(name,password)values('jack','32dsaw');
savepoint s1;
insert into user(name,password)values('rose','545swc');
rollback to s1;

在java的使用：

package com.xinguo.pm.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class TransactionTest {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db","root","root");

        //开启事务
        conn.setAutoCommit(false);
        PreparedStatement ps = null;

        try {
            String sql1 = "insert into user(name.password) values(?,?)";
            ps = conn.prepareStatement(sql1);
            ps.setString(1, "jack");
            ps.setString(2,"123456");

            ps.executeUpdate();

            String sql2 = "insert into user(name.password) values(?,?)";
            ps = conn.prepareStatement(sql2);
            ps.setString(1, "rose");
            ps.setString(2,"234567");

            ps.executeUpdate();

            conn.commit();//提交事务
        } catch (Exception e) {
            conn.rollback();//回滚事务
        }

    }
}

开始时设置为不自动提交：conn.setAutoCommit(false);

回滚到保留点：

package com.xinguo.pm.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Savepoint;

public class TransactionTest {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db","root","root");

        //开启事务
        conn.setAutoCommit(false);
        PreparedStatement ps = null;
        Savepoint st = null;
        try {
            String sql1 = "insert into user(name.password) values(?,?)";
            ps = conn.prepareStatement(sql1);
            ps.setString(1, "jack");
            ps.setString(2,"123456");

            ps.executeUpdate();

            st = conn.setSavepoint();//设置一个保留点

            String sql2 = "insert into user(name.password) values(?,?)";
            ps = conn.prepareStatement(sql2);
            ps.setString(1, "rose");
            ps.setString(2,"234567");

            ps.executeUpdate();

            conn.rollback(st);//回滚到保留点
            conn.commit();//提交事务
        } catch (Exception e) {

        }

    }
}

事务的四个特征：

1. 原子性（atomic） 组成事务的处理语句组成一个逻辑单元，这是最小的执行单元
2. 一致性（consistent） 在事务处理执行之前和之后，数据是一致的
3. 隔离性（isolated） 一个事务的处理对另一个事务没有影响
4. 持续性（durable）当事务处理成功后，其结果在数据库中被永久的记录下来

2.索引

索引是优化数据库查询速度的重要途径。
但是会降低更新表的速度，比如对表的insert、update、delete操作，因为更新表
时，MySQL不仅仅要保存数据，还要保存索引文件。
并且建立索引会占用磁盘空间。如果在一个大表上创建了多种索引组合，索引文件会膨胀的很快。
创建索引

CREATE INDEX index1 ON user (name(20));

如果索引类型是varchar类型需要加上长度。