索引和事务

admin管理员组

文章数量:1570215

一.索引的含义及应用

索引就是排好序的，帮助我们快速查找的数据结构。简单来讲，索引就是一种将数据库中的记录按照特殊形式存储的数据结构，通过索引能显著提高数据查询的效率，提升服务器的性能。

把无序的数据变成有序的查询

1.把创建了的索引的列的内容进行排序

2.对排序结果生成倒排表

3.在倒排表内容上拼上数据地址链

4.在查询的时候，先拿到倒排表内容，再取出数据链地址，从而拿到具体数据

二.索引的优势与劣势
优点:提高数据检索的效率，降低数据库IO的成本

        通过索引列对数据行进行排序，降低数据排序的成本，降低CPU的消耗

缺点:创建索引和维护索引需要耗费时间

        索引需要占物理空间，除了数据表占用数据空间之外，每个索引还要占据一定的物理空间

        当对表中的数据进行添加、删除、和修改的时候，索引也需要动态的维护，降低了数据的维护速度

创建索引的原则:

(1)在需要经常搜索的列上创建索引，加快搜索的速度

（2）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排好序，其指定的范围也是连续的

（3）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序时间

（4）在作为主键的列上创建索引，加快搜索的速度

（5）在经常使用where 子句中的列上创建索引，加快条件判断速度

三.索引的查看、创建的删除

1.索引的查看：

show index from 表名

 

主键会自带一个索引

2.索引的创建：

create index  索引名 on 表名（列名）

3.索引的删除：

drop  index  索引名 on 表名

四.索引的底层结构：

Mysql中常用的索引结构有两种:一种是B+树，另一种则是Hash，Hash底层是由Hash表来实现的，是根据<key,value>来存储数据的结构

对于每一行数据，存储引擎都会对所有的索引列计算出一个哈希码，哈希吗是一个较小的值，如果出现哈希码值相同的情况的时候会拉出一条链条。

Hash索引的优点:

如果索引自身只需存储对应的Hash值，索引结构很紧凑，只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引

没有哈希冲突的情况下，等值查询访问哈希索引的数据很快，如果发生哈希冲突，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到符合条件的行

Hash索引的缺点

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行

哈希索引只支持等值比较查询，不支持任何范围查询和部分索引列匹配查找

哈希索引数据并不是按照索引值顺序存储的，也就无法用于排序。

那会不会是二叉搜索树呢？其实也不是的，因为当元素个数多的时候，树的高度也就会比较高，树的高度也决定了查询时候元素的比较次数会多，数据库进行比较时，是会读硬盘的。

那B树是不是呢？

我们看到一个节点上的数值多了，这样读写硬盘的次数就减少了，因为每个结点都是在硬盘上的。

实际上索引的底层结构也不是它

实际上对于带有主键的表，索引的底层是B+树 

叶子结点会用一种了类似于链表的方式连接起来，叶子结点包含了所有结点的值。

这种结构的好处：1.树的高度变低，硬盘IO的次数就会减少 

2.更适合范围查询，直接在叶子结点找到数据范围最小值和最大值就可以了

3.比较次数比较均匀，按照图来看，数据基本上都在3次多

4因为叶子结点包含所有的key，会包含所有的数据，而非叶子节点，只会保留一条数据id,这样非叶子结点我们可以把它放在内存里就可以了，进一步降低了硬盘IO。

我们再从底层深入地了解一下

我们知道数据库当中的数据其实是以页的形式存储在磁盘当中的，我们为1个字段设置主键

那么我们再去往里面插入数据的时候，数据都是按照主键大小进行排列的，

我们创建一张table表，分别设置了a、b、c、d、e，并为a字段设置主键，并将存储引擎设置为innodb。这时我们的主键也就默认带索引。 

 那这样的话，就会产生一个问题，一旦数据量特别多的时候，我们要查某一条数据的话，假设它的主键值很大，就需要遍历链表，查起来就会比较麻烦，这时候我们就引入了页目录

每个页目录里面存放的是主键，这样的话我们去查询数据的时候，先查页目录，定位我们要查找数据的具体位置，然后再去遍历，这样就会快很多， 但是如果主键非常大的时候，查起来还是不方便，这时我们就引入了索引

此时我们发现我们索引的数据结构是一个B+树，最下边叶子结点用双向链表的形式组织起来，这样我们去查找数据的时候 ，自上而下，从根节点去找我们要查找数据的大体范围，然后到叶子结点再进行遍历，比如我们要查找主键id为3的数据，我们从根节点开始3<5,定位页地址1，然后到第二层，3=3到页地址3，然后到了叶子结点，去遍历链表得到了结点

因为底层是一个双向链表，因此适合范围查询，比如当我们去查找查找主键大于3的数据，那么我么先定位到主键值为3的值，然后向右遍历就可以了，如果我们要找主键小于3的数据，那么我们先定位到主键值为3的值，然后向左遍历即可。

我们再来看一下联合索引   我们创建bcd字段联合索引

我们看到联合索引它则是以它的索引字段进行数据排序的，底层叶子结点上边是索引字段下边是主键，通过主键进行回表查询，在Mysql5.6之前联合索引先回表，再查询，5.6之后先查询再回表，

走不走联合索引的几种情况

(1)当联合索引的字段在where条件里都有的时候

 （2）当不符合最左匹配原则  也就是联合索引的第一个字段b在where条件当中没有的时候

这时候我们是无法从根节点上的值进行比较，也就无法走索引了。

(3) 当要查找的字段有的不在联合索引的叶子节点

像这种情况，我们并没有走索引，而是全表扫描，当我们查找全表我们的e字段并不在我们的联合索引里，如果走索引，这时就需要回表查询，相比于直接全表查询，那个更快，需要看具体情况

 像这种情况即使我们全表扫描后需要对数据排序，而走索引不需要对数据排序（数据已经排好了）但是走索引需要回表查询，这时候效率还是比全表扫描低

(4)当要查找的字段在联合索引的叶子节点（走联合索引）

五.Buffer Pool

1.含义:Buffer Pool是位于内存中的数据缓冲区，在其中存取了数据库的数据页

当我们要读取或者修改一条数据的时候，需要我们先去Buffer Pool里面查找相关数据，如果找不到，则去硬盘里读取数据，然后加载到Buffer Pool，在Buffer Pool里进行数据的修改。

2.BufferPool内部数据结构

（1）free链表 

当我们去把磁盘里的页加载到Buffer Pool里面的时候，是按照顺序加载到Buffer Pool，想象一种情况，当我们Buffer Pool里面已经满了的时候，Buffer Pool里面的数据有的会回写到磁盘当中，这时候就出现Buffer Pool里面内存不连续的情况，当我们再去往Buffer Pool里面存入数据页的时候，怎样存放呢？这里我们引入了free链表，每个链表里的1个节点指向Buffer Pool里面一个空的数据页

（2）flush链表

比如当我们执行sql语句, update  t1 set math=80 where id=1  假设id为1的数据在BufferPool里面存在，我们去BufferPool里面找到这条数据所在的页，然后进行修改，当修改完这条数据，这条数据并不一定会立即写回到磁盘当中，我们称这个页为"脏页",BufferPool里面可能会有很多脏页，为了后期更好的定位这些脏页，把这些脏页重新写回到磁盘当中，我们用flush链表来管理这些脏页，每个flush链表中的1个节点指向一个脏页

（3）lru链表

当Buffer Pool里面的数据页满了的时候，当我们再插入新的数据页的时候咋办？

采用Iru链表，在链表前面的数据区域时热数据区域，后面是冷数据区域，当我们插入新的数据页的时候，会删掉冷数据区域最后一个控制块，然后把新的数据页插入到冷数据区域最前头，当冷数据页第二次被访问且与第一次被访问超过1s，就会把冷数据页加到热数据区域，并删掉在冷数据区域对应的控制块。

3.BufferPool的具体流程

我们先来看一下BufferPool的一个结构图

当执行SQL语句的时候进行修改的时候，用户提交事务，LogBuffer会把SQL语句修改信息进行保存，Log Buffer中的日志并不是立即写入磁盘中的Redo Log文件 ,会选择在合适的时机，写入到磁盘当中的Redo Log,磁盘当中的RedoLog当中，当数据库宕机后，可以利用Redolog进行恢复。

另外我们可以对RedoLog持久化进行配置

六.事务

1.事务的含义：将多个SQL打包到一起，作为一个整体执行，要么操作就全部执行，要么一旦出现异常，就都会回到原来的样子。数据库会把执行的每个操作记录下来，如果某个操作出错，就恢复到原来的样子，比如删除 会恢复到插入，修改会恢复到原来的样子，这个过程叫做‘’回滚‘’。

举个例子：我们转账的时候，我们给别人转账了50，自己的钱包会少50，而别人的钱包却没增加50，这时候会恢复到原来没转钱的状态。

2.事务的特性：

(1)原子性。是指将多个SQL打包一同执行。

(2)持久性，事务产生的修改会保留下来会写入硬盘，不会因为突发情况而丢失，比如断电。

(3)一致性，事务执行前后数据的要合法，比如像刚才的转账出现的状况就是不合法的

(4)隔离性：指事务之间的影响程度。

一般而言，事务的隔离级别越高，事务的并发程度越低，数据准确性越高，但效率越低，事务的隔离级别越低，事务的并发程度越高，数据准确性越低，效率越高。

 Mysql中事务之间的隔离级别有4种:

(1)读未提交:这种情况下会出现脏读和幻读以及不可重复读的问题

(2读已提交:这种情况下会出现，幻读以及不可重复读的问题

(3)可重复读：Mysql的 默认隔离级别，会出现幻读的问题

(4)串行化：能解决所有并发的问题，但效率太低

脏读：A事务在写完数据后并没有进行提交，B事务读取完数据，A事务进行回滚，解决方法针对写操作加锁

不可重复读:A事务写完数据后提交事务，B数据进行读取，此时A事务再将提交后的数据进行修改然后再提交，此时B事务前后读取的数据是不一样的，解决方法在针对写加锁的基础上针对读加锁

幻读:A事务两次查询得到的结果集不同，因为B事务新增了一部分数据

事务的四种特性是如何进行保证的?

对于原子性:主要是通过 undolog日志来保障的,undolog是存储引擎层(innodb)生成的，记录的是逻辑操作日志，比如对某一行数据进行了insert语句操作，那么undo log就记录一条与之相反的delete操作，主要用于事务的回滚

持久性:主要通过redo log日志,redolog是存储引擎层(innodb)生成的，会记录对bufferpool里面数据的修改，当数据库宕机，可以利用redo log日志来将数据持久化到磁盘当中去。

 一致性:通过其他3种特性和代码逻辑来保证的

  隔离性:主要是通过Mysql各种锁以及MVCC机制来实现的

串行化:是通过对读操作加锁，和写操作加锁加锁来实现的

读锁:select___lock in share mode   读锁是更享的，多个事务可以同时读取同一个资源，但不允许其他事务修改

写锁:写锁是排他的，会阻塞其他的写锁和读锁，update、delete、insert都会加写锁

不可重复读和可重复读主要是通过MVCC,多版本并发机制，就可以做到读写不阻塞，且避免了类似脏读这样的问题，主要是通过undo日志链来实现

我们看到对一条数据多次修改，Mysql都会帮我们记录着 

对于不可重复读读取的是u都是最新修改的数据

而对于可重复读，读取的历史快照数据，也就是第一次提交的，所以同一个事务中（还未提交）进行多次select操作，读取的都是一样的数据

如果是修改的话，那么修改的最新的数据，这样可以很好的提高 读写并发性能。

七.JDBC

1.JDBC的作用:JDBC为多种关系数据库提供了统一的访问方式，为java开发人员操作数据库提供了统一的访问方式。

2.应用JDBC通常分为五个步骤：

(1).建立数据库连接

(2).创建操作命令

(3).执行SQL语句

(4).处理结果集

(5).释放资源

增删改 的操作大体一样，只需换一下SQL语句

 public static void main(String[] args) throws SQLException {
        //1.先去创建DataSource 数据源，描述了mysql数据库在哪
        MysqlDataSource datasource=new MysqlDataSource();
        ((MysqlDataSource)datasource).setURL("jdbc:mysql://127.0.0.1:3306/java_114?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)datasource).setUser("root");
        ((MysqlDataSource)datasource).setPassword("1291691906");
        //2.和数据库建立连接
        Connection connection=datasource.getConnection();
        Scanner scanner=new Scanner(System.in);
        System.out.println("请输入id");
        int id=scanner.nextInt();
        System.out.println("请输入name");
        String name=scanner.next();
        //3.构造SQL语句
        String sql="insert into student values(?,?)";
        //4.创建操作命令Statement对象,使用操作命令来执行SQL,将SQL语句发送到数据库里在这里对SQL语句进行词性语法的分析，SQL语句会预编译

        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,id);
        statement.setString(2,name);
        int ret=statement.executeUpdate();//executeUpdate()返回一个整数值，ret表示此次SQL语句操作影响了多少行。
        System.out.println(ret);
        System.out.println("sql:"+statement);
        //5.断开连接，释放资源，先创立的后释放。
        statement.close();
        connection.close();







    }

public static void main(String[] args) throws SQLException {
        DataSource datasource=new MysqlDataSource();
        ((MysqlDataSource)datasource).setURL("jdbc:mysql://127.0.0.1:3306/java_114?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)datasource).setUser("root");
        ((MysqlDataSource)datasource).setPassword("1291691906");
        Scanner scanner=new Scanner(System.in);

        System.out.println("请输入id");
        int id=scanner.nextInt();

        System.out.println("请输入name");
        String name=scanner.next();
        Connection connection=datasource.getConnection();
        String sql="update student set id=? where name=?";
        PreparedStatement statement=connection.prepareStatement(sql);

        statement.setInt(1,id);
        statement.setString(2,name);


        //statement.setString(2,name);
        int ret=statement.executeUpdate();
        System.out.println(ret);
        System.out.println(statement);
        statement.close();
        connection.close();

    }

ublic static void main(String[] args) throws SQLException {
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java_114?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("1291691906");
        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();
        String sql="delete from student where id=? or name=?";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,id);
        statement.setString(2,name);
        int ret=statement.executeUpdate();
        System.out.println(ret);
        System.out.println("sql:"+statement);
        statement.close();
        connection.close();







    }

查找：

public static void main(String[] args) throws SQLException {
        1.先去创建DataSource 数据源，描述了mysql数据库在哪
        DataSource datasource=new MysqlDataSource();
        ((MysqlDataSource)datasource).setURL("jdbc:mysql://127.0.0.1:3306/java_114?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)datasource).setUser("root");
        ((MysqlDataSource)datasource).setPassword("1291691906");
        //2.和数据库建立连接
        Connection connection=datasource.getConnection();
         //3.构造SQL语句
        String sql="select*from student";
        //4.创建操作命令Statement对象,使用操作命令来执行SQL，将SQL语句发送到数据库里，对SQL语句进行词性语法的分析。
        PreparedStatement statement=connection.prepareStatement(sql);
        //5.ResultSet对象被称为结果集，代表符合所有SQL语句条件的数据，并且可以通过getxx方法访问每一行的数据
        ResultSet resultSet=statement.executeQuery();//返回单个结果集
        while(resultSet.next())//将数据一行一行的往下遍历。
        {
            int id=resultSet.getInt("id");//获取当前行的id的值
            String name=resultSet.getString("name");//获取当前行name的值
            System.out.println("id:"+id+" "+"name:"+name);
        }
        //释放资源。先创立的后释放
        resultSet.close();
        statement.close();
        connection.close();

    }

3.JDBC里涉及的对象及方法解析

（1）ResultSet是一个结果集，它里面的数据一行一行排列，每行有多个字段，并且有一个记录指针，依次往下遍历指针所指的数据叫做当前数据行，如果想要获取某一行，使用Reslut对象里的next()方法，想要获取ResultSet对象里的所有行，可以用whlie循环 

（2）Connection接口实现类由数据库提供获取Connection对象通常有两种方式：DriverManner（驱动管理类）的静态方法获取2.通过DataSource（数据源）对象获取，实际中我们采用DataSource对象。这两种方式的区别1.DriverManger类获取的Connection连接，是无法重复利用的，每次使用完释放资源后，通过Connection.close()关闭物理连接。DataSource提供连接池的支持，连接池在初始化时将创建一定数量的数据库连接，这些连接是可以复用的，每次使用完数据库连接时，释放资源调用connection.close()将Connection对象回收。

（3）Statement对象,将SQL语句发送到数据库里，JDBC API主要提供了三种Statement对象

Statement

用于执行不带参数的简单的SQL语句

PreparedStatement

用于执行带或者不带参数的SQL语句

SQL语句会预编译在数据库系统

CallableStatement

用于执行数据库存储过程的调用。

实际开发中，我们常用PreparedStatement对象。

PreparedStatement对象：两种执行SQL的方法

executeQuery()方法执行后返回单个结果集，通常用于select语句

executeUpdate()方法返回一个整数，表示受影响的行数，通常用于update、insert、delete语句

本文标签： 索引事务