MySQL的索引事务和JDBC编程

目录

索引

查看索引

创建索引

删除索引

底层数据结构（这个很重要哦，面试容易问）

事务

事务的使用

事务的基本特性

并发执行事务可能产生的问题

MySQL提供的四种事务隔离级别

JDBC编程

JDBC的来源（一定要了解）

Java操作MySQL

拓展知识（也要看！！）

SQL语句设置为动态传入值

查询操作，Java打印结果集

---

索引

很多时候我们都需要对表进行操作，如果数据很多的话，每次都一条条的遍历速度岂不是会很慢，这时候也就引进了索引，我们只需维护索引表，多占据了点存储空间但查询效率会很高的！所以索引是用来增快查询效率的。

缺点也有的：

1.会占用一部分额外的空间。

2.可能会拖慢增删改的速度，虽然我们可以很快速的查到对应的记录，并进行操作，但我们还要维护索引表。

创建主键约束（ PRIMARY KEY ）、唯一约束（ UNIQUE ）、外键约束（ FOREIGN KEY ）时，会自动创建对应列的索引。

查看索引

show index from 表名;

创建索引

对于我们没有设置约束（unique,primary key,foreign key）的列，索引固然不会自动生成，但是我们可以手动给一些列添加索引。

这个操作很危险，如果是空表，我们给字段创建索引没问题，但如果表中已经有大量数据，我们再创建索引，容易卡死，服务器会一直执行这个操作，也无法再给其他的提供服务。所以建议大家创建完表后不要轻易创建索引，在创建表的时候多想想，哪些可能会用到索引。

create index 索引名（自己起） on 表名(字段名) 

删除索引

指定好要删除的索引名和表名。删除索引只可以删除自己手动创造的索引，对于自动生成的索引不可以进行删除！！这个操作也危险的很！

drop index 索引名 on 表名;

底层数据结构（这个很重要哦，面试容易问）

mysql索引的数据结构取决于底层的存储引擎。mysql存储方式有很多种，现在我们主要用的就是innodb存储引擎，因为其他的存储引擎可能会用到hash作为索引。hash：不能够范围查询，不能够进行模糊匹配查询（一般精确查询的时候可以用）；红黑树嘛则可以进行范围查询也可以进行模糊查询，但是他会引入较多的IO硬盘。

通过分析我们原来学过的数据结构部分内的结构，B+数简直是成为数据库底层数据结构的天选之子。我们先来回顾一下B树，再进一步突出B+树的优点。

B树（B-树）：

本质上是一个N叉搜索树，一个结点上可以保存多个key。N个key就可以延伸出N+1个分叉，也就划分出了N+1个区间。

1.B树查询元素的流程：从树根节点出发，判断要查找的元素是否在根节点上存在，如果不存在，看元素落在哪个区间，就沿着这个区间的路线往下寻找最终找到叶子结点，如果仍不存在那就是真的不存在了。

2.对于数据库而言，每个节点都要把数据从硬盘上读出来再进行比较，所以一个节点上有一个key还是有多个key影响差不多。

3.对于B树而言，插入或者删除元素可能会涉及拆分和合并（对于如何拆分合并这里就不进行详细叙述了）

B+树：

在B树基础上又做了一些改进。

1.B+树也是N叉搜索树，但是N个key只分出N个区间，其中结点上的最后一个key就是最大值（取最小值也可以）

2.父节点的key会在子结点中重复出现（以最大值或者最小值的身份），虽然浪费了很多不必要的空间，但是实现了一个效果：叶子结点这一层包含了一整个数据集。

3.把叶子结点按照链表方式，首尾相连，此时，就可以通过叶子节点之间的连接，快速找到下一个或者上一个元素，进一步也可以更方便进行范围查询。

4.所有的查询操作最终都落在了叶子结点上，查询时间比较均衡。

5.由于叶子结点上是完整的数据全集，因此表的每一行数据的其他列，都可以保存到叶子结点上，对于非叶子结点嘛，只需存储构建索引的key即可。

事务

事务的本质就是把多个操作打包成一个操作来完成，要么一起成功执行，要么一起死（都不执行）。这里一个都不执行的意思指，执行了能执行的，但遇到报错后，原来的全部都不做数（还原到最初没有执行的样子，也可称为回滚）。

回滚的实现：把事务中执行的操作都记录下来（通过特定的日志来记录，数据库自己记录！），如果需要回滚，直接按照之前操作的逆操作来执行。例如：上个操作是插入，逆操作就是删除。上个操作是删除，逆操作就是插入。

事务的使用

start transaction;#开启事务
执行多条SQL语句
rollback/commit#回滚/提交

只要开启了事务，必须以这两个结尾，否则，后续在执行的任何SQL语句均属于事务中。
rollback:手动触发回滚
commit:把事务里面的SQL按照原子方式执行（带有回滚机制）

事务的基本特性

1.原子性：保证多个操作被打包成一个整体，要么能全部执行正确，要么就一个都不执行。

2.一致性：事务执行之前与事务执行之后，数据都可以对上。

3.持久性：事务执行的各种操作都是持久生效的（写入到硬盘中）。

4.隔离性：并发执行事务时，隔离性会在执行效率和数据可靠之间做出权衡。隔离描述的是同时执行的事务之间相互的影响，隔离性越高，数据越可靠，并发性（同时执行）越低。

并发执行事务可能产生的问题

1.脏读：读了写事务提交前的中间数据（脏数据），可以增加写加锁，提交之前不可以读。

2.不可重复读：一个事务内，多次读取同一个数据，发现前后数据不同（在读的时候，另外一个事务修改了数据），我们可以增加读加锁，读的时候不可以修改。

3.幻读：一个事务内，多次读到的数据，值相同，但是结果集不同。我们可以彻底串行化，不采用并行机制。

MySQL提供的四种事务隔离级别

1.read uncommitted，允许读未提交的数据。（会出现上面的脏读，不可重复读，幻读问题）隔离性最低，并发程度最高，数据可靠性最高，效率也最高。

2.read committed，允许读取已经提交的数据（进行了写加锁），解决了脏读，存在不可重复读和幻读，隔离性提高了，并发性降低了，数据可靠性提高了，但效率降低了。

3.repeatable read，（默认的隔离级别）可以重复读取数据（进行了读与写操作加锁），解决了脏读，不可重复读的问题，但存在幻读问题，隔离性又提高了，并发性也降低了，但数据可靠性又高了，效率又低了。

4.serializable，事务彻底串行执行，解决了脏读，不可重复读，幻读的问题。隔离性最高，并发性最低（也可以说没有并发性），数据最可靠，效率也最低！

MySQL可以配置自己的隔离级别，但是大部分情况下，默认级别就够用了！

JDBC编程

JDBC的来源（一定要了解）

说白了，就是通过Java代码操作数据库，实际开发中，我们都是通过代码来操作数据库的，但底层都离不开SQL语句。我们先来了解一下什么是JDBC吧！

成熟的数据库都会给我们提供很多API（一些类或者方法），我在初识数据库那一篇文档中提到了数据库大概有哪些，大家可以去看看。是不是有很多不同的数据库呀，这些数据库是不同的程序员编写的，提供的API也就不同了，我们用哪个数据库还得去把对应的API学习了，那岂不是对于我们很不友好嘛！于是，在那时候，Java就很火了，他就提出了一套操作数据库的API，并告诉各种数据库的开发者，你如果不遵循我的API，我就不采用你，所以各大数据库就提供了相对应的驱动包（将各数据库自己的API通过驱动包转换成Java提供的），所以我们只需要学习JDBC这一种就可以了！在写项目的时候把驱动包放到咱们项目中我们就可通过JDBC提供的API去操作各大数据库。

Java操作MySQL

1.首先创建我们的项目。

2.在我们创建的项目中，可以创建一个lib的包，把我们下载的驱动包放到这个文件夹下。

3.我们将创建的文件夹lib改一下，这样idea就知道里面存放的是库文件

4.下面就开始创建Java文件编写代码了，虽然比较繁琐，但每次套路都一样，建议刚开始多敲敲，不要直接复制粘贴，锻炼锻炼自己。

1.创建数据源

        //1.先创建一个DataSource
        DataSource dataSource = new MysqlDataSource();

这我们进行了向上转型，前面在数据库ArrayList和List中我也说过，进行向上转型，可以方便我们后续对代码的修改，如果我们用别的数据库，我们仅更换这一句就行。

2.设置URL

((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/javatest?characterEncoding=utf8&useSSL=false");

这个我们又向下转型转回来了，目的就是为了拿到子类特有的方法setUrl来设置url

3.设置数据库名字和密码（数据库大家都是root，密码为我们安装数据库的时候设置的密码）

        //数据库的用户名和密码
        ((MysqlDataSource) dataSource).setUser("root");  #大家都是root
        ((MysqlDataSource) dataSource).setPassword("rootroot");

4.前面三个步骤只是告诉Java程序，数据库服务器的位置，并未连接数据库呢，我们这步就要去连接了。注意我们导入Connection包的时候只可以导java.sql的

        //2.建立数据库服务器之间的连接，连接好以后才能进行后续的请求-响应交互
        Connection connection = dataSource.getConnection();

我们编写完代码后，会有个波浪线对吧，这就要涉及到异常了，getConnection这个方法可能会抛出异常，上一个并未处理，我们可以进行处理也可以再进行抛出，我们这里继续抛出就好。

5.编写sql语句（在Java程序中的sql不用带分号）

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

1）SQL是String类型，正常的话，我们可以使用JDBC提供的statement对象，把String转换成Statement再发给服务器。但是所有操作都交给服务器执行，会很累的，万一再有错的话，还得返回给客户端，白白增加服务器的负担。

2）所以我们用的是PreparedStatement，这个会在客户端初步分析SQL（看看是否有错），没问题的话再发送给服务器。

3）PreparedStatement中的两个方法是我们常用的：executeQuery()---用于查询操作返回的是个结果集和executeUpdate()---用于插入，删除，修改操作，返回的是一个整型（表示多少条SQL语句成功执行）

6.当我们执行完SQL操作后，一定要释放资源，关闭顺序一定要注意！先创建的对象后关闭，后创建的对象先关闭。

#增改查操作要关闭的资源
        statement.close();
        connection.close();


#查操作就得多关闭一个资源(结果集)
        resultSet.close();
        statement.close();
        connection.close();

拓展知识（也要看！！）

SQL语句设置为动态传入值

对于上面的示例，SQL语句代码是写死的，我们也可设置为动态的。

1.可以采用字符串拼接，但是非常非常非常非常不建议！！（很危险）

String name = "张三";
String sql1 = "select * from student where name = " + name;

2.使用占位符的方法，PreparedStatement提供的。

使用?作为占位符，占一个位置，后续PreparedStatement会把变量的数值带入到问号中。

        String sql = "insert into student values(?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,1);
        statement.setString(2,"张三");

#还要setDouble,等等，第一个参数表示第几个占位符，第二个表示在对应占位符传入的值
#删除和修改与插入用法一样

查询操作，Java打印结果集

        ResultSet resultSet = statement.executeQuery();
        //遍历
        while (resultSet.next()) {
            //针对某一行进行处理
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            System.out.println("id:" + id + ",name:" + name);
        }

刚开始位置指向的是字段名，通过next()来一个一个移动，获取对应行的内容。到最后一行后，光标再执行next()就会返回false此时也就读取完毕了。