ACID

ACID是针对的什么?

ACID是针对mysql数据库的事务来说的. mysql的每个操作都属于事务的一部分, 如果不显式的说明事务包含几个操作, 那么每个操作都是一个独立的事务. 因此ACID的特点都是跟事务的特点息息相关的.

ACID是什么?

A

automicity 原子性, 事务是原子性的, 事务的提交要么全部成功, 要么全部失败, 因此事务是可以回滚的

C

consistency 一致性, 一致性要求事务前后,数据库中的数据是一致的

(1). 如果数据库只有一台机器, 没有并发事务, 一致性由原子性保证

(2). 如果有多台mysql机器, 那么一个事务之后, 所有机器的数据都应该被修改

(3). 如果有并发的事务, 系统必须以类似串行的方式执行事务

I

isolation 隔离性,事务与事务应该是处于某种隔离状态, 事务本身应该不需要关注另一个事务, 同时一个事务对于另一个事务造成的影响应该是可控制, 可预测的

D

durability 持久性, 事务带来的数据变化, 都会被持久化到磁盘

mysql 基础

数据存储在哪里

(1) .frm 文件与操作系统和数据库的引擎无关，都有这么个与表同名的文件。该文件用来保存每个数据表的元数据信息。包括表的结构定义等

(2) .myd 即my data，表数据文件

(3) .myl 即my index ，索引文件

(4) .log 日志文件

redo日志

redo日志记录了事务的正向操作, 就是可以实现前滚的功能(无论事务是否提交都会记录). mysql在记录数据变化的时候, 是先将变化记录到redo日志, 然后更新内存, 最后才再根据某些策略更新到磁盘(避免了每次都将内存中的数据更新到磁盘). 这样如果mysql服务器在写入磁盘前崩溃了, mysql在重新启动时就可以根据redo日志, 进行前滚操作, 将未完成的操作继续完成, 可以重新提交事务, 写入磁盘, 保证数据一致性.

redo日志的写入

在一个事务期间, 每次引起数据库数据变化的操作, 都会写入redo日志, 具体内容就是描述了物理数据的变化(而不是记录SQL语句), 如果一个事务发生了多次数据变化, 就会写入多次

redo日志如何清理

redo日志是循环写的, 即redo日志写满了, 会清空, 然后重新写, redo日志在清空前, 会保证记录的所有操作已经更新到磁盘了, 保证redo日志没用了, 才会清空

redo日志为啥要记录未提交的事务

按道理来说, 未提交的事务, 服务器如果崩溃了, 是不应该记录的, 但是mysql是采取恢复时先执行, 然后通过undo日志再回滚.

undo日志

记录了一条数据从创建后, 不同事物或者同一事物对于数据的修改操作的记录每次修改数据undo日志以事务id + 数据id + 旧值(记录了一个事务对于数据改变的操作链, 每个事务都会在注意undo日志记录的都是这个事务修改之前的值, 现在的值是多少不关心) + 上一版本指针的形式来实现, 同一条数据的操作会组成一个链表.

undo日志如何完成回滚

当事务需要回滚的时候, 就会通过undo日志里的操作链表, 逐一恢复事务带来的变化. 恢复的具体方式为执行相反的sql语句, 例如本身是insrt就是执行delete, 本身是update, 就修改会update之前的值

undo日志如何清理

udo日志主要被事物回滚和MVCC机制使用, 简单来说, 如果事务提交了, 并且MVCC生成的所有read view快照中, 没有使用到相关的事务, 那么相关的链表就可以被删除或者缩短了.

当事务提交的时候，InnoDB 不会立即删除 undo log，因为后续还可能会用到 undo log，如隔离级别为 repeatable read 时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即 undo log 不能删除。

当事务提交之后，undo log 并不能立马被删除，而是放入待清理的链表，由 purge 线程判断是否有其他事务在使用 undo 段中表的上一个事务之前的版本信息，决定是否可以清理 undo log 的日志空间。

bin-log

是数据库的二进制文件, 记录了MYSQL所有执行更改的所有操作，但是不包括SELECT和SHOW这类操作.

生成的时机

不同于redo-log, bin-log是在事务真正提交后, 才会一次性写入bin-log, 并且写入的是具体执行的SQL命令.

bin-log写满了怎么办

binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件, binlog可以作为恢复数据使用，主从复制搭建

单点mySQl的事务一致性

可以说，一致性是事务追求的最终目标：前面提到的原子性、持久性和隔离性，都是为了保证数据库数据的一致性而采取的措施.

原子性如何保证?

首先事务只是一个概念, 它本身并不具有原子性, 也就是我们在一个事务修改多个数据的时候, 每修改一个数据, 其实已经是真正修改内存中的数据了, 只是没有同步到磁盘而已, 因此在需要回滚的时候, 我们需要通过undo日志来执行一个相反的操作, 来将内存中的数据修改回来. 而不是说当事务提交的才去修改数据. 事务提交之前, 内存中的数据都被改了, 只不过事务提交的时候, 才会真正的更新到磁盘(也就真正意义上的修改)

两个写事务并行

mysql数据库通过锁机制, 来使得两个写事务进行隔离, InnoDB同时支持表锁和行锁, isam只支持表锁, 事务在修改数据之前，需要先为数据加相应的锁；加锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

需要注意: innodb加行锁的前提是,命中的数据是通过索引访问到的，否则会切换为表锁。

原因是InnoDB行锁是通过给索引上的索引项加锁来实现的, 因此只有通过索引访问, 才能控制加行锁.

互斥锁(排他锁), 共享锁

互斥锁: 就是写锁, msyql写操作必定会触发的锁, 一个对象只能加一个互斥锁, UPDATE、DELETE和INSERT语句会自动加排他锁

加了互斥锁就可以对对象进行读取和更新。加锁期间其它事务不能对该对象加任何锁(即其它事务即不能写也不能读, 不能加互斥锁, 也不能加共享锁)

共享锁: 就是读锁, 一个对象能加多个共享锁 select语句不会加共享锁, 要加需显式的加加了共享锁, 就可以读取该对象, 多个事务可以同时为一个对象加共享锁(前提是该对象没有其它锁), 若果一个对象被加了共享锁, 就不能加互斥锁

mysql基础的锁概念是这两种, 其它还有很多各种各样的锁, 都是在这两种锁的基础上侧重点不同, 并且在不同的情况下使用各种各样的锁.

间隙锁

间隙锁是排他锁的一种.

间隙锁是封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。即加了间隙锁, 数据库表的数目不会发生变化, 即记录之前的间隙不变

隔离性如何保证?

事务隔离级别就是针对读事务和写事务并发时的可见性问题, 两个写事务必然是加锁实现的.

事务隔离级别描述的都是当前事务是读事务, 其它写事务对我能够造成哪种影响. 使得读到的数据是可控, 可预知的.

会出现哪几种问题?

脏读: 读事务读取了写事务未提交的内容, 然后写事务回滚, 那么读事务读到了脏数据

不可重复读: 重复读取得到了不同的数据就是读事务包含了两次读取, 第一次读取后, 写事务对数据进行了修改并提交了事务, 第二次读取的时候读到了不同的数据(指的数据发生了修改)

幻读: 属于不可重复读的一种, 就是第二次读取读到了第一次不存在的数据(新增的数据), 虽然幻读属于不可重复读, 但是需要单独讨论

如何解决 ?

这种情况, mysql是通过隔离级别来保证结果的可预知性

UNCOMMITTED: 会读取写事务未提交的数据最弱的隔离级别, 几乎不隔离, 脏读, 不可重复读, 幻读都会出现

READ_COMMITTED: 会读取到写事务已提交的数据会出现不可重复读, 幻读, 不会脏读

REPEATABLE_READ: 可以重复读, 即不会读取到写事务提交的修改数据不会脏读, 不会不可重复读, 但是会幻读

SERIALIZABLE: 串行化强制事务串行执行, 也就是读事务和写事务不可并行了, 相当于两个写事务, 不会脏读, 不会不可重复读, 不会幻读

MVCC(用作写读的情况)

MVCC多版本控制是依赖undo日志实现的一种无锁化控制机制, 能够实现依赖以下两个底层条件

(1) 数据库每行记录有两个隐藏列最新一次修改的事物id 当前数据的最近历史记录在undo日志中的指针
(2) undo日志记录了事务的操作链

针对的就是在当前快照读开始时, 已经开始的事务们(在开始时还未结束的事务), 还未开始的事务, 不考虑.

MVCC如何工作的

MySQL中MVCC+行级锁的工作机制 MVCC工作是依靠read view快照以及可见性比较算法实现的

read view快照

有个基础前提, mysql每个事务都是有id, 并且id是自增的:

(1) low_limit_id：目前出现过的最大的事务ID+1，即下一个将被分配的事务ID

(2) up_limit_id：活跃事务列表trx_ids中最小的事务ID，如果trx_ids为空，则up_limit_id 为 low_limit_id.

(3) trx_ids：Read View创建时其他未提交的活跃事务ID列表。意思就是创建Read View时，将当前未提交事务ID记录下来.

(4) creator_trx_id：当前创建事务的ID，是一个自增的

快照的执行的时刻

(1) REPEATABLE隔离级别下

在innodb中，创建一个新事务后，第一次select语句的时候，innodb会创建一个read view快照. 非常需要注意, 只会在事务中第一个查询操作生成快照, 才能保证整个事务中读的可预见.

(2) READ_COMMIT

事务中, 每次select语句, 都会生成一个read view快照.

可见性比较算法(针对REPEATABLE隔离级别来举例)

当前事务如果要读取某一行数据, 这一行数据最近修改的事务id假设为x.

(1) 如果x < up_limit_id, 表明x是不活跃的事务, 是已经提交的事务了, 所以该行数据不受其他写事务的影响, 跳到步骤5.

(2) 如果 x >= low_limit_id, 那么表明最新修改该行的事务在当前事务创建快照之后才创建的事务, 就算这个事务已经提交了, 但是按照事务的先后顺序, 在我读取的时候, 事务没生成, 那么我不应该能查询到未来的数据, 因此当然应该是不可见的。跳到步骤4。

(3) 如果 up_limit_id <= x < low_limit_id, 表明“最新修改该行的事务”在“当前事务”创建快照的时候可能处于“活动状态”或者“已提交状态”；所以就要对活跃事务列表trx_ids进行查找(因为有序, 所以可二分查找): (1) 如果在trx_ids能查找到, 那么表面该事务在查询时还是未提交的状态, 应该是不可见的, 跳到步骤4 (2) 如果在trx_ids不能查找到, 那么表面该事务是提交的, 是应该查到的, 跳到步骤5.

(4) 在undo日志中, 找到该行记录的上一个事务id, 从步骤1开始判断, 直到跳到步骤5

(5) 将该可见行的值返回

可见性比较算法(针对READ_COMMIT隔离级别来举例)

相比于可重复读, 每次都是重新生成快照.

假设两个快照之间某个事物x修改了数据A, 而两次查询语句都会查询数据A.

那么第二个快照和第一个快照相比, 活跃列表就会发生变化, 在两次快照之间, x完成了提交, 那么第二次快照就会因为在trx_ids找不到x, 认为他是已提交的, 返回的就是x修改后的数据.

但是数据A在两次读之间发生了变化.

为啥READ_UNCOMMIT不能用MVCC控制

不是READ_UNCOMMIT不能用MVCC, 而是根本没必要, READ_UNCOMMIT下, 每次读取就读取最新的行就行了, 根本不需要做任何其他的操作.

MVCC为啥解决不了幻读

mysql的读分为两种, 快照读和当前读

快照读

单纯的select()操作, 也就是只是单纯的读取, 是和当前事务绑定的, 根据隔离级别遵循MVCC的控制机制. 需要根据数据的最新修改事务id来判断的

当前读

除了单纯的select()操作, 其他的update, insert, delete都是先伴随一个查询的操作, 才有后续的操作的. 此时的读取, 读取的是最新的值, 然后会对该记录加锁, 防止其它记录修改. 例如以下操作 update ... (更新操作) delete ... (删除操作) insert ... (插入操作) select ... lock in share mode (共享读锁) select ... for update (写锁

REPEATABLE下的MVCC到底为啥不能解决幻读?

如果事务中只有快照读, 那么MCCC是可以保证不会出现幻读的. 原因就是新增的数据的事务x, 根据可见性逻辑, 要么符合x >= low_limit_id, 要么属于在trx_ids中找不到, 属于未提交的情况, 因此新增的数据是看不到的.

但是如果有当前读, 不再遵循可见性比较算法, 那么就会读取到新增的数据, 此时就发生了幻读的情况.

REPEATABLE是如何解决幻读的?

REPEATABLE单纯的MVCC无法解决幻读, 但是在这个隔离级别下, mysql还会采用还会对“当前读语句”读取的记录行加记录锁（record lock）和间隙锁（gap lock），禁止其他事务在间隙间插入记录行，来防止幻读。这样就算事务中有当前读也不怕了. 即REPEATABLE实现了类似读写事务串行执行的效果

并行事务控制思想

也就是如何解决并行写事务带来的问题的思路

悲观并发控制

悲观锁: 适用于写多读少的情况即做操作前先上锁当要对数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制，在修改数据之前先锁定，再修改的方式被称之为悲观并发控制

悲观锁上的锁可以是互斥锁, 也可以是共享锁.

悲观锁的实现

借助mysql的锁, 例如通过 for update 的方式在查询的时候就进行加锁, 然后再修改数据(本身mysql会对update语句加锁, 但是查询就不会, 因此这里在查询的时候提前加锁)

乐观控制思想

乐观锁: 适用于读多写少的情况即做操作前不上锁乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。

乐观锁的实现

(1) 数据库增加版本号字段一般是通过数据库增加版本号字段, 在更新数据的时候会比较, 如果不同则不更新(需要手动实现@Verison注解)

(2) CAS操作进行比较利用先比较后更新的操作方式, 每次更新的时候, 比较当前主存中的值和期望的值是不是同一个来决定是否完成更新.

CAS的问题: CAS实现如果不是比较版本号字段, 而是通过比较某个属性字段, 即会出现ABA问题, 即字段的值本身是A, 修改成B了, 又修改成A了, 此时根据乐观锁的原理, 是不能算同一条数据的, 但是CAS无法判断出不同(当然如果你业务上认为这种是认可的, 也是可以用的)

本地SQL命令如何发送到远程服务器上的?

不使用数据库连接池的步骤：

TCP建立连接的三次握手 MySQL认证的三次握手真正的SQL执行 MySQL的关闭 TCP的四次握手关闭可以看到，为了执行一条SQL，经过非常多网络交互。

使用数据库连接池

第一次访问的时候，需要建立连接。但是之后的访问，均会复用之前创建的连接，直接执行SQL语句。

mysql 主从同步

在mysql集群中, slave需要同步主库的数据, 才能够保证整个集群的数据一致性

1).Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；

2).Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave 的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置；

3).Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”；

4).Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

为什么要relay-log日志

由于网络等原因，Binary log不可能一口气存到 I/O thread中，所以Relay log中用来缓存Binary log的事件。（Relay log存储在从服务器slave缓存中，开销比较小）

SQL语法

注释方式

以下三种方式可以作为sql脚本的里面注释内容.

(1) ## 注释

(2) -- 注释

(3) /* 注释 */

通配符

(1) % 匹配任意个字符

(2) _ 匹配一个任意字符

(3) [] 匹配集合内的任意字符例如[AB]匹配A或者B

(4) ^ 否定一个字符例如 ^A 不匹配A 或者 [^AB]不匹配A, 也不匹配B

SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本

拼接字段

(1) CONCAT() 用于连接多个字段的值, 将它们拼接起来, 类似字符串拼接

(2) TRIM() 去除列值前后的空格

SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;

(3) GROUP_CONCAT() 将多行的结果拼接成一行

拼接前:

拼接后:

SELECT GROUP_CONCAT(id) from table where parent_id = (select id from table where name = "电视");

函数

统计函数针对列是数字的

AVG() 会忽略null, 返回某列的平均值
MAX() 返回某列最大值
MIN() 返回某列最小值
SUM() 返回某列值之和

文本处理针对列是字符串的

LEFT() 从左边开始, 截取一定长度的字符串
RIGHT() 从右边开始, 截取一定长度的字符串
LOWER() 将某列的值, 转换为小写返回
UPPER() 将某列的值, 转换为大写返回

日期针对列是日期类型的

方法非常多, 能够分别返回年月日,时分秒等, 还可以操作日期加减法等.

分组

mysql 利用 group by 语法将某列中具有相同值的不同行, 放在一个集合后, 就得到了很多个不同的集合(因为某列中不同的值有很多)

(1) 生成的不同组, 会自动按照分组的列值进行排序

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col;
例如上述语句会按照col的值进行排序

(2) 分组后的结果如果能排序, 可以利用order by 排序

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;
这里分组的结果 会按num再次排序, 本身col的排序就不在了

(3) where 可以对分组前的数据进行过滤, having 可以对分组的结果进行过滤

SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;
这里where对col进行了过滤, having对分组后的结果进行了过滤

子查询

子查询是因为我有个参数param想查询A表, 但是无法直接查询得到, 只能分为两步, 第一我先用param查询B, 从B得到某个结果, 第二再去查询A. 子查询就是将这两步变成了一条SQL.

(1) 标量子查询: 子查询只能返回一个字段的数据

SELECt * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1)

(2) 列子查询: 子查询返回一列多行的数据

SELECT * FROM mytable1 WHERE col1 IN (SELECT col2 FROM mytable2);

(3) 行子查询: 子查询返回一行多列的数据

SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)

(4) 表子查询: 子查询返回多行多列, 返回的结果作为表

SELECT * FROM students
INNER JOIN (SELECT Java, Python, English, Math, id FROM course) AS course1 ON students.Id = course1.Id
WHERE Math > (SELECT AVG(Math) FROM course);

连接

连接其实就是把多个表的数据都摆到你面前, 你可以使用条件在多个表中查询某些数据.

连接用于连接多个表, 采用JOIN关键字, 连接够多个表的筛选条件使用ON关键字连接表的目的和子查询的目的基本是一致的, 因此连接可以替换子查询, 并且查询效率一般会高于子查询

内连接 INNER JOIN

等值连接, 将符合等值表达式的不同表里的数据连接起来

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

自然连接 NATURAL JOIN

自动将两张表里所有同名的列作为等值条件, 连接起来

SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;

外连接

内连接返回的是两表中都符合等值条件的数据行, 如果一条数据在A表中查找到了, 在B中没有对应数据, 那么内连接不会返回该数据. 外连接对于上述情况就会返回数据, 另一张表中找不到的数据就填充null

(1) 左外连接 LEFT OUTER JOIN 等同于 LEFT JOIN

即以左表为基准, 左表中的数据会全部展示, 对应的右表数据找不到填充null

SELECT * FROM tbl_dept a LEFT JOIN tbl_emp b ON a.id=b.deptId;

(2) 右外连接 RIGHT OUTER JOIN

即以右表为基准, 右表中的数据会全部展示, 对应的左表数据找不到填充null

(3) 全外连接 FULL OUTER JOIN

mysql 不支持full join, 只能采用union的方式, 将左查询和右查询的结果结合的方式

select * from A left join B on B.name = A.name 
union 
select * from A right join B on B.name = A.name;

组合查询 UNION

使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。

每个查询必须包含相同的列、表达式和聚集函数。

默认会去除相同行，如果需要保留相同行，使用 UNION ALL。只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;

应用场景

(1) 如果不同的表具有相同的结构, 那么可以在一次查询中返回两个表中查到的数据

这种情况很少见吧?

(2) 对一个表的多次查询, 可以拼接成一次查询.

那为啥不将多次查询的条件组合起来, 弄个In查询呢.

何时用子查询? 何时用连接

多表连接和子查询在大多数情况下是可以通用的，即能用多表连接解决的问题也能由子查询解决，由子查询解决的问题也能由多表连接完成。但在以下个方面二者不能通用：当查询的内容包含了几张表独有的字段时，只能用多表连接实现；当查询中涉及到使用集合函数或值的比较时，使用子查询更为方便；

视图

视图就是虚拟的表, 是通过sql语句执行后生成的虚拟的表, 本身不存储数据. 操作视图就跟操作普通表一样, 视图存在的目的就是为了简化你的sql语句, 每次不用写那么长的的sql语句, 我觉得可以理解为一种映射关系.

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;

好处

简化复杂的 SQL 操作，比如复杂的连接；
只使用实际表的一部分数据, 通过使用视图, 用户只能访问某些表的指定列；
通过只给用户访问视图的权限，保证数据的安全性；
更改数据格式和表示。

视图数据变更

视图数据会根据你创建视图时候的sql语句, 自动进行相应的数据变化
一般使用视图的时候, 很少会对视图数据进行更新, 视图数据更新, 原表数据也会更新.
视图能否更新数据是首视图建立的条件限制的

存储过程

存储过程就是在mysql里编写一个函数, 函数体都是mysql语句, 在使用时传入参数就可以执行不同的逻辑.

好处

代码封装，保证了一定的安全性；
代码复用；
由于是预先编译，因此具有很高的性能。

触发器

触发器可以设置为在执行某个sql语句后, 自动执行一些sql语句, 可自动执行的sql类型为INSERT, DELETE, UPDATE.

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; -- 获取结果

MySQL相关