目录
一、索引
1、索引的引出
2、存储引擎的概念
3、索引的使用
(1)测试用例
(2)索引的创建
(3)索引的删除
(4)查看索引
4、索引的数据结构
(1)了解B树
(2)聚簇索引和非聚簇索引
(3)B+树
二、事务
1、事务的引出
2、事务的概念
3、事务的特性
4、脏读、不可重复读、幻读
5、事务的使用
6、Mysql事务的四种隔离级别
三、JDBC
1、JDBC的概念
2、JDBC的工作原理
3、使用idea进行JDBC具体操作
一、索引
1、索引的引出
索引是一种特殊的文件,包含着对数据表里所有记录的引用指针。可以,创建索引,并指定索引的类型,各类索引有各自的数据结构实现,类似于数组中的下标,因为通过索引去查找数据库中的数据是非常快的。
2、存储引擎的概念
存储引擎就是Mysql对数据库进行增删改查的不同的实现方法
Mysql中经典的两种存储引擎:
MySlAM:在Mysql5.5之前的默认存储引擎,不支持事务,性能比较高
InnoDB:在Mysql5.5之后的默认存储引擎,支持事务,但是性能略低
之后的索引和事务的实现都是基于InnoDB
存储引擎的查看
3、索引的使用
(1)测试用例
-- 创建用户表
DROp TABLE IF EXISTS test_user;
CREATE TABLE test_user (
id_number INT,
name VARCHAr(20) comment '姓名',
age INT comment '年龄',
create_time timestamp comment '创建日期'
);
-- 构建一个8000000条记录的数据
-- 构建的海量表数据需要有差异性,所以使用存储过程来创建, 拷贝下面代码就可以了,暂时不用理解
-- 产生名字
drop function if exists rand_name;
delimiter $$
create function rand_name(n INT, l INT)
returns varchar(255)
begin
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
if i=0 then
set return_str = rand_string(l);
else
set return_str =concat(return_str,concat(' ', rand_string(l)));
end if;
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;
-- 产生随机字符串
drop function if exists rand_string;
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare lower_str varchar(100) default
'abcdefghijklmnopqrstuvwxyz';
declare upper_str varchar(100) default
'ABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
declare tmp int default 5+rand_num(n);
while i < tmp do
if i=0 then
set return_str =concat(return_str,substring(upper_str,floor(1+rand()*26),1));
else
set return_str =concat(return_str,substring(lower_str,floor(1+rand()*26),1));
end if;
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;
-- 产生随机数字
drop function if exists rand_num;
delimiter $$
create function rand_num(n int)
returns int(5)
begin
declare i int default 0;
set i = floor(rand()*n);
return i;
end $$
delimiter ;
-- 向用户表批量添加数据
drop procedure if exists insert_user;
delimiter $$
create procedure insert_user(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into test_user values ((start+i) ,rand_name(2, 5),rand_num(120),CURRENT_TIMESTAMP);
until i = max_num
end repeat;
commit;
end $$
delimiter ;
-- 执行存储过程,添加8000000条用户记录
call insert_user(1, 8000000);
(2)索引的创建
索引是一种特殊的文件,包含着对数据表里所有记录的引用指针。可以,创建索引,并指定索引的类型,各类索引有各自的数据结构实现,类似于数组中的下标,因为通过索引去查找数据库中的数据是非常快的。
2、存储引擎的概念
存储引擎就是Mysql对数据库进行增删改查的不同的实现方法
Mysql中经典的两种存储引擎:
MySlAM:在Mysql5.5之前的默认存储引擎,不支持事务,性能比较高
InnoDB:在Mysql5.5之后的默认存储引擎,支持事务,但是性能略低
之后的索引和事务的实现都是基于InnoDB
存储引擎的查看
3、索引的使用
(1)测试用例
-- 创建用户表
DROp TABLE IF EXISTS test_user;
CREATE TABLE test_user (
id_number INT,
name VARCHAr(20) comment '姓名',
age INT comment '年龄',
create_time timestamp comment '创建日期'
);
-- 构建一个8000000条记录的数据
-- 构建的海量表数据需要有差异性,所以使用存储过程来创建, 拷贝下面代码就可以了,暂时不用理解
-- 产生名字
drop function if exists rand_name;
delimiter $$
create function rand_name(n INT, l INT)
returns varchar(255)
begin
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
if i=0 then
set return_str = rand_string(l);
else
set return_str =concat(return_str,concat(' ', rand_string(l)));
end if;
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;
-- 产生随机字符串
drop function if exists rand_string;
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare lower_str varchar(100) default
'abcdefghijklmnopqrstuvwxyz';
declare upper_str varchar(100) default
'ABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
declare tmp int default 5+rand_num(n);
while i < tmp do
if i=0 then
set return_str =concat(return_str,substring(upper_str,floor(1+rand()*26),1));
else
set return_str =concat(return_str,substring(lower_str,floor(1+rand()*26),1));
end if;
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;
-- 产生随机数字
drop function if exists rand_num;
delimiter $$
create function rand_num(n int)
returns int(5)
begin
declare i int default 0;
set i = floor(rand()*n);
return i;
end $$
delimiter ;
-- 向用户表批量添加数据
drop procedure if exists insert_user;
delimiter $$
create procedure insert_user(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into test_user values ((start+i) ,rand_name(2, 5),rand_num(120),CURRENT_TIMESTAMP);
until i = max_num
end repeat;
commit;
end $$
delimiter ;
-- 执行存储过程,添加8000000条用户记录
call insert_user(1, 8000000);
(2)索引的创建
存储引擎就是Mysql对数据库进行增删改查的不同的实现方法
Mysql中经典的两种存储引擎:
MySlAM:在Mysql5.5之前的默认存储引擎,不支持事务,性能比较高
InnoDB:在Mysql5.5之后的默认存储引擎,支持事务,但是性能略低
之后的索引和事务的实现都是基于InnoDB
存储引擎的查看
(1)测试用例
-- 创建用户表
DROp TABLE IF EXISTS test_user;
CREATE TABLE test_user (
id_number INT,
name VARCHAr(20) comment '姓名',
age INT comment '年龄',
create_time timestamp comment '创建日期'
);
-- 构建一个8000000条记录的数据
-- 构建的海量表数据需要有差异性,所以使用存储过程来创建, 拷贝下面代码就可以了,暂时不用理解
-- 产生名字
drop function if exists rand_name;
delimiter $$
create function rand_name(n INT, l INT)
returns varchar(255)
begin
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
if i=0 then
set return_str = rand_string(l);
else
set return_str =concat(return_str,concat(' ', rand_string(l)));
end if;
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;
-- 产生随机字符串
drop function if exists rand_string;
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare lower_str varchar(100) default
'abcdefghijklmnopqrstuvwxyz';
declare upper_str varchar(100) default
'ABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
declare tmp int default 5+rand_num(n);
while i < tmp do
if i=0 then
set return_str =concat(return_str,substring(upper_str,floor(1+rand()*26),1));
else
set return_str =concat(return_str,substring(lower_str,floor(1+rand()*26),1));
end if;
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;
-- 产生随机数字
drop function if exists rand_num;
delimiter $$
create function rand_num(n int)
returns int(5)
begin
declare i int default 0;
set i = floor(rand()*n);
return i;
end $$
delimiter ;
-- 向用户表批量添加数据
drop procedure if exists insert_user;
delimiter $$
create procedure insert_user(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into test_user values ((start+i) ,rand_name(2, 5),rand_num(120),CURRENT_TIMESTAMP);
until i = max_num
end repeat;
commit;
end $$
delimiter ;
-- 执行存储过程,添加8000000条用户记录
call insert_user(1, 8000000);
(2)索引的创建
在没有索引的情况下查询某个id的具体信息。此时就是一行行的去查找,直到第667799行
explain
Mysql的引擎分析这个sql语句可能会用到的索引信息,包括扫描的行数
索引的创建需要时间和空间,可以把经常查询的属性作为索引,比如测试用例中的id属性
create index 索引名称 on 表名(属性名);
可以看出来,创建了索引之后大概多出了44mb的内存
在创建所以那之后再次查询id=667799的信息
可以看的出来在使用了索引之后查找的效率提升了千万倍
而对于主键约束,外键约束,唯一约束来说,其实他们就是索引,就是比较特殊的索引
(3)索引的删除
(4)查看索引
4、索引的数据结构
Mysql的所以的实现基于B+树的设计理念
(1)了解B树
(2)聚簇索引和非聚簇索引
聚簇索引(主键索引,一个表只有一个聚簇索引):构建索引树上的每个节点,除了要保存索引列的信息,还要保存这条记录的完整内容,查询速度快,保存的信息多,占用的空间大
非聚簇索引(普通索引,二级索引):一张表可以有多个非聚簇索引,索引树上的每个节点,除了保存索引列的信息之外,还保存了该记录的行号(对应的主键id) ,查询速度慢,根据非聚簇索引先在索引树上找到对应记录,然后根据保存主键id返回聚簇索引找到对应的信息,保存的信息少,占用空间小
聚簇索引(主键索引,一个表只有一个聚簇索引):构建索引树上的每个节点,除了要保存索引列的信息,还要保存这条记录的完整内容,查询速度快,保存的信息多,占用的空间大
非聚簇索引(普通索引,二级索引):一张表可以有多个非聚簇索引,索引树上的每个节点,除了保存索引列的信息之外,还保存了该记录的行号(对应的主键id) ,查询速度慢,根据非聚簇索引先在索引树上找到对应记录,然后根据保存主键id返回聚簇索引找到对应的信息,保存的信息少,占用空间小
(3)B+树
二、事务
1、事务的引出
二、事务
1、事务的引出
针对这样的案列,就需要用事务来保证SQL全部执行或者全部失败。
2、事务的概念
事务就是将若干个SQL打包成一个整体,实际执行的时候,这个整体要么全部执行,要么全部失败,在不同的环境中,都可以有事务。对应在数据库中,就是数据库事务。
若执行过程出现了突发情况,某些操作执行不下去了,Mysql就会保证突发情况恢复之后,通过事务的‘回滚’操作进行数据的还原(Mysql的binglog文件记录了所有表的数据修改动作)
3、事务的特性
ACID:原子性,持久性,隔离性,一致性
①原子性:事务中最核心的操作,一个事务中的所有操作,要么全部执行成功,要么全部执行失败(当执行失败后,就通过‘回滚’操作恢复数据)
②持久性:一个事务全部执行完成之后,这个事务堆数据库的所有修改都是永久的,不会丢失
③一致性:一个事务执行前后的数据都是一种合法状态,事务永远都是从一个一致性到另一个一致性状态。
④隔离性:多个并发事务访问数据库时,事务之间是相互隔离的,一个事务不应该干扰到其他事务,不同的事务之间相互隔离。
4、脏读、不可重复读、幻读
①脏读:脏读又称无效数据的读出,是指在数据库访问中,事务T1将某一值修改,然后事务T2读取该值,此后T1因为某种原因撤销对该值的修改,这就导致了T2所读取到的数据是无效的,值得注意的是,脏读一般是针对于update操作的。
②不可重复读:事务A在执行读取操作,由整个事务A比较大,前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据,比如此时读取了小明的年龄为20岁,事务B执行更改操作,将小明的年龄更改为30岁,此时事务A第二次读取到小明的年龄时,发现其年龄是30岁,和之前的数据不一样了,也就是数据不重复了,系统不可以读取到重复的数据,成为不可重复读。
③事务A在执行读取操作,需要两次统计数据的总量,前一次查询数据总量后,此时事务B执行了新增数据的操作并提交后,这个时候事务A读取的数据总量和之前统计的不一样,就像产生了幻觉一样,平白无故的多了几条数据,成为幻读。
5、事务的使用
事务就是将若干个SQL打包成一个整体,实际执行的时候,这个整体要么全部执行,要么全部失败,在不同的环境中,都可以有事务。对应在数据库中,就是数据库事务。
若执行过程出现了突发情况,某些操作执行不下去了,Mysql就会保证突发情况恢复之后,通过事务的‘回滚’操作进行数据的还原(Mysql的binglog文件记录了所有表的数据修改动作)
ACID:原子性,持久性,隔离性,一致性
①原子性:事务中最核心的操作,一个事务中的所有操作,要么全部执行成功,要么全部执行失败(当执行失败后,就通过‘回滚’操作恢复数据)
②持久性:一个事务全部执行完成之后,这个事务堆数据库的所有修改都是永久的,不会丢失
③一致性:一个事务执行前后的数据都是一种合法状态,事务永远都是从一个一致性到另一个一致性状态。
④隔离性:多个并发事务访问数据库时,事务之间是相互隔离的,一个事务不应该干扰到其他事务,不同的事务之间相互隔离。
4、脏读、不可重复读、幻读
①脏读:脏读又称无效数据的读出,是指在数据库访问中,事务T1将某一值修改,然后事务T2读取该值,此后T1因为某种原因撤销对该值的修改,这就导致了T2所读取到的数据是无效的,值得注意的是,脏读一般是针对于update操作的。
②不可重复读:事务A在执行读取操作,由整个事务A比较大,前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据,比如此时读取了小明的年龄为20岁,事务B执行更改操作,将小明的年龄更改为30岁,此时事务A第二次读取到小明的年龄时,发现其年龄是30岁,和之前的数据不一样了,也就是数据不重复了,系统不可以读取到重复的数据,成为不可重复读。
③事务A在执行读取操作,需要两次统计数据的总量,前一次查询数据总量后,此时事务B执行了新增数据的操作并提交后,这个时候事务A读取的数据总量和之前统计的不一样,就像产生了幻觉一样,平白无故的多了几条数据,成为幻读。
5、事务的使用
①脏读:脏读又称无效数据的读出,是指在数据库访问中,事务T1将某一值修改,然后事务T2读取该值,此后T1因为某种原因撤销对该值的修改,这就导致了T2所读取到的数据是无效的,值得注意的是,脏读一般是针对于update操作的。
②不可重复读:事务A在执行读取操作,由整个事务A比较大,前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据,比如此时读取了小明的年龄为20岁,事务B执行更改操作,将小明的年龄更改为30岁,此时事务A第二次读取到小明的年龄时,发现其年龄是30岁,和之前的数据不一样了,也就是数据不重复了,系统不可以读取到重复的数据,成为不可重复读。
③事务A在执行读取操作,需要两次统计数据的总量,前一次查询数据总量后,此时事务B执行了新增数据的操作并提交后,这个时候事务A读取的数据总量和之前统计的不一样,就像产生了幻觉一样,平白无故的多了几条数据,成为幻读。
6、Mysql事务的四种隔离级别
①读未提交:处在该隔离级别的事务可以看到其他已经提交的事务对数据库的修改~~RU
②读已提交:处在该隔离级别的事务可以看到其他已经提交事务对数据库的修改~~ RC
③可重复度:InnoDB引擎默认的隔离级别,一个事务一旦开启,在该隔离级别下,该事务提交之前,多次查询看到的结果是相同的,无论其他事务如何修改数据库,在当前事务在都是不可见的
④串行化:事务最高隔离级别,所有事务串行访问数据库,不会发生冲突,不会产生事务问题,但是不能并发执行了
三、JDBC
1、JDBC的概念
JDBC,即Java Database Connectivity,java数据库连接。是一种用于执行SQL语句的Java API,它是Java中的数据库连接规范。这个API由java.sql.*,javax.sql.* 包中的一些类和接口组成,它为Java开发人员操作数据库提供了一个标准的API,可以为多种关系数据库提供统一访问。
2、JDBC的工作原理
①读未提交:处在该隔离级别的事务可以看到其他已经提交的事务对数据库的修改~~RU
②读已提交:处在该隔离级别的事务可以看到其他已经提交事务对数据库的修改~~ RC
③可重复度:InnoDB引擎默认的隔离级别,一个事务一旦开启,在该隔离级别下,该事务提交之前,多次查询看到的结果是相同的,无论其他事务如何修改数据库,在当前事务在都是不可见的
④串行化:事务最高隔离级别,所有事务串行访问数据库,不会发生冲突,不会产生事务问题,但是不能并发执行了
1、JDBC的概念
JDBC,即Java Database Connectivity,java数据库连接。是一种用于执行SQL语句的Java API,它是Java中的数据库连接规范。这个API由java.sql.*,javax.sql.* 包中的一些类和接口组成,它为Java开发人员操作数据库提供了一个标准的API,可以为多种关系数据库提供统一访问。
2、JDBC的工作原理
JDBC,即Java Database Connectivity,java数据库连接。是一种用于执行SQL语句的Java API,它是Java中的数据库连接规范。这个API由java.sql.*,javax.sql.* 包中的一些类和接口组成,它为Java开发人员操作数据库提供了一个标准的API,可以为多种关系数据库提供统一访问。
3、使用idea进行JDBC具体操作
首先导入对应的jar包
首先导入对应的jar包
package JDBC;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JDBCtest {
public static void main(String[] args) throws SQLException {
//先获取数据源,数据是从那个数据库来的
MysqlDataSource dataSource=new MysqlDataSource();
//配置数据源的属性,用户名,密码,连接ip和端口号
dataSource.setURL("jdbc:mysql://127.0.0.1:3306/day6_8?characterEncoding=utf8&useSSL=false");
//-u root
dataSource.setUser("root");
//-p
dataSource.setPassword("renx1553814535");
//获取数据库连接-根据上述配置发起网络请求,连接对象
//java.sql.Connection
Connection connection= dataSource.getConnection();
//SQL语句
String sql="insert into user(username,password) values(?,?)";
//获取执行sql的Statement对象,mysql中每一个sql语句的执行就对应一个Statement对象
PreparedStatement statement=connection.prepareStatement(sql);
statement.setString(1,"张三");
statement.setString(2,"1234");
//执行sql语句,增删改调用executeUpdata,返回受影响的行数
int rows=statement.executeUpdate();
//执行
System.out.println("插入成功,插入"+ rows +"行");
//关闭资源
connection.close();
}
}
package JDBC;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCtest1 {
public static void main(String[] args) throws SQLException {
//先获取数据源,数据是从那个数据库来的
MysqlDataSource dataSource=new MysqlDataSource();
//配置数据源的属性,用户名,密码,连接ip和端口号
dataSource.setURL("jdbc:mysql://127.0.0.1:3306/day6_8?characterEncoding=utf8&useSSL=false");
//-u root
dataSource.setUser("root");
//-p
dataSource.setPassword("renx1553814535");
//获取数据库连接-根据上述配置发起网络请求,连接对象
//java.sql.Connection
Connection connection= dataSource.getConnection();
//SQL语句
String sql="select * from user where username=?";
//获取执行sql的Statement对象,mysql中每一个sql语句的执行就对应一个Statement对象
PreparedStatement statement=connection.prepareStatement(sql);
statement.setString(1,"张三");
//执行sql语句,取到返回值,返回值存在ResultSet对象中
ResultSet resultSet=statement.executeQuery();
//遍历结果集
String pass=null;
while (resultSet.next()){
//打印查询结果
//获取查询结果集中列名为id的属性值,数据库的列是啥类型,这里就用java对用的类型接收
int id=resultSet.getInt("id");
//查询结果集中属性名为username的属性值
String username=resultSet.getString("username");
//查询结果集中属性名为password的属性值
String password=resultSet.getString("password");
System.out.println("id="+id+",username="+username + ",password="+password);
// //获取查询结果中列名为password的值
// pass = resultSet.getString("password");
}
// if ("1234".equals(pass)){
// System.out.println("登录成功");
// }else {
// System.out.println("密码错误,登录失败");
// }
//关闭资源
resultSet.close();
connection.close();
}
}



