栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

MySQL的索引和事务

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

MySQL的索引和事务

目录

1. 索引

索引背后的数据结构

MySQL的索引实现基于B+树的设计(N叉搜索树)

首先了解B-树(B树)

B+树

聚簇索引

非聚簇索引-二级索引

存储引擎

2. 事务

事务的应用场景-银行转账的场景

事务的ACID特性

原子性

持久性

隔离性

一致性 

事务的三个关键字

MySQL事务的四种隔离级别

3. JDBC

配置MySQL的IP和端口号

JDBC相关代码


1. 索引

索引是一种特殊的文件,包含数据库中所有记录的引用。

类似于数组的引用下标,通过这个下标就能拿到数组中的数据。和数组中下标一样,通过下标寻找数组数据很快,通过索引去查找数据库中的数据也很快。经常查询的属性设置为索引。


为何需要索引?——查多改少

eg:索引和原数据表的关系,就相当于书的目录和书本的内容之间的关系,根据书本目录去查找特定内容,远比你拿一本书直接翻快的多。但是涉及到书本内容的修改、删除,还需要改目录。

索引提高查询数据的效率,但是会拖慢增,删,改的效率,创建索引也需要时间和空间的开销。

在test_user表中为id_number属性创建索引 id_idx

create index id_idx on test_user(id_number);

查询 id_number 为778899的用户信息

select * from test_user where id_number=556677;


为提供查询速度,创建 id_number 字段的索引

create index idx_test_user_id_number on test_user(id_number);

换一个身份证号查询,并比较执行时间

select * from test_user where id_number=776655;

在很少的常数的间之内就可以查询到数据。

索引背后的数据结构

高效查找数据的数据结构

BST:二分搜索树——>RBTree,查找的时间复杂度为logN。

当数据量非常大时,由于二叉树每个节点最多有两个子树,RBTree的高度很高,数据库的数据都在磁盘上存储,每当访问一个二叉树的节点都得读写一次磁盘,这样就非常慢。

哈希表:理论上O(1)找到数据,常数时间。
哈希表是查找数据是否等于指定数据,区间查找就不行了,无法处理范围查找查找。

MySQL的索引实现基于B+树的设计(N叉搜索树)

B树家族是严格平衡树,左右子树的高度差为0,左树和右树高度完全相等。

首先了解B-树(B树)

引入N叉搜索树后可以大大降低树的高度(相较于BST来说)

B-树的特点
1. 每个树节点上都有多个值。

2. 每个节点上的(分叉树)子树的个数,就是当前节点值的个数+1。

3. 保证子树中的节点值一定要处在父节点的范围之内。

B+树

特点:

1. B+树中,子节点中存在的最大值(最小值)是在父节已经出现过的值。这样做的目的就是最底层的叶子节点包含了整个数据的全集。

2. B+树中,最底层的叶子节点使用链表连接。更高效率的区间查找。
3. 对于B+树来说,所有的数据都存储在叶子节点,非叶子节点只需要保存索引列的辅助信息(索引列的值)。非叶子节点占用空间小,可以直接放在内存中。减少磁盘l0。

聚簇索引

聚簇索引(主键索引,一个表只有一个聚簇索引)。

构建索引树上的每个节点,除了要保存索引列的信息,还需要保存这条记录的完整内容。

特点:查询速度快,一个表只有一个聚簇索引,保存的信息多,占用空间大。

select name from stu where id = 300;
根据聚簇索引找到id= 300的节点,就能拿到所有的列信息。只需要在聚簇索引树上查找即可。

非聚簇索引-二级索引

非聚簇索引(普通索引,唯一索引,index创建的都是非聚簇索引,一张表可以有多个非聚簇索引)。

索引树上的每个节点,除了保存索引列的信息之外,还需要保存该记录的行号(对应的主键id)。

select name from stu where k = 3;
根据非聚簇索引先在索引树上找到k =3的这条记录,然后这个节点内部保存了k = 3对应的主键id为300。还需要返回在聚簇索引树中找到id = 300的记录,才能拿到完整的值——回表查询。

特点:非聚簇索引相较于聚簇索引有更多的查表操作——更多的读写磁盘操作。

查询速度慢(只能找到主键信息,需要回表查询),一个表可以有多个非聚簇索引,保存的信息少,占用空间小。

存储引擎

MySQL数据库插件式的设计,每种索引在不同的存储引擎中的实现都有可能不同。

存储引擎:就是MySQL到底如何对数据进行增删查改的不同实现方案。
MySQL有多个存储引擎,如同一种汽车品牌可能有多种车型。


MySQL中最经典的两种存储引擎
MylSAM:MySQL5.5之前的默认存储引擎,不支持事务,性能比较高。
InnoDB:MySQL5.5之后的默认存储引擎,支持事务,性能不如MyISAM。

show variables like 'default_storage_engine';

2. 事务

所谓的事务:把若干个SQL操作打包为一个整体,实际执行的时候,这个整体要么全部执行,要么都不执行。

若执行的过程中出现了突发情况,某些操作执行不下去了,MySQL可以保证突发情况恢复之后,数据没有遭到破坏。

通过事务的"回滚"roll back操作,进行数据的还原(MySQL中的binlog文件实现,记录了所有表数据的修改动作)。

事务的应用场景-银行转账的场景

A向B转账 100$

update account set banlance = banlance - 100$ where name = 'A';
update account set banlance = banlance + 100$ where name = 'B';

转账之后很行系统崩溃,A的钱扣了,但是B没收到,没给B的账户转账。

此时需要事务,实际执行的时候,这个整体要么全部执行,要么都不执行。

事务的ACID特性

原子性atomicity,持久性isolation,隔离性durability,一致性consistency

原子性

原子性:事务中最核心的操作
一个事务中的所有操作,要么全部执行成功,要么全部执行失败。(执行失败之后,数据的恢复就通过rollback回滚)。

持久性

持久性:一个事务执行完成之后,这个事务对数据库的所有修改都有永久的(持久化,保存到磁盘上),不会丢失。

隔离性

隔离性:多个并发事务访问数据库时,事务之间是相互隔离的,一个事务不应该被其他事务干扰,不同事务之间相互隔离。

普通SQL:一个SQL中执行的操作,MySQL自身可以保证数据并发时的正确性(通过读写锁),若是事务,并发执行就会存在相应的问题。


并发:多个任务不一定是在同时执行的,也不一定就是在不同的CPU上执行。以前的单核处理器上的任务调度都属于并发执行。

例如:

​

所有任务并发执行,在不同的时间段执行不同的任务。

此时CPU的任务调度顺序为1234,让用户感觉好像是这两个程序,任务在同时进行其实在不同时间段执行了不同的任务。


并行:多个任务一定是在不同的CPU上同时执行
问题1:脏读

事务A在修改数据,事务B读取到了事务A修改后的数据,此时事务A进行了"回滚",前面的修改不做数了,事务B读到的数据就是"脏"数据,这种情况称为脏读。

问题2:不可重复读

同一个事务在多次相同查询后得到的数据不同。

在不同的查询时,其他事务的修改对于本事务来说是可见的。

例如:
事务A: select name from stu;

//其他操作...

再次select name from stu;

问题3:幻读

每次查询得到的值都是相同的,其他事务的修改对于本事务不可见,明明事务中查询不到重复的信息,但是就是插入不进,称为幻读。

 

一致性 

一致性:一个事务执行前后的数据都是一种合法性的状态,事务永远都是从一个一致性状态到另一个一致性状态。在执行事务前后,数据库的数据都是合理的,

假设执行更新操作之前,A账户余额1,B账户余额300$,B向A转账100$。

没有事务,就有可能出现:A账户余额1,B账户余额200$。数据不合理,二者的总余额应相等。

事务的三个关键字

开启事务,start transaction,后面的多个sql语句是一个整体。

回滚操作,rollback回滚了上次对数据库做的修改。
提交事务,就是把开启事务之后的所有SQL语句统一在数据库上进行持久化。

MySQL事务的四种隔离级别

1. 读未提交

处在该隔离级别的事务可以看到其他还没提交事务对数据库的修改——RU

造成脏读,不可重复读,环读。

2. 读已提交——Oracle数据库默认

处在该隔离级别的事务可以看到其他已经提交事务对数据库的修改~~RC的隔离级别
3.可重复读——lnnoDB引擎默认的隔离级别
一个事务一旦开启,在该隔离级别下,该事务提交之前,多次查询看到的结果是相同的。

无论其他事务如何修改数据库,在当前这个事务下都是不可见的。

4. 串行化

事务最高隔离级别,所有事务都串行访问数据库。不会发生冲突,不会产生任何事务的问题,但就没有并发执行了。


​

事务的隔离级别越高,安全性越强,并发性就越低。

练习:求不同隔离级别下v1,v2,v3的值

​

3. JDBC

Java操作数据库的规范

JDBC为多种关系数据库提供了统一访问方式,作为特定厂商数据库访问API的一种高级抽象,它主要包含一些通用的接口类。

java.sql:无论现在通过Java操作哪个具体的数据库,数据库厂商实现的驱动包都需要满足JDBC的标准(接口)。

​

​

不管是什么数据库,MySQL,SQLLite,Oracle都得实现JDBC的接口。
对于程序员来说,不管操作什么数据库都是相同的套路,只是更换了具体子类(驱动)。

​

*.jar就是把一系列编译好的class文件,打包压缩后产生的文件,其他程序引入这个jar包就有了这个包中的所有类,等同于别人写好的东西直接拿来用。

配置MySQL的IP和端口号

MySQL的驱动包背后都是一些网络请求,操作数据库其实就是在发起网络请求

JDBC相关代码

 JDBC插入操作

package jdbc;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

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


public class JDBCTest {
    public static void main(String[] args) throws SQLException {
        // 1.先获取数据源 - 数据是从哪个数据库来的
        MysqlDataSource dataSource = new MysqlDataSource();
        // 2.配置数据源的属性,用户名,密码,连接的IP地址,端口号
        dataSource.setURL("jdbc:mysql://127.0.0.1:3306/my_class_ds?CharacterEncoding=utf8&useSSL=false");
        // -u root
        dataSource.setUser("root");
        // -p 123456
        dataSource.setPassword("123456");
        // 3.获取数据库连接 - 根据上面的配置发起网络请求
        // java.sql.Connection 连接对象
        Connection connection = dataSource.getConnection();
        // 4.拼装SQL语句,就是String 对象
        String sql = "insert into user(username,password) values(?,?)";
        // 5.获取执行SQL的Statement对象,mysql中每一个sql的执行就对应一个Statement对象
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, "小明");
        statement.setString(2, "123");
        // 6.执行这个SQL,增删改调用executeUpdate,返回受影响的行数
        int rows = statement.executeUpdate();
        System.out.println("插入成功,插入" + rows + "行");
        // 7.关闭资源
        connection.close();
    }
}

查询操作

package jdbc;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class JDBCTest1 {
    public static void main(String[] args) throws SQLException {
        // 1.获取数据源
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setURL("jdbc:mysql://127.0.0.1:3306/my_class_ds?characterEncoding=utf8&useSSL=false");
        dataSource.setUser("root");
        dataSource.setPassword("123456");
        // 2.获取连接对象,发起请求获取MySQL连接
        Connection connection = dataSource.getConnection();
        // 3.获取Statement对象,就是具体执行SQL的对象
        String sql = "select * from user where username = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, "小明");
        // 4.执行SQL取得返回值,返回值存在ResultSet对象中
        ResultSet resultSet = statement.executeQuery();
        // 5.遍历结果集
        String pass = null;
        while (resultSet.next()) {
            // 获取查询结果集中列名为id的属性值,数据库的列是什么类型,咱就用Java中对应的类型来接收
            int id = resultSet.getInt("id");
            // 查询结果集中属性名为username的属性值
            String userName = resultSet.getString("username");
            // 查询结果集中属性名为password的属性值
            String password = resultSet.getString("password");
            System.out.println("id = " + id + ",userName = " + userName + ",password = " + password);
            // 获取查询结果中列名为password的值
//            pass = resultSet.getString("password");
        }
//        if ("111111".equals(pass)) {
//            System.out.println("登录成功");
//        }else {
//            System.out.println("密码错误,登录失败");
//        }
        // 6.关闭资源
        resultSet.close();
        connection.close();
    }

}

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/967349.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号