[数据结构与算法]【MySQL 数据库 进阶】 面试官常问 索引+事务+JDBC

索引与事务

本条帖子只介绍原理及概念，不包含具体的底层实现

一、索引

1.1 何为索引

在数据库中数量及其庞大的时候，怎么快速找到目标尼？此时就会用到索引。一本书想快速找到自己想找的知识点，首先就会先去看目录，而索引就是相当于书的目录，方便查询。

1.2 索引的特点

• 类似于书籍目录，快速定位，方便查询
• 索引提高数据库的性能帮助极大
• 数据库中的表，数据，索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系 。

二、索引的数据结构

说到速度，增删查改，肯定就能想到数据结构，数据库本质上也是用数据结构来实现的，那么怎样的数据结构是合适索引的尼？

• 首先二叉搜索数，他的底层实现固能提高查找效率，但是当他是单枝树的时候时间复杂度就是O(N)了
• 其次是AVL树，AVL树是二叉树搜索数的升级版，它解决了单枝树的情况，但是也引入了新问题，在进行插入或删除操作的是后就会破坏AVL树的结构规则，规则是abs（左子树高度-右子树高度）<=1，破坏了规则就要调整，这样每次调整就会很频繁，那么插入或删除就变得低效了。
• 然后就是哈希表，哈希表的时间复杂度是O(1)，这个速度就快了，但是哈希表存在很大的局限性，哈希查询是key == value，但在数据库中有很多的查询方式（< > != …），哈希表还有个致命缺陷就是哈希冲突。
• 再然后就是红黑树，红黑树毕竟是二叉树的基础，所以查询效率是由树的高度决定的，高度也就相当于比较次数，当数据结构庞大的时候，高度也会高比较次数，就会更多，查找效率就会减慢了

那么这上面的数据结构可以说是数据结构中的顶流了，他们都不行，那么MySQL中的索引使用的什么数据结构尼？？？

其实MySQL中的索引用的是一个N叉搜索树！！！

啥是N叉搜索树？？？

上面说了红黑树查询效率是由树的高度决定的，N叉搜索树目的就是来减少高度，高度低了查询比较次数就减少了，效率就高了。

N叉搜索树，相当于B+树，想要了解B+树，就要先看看B树。

2.1 B树

二叉树和B树区别

二叉树：

• 每个节点含一个值
• 只有两个分支
• 每个节点所能包含的信息少

B树：

• 每个节点含多个值
• 有多条分支
• 每个节点里面包含了很多列，每个节点占用的空间大，保证了B数每个节点都要存在硬盘上，像MySQL这种有庞大的数据这一点明显更适合

可以从它两区别看出，B树优化了二叉树高度问题，减少查询次数，增加了效率，并且能把数据存在硬盘上不会消失

但是B树还可以进一步改进，然后就有了B+树：

2.3 B+树

B+树和B树变化有两方面

• B+树非叶子节点的值存在重复，保证叶子节点这一层是完整的数据结构集合（重复带来的意义）
• 最后叶子节点用过链表方式，把所有的叶子节点按照顺序连接起来

B+树的优势：

1、善于范围查找，如那两个边界值，分别找两个边界值的位置，（id<50 and id >30)

2、查询最终会落在叶子节点这一层，查询速度稳定，快

3、因为叶子是数据的全集，所以把叶子节点存在了硬盘上，非叶子节点存在内存中，又进一步降低了读取硬盘的次数！！！（B+树的大杀器）

注意事项：

1、索引是查找比较多的情况下才适合用

2、索引占据的空间不小，磁盘小不建议用

3、建立索引需要区分度比较大得时候才适合制作索引

4、在MySQL中使用create index命令，MySQL底层自动帮我们创建B+树

2.4非聚簇索引

描述MySQL底层是怎样组织数据的。

非聚簇索引是通过“表”的结构，把所有的数据装进去

最后通过表的形式，把数据都装入一个表中

2.5聚簇索引

数据本身就是通过B+树方式组织的，每个叶子节点存一条完整记录。

聚簇和非聚簇，最终是聚簇的索引更高效，因为非聚簇还要建一个表再把数据放入表中这个动作拖慢了速度，但是非聚簇有一个优点就是它是通过每一行存储数据那么它的磁盘碎片就比较少，更省空间。

三、事务

3.1 何为事务

一般在钱的事情上人们都比较敏感，如果别人给你转了200，那么别人的账户就-200，这时候如果服务器断了，你就没有收到别人的200块钱，这种现象会造成一种社会的恐慌是一种严重的现象，为了解决这种现象SQL就使用事务来控制，保证要么全部执行成功，要么全部执行失败。

3.2 事务的概念

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。

3.3 事务的特性

• 原子性：将一系列操作打包到一起，构成一个整体，这个整体要么全做完，要么全失败。 such as：A账户-200，B账户+200，中途数据库崩溃，那么原子性，会把做了中间状态的操作还回去（术名：回滚），原子性会把中间的操作记录下来，当数据库崩溃是，在把上一条操作还回去，从而实现要么全成功，要么全失败。
• 一致性：执行事务之前，和执行完事务之后，当前表里面的数据都是合理的状态
• 持久性：事务操作的数据都是在硬盘上操作的，而硬盘是一个持久化储存的。
• 隔离性：多个事务，并发执行时产生的问题！！！

四、JDBC

4.1 何为JDBC

JDBC是java当中的数据库的API，也就是说JDBC是通过java代码操作数据库的。由于数据库支持多种编程，也就是说不同数据库支持不同语言的API，数据库也分多种数据库，也分多种API，如果通过代码操作不同数据库完成同样的功能，也就要写多种语言的代码，开发成本高，学习成本也高，时间成本也高，所以为了解决上诉问题，就是把各类数据库的各类API抽象出来封装到一层，封装出一套统一的API。 那么JDBC就是java语言标准库提供的API。

4.2 JDBC工作原理

JDBC优势：
1、Java语言访问数据库操作完全面向抽象接口编程
2、开发数据库应用不用限定在特定数据库厂商的API
3、程序的可移植性大大增强

4.3 JDBC的实现

1、增加操作：

public static void testInsert() throws SQLException {
        //DataSource标准库的接口，    MYsqlDataSource()是MySQL JDBC的驱动
        DataSource dataSource=new MysqlDataSource();
        //1.设置IP和端口，通过URL来表示，
        //1).setURL属于MySQLDataSource的方法，用到向下转型
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java101?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("0123456789");

        //3.连接数据库
        Connection connection=dataSource.getConnection();

        //4.让用户输入id和姓名
        Scanner scanner=new Scanner(System.in);
        System.out.println("请输入学号：");
        int id=scanner.nextInt();
        System.out.println("请输入姓名");
        String name=scanner.next();

        //5.构造一个SQL语句，给插入做准备
        String sql="insert into student values(?,?)";
        PreparedStatement statement=connection.prepareStatement(sql);//与数据库服务器进行连接交互
        statement.setInt(1,id);//提供setXXX方法，下标从1开始
        statement.setString(2,name);
        System.out.println("sql:"+ statement);
    
        //executeUpdate()来变更数据库增，改，删操作
        int ret=statement.executeUpdate();
        System.out.println("ret:"+ ret);

       //释放资源
        statement.close();
        connection.close();
    }

2、删除操作，跟增加操作差不懂不在赘述：

public static void testDelete() throws SQLException {
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java101?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("0123456789");
    
        Connection connection=dataSource.getConnection();

        Scanner scanner=new Scanner(System.in);
        System.out.println("请输入删除的id");
        int id=scanner.nextInt();

        //构造sql
        String sql="delete from student where id=?";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,id);
        System.out.println("sql:" + statement);

        //执行sql
        int ret = statement.executeUpdate();
        System.out.println("ret:" + ret);

        //释放资源
        statement.close();
        connection.close();


    }

3、修改操作，和前面增，删操作一样

public static void testUpdate() throws SQLException {
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java101?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("0123456789");

        Connection connection=dataSource.getConnection();

        Scanner scanner=new Scanner(System.in);
        System.out.println("请输入你要修改的id");
        int id=scanner.nextInt();
        System.out.println("请输入你要修改的名字");
        String name=scanner.next();

        //构造sql
        String sql="Update student set id=? where name=?";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,id);
        statement.setString(2,name);
        System.out.println("sql:"+statement);

        //执行sql
        int ret= statement.executeUpdate();
        System.out.println("ret:" + ret);

        //释放资源
        statement.close();
        connection.close();

    }

4.查询操作，跟前面不太一样：

public static void testSelect() throws SQLException {
    
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java101?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("0123456789");

        Connection connection=dataSource.getConnection();

        String sql="select * from student";
        PreparedStatement statement= connection.prepareStatement(sql);

       // 因为select最终呈现的表是一张临时表，要写额外的代码，报这张临时表获取到
        ResultSet resultSet=statement.executeQuery();
        //resultSet是一张表，

        while (resultSet.next()) {
            //next()表示获取当前行同时切换下一行类似于i++，有返回true，没有返回false
            //resultSet表示当前行，下面get表示每一行中的具体列
            int id=resultSet.getInt("id");
            String name=resultSet.getString("name");
            System.out.println("id:"+id+" name:"+name);
        }
        //释放资源
        resultSet.close();
        statement.close();
        connection.close();

    }

以上JDBC的实现是固定套路，需要多敲代码熟悉，自然就记住了，上面的索引和事务大家也要掌握原理和概念，面试官在考察MySQL的时候很有可能问到底层原理，但不必掌握底层代码的实现，除非你以后是专门开发数据库的，那么你就要看底层代码B+树怎么实现了。

铁汁们，觉得笔者写的不错的可以点个赞哟?🧡💛💚💙💜🤎🖤🤍💟，收藏关注呗，你们支持就是我写博客最大的动力！！！！