MySQL数据库高级教程

Akira37

已于 2024-07-15 20:22:28 修改

阅读量4.1k

点赞数 78

文章标签： mysql 面试数据库后端 linux 数据库架构 b树

于 2024-07-09 15:57:06 首次发布

本文链接：https://blog.csdn.net/Akira37/article/details/140290880

版权

Java面试专题 MySQL高级：SQL规范、事务、索引、调优、分库分表、锁…

MySQL数据库基础教程

文章目录

1 SQL规范
2 事务
3 索引
4 调优
5 分库分表
6 MySQL锁相关

1 SQL规范

1.1 查询语句的执行顺序

sql的编写顺序

select           字段列表
from             表名列表
join on          表关联
where            条件列表
group by         分组字段列表
having           分组后的条件列表
order by         排序字段列表
limit            分页参数

sql的执行顺序

from             表名列表
join on          表关联
where            条件列表
group by         分组字段列表
having           分组后的条件列表
select           字段列表
order by         排序字段列表
limit            分页参数

【例】给表、SQL字段起别名
select name ,age from tb_user where name = '张三'  order by age limit 10;
给表起别名
可以正常执行，如果from后不先执行，则select和order by无法引用：
select u.name, u.age from tb_user u where name = '张三' order by u.age limit 10;
给SQL的字段起别名
在where之后使用别名，执行报错，因为where在select 之前执行：
select u.name uname, u.age uage from tb_user u where uname = '张三' order by u.age limit 10;
在order by后面使用别名，可以正常执行，因为select执行完成后才会执行order by：
select u.name uname ,u.age uage from tb_user u where u.name = '张三' order by uage limit 10;

1.2 多表查询

多表关系

一对多：在多的一方设置外键，关联一的一方的主键
一对一：用于表结构拆分，在其中任何一方设置外键（给唯一约束UNIQUE）,关联另一方的主键
对多对：需建立中间表，中间表包含两个外键，关联两张表的主键

多表查询

内连接
- 隐式：select ... from 表A, 表B where 条件...
- 显式：select ... from 表A inner join 表B on 条件...
外连接
- 左外连接：select ...from 表A left join 表B on 条件...
- 右外连接：select ...from 表A right join 表B on 条件...
自连接：select ... from 表A 别名1,表A 别名2 where 条件...
子查询：列子查询、行子查询、表子查询

内连接和外连接的区别

内连接取出连接表中匹配到的数据，匹配不到的不保留

外连接取出连接表中匹配到的数据，匹配不到的也会保留，其值为NULL。

左外连接，以左表为主表

右外连接，以右表为主表

以某一个表为主表后，进行关联查询，不管能否关联上，主表的数据都会保留，关联不上的以NULL显示

1.3 CHAR和VARCHAR的区别

最大长度：char最大长度为255字符；varchar最大长度为65535个字节。
定长：char是定长的，不足的部分用隐藏空格填充；varchar是不定长的。
空间使用：char浪费空间；varchar更加节省空间。
查找效率：varchar需要计算内容占用的长度，而char不会，所以char查找效率很高，varchar查找效率更低。

在项目中视不同场景选择使用，例如枚举值可选择使用char，描述信息或名字类可选择使用varchar

2 事务

事务（Transaction）：由多个操作组成的一个逻辑单元，这些操作要么都成功，要么都失败。

2.1 ACID

原子性（Atomicity）：组成事务的操作，要么全部成功,要么全部失败，不可能只执行一部分操作。
一致性（Consistency）：系统（数据库）总是从一个一致性的状态转移到另一个一致性的状态，不会存在中间状态。
隔离性（Isolation）：通常，一个事务在完全提交之前，对其他事务是不可见的
持久性（Durability）：一旦事务提交，那就将永远保持这样，哪怕系统崩溃也不会影响这个事务的结果。

【例】转账：A向B转账500，转账成功，A扣除500元，B增加500元

原子操作体现在要么都成功，要么都失败

在转账的过程中，数据要一致，A扣除了500，B必须增加500

在转账的过程中，隔离性体现在A向B转账，不能受其他事务干扰

在转账的过程中，持久性体现在事务提交后，要把数据持久化（落盘操作）

2.2 并发事务的潜在问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

脏读（Dirty Read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to Modify）：指在一个事务读取一个数据时，另一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失。【例】事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
不可重复读（Unrepeatableread）：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据，则在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不一样。这就发生了在一个事务内两次读到的数据是不一样的情况。
幻读（Phantom read）：幻读与不可重复读类似。一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，如同发生了幻觉一样，因此称为幻读。

解决方法：对事务进行隔离

2.3 MySQL的隔离级别

MySQL的四种隔离级别如下：

未提交读（READ UNCOMMITED）：该隔离级别下，其他事务可以看到本事务没有提交的部分修改。因此会造成脏读的问题（读取到了其他事务未提交的部分,而之后该事务进行了回滚）。这个级别的性能没有足够大的优势，但是又有很多的问题，因此很少使用。

# 插入数据
insert into goods_innodb(name) values('华为');
insert into goods_innodb(name) values('小米');

# 会话一
set session transaction isolation level read uncommitted;	# 设置事务的隔离级别为read uncommitted
start transaction;		# 开启事务
select * from goods_innodb;		# 查询数据

# 会话二
set session transaction isolation level