Mysql

视图

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。其内容由查询定义。基表：用来创建视图的表叫做基表。通过视图，可以展现基表的部分数据。视图数据来自定义视图的查询中使用的表，使用视图动态生成。

优点

简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。


CREATE VIEW <视图名> AS <SELECT语句>

触发器

触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行DML操作（ insert ， delete ， update ）时就会激活它执行。

监视对象： table

监视事件： insert 、 update 、 delete

触发时间： before ， after

触发事件： insert 、 update 、 delete


CREATE TABLE `work` (
    `id` INT PRIMARY KEY auto_increment,
    `address` VARCHAR (32)
) DEFAULT charset = utf8 ENGINE = INNODB;

CREATE TABLE `time` (
    `id` INT PRIMARY KEY auto_increment,
    `time` DATETIME
) DEFAULT charset = utf8 ENGINE = INNODB;

CREATE TRIGGER trig_test1 AFTER INSERT
ON `work` FOR EACH ROW
INSERT INTO `time` VALUES(NULL,NOW());

存储过程

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

优点

能完成较复杂的判断和运算有限的编程
可编程行强，灵活
SQL编程的代码可重复使用
执行的速度相对快一些
减少网络之间的数据传输，节省开销


CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数
据类型…]]) [特性 ...] 过程体

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用",“分割开。

MySQL 存储过程的参数用在存储过程的定义，共有三种参数类型 IN , OUT , INOUT 。

IN ：参数的值必须在调用存储过程时指定，0在存储过程中修改该参数的值不能被返回，可以设置默认值

OUT ：该值可在存储过程内部被改变，并可返回

INOUT ：调用时指定，并且可被改变和返回

过程体的开始与结束使用 BEGIN 与 END 进行标识。

游标

游标是针对行操作的，对从数据库中 select 查询得到的结果集的每一行可以进行分开的独立的相同或者不相同的操作。

对于取出多行数据集，需要针对每行操作；可以使用游标；游标常用于存储过程、函数、触发器、事件。

游标相当于迭代器

直接来个例子就知道：


CREATE PROCEDURE proc_while (
IN age_in INT,
OUT total_out INT
)
BEGIN
-- 创建 用于接收游标值的变量
DECLARE p_id,p_age,p_total INT ;
DECLARE p_sex TINYINT ;
-- 注意:接收游标值为中文时,需要给变量 指定字符集utf8
DECLARE p_name VARCHAR (32) CHARACTER SET utf8 ; -- 游标结束的标志
DECLARE done INT DEFAULT 0 ; -- 声明游标
DECLARE cur_teacher CURSOR FOR SELECT
teacher_id,
teacher_name,
teacher_sex,
teacher_age
FROM
teacher
WHERE
teacher_age > age_in ; -- 指定游标循环结束时的返回值
DECLARE CONTINUE HANDLER FOR NOT found
SET done = 1 ; -- 打开游标
OPEN cur_teacher ; -- 初始化 变量
SET p_total = 0 ; -- while 循环
WHILE done != 1 DO
FETCH cur_teacher INTO p_id,
p_name,
p_sex,
p_age ;
IF done != 1 THEN
SET p_total = p_total + 1 ;
END
IF ;
END
WHILE ; -- 关闭游标
CLOSE cur_teacher ; -- 将累计的结果复制给输出参数
SET total_out = p_total ;
END//
delimiter ;
-- 调用
SET @p_age =20;
CALL proc_while(@p_age, @total);
SELECT @total;

索引

分类：主键索引、唯一索引、普通索引、组合索引、以及全文索引；

主键索引

非空唯一索引，一个表只有一个主键索引；在 innodb 中，主键索引的B+树包含表数据信息。


PRIMARY KEY(key)

唯一索引

不可以出现相同的值，可以有NULL值。


UNIQUE(key)

普通索引

允许出现相同的索引内容。


INDEX(key)
-- OR
KEY(key[,...])

组合索引

对表上的多个列进行索引。符合最左匹配原则：从左到右依次匹配，遇到 > < between like 就停止匹配；


INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);

全文索引

将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；关键词 FULLTEXT; 在短字符串中用 LIKE % ；在全文索引中用 match 和 against。

主键选择规则

innodb 中表是索引组织表，每张表有且仅有一个主键。

如果显示设置 PRIMARY KEY ，则该设置的key为该表的主键。
如果没有显示设置，则从非空唯一索引中选择。
1. 只有一个非空唯一索引，则选择该索引为主键。
2. 有多个非空唯一索引，则选择声明的第一个为主键。
没有非空唯一索引，则自动生成一个 6 字节的 _rowid 作为主键。

索引实现

索引存储

innodb由段、区、页组成；段分为数据段、索引段、回滚段等。区大小为 1 MB（一个区由64个连续页构成）。页的默认值为16k。页为逻辑页，磁盘物理页大小一般为 4K 或者 8K。为了保证区中的页的连续，存储引擎一般一次从磁盘中申请 4~5 个区。

聚集索引与辅助索引

聚集索引就是以主键为key构造B+树

辅助索引就是以辅助索引为key构造B+树，value为对应的主键

页和B+树

B+树单个节点是页，页中由id再向下分裂指向多个页。

我们知道主键索引的id就是B+树节点的key，为了范围查询我们不使用hashmap，那为什么不用B树呢？

这是因为B树在叶子节点上保存数据，执行范围查询时，可能需要反复载入不连续的页，cache和内存的利用率都不是很高。而B+树直接进入叶子节点，然后范围查询只需要根据叶子节点之间的指针载入连续的页就行，这种确定的内存访问优化空间更大，效率更高。

索引失效

select … where A and B 若 A 和 B 中有一个不包含索引，则索引失效。
索引字段参与运算，则索引失效；例如： from_unixtime(idx) = ‘2021-04-30’。
索引字段发生隐式转换，则索引失效；例如： ‘1’ 隐式转换为 1 。
LIKE 模糊查询，通配符 % 开头，则索引失效；例如： select * from user where name like ‘%ark’。
在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0 则修改为 idx > 0 or idx < 0 。
组合索引中，没使用第一列索引，索引失效。

索引原则

查询频次较⾼且数据量⼤的表建⽴索引；索引选择使⽤频次较⾼，过滤效果好的列或者组合。
使⽤短索引；节点包含的信息多，较少磁盘io操作。
对于很长的动态字符串，考虑使用前缀索引。
对于组合索引，考虑最左侧匹配原则和覆盖索引。
尽量选择区分度⾼的列作为索引；该列的值相同的越少越好。
尽量扩展索引，在现有索引的基础上，添加复合索引。
不要 select * ；尽量只列出需要的列字段。
索引列，列尽量设置为非空。

约束

为了实现数据的完整性，对于innodb，提供了以下几种约束，primary key，unique key，foreign key， default, not null。

外键约束

外键用来关联两个表，来保证参照完整性；MyISAM存储引擎本身并不支持外键，只起到注释作用。而innodb完整支持外键。

约束于索引的区别

创建主键索引或者唯一索引的时候同时创建了相应的约束；但是约束是逻辑上的概念；索引是一个数据结构既包含逻辑的概念也包含物理的存储方式；

事务

事务将数据库从一种一致性状态转换为另一种一致性状态。

在数据库提交事务时，可以确保要么所有修改都已经保存，要么所有修改都不保存。

事务是访问并更新数据库各种数据项的一个程序执行单元。

在 MySQL innodb 下，每一条语句都是事务。可以通过 set autocommit = 0，设置当前会话手动提交。


-- 显示开启事务
START TRANSACTION | BEGIN
-- 提交事务，并使得已对数据库做的所有修改持久化
COMMIT
-- 回滚事务，结束用户的事务，并撤销正在进行的所有未提交的修改
ROLLBACK
-- 创建一个保存点，一个事务可以有多个保存点
SAVEPOINT identifier
-- 删除一个保存点
RELEASE SAVEPOINT identifier
-- 事务回滚到保存点
ROLLBACK TO [SAVEPOINT] identifier

ACID特性

原子性（A）

事务操作要么都做（提交），要么都不做（回滚）。事务是访问并更新数据库各种数据项的一个程序执行单元，是不可分割的工作单位。通过undolog来实现回滚操作。undolog记录的是事务每步具体操作，当回滚时，回放事务具体操作的逆运算。

隔离性（I）

事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，也就是事务提交前对其他事务都不可见。通过 MVCC 和锁来实现。MVCC 时多版本并发控制，主要解决一致性非锁定读，通过记录和获取行版本，而不是使用锁来限制读操作，从而实现高效并发读性能。锁用来处理并发 DML 操作。数据库中提供粒度锁的策略，针对表（聚集索引B+树）、页（聚集索引B+树叶子节点）、行（叶子节点当中某一段记录行）三种粒度加锁。

持久性（D）

事务提交后，事务DML操作将会持久化（写入redolog磁盘文件哪一个页页偏移值具体数据）。即使发生宕机等故障，数据库也能将数据恢复。redolog记录的是物理日志。

一致性（C）

一致性指事务将数据库从一种一致性状态转变为下一种一致性的状态，在事务执行前后，数据库完整性约束没有被破坏。例如：一个表的姓名是唯一键，如果一个事务对姓名进行修改，但是在事务提交或事务回滚后，表中的姓名变得不唯一了，这样就破坏了一致性。一致性由原子性、隔离性以及持久性共同来维护的。

事务并发异常

脏读

事务（A）可以读到另外一个事务（B）中未提交的数据，也就是事务A读到脏数据。在读写分离的场景下，可以将slave节点设置为 READ UNCOMMITTED。此时脏读不影响，在slave上查询并不需要特别精准的返回值。

不可重复读

事务（A) 可以读到另外一个事务（B）中提交的数据。通常发生在一个事务中两次读到的数据是不一样的情况。不可重复读在隔离级别 READ COMMITTED 存在。一般而言，不可重复读的问题是可以接受的，因为读到已经提交的数据，一般不会带来很大的问题，所以很多厂商（如Oracle、SQL Server）默认隔离级别就是READ COMMITTED。

幻读

事务中一次读操作不能支撑接下来的业务逻辑。通常发生在一个事务中一次读判断接下来写操作失败的情况。例如：以name为唯一键的表，一个事务中查询 select * from t where name =‘mark’。不存在，接下来 insert into t(name) values (‘mark’)。出现错误，此时另外一个事务也执行了 insert 操作。幻读在隔离级别 REPEATABLE READ 及以下存在。但是可以在REPEATABLE READ 级别下通过读加锁（使用next-key locking）解决。

隔离级别

ISO和ANIS SQL标准制定了四种事务隔离级别的标准，各数据库厂商在正确性和性能之间做了妥协，并没有严格遵循这些标准。MySQL innodb默认支持的隔离级别是 REPEATABLE READ。

READ UNCOMMITTED

读未提交：该级别下读不加锁，写加排他锁，写锁在事务提交或回滚后释放锁。

READ COMMITTED

读已提交：从该级别后支持 MVCC (多版本并发控制)，也就是提供一致性非锁定读。此时读取操作读取历史快照数据。该隔离级别下选择读取快照中历史版本的最新数据，所以读取的是已提交的数据。

REPEATABLE READ

可重复读：该级别下也支持 MVCC，此时读取操作读取事务开始时的版本数据。

SERIALIZABLE

可串行化：该级别下给读加了共享锁。所以事务都是串行化的执行，此时隔离级别最严苛。


-- 设置隔离级别
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 或者采用下面的方式设置隔离级别
SET @@tx_isolation = 'REPEATABLE READ';
SET @@global.tx_isolation = 'REPEATABLE READ';
-- 查看全局隔离级别
SELECT @@global.tx_isolation;
-- 查看当前会话隔离级别
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
-- 手动给读加 S 锁
SELECT ... LOCK IN SHARE MODE;
-- 手动给读加 X 锁
SELECT ... FOR UPDATE;
-- 查看当前锁信息
SELECT * FROM information_schema.innodb_locks;

锁

锁机制用于管理对共享资源的并发访问，用来实现事务的隔离级别。

粒度

共享锁和排他锁都是行级锁。MySQL当中事务采用的是粒度锁。针对表（B+树）、页（B+树叶子节点）、行（B+树叶子节点当中某一段记录行）三种粒度加锁。

意向共享锁和意向排他锁都是表级别的锁。

共享锁（S）

事务读操作加的锁，对某一行加锁。

在 SERIALIZABLE 隔离级别下，默认帮读操作加共享锁。

在 REPEATABLE READ 隔离级别下，需手动加共享锁，可解决幻读问题。

在 READ COMMITTED 隔离级别下，没必要加共享锁，采用的是 MVCC。

在 READ UNCOMMITTED 隔离级别下，既没有加锁也没有使用 MVCC。

排他锁（X）

事务删除或更新加的锁；对某一行加锁。

在4种隔离级别下，都添加了排他锁，事务提交或事务回滚后释放锁。

意向共享锁（IS）

对一张表中某几行加的共享锁。

意向排他锁（IX）

对一张表中某几行加的排他锁。

兼容性

由于innodb支持的是行级别的锁，意向锁并不会阻塞除了全表扫描以外的任何请求。

意向锁之间是互相兼容的。

IX 对共享锁和排他锁都不兼容。

IS 只对排他锁不兼容。

当想为某一行添加 S 锁，先自动为所在的页和表添加意向锁 IS，再为该行添加 S 锁。

当想为某一行添加 X 锁，先自动为所在的页和表添加意向锁 IX，再为该行添加 X 锁。

锁算法

Record Lock: 记录锁，单个行记录上的锁。
Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。全开区间。REPEATABLE READ级别及以上支持间隙锁。
Next-Key Lock：记录锁+间隙锁，锁定一个范围，并且锁住记录本身。左开右闭区间。
Insert Intention Lock：插入意向锁，insert操作的时候产生。在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。
AUTO-INC Lock：自增锁，是一种特殊的表级锁，发生在 AUTO_INCREMENT 约束下的插入操作。采用的一种特殊的表锁机制。完成对自增长值插入的SQL语句后立即释放。在大数据量的插入会影响插入性能，因为另一个事务中的插入会被阻塞。从MySQL 5.1.22开始提供一种轻量级互斥量的自增长实现机制，该机制提高了自增长值插入的性能。

MVCC

多版本并发控制，用来实现一致性的非锁定读。非锁定读是指不需要等待访问的行上X锁的释放。

在 read committed 和 repeatable read下，innodb使用MVCC。

对于快照数据的定义不同；

在 read committed 隔离级别下，对于快照数据总是读取被锁定行的最新一份快照数据。
repeatable read 隔离级别下，对于快照数据总是读取事务开始时的行数据版本。

redolog

redo log用来实现事务的持久性。内存中包含 redo log buffer，磁盘中包含 redo log file。

当事务提交时，必须先将该事务的所有日志写入到redolog进行持久化，待事务的commit操作完成才完成了事务的提交。

redo log 顺序写，记录的是对每个页的修改（页、页偏移量、以及修改的内容）。在数据库运行时不需要对 redo log 的文件进行读取操作，只有发生宕机的时候，才会拿redo log进行恢复。

undolog

undo log用来帮助事务回滚以及MVCC的功能。

存储在共享表空间中， undo 是逻辑日志，回滚时将数据库逻辑地恢复到原来的样子，根据 undo log 的记录，做之前的逆运算。

比如事务中有insert 操作，那么执行 delete 操作，对于 update 操作执行相反的 update 操作。

同时 undo 日志记录行的版本信息，用于处理 MVCC 功能。

主从复制

主库更新事件(update、insert、delete)通过io-thread写到binlog。
从库请求读取binlog，通过io-thread写⼊（write）从库本地 relay log（中继⽇志）。
从库通过sql-thread读取（read） relay log，并把更新事件在从库中执⾏（replay）⼀遍。

读写分离

写主（mysql）读从（内存式数据库）

缓冲层

使用redis或memcached作为缓冲层，保存一些mysql的热点数据，用于应对高频的读操作。

缓冲策略主要是优化的lru。

但用户无法控制具体缓存哪些东西（与业务无关）

一致性问题

主从之间存在数据一致性问题

简单的读写策略可以解决大部分问题：（感觉可以参考CPU cache的策略）

写：写mysql，mysql写完成后同步给redis（有点问题的，因为redis里的还是旧的）
读：读redis，不可用的话再去mysql中找，mysql有就返回并把数据回写redis

强⼀致性

上面写策略还存在点漏洞，这里补齐：

写：先删除redis里的缓存，再去写到mysql中，这样就避免了读到旧数据。

但只适用于单数据中心，如果是多数据中心，可以设置强一致性需求忽略缓存层，或转化为单数据中心后操作。

最终⼀致性

只确保最终的一致性，那就使用一开始的方案就行

但还有一个替代方案：

先写redis，设置key的过期时间为200ms（经验值），等待mysql回写redis，覆盖key，设置更⻓的过期时间。

200ms 默认的是写mysql到mysql同步到redis的时⻓；这个需要根据实际环境进⾏设置。

缓存穿透

假设某个数据redis不存在，mysql也不存在，⽽且⼀直尝试读就会发生缓存穿透，数据最终压⼒依然堆积在mysql，可能造成mysql不堪重负⽽崩溃。

方案

发现mysql不存在，将redis设置为 <key, nil> 设置过期时间下次访问key的时候不再访问mysql 容易造成redis缓存很多⽆效数据。
布隆过滤器，将mysql当中已经存在的key，写⼊布隆过滤器，不存在的直接pass掉；

缓存击穿

某些数据redis没有，但是mysql有。此时当⼤量这类数据的并发请求，同样造成mysql压力过⼤。

方案

加锁：请求数据的时候获取锁，如果获取成功，则操作完后释放锁。获取失败，则休眠⼀段时间（200ms）再去获取。
增加redis中的存活时间：将很热的key，设置不过期（redis中）

缓存雪崩

表示⼀段时间内，缓存集中失效(redis⽆ mysql有)，导致请求全部⾛mysql，有可能搞垮数据库，使整个服务失效。

方案

如果因为缓存数据库宕机，造成所有数据涌向mysql：那就采⽤⾼可⽤的集群⽅案，如哨兵模式、cluster模式；
如果因为设置了相同的过期时间，造成缓存集中失效：那就设置随机过期值或者其他机制错开失效。
如果因为系统重启的时候，造成缓存数据消失：重启时间短，redis开启持久化（过期信息也会持久化）就⾏了。重启时间⻓，提前将热数据导⼊redis当中。