MySQL 全面知识总结

时间2025-04-23 10:17:55发布yu分类文章浏览19

导读：Mysql存储特点 Mysql存储数据以数据页为最小单位。在同一个数据页中，数据按照主键，连续存储；如果没有主键，则按照Mysql维护的 ROW_ID 来连续存储。...

Mysql存储特点

Mysql存储数据以数据页为最小单位。
在同一个数据页中，数据按照主键，连续存储；如果没有主键，则按照Mysql维护的 ROW_ID 来连续存储。
数据页和数据页之间以双向链表关联。
数据和数据之间以单向链表关联。

SQL 执行流程

查询

SQL 语句执行顺序

(8) SELECT 
(9) DISTINCT<Select_list>
(1) FROM <LEFT_TABLE>
(3) <join type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <grou_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
(11) LIMIT <limit_number>

Group By

左连接和右连接

比如 A 表为左表，B 表为右表

左连接
- left join 是以左表为准的，是以 A 表的记录为基础的。换句话说,左表 (A) 的记录将会全部表示出来,而右表 (B) 只会显示符合搜索条件的记录(例子中为: A.aID = B.bID)。B 表记录不足的地方均为 NULL。
右连接
- 和 left join 相反

drop、delete、truncate的区别

delete
- DELETE 语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。DELETE 操作不会减少表或索引所占用的空间。
- delete 可根据条件删除表中满足条件的数据，如果不指定 where 子句，那么删除表中所有记录。
truncate
- TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。
- 并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
- 当表被 TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，
drop
- drop 语句删除表结构及所有数据，并将表所占用的空间全部释放。
- 不能回滚，不会触发触发器。

即使配置了 binlog_format = row, truncate 和 drop 删除操作在日志中保存还是 statement 格式的，所以 binlog 中只有一句 drop/truncate 语句，没法恢复数据。除非数据库有全量备份。

不同的count用法

首先你要弄清楚count()的语义。count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加。最后返回累计值。

count(*)

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高，但是不支持事务；
而 InnoDB 执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数，虽然结果准确，但会导致性能问题。
show table status 命令虽然返回很快，但是不准确；

count(1)

InnoDB 会遍历整张表，但不取值。server 对于返回的每一行，放一个数字"1"进去，判断不可能为空的，按行累加。

count(主键id)

InnoDB 会遍历整张表，然后把每一行的 id 取出，返回给 server。server 对于返回的每一行，判断不为空的，然后按行累加。

count(字段)

InnoDB 会遍历整张表，然后 server 把不为 null 的字段取出来，逐行累加。

为什么InnoDB不跟MyISAM一样，也把数字存起来呢？

MySQL 数据类型

tinyint：8位，1字节
allint：16位，2字节
mediumint：24位，3字节
int：32位，4字节
bigint：64位，8字节
float：32位，4字节
double：64位，8字节
char：1~255字节
varchar：L+1 字节, 在此 L <= M 和 1 <= M <= 255
text：L+2 字节, 在此 L < 2^16

普通读和快照读

快照读：普通的语句，通过生成 ReadView 来实现，利用 MVCC 机制，不会对记录行加锁。
- 不包括：和
当前读：当前读，读取的是最新版本，并且需要先获取对应记录的锁，如以下这些 SQL 类型：
- DDL：、、

1.MyISAM

2.InnoDB

(1) MyISAM 和 InnoDB 的区别

存储引擎：是表级别的，形容数据表

a. 存储文件的区别

mySIAM: .frm, .MYD, .MYI, 三个文件
innoDB: .frm, .ibd

b. 索引的区别

mySIAM索引底层数据结构是 B+ 树
- mySIAM 的索引和数据保存在不同的文件中（.MYI 和 .MYD）

叶子节点保存的是数据在表中的地址 （非聚集的）

InnoDB索引底层数据结构是 B+ 树
- 索引和数据保存在相同的文件中（.ibd）
- 叶子节点保存的是表的数据 （聚集的：数据都保存在叶子节点）

c. 是否支持行级锁

MyISAM 只支持表级锁。
InnoDB 支持表级锁和行级锁（默认为行级锁）。

d. 是否支持事务

MyISAM 不支持事务
InnoDB 支持事务，提供 commit 和 rollback 功能
- 默认支持 REPEATABLE READ 隔离级别
- 可以用 MVCC 和 Next-Key Lock 解决幻读的问题

e. 是否支持 MVCC

MyISAM 不支持。
InnoDB 支持。

f. 是否支持外键

MyISAM 不支持外键。
InnoDB 支持外键。

g. 是否支持安全恢复

MyISAM 不支持安全恢复。
InnoDB 可以依赖 redo log 来进行恢复，所以，数据库崩溃重启后，会恢复到崩溃前的状态。

3.Memory

索引是帮助 MySQL 高效获取数据的排好序的数据结构。

1.为什么要索引？

索引是帮助 MySQL 高效获取数据的排好序的数据结构。

如果没有索引，查找表就是从第一行数据开始找，会使得数据库和磁盘直接进行大量的 IO 操作。降低效率。

比如：会一条一条取出来作比较。

2.什么是B+树？

B+ 树的非叶子节点，只保存索引，而不保存数据，因此 B+ 树比 B 树更加矮壮。这就意味着，B+ 树检索速度会更快
B+ 树叶子节点是一个有序双向链表，遍历查询更方便。
B+ 树的一个节点占有一页，一页大概是16k。每次查询，把一页加载到内存中去查询（比如二分查询）。如下图，就是一个节点（一页）：

h=3 的 B+ 树差不多能放2千万的数据。

B+ 树查询过程

RAM是内存

为什么不用二叉搜索树？

如果数据是递增（或递减）的，搜索二叉树就成了一个链表。就和全表查询一样了。

为什么不用红黑树？

红黑树就是二叉平衡树。左右子树高度差不超过1。

红黑树也是一种二叉树，所以数据量大的时候，树的高度较高。

b 树比红黑树强的地方

红黑树是一种"二叉搜索树"，每个node节点只能保存一对key，value
B 或 B+ 树是一种”多路搜索树“，每个node节点可以保存多个数据
因此，相比较而言，B 或者 B+ 树比红黑树高度更低，高度更低就意味着检索速度更快。

为什么不用 Hash 表

只能满足 =， IN 的查询，不支持范围查询

比如查询 col > 10
hash 有冲突问题

为什么不用B树？

B 树

B+ 树相比较 B 树，有两个优势
- 更矮壮：B+ 树的非叶子节点，只保存索引，而不保存数据，因此 B+ 树比 B 树更加矮壮。这就意味着，B+ 树检索速度会更快
- 叶子节点有序且是双向链表：B+ 树叶子节点是一个有序双向链表，遍历查询更方便，尤其支持范围查找。而B树就不行。

3.为什么建议 innoDB 表必须建主键，并且是auto_increment

如果不使用自增的主键，B+ 树会选一列唯一不重复的列来建立数据，如果没有，会维护一个row_id，但是这样会让 MySQL 多维护一列数据，而这个数据本身可以避免出现。
主键要自增：B+树叶子节点是有序排列的，如果主键索引不是自增的，新插入的数据可能会插到中间节点之间，这样可能会导致树不平衡而花费时间重新平衡。

什么是回表

回表会基于非主键索引的查询后回到主键索引树搜索的过程。即当通过非主键索引找到索引列值以外的字段时，就会回表。

比如：

create table T (
    ID int primary key,
    k int NOT NULL DEFAULT 0, 
    s varchar(16) NOT NULL DEFAULT '',
    index k(k)) engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

此时执行：

先在 k 索引树找到 k = 3 的数据记录(一个数据页,保存若干id数据)，找到 id = 300
然后去主键索引树，找到 id = 300 的数据记录
这个过程就是在回表

原因：

因为非主键索引建立的B+树叶子节点的数据表保存的是索引列值和主键。（聚簇索引保存的是主键和其他所有列值）
所以，如果要查询索引值以外的值时，先要通过非主键索引找到相应的主键值，再通过主键值去聚簇索引B+树找到相应的数据行，再读取出要查询的数据。
比如，MySQL采用非主键索引name来作为索引，那么底层B+树存放的是name列值和主键id。如果此时用一下sql查询
```
select * from student where name = "James"
```
那么MySQL只能进行查询到name列值和相应的id，而其他的列值就必须通过这个id，再去聚簇索引保存的B+树，找到相应的数据并读取。这就是回表。

怎样避免回表

通过覆盖索引的方式。

就是建立联合索引，使得查询时候，想要的结果已经在叶子节点上了，而不需要进行回表操作。
比如如果要查 orderId 和 orderName，那就以这两个字段作为联合索引。这样 orderId 和 orderName 就作为数据保存在B+树的叶子节点上了，就不需要回表操作了。

覆盖索引

将被查询的字段，建立到联合索引里去

场景1：全表count查询优化

原表为： user(id, name, sex)；

直接： select count(name) from user; 不能利用索引覆盖。

添加索引： alter table user add key(name); 就能够利用索引覆盖提效。

什么是联合索引？

最左前缀原则/最左匹配原则

总结：对于(a, b, c) 这样的联合索引， (a, c), (a, b), (a) 这些查询方式，根据最左匹配原则，都会从(a, b, c) 的联合索引树去查询数据。因为叶子节点是先根据 a 来进行排序的，然后根据 b，再根据c。而遇到范围查询，如 between，in，>，<，则会停止匹配。比如，where a = 1 and b > 2 and c = 3，在联合索引树中，先找到 a = 1，b > 2 的所有叶子节点，根据 id 去主键索引树查询完整的记录。

B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

(I) 比如上图索引为

比如
- 查询条件为 where name = '张三'，则找到 ("张三", 10), id=4 的记录，然后往后遍历所有满足条件的值
- 查询条件为 where name like '张%'，则找到 ("张六", 30), id=3 的记录，然后往后遍历所有满足条件的值
所以，只要满足最左前缀，就可以利用索引来加速查找。

因此，建立索引时，要考虑索引顺序。比如，居民身份信息索引就只需建立和就行了，不能再单独建立一个

(II) 在最左匹配原则中，有如下说明：

最左前缀匹配原则，非常重要的原则，mysql会根据联合索引一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配
- 比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
- 因为B+树的叶子节点中，数据页和数据页是按照联合索引第一个值来排序的，然后才是第二个，然后才是第三个。
= 和 in 可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成查询效率最高的形式，也就是匹配联合索引的形式。
匹配最左边的列
- 比如联合索引是(a1, a2, a3)
  - 那么(a1)， (a1, a2)，(a1, a3) 都会触启用联合索引(a1, a2, a3)的查询，到联合索引(a1, a2, a3)树中去查询。（a2, a1）等也行
  - 而（a2），（a2，a3）等都不会触发联合索引(a1, a2, a3)的查询。

.匹配列前缀

如果id是字符型，那么前缀匹配用的是索引，中坠和后缀用的是全表扫描。

select * from staffs where id like 'A%';//前缀都是排好序的，使用的都是联合索引
select * from staffs where id like '%A%';//全表查询
select * from staffs where id like '%A';//全表查询

遇到范围
- 遇到范围查询 (>、<、between、like) 就会停止匹配。
- 原因：
  - 联合索引底层B+树是通过最左边的列来构建的。
    
    可以看到，左边的列是有序的，为1，1，2，2，3，3。左边列相同，才会根据后一个列值排序。所以，遇到范围查询是没办法按序去查询的，只能退化到线性查询。

by the way，联合索引最多只能包含16列

索引下推

当有联合索引 (name, age) 时，如果我们执行以下语句：

select * from user_info where name="王%" and age=20 and ismale=1;

在mysql 5.6之前，该查询首先会通过name去联合索引 (name, age) 树里查询，找到多条符合name="王%"的数据，然后根据 id 触发回表操作，去主键索引里找到符合条件的数据，整个过程需要回表多次。

mysql 5.6 之后，有了索引下推。在索引内部就会先判断 age 是否等于 20，这样在 (name, age) 联合索引树中只会找到一个数据，然后拿着 id 回表找到数据。整个过程只需要回表一次。

聚簇/非聚簇索引

根据主键创建的表就是聚簇索引。InnoDB中，表数据文件本身就是按 B+Tree 组织的一个索引结构，聚簇索引就是按照每张表的主键构造一颗 B+ 树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分；

特点：

使用主键值的大小进行记录和页的排序。
- 页内的记录按照主键大小排成单向链表
- 各个数据页之间按照主键大小顺序排成双向链表
MySQL一个表只有一个聚簇索引。
如果没有定义主键。
- InnoDB 会选择一个非空的唯一索引来代替。
- 如果没有这样的索引，则 InnoDB 会隐式的定义一个主键来作为聚簇索引。

缺点：

插入速度严重依赖于插入顺序。所以最好自增ID为主键，否则插入会带来B+树的分裂。
更新主键代价很高，所以最好主键为不可更新。

MySQL 怎么创建索引？

三种方式

CREATE TABLE tableName(  
  id INT NOT NULL,   
  columnName  columnType,
  INDEX [indexName] (columnName(length))  
);

索引原则

适合创建索引

频繁被查询的数据。
不为 NULL 的数据。
频繁作为 WHERE 条件的字段。
频繁需要 ORDER BY 的字段（因为索引叶子节点已经有序）。
频繁用于表连接的字段。

尚硅谷版本：
字段的数值有唯一性的限制。业务上具有唯一特性的字段，即使是组合字段，页必须建成唯一索引。

比如学生的学号，具有唯一性。而姓名有可能出现同名的情况，就不适合。

频繁被 WHERE 查询条件的字段。

针对GROUP BY，ORDER BY的2个字段，需要建立联合索引且GROUP BY字段在前, ORDER BY字段在后。

GROUP BY：建立索引后，相同类型的数据就排在一起了。

ORDER BY：建立索引后，数据就有序了。

UPDATE、DELETE 的 WHERE 条件列

如果更新的字段不是索引字段，提升效率会更明显，因为不需要因为修改了索引而维护索引。

DISTINCT 字段需要创建索引。

多表 JOIN 连接操作时，创建索引注意事项

连接表的数量不要超过3张

对 WHERE 条件创建索引

对用于连接的字段创建索引
使用字符串前缀对varchar创建索引
#对于varchar上创建索引，要指定索引长度，即查询前一部分，但是导致的问题就是排序不准确
create table shop(addrass varchar(120) not null);
#指定字符串索引前缀部门
alter table shop add index(addrass(12));
#查看区分度，越高越好
select count(distinct addrass) / count(*) from shop;
索引尽量不要超过6个。索引太多会占用磁盘空间，CUD需要维护索引。优化器也要进行多次选择。

不适合创建索引

频繁更新的字段，因为维护索引的代价很高。
不建议对无序字段创建索引。（主键是 id 的话，尽量自增）。
不要创建冗余索引。比如已经创建了 (name, age) 的索引，那么不需要再创建 (name) 索引了，因为根据最左匹配原则，(name) 索引也可以走 (name, age) 索引。

1. 什么是事务？

事务是一组操作，这一组操作要么同时成功，要么同时失败。

2. 事务的特性

ACID

Atomicity：原子性
Consistency：一致性
Isolation：隔离性
Duration：持久性

原子性

事务的操作要么成功，要么失败。

MYSQL InnoDB 的底层是通过 undo log 来实现的。undo log 记载着变化前的数据。所以，一旦事务操作失败，数据库就会根据 undo log 的值回滚成原来的数据。

一致性

一致性是事务的目的。我们对数据库操作，就是要保证数据的一致性。一旦事务操作失败，就应该回滚到原先的数据。

隔离性

事务与事务之间是隔离的，互不影响的。如果多个事务同时操作一个数据，可能会出现脏读、不可重复读和幻读的问题。

数据库一共有四种隔离级别。
- READ UNCOMMITED
- READ COMMITED
- REPEATABLE READ
- SERIALIZABLE
不同隔离级别事务之间的隔离性是不一样的，级别越高，隔离性越好，但是性能也会越低
事务的隔离性是 MySQL 的各种锁来实现的

持久性

事务一旦 commit，那么对数据库的改变应该是永久的。就是说，数据应该被持久化在硬盘上。

持久性是通过 redo log 来实现的。数据库在对数据进行修改的时，先是查询找到相应要修改的数据页，然后把数据页写入到内存中，进行修改。
为了防止内存修改完了，mysql 挂掉，所以内存修改完 mysql 会把修改写入 redo log，记载本次在内存中对数据的修改。如果 MySQL 挂掉，我们也可以通过这个 redo log 来恢复数据。

3. 事务的使用

START TRANSACTION;
...
SQL
...
COMMIT;

从锁的粒度来分，锁分为表锁和行锁。表锁锁的是整张表，或者说，锁的是索引树。行锁锁的是索引。行锁又分为读锁（共享锁、S 锁）、写锁（排他锁、X 锁）。读锁是共享的，它允许多个事务同时读取一个资源。写锁是排他的，写锁会阻塞其他的写锁和读锁。

1.全局锁

给整个数据库加锁
MySQL 提供了加全局读锁的方法：

这样，其他线程的 DDL 和 DML 语句都会被阻塞
- 使用场景：做全库逻辑备份（把每个表都 select 出来存成文本）

2.表级锁

有哪些表级锁？

会对整张表进行加锁，并发能力差（MyISAM 只支持表级锁）

(1) 表锁

使用：
- 比如：lock tables t1 read, t2 write
  
  其他线程写 t1 和读写 t2 都会被阻塞。

处理非索引字段时，要进行全表扫描，使用的就是表锁。

(2) 元数据锁 MDL

MDL不需要显式使用

当对一个表做增删改查的时候，会自动加上MDL读锁
- 读锁不互斥，可以多线程同时对一个表进行增删改查
当对一个表的结构进行变动时，会自动加上MDL写锁
- 读锁和写锁、写锁和写锁是互斥的，所以两个线程如果要同时给表加字段，第二个线程必须等第一个线程执行完才可以继续加。
事务中的 MDL，会在语句执行开始时申请，但是语句执行结束时不会马上释放，而是在事务提交后释放。

元数据锁的问题：

解决：

在做 DDL 变更时，如果有长事务在执行，要考虑暂停 DDL，或者 kill 掉长事务。

表锁锁的是什么？

表锁的是整张表

3.行锁

(1) 行锁是什么？

InnoDB 支持。针对数据库行记录的锁。（针对索引字段加的锁，因为非索引字段要走全表扫描，需要对所有记录加锁）

(2) 什么是两阶段锁？

a. 两阶段锁

两阶段锁——将事务的获取锁和释放锁分为增长和缩减两个阶段

增长阶段：事务可以获得锁，但不能释放锁
缩减阶段：事务只可以释放锁，不可以获得锁

b. 严格两阶段锁

锁的释放只能发生在事务 commit 或者 rollback 的时候。

c. 好处

使得事务的并发调度可串行化。（事务并发调度的结果和事务串行调度结果保持相同。）

如果事务没有 commit 或者 rollback 就释放锁，那么其他事务可能在 commit 或 rollback 之前就更新数据，导致错误。
- READ UNCOMMITTED: 允许事务读取更新了但未提交的数据。脏读、不可重复读、幻读的问题均存在。

在 InnoDB 中，行锁是在需要的时候才加上的，但是只会在事务提交以后才会释放。

如果事务中需要锁多行，那要把最可能造成锁冲突和最可能影响并发度的锁放后面。

比如：一个电影票在线交易业务，顾客A要在影院B购买电影票.

(3) 行锁锁的是什么？

行锁锁的是索引。

(4) 有哪几类行锁？

记录锁：单个行记录的锁
间隙锁：锁住一个范围，不包含记录本身
临键锁：Next-key lock，锁住一个范围 + 记录本身（用于解决 MySQL REPEATABLE READ 隔离级别的幻读问题）

(5) 怎么减少行锁对性能的影响？

4.死锁

发生死锁后的策略：

超时等待：设置超时时间，超时后自动退出
- 缺点：超时时间不好评估，太长会影响线程工作效率，太短可能会在简单的锁等待时，误判锁是死锁退出。
主动死锁检测：判断是否有死锁链产生。从事务A到申请的资源持有者事务B，再一路判断下去，如果最终形成了一个环，说明有死锁。
- 有死锁：选择死锁环中一个最小代价化的事务回滚，打破死锁条件。
  - 但是可能某个事务一直被回滚，造成饥饿现象。
- 缺点：如果有大量并发线程在操作，那么进行死锁检测会消耗大量 CPU 资源。
  
  怎么解决由这种热点行更新导致的性能问题呢？
产生死锁的四个必要条件：
1. 不可剥夺条件：线程占有的资源不会被其他线程抢占。
2. 请求和等待条件：线程占有一部分资源后，又去申请其他的资源。而申请的资源被其他的线程占有，因此等待其他线程释放资源。
3. 互斥条件：线程占有的资源不可被其他的线程访问，其他线程想要获得该资源，必须等待线程释放该资源。
4. 循环等待条件：产生死锁循环链。

5.共享锁/读锁 Slock

读锁可以被多个事务共享。多个事务可以同时读取一个资源，但是不允许其他事务修改这个资源。
针对行锁
加锁：
释放锁：、

6.排他锁/写锁 Xlock

事务在进行写操作时，不允许其他事务进行读或者修改
加锁：
- DML 语句默认加锁
释放锁：、

7.意向锁

意向锁的含义是如果对一个结点加意向锁，则说明该结点的下层结点正在被加锁。
加锁时可以通过节点意向锁来判断，而不用逐行判断是否加了行锁。

总结：

binlog，归档日志，为了数据库的数据一致性。用于数据备份和主从数据同步。（事务开始 —> 写 binlog cache —> 事务结束 —> binlog cache 到磁盘）

redo log，重做日志，为了数据库崩溃后的数据恢复。（事务开始 —> 写 redo log buffer —> redo log buffer 到磁盘 —> 事务结束）

undo log，回滚日志，用于事务回滚，保证事务的原子性。

1.两阶段提交

两阶段提交是针对 redo log 的，就是 redo log 分为了 prepare 和 commit 两个阶段。
- redo log 在事务进行时，持续写入磁盘日志，而 binlog 只在事务结束后写入磁盘日志。
- 如果事务进行中，主 MySQL 崩溃，那么 redo log 的数据和 binlog 不一致。主库根据 redo log 恢复的数据和从库根据 binlog 同步的数据就不一致了。
- 为了解决这个问题，redo log 采用两阶段提交：
  - 事务 commit 之前，redo log 处于 prepare 阶段。这时候，如果主库崩溃，MySQL 恢复过来后，判断 redo log 是 prepare 阶段，则进行事务回滚。
  - 事务 commit 之后，binlog 记录到磁盘以后，redo log 再进行 commit 阶段。

2.binlog（归档日志）

Binary log，二进制逻辑日志。binlog 是二进制逻辑日志，server 层持有，所有存储引擎都能用，它采用追加写的形式，记录语句的逻辑操作，主要用于数据备份和主从中的主从同步。

binlog 是 server 层定义的，所有引擎都可以使用。
binlog 是一种逻辑日志，记录的是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”。
binlog会记录所有的逻辑操作，只要发生了数据的变更，就会记录 binlog；并且是采用"追加写"的形式。
binlog 用于
- 备份数据。
- 在主从中，从库利用主库上的 binlog 进行重播，实现主从同步。

(1) 三种记录格式

STATEMENT

记录的内容是语句原文，每一条修改语句都会被记录到 binlog 中。

比如执行一条，记录的内容如下：

DML，DDL 语句都会明文显示
不支持 RU，RC 的隔离级别
可能会导致主从数据不一致
比如：
- 主库有 id=1 和 id=10 两行数据
- 从库有 id=1,2,3,10 这四行数据
- 主库执行 delete from t1 where id < 10 命令，从库删除过多数据；

ROW

基于行的：记录的内容不再是简单的语句了，还包含操作的具体数据：

DDL语句明文显示，DML语句加密显示；
导致 binlog 文件较大

MIXED

混合模式：会判断这条语句是否可能引起数据不一致，如果是，就用格式，否则就用格式。

(2) 写入机制

整体流程：事务开启的时候，将 binlog 写入 binlog cache，然后在事务结束的时候，把 binlog 从 binlog cache 里写入到磁盘中。

具体：

系统会为每个线程，分配一个块空间 binlog cache
事务开启后，binlog 会被写入到 binlog cache中。
- 可以通过 binlog_cache_size 来指定 binlog cache 的大小，当超过 binlog cache 大小时，会暂存在磁盘中。
事务结束后，binlog 会被写入到文件系统的 page cache 里。
最后，再从 page cache 持久化到磁盘上。
- 可以设置 fsync 的值，来决定什么时候持久化到磁盘上。1 是每次事务提交都持久化到磁盘，N 是累积 N 个事务后，再提交到磁盘。

3.redo log (重做日志)

redo log，是 InnoDB 引擎独有的物理日志，主要用于记录数据页上的修改，它由四个文件组成，从头开始记录，当写到第四个文件末尾，则回到第一个文件开头继续写，redo log 主要用于 MySQL 崩溃后恢复。

InnoDB 引擎独有的物理日志。
redo log 记录的是在某个数据页上的修改。
redo log 是个文件组，一共有四个文件，会从头开始写，写到第四个文件底，再回到第一个文件从头开始写。
用于 MySQL 崩溃后恢复。

(1) 什么时候写

MySQL 以数据页为单位（16kb），每次查询时，将一个数据页从磁盘加载到缓冲池（Buffer Pool），后续查找都会先从缓冲池里找，找不到再去磁盘加载。

更新时，先去缓冲池（Buffer Pool）尝试更新，如果找到要更新的数据，则直接更新，并记录 redo log。
否则，从磁盘中加载对应的数据页到缓冲池，然后进行更新，并且记录 redo log。

(2) 写入机制

redo log 在事务开始后，会往 redo log buffer 里写日志；但和 binlog 不一样的是，他有一个后台线程，每一秒都将 redo log buffer 里的数据持久化到磁盘中。
- 而 binlog 是要在事务提交以后，才会把 binlog 从 binlog cache 中 fsync 到磁盘上。

(3) 日志文件组

redo log 一共有四个文件，每个文件 1GB。
- 日志文件组维护一个write position，和一个 checkpoint。每次加载日志文件组恢复数据时，会清空加载过的记录，并把后移更新。
  - write position 是当前写的位置。
  - check point 是当前擦除的位置。

4.undo log (回滚日志)

用于 MVCC。事务的修改会将原纪录记录到 undo log 中，然后再对数据进行更新。一旦事务需要进行回滚操作，就从 undo log 中回滚。
回滚日志会先于数据被持久化在磁盘上。

5.Undo log 和 Redo log 有什么区别？

(1) undo log（回滚日志）

undo log 是用于事务回滚和 MVCC 多版本控制。是逻辑日志。
undo log 记录的是上一个版本的数据。

(2) redo log（重做日志）

InnoDB 持有的日志，是物理日志。
MySQL每次更新一条数据，innoDB 引擎会把更新操作写到 redo log 里，并更新内存，当空闲的时候，再把 redo log 的操作到磁盘上更新。redo log 里记录了在数据页上做了什么修改。
有了 redo log，就算 MySQL 异常重启，之前的记录也不会丢失。
- innoDB 的 redo log 文件一共有四个，占4GB。
- checkpoint 是要擦除的位置，擦除记录前把记录更新到数据文件中。
- write pos 是当前写的位置，一边写一边后移。

6.binlog 和 Redo log 有什么区别？

binlog 是 server 层定义的；

redo log 是 InnoDB 层定义的；
binlog 先于 redo log 被记录（两阶段提交）
binlog 是逻辑日志；

redo log 是物理日志；
binlog 是追加写；

redo log 是循环写，日志大小固定；

1.并发问题

脏写：事务A修改了事务B未提交的数据。
脏读：事务A读取了事务B更新，但未提交的数据。之后事务B对数据库进行了回滚，那么事务A读到的就是无效的数据。
不可重复读：事务A读取的数据，该数据之后被事务B修改并提交。事务A再次读取这个数据时，发生这个数据和之前读到的不一样。
幻读：事务 A 读取一张表的一些数据，事务 B 在这个表中插入了几行新的数据并提交。当事务A再次读取的时候，发现多读了一些数据。

2.四种隔离级别

READ UNCOMMITTED: 允许事务读取更新了但未提交的数据。脏读、不可重复读、幻读的问题均存在。
READ COMMITTED: 允许事务读取已经被其他事务提交了的数据。可以避免脏读，但是不可重复读、幻读问题不可避免。
REPETABLE READ: 事务读取一个字段时，不允许其他事务对该字段进行修改。可以避免脏读，不可重复读，但幻读问题不可避免。
SERILIZATION: 事务读取一个表时，不允许其他事务对这个表进行操作。可以避免脏读，不可重复读，幻读问题，但是性能低。

(1) READ UNCOMMITTED原理

读不加锁，写加锁。会带来脏读问题。脏读问题不可接受。
- InnoDB 引擎会给更新操作默认加写锁，但如果读不加锁，就会导致脏读的问题。
但是如果给读加锁，那么数据库在进行更新时，就不可读了，会带来性能上的问题
解决办法：MVCC（多版本并发控制 Multi-Version Concurrency Control）

(2) MySQL 怎么实现的可重复读

锁 + MVCC 实现事务级数据快照，事务只能读取当前事务版本的数据。其他事务修改的数据版本更高，当前事务无法读到。

怎么避免幻读呢？

当前读：比如：就是一条当前读语句

快照读：执行 select 的时候，生成一个快照。所以看不到对其他事务的操作不感知。

当前读的时候，可以加 Next-Key Lock 来避免幻读。next-key lock 是前开后闭区间，而间隙锁是前开后开区间。
- Next-Key Lock 是行锁和间隙锁的结合。
  - 行锁：可以锁住已经存在的行
  - 间隙锁：避免插入新行。间隙锁就是给不存在的行加锁。
```
比如：现在数据库里有数据 1，2，3，...，101。
现在使用 SELECT * FROM table WHERE id > 100;
那么，间隙锁就会给 id > 101 的数据（实际上还没存在）加上锁，使得新增大于 101 的数据。
```
使用快照读
加表锁

(3) SERILIZATION

间隙锁实现的

3.悲观并发控制

加锁。—— 数据库的各种锁

4.乐观并发控制

乐观锁。

(1) 基于时间戳的协议

每个事务都有全局唯一的读时间戳和写时间戳。
读和写操作按照时间戳串行执行。
小于当前时间戳的事务进行回滚，并且重新分配时间戳。

postgresql 就使用了该协议

(2) 基于验证的协议

乐观并发控制其实本质上就是基于验证的协议
将事务执行分为三个阶段：
1. 读阶段：执行事务所有的读操作和写操作。
  - 写操作的值存入临时变量中，而不是真的更新数据库。
2. 验证阶段：验证更新是否合法
  - 判断是否有其他事务在读阶段更新了数据
3. 写阶段：
  - 如果更新合法（读阶段要更新的数据没有被其他事务改动），则将临时变量中的数据写入数据库。
  - 否则事务被abort

5.多版本并发控制 MVCC

MVCC 如何实现各种隔离级别

MVCC 会生成数据快照（snapshot）

READ COMMIT 可以解决脏读的问题，MVCC 通过在数据记录后面加上版本号来实现 READ COMMIT。事务 A 读取记录时，会生成版本号，事务 B 更新数据，会加写锁，然后更新版本号。事务 A 再次读会读最新版本号的数据。
REPEATABLE READ 隔离级别是事务级别的数据快照，事务每次只能读当前事务版本的数据。可以解决不可重复读的问题。
MVCC 可以使用Next-Key Lock （行锁 + 间隙锁）来解决幻读的问题。

SERILIZABALE READ 是只允许事务串行执行，而不允许并行执行。这样是最安全的，但是也是效率最低的。

前提：大多事务是只读的，而少数事务是写操作。

每一个写操作都会创建一个新版本的数据
读操作从多个版本的数据中选一个最合适的返回。

当事务提交以后，该类型的 undo log 占用的 undo log segment 就会被系统回收（要么被重用，要么被释放）

MVCC 的原理

① Read View + ② 隐藏字段 + ③ undo log 实现

(1) undo log

undo log 记录了数据修改前的数据，方便我们进行回滚操作（保证事务的原子性）。有了 undo log，我们就可以查询到不同版本的数据。

(2) 隐藏字段

每一行数据有两个隐藏字段：
- trx_id：最后插入或更新该行的事务 id （就是最新的事务 id）
  - 删除也视为，但会在记录头的 deleted_flag 标记为删除。
- roll_ptr：在 undo log 中的指针。
- row_id：如果没有设置主键，innoDB 会用该列来生成聚簇索引。

(3) Read View

事务读的时候，用 Read view 来做可见性判断。

多个事务对同一行记录进行更新会产生多个历史快照，这些历史快照保存在 Undo log 中。如果一个事务要查询这个行记录，需要读取哪个版本的行记录呢？这就需要 Read View 来做可见性判断。
read view 是在 select 时，MVCC 生成的，用于记录并维护系统当前活跃（启动了，还未提交）事务的 ID，它有几个重要的字段：
1. creator_trx_id：创建该 READ VIEW 的事务 id。
  - 只有 INSERT、DELETE、UPDATE 这些会生成。
2. trx_ids：创建 Read View 时候活跃的（尚未提交）的事务。
3. up_limit_id：活跃事务列表中最小的事务 ID（活跃事务列表 ids，即当前数据库中正在执行且尚未提交的事务，这些事务不应该被当前正在读的事务看到）。
4. low_limit_id：下一次要生成的事务ID，即系统中的最大事务 id。

Read View 规则：

如果 trd_id == Read View 的 creator_trx_id，那么就代表当前版本的行就是当前事务最后修改并提交的，可以直接读。
如果 trd_id < Read View 的 up_limit_id，即当前活跃事务中最小的事务 id 都大于行最大的 id，当前事务可以直接读。
如果 trd_id >= Read View 的 low_limit_id，即当前版本的行的事务 id 大于等于 Read View 中活跃事务下一次要生成的事务 id，那么当前版本的行不可以被当前事务访问。
如果 trd_id 在 Read View 的 up_limit_id 和 low_limit_id 之间，那需要判断 trx_id 是否在活跃列表 trx_ids 中，
- 如果在，说明创建 Read View 的时候，该生成该版本数据的事务还是活跃的，不能访问。
- 如果不在，说明创建 Read View 的时候，该生成该版本数据的事务已经提交了，可以访问。

流程

首先获得事务 id。
获取 Read View。
查询数据，与 Read View 进行比较。
如果不符合 Read View 规则，则从 undo log 获取历史数据。
最后返回符合规则的数据。

(1) MySQL 和 MVCC

总结：

insert: 创建版本号 ——> 当前系统版本号

update: 找到比事务当前版本号小的最大版本号的数据行，创建一行新的数据行，并且更新数据，然后把创建版本号写为当前系统版本号

delete: 把删除版本号置为当前系统版本号

select: 选出数据行创建版本号小于等于事务创建版本号的，并且删除版本号未定义或者删除版本号大于事务删除版本号的。

InnoDB 的 MVCC

InnoDB 的 MVCC，主要依赖于隐藏字段、Read View、undo log

隐藏字段
- db_trx_id：最后插入或更新该行的事务
  - 删除也视为，但会在记录头的 deleted_flag 标记为删除。
- db_roll_ptr：回滚指针
Read view：多个事务对同一行记录进行更新会产生多个历史快照，这些历史快照保存在 Undo log 中。如果一个事务要查询这个行记录，需要读取哪个版本的行记录呢？这就需要 Read View 来做可见性判断。
undo log：用于回滚
- 不同事务或者相同事务的对同一记录行的修改，会使该记录行的成为一条链表，链首就是最新的记录，链尾就是最早的旧记录。

具体实现

插入：InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
更新：获取最大版本号的数据，然后计算该数据更新后的结果，并创建一个当前系统版本号的数据。
1. 用写锁锁定行
2. 记录 redo log
3. 把改行修改前的数据到 undo log
4. 修改当前行的值，填写事务编号，使回滚指针指向 undo log 中的修改前的行。
删除：InnoDB 将数据行的删除标志设置为当前系统版本号

读：满足两个条件：
1. 数据行的 create_version <= 事务的 create_version
2. 数据行的 delete_version 未定义，或者 > 事务的 delete_version

MySQL 会定期清除版本最低的数据。

(2) PostgreSQL 与 MVCC

多版本时间戳排序协议。

PostgreSQL 中都是使用乐观并发控制的
PG 的读请求，数据库直接返回最新的消息。

第一范式

列都是不可再分的。
- 右表满足第二范式

第二范式

有主键，非主键列依赖主键。不存在非主键列对主键的部分依赖。
- 一个表描述一件事情
例子1：

例子2：

id, stu_name, course_id, course_name 拆分学生表和课程表

第三范式

非主键列之间不存在传递依赖。

比如：Student(id, 姓名, 年龄, 所在学院, 学院地点, 学院电话)
- 因为存在如下决定关系：
  
  (学号) → (姓名, 年龄, 所在学院, 学院地点, 学院电话)，这个数据库是符合2NF的，
- 但是不符合3NF，因为存在如下决定关系：
  
  (学号) → (所在学院) → (学院地点, 学院电话)

1.分库

a.垂直分库

b.水平分库

2.分表

a.垂直分表

b.水平分表

参考：MySQL高性能优化规范建议 | JavaGuide【对线面试官】MySQL调优 (qq.com)

数据库表存在一定数据量，就需要有对应的索引
- 给经常 select 的列添加索引。
发现慢查询时，检查是否走对索引，是否能用更好的索引进行优化查询速度，查看使用索引的姿势有没有问题
- explain 看是否走索引。
- 查询时，使用、、、或者类似或导致索引失效。
- 使用覆盖索引避免回表操作。
- 尽量指定 select 的列，避免 select *。
- 考虑建立联合索引，把区分度高的列放左边，匹配最左匹配原则。
当索引解决不了慢查询时，一般由于业务表的数据量太大导致，利用空间换时间的思想（NOSQL、聚合、冗余...)
- insert、delete 要拆分。
- 用子查询来优化，比如，从 offset 获取到 offset + n 的数据，提高查询效率。
- 数据量太大，可以考虑能否把旧数据备份后删除。
- 如果对于数据一致性要求没有那么高的话，可以用缓存（如 redis）来优化查询。
- 如果涉及到字符串查询导致查询速度慢的话，可以用 ElastaticSearch 搜索引擎来存储数据，查询走 ES。
- 可以根据查询条件的维度，考虑做聚合表，比如用户下单后的订单明细，可以按天聚合成一条数据，查询就快了。
当读写性能均遇到瓶颈时，先考虑能否升级数据库架构即可解决问题，若不能则需要考虑分库分表
- 升级主从架构，实现读写分离。
- 分库分表。比如按照 userId 进行分表。
  - 分库分表的 id 可以通过雪花算法来实现。
  - 分库分表虽然能解决掉读写瓶颈，但同时会带来各种问题，需要提前调研解决方案和踩坑。

1.数据库服务器内核优化

2.my.cnf 配置，搭配压力测试进行调试

3.sql语句调优

使用缓存优化查询（不推荐）
- 进行多次相同查询，结果会放入缓存中
- 后续再进行同样的查询，就会直接从缓存中提取，不会到表中提取。
- 但是，
  - 缓存失效会很频繁，只要表更新了，这个表所有的查询缓存就会失效，经常好不容易把结果保存了，结果还没用就失效了。
  - sql 语句要完全一样才能命中缓存。
  第一条语句会因为函数每次时间不一样导致缓存失效。
用 explain 检测 SQL 查询，看看我们的 sql 是否走了索引
给常 select 的列建立索引
- 比如，给 where 后面的字段添加索引
使用覆盖索引避免回表操作
- 比如索引保存的是居民身份证，但是现在有个高频请求：需要通过居民身份证去找到居民姓名，可以通过简历联合索引 (居民身份证，姓名) 来提高搜索效率。
指定 select 中查询的列，尽量不要 select *
考虑建立联合索引，把区分度高的列放在左边，充分考虑最左匹配原则。
limit 1（明确只有一行数据时）
选择正确的数据库引擎（mySIAM, innoDB）
大量的 delete、insert 进行拆分，分几次进行。大查询也可以分几次进行，每次返回一部分数据。
数据表特别大的时候，把不用的旧数据备份后删除。
数据类型尽量使用小的
固定字段长度
明确的固定的字段上使用 enum （性别、国家、市） varchar
id 主键每张表都要建立集群分区
rand() 计算是在 cpu 上进行的
连接两表的时候， join 尽量保持两个字段的类型一致
垂直分割

四个层面

查询语句优化（逻辑层面）
索引优化（物理层面）
数据库参数设置优化——调整my.cnf
分库分表

查询语句优化

切分查询
- 将大查询切分成小查询。每个小查询功能一样，只完成一小部分，每次返回一小部分的查询结果
- 比如：删除大量的数据改成一次删除xxx行数据。
分解关联查询

count

统计某个列值的数量（不包括NULL）
- COUNT(name)
- COUNT(1)：统计主键列值的数量，而主键列肯定非空。因此COUNT(1)和COUNT(*)差不多
统计结果集的行数（包括NULL）
- COUNT(*)，忽略所有的列，直接统计所有的行数

索引查询优化

最左前缀原则

在最左匹配原则中，有如下说明：

最左前缀匹配原则，非常重要的原则，mysql会根据联合索引一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
- 因为B+树的叶子节点中，数据页和数据页是按照联合索引第一个值来排序的，然后才是第二个，然后才是第三个。
= 和 in 可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成查询效率最高的形式，也就是匹配联合索引的形式。

CREATE TABLE `user` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL DEFAULT '',
  `password` varchar(20) NOT NULL DEFAULT '',
  `usertype` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`userid`),
  KEY `a_b_c_index` (`username`,`password`,`usertype`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

现在有一个user表，联合索引为(username, password, usertype)

explain select * from user2 where username = '1' and password = '1';

当查询条件有username，则会使用联合索引a_b_c_index查询。
当查询条件没有username，则不会使用。

explain select * from user2 where password = '1' and username = '1';

查询条件有username，乱序也是可以用上索引的

索引失效情况

计算导致索引失效

#用了索引
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

#计算导致索引失效
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

函数导致索引失效

#创建索引
CREATE INDEX idx_name ON student(NAME);

# 索引起作用
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
# 函数导致索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

类型转换导致索引失败

# name是varchar型
# 索引有用
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123'; 
# 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123;

范围条件右边的列索引失效

# 创建索引
CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);

# 索引idx_age_classId_name不能正常使用
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

# 创建索引
CREATE INDEX idx_age_name_classid ON student(age,NAME,classid);

# 索引idx_age_name_classid可以正常使用
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

应用开发中范围查询，例如:金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后。（创建的联合索引中，务必把范围涉及到的字段写在最后）

!= 或者 <> 导致索引失效

# 创建索引
CREATE INDEX idx_name ON student(NAME);

# 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;

# 但这个用了主键索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.id != 55 ;

is null可以用索引，is not null 无法使用索引

# 用了索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL; 

# 没用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

like以通配符%开头索引失效
```
# 使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%';

# 未使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';
```
拓展：Alibaba《Java开发手册》 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

or 前后存在非索引的列，索引失效

#未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
#在age字段上创建索引，发现还是没有用到索引
CREATE INDEX idx_age ON student(age);
#在classid上创建索引，这时候用上了索引，因为OR的前后两个字段都加上了索引。
CREATE INDEX idx_cid ON student(classid);

字符集不同导致索引失效

索引查询优化

关联查询优化

确保 ON 或者 USING 子句上的列上有索引。
- 一般来说，只需要在关联顺序中第二个表的相应列上加索引。
内连接：有索引的数据量大的表作为被驱动表供没有索引的数据量小的驱动表查询。（给关联字段添加索引）
Join 查询时，使用查询结果集(行 * 单行容量)小的驱动表嵌套大的驱动表

子查询优化

禁止使用 not in，not exists 子查询，改用或者 ;

排序索引

对应索引顺序不能错，否则不会使用索引
对于排序数据优化器会综合考虑全加载到内存进行 fileSort 更快还是使用索引排序更好。尽量使用上索引排序
当使用 where ... order by 时，也能用上索引

Group by 优化

使用 group by，order by，distinct时，尽量保证where过滤结果集在1000 以内

分页查询优化

select … from … limit 20000, 10改为select …from where id > 20000 limit 10。保证往聚簇索引上靠
- select* from article LIMIT 1,3
- select * from article LIMIT 3 OFFSET 1
上面两种写法都表示取第2,3,4三条数据。

LIMIT用法：

①如果后接一个参数，如select* from article LIMIT 10。表示取前10个数据。

②如果后接两个参数，如LIMIT 1,3 。第一个数表示要跳过的数量，后一位表示要取的数量，例如：select* from article LIMIT 1,3 就是跳过1条数据，从第2条数据开始取，取3条数据，也就是取2,3,4三条数据。

OFFSET用法：

③当limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的数量，offset表示要跳过的数量。例如：select * from article LIMIT 3 OFFSET 1 表示跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据。

覆盖索引

索引下推（ICP）

主键设计方案

淘宝：订单id可能是时间 +去重字段 + 用户id尾号6位
mysql8.0有改进的主键id

实现

高可用性、可扩展性
- 当主库发生故障时，可以快速的切到其某一个从库，并将该从库提升为主库，因为数据都一样，所以不会影响系统的运行
负载均衡、读写分离
- 主数据库负责写，从数据库负责读

分为

同步：用户写到主数据库的数据必须同步到从数据库后，才会告诉用户写成功。
异步：用户写到主数据库的数据，就告诉用户成功。
半同步：用户写到主数据库的数据，只要同步到一个从数据库就告诉用户成功。

分布式系统 CAP 理论

Consistency 一致性：任何用户读到的数据要么是最新的，要么就响应错误。
Available 可用性：任何用户的请求一定能返回到数据，而不会得到响应错误。
Partition tolerance 分区容忍性：分布式系统依靠网络传输数据，而网络是不可信的。但是消息丢失或者延迟，不影响系统继续提供服务。

主从模型

形式

一主一从
- 理想的模型，但是主库的压力太大（N 个从库，就需要主库中有 N 个 binlog dump 线程，会造成主库的性能震荡）。
一主多从
多主一从
双主
级联
- 减轻主库压力
- 但是容易放大错误的影响

主从原理

主库记录 binlog，然后从库将 binlog 在自己的服务器上重放，从而保证了主、从的数据一致性。

基于行
- 物理
- binlog 中记录实际更新数据的每一行。
基于语句
- 逻辑
- binlog 中记录了操作的语句，通过这些语句在从数据库中重放来实现。

主从步骤

从库启动 I/O 线程，与主库建立连接。
主库启动 binlog dump 线程，读取主库上的 binlog event，并且发送给从库 I/O 线程。
从库 I/O 线程将读取到的 binlog event 写到 relay log 中。
从库启动 SQL 线程，把 Relay log 中的数据进行重放，完成数据同步。

Relay log

引入了 Relay Log 之后，让原本同步的获取事件、重放事件解耦了，两个步骤可以异步的进行，Relay Log 充当了缓冲区的作用。Relay Log 有一个的文件，用于记录当前的进度，下一个事件从什么 Pos 开始写入，该文件由 SQL 线程负责更新。

方式

(1) 异步

主库不主动发送 binlog event，而是等从库建立 I/O 线程后，再创建 binglog dump 线程，读取 binlog，把 binlog event 发送过去。
主库执行完自己的事务后，记录完 binlog 后，就会返回结果；由 binlog dump 自行读取 binlog，再与从库通信。
- 处理请求和主从同步完全异步执行

(2) 同步

主库写完数据，并写完 binlog 后，必须等待所有从库同步完成后，再返回成功消息。

(3) 半同步

主库处理事务后，只需要等待一个从库同步完成后，就可以返回消息。

数据一致性

从库维护一个 relay-log.info 文件中，记录了当前从库正在的 binlog 和写入的 relay log 的 POS 位置。

MySQL 怎么开启主从？

Group by

用法

SELECT column_name, function(column_name) 
FROM table_name 
WHERE column_name operator value 
GROUP BY column_name;

分组后的条件使用 HAVING 来限定，WHERE 是对原始数据进行条件限制
几个关键字的使用顺序为 where 、group by 、having、order by

Datetime 和 Timestamp

Datetime：没有时区信息，8 bytes
舟山版权声明：本网信息来自于互联网，目的在于传递更多信息，并不代表本网赞同其观点。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，并请自行核实相关内容。本站不承担此类作品侵权行为的直接责任及连带责任。如若本网有任何内容侵犯您的权益，请及时联系我们，本站将会在24小时内处理完毕，E-mail：xinmeigg88@163.com

展开全文READ MORE

知识总结

【每周一星】百度创始人——李彦宏免费！又一款国产AI音乐工具，一键生成中文歌曲，太上头了！