全面解析MySQL(5)——“索引、事务、JDBC”三大核心

1.索引

1.1 概述

MySQL索引：是一种特殊的数据结构，用于快速查找数据库表中的数据。它类似于书籍的目录，通过存储表中某些列的值及其对应的物理位置，减少查询时需要扫描的数据量，从而提升查询效率 优势：

• 大幅提高查询速度，降低数据库IO成本
• 通过索引对数据进行排序，减少CPU负担

劣势：

• 索引占用额外的物理空间
• 进行增删改操作时效率可能反而低(修改数据的同时还要修改对应的索引)

1.2 索引的数据结构

1.2.1 hash表

以键值对的方式存储数据，通过key来快速查找对应的value，时间复杂度理论上可以达到O(1) 缺点：

• 不能进行范围查找
• key之间只能比较是否相等，不能进行模糊匹配

1.2.2 二叉搜索树

二叉搜索树左子树均小于当前节点，右子树均大于当前节点，查询时间复杂度为O(log n)，天然支持排序操作，对于需要排序输出的查询，可以减少额外的排序开销 缺点：

• 可能出现树不分叉的极端情况(子树全在左边或右边)，查询效率不稳定

在这里插入图片描述

• 范围查询需要多次遍历子树，导致额外开销

1.2.3 平衡二叉树

平衡二叉树(如红黑树)保证了树的高度平衡，查找、插入、删除操作的时间复杂度为 O(log n)，有稳定的查询性能 缺点：

• 树的高度较高时，访问深层节点可能触发多次磁盘I/O
• 插入或删除数据时可能触发频繁的旋转操作，维护平衡的开销较大

1.2.4 B树

B树：是一种自平衡的多路搜索树，广泛应用于数据库和文件系统中。其核心设计目标是减少磁盘I/O操作，通过保持树的高度较低来提高查询效率。B树的每个节点可以包含多个键和子节点指针，具体数量由阶数(order)决定 特点：

• 每个节点存储多个键和子节点指针。例如：键k1和k2将数据划分为(负无穷，k1)，(k1，k2)，(k2，正无穷)三个区间，那么对应p0、p1、p2三个子节点指针

在这里插入图片描述

• 每个节点存储键和数据，且键从小到排列
• 父节点中的键不会出现在子节点中
• 所有叶子节点处于同一层，相互之间没有指针连接

缺点：

• 进行范围查询时仍然需要多次遍历子树
• 如果节点中存储的数据过大，那么能存储的键和子节点指针会减少，可能导致树的高度增加

1.2.5 B+树

B+树是MySQL存储引擎InnoDB使用的数据结构，它在B树的基础上又进行进一步优化

• 只有叶子节点才会存储数据，其他节点只存储键值
• 叶子节点之间使用双向指针连接，相当于双向链表

在这里插入图片描述

B+树每次都需要遍历到最底层才能拿到数据，看似IO开销大于B树。但实际上，B+树的非叶子节点不存储数据，能存储的键和子结点指针的数量明显大于B树，B+树的高度也低于B树

1.3 索引的类型

1.3.1 主键索引

主键索引(Primary Key Index)是一种特殊的数据库索引，用于唯一标识表中的每一行数据。具有以下特性：

• 唯一性：不允许重复值，确保每行数据唯一
• 非空性：主键列不允许为null
• 自动创建索引：在MySQL定义主键时会自动为其创建索引，加速查询

1.3.2 普通索引

普通索引是最基础的索引类型，用于加速数据的查询操作。它不强制要求列值的唯一性，允许重复值和空值。普通索引的主要目的是提高查询效率，尤其是在where、join、order by等操作中

1.3.3 唯一索引

唯一索引(Unique Index)是数据库中的一种约束，确保索引列或列组合中的值唯一，允许包含空值

1.3.4 全文索引

全文索引是一种用于快速检索文本内容的技术，通过建立单词或短语的索引，实现对大规模文本数据的高效查询，适用于模糊匹配

1.3.5 聚集索引&非聚集索引

聚集索引(Clustered Index)决定了表中数据的物理存储顺序，每个表只能有一个聚集索引，索引的键值顺序与数据行的物理顺序一致

• 数据行按索引键排序存储
• 叶子节点直接包含实际数据页
• 默认使用表中的主键索引作为聚集索引，没有主键则选择第一个满足所有列都是not null的唯一索引作为聚集索引(create table时如果没有primary key那么第一个not null+unique会自动升级为primary key，与这里的情况有相似之处)。如果都没有则隐式创建隐藏聚集索引

非聚集索引(Non-Clustered Index)与数据物理顺序无关，仅存储索引键值和指向实际数据的指针，一个表可创建多个非聚集索引

• 索引结构与数据存储分离，叶子节点包含指向数据行的指针
• 查询需二次查找(回表)获取完整数据

回表：是数据库查询中的一种现象，指当使用非聚集索引查找数据时，无法直接从索引中获取全部所需列，需通过索引中的指针(通常是主键id)回到主表中获取完整数据行

在这里插入图片描述

1.4 使用索引

以普通索引为例 创建索引

#创建表时指定索引列
create table 表名(
id int,
name varchar(128),
index(name));
#修改表中的列为普通索引
create table 表名(
id int,
name varchar(128));
alter table 表名 add index(列名);
#单独创建索引并指定索引名
create table 表名(
id int,
name varchar(128));
create index 索引名 on 表名(列名);

查看索引

show keys from 表名;
show index from 表名;
desc 表名;

删除索引

alter table 表名 drop index 索引名;

2.事务

2.1 概念

MySQL事务：指一组SQL操作的集合，这些操作要么全部执行成功，要么全部失败回滚。主要目的时保证数据库操作的原子性、一致性、隔离性和持久性，即ACID特性

• 原子性(Atomicity)：事务中的所有操作要么全部完成，要么全部不完成。如果事务中的任何操作失败，整个事务将回滚到初始状态
• 一致性(Consistency)：事务执行前后，数据库从一个一致状态转移到另一个一致状态。不会因为事务的执行而破坏数据库的完整性
  • 例如：银行卡A有100元，银行卡B有100元，两卡总共有200元。假设A给B转账50元，那么A有50元，B有150元，总数还是200元
• 隔离性(Isolation)：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。MySQL提供了不同的事务隔离级别来控制事务之间的可见性
• 持久性：事务一旦提交，其对数据库的修改就是永久性的，即使系统崩溃也不会丢失

2.2 使用事务

2.2.1 基础语法

#开启事务
start transaction;
#或
begin;

#提交事务
commit;

#回滚事务
rollback;

2.2.2 开启事务后执行回滚

#开启事务
begin;
#插入数据成功
insert into demo1 values (4,"赵六");
#回滚至事务开启时的状态
rollback;

select * from demo1;

在这里插入图片描述

2.2.3 开启事务后执行提交

#开启事务
begin;
#插入数据
insert into demo1 values (4,"赵六");
#提交事务
commit;
#回滚事务
rollback;

select * from demo1;

在这里插入图片描述

2.2.4 设置保存点

保存点：是数据库管理中的一个功能，用于在事务执行过程中创建标记点，以便在需要时回滚到该标记点而非整个事务起点

begin;
insert into demo1 values (5,"田七");
#设置保存点A
savepoint A;
insert into demo1 values (6,"周八");
#设置保存点B
savepoint B;
#回滚至保存点A,而不是事务开始时
rollback to A;
select * from demo1;

在这里插入图片描述

2.3 隔离级别

MySQL支持读未提交、读已提交、可重复读、串行化四种事务隔离级别，用于控制事务之间的可见性和影响

2.2.1 读未提交

READ UNCOMMITTED：最低隔离级别，允许事务读取未提交的数据变更 常见问题：在读未提交隔离级别下，数据库对于数据的读取不做任何限制，并发性很高但也会引发大量的数据安全问题。例如：事务A执行插入语句，在未提交的情况下可以被事务B读取，当事务A进行回滚时，事务B读取到的数据将没有意义，这种现象叫做脏读

在这里插入图片描述

提升至读已提交隔离级别

在这里插入图片描述

2.2.2 读已提交

READ COMMITTED：事务只能读取已提交的数据变更，避免脏读 常见问题：事务A针对某条数据进行查询，然后事务B对这条数据进行修改，当事务A再次查询该数据时会得到事务B修改后的值。即事务A以相同的查询条件得到不一样的查询结果，这种现象叫做不可重复读

在这里插入图片描述

提升至可重复读隔离级别

在这里插入图片描述

2.2.3 可重复读

REPEATABLE READ(MySQL默认隔离级别)：确保同一事务中多次读取同一数据的结果一致，避免脏读和不可重复读 常见问题：假设事务A进行某一范围的查询，然后事务B在该范围内插入一条新的数据，事务A再次查询时会返回原有数据+事务B新插入的数据。即同一事务内多次执行相同范围的查询，返回的行数不同，这种现象叫做幻读

在这里插入图片描述

在这里插入图片描述

上述查询结果中，明明没有查询到id=2的结果，以为只有一条数据。但是插入id=2的数据时发生了主键冲突

2.2.4 串行化

SERIALIZABLE：最高隔离级别，完全串行化执行事务，避免脏读、不可重复读和幻读，但性能最差

3.JDBC编程

3.1 概述

JDBC(Java Database Connectivity)是Java提供的用于执行SQL语句的API，允许Java程序与多种关系型数据库交互。它为数据库操作提供统一接口，屏蔽底层数据库差异，开发者无需针对不同数据库编写特定代码 JDBC核心组件

• DriverManager：管理数据库驱动，建立与数据库的连接
• Connection：表示与数据库的会话(类似两个人打电话)，用于创建Statement对象
• PreparedStatement：执行SQL语句并返回结果
• ResultSet：封装SQL查询结果，提供遍历数据的方法

3.2 使用JDBC

3.2.1 创建项目&引入依赖

• 1.创建Maven项目

在这里插入图片描述

• 2.引入MySQL驱动 8.4.0是驱动版本，需要和MySQL本体的版本对应

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>

3.2.2 使用JDBC

• 1.初始化

        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setServerName("jdbc:mysql://127.0.0.1:3306/java117?characterEncoding=utf8&useSSL=False");
        dataSource.setPort(3306);
        dataSource.setUser("root");
        dataSource.setPassword("123456");

• 2.建立连接

		Connection connection = 
				dataSource.getConnection();//需要处理SQLException

• 3.编写sql语句

		String sql1 = "select * from 数据表名";
		String sql2 = "insert into 数据表名 values ();"
		//可以使用?作为占位符
		String sql = "insert into jdbc (id,name,age) values(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, 1);
        preparedStatement.setString(2, "张三");
        preparedStatement.setInt(3, 18);

• 4.预编译sql

		PreparedStatement preparedStatement1 = 
				connection.prepareStatement(sql1);
		PreparedStatement preparedStatement2 = 
				connection.prepareStatement(sql2);

• 5.执行sql并返回结果集

		//5.1 如果是查询使用executeQuery()方法
        //5.2如果是增删改使用executeUpdate()方法
        ResultSet resultSet1 = preparedStatement1.executeQuery();
        ResultSet resultSet2 = preparedStatement2.executeUpdate();

• 5.遍历结果

		//可以根据数字获取指定列,也可以指定列名
		while (resultSet1.next()) {
        		System.out.println(resultSet1.getString(1) + ","
                		+ resultSet.getString(2) + ","
                    	+ resultSet.getString(3));
        }

• 6.释放资源

		//需要处理异常
		resultSet.close();
		preparedStatement.close();
        connection.close();