深入探索MySQL---事务，SQL优化，索引，存储引擎

最新推荐文章于 2024-09-02 14:48:18 发布

szy2333

最新推荐文章于 2024-09-02 14:48:18 发布

阅读量188

点赞数

文章标签： mysql java sql 数据库

本文链接：https://blog.csdn.net/szy2333/article/details/82315749

版权

事务

就是把多条sql语句看做一个整体来执行，是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作系列组成，有四大特性ACID（原子性，一致性，隔离性，持久性）

原子性-Atomicity：指事务所包含的所有操作要么全部成功应用到数据库，要么失败回滚不会对数据库产生任何影响

一致性-Consistency:事务要保证数据库整体数据的完整性和业务的数据的一致性，事务成功提交整体数据修改，事务错误则回滚到数据回到原来的状态

隔离性-Isolation：多个用户并发访问数据时，如操作同一张表，数据库为一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。多个事务操作一个对象会以串行等待的方式保证事务之间的相互隔离。

持久性-Durability：一旦事务提交，那么对数据库中的数据的改变就是永久性的，不会因为异常，宕机造成数据错误或丢失

将事务手动提交(原子性)

有三种主要方法：Connection conn = ...

1.conn.setAutoCommit(布尔值); 自动提交，一般默认值为true，也就是默认自动提交，即执行一条语句，jdbc会偷偷帮我们添加一个setAutoCommit(true);让事务提交更改生效。但是我们可以将参数值改为false，那么事务就会变为手动提交，后续的多天SQL语句就会视为一个事务

2.conn.commit(); 上面变为手动提交之后，那么在多条SQL语句之后使用该方法，就可以使更改生效

3.conn.rollback(); 手动回滚事务，即如果有一条sql语句出现问题，那么将语句回滚，即使有SQL语句有成功也撤销，一般使用try--catch语句块

通过一个例子进行试验

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

public class CommitTest1 {
    /**
     * 事务
     * 把多条sql语句看做一个整体执行，ACID(原子性，一致性，隔离性，持久性)
     * 一般情况下，conn.setAutoCommit(true)中的true是默认值，为自动提交
     * 所谓的自动提交，就是在每条执行的增删改sql语句之后，由jdbc自动增加一条commit语句，让事务提交(更改生效)
     * 当将其中的true变为false，含义就是让事务手动提交，后续的多条sql就视为一个事务
     */

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = Utils.getConnection();
            //让事务手动提交
            conn.setAutoCommit(false);
            stmt = conn.prepareStatement("delete from student where sid = ?");
            stmt.setInt(1, 1021);
            stmt.executeUpdate();

            stmt.setInt(1, 1026);
            stmt.executeUpdate();
            //int i = 10/0;

            //所有操作都成功了之后，再commit
            conn.commit();

        } catch (Exception e) {
            e.printStackTrace();
            if(conn != null) {
                try{
                    //一旦有异常，回滚之前的操作
                    conn.rollback();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        } finally {
            Utils.close(stmt, conn);
        }
    }
}

当出现int i = 10/0的时候，即遇到错误时，事务是不会提交的，并会出现异常

将那一行注释掉之后，事务没有遇到错误，于是提交，下面两图分别操作前数据库和操作成功后数据库

事务的并发性（不隔离会造成的问题）

1.脏读：事务A读取了事务B的数据，然后事务B进行了回滚操作，那么A读到的数据就是脏数据

2.不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次多次读取同一数据时，结果不一致

3.幻读：系统管理员A将数据库中所有的学生数据改为ABCD四个等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就像发生了幻觉一样，这就叫做幻读

MySQL事务的隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommited）	是	是	是
读提交（read-commited）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

注意：

1.事务隔离级别为读提交时，写数据只会锁住相应的行

2.事务隔离级别为可重复度时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读

3.事务隔离级别为串行化时，读写数据都会锁住整张表

4.隔离级别越高，越能保证数据的完整性和一致性，但是同时对并发性能的影响也大

关于隔离级别的详细讲解可以参考转载的这篇博文https://www.cnblogs.com/huanongying/p/7021555.html

MySQL中的锁

mysql中的锁多种多样，并且是基于索引实现的，当我们的SQL命中索引时，就是行锁，没有命中，就是表锁

便于理解，将锁分为了这几类

锁的属性	共享锁、排他锁
锁的粒度	表锁、行锁、记录锁、间隙锁、临键锁
锁的状态	意向共享锁、意向排它锁

1.属性锁

共享锁（Share Lock）

简称S锁，又被称为读锁。当事务为数据加上读锁之后，其他事务也只能加读锁，只有等所有读锁释放之后才可以对数据加写锁。这样保证了在读取数据的时候不会被修改，避免了重复读的问题。

排他锁（eXclusive Lock）

简称X锁，又称为写锁。当事务为数据加上写锁时，在锁释放之前，其他事务不能再为数据加任何锁。这样保证了在修改数据的时候不被他人修改和读取，避免了脏数据和脏读。

2.粒度锁

表锁

锁住整张表，其他事务想要访问表必须等锁释放，加锁容易但效率低下。

行锁

锁住某一条或者某几条数据，其他事务访问时，只能被锁住的数据不能访问，其他可以正常访问。并发高但加锁麻烦。

记录锁（属于行锁，排它锁）

select * from table where id = 1 for update

该语句会在id=1的记录上加上记录锁，防止其他事务对这条数据进行插入更新删除

间隙锁（属于行锁，排它锁）

假设id为1、4、8、12是有数据的，执行语句

select * from table where id between 4 and 9 for update

那么遵循左开右闭的原则，(4,8] 区间和（8，11] 区间都不可插入数据，而其他区间都可以
如果锁住不存在的数据，如下

select * from table where id = 2 for update

那么id = 2 所在的区间(1,4] 会产生间隙，那么这个区间会加上间隙锁

结论：

对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁
对于查找某一范围内的查询语句，会产生间隙锁

临建锁：

其实现与间隙锁相差不大，区别在于临键锁只与非唯一索引有关，在唯一索引列（包括主键列）上不存在临键锁。

3.状态锁

意向共享锁

当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁。

意向排它锁

当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它锁。

关于数据库中的锁，详细内容可以参考这篇博文：https://zhuanlan.zhihu.com/c_1046719595074949120

SQL优化的一些方法（尽量避免的操作）

1.应尽量避免扫描全表，而是考虑在where或者order by涉及的建立索引

2.尽量避免在where子句中对字段进行null值判断，否则会导致引擎放弃使用索引而使用全表扫描，可以尽量设置属性为not null，或者默认值为0，确保表中没有null值

3.尽量避免where子句中使用or来连接条件，这样会导致全表扫描

4.in 和 not in也要慎用，能用between...and就不要使用in了

用between...and替换

或者使用exists代替也可以

替换后的语句

5.使用模糊查询 ".....like ' %abc ' " 也会导致全表查询

6.应尽量避免在where子句中对字段进行表达式操作，会导致引擎放弃使用索引而进行全表扫描

应改为

7.尽量避免在where子句中对字段进行函数或者运算操作，这将导致引擎放弃索引而进行全表扫描

8.索引不是越多越好，虽然通过索引能提高响应的select的效率，但同时也降低了insert和update的效率，因为进行增删改操作有可能会重建索引，所以怎样建立索引应该视情况而定

9.尽量使用varchar代替char，因为变长字段储存空间小，可以节省存储空间，其次对于查询来说，在相对较小的字段内搜索效率更高一些

10.查询时，尽量不要使用*，而是用具体的字段，不要返回用不到的任何字段

11.尽量避免大事务操作，提高系统高并发能力

批处理

在进行大量的数据时，会很耗费时间，先给一个简单的例子，插入500条数据，用普通的循环进行，看用了多长时间

public static void main(String[] args) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = Utils.getConnection();
            long startTime = System.currentTimeMillis();
            stmt = conn.prepareStatement("insert into student (sname, sex) values(?, ?)");
            for(int i = 0; i < 500; i++) {
                 stmt.setString(1, "曹操");
                 stmt.setString(2, "男");
                 stmt.executeUpdate();
            }
            long endTime = System.currentTimeMillis();
            long time = endTime - startTime;
            System.out.println("运行时间为: " + time);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Utils.close(stmt, conn);
        }
    }

运行结果：

在程序结束之后，记录运行的时间，我们发现仅仅插入500条相同的语句，就花费了43秒左右，十分耗时，为什么如此耗时呢？因为jdbc代码不断发送给数据库，每执行一次就会进行一次交互，进行一次网路通信的传输。所以如果我们能把这500条数据当做一个整体的数据包来执行，那么就只需要和数据库进行一次交互，大大提高了执行效率，这其实就是批处理的原理，即减少与数据库之间的交互

代码演示如何进行批处理

public class BatchJDBCTest {
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = Utils.getConnection();
            long startTime = System.currentTimeMillis();
            stmt = conn.prepareStatement("insert into student (sname, sex) values(?, ?)");
            for(int i = 0; i < 500; i++) {
                 stmt.setString(1, "曹操");
                 stmt.setString(2, "男");
                 //stmt.executeUpdate();  //调用一次，就和服务器交互一次,抛弃

                 stmt.addBatch();  //将insert语句加入批处理包
            }

            stmt.executeBatch();   //将批处理包中的数据一次性发送给数据库

            long endTime = System.currentTimeMillis();
            long time = endTime - startTime;
            System.out.println("运行时间为: " + time);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Utils.close(stmt, conn);
        }
    }
}

测试结果：如下图，只用了0.3秒左右

要注意，要使用批处理，就需要在URL中加入 "rewriteBatchedStatements=true"这句话

但是是不是与数据库交互次数越少就一定越好呢？答案是不是的。加入有一百万条数据，那么将1000000条数据都放在数据包中，那么就会十分的占用内存！也就是用空间换取时间。所以我们想要寻找一种空间时间都优的方法，其实并不难：我们只需要将这些数据分成小数据包，比如10000000多插入语句，我们将500次进行一次数据包打包，那么这样就既节省空间有节省时间了

补充一点：批处理只是对于增删改能提升效率，对于查询是没有用的

抓包

抓包大小与查询有关

MySQL查询数据是用ResultSet包装多少，查多少ResultSet就返回多少，对于查询数据，假设一次性在一张大表中查询一百万条数据，那么有可能内存会被撑爆，而且效率会受影响。但是oracle就比较好，我们看看它是怎么样解决这个问题的

oracle并不是一次性返回ResultSet。而是建立与数据库表之间的联系(游标技术)，在第一次调用rs.next()方法才会从数据库中调用并返回rs，而且并不是一次性全部返回到ResultSet，俄日是通过fetchSize，即抓取大小。加入我们将fetchSize记为10，那么无论数据表中的数据有多少，我们每次只查询不超过fetchSize的大小的数量并返回。。。虽然增加了与服务器之间的交互，但是节省了内存压力。

而MySQL中其实也有这样的方案，只是需要我们通过添加"userCursorFetch=true&defaultFetchSize=50"来启动

关于测试，大家可以查询一百万条记录，然后查询内存占用，再用抓包后继续查询内存占用，进行比较

从数据库角度考虑查询效率提升

索引技术---使用索引进行加速查询。

原理：在Java中一般用排序后进行二分查找，而在数据库中，我们使用的和这个类似，只不过查询后的结果不是一维结果，而是平衡树的结果，然后根据排序后的结果进行查找

对于查询，建立索引： create index 索引名字 on 表名(列明)；

比如，我们利用刚刚批处理的方式建立一张有一千万个数据的表，然后分别通过普通查找和索引查找进行使用时间的观察

注意：

1.索引并不是随便乱建的，而是针对经常查询的列，否则出了浪费空间并没有太大意义，而且索引虽然提高了查询效率，但是降低了增删改的效率，所以要根据情况选择是否用索引

2.而且主链列也会主动建立索引

3.外键列建立索引有助于表连接的效率

4.查询列如果应用了函数，则不会利用索引

5.模糊查询如果是%开头则不会走索引

数据库连接池

预先创建一些数据库连接，用的时候从连接池借，每次使用完连接，要还回连接池，而不是真正关闭

连接池的接口实现： javax.sql.DataSource;

DataSource.getConnection(); --->从连接池获取一个空闲的连接 --->迟连

上面我们用过的DriverManager.getConnection() --->直接与数据库进行新的连接--->直连

如何实现连接池：

服务器(如Tomcat，weblogic，websphere)都内置了连接池

也可以用第三方的独立连接池实现(如apache bdcp，c3p0, alibaba druid)

szy2333

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
深入探索MySQL---事务，SQL优化，索引，存储引擎

事务就是把多条sql语句看做一个整体来执行，是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作系列组成，有四大特性ACID（原子性，一致性，隔离性，持久性）原子性-Atomicity：指事务所包含的所有操作要么全部成功应用到数据库，要么失败回滚不会对数据库产生任何影响一致性-Consistency:一个事务执行之前和执行之后必须处于一致性状态(比如A,B一共有5000元，...
复制链接

扫一扫