Mysql-索引，事务，集群，缓存

索引

---

分类

主键索引

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

PRIMARY KEY(key)

唯一索引

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

UNIQUE(key)

普通索引

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

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

组合索引

对表上的多个列进行索引

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

全文索引

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

主键选择

innodb 中表是索引组织表，每张表有且仅有一个主键；
1. 如果显式设置 PRIMARY KEY ，则该设置的key为该表的主键；
2. 如果没有显式设置，则从非空唯一索引中选择；
1. 只有一个非空唯一索引，则选择该索引为主键；
2. 有多个非空唯一索引，则选择声明的第一个为主键；
3. 没有非空唯一索引，则自动生成一个 6 字节的 _rowid 作为主键；

约束

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

---

外键约束

外键用来关联两个表，来保证参照完整性；/[ƍ5$/存储引擎本身并不支持外键，只起到注释作
用；而KPPQFD完整支持外键；

create table parent (
    id int not null,
    primary key(id)
) engine=innodb;

create table child (
    id int,
    parent_id int,
    foreign key(parent_id) references parent(id) ON DELETE CASCADE ON UPDATE
    CASCADE
) engine=innodb;

-- 被引用的表为父表，引用的表称为子表；
-- 外键定义时，可以设置行为 ON DELETE 和 ON UPDATE，行为发生时的操作可选择：
-- CASCADE 子表做同样的行为
-- SET NULL 更新子表相应字段为 NULL
-- NO ACTION 父类做相应行为报错
-- RESTRICT 同 NO ACTION
INSERT INTO parent VALUES (1);
INSERT INTO parent VALUES (2);
INSERT INTO child VALUES (10, 1);
INSERT INTO child VALUES (20, 2);
DELETE FROM parent WHERE id = 1;

约束于索引的区别

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

索引实现

---

索引存储

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

页

页是 innodb 磁盘管理的最小单位；默认16k，可通过 innodb_page_size 参数来修改；
B+树的一个节点的大小就是该页的值

B+树

B+树层高问题

B+树的一个节点对应一个数据页；B+树的层越高，那么要读取到内存的数据页越多，io次数越多；
innodb一个节点16kB；
假设：
key为10byte且指针大小6byte，假设一行记录的大小为1kB；
那么一个非叶子节点可存下16kB/16byte=1024个（key+point）；每个叶子节点可存储1024行数据；
结论：
2层B+树叶子节点1024个，可容纳最大记录数为： 1024 * 16 = 16384；
3层B+树叶子节点1024 * 1024，可容纳最大记录数为：1024 * 1024 * 16 = 16777216；
4层B+数叶子节点1024 * 1024 * 1024，可容纳最大记录数为：1024 * 1024 * 1024 * 16 =
17179869184;

聚集索引

按照主键构造的 B+ 树；叶子节点中存放数据页；数据也是索引的一部分；

select * from user where id >= 18 and id < 40;     # id 是主键

关于自增id

超过类型最大值会报错；
类型 bigint 范围： ;
假设采用 bigint 1秒插入1亿条数据，大概需要5849年才会用完索引；

辅助索引

叶子节点不包含行记录的全部数据；辅助索引的叶子节点中，除了用来排序的key还包含一个
bookmark；该书签存储了聚集索引的key

-- 某个表 包含 id name lockyNum; id是主键，lockyNum存储辅助索引;
select * from user where lockyNum = 33;

Change Buffer

innodb存储引擎对非唯一辅助索引的 DML 操作进行缓冲；包含 insert buffer，delete buffer，purge buffer；
change buffer 是一颗B+树，所有的非唯一辅助索引的 change buffer 存储在一棵B+树上，存储在共享存储表空间；
对于非唯一辅助索引的 DML 操作不是每一次直接插入到索引页，先判断插入的非唯一辅助索引页是否在缓冲区中，如果在，直接插入；如果不在，则先放到一个 change buffer 对象中，然后再以一定的频率将 change buffer当中的数据合并到索引页中；

自适应hash索引

hash索引的时间复杂度是 ；
自适应hash索引通过缓冲池的B+树页来构建的；innodb存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立hash索引；启用后，读取和写入速度提高2倍，辅助索引的操作性能提高5倍；
只能用于等值查询，不能用于范围查询；

最左匹配原则

对于组合索引，从左到右依次匹配，遇到> < between like 就停止匹配

覆盖索引

从辅助索引中就能找到数据，而不需通过聚集索引查找；利用辅助索引树高度一般低于聚集索引树；较少磁盘io；

索引失效

---

• 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操作；
• 对于很长的动态字符串，考虑使用前缀索引；

有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的
部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索
引的选择性是指不重复的索引值和数据表记录总数的比值。索引的选择性越高则查询效率越
高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。对于
BLOB,TEXT,VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

select count(distinct left(name,3))/count(*) as sel3,
count(distinct left(name,4))/count(*) as sel4,
count(distinct left(name,5))/count(*) as sel5,
count(distinct left(name,6))/count(*) as sel6,
from user;
alter table user add key(name(4));
-- 注意：前缀索引不能做 order by 和 group by

• 对于组合索引，考虑最左侧匹配原则和覆盖索引；
• 尽量选择区分度⾼的列作为索引；该列的值相同的越少越好

select count(distinct idx) / count(*) from table_name;

• 尽量扩展索引，在现有索引的基础上，添加复合索引；
• 不要 select * ； 尽量只列出需要的列字段；
• 索引列，列尽量设置为非空；

性能优化 EXPLAIN

用来查看SQL语句的具体执行过程。

原理：模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。

---

执行计划

Column	Meaning
id	The SELECT identifier （查询id）
select_type	The SELECT type （查询类型）
table	The table for the output row （输出结果集的表）
partitions	The matching partitions （匹配的分区）
type	The join type （表的连接类型）
possible_keys	The possible indexes to choose（可能使用的索引）
key	The index actually chosen （实际使用的索引）
key_len	The length of the chosen key （索引字段的长度）
ref	The columns compared to the index （列与索引的比较）
rows	Estimate of rows to be examined （预估扫描行数）
filtered	Percentage of rows filtered by table condition （按表条件过滤的行百分比）
extra	Additional information （额外信息，如是否使用索引覆盖）

id

select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序；
id号分为三种情况：
1. id相同，那么执行顺序从上到下；
2. id不同，id越大越先执行；
3. id有相同的也有不同的，id相同的按 1 执行，id不同的按 2 执行；

select_type

主要用来分辨查询的类型，是普通查询还是联合查询还是子查询

select_type Value	Meaning
SIMPLE	Simple SELECT (not using UNION or subqueries) （简单查询-没有联合查询 和子查询）
PRIMARY	Outermost SELECT (最外层select)
UNION	Second or later SELECT statement in a UNION （若第二个select出现在 union之后，则被标记为union）
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query （union或union all联合而成的结果会受外部表影响）
UNION RESULT	Result of a UNION. （从union表获取结果的select）
SUBQUERY	First SELECT in subquery （在select或者where列表中包含子查询）
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query（subquery的子查询 要受到外部表查询的影响）
DERIVED	Derived table（ from子句中出现的子查询，也叫做派生类）
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re evaluated for each row of the outer query（表示使用子查询的结果不能被 缓存）
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)（表示union的查询结果不能被缓 存：sql语句未验证）

table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集;
1. 具体表名或者表的别名，从具体的物理表中获取数据；
2. 表明为derivedN的形式，表示 id 为 N 的查询产生的衍生表；
3. 当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id;

type

type 显示访问类型；采用怎么样的方式来访问数据；效率从好到坏依次为：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery >
index_subquery > range > index > ALL
ALL ： 全表扫描；如果数据量大则需要进行优化；
index ：全索引扫描这个比 ALL 的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序；
range ：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了 index 的全索引扫描，适用的操作符： = , <> , > , >= , < , <= , IS NULL , BETWEEN , LIKE , or IN()
index_subquery ：利用索引来关联子查询，不再扫描全表；
unique_subquery ：该连接类型类似与 index_subquery ，使用的是唯一索引；
index_merge ：在查询过程中需要多个索引组合使用；
ref_or_null ：对于某个字段即需要关联条件，也需要 null 值的情况下，查询优化器会选择这
种访问方式；
ref ：使用了非唯一性索引进行数据的查找；
eq_ref ：使用唯一性索引进行数据查找；
const ：这个表至多有一个匹配行；
system ：表只有一行记录（等于系统表），这是 const 类型的特例；

possible_keys

查询涉及到字段的索引，则这些索引都会列举出来，但是不一定采纳；

key

实际使用的索引，如果为 NULL ，则没有使用索引

key_len

 表示索引中使用的字节数；查询中使用的索引长度；在不损失精度的情况下长度越短越好。

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数；

rows

大致估算出找出所需记录需要读取的行数，反映了sql找了多少条数据，该值越小越好。

extra

额外信息；
using filesort ：使用了文件排序；
using temporary ：建立临时表来保存中间结果，查询完成之后把临时表删除；
using index ：采用覆盖索引，直接从索引中读取数据，而不用访问数据表。如果同时出现using
where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的
查找；
using where ：使用where进行条件过滤；
using join buffer ：使用连接缓存；
impossible where ： where 语句的结果总是 false ;

优化器选择过程

优化器根据解析树可能会生成多个执行计划，然后选择最优的的执行计划；

SHOW VARIABLES LIKE 'optimizer_trace';
-- 启用优化器的追踪
SET optimizer_trace='enabled=on';
-- 执行一条查询语句
SELECT * FROM information_schema.optimizer_trace;
-- 用完关闭
SET optimizer_trace="enabled=off";
SHOW VARIABLES LIKE 'optimizer_trace';

慢日志查询

---

开启

查看

SHOW GLOBAL VARIABLES LIKE 'slow_query%';
SHOW GLOBAL VARIABLES LIKE 'long_query%';

设置

SET GLOBAL slow_query_log = ON; -- on 开启 off 关闭
SET GLOBAL long_query_time = 4; -- 单位秒；默认10s；此时设置为4s

或者修改配置

slow_query_log = ON
long_query_time = 4
slow_query_log_file = D:/mysql/mysql57-slow.log

mysqldumpslow

查找最近10条慢查询日志

mysqldumpslow -s t -t 10 -g 'select' D:/mysql/mysql57-slow.log

SHOW PROFILE

# 查看是否开启
SELECT @@profiling;
# 设置开启
SET profiling = 1;
# 查看所有 profiles
show profiles;
# 查看query id 为 10 那条查询
show profile for query 10;
# 查看最后一条查询
show profile;
# 最后关闭
SET profiling = 0;

SHOW PROCESSLIST

查看连接线程；可以查看此时线上运行的 sql 语句；
如果要查看完整的SQL语句：SHOW FULL PROCESSLIST; 然后优化该语句；
 

事务 

---

目的

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

组成

事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成；

特征

在数据库提交事务时，可以确保要么所有修改都已经保存，要么所有修改都不保存；
事务是访问并更新数据库各种数据项的一个程序执行单元。
在 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上查询并不需要特别精准的返回值。

seq	session A	session B
1	SET @@tx_isolation='READ UNCOMMITTED';
2	BEGIN;
3	SELECT * FROM dirty_read_t WHERE id > 3;
4		SET @@tx_isolation='READ UNCOMMITTED';
5		BEGIN;
6		INSERT INTO dirty_read_t (id, name, sex, age) VALUES (5, 'milo', 2, 20);
7	SELECT * FROM dirty_read_t WHERE id > 3;

不可重复读

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

seq	session A	session B
1	SET @@tx_isolation='READ COMMITTED';
2		SET @@tx_isolation='READ COMMITTED';
3	BEGIN;
4		BEGIN;
5	SELECT * FROM t WHERE id > 3;
6		INSERT INTO t (id, name, sex,age) VALUES (5, 'milo', 2, 20);
7		COMMIT;
8	SELECT * FROM t WHERE id > 3;

幻读

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

seq	session A	session B
1	SET @@tx_isolation='REPEATABLE READ';
2		SET @@tx_isolation='REPEATABLE READ';
3	BEGIN;
4	SELECT * FROM t WHERE id = 5;
5		BEGIN;
6		INSERT INTO t (id, name, sex, age) VALUES (5, 'milo', 2, 20);
7		COMMIT;
8	INSERT INTO t (id, name, sex, age) VALUES (5, 'milo', 2, 20);

解决

seq	session A	session B
1	SET @@tx_isolation='REPEATABLE READ';
2		SET @@tx_isolation='REPEATABLE READ';
3	BEGIN;
4	SELECT * FROM t WHERE id = 5 LOCK IN SHARE MODE;
5		BEGIN;
6		INSERT INTO t (id, name, sex,age)VALUES(5, 'milo', 2, 20);
7	INSERT INTO t (id, name, sex,age)VALUES(5, 'milo', 2, 20);

隔离级别

---

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

READ UNCOMMITTED

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

READ COMMITTED

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

REPEATABLE READ

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

SERIALIZABLE

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

不同隔离级别下并发异常

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	存在	存在	存在
READ COMMITTED	不存在	存在	存在
REPEATABLE READ	不存在	不存在	存在（可手动给读加锁解决）
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）

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

锁的兼容性

锁	S	X	IS	IX
S	兼容	冲突	兼容	冲突
X	冲突	冲突	冲突	冲突
IS	兼容	冲突	兼容	兼容
IX	冲突	冲突	兼容	兼容

由于innodb支持的是行级别的锁，意向锁并不会阻塞除了全表扫描以外的任何请求；
意向锁之间是互相兼容的；
IX 对共享锁和排他锁都不兼容；
IS 只对排他锁不兼容；
当想为某一行添加 S 锁，先自动为所在的页和表添加意向锁 IS，再为该行添加 S 锁；
当想为某一行添加 X 锁，先自动为所在的页和表添加意向锁 IX，再为该行添加 X 锁；

锁算法

Record Lock

记录锁，单个行记录上的锁；

Gap Lock

间隙锁，锁定一个范围，但不包含记录本身；全开区间；REPEATABLE READ级别及以上支持间隙锁；
如果 REPEATABLE READ 修改 innodb_locks_unsafe_for_binlog = 0 ，那么隔离级别相当于
退化为 READ COMMITTED；

-- 查看是否支持间隙锁，默认支持，也就是 innodb_locks_unsafe_for_binlog = 0;
SELECT @@innodb_locks_unsafe_for_binlog;

Next-Key Lock

记录锁+间隙锁，锁定一个范围，并且锁住记录本身；左开右闭区间；

Insert Intention Lock

插入意向锁，insert操作的时候产生；在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。
假设有一个记录索引包含键值4和7，两个不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

锁兼容

锁	GAP	Insert Intention	Record	Next-key
GAP	兼容	兼容	兼容	兼容
Insert Intention	冲突	兼容	兼容	冲突
Record	兼容	兼容	冲突	冲突
Next-key	兼容	兼容	冲突	冲突

横向：表示已经持有的锁；纵向：表示正在请求的锁；

 AUTO-INC Lock

自增锁，是一种特殊的表级锁，发生在 AUTO_INCREMENT 约束下的插入操作；采用的一种特殊的表锁机制；完成对自增长值插入的SQL语句后立即释放；在大数据量的插入会影响插入性能，因为另一个事务中的插入会被阻塞；从MySQL 5.1.22开始提供一种轻量级互斥量的自增长实现机制，该机制提高了自增长值插入的性能；

锁的对象

行级锁是针对表的索引加锁；索引包括聚集索引和辅助索引；
表级锁是针对页或表进行加锁；
考虑 InnoDB 在 read committed 和 repeatable read 级别下锁的情况；

示例1

select * from t where id = 5 for update; lock in share mode
-- id 为主键 Read committed 隔离级别
-- 在主键 id = 5 行上加 X 锁
-- id 是唯一索引 Read committed 隔离级别
-- 在唯一索引id=5行上加X锁，在主键索引上对应行加X锁
-- id 是非唯一索引 Read committed 隔离级别
-- 在非唯一索引上所有id=5行加上X锁，对应的主键索引列加上X锁
-- id 不是索引 Read committed 隔离级别
-- 在聚集索引上扫描，所有行上加X锁，此处有个优化，不满足的行在加锁后，判断不满足即可释放锁
-- id 为主键 repeatable read 隔离级别
-- 在主键id=5行上加X锁
-- id 是唯一索引 repeatable read 隔离级别
-- 在唯一索引id=5行上加X锁，在主键索引上对应列加X锁
-- id 是非唯一索引 repeatable read 隔离级别
-- 在非唯一索引上查找id=5行，找到则加上X锁和GAP锁，然后对应的聚集索引加上X锁; 没有找到则
加上GAP锁
-- id 不是索引 repeatable read 隔离级别
-- 在聚集索引上扫描，所有行加上X锁和GAP锁

示例2

-- 在 RR 下
-- 不加任何锁
select .. from t;
-- 扫描到任何索引行上加S锁（next-key lock） 在聚集索引上加X锁
select...from t lock in share mode;
-- 扫描到任何索引行上加X锁（next-key lock） 在聚集索引上加X锁
select..from t for update;
-- 扫描到任何索引行上加X锁（next-key lock） 在聚集索引上加X锁
update..where condition
delete from..where condition
-- 如果是间隙插入，先添加 insert intention lock, 后在该行上添加X锁;
-- 如果是递增插入，添加 auto-inc lock 或者 轻量级的互斥锁；
insert into ...

MVCC

多版本并发控制；用来实现一致性的非锁定读；非锁定读是指不需要等待访问的行上X锁的释放；
在 read committed 和 repeatable read下，innodb使用MVCC；然后对于快照数据的定义不同；
在 read committed 隔离级别下，对于快照数据总是读取被锁定行的最新一份快照数据；而在
repeatable read 隔离级别下，对于快照数据总是读取事务开始时的行数据版本；
思考：为什么读取快照数据不需要上锁？
因为没有事务需要对历史的数据进行修改操作；

 

示例

redo

redo 日志用来实现事务的持久性；内存中包含 redo log buffer，磁盘中包含 redo log file；
当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的commit操作完成才完成了事务的提交；
redo log 顺序写，记录的是对每个页的修改（页、页偏移量、以及修改的内容）；在数据库运行时不需要对 redo log 的文件进行读取操作；只有发生宕机的时候，才会拿redo log进行恢复；

undo

undo 日志用来帮助事务回滚以及MVCC的功能；存储在共享表空间中；undo 是逻辑日志，回滚时将数据库逻辑地恢复到原来的样子，根据 undo log 的记录，做之前的逆运算；比如事务中有insert 操作，那么执行 delete 操作；对于 update 操作执行相反的 update 操作；
同时 undo 日志记录行的版本信息，用于处理 MVCC 功能；

主从复制

---

原理图

binlog

产生时机，事务提交后；
重启生成新的binlog；
flush logs 生成新的binlog；

作用

• 使用binlog恢复数据（增量数据）；
• 用来主从复制（replication）；

开启

[mysqld]
log-bin=mysql-bin
# 每提交n次事务，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入
磁盘。
sync_binlog=1
# 开启两段式事务提交
# 1.prepare，先将redolog持久化到磁盘；
# 2.prepare成功，再将事务日志持久化到binlog；
# 3.再在redolog上持久化commit操作；
innodb_support_xa=1

总结

1. binlog 文件会随服务的启动创建一个新文件；
2. 通过 flush logs 可以手动刷新日志，生成一个新的 binlog 文件；
3. 通过 show master status 可以查看 binlog 的状态；
4. 通过 reset master 可以清空 binlog 日志文件；
5. 通过 mysqlbinlog 工具可以查看 binlog 日志的内容；
6. 通过执行 DML 操作，mysql 会自动记录 binlog;

数据恢复

show master status;
show binlog events in 'mysql57-bin.000001';

# 查看binlog
mysqlbinlog mysql-bin.0000xx
# 恢复数据
mysqlbinlog mysql-bin.0000xx | mysql -u 用户名 -p 密码 数据库名
# mysqlbinlog mysql-bin.000002 --start-position 154 --stop-position 755 |
mysql -uroot -p123456
# --start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
# --stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间
# --start-position：从二进制日志中读取指定 position 事件位置作为事件开始
# --stop-position：从二进制日志中读取指定 position 事件位置作为事件截至

读写分离

---

mysql-proxy

项目发展是有序的，从单节点到多个节点，怎么做到无痛升级？应用程序尽量不改动，使用proxy层来实现功能；
mysql-proxy是mysql官方提供的mysql中间件服务，上游可接入若干个mysql-client，后端可连接若干个mysql-server。它使用mysql协议，任何使用mysql-proxy的下游无需修改任何代码，即可迁移至mysql-proxy上。

原理

go-mysql-transfer lua脚本 读从 写主 读要求一致性高 去主数据库读
mysql-proxy向用户提供了6个 hook 点；让用户通过Lua脚本来完成功能，这些 hook 点是以函数的形式提供的，用户可以实现这些函数，在不同事件、不同操作发生时，来定义我们需要的行为

connect_server()

mysql-client与mysql-proxy建立连接时，mysql-proxy会调用该函数。用户可以实现该函
数，来实现负载均衡工作。注意：若有多个mysql-server后端，而用户没有实现这个函数，
那么mysql-proxy默认采用轮询（round-robin）策略。

read_handshake()

mysql-server向mysql-proxy返回握手数据包时，mysql-proxy会调用该函数；用户可以实现
该函数，来实现权限验证工作。

read_auth()

mysql-client向mysql-proxy发送认证数据包（包括用户名、密码以及数据库）时，mysqlproxy会调用该函数；

read_auth_result()

mysql-server向mysql-proxy返回认证回包时，mysql-proxy会调用该函数；

read_query()

mysql-client经由mysql-proxy向mysql-server发送query数据包时，mysql-proxy会调用该
函数；用户可以在这层实现修改请求内容、请求路由、亦或直接返回请求等操作；

read_query_result()

mysql-server经由mysql-proxy向mysql-client发送query回包时，mysql-proxy会调用该函
数；用户可以在这层做合并数据包或者对结果集进行修改；

分表分库

---

水平拆分和垂直拆分；
为了解决由于数据量过大而导致数据库性能降低的问题，采取分库分表从而达到提升数据库性能的目的；
什么时候分表分库？ 当一个表的数据量达到500w条或者表文件大小大于2G；

垂直分表

水平分表

垂直分库

水平分库

垂直拆分

特点

• 每个库（表）的结构都不一样；
• 每个库（表）的至少有一列数据一样；
• 并集是全量数据；

优点

• 业务清晰；
• 维护简单；

缺点

• 若单表数据量大，该表依然查询效率低；
• 关联查询困难；

水平拆分

特点

• 每个库（表）的结构都一样；
• 每个库（表）的数据内容都不一样；
• 并集是全量数据；

优点

• 库（表）的数据量维持在合理范围；一定程度提升性能；提供了均衡的负载能力；

缺点

• 扩容难度大；
• 拆分规则选取较难；
• 关联查询困难；

问题

• 主键避重
• 公共表处理
• 事务一致性 （分布式事务）
• 跨节点关联查询
• 跨节点分页、排序函数；

原则

原则一：能不分就不分，1000 万以内的表，不建议分片，通过合适的索引，读写分离等方式，可以很好的解 决性能问题。
原则二：分片数量尽量少，分片尽量均匀分布在多个 DataHost 上，因为一个查询 SQL 跨分片越多，则总体 性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量。
原则三：分片规则需要慎重选择，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联 性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性 Hash 分片，这几种分片都有利于扩容
原则四：尽量不要在一个事务中的 SQL 跨越多个分片，分布式事务一直是个不好处理的问题
原则五：查询条件尽量优化，尽量避免 Select * 的方式，大量数据结果集下，会消耗大量带宽和CPU 资源， 查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。

实现

• 在应用层实现；
• 使用代理层；

MyCat

---

MyCat是目前最流行的基于Java语言编写的数据库中间件，是一个实现了MySql协议的服务器，其核心功能是分库分表。配合数据库的主从模式还可以实现读写分离。
使用开源框架最重要的准则就是是否活跃；MyCat显然复合这个准则；

官方集群

---

MySQL Replication

如上主从复制

MySQL Fabirc

这是在MySQL Replication的基础上，增加了故障检测与转移，自动数据分片功能。依然是一主多从的架构，虽然只有一个主节点，当主节点宕机，会从从库中选举一个来当主节点；
这个方案用来实现高可用，但是节点故障恢复时间长达30秒或更长；

MySQL Cluster

MySQL Cluster是多主多从结构；最大不足在于只支持 NDB；
优点：

• 高可用性，自动切分数据，数据在不同节点进行备份；
• 方便数据库的水平拓展；
• 多个主节点，节点故障恢复通常小于1秒。

第三方集群

---

MMM

在 MySQL Replication 基础上的优化；该项目为 google 开源项目；
是一个双主多从结构，使用 perl 语言来对 MySQL Replication扩展；主要实现了监控 MySQL 主主复制并做切换转移；
注意：虽然叫双主复制，但是同时刻只允许对一个主进行写入，另外一个主备提供部分读服务；

优点

• 高可用，3 秒内主备切换；

缺点

• 无法完全保证一致性；双主复制有延时，切换后可能不是最新数据；
• 使用 VIP（虚拟IP浮动） 技术；限定部署必须在同一个 IDC 机房；

MHA

在 MySQL Replication 基础上的优化。
M（master）H（high）A（availability）是多主多从架构；提供更多的主节点，需要使用
keepalived 来实现 VIP；
每一个复制组有主节点，主备节点以及从节点构成；

优点

• 可以进行故障的自动检测和转移；
• 在主库异常宕机时能最大程度保持数据一致性；

缺点

• 还需要引入读写分离相应逻辑，可以考虑 mysql-proxy 做到读的负载均衡；

Galera Cluster（MyCat）

由 CoderShip 开发的 MySQL多主结构集群，这些主节点互为其他节点的从节点。
原生的主从复制采用的是异步复制，而 Galera 采用的是多主同步复制，并针对同步复制过程中，会大概率出现的事务冲突和死锁进行优化；Galera的复制同步不是通过binlog实现，而是重写了wsrep api；
同步复制过程中，主库的单个事务更新需要在所有从库上同步更新，所以在主库提交事务时，集群中所有节点的数据保持一致；

优点

• 可对任意节点进行读写操作，某个节点宕机不影响其他节点的读写功能，而且不需要做故障切换；
• 扩展性强，新增节点自动拉取在线节点的数据（新节点加入时，集群会选举一个节点专门为新节点提供数据），数据同步完成后，新节点才会对外提供服务，不需手动备份恢复；

缺点

• 响应会降低，因为实现了数据的强一致性；

为什么需要缓冲层？

---

前提

读多写少，单个主节点能⽀撑项⽬数据量；数据的主要依据是mysql；

mysql

mysql有缓冲层，它的作⽤也是⽤来缓存热点数据，这些数据包括数据⽂件、索引⽂件等； mysql缓冲层是从⾃身出发，跟具体的业务⽆关；这⾥的缓冲策略主要是lru，当然是经过优化的lru；
mysql数据主要存储在磁盘当中，适合⼤量重要数据的存储；磁盘当中的数据⼀般是远⼤于内存当中的数据； mysql是关系型数据库，⽅便 OLTP 进⾏统计分析；⼀般业务场景关系型数据库（mysql）作为主要数据库；

缓冲层

缓存数据库可以选⽤redis， memcached；它们所有数据都存储在内存当中，当然也可以将内存当中的数据持久化到磁盘当中；内存的数据和磁盘的数据是⼀⽐⼀的；

存储⽐较

⼏项重要的数据

1. 内存的访问速度是磁盘访问速度的10万倍（数量级倍率）；内存的访问速度⼤约是100ns，⽽⼀次磁盘访问⼤约是10ms；访问mysql时访问磁盘的次数跟b+树的⾼度相关；
2. ⼀般⼤部分项⽬中，数据库读操作是写操作的10倍左右；

总结
 

1. 由于mysql的缓冲层不由⽤户来控制，也就是不能由⽤户来控制缓存具体数据；
2. 访问磁盘的速度⽐较慢，尽量获取数据从内存中获取；
3. 主要解决读的性能；因为写没必要优化，必须让数据正确的落盘；如果写性能出现问题，那么请使⽤横向扩展集群⽅式来解决；
4. 项⽬中需要存储的数据应该远⼤于内存的容量，同时需要进⾏数据统计分析，所以数据存储获取的依据应该是关系型数据库；
5. 缓存数据库可以存储⽤户⾃定义的热点数据；以下的讨论都是基于热点数据的同步问题；

原理图
为什么有同步的问题？

没有缓冲层之前，我们对数据的读写都是基于mysql；所以不存在同步问题；这句话也不是必然，⽐如读写分离就存在同步问题（数据⼀致性问题）；
引⼊缓冲层后，我们对数据的获取需要分别操作缓存数据库和mysql；那么这个时候数据可能存在⼏个状态？
1. mysql有，缓存⽆
2. mysql⽆，缓存有
3. 都有，但数据不⼀致
4. 都有，数据⼀致
5. 都没有
4和5显然是没问题的，我们现在需要考虑1、 2以及3；
⾸先明确⼀点：我们获取数据的主要依据是mysql，所以mysql数据正确就万事⼤吉，只需要将mysql的数据正确同步到缓存数据库就可以了；同理，缓存有， mysql没有，这⽐较危险，此时我们可以认为该数据为脏数据；所以我们需要在同步策略中避免该情况发⽣；同时可能存在mysql和缓存都有数据，但是数据不⼀致，这种也需要在同步策略中避免；
缓存不可⽤的话，我们整个系统应该要保持正常⼯作；
mysql不可⽤的话，应该停⽌对外提供服务；
另外可以将问题 3 转化为问题 1；

尝试解决同步（⼀致性）

写

主要数据存储在mysql当中，所以先写mysql，如果mysql不可⽤，直接返回； mysql写成功后，再将数据同步给redis就⾏了，如果此时redis不可⽤，应该怎么做？

读

先从redis当中获取数据，如果redis不可⽤，直接去mysql获取；如果redis有，直接返回；如果redis没有，转⽽向mysql请求，如果mysql没有，直接返回；如果MySQL有，则返回并将数据回写到redis当中；

总结

1. 业务层引⼊了两个变化⽅向，尽量减少redis的流程；
2. 业务层控制热数据流程；回写流程控制热数据流程；
3. 热数据不是总是热数据；需要将热数据设置超时时间；

解决数据同步问题

---

⼀致性问题

强⼀致性

同步是否成功的依据来源于mysql是否同步到redis，即使没有同步成功，也没关系；
写流程：先删除缓存，再写mysql，后⾯数据的同步交由go-mysql-transfer；
先删除缓存，为了避免其他服务读取旧的数据；也是告知系统这个数据已经不是最新，建议从mysql获取数据；
强⼀致性只适⽤于单数据中⼼的模型下；
多数据中⼼的模型下，不管先操作redis还操作mysql都会引起分布式异常问题的产⽣，此时可以通过加分布式锁的⽅式解决，但是这得不偿失；可以将多数据中⼼转化为单数据中⼼；或者强⼀致性需求读写都⾛mysql；其他⼀致性需求低的⾛最终⼀致性；

最终⼀致性

读写分离，主库将数据同步到从库，是需要时间，那么在同步期间，主从之间数据有差异；
这⾥有写两种⽅案：
第⼀种：直接写mysql，等待mysql同步数据到redis；
第⼆种：先写redis，设置key的过期时间为200ms（经验值），等待mysql回写redis，覆盖key，设置更⻓的过期时间；

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

数据同步⽅案1

原理图
  

数据同步⽅案2

原理图

代码1

git clone https://gitee.com/mirrors/go-mysql-transfer.git

/*
mysql 配置⽂件 my.cnf
log-bin=mysql-bin # 开启 binlog
binlog-format=ROW # 选择 ROW 模式
binlog redolog binlog 事务提交后产⽣的 不要纠结回滚了怎么办？
server_id=1 # 配置 MySQL replaction 需要定义，不要和 go-mysql-transfer 的
slave_id 重复
*/
CREATE table `t_user`(
    `id` bigint,
    `name` varchar(100),
    `height` INT8,
    `sex` varchar(1),
    `age` INT8,
    `createtime` datetime,
    PRIMARY KEY(`id`)
);

insert into `t_user` values (10001, 'mark', 180, '1', 30, '2020-06-01');
update `t_user` set `age` = 31 where id = 10001;
delete from `t_user` where id = 10001;



-- go-mysql-transfer
--[[
安装步骤：
GO111MODULE=on
git clone https://gitee.com/0k/go-mysql-transfer.git
go env -w GOPROXY=https://goproxy.cn,direct
go build

修改 app.yml
执⾏ go-mysql-transfer
]]

local ops = require("redisOps") --加载redis操作模块

local row = ops.rawRow() --当前数据库的⼀⾏数据,table类型， key为列名称
local action = ops.rawAction() --当前数据库事件,包括： insert、 updare、 delete

if action == "insert" then -- 只监听insert事件
    local id = row["id"] --获取ID列的值
    local name = row["name"] --获取USER_NAME列的值
    local key = name .. ":" .. id
    local sex = row["sex"]
    local height = row["height"] --获取PASSWORD列的值
    local age = row["age"]
    local createtime = row["createtime"] --获取CREATE_TIME列的值
    ops.HSET(key, "id", id) -- 对应Redis的HSET命令
    ops.HSET(key, "name", name) -- 对应Redis的HSET命令
    ops.HSET(key, "sex", sex) -- 对应Redis的HSET命令
    ops.HSET(key, "height", height) -- 对应Redis的HSET命令
    ops.HSET(key, "age", age) -- 对应Redis的HSET命令
    ops.HSET(key, "createtime", createtime) -- 对应Redis的HSET命令
end

代码2

git clone https://gitee.com/josinli/mysql_redis.git

触发器：具备事务 外键具备事务 如果
不建议使⽤，有事务的场景容易出错；虽然保证了真正的强⼀致性；
这个实现每次插⼊修改都需要重新建⽴redis连接，操作完后⼜释放redis连接；

问题是否解决？

没有，我们刚刚思考的⽅向全是正常流程下的⽅式，我们来看异常情况；

缓存穿透

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

解决：

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

缓存击穿

缓存击穿 某些数据redis没有，但是mysql有；此时当⼤量这类数据的并发请求，同样造成mysql过⼤；

原理图

解决：

1. 加锁，请求数据的时候获取锁，如果获取成功，则操作，获取失败，则休眠⼀段时间（200ms）再去获取；获取成功，则释放锁
⾸先读redis，不存在，读mysql，存在，写redis key的锁
整个流程⾛完，才让后⾯的服务器访问
2. 将很热的key，设置不过期

缓存雪崩

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

解决：

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