MySQL入门

Myth丶恋晨 2022-01-23 04:11 206阅读 0赞

**目录**

数据库引擎

索引

数据结构

b+tree

hash

full-text

b+tree索引

聚集索引

非聚集索引

b+tree索引的应用

主键索引

覆盖索引

联合索引

索引优化

索引最左匹配原则

锁

行锁（Innodb）

表锁（MyISAM）

共享锁

排他锁

事务的四大特性

原子性（Atomicity）

一致性（Consistency）

隔离性（Isolation）

持久性（Durability）

事务隔离级别（隔离性）

未提交读 read uncommitted

已提交读 read committed

可重复读 repeatable read

串行化 serializable

并发事务的问题

丢失更新

脏读

不可重复读

幻读

InnDB在RR事务隔离级别下解决幻读问题

快照读（MVCC）

当前读（next-key锁）

--------------------

# 数据库引擎 #

MyISAM：为表锁，不支持事务

InnoDB：为行锁，同时也支持表锁，支持事务。

# 索引 #

索引是为了更快的查询到数据，让数据有序而建立的。

## 数据结构 ##

### b+tree ###

数据都存放在叶子节点，叶子节点形成有序链表，查找方便，减少IO次数。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pbmdtZW5nYmFieQ_size_16_color_FFFFFF_t_70][]

1.非叶子节点的子树指针跟关键字个数相同

2.非叶子节点仅用来存放索引，数据都保存在叶子节点中

3.所有叶子节点都有一个指针指向下一个叶子节点

### hash ###

### full-text ###

全文索引

## b+tree索引 ##

b+tree索引从存储的角度可以分为聚集索引和非聚集索引，区别在于非聚集索引叶子节点不包含整行的数据记录，包含键和值以及响应行数据的聚集索引键。

### 聚集索引 ###

按照每张表的主键构造一个B+tree树，叶子节点存放的是所有行记录信息。每个表只能有一个聚集索引，聚集索引的叶子节点被称为数据页，每个数据页之间用链表来连接。

### 非聚集索引 ###

又叫做辅助索引。叶子节点不存放行记录的全部信息，存放键值和指针，从而获得主键索引然后通过主键到聚集索引中获得相应的数据。

## b+tree索引的应用 ##

### 主键索引 ###

### 覆盖索引 ###

从辅助索引中就可以得到查询的记录，不需要查询聚集索引中的记录。因为辅助索引的叶子节点就包含了覆盖索引的数据。

### 联合索引 ###

两个或多个字段组成的索引。

## 索引优化 ##

explain，关键点type  最好为ref....index,all，all为全表扫描，extra：using index好。using filesore还得经过一次order by排序

## 索引最左匹配原则  ##

在user\_id,date（a,b）上建立联合索引，当在user\_id一致的基础上date是有序的，所以查询a命中索引，查询a,b命中索引，但是查询b是不命中索引的，因为b本身是无序的。

![20190602155539677.png][]

联合索引本身也是b+tree。

select \* from 表 where a = xxx and b= xxx 是可以使用（a,b）联合索引。

select \* from 表 where a =xxx是可以使用（a,b）联合索引。

select\* from 表 where b= xxx是不可以使用（a,b）索引，因为1，2,1,4,1,2不是有序的。

# 锁 #

## 行锁（Innodb） ##

锁住的是此行数据。

1.select \* from 表 where id = 1  上读锁

此时锁住这行记录，不能再给这行记录上写锁，即更新，修改删除操作。但是还可以再上读锁。因为此时锁住的只是这一行数据，其他行还是可以上读或者写锁的。

2.update 表 set name= 'alice' where id=1

此时锁住这行记录，不能再给这行记录上写锁，也不能再上读锁，但是可以给其他行上锁。

## 表锁（MyISAM） ##

锁住的是表的数据。

1.select \* from 表 where id = 1  上读锁

此时锁住这个表，不能再给这个表上写锁，即更新，修改删除操作。但是还可以再给其他行上读锁。

2.update 表 set name= 'alice' where id=1

此时锁住这个表，不能再给这个表上写锁，也不能再上读锁。

## 共享锁 ##

读锁

## 排他锁 ##

写锁

## 事务的四大特性 ##

ACID

##  原子性（Atomicity） ##

## 一致性（Consistency） ##

## 隔离性（Isolation） ##

## 持久性（Durability） ##

## 事务隔离级别（隔离性） ##

### 未提交读 read uncommitted ###

### 已提交读 read committed ###

### 可重复读 repeatable read ###

### 串行化 serializable ###

## 并发事务的问题 ##

### 丢失更新 ###

当两个事务基于同样的值更新时，后一个会覆盖前一个的结果。

本来银行卡有2000，第一个操作是取100，第二个操作是取1，结果是银行卡还有1999

### 脏读 ###

A读到了B未提交的数据，B事务回滚，此时A读取到的数据就无效了。产生了数据一致性的问题。RC可以解决。

### 不可重复读 ###

事务A读取一条数据，此时并未提交，但是B在A的基础上更新了这表数据，并且提交了，此时A读取到的数据就变化了。两次读的内容不一致。重点在于update和delete RR可以解决。

### 幻读 ###

事务A查询一条数据不存在就插入，此时A事务查询id= 1的数据不存在，事务B插入的id= 1的数据并提交，此时事务A插入数据报主键冲突。幻读是出现了之前没有出现的数据或者出现的数据突然不在了。重点在于insert 。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pbmdtZW5nYmFieQ_size_16_color_FFFFFF_t_70 1][]

# InnDB在RR事务隔离级别下解决幻读问题 #

重复之前的说法，什么是幻读？

在一次事务里面，多次查询后，结果集的个数不一致的情况叫做幻读，多出来或者少的行就被叫做幻行。

Mysql解决幻读的方式：

## 快照读（MVCC） ##

多版本并发控制MVCC ，每一行都多了2个字段，行的创建版本，行的删除（过期）版本。版本号（trx\_id）随着每次事务的开启自增。

普通的select就是快照读。

select \* from T where number= 1

原理：将历史数据存一份快照，其他事务增加或者删除数据，对于当前事务都是不可见的。

## 当前读（next-key锁） ##

next-key锁包含两部分：

record lock 行锁

gap 间隙锁

记录锁是加在索引上的锁，间隙锁是加在索引之间的锁。

原理：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据都是一致的。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pbmdtZW5nYmFieQ_size_16_color_FFFFFF_t_70]: /images/20220123/bebf68e342874534a0da3013cf6eccbd.png
[20190602155539677.png]: /images/20220123/f665e55e03e3498a9714a231ad687743.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pbmdtZW5nYmFieQ_size_16_color_FFFFFF_t_70 1]: /images/20220123/093891996c7946079b8a763bb4c6cef1.png