Java进阶之路(六) MySQL(索引、锁、事务、隔离级别)

本文详细介绍了MySQL数据库的基本操作,包括SQL语句的执行流程、数据的增删改查、事务处理和隔离级别。此外,还探讨了索引的类型、优化策略以及B+树作为索引结构的优势。同时,分析了SQL执行慢的原因,并提出了性能优化的多种方法,如索引使用、事务管理和表结构优化。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Mysql

一条SQL语句怎么执行

通过连接器登录数据库
查询缓冲(8.0后已删除)
进入分析器进行词法分析和语法分析
通过优化器选择最优方案
通过执行器执行命令

三大范式

1NF:字段不可分

2NF:有主键

3NF:非主键字段不能相互依赖

查询

--基本查询
SELECT * FROM <TABLE> WHERE <CONDITION> NOT/AND/OR <CONDITION1>;
--投影查询
SELECT KEY1,KEY2,KEY3 KEY3_NEWNAME FROM <TABLE>;
--正序查询
SELECT KEY1,KEY2 FROM <TABLE> ORDER BY <CONDITION>;
--倒序查询
SELECT KEY1,KEY2 FROM <TABLE> ORDER BY <CONDITION> DESC;
--补充倒序查询(先查条件1再查条件2)
SELECT KEY1,KEY2 FROM <TABLE> ORDER BY <CONDITION1> DESC,<CONDITION2>;
--分页查询,每页<PAGES_SIZE>条记录,PAGEINDEX从1开始,是页数
SELECT KEY FROM <TABLE> ORDER BY <CONDITION> LIMIT <PAGES_SIZE> OFFSET <PAGES_SIZE*(PAGEINDEX-1)>;
--聚合查询
SELECT COUNT/SUM/AVG/MAX/MIN(KEY) KEY_NEWNAME FROM <TABLE> WHERE <CONDITION>;
--分组聚合查询
SELECT COUNT/SUM/AVG/MAX/MIN(KEY) KEY_NEWNAME FROM <TABLE> WHERE <CONDITION> GROUP BY KEY;
--多表查询(笛卡尔查询),返回记录数为乘积,有风险
SELECT * FROM <TABLE1>,<TABLE2>;
--多表查询,使用别名投影
SELECT 
    t1.<KEY1>  KEY1_NEWNAMEFROM
    t1.<KEY2>  KEY2_NEWNAMEFROM
    t2.<KEY1>  KEY1_NEWNAMEFROM
FROM <TABLE1> t1,<TABLE2> t2
WHERE <CONDITION>;
--内连接查询
SELECT KEY1, KEY2, KEY3
FROM <TABLE1> t1
INNER JOIN <TABLE2> t2
ON t1.KEY1 = t2.KEY4;
--外连接查询
SELECT KEY1, KEY2, KEY3
FROM <TABLE1> t1
LEFT/RIGHT/FULL OUTER JOIN <TABLE2> t2
ON t1.KEY1 = t2.KEY4;

假设查询语句是:

SELECT KEY1, KEY2, KEY3
FROM <TABLEA> A
LEFT/RIGHT/FULL OUTER JOIN <TABLEB> B
ON A.KEY1 = B.KEY4;

在这里插入图片描述

修改数据

--插入多条
INSERT INTO <TABLE> (KEY1, KEY2) VALUES (VALUE1, VALUE2),(VALUE3, VALUE4);
--更新多条数据(修改where为一个范围即可)
--SET后可以为一个计算表达式
UPDATE <TABLE> SET KEY1=VALUE1, KEY2=VALUE2 WHERE <CONDITION>;
--删除多条记录(修改where为一个范围即可)
DELETE FROM <TABLE> WHERE <CONDITION>;

MYSQL操作

# 列出所有库
SHOW DATABASES;
# 建库
CREATE DATABASE <TABLE>;
# 删库
DROP DATABASE <TABLE>;
# 列出所有表
SHOW TABLES;
# 查看表结构
DESC <TABLE>;
# 查看创建表的SQL语句
SHOW CREATE TABLE <TABLE>;
# 建表
CREATE TABLE <TABLE>;
# 删表
DROP TABLE <TABLE>;
# 修改表——新增列,类型为VARCHAR(10)
ALTER TABLE <TABLE> ADD COLUMN <COLNAME> VARCHAR(10) NOT NULL;
# 修改表——删除列
# 修改表——改名和改类型为VARCHAR(20)
ALTER TABLE <TABLE> CHANGE COLUMN <COLNAME> <COLNAME_NEWNAME> VARCHAR(20) NOT NULL
# 快速建表
CREATE TABLE <TABLE> (
    KEY1 BIGINT NOT NULL AUTO_INCREMENT,#AUTO_INCREMENT即定义自增主键
    KEY2 BIGINT NOT NULL,
    KEY3 DOUBLE NOT NULL,
    PRIMARY KEY (id)
);

索引类型

通过UNIQUE关键字添加一个索引

ADD UNIQUE INDEX uni_name (name);
聚集索引和非聚集索引的区别
  1. 聚集索引(主键索引):在数据库里面,所有行数都会按照主键索引进行排序。
  2. 非聚集索引:就是给普通字段加上索引。
  3. 联合索引:就是好几个字段组成的索引,称为联合索引。
联合索引最左前缀匹配

例如:key idx_age_name_sex('age','name','sex')

如果where内有age,无论age在and连接的哪个位置,都会使用索引;如果age是个范围,就只有age使用索引

如果没有age就不使用;如果有!=也不使用索引

索引失效

1.有or必全有索引;
2.复合索引未用左列字段;
3.like以%开头;
4.需要类型转换;
5.where中索引列有运算;
6.where中索引列使用了函数;
7.如果mysql觉得全表扫描更快时(数据少);

MySQL 索引为什么采用B+树?

B+树能显著减少IO次数,提高效率
B+树的查询效率更加稳定,因为数据放在叶子节点
B+树能提高范围查询的效率,因为叶子节点指向下一个叶子节点

一条sql执行的慢的原因

偶尔慢:
数据库在刷新脏页
等待锁的时候

一直慢:
没有用到索引
数据库选错索引

事务

把多条语句作为一个整体进行操作的功能,被称为数据库事务

ACID四大属性
  • A:Atomic,原子性,将所有SQL作为原子工作单元执行,要么全部执行,要么全部不执行;
  • C:Consistent,一致性,事务完成后,所有数据的状态都是一致的,即A账户只要减去了100,B账户则必定加上了100;
  • I:Isolation,隔离性,如果有多个事务并发执行,每个事务作出的修改必须与其他事务隔离;
  • D:Duration,持久性,即事务完成后,对数据库数据的修改被持久化存储。

使用BEGIN开启一个事务,使用COMMIT提交事务

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
隔离级别

对于两个并发执行的事务,如果涉及到操作同一条记录的时候,可能会发生问题。因为并发操作会带来数据的不一致性,包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让有针对性地选择事务的隔离级别,避免数据不一致的问题。

SQL标准定义了4种隔离级别,分别对应可能出现的数据不一致的情况:

Isolation Level脏读(Dirty Read)不可重复读(Non Repeatable Read)幻读(Phantom Read)
Read UncommittedYesYesYes
Read Committed-YesYes
Repeatable Read--Yes
Serializable---

Read Uncommitted:读到了另一个事务修改但未提交的数据,出现脏读
在这里插入图片描述

Read Committed:多次读数据的时候读到了另一个事务修改后的数据,出现数据不一致的情况,也就是不可重复读

在这里插入图片描述

Repeatable Read:幻读是指,在一个事务中,第一次查询某条记录,发现没有,但是,当试图更新这条不存在的记录时,竟然能成功,并且,再次读取同一条记录,它就神奇地出现了。

在这里插入图片描述

Serializable是最严格的隔离级别,所有事务依次执行,不会出现脏读、不可重复读、幻读。但这样是串行执行,效率会受影响。

MySQL中,默认会使用Repeatable Read的隔离级别。

死锁:

悲观锁是利用了数据库锁的机制实现的,具有独占性和排他性,分为读锁和写锁,即都能读但只有一个能获取到写的权限

乐观锁通过CAS实现,多个线程尝试使用CAS同时更新变量时,先根据版本进行冲突检测,只有一个能成功,其他的都会失败并获取失败信息且可以再次尝试

MySQL实现了什么锁?怎么实现的

全局锁

表级锁

表锁
元数据锁

行锁

innodb和myisam对比及索引原理区别

innodbmyisam
具备事务没有事务
支持事务和行级锁只支持表级锁
两个文件:【表名(表定义).frm、表名(日志文件.)ibd】三个文件:【表名(表定义).frm、表名(数据文件).MYD、表名(索引文件).MYI】
索引组织表【聚簇索引】堆组织表【非聚簇索引】
与事务隔离级别相关读写互相阻塞
速度相对慢速度相对快

大表优化

字段优化
索引优化
查询SQL优化
升级硬件
读写分离
使用缓存

垂直拆分
水平拆分

将日志、监控、统计类、弱结构化数据迁移到NoSQL

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值