MySQL详解

一、通用语法及分类

• DDL:数据定义语言，用来定义数据库对象（数据库，表，字段）
• DML：数据库操作语言，用来对数据库表中的数据进行增删改
• DQL：数据库查询语言，用于查询数据库中表的记录
• DCL：数据库控制语言，用于创建数据库用户、控制数据库的控制权限

数据库操作语句

查询所有数据库：

show databases;

查询当前数据库：

select database();

创建数据库：

create database [ if not exists ] 数据库名 [ dafault charset 字符集 ] [collate 排序规则 ];

删除数据库：

drop databse [ if exists ] 数据库名;

使用数据库：

use 数据库名;

注意：UTF-8字符集长度为3字节，有些符号占4个字节，所以推荐使用utf-8mb4字符集

表操作

查询当权数据库所有表：

show tables;

查询表结构：
desc 表名;

查询指定表的建表语句：

show create table 表名;

创建表：

CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释],
	字段2 字段2类型 [COMMENT 字段2注释],
	字段3 字段3类型 [COMMENT 字段3注释],
	...
	字段n 字段n类型 [COMMENT 字段n注释]
)[ COMMENT 表注释 ];

添加数据：

指定字段：

insert into 表名 （字段名1， 字段名2，...) values (值1，值2，...）;

全部字段：

insert into 表名 values(值1，值2，...);

批量添加数据：

insert into 表名 （字段名1， 字段名2，...) values (值1，值2，...），(值1，值2，...);

insert into 表名 values (值1，值2，...），(值1，值2，...);

更新和删除数据：

修改数据：

update 表名 set 字段名1 = 值1， 字段2 =值2，...[ where 条件 ];

删除数据：

delete from 表名 [ where 条件 ];

二、事务

四大特性ACID

1. 原子性(Atomicity)：事务是不可分割的最小操作但愿，要么全部成功，要么全部失败
2. 一致性(Consistency)：事务完成时，必须使所有数据都保持一致状态
3. 隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
4. 持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
    

SQL事务

问题	描述
脏读	一个事务读到另一个事务还没提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在

SQL事务隔离级别：

隔离级别	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable Read(默认)	×	×	√
Serializable	×	×	×

由于无论哪种隔离级别，都不允许脏写的情况发生，所以没有列入到表格中。

        

三、MVCC

多版本并发控制，指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能，MVCC的具体实现，还需要依赖数据库记录中的三个隐式字段、undo log日志，readView.

mvcc实现原理

MVCC (Multi-Version Concurrency Control)多版本并发控制，利用记录的版本链和ReadView，来控制并发事务访问相同记录时的行为。

ReadView 一致性视图，用来判断版本链中的哪个版本是当前事务可见的。

数据库记录中的隐藏字段
DB_TRX_ID：最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID
DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本.
DB_ROW_ID：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段.

undo log
回滚日志，在insert，update，delete的时候产生的便于数据回滚的日志
当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除
而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照时也需要，不会立即被删除.

Readview
ReadView（读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前的事务（未提交的）id.
ReadView中包含了四个核心字段：
m_ids:当前活跃的事务ID集合
min_trx_id:最小活跃事务ID
max_trx_id:预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_id:ReadView创建者的事务ID

不同的隔离级别，生成ReadView的时机不同：
READ COMMITED:在事务中每一次执行快照读时生成ReadView.
REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView.
 

四、锁

mysql中的锁有哪些：

全局锁；

表级锁：1.表锁      2.元数据锁（MDL)     3.Auto - INC锁    4.意向锁

行级锁（所有的锁，都是锁住索引）：共享锁（s锁）和独占锁（x锁）

共享锁：读读共享，读写互斥。

独占锁：写写互斥，读写互斥。

MySQL支持行级锁，MyIsm不支持行级别的锁。

以下是两种不同的select语句：

//对读取的记录加共享锁
select ... lock in share mode;

//对读取的记录加独占锁
select ... for update;

如果采用MVCC方式，读-写操作彼此并不冲突，性能更高；

如果采用加锁方式，读-写操作彼此需要排队执行，从而影响性能；

针对DELETE操作：

先在B+树中定位到这条记录的位置，获取这条记录的X锁，最后再执行delete mark操作。

针对INSERT操作一般情况下，新插入的一条记录受隐式锁保护，不需要在内存中为其生成对应的锁结构。

针对UPDATE操作，分为如下3种情况：

① 未修改主键并且被更新的列在修改前后所占用的存储空间未发生变化先在B+树中定位到这条记录的位置，然后获取这条记录的X锁，最后在原记录的位置进行修改操作。

② 未修改主键并且被更新的列在修改前后所占用的存储空间发生变化先在B+树中定位到这条记录的位置，然后获取这条记录的X锁，之后将原记录彻底删除掉（即：把记录彻底移入垃圾链表），最后再插入一条新记录。

③ 修改主键相当于在原记录上执行DELETE操作之后再来一次INSERT操作。加锁操作就需要按照DELETE和INSERT的规则进行了。

I n n o D B 表 级 锁

InnoDB存储引擎提供的表级S锁或者X锁相当“鸡肋”，只会在一些特殊情况下（比如系统崩溃恢复时）用到。

IS锁和IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。

当我们使用了auto_increment修饰列的时候，就会涉及到AUTO-INC锁和轻量级锁。其中：innodb_autoinc_lock_mode系统变量，用来控制到底使用上述两种方式中的哪一种。

① 0：一律采用AUTO_INC锁。

② 1：混合使用。插入记录的数量确定时采用轻量级锁，不确定时采用AUTO-INC锁。

③ 2：一律采用轻量级锁。

I n n o D B 行 级 锁 — — 记 录 锁

LOCK_REC_NOT_GAP   被称为记录锁，也就是仅仅负责把1条记录锁上的锁。

I n n o D B 行 级 锁 — — g a p 锁

LOCK_GAP被称为gap锁，锁住了指定记录前面的间隙，防止其间插入新记录。gap锁的提出仅仅是为了防止插入幻象记录（即：幻读现象）而提出的。

I n n o D B 行 级 锁 — — n e x t - k e y 锁

LOCK_ORDINARY被称为next-key锁，本质就是一个 记录锁 + gap锁 的合体。它既能保护该条记录，又能阻止别的事务将新纪录插入到被保护记录前面的间隙中。

I n n o D B 行 级 锁 — — 插 入 意 向 锁

LOCK_INSERT_INTENTION也被称为插入意向锁，事务在等待时也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在处于等待状态。

五、加 锁 操 作 解 析

半 一 致 性 读 的 语 句

当隔离级别为READ UNCOMMITTED或READ COMMITTED且执行UPDATE语句时，将会使用半一致读。

 什么是半一致读呢？

        就是当UPDATE语句读取到已经被其他事务加了X锁的记录时，InnoDB会将该记录的最新提交版本读出来，然后判断该版本是否与UPDATE语句中的搜索条件相匹配。如果不匹配，则不对该记录加锁，从而跳到下一条记录；如果匹配，则再次读取该记录并对其进行加锁。这样做的目的就是让UPDATE语句尽量少被别的语句阻塞。

I N S E R T 语 句

insert语句在一般情况下不需要在内存中生成锁结构，只是单纯依靠隐式锁保护插入的记录。

不过在当前事务插入一条记录之前，需要先定位该记录在B+树中的位置。如果该位置的下一条记录已经被加了gap锁或next-key锁。那么，当前事务就会为该记录加上插入意向锁，并且事务进入等待状态。