这篇博客主要介绍了数据库的高级概念，包括事务的四大特性、不同隔离级别的理解与应用，视图的创建与删除，以及索引的原理、创建、删除、使用策略。详细探讨了索引在数据库性能优化中的作用，强调了正确使用索引的重要性。此外，还涉及到了存储过程、约束和Case表达式的部分知识点。
数据库
１.事务
(1)什么是事务？
为了完成某个业务而对数据库进行一系列操作，这些操作要么全部成功，要么全部失败。
(2)事务的四个特性
- 原子性：事务包含的这一系列操作，要么全部成功，要么全部失败。
- 一致性：事务完成之后，不会将非法的数据写入数据库。
- 隔离性：多个事务可以在一定程度上并发执行。
- 持久性：事务完成之后，数据要永久保存（一般会保存在硬盘上）。
原子性（由DBMS的事务管理子系统来实现）；
一致性（由DBMS的完整性子系统执行测试任务）；
隔离性（由DBMS的并发控制子系统实现）；
持久性（由DBMS的恢复管理子系统实现的）；
(3)隔离级别
-
读未提交：一个事务可以读取到另外一个事务尚未提交的数据。该隔离级别可能会产生“脏读”、“不可重复读取”和“幻影读取”问题。
-
读已提交：一个事务只能读取到另外一个事务已经提交的数据。该隔离级别解决了“脏读”问题，但是仍然可能会发生“不可重复读取”和“幻影读取”问题。
-
可重复读取：在同一个事务当中，多次读取同一份数据，结果一样。该隔离级别解决了“脏读”和“不可重复读取”问题，但是仍然有可能会产生“幻影读取问题”（虚读）。
-
序列化：多个同务只能排队执行，即只有一个事务结束之后，另外一个事务才能开始执行。该隔离级别解决了“脏读”，“不可重复读取”和“幻影读取”问题，但是程序性能会下降。所以只有必要的时候（比如在银行系统里面）才会使用。
总结：
隔离级别从低到高依次是"读未提交"、“读已提交”、“可重复读取”和“序列化”，隔离级别越高，性能越低。mysql数据库默认隔离级别是“可重复读取”，oracle是“读已提交”。数据库底层使用的“加锁”的机制来实现不同的隔离级别，包括对整个表加锁，对表中的行加锁。
mysql数据库开始事务、提交事务、回滚事务
begin; commit; rollback; mysql数据库必须将数据库引擎设置为"innodb"才能支持事务。
２.视图
(１)什么是视图？
在已有的表或者视图上创建的虚拟表。
(2)创建视图
create view 视图名 as select
注：可以对单表或者多表进行查询，数据库会将视图的定义保存下来。
可以对（单表）视图进行一些增删改查操作，这些操作会影响到原始的表。
(3)删除视图
drop view 视图名
参考sql:
create table t_emp(
id int primary key auto_increment,
name varchar(50),
salary int,
age int
);
create view v_emp as select * from t_emp;
create view v_emp2(name,salary) as select name,salary from t_emp;
insert into v_emp2 values('Jhon',3000);
create table t_dept(
id int primary key,
name varchar(50),
addr varchar(100)
);
insert into t_dept values(100,'财务部','北京');
insert into t_dept values(200,'开发部','上海');
create table t_staff(
id int primary key auto_increment,
name varchar(30),
age int,
dept_id int
);
insert into t_staff values(null,'张三',33,100);
insert into t_staff values(null,'李四',23,100);
insert into t_staff values(null,'王五',43,200);
create view v_staff_dept(sname,dname,addr)
as
select s.name sname,d.name dname,d.addr from t_staff s
join t_dept d on s.dept_id = d.id;
drop view v_emp;
３.索引
(1)什么是索引？
为了提高查询的速度而在数据库端创建的一种排序的数据结构。
注：索引类似于一本书的目录
(2)如何创建索引？
create index 索引名 on 表名(字段列表)
(3)如何查看当前查询是否用到的索引？
(4) 在哪些字段上加索引？
应该将经常作为查询条件的字段加索引，除此以外，还要在分组、过滤、排序及联合查询的字段上加索引。
(5)如何删除索引？
drop index 索引名 on 表名
(6)联合索引
所谓联合索引（复合索引），指的是索引字段是多个，比如:
使用联合索引时，要注意“最左匹配原则”。即在使用联合索引的某个字段作为查询条件时，该字段左边的所有字段也要同时作为查询条件，比如：
联合索引包含了三个字段(c1,c2,c3),则：
将c1,c2,c3同时作为查询条件时，会用到索引；
将c1,c2同时作为查询条件时，会用到索引；
将c1作为查询条件时，会用到索引；
将c2作为查询条件时，不会用到索引；
将c3作为查询条件时，不会用到索引；
将c1和c3作为查询条件时，不会用到索引。
(7) 索引原理
假设t_user表有15条记录，每个磁盘块只能放４条记录，则需要４个磁盘块，对应的"B+"
树结构如下：
(8)主键索引和非主键索引。
数据库自动会为主键字段添加上相应的索引，该索引称之为主键索引。
比如，有一张表(id是主键，除此之外还有name,email,age等字段)，则数据库会为id字段加上主键索引，如果以id作为条件，显然会用到主键索引，但是如果是以name等非主键字段作为查询条件，则不会用到主键索引，所以为了提高查找的速度，可以在非主键字段上加上相应的索引。
主键索引和非主键索引有什么区别？
非主键索引叶子节点存放的是索引字段及主键值，而主键索引叶子节点存放的是完整的记录。使用主键索引查找要比使用非主键索引一般要快一些，因为使用非主键索引，很多时候需要“回表”。
什么是索引覆盖？
查找的字段都包含在了索引字段里面，此时，不需要进行“回表”操作，查找的速度会非常快。
面试相关：
为什么数据库不使用B树而是使用B+树？
因为Ｂ树索引块除了可以存放索引字段及指针以外，还可以存放记录，也就是说，Ｂ树的索引的扇出变少（扇出指的是索引块当中指针的个数），相应的由索引块构成的树的高度增加，也就是说，需要进行更多的“i/o”操作。
为什么数据库很少使用hash作为索引的数据结构？
hash索引虽然依据索引字段定位记录很快（比B+树还要快），但是不能进行范围查询。
(9)使用索引需要注意的问题:
a. 使用索引之后，虽然加快了查询的速度，但是在进行添加、删除、修改操作时会变慢，因为需要重建索引。在批量添加记录时，建议先临时删除索引，在批量添加成功之后再加上相应的索引。
b.不要建过多的索引。
注：索引会占用硬盘空间，一般建议不要超过6个。
c.小表不要建索引。
注：小表使用全表扫描更快。
d.索引字段不要参与计算。
比如 “select * from t_user where id-1 = 100” 不会用到索引。
注：包括使用函数都不行。
e. 尽量在同值少的字段上建索引。
注： 比如性别就不适合作索引。
４.存储过程（了解）
(1)什么是存储过程?
存储在数据库端的一组为了完成特定功能的sql语句。
(2)如何创建存储过程?
create procedure 存储过程名([参数])
参数格式 (参数类型 参数名 数据类型)
参数类型有三种：
IN: 输入参数，该参数的值必须在调用该存储过程时指定，在存储过程内部使用， 不能返回。
缺省值是IN。
OUT：输出参数，该参数值的值可以在存储过程内部修改，并可返回。
INOUT：输入输出参数，该参数需要在调用时指定，并且可以返回。
delimiter //
create procedure proc_find1()
begin
select * from t_emp;
end
//
delimiter ;
注：
delimiter // 这句的作用是将结束符号设置为"//"
call proc_find1；
delimiter //
create procedure proc_find2(eid int)
begin
select * from t_emp where id = eid;
end
//
delimiter ;
call proc_find2(1);
delimiter //
create procedure proc_find3(out max_sal int)
begin
select max(salary) into max_sal from t_emp;
end
//
delimiter ;
call proc_find3(@sal);
select @sal
注意：小海豚SQLyog中对delimiter的识别略有不同，修改结尾符时两个都要写。
(3)使用jdbc调用存储过程
public class CallProcdure {
/**
* 调用不带参的存储过程
*/
public static void test1() {
Connection conn = null;
try {
conn = DBUtil.getConnection();
CallableStatement cs =
conn.prepareCall("{call proc_find1}");
ResultSet rs = cs.executeQuery();
while(rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int salary = rs.getInt("salary");
int age = rs.getInt("age");
System.out.println("id:" + id + " name:"
+ name + " salary:" + salary + " age:" + age);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(conn);
}
}
/**
* 调用带输入参数的存储过程
*/
public static void test2() {
Connection conn = null;
try {
conn = DBUtil.getConnection();
CallableStatement cs =
conn.prepareCall("{call proc_find2(?)}");
cs.setInt(1, 1);
ResultSet rs = cs.executeQuery();
while(rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int salary = rs.getInt("salary");
int age = rs.getInt("age");
System.out.println("id:" + id + " name:"
+ name + " salary:" + salary + " age:" + age);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(conn);
}
}
/**
* 调用带输出参数的存储过程
*/
public static void test3() {
Connection conn = null;
try {
conn = DBUtil.getConnection();
CallableStatement cs =
conn.prepareCall("{call proc_find3(?)}");
//输出参数赋值不能用setxxx
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
int maxSal = cs.getInt(1);
System.out.println("maxSal:" + maxSal);
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(conn);
}
}
５.约束
(1)什么约束？
是一种限制，通过对表的行或者列的数据做出限制来确保数据的完整性和一致性。
(2)有哪些约束？
主键：相当于唯一性约束 + 非空约束的组合。
注：一张表只能一个主键，数据库会为主键添加主键索引
外键：用于确保两个表之间的参照完整性。
create table t_cls(
cno int primary key,
cname varchar(50)
);
insert into t_cls values(100,'jsd2002');
insert into t_cls values(200,'jsd2003');
create table t_stu(
sno int primary key auto_increment,
sname varchar(50),
cno int,
foreign key(cno) references t_cls(cno)
);
insert into t_stu values(null,'Sally',100);
外键所在的表称之为从表，从表参照的表称之为主表，
这儿，参照的含义是指从表中外键的值来自于主表的主键。
插入记录时，要先插入主表中的记录。
删除记录时，要先删除从表中的记录。
非空： not null
唯一性：unique
检查(了解)：
注：检查约束跟数据库版本有关系，mysql8.0.16之后才支持。
create table t_check(
id int primary key,
name varchar(50),
salary int check(salary > 0 and salary < 20000)
);
练习：写一个存储过程，将薪水前２的员工信息找出来。
并且使用jdbc来调用该存储过程。
delimiter //
create procedure proc_find4()
begin
select * from t_emp order by salary desc limit 2;
end
//
delimiter ;
SQL执行过程：
1. FROM : 对FROM的左表和右边计算笛卡尔积，生成虚表v1（临时表）。
2. ON: 对虚表v1进行ON筛选，只有符合join条件的行被保留到虚表v2。
3. WHERE: 对虚表v2进行where条件的过滤，生成虚表v3。
4. GROUP BY: 依据group by子句中的列，对虚表v3进行分组操作，生成虚表v4。
5. ＨAVING: 对虚表v4使用having进行过滤，生成虚表v5。
6. SELECT :使用select选择需要的列，生成虚表v6。
7. DISTINCT:对虚表v6中的记录进行去重，生成虚表v7。
8. ORDER BY: 对虚表v7中的记录进行排序，生成虚表v8。
9. LIMIT: 从虚表v8中取出指定条数的记录。
6. Case表达式（未完待续…）
“新手用where 句子进行条件分支，高手用select 句子进行条件分支"
“新手用having 句子进行过滤，高手用select 句子进行过滤"
Case的最强大功能在于，自定义条件分组显示
三张表：
及不及格
是男是女
分分段显示
7.简答题：
1.如何对数据库查询操作进行优化？(数据量大)
- 属于开放性题型–重点:一方面考察数据库知识掌握 ;一方面考察分析问题的能力
- 先去看目前的查询操作是怎么写的,看这些操作的sql语句是否存在大量的嵌套查询,先对sql语句进行优化(能用关联查询的尽量用关联查询)
- 查看是否建立索引
1. 若无,建立索引;
2. 若有,查看下索引建立的正确与否
1. 索引应该建立在哪些字段上?
1. 为了提高查询性能,通常应该建议在where,goup by,order by 后的字段
2. 注意：索引在哪些情况下会失效?列举几种情况
- 模糊查询以’%'开头，会导致索引失效
- like ‘_xx%’ —索引有效
- like ‘x_’ – 索引有效
- like ‘%x_’ — 索引失效
- 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
- 违背了最左匹配原则
- 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
- 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
- 如：select id from t where num/2=100应改为:select id from t where num=100*2
- 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
- 如：select id from t where substring(name,1,3)=’abc’ ，name以abc开头的id应改为: select id from t where name like ‘abc%’
- 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
3. 索引的原理是什么?
采用B+树实现
4. 什么操作会自动给字段加索引?
- 添加主键
- 添加外键
- 添加唯一性约束也会给对应的字段添加索引
5. 事务:
-
什么是事务,事务的特性是什么?
- 事务是指对数据库进行操作的最小执行单元,不可再分,要么全都执行成功,全都执行失败!
- 特性:ACID
- 一致性
- 隔离性
- 持久性
- 原子性
-
数据库中事务的提交和回滚:
- 提交:事务中的一系列操作都执行结束,提交,表示事务执行成功
- 回滚:事务中若有步骤执行失败,此时回滚事务,该事务执行失败
3. 锁
1. 锁的分类:
1. 共享锁(S锁,读锁)和排他锁(X锁,写锁)—行锁
-
共享锁:若事务A 对某行数据加S锁,此时允许其他事务对该行数据加S锁,即可以有多个事务共同读取改行数据,但是不允许其他事务对该数据加X锁
-
排他锁(X锁,写锁,独占锁):若事务A对某行数据加X锁,此时不允许其他事务对该行数据加任何锁
-
数据库中什么操作会自动添加以上的锁:
1. 数据库中进行增,删,改操作时,会自动给行添加排他锁,行数据添加上了排他锁,不允许其他事务对该行数据加任何锁
2. 数据库中进行查(select)操作时,对数据不加任何锁
1. 通过sql语句证明以上锁的存在 注意:数据库中事务默认是自动提交的,必须设置数据库的事务提交为非自动 `set autocommit=0` 2. 步骤: 证明事务A给行数据加排他锁后,不允许其它事务给该行数据加任何锁 1. 开启2个mysql客户端,设置自动提交为0 2. 事务A 对class表中的id=1的数据进行update,修改之后没有commit 3. 此时事务B 对clss表中的id=1的数据进行update,可以看到,等待 4. 事务A commit,此时事务B自动执行成功,事务Bcommit 5. 在2个客户端查询最终结果,是事务B 操作后的结果 造成以上现象的原因: 增,删,改操作会自动开启事务,对行数据进行操作,会为行数据加排他锁,事务访问期间,其它事务无法为该行数据加任何锁.(以上案例证明不能加排他锁) 事务A给行数据添加排他锁后,其它事务不能对该行数据添加共享锁证明: 1. 事务A 对class表中的id=1的数据进行update,修改之后没有commit 2. 事务B 中执行'select ..from..lock in share mode',查看查询是否等待,若等待,则表明行数据已有排他锁,不能加共享锁 3. 事务B中执行 `select ..from.` 查看查询是否等待 ~~~ -
若事务A给行数据添加共享锁,允许其它事务给该行数据添加共享锁,但是不允许添加排他锁.
1. 开启3个客户端,并设置事务自动提交为0,均对class表进行操作
2. client1中,执行select * from class where class_id=1 lock in share mode;
3. client2中,执行select * from class where class_id=1 lock in share mode;查看是否查询成功
4. client3中,执行update class set class_name='t' where class_id=1;查看是否成功
注意:client3中也可以通过 select * from class where class_id=1 for update来对行数据加排他锁
5. 将client1,client2中的事务均提交,查看client3中是否执行成功.
-
如何手动添加以上的锁
-
给行数据手动添加共享锁:
select ..from..lock in share mode或者
select..from .. for share—是版本8中用来替换以上的sql的 -
添加排他锁:
select...from...for update
-
3. 意向锁–表级锁
4. …–查看mysql官网手册
https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_intention_shared_lock
2. 死锁
是一种现象,出现了事务之间彼此等待对方锁的释放,而各自不释放锁的现象,即死锁.
MySQL中的处理方法：自动检测死锁，一旦检测到死锁形成则将其中一个事务回滚，另一个事务可以正常提交执行。
java代码中多线程并发执行也可能出现死锁:
1、模拟数据库中场景：
package deadlock;
/**
* 模拟数据库死锁场景
* 互斥、占有且申请、不可抢占、循环等待
* @Author Edward
* @Date 2020/7/11 10:43
* @Version 1.0
*/
public class DeadLockDemo {
public static void main(String[] args) {
// 用两个对象模拟两行数据
User user1 = new User();
User user2 = new User();
// new两个线程分别操作两个对象
ThreadDemo t1 = new ThreadDemo(user1,user2);
ThreadDemo t2 = new ThreadDemo(user2,user1);
t1.start();
t2.start();
}
}
class ThreadDemo extends Thread{
private User user1;
private User user2;
public ThreadDemo(User user1, User user2){
this.user1 = user1;
this.user2 = user2;
}
public void run(){
synchronized (user1){
System.out.println("conduct first operation...");
try {
// 模拟事务尚未提交的等待
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
synchronized (user2){
System.out.println("conduct second operation...");
}
}
}
}
class User{
private Integer id;
private String name;
private Integer age;
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
2、简易版：
package deadlock;
/**
* Dead Lock
* @Author Edward
* @Date 2020/7/11 11:01
* @Version 1.0
*/
public class DeadLockDemoAg {
private static String A= "A";
private static String B= "B";
public static void main(String[] args) {
DeadLockDemoAg.deadlock();
}
private static void deadlock() {
new Thread(()->{
synchronized (A){
System.out.println(Thread.currentThread().getName()+" possess the A");
try {
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
synchronized (B){
System.out.println(Thread.currentThread().getName()+" possess the B");
}
System.out.println("AB is not a deadlock");
}
},"t1").start();
new Thread(()->{
synchronized (B){
System.out.println(Thread.currentThread().getName()+" possess the B");
try {
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
synchronized (A){
System.out.println(Thread.currentThread().getName()+" possess the A");
}
System.out.println("BA is not a deadlock");
}
},"t2").start();
}
}
3. 悲观锁和乐观锁
1. 悲观锁和乐观锁是2种思想,而不是数据库中真是存在的锁.
-
悲观锁:事务A 对某行数据进行访问(查询,新增,删除,修改),总是悲观的认为在我访问期间,其它事务也会访问该数据,所以为了保证数据的安全,在事务A访问某数据时,就对该数据加排他锁,以防止其它事务操作该数据.
- 注意点:悲观锁思想中,若事务对数据进行查询操作,此时也给数据加排他锁
- 问题:
- 应用悲观锁,事务A对某行数据访问(select),此时会给数据加排他锁,
- 问其它客户端通过select * from … 是否可以访问到该数据?
可以,因为select …from…没有任何锁
- 悲观锁的利弊:
- 利:保证安全
- 弊:效率降低
-
乐观锁:
-
当某事务访问数据时,总是乐观的认为不会有其它事务和我并发访问该数据.但是实际中确定存在并发安全的.乐观锁提供了这样一种机制来保证数据安全:
在表中添加一个字段version,有默认值,通常默认值从0/1开始,之后每当有事务对该行数据进行修改操作,会让version的值自增1;这样的话,当某事务/线程对一行数据进行修改时,会先查询到该行数据,包括version的值,之后进行修改操作,在提交之前,将之前获取的version和数据库中的version进行对比,若值相等,表示在本事务执行期间,无其他事务操作该数据,可以提交,修改成功.若version不相等,表示在本事务执行期间,有其它事务对该属于进行了操作(修改),此时,修改失败!
应用:
悲观锁和乐观锁,经常会应用到持久层框架中
-
扫一扫
296
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
