MySQL：索引事务以及Java操作MySQL

最新推荐文章于 2024-06-07 23:00:33 发布

胖了你都蹲不下来撸猫

最新推荐文章于 2024-06-07 23:00:33 发布

阅读量706

点赞数 8

分类专栏：数据库自学用品文章标签： mysql java 数据库

本文链接：https://blog.csdn.net/m0_50444008/article/details/136981315

版权

数据库自学用品专栏收录该内容

4 篇文章 0 订阅

订阅专栏

10. 索引

引入索引能够提高查询速度，但是也会引入两个问题

10.1 索引带来的问题

目录本身要占据存储空间
索引能够提高查询速度，可能会拖慢增删改的速度：后续对数据进行增删改，都是要同步更新索引的

10.2 索引的使用

10.2.1 查看索引

show index from 表名;

针对主键这样的列，会自动生成索引
unique，foreign key也都是会自带索引（父表中删除修改数据的时候，要去子表查询，为了让子表这里也查的快，对于外键列也要有索引）

10.2.2 创建索引

create index 索引名字 on 表名(列名);

针对某一列创建索引之后，后续查询的时候，也必须得针对这一列条件查询，才能够通过索引来提速
索引的创建也是一个危险操作：如果表本身很大，创建索引会引起大量的CPU/硬盘IO消耗，很可能把数据库搞挂

如何给一个已经包含大量数据的表添加索引

部署新的数据库，用新的数据库代替旧的数据库

另外搞个机器，也搭建好一样的数据库服务器
创建表，建立索引
把旧的数据库的数据，导入到新的数据库中
数据导好之后，把应用程序的请求切换到新的服务器上

10.2.3 删除索引

drop index 索引名 on 表名;

删除索引也是比较危险的操作，会涉及到大量的IO，就可能把MySQL主机搞挂了

数据库是非常关键的，实际开发中，确保数据库的安全、稳定、可用是基本方针

10.3 索引的核心内容：底层数据结构

面试的经典问题，MySQL相关的面试题，具体SQL的考察很少出现，大概率考个多表查询，面试中更高频的问题

索引：谈一谈索引内部的结构是怎么样的
事务

索引一定是引入了一些额外的数据结构，来增加查询速度，默认情况下，进行条件查询操作，就是遍历表，一条一条数据都带入条件，引入索引，就是要通过其他的数据结构，加快查询的速度，减少遍历表的可能性

哪些数据结构，能够加快查询速度

顺序表：随机访问
链表：中间位置增删
栈/队列：特殊位置的操作
二叉搜索树（红黑树）：普通的二叉树最坏变成链表，时间复杂度 $O (N)$ ，如果是比较平衡的树， $O (l o g N)$
哈希表：只能查询key相等的情况，无法进行范围查询，经过hash函数的映射，原来key之间的大小关系，不能反映到计算出来的hash值的大小关系，也无法决定下标的大小关系

红黑树和哈希表都不适合给数据库使用

为什么TreeMap/TreeSet不使用AVL树，而使用红黑树

红黑树本质上是一个没那么严格的平衡二叉搜索树，AVL树则是一个非常严格的平衡二叉搜索树，红黑树触发旋转的概率远远低于AVL树，虽然没有AVL树那么平衡，但是查询时候的速度，并没有差多少

在哈希表中，查询元素时间复杂度是多少

谈到哈希表的时候，往往不谈最坏的，就认为是 $O (1)$ ，然而面对哈希冲突，一旦出现链表/红黑树，就不是严格的 $O (1)$ ，即使不是 $O (1)$ 也绝对不是 $O (N)$ ，这个哈希表中，所有的数据加到一起的数量是N，链表的长度，当然不是N，虽然理论上存在，所有数据都出现在一个链表上的极端情况，这个情况认为工程上不会存在，除非你故意构造一个特别特殊的hash函数，最多可以设链表的最大长度为M，复杂度为 $O (M)$ ，另外，在使用hash表的时候，可以在合适的时机，对哈希表进行扩容，并且选择合适的哈希函数，就可以确保每个链表上的元素都不是很多，近似认为是 $O (1)$ 了，比如哈希表上的每个链表，长度是100，从长度100的链表上查询元素，虽然要遍历，但是实际上这个操作和 $O (1)$ 差不了多少

归根结底，还是可以把哈希查询操作视为是 $O (1)$

红黑树为什么不适合给数据库使用

红黑树里面的元素是有序的，可以进行范围查找，在红黑树里，找到中序遍历的下一个后继元素，这样的操作，并不高效，很有可能需要往父亲节点上一系列回溯，才能找到后继，这个原因虽然可以通过“线索化”的方式来解决，但是要付出更多的存储空间
红黑树是二叉搜索树，当元素非常多的时候，就会使树的高度变得很高，树的高度越高，进行查询的效率就比较低，高度每增加一层，比较次数就增加1，数据库的数据/索引大多都是保存在硬盘上的，上述的每一次比较，都需要一次硬盘IO操作

因此，红黑树不太适合于大规模在硬盘上管理数据的场景，于是引入了B树

B树本质上是一个N叉搜索树，每个节点上可以存储多个元素，延伸出多个子树，表示同样数量的数据，需要的节点数就少了，对应树的的高度也降低了，拿着要查询的key在某个结点上比较的时候，确实需要比较更多的次数，但是这里的比较，其实挺高效的

每个节点上的这些key也是有序排列的，比较的时候可以直接进行二分查找
B树也会控制，某个节点上保存的key不会太多，如果插入更多的元素，使得key变多了，就会使得结点分裂出更多的子树出来
多个数据，都是放在一块连续的存储空间上，进行比较的时候，一次硬盘IO就能读出整个节点，就可以直接完成上述比较（进行多次比较，实际上只有一次硬盘IO）

哈希表扩容时负载因子的阈值是多少

（1）阈值可以调整

（2）都是参数，背参数，意义不大

数据库索引数据结构的最终形态是B+树

B+树同样也是N叉搜索树，非叶子节点里面包含的值，都会在子节点中进一步重复出现，叶子结点通过类似于链表这样的链式结构串起来，此时就可以通过上述链式结构非常方便的遍历整个表中的所有数据，同时也非常方便进行范围查询

B+树相比于B树的优势

非常方便进行遍历和范围查询
当前任何一次查询操作，最终都要落到叶子节点完成的，于是查询任何数据，经历的硬盘IO的次数是一样的，查询操作消耗的时间是稳定的
由于叶子节点是数据的全集，对应的，非叶子节点中都是重复出现的数据，就可以把表每一行的数据，最终都关联到叶子节点这一层，非叶子节点只保存一个单纯的key值即可

别忘了，数据库每一行都有很多列，student(id, name, classId, gender, score…)，此时比如使用id这一列来做索引，这里的B+树的非叶子节点，都只需要保存一个id这样的值就行了，到了叶子结点，每个叶子结点不光要保存id，还要把后续的name，classId等信息也保存起来，这样组织之后，非叶子节点占用的空间就比较小，此时非叶子节点可以缓存到内存中，查询速度大大提高了

针对哪个列创建索引，就是针对哪个列构建B+树，主键索引的B+树，叶子节点是带有数据行的，其他的列的索引，叶子节点，则是主键id，针对非主键列进行索引查询，查到的结果是一个主键id，还需要去主键索引中再做一次查询（称为“回表”）

【注意】

B+树这个结构，是一直存在的，如果你定义了主键，自然就是按照主键来建立，如果没定义主键，会有自带的隐藏的列，建立
B+树存在的前提，使用了innodb这个存储引擎，MySQL支持多种存储引擎，不同的存储引擎使用的索引的数据结构是不同的，innodb是最常用的，也是面试考的

10.4 索引总结

索引的基本概念
和索引相关的SQL以及进行什么样的查询，能够命中索引
索引背后的结构，以及这套结构的优点

11. 事务

11.1 事务的概念

事务：把多个操作打包成一个整体，就能够保证，这个整体要么都执行成功，要么就一个都不执行，能够有效避免，部分执行，部分未执行，产生的一些“中间状态引起的”问题

把多个操作打包成一个整体，称为“原子性”（atom）
这里并非是真的没有执行，事务中的若干个SQL必然是要一条一条地执行，事务能够保证，当执行到某一条的时候，如果出现问题了，数据库能够自动地把前面SQL造成的影响给回复回去，恢复如初，看起来就好像一条SQL都没执行的样子，这里的“翻新操作”，称为回滚（rollback）
数据库事务的原子性，核心就是通过回滚机制来保证的
为了实现回滚机制，数据库会在执行事务的时候，记录日志，数据库的日志是写入到硬盘的文件中，当事务最终都执行完毕，中间没有差错，这些记录的内容就可以不要了，但是如果执行事务的过程中出现了问题，MySQL就可以根据日志中记录的内容来进行恢复操作，只要是数据库处于正常工作的状态，就能够始终保证事务前面进行的操作被正确回滚（关系型数据库一般都能够支持这一点）
- 之前进行了新增操作，就把数据删除掉
- 之前进行了删除操作，就把数据新增回来
- 之前进行了修改操作，就把数据改回去
- 之前进行了查询操作，不影响，不需要任何恢复行为

11.2 事务的基本特性

原子性：有回滚机制，能够触发还原（事务最核心的部分）
一致性：执行事务之前和执行事务完毕之后，数据是一致的（不会出现“对不上”的情况）

也是和回滚有关的，一旦触发了回滚，回滚回去的数据得是对的，如果顺利执行没有触发回滚，数据也是要符合要求的。一致性是对“数据正确”的承诺
持久性：此处的之久，指的是程序重启/主机重启，数据仍然存在，不仅仅是数据库，后续但凡见到“持久性”都是这个意思

执行事务对数据库产生的修改，就会在硬盘上持久保存，重启之后仍然存在，这里内部的原理比较复杂，MySQL是一个非常非常复杂的软件，背后做了很多事情，提交一个事务，背后一系列的内存/硬盘之间的交互联动，但是最终能够保证提交后的事务一定会落到硬盘上
隔离性：描述的是数据库并发（多个客户端，同时给服务器，发起事务）执行事务时，产生的情况。（面试高频）
1. 脏读问题：数据库中，如果有事务A和事务B，事务A针对某个表做出了一些修改，在事务A提交之前，事务B就对这里的数据进行了读取，最终就可能出现A后续的操作又把上述数据进行了修改，导致最终B读到的数据和A提交的数据是不同的
  
  解决办法：针对“写操作”加锁，执行A的过程中，B就不能执行了，相当于降低了“并发能力”，也就会降低数据库服务器的处理效率，提高了“隔离性”，也提高了数据的准确性
2. 不可重复读问题：事务A针对数据进行修改，提交，接下来事务B进行读取数据（事务B这里的多个SQL都要进行读操作），在执行B的过程中，又有一个事务C，又针对数据进行了修改，就会使得B里面的不同读操作，读出来的结果不一样
  
  解决办法：针对“读操作”加锁，并发程度进一步降低，效率也随之降低，“隔离性”又进一步提高，数据的准确性也会提高
3. 幻读问题：事务A先修改并提交数据，事务B进行读数据，事务C没有修改B读的数据，但是给对应的表进行了新增数据/删除数据等操作，导致事务B中，读到的数据集不同（已有的数据内容是一致的，数据的条数增加/减少），可以视为是“不可重复读”的特殊情况
  
  解决办法：“串行化”使所有的事务严格的按照“一个接一个”的方式执行，完全没有并发，此时执行效率是最低的，隔离性也是最高的，数据也是最准确的

11.3 MySQL隔离级别

read uncommitted：允许读取其他事务未提交的数据（脏读+不可重复读+幻读：并发程度最高，隔离性最低）
read committed：只能读取其他事务提交后的数据（不可重复读+幻读：并发程度降低，隔离性提高）
repeatable read：针对读操作和写操作都加锁（幻读：并发程度又降低，隔离性又提高）
串行化serializable：所有事务都是串行执行的（并发基本没有，隔离性最高）

12. 通过Java操作MySQL（重点）

日常开发中涉及到数据库的相关内容，都是通过代码来进行操作的，手动操作的情况比较少（定位bug之类，构造测试数据…）

12.1 JDBC

Java通过JDBC这样的技术来操作MySQL

MySQL是一个基于C/C++实现的数据库，本身也提供了一系列的API（应用程序编程接口：application programming interface），让程序员调用，从而通过代码来操作数据库
对于Java来说，Java提供了“标准库”，只要安装了Java，此时就可以使用标准库中的类和方法（标准库的API），也可以使用其他人写好的类和方法（第三方库的API）
有的库，提供的API特别多，形成了一系列的体系，这种情况也可以称为SDK（软件开发工具包）
每个数据库厂商额外写一些代码，能够按照Java提供的这一套标准把原来的原生API重新封装一下，后续程序员只需要学习掌握Java这一套API就可以无缝切换各种数据库了
应用程序 --> JDBC --> MySQL提供的“接口转换”程序 --> MySQL原生API --> 操作MySQL
这样的接口转换称为数据库驱动

12.2 MySQL驱动包

JDBC是Java标准库提供的，安装了JDK就自带JDBC，但是，使用JDBC操作MySQL就需要下载并导入MySQL的驱动包，直接去中央仓库下载

12.3 导入项目

项目中随便创建一个目录，把下载的.jar拷贝到lib中
右键这个目录，Add as Library（告诉IDEA，当前这个目录是存放第三方库的目录，此时IDEA就能够识别到咱们拷贝进来的驱动包了）

12.4 编写代码

12.4.1 创建数据源(DataSource)

要想编写jdbc，还需要准备好数据库和数据表
DataSource数据源：要操作的数据库，数据是在哪里，在MySQL中，就需要设定好，mysql服务器的位置，要访问的数据库的名字，访问数据库的用户名和密码
URL：协议的名称://ip地址:端口号/数据库名?参数=值&参数=值
URL描述了服务器/服务器上的资源在网络中存在的位置
MySQL中可以手动创建各种名字的用户，默认会自带一个root用户，root是一个管理员账户（权限最大的账户）
DataSource是要能够对接到各种不同数据库的，不同的数据库，设置数据源的方式，不一样
对于MySQL设置url、user、password，对于别的数据库就不一定了
进行客户端-服务器之间通信的时候，常见有两种通信模式：有连接（JDBC，类似于打电话）和无连接（类似于发微信）

DataSource dataSource = new MysqlDataSource();
((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3006/java111?characterEncoding=utf8&useSSL=false");
((MysqlDataSource) dataSource).setUser("root");
((MysqlDataSource) dataSource).setPassword("123456");

耦合：一个模块修改了代码，对别的模块的影响

内聚：实现某个功能的时候，如果和这个功能相关的代码，集中放到一起，就认为是“高内聚”

耦合描述的是模块之间的事情，内聚描述的是模块内部之间的事情（也可能是模块之间）

12.4.2 和数据库服务器建立连接

Connection connection = dataSource.getConnection();

异常：受查异常、非受查异常
这个Connection对象，就表示客户端和服务器之间的“连接对象”
在getConnection很可能失败（服务器没有接受连接）
- 数据库服务器没有正确启动
- url写错了
- 用户名写错了
- 密码写错了
- 网络断开了

12.4.3 构造一个操作数据库的SQL语句

String sql = "insert into test values(1, '张三')";
PreparedStatement preparedStatement = connection.prepareStatement(sql);

System.out.println("请输入学号：");
int id = scanner.nextInt();
System.out.println("请输入姓名：");
String name = scanner.nextLine();

String sql = "insert into test values(?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
preparedStatement.setString(2,name);

Statement statement = 仅仅是表示一个普通的语句对象
PreparedStatement preparedStatement = 则是一个带有“预编译”功能的语句对象
一个字符串SQL发送到数据库服务器上，是要先对SQL进行解析，进行各种校验（判定SQL是否符合语法要求等）才能执行，这个解析操作也是需要花费一定的开销的，虽然开销不是很大，但是MySQL服务器要同时给多个客户端提供服务，为了减轻数据库服务器的负担，就可以在客户端这边俩完成，此时把解析后的结果发给服务器，服务器直接执行即可

12.4.4 执行SQL

int n = preparedStatement.executeUpdate();
System.out.println(n);

//3.构造一个操作数据库的SQL语句
Statement statement = connection.createStatement();
String sql = "select * from test";


//4.执行SQL，把刚才解析好的语句发给数据库服务器

ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
    int id = resultSet.getInt("id");
    String name = resultSet.getString("name");
    System.out.println("id = " + id + ", name = " + name);
}

//5.执行完毕
statement.close();
connection.close();

executeUpdate()：insert、update、delete
executeQuery()：select
执行这个方法，就会在内部，给数据库服务器发起请求，请求中就是包含了解析后的SQL，等待数据库执行SQL，过一会数据库执行完SQL，返回响应，这个方法在获取到相应，并且把数据库返回的结果通过返回值体现出来

12.4.5 执行完毕释放资源

preparedStatement.close();
connection.close();

主要释放语句对象和连接对象，DataSource是不必释放的
先创建的后释放
Java有垃圾回收机制（GC），内存这个资源，Java是能够帮我们自动回收的，其他资源Java无能为力，需要手动释放

12.5 常见错误

ip/port错误：Coummunications link failure
数据库名字不对：Unknown database ‘java’
url参数写错：Unsupported character encoding ’ ’
用户名/密码错误