MYSQL中的索引与事务+JDBC

写个堆排

已于 2023-11-11 17:05:33 修改

阅读量2.4k

点赞数 2

文章标签：面试 java 数据库开发

于 2022-03-13 11:46:39 首次发布

本文链接：https://blog.csdn.net/weixin_61518137/article/details/123454062

版权

1.索引是啥？
2.索引要解决的问题
3.索引的应用场景
4.索引的数据结构
a) 为什么不用哈希表
b) 为什么不用二叉搜索树
c) 啥是B树，相较上面两个有什么优势(画图)
d) 啥是B+树，为什么用它来作索引的数据结构
5.更详细的说下索引的细节方面

6 事务是什么？解决的问题是什么？

7 事务的特性

8介绍并发执行

9 介绍 mysql的隔离级别

索引的主要的意义就是加快查找速率，查找速率提高了，但是同时也会付出一定的代价，书的目录也是废纸的，数据库中的索引，也是需要耗费一定的存储空间的，数据量越多，索引消耗的额外空间也会越多；书的目录如果确定了，后续对于书的内容调整，都可能会影响到目录的准确性，就需要重新调整目录，对书的内容进行修改，我们也是需要进行同步修改索引的，数据库中的索引也是一样，进行增删改查的时候，往往也需要进行同步的调整索引的结构，更多的增加了时间的浪费

好处：加快查找速率，大部分情况下是需要进行查询的

坏处：占用的更多的空间，拖慢了增删查的效率

虽然索引的坏处有很多，但是索引仍然是一个很常用的东西，在实际需求场景中，查找操作往往是最高频的操作，每一次比较都会涉及到硬盘上面的IO操作
有了索引之后，对于查找效率的提升是非常巨大的，当mysql中的数据量达到千万级别的时候
(一个表里面有几千万,上亿的数据)再去遍历表,就会变得非常的低效,在mysql中进行查找比较的时候，不是像下面这种的)
for(int i=0;i<1kw;i++)
{    
       if(arr1[i]==num)
{  break;
}
}
1)时间复杂度很高
2)这个是纯纯的在内存中进行查找,但是MYSQL中的比较是在硬盘上面的,硬盘上面的IO的速度比内存中的速度要慢上一个3-4数量级
这只能是慢上加慢
1)定义:索引是一种特殊的文件，包含着数据表中所有有记录的引用指针。可以对表中的一列或多列建立索引，就好像书的目录一样，它的存在就是加快数据库查询的；

注意:我们在这里说的查找，是按照值来进行查找，而不是根据下标进行查找，根据下标来进行查找，不叫查找，十分不希望说顺序表查找的时间复杂度是O(1)

2)索引背后考虑的数据结构:肯定要加快查找速度

1)顺序表:例如我想查找id是8的学生信息，如果没有索引，查找过程就相当于是顺序表查找，如果是针对顺序表查找，顺序表是在连续的内存空间中，内存支持随机访问操作，访问任意地址上面的数据，内存访问速度还行，而且数据也没那么多，速度还可以，访问任意地址上面的数据，速度都是极快的；

如果是针对数据库查找，数据库的数据可是在磁盘上，磁盘访问速及其的慢，而且数据量也可能很多；顺序表针对下标来进行访问，速度还是很快的，但是想要根据值来进行访问，速度还是很慢的

2)链表查的比顺序表都慢

3)AVL数(不让二叉搜索树变成单份支树，要求任意节点的高度差不超过1,增删改查更慢)

2)哈希表:O(1)

2.1)我们都知道，哈希表下的每一个节点都是一个链表连接起来的，比如要把一种数据类型放到哈希表中，是通过哈希函数得到一个下标，再根据下标取到对应的链表

2.2)但是哈希表只能处理相等的情况,对象等进行判定，不可以针对范围型数据进行查询，例如我们想查找学生id为6，到8的学生信息，哈希表就无能为力；

2.3)哈希表都不是按照大小规则来去排列顺序的
select * from student where id>6 and id <8

3)二叉搜索树：

相比于哈希表，二叉搜索树虽然可以处理范围查找，虽然可以提高访问速度，但是效率还是不太高：

1)二叉树的每个节点最多有两个叉，当数据量比较多的时候，树的高度就会比较高，高度就对应着比较次数，最终的操作效率，会很低，每一次进行比较的时候都意味着磁盘IO，高度很难进行控制

2)况且中序遍历效率也不是很高，时间复杂度(最坏的情况)为O(N)(最坏的情况，变成链表),不能阿波正查找速度很快；

3)二叉树的每个节点只存放一个数据，花费磁盘IO次数比较多;

4)堆也不行，只能查找最大值或者最小值

最终的数据结构:B+树，向多叉搜索树来进行解决；在整个数据量条数一定的情况下，N叉搜索树的高度一定比二叉搜索树小;

数据库有很多种，每一个数据库底层有支持都中存储引擎，实现了数据库具体按照啥样的结构来进行存储的程序，每一个存储引擎的存储的结构可能都不一样，背后的索引结构可能也不同

1)B树的每一个节点上面都会存储N个Key值，在第二层里面，小于30的分成一组，放在30的最左边，30-40之间的分成一组，40-50之间分成一组，50到60之间又分成了一组，在第三层里面，小于15的数据分到了一个节点，在15-16之间分成了一个节点，20-25之间又分成了一个节点，里面存放着21，22

2)N个key值就会存储N+1个区间，每一个区间都会对应一个子树，从B树中查找元素，这个就和二叉搜索树非常类似，先从根节点进行出发，根据带比较的元素，确定一个区间(不断划分区分)

3)B树每个节点存的数据的个数和节点的度是相关的存的个数+1=度

这里面的度指的是二叉树中某个结点的子节点或直接后继节点的个数，

再确定区间的时候，不也是在进行多次比较吗？这里比较和二叉搜索树相比有什么优势呢？

3)二叉搜索树，每一个节点比较一次，比较的次数是和高度息息相关的，但是对于B树来说高度是变小了，但是对于每一个节点来说，要进行比较多次，看似比较次数没有发生变化；

4)但是相比于比较次数来说，这不是关键的，IO次数是更关键的；

5)树是以节点为单位，进行磁盘的IO操作的，读一个磁盘花的时间比花上时间读内存几百次的时间都多

6)在二叉搜索树里面一个节点对应着一次IO操作，也就是说进行一次比较就对应着一次磁盘IO操作；但是在B树里面，一次IO操作得到一个节点，里面有多个数据，可以进行多次比较;对于我们的二叉搜索树和B书来说可能比较的次数没有发生变化，但是磁盘IO的操作是会大大进行减少的；因为访问磁盘比访问IO次数要慢个几千倍，几万倍，让访问磁盘IO的次数尽量少，提高效率,B树最大的意义就是有效的减少了磁盘IO的次数；

7)但是此时B树想要进行范围型查找，也是不太轻松的，例如在下面的图中，我想要查找1-18之间的数据，进行范围型查找就会变得很麻烦了；

1)但是对于B+树来说，他也是一个N叉搜索树，每一个节点上包含了多个key值，父亲结点的值都会在子节点上面进行出现，也就是说非叶子节点里面的值，最终都会在叶子节点里面体现出来；

2)父亲节点里面的值，都会作为子节点中的最大值或者最小值，最下边的叶子节点，都是通过链表来进行数据连接的；每一个节点如果说有N个Key，那么就是直接分成了N个区间

1)数据每一层都链接在一起；

2)数据只在叶子节点保存，非叶子节点只保存一些辅助查找的边界信息，查询任何一条的记录速度是比较平均的，不会出现效率差异大的情况；

3)使用B+树来进行查找的时候，整体使用的IO次数还是比较少的(树的高度是不会特别高的)

4)最终的查询都会落到叶子节点(没有子节点)上面使用,链表进行连接的时候，就非常适合范围型查找，直接截取链表

5)一个节点就对应着一次IO次数，所以说咱们的查询速度比较稳定(多次查询时间都是差不多的)

6)所有的数据载荷(每一条学生的记录)，都是放到叶子节点上面的，非叶子节点上面只需要保存key(学生id)值即可(它的存在只是为了加快非叶子节点的查找速率；
id name sex score
1  lijiawei 男  34
2   hhhh    女  47
此时id这一列就叫做索引,整个信息,存的就是载荷
这里面的key的值就是id的值

1)索引的缺点:是可以加快查找效率，但是对删除和插入，修改操作会减慢（需要同步调整索引结果），索引也会占用额外的空间；

2)索引的使用场景：数据量很大适用于查找很频繁，但是插入删除修改，都不频繁的场景；

3)索引的分类：给具体的表中加索引的时候，加载主键上的索引和加载其他列上的索引是完全不同的，主键索引的叶子节点是存放的数据的完整记录，而其他普通的叶子节点是存放的是主键的id

4)例如要按name查找，先通过索引查找到王五对应的主键id，再通过主键id去主键索引查找王五的信息

5)对于一个student的表来说(自动创建索引)，这个自带的索引，就是primary可以根据这个主键约束带来的，查询的时候如果查询条件中指定了根据主键进行查询，这个时候的查询速度就会变得非常快；unique也是自带索引的；

1)查找索引 show index from student

在创建表的时候，就应该考虑要把索引规划好

可以根据一张表中的某个列来创建索引，当我们去查看数据库中的一张表的时候，有时候可能就自带了一个索引，这个自带的索引，就是primary这个主键约束带来的

进行查询的时候，如果说查询条件指定了根据主键进行查询，那么这个时候查询速度就会变得非常快，unique也是自带索引的；

2)创建索引 create index 索引名 on 表名(列名)

create index lijiawei on student (name)（如果针对线上环境的数据库中的表没有索引，不要贸然的去创建索引，否则可能会挂)，我们此时就根据name这个列创建了索引

3)删除索引 drop index lijiawei on student;(索引名字和表名）

但是有一说一，创建索引是一个非常低效的事情，尤其是表中有很多数据的时候

4)创建表的时候，我们就应该把索引规划好，创建索引，删除索引这两个操作都有可能会失败，千万不要等这个表用了一段时间之后再去创建

事务诞生的目的就是为了把若干个独立的操作打包成一个整体

首先说一个转账问题

假设A给B转账2000元钱

A的账户余额 -2000

B的账户余额 +2000

但是我们假设 A的账户余额如果减少了2000，B的账户余额没有增加，A的钱减少了，但是B的钱没有增加，这2000元钱是不是凭空消失了呢

在执行完第一个SQL之后，在执行第二个SQL语句之前/数据库崩了，程序崩了，机器断电

事务的特性

a)原子性:事务中的若干个操作，要么全部执行成功，要么全部不执行，但此处的不执行并不是真的不执行，而是一但中间某个操作执行操作执行出错，就把前面已经执行完毕的步骤会滚回去，所以数据库会自动地来进行一些还原性的操作，来消除前面的SQL造成的影响，看起来就好像没有进行执行，回滚回去要借助逆向操作，目的是把原来操作造成的影响进行还原。例如：将原来的-2000操作变成+2000操作；
原子性:是一个不可拆分的最小单元
在SQL中，有的复杂的任务需要多个SQL来进行执行，有的时候，也同样需要打包在一起，前一个SQL是为了给后一个SQL提供支持，如果后一个SQL不执行了，或者在执行的过程中出现了问题，那么前一个SQL也就失去了意义；
那么进行回滚操作的时候，如何判定前面都执行了什么拿？
数据库会对执行中的每一个操作，记录下来
数据库想要记录上面的详细操作，也是需要消耗大量的时间和空间的，因此这个记录不会保存那么久，你的数据库是沉淀一年的，但是最新记录仅仅保存了几天
b)一致性：执行事务的前后，数据库中的数据始终处于一种合理合法的状态，例如转账操作，减账户操作，减帐户余额的时候，不可以将账户减成负数；

c)持久性：事务一旦执行完毕，此时对于数据来说，修改就是持久生效的啦（写入磁盘了）（写到磁盘就是持久生效的，数据写到内存中就是不持久的了，关机重启就没了）

d)隔离性:
start transaction;//开启事务
-- 阿里巴巴账户减少2000
update accout set money=money-2000 where name = '阿里巴巴';
-- 四十大盗账户增加2000
update accout set money=money+2000 where name = '四十大盗';
commit;//结束事务

隔离性与并发性相勃：

1)如果多个事务之间隔离性越强，并发程度越低，效率就越低

2)如果多个事务之间隔离性越弱，并发程度越高，效率就越高

其实来说隔离性是保证了数据的准确，并发性是为了保证事务执行的效率；

但是并发执行事务会出现问题：

1)脏读:如果一个事务A对某个数据进行修改(还没提交呢），另一个事务B读取了这里的修改内容，此时这样的事务B的读操作就是脏读；因为A在提交数据之前，随时就有可能修改刚才的数据；此时事务B可能读到的就是一个脏数据，这个数据是一个临时的结果，并不是最终的结果，例如写一个代码:class student，这个代码正在写的过程中，xxx看了一眼我的显示器，代码中有一个class student，里面有一个class student，里面有String name，看完之后就跑了，我之后就把这个内容删了，xxx看的数据就是脏读；
在这里面看到的student类，看到的并不是最终版本的数据，而是一个中间过程的数据，最终可能会被改成别的值，并没有什么实际意义
出现脏读的原因就是事务与事务之间，没有进行任何的隔离，他们是在交叉进行，没有进行真正的隔离开，是完全没有进行隔离
在A修改数据的时候（提交之前），B尝试读，就会阻塞，一直阻塞到A写完B才可以读数据

2)不可重复读:一个事务A执行过程中针对同一个数据两次读到的结果不一样就叫不可重复读

3)幻读:在一个事务执行过程中进行多次查询，多次查询的结果集不一样，是一种特殊的不可重复读)同一个事务，多次读到的结果集不一样，具体的结果数据是一致的；

这就会造成隔离性最高，并发程度最低，数据最可靠，速度最慢;所以并发和隔离是不可兼得的；我们就可以根据实际需要来调整数据库的隔离级别，通过不同的隔离级别，也就控制了事务之间的隔离性，也就控制了并发程度

MYSQL客户端不是自己可以凭空实现的，而是数据库会为我们来提供一组API来供使用，但是数据库的种类有很多，不同数据库提供的API都不太一样，MYSQL中的API和SQL server中的API是不太一样的，并且差异都很大；

什么是API？ API就是application programming interface 提供了一组函数/类/方法，让用户直接去使用，这是一个非常广义的概念；

每当我们操作一个数据库时，都要使用不同的API，这是很让人头大的；所以在java中，为了解决这个问题，引入了JDBC，可以理解成是java自带的一组数据操作的API，这组API可以说是覆盖了所有的各种的数据库操作方式，把不同的API给统一到一起了；

java自己本身完成JDBC API和具体数据库API的转换；不同数据库的API会通过一个中间转换器转化成JDBC风格的API，这个中间转换器就相当于是一个数据驱动程序；像相当于是以前支持很多格式充电头；可以通过转接头来转换我们手机的插头接口，那么数据库驱动程序就是转接头；
1)创建Datasource对象,这是一个准备工作；
2)基于Datasource对象，针对DataSource进行一些配置，以便后续可以更方便地访问服务器
这里要用到向下转型，配置需要三方面信息URL，user,Password
3)创建Connection,和数据库建立连接(相当于打开了客户端，输入了密码，连接成功了
4)利用PrepareStatement来拼装具体的SQL语句，这就相当于是在客户端具体输入了SQL的过程；
5)拼装好SQL之后，要执行SQL中的语句，相当于是在SQL客户端敲了一下回车
6）查看服务器的结果，屏幕上会显示
7）关闭连接，释放资源

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

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

public class MYSQL {
    public static void main(String[] args)throws SQLException {
1)进行一些准备工作
        DataSource dataSource=new MysqlDataSource();((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java?characterEncoding=utf-8&useSSL=true");
 下一步 设置用户名
        ((MysqlDataSource)dataSource).setUser("root");
 设置密码哦
        ((MysqlDataSource)dataSource).setPassword("12503487");
————————————————————————————————————————————————————————————————————————————————————————
2 和数据库建立连接,连接建立好了之后就可以进行接下来的数据
  一定要保证Connection是来自java.sql.Connection,当连接建立出错时，就会抛出这个异常
  建立连接的意义是为了确保当前网络连接是否正常，如果不正常，就会抛出上面的异常
  Connection的生命周期是比较短的，每次请求可以创建出一个新的Connection
        Connection connection=dataSource.getConnection();
_______________________________________________________________________________

 3拼装一个SQL语句，这里要用到一个preparestatement
        //当前实例中，当前的数据是写死的，我们可以让程序动态分配下去；
String SQL="insert into student values(1,"曹操",10);
PreparedStatement preparedStatement=connection.prepareStatement(SQL);
___________________________________________________________________________
第三步还可以这么写,下面这三个参数都可以通过Scanner这个类来动态获取到
int id=1;
String name="曹操"；
int classID=10;
String SQL="insert into students values(?,?,?)";
这里面的问号是一个占位符，可以把具体的变量的值替换到？里面去;
PrepareStatement statement=connection.PrepareStatement(SQL);
statement.setInt(1,id);
statement.setString(2,name);
statement.setInt(3,classid);
       
——————————————————————————————————————————————————————————————————————————————————
 4拼装完之后，可执行语句了
        int ret=preparedStatement.executeUpdate();
        //insert dele update都时使用ececuteUpdate来执行的
        //但是select就用executeQuery来执行，
          返回值表示此次操作执行了多行
        System.out.println(ret);
        //5 关闭资源,谁后创建，谁先释放
        preparedStatement.close();
        connection.close();
    }
}

((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java？characterEncoding=utf-8&useSSL=true");

1)第一个部分是向下转型，第二个部分是setURL，表示需要访问那个数据库的服务器
jdbc:mysql这是固定的协议名
2)IP地址表示要访问哪一台主机，端口号是为了要区分一台主机上的多台服务器
端口号后面是要访问的数据库名字 createdatabase[数据库名]
3)?后面表示字符集要和数据库配置的服务器的字符集要一致，否则会出现字符集乱码
最后表示是否加密

1）DataSource它的作用是用于配置如何连接MYSQL，他的一个类对象调用一个getConnection方法返回一个Connection对象；

2）Connection表示建立好的一次连接，操作数据库是需要先建立连接看一看发送请求的能力和接收数据的能力是否有缺陷，他的类对象调用一个prepareStatement方法来返回一个PrepareStatement对象；

3)PrepareStatement他有关联到一个SQL语句，然后她再调用executeUpdate或者executeQuery来执行具体的SQL语句；

4)ResultSet它用来表示Select查找结果的结果集，MYSQL返回的结果全在这个类中；-

示例一:下面用一个JDBC编程实现MYSQL数据库的查找工作；与上面结果不同的是MYSQL会根据发送的查询请求来返回一个表；


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

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


public class HelloServlet{
    public static void main(String[] args)throws SQLException {
        //1创建DataSource对象
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/javaweb?characterEncoding=utf-8&userSSL=true");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("12503487");
        //2借助Connection对象，与数据库进行连接
        Connection connection=dataSource.getConnection();
        //3拼装SQL语句;
        String sql="select * from student";
        PreparedStatement preparedStatement= connection.prepareStatement(sql);
        //4执行请求
        ResultSet resultSet=preparedStatement.executeQuery();
        //5遍历结果集，结果集相当于是一张表，这个表里面有很多行，每一行是一条记录next()一方面是判断当前是否存在下一行，另一方面是如果有下一行就获取到这一行
        while(resultSet.next())
        {//在这里一定要保证方法中的列名和表中的列名一定是一致的
            int id=resultSet.getInt("id");
            String name=resultSet.getString("name");
            int classid=resultSet.getInt("classid");
            System.out.println("id"+id+"name"+name+"classid"+classid);

        }
        //6关闭释放资源;
        resultSet.close();
        preparedStatement.close();
        connection.close();


    }
}

示例二：进行特定名字的删除操作

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

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class HelloServlet{
    public static void main(String[] args)throws SQLException {
        System.out.println("请输入你要求删除的学生姓名");
        Scanner scanner=new Scanner(System.in);
        String name=scanner.next();
        //1创建DataSource对象
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/javaweb?characterEncoding=utf-8&userSSL=true");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("12503487");
        //2与数据库建立连接
        Connection connection=dataSource.getConnection();
        //3拼装SQL语句
        String SQL="delete from student where name = ?";
        PreparedStatement preparedStatement=connection.prepareStatement(SQL);
        preparedStatement.setString(1,name);
        //4执行SQL
        int ret=preparedStatement.executeUpdate();
        if(ret==1)
        {
            System.out.println("删除成功");
        }else{
            System.out.println("删除失败");
        }
        //5关闭并释放资源
        preparedStatement.close();
        connection.close();

    }

}

示例三：进行修改操作，我们要修改id为n的学生姓名


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

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class HelloServlet {
    public static void main(String[] args)throws SQLException {
        System.out.println("请输入你要修改的学生的id");
        Scanner scanner=new Scanner(System.in);
        int id=scanner.nextInt();
        String name=scanner.nextLine();
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/javaweb?characterEncoding=utf-8&userSSL=true");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("12503487");
        Connection connection=dataSource.getConnection();
        String SQL="update student set name = ? where id = ?";
        PreparedStatement preparedStatement=connection.prepareStatement(SQL);
        preparedStatement.setString(1,name);
        preparedStatement.setInt(2,id);
        int ret=preparedStatement.executeUpdate();
        if(ret==1)
        {
            System.out.println("修改成功");
        }else{
            System.out.println("修改失败");
        }
        preparedStatement.close();
        connection.close();


    }
}