MySQL面试(3): 综合

数据库原理

事务

概念

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用
Rollback 进行回滚。

ACID

1. 原子性(Atomicity)：

事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

2. 一致性(consistency)：

数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的.

3. 隔离性(Isolation)

一个事务所做的修改在最终提交以前，对其它事务是不可见的

4. 持久性(Durability)

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

使用重做日志来保证持久性。

ACID之间不是平级关系：

• 只有满足一致性，事务的执行结果才是正确的

• 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性

• 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性

• 事务满足持久化是为了能应对数据库崩溃的情况

CAP理论

C(Consistency) 一致性

一致性指*“all nodes see the same data at the same
time”*，即更新操作成功并返回客户端完成后，所有节点在同一时间的数据完全一致，所以，一致性，说的就是数据一致性。

对于一致性，可以分为从客户端和服务端两个不同的视角。从客户端来看，一致性主要指的是多并发访问时更新过的数据如何获取的问题。从服务端来看，则是更新如何复制分布到整个系统，以保证数据最终一致。

一致性是因为有并发读写才有的问题，因此在理解一致性的问题时，一定要注意结合考虑并发读写的场景。从客户端角度，多进程并发访问时，更新过的数据在不同进程如何获取的不同策略，决定了不同的一致性。

三种一致性策略：

对于关系型数据库，要求更新过的数据能被后续的访问都能看到，这是强一致性

如果能容忍后续的部分或者全部访问不到，则是弱一致性

如果经过一段时间后要求能访问到更新后的数据，则是最终一致性

CAP中说，不可能同时满足的这个一致性指的是强一致性

A(Availability) 可用性

可用性指*“Reads and writes always
succeed”*，即服务一直可用，而且是正常响应时间。

对于一个可用性的分布式系统，每一个非故障的节点必须对每一个请求作出响应。所以，一般我们在衡量一个系统的可用性的时候，都是通过停机时间来计算的。

P(Partition Tolerance) 分区容错性

分区容错性指*“the system continues to operate despite arbitrary message loss or
failure of part of the system”*，
即分布式系统在遇到某节点或网络分区故障的时候，仍然能够对外提供满足一致性和可用性的服务。

比如现在的分布式系统中有某一个或者几个机器宕掉了，其他剩下的机器还能够正常运转满足系统需求，或者是机器之间有网络异常，将分布式系统分隔未独立的几个部分，各个部分还能维持分布式系统的运作，这样就具有好的分区容错性。

简单点说，就是在网络中断，消息丢失的情况下，系统如果还能正常工作，就是有比较好的分区容错性。

CAP理论的3进2

CAP理论的核心是：一个分布式系统不可能同时很好的满足一致性，可用性和分区容错性这三个需求，最多只能同时较好的满足两个。

根据CAP原理将NoSQL数据库分成了满足CA原则、满足CP原则和满足AP原则三大类：

CA -
单点集群，满足一致性，可用性的系统，通常在可扩展性上不太强大。没有P意味着舍弃了分布式系统，通常的关系型数据库就是这种。

CP -
满足一致性，分区容忍性的系统，通常性能不是特别高。很多分布式的数据库都是设计成CP的，在发生极端情况下，优先保证数据的强一致性，代价就是舍弃系统的可用性。比如丢失一些请求等。比如redis、MongoDB等，Zookeeper中也使用了CP。

AP -
满足可用性，分区容忍性的系统，通常可能对一致性要求低一些。要高可用并允许分区，则需放弃一致性。一旦网络问题发生，节点之间可能会失去联系。为了保证高可用，需要在用户访问时可以马上得到返回，则每个节点只能用本地数据提供服务，而这样会导致全局数据的不一致性。而由于当前的网络硬件肯定会出现延迟丢包等问题，所以分区容忍性是我们必须需要实现的。

BASE理论

BASE是指基本可用（Basically Available）、软状态（ Soft State）、最终一致性（
Eventual Consistency）。

基本可用

基本可用是指分布式系统在出现故障的时候，允许损失部分可用性，即保证核心可用。

电商大促时，为了应对访问量激增，部分用户可能会被引导到降级页面，服务层也可能只提供降级服务。这就是损失部分可用性的体现。

软状态

软状态是指允许系统存在中间状态，而该中间状态不会影响系统整体可用性。分布式存储中一般一份数据至少会有三个副本，允许不同节点间副本同步的延时就是软状态的体现。MySQL
replication的异步复制也是一种体现。

最终一致性

最终一致性是指系统中的所有数据副本经过一定时间后，最终能够达到一致的状态。弱一致性和强一致性相反，最终一致性是弱一致性的一种特殊情况。

并发一致性问题

丢失修改

T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了
T1 的修改。

读脏数据

T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2
读取的数据是脏数据。

不可重复读

T2 读取一个数据，T1 对该数据做了修改。如果 T2
再次读取这个数据，此时读取的结果和第一次读取的结果不同。

幻影读

T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1
再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

隔离级别

未提交读(read uncommitted)

提交读(read committed)

可重复读(repeatable read)

可串行化(serializable)

数据库基础

SQL相关

连接查询

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

连接可以替换子查询，并且比子查询的效率一般会更快。

可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL
语句以及连接相同表。

内连接

内连接又称等值连接，使用 INNER JOIN 关键字。

SELECT A.value, B.value

FROM tablea AS A INNER JOIN tableb AS B

ON A.key = B.key;

可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE
中将两个表中要连接的列用等值方法连接起来。

SELECT A.value, B.value

FROM tablea AS A, tableb AS B

WHERE A.key = B.key;

在没有条件语句的情况下返回笛卡尔积。

自连接

自连接可以看成内连接的一种，只是连接的表是自身而已。

一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名

SELECT e1.name

FROM employee AS e1 INNER JOIN employee AS e2

ON e1.department = e2.department

AND e2.name = “Jim”;

自然连接

自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。

内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。

SELECT A.value, B.value

FROM tablea AS A NATURAL JOIN tableb AS B;

外连接

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

左连接又称左向外连接，查询的结果集包括SQL语句中左表的所有行，右表中匹配的行。如果左表的某行在右表中没有匹配行，则用空值表示。

右连接也成右向外连接，查询的结果集包括SQL语句中右表的所有行，左表中匹配的行。如果右表的某行在左表中没有匹配的行，则用空值表示

分组

分组就是把具有相同的数据值的行放在同一组中。可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。

指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。

GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。

SELECT col, COUNT(*) AS num

FROM mytable

GROUP BY col

ORDER BY num;

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

SELECT col, COUNT(*) AS num

FROM mytable

WHERE col > 2

GROUP BY col

HAVING num >= 2;

分组规定：

• GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；

• 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；

• NULL 的行会单独分为一组；

• 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

Distinct

相同值只会出现一次。当后面跟多个列时，它作用于所有列，也就是说所有列的值都相同才算相同。

Limit

限制返回的行数。可以有两个参数，第一个参数为起始行，从 0
开始(默认，可以不写)；第二个参数为返回的总行数。

排序

默认是asc(升序)，降序是desc

可以按多个列进行排序，并且为每个列指定不同的排序方式：

SELECT *

FROM mytable

ORDER BY col1 DESC, col2 ASC;

组合查询

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

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

默认会去除相同行，如果需要保留相同行，使用 UNION ALL。

只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

SELECT col

FROM mytable

WHERE col = 1

UNION

SELECT col

FROM mytable

WHERE col =2;

视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

对视图的操作和对普通表的操作一样。

视图具有如下好处：

• 简化复杂的 SQL 操作，比如复杂的连接；

• 只使用实际表的一部分数据；

• 通过只给用户访问视图的权限，保证数据的安全性；

• 更改数据格式和表示

触发器

触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。

触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE
关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER
用于审计跟踪，将修改记录到另外一张表中。

INSERT 触发器包含一个名为 NEW 的虚拟表。

CREATE TRIGGER mytrigger AFTER INSERT ON mytable

FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; – 获取结果

DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。

UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW
是可以被修改的，而 OLD 是只读的。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

存储过程

存储过程可以看成是对一系列 SQL 操作的批处理。

使用存储过程的好处：

• 代码封装，保证了一定的安全性

• 代码复用

• 由于是预先编译，因此具有很高的性能

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ;
为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

包含 in、out 和 inout 三种参数。

给变量赋值都需要用 select into 语句。

每次只能给一个变量赋值，不支持集合的操作。

delimiter //
create procedure myprocedure( out ret int )
begin
declare y int;
select sum(col1)
from mytable
into y;
select y*y into ret;
end //
delimiter ;

call myprocedure(@ret);
select @ret;

游标

在存储过程中使用游标可以对一个结果集进行移动遍历。

游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤：

1. 声明游标，这个过程没有实际检索出数据

2. 打开游标

3. 取出数据

4. 关闭游标

delimiter //
create procedure myprocedure(out ret int)
begin
declare done boolean default 0;
declare mycursor cursor for
select col1 from mytable;
# 定义了一个 continue handler，当 sqlstate ‘02000’ 这个条件出现时，会执行
set done = 1
declare continue handler for sqlstate ‘02000’ set done = 1;
open mycursor;
repeat
fetch mycursor into ret;
select ret;
until done end repeat;
close mycursor;
end //
delimiter ;

事务管理

基本术语：

• 事务（transaction）指一组 SQL 语句；

• 回退（rollback）指撤销指定 SQL 语句的过程；

• 提交（commit）指将未存储的 SQL 语句结果写入数据库表；

• 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）

不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。

MySQL
的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现
START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK
语句执行后，事务会自动关闭，重新恢复隐式提交。

通过设置 autocommit 为 0 可以取消自动提交；autocommit
标记是针对每个连接而不是针对服务器的。

如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION
语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。

START TRANSACTION
// …
SAVEPOINT delete1
// …
ROLLBACK TO delete1
// …
COMMIT

高频面试

1. 连接查询与子查询比较

子查询：把内层查询结果当作外层查询的比较条件

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。

可以使用连接查询（JOIN）代替子查询，连接查询不需要建立临时表，因此其速度比子查询快。

子查询

1. where型子查询：把内层查询的结果作为外层查询的比较条件。

2. from型子查询：把内层的查询结果当成临时表，供外层sql再次查询。查询结果集可以当成表看待，临时表需要一个别名。

3. exists型子查询：把外层sql的结果，拿到内层sql去测试，如果内层的sql成立，则该行取出。内层sql是exists后的查询。

连接查询

如果是JOIN的话，它是走嵌套查询的。小表驱动大表，且通过索引字段进行关联。如果表记录比较少的话，还是OK的。大的话业务逻辑中可以控制处理，即分别根据索引单表取数据，然后在程序里面做join，merge数据。

2. 不推荐使用jion的原因

1.DB承担的业务压力大，能减少负担就减少。当表处于百万级别后，join导致性能下降；

2.分布式的分库分表。这种时候是不建议跨库join的。目前mysql的分布式中间件，跨库join表现不良。

3.修改表的schema，单表查询的修改比较容易，join写的sql语句要修改，不容易发现，成本比较大，当系统比较大时，不好维护。

不使用join的解决方法

在业务层，单表查询出数据后，作为条件给下一个单表查询，也就是子查询。 会担心子查询出来的结果集太多。mysql对in的数量没有限制，但是mysql限制整条sql语句的大小。通过调整参数max_allowed_packet，可以修改一条sql的最大值。建议在业务上做好处理，限制一次查询出来的结果集是能接受的。分开查多次虽然效率低（在某些场景下，分查比联合查询效率更高），但是有利于程序查询缓存，减少锁的竞争，减少查询冗余，应用层面相当于实现哈希关联。更容易对数据库进行拆分，做到高可用，易拓展，解耦。

3. drop、delete、truncate 比较

delete

1、可以删除整张表的数据：delete from
table_name，也可以添加where子句控制要删除的数据：delete from table_name where .
. .，同时可以操作view

2、属于DML语言，每次删除一行，都在事务日志中为所删除的每行记录一项。产生rollback，事务提交之后才生效;如果有相应的 trigger,执行的时候将被触发，

如果删除大数据量的表速度会很慢

3、删除内容不删除表的结构(定义)，不释放空间。

truncate

1、truncate table table_name，只能操作table，在功能上与不带 WHERE 子句的 DELETE
语句相同：二者均删除表中的全部行,但truncate比delete速度快，由于默认情况下truncate通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放，所以使用的系统和事务日志资源少，可以使用reuse storage; truncate会将高水线复位(回到最开始).

2、 truncate是DDL语言, 操作立即生效,自动提交，原数据不放到rollback
segment中, 不能回滚. 操作不触发trigger

3、删除内容、释放空间但不删除表的结构(定义)。

drop

1、drop table
table_name，drop语句将删除表的结构，以及被依赖的约束(constrain),触发器(trigger),索引(index);
依赖于该表的存储过程/函数将保留,但是变为invalid状态.

2、drop也属于DDL语言，立即执行，执行速度最快

3、删除内容和定义，释放空间。

4. 视图能更新吗？何时更新？

不是所有的视图都可以更新，视图更新必须遵循以下规则

（1）若视图的字段是来自字段表达式或常数，则不允许对此视图执行INSERT、UPDATE操作，允许执行DELETE操作；

（2）若视图的字段是来自库函数，则此视图不允许更新；

（3）若视图的定义中有GROUP BY子句或聚集函数时，则此视图不允许更新；

（4）若视图的定义中有DISTINCT任选项，则此视图不允许更新；

（5）若视图的定义中有嵌套查询，并且嵌套查询的FROM子句中涉及的表也是导出该视图的基表，则此视图不允许更新；

（6）若视图是由两个以上的基表导出的，此视图不允许更新；

（7）一个不允许更新的视图上定义的视图也不允许更新；

（8）由一个基表定义的视图，只含有基表的主键或候补键，并且视图中没有用表达式或函数定义的属性，才允许更新。

MySQL相关

索引

1. 为什么要使用索引

• 可以避免全表扫描，提升检索效率，多数查询可以仅扫描少量索引页及数据页，而不是遍历所有数据页。

• 对于非聚集索引，有些查询甚至可以不访问数据页。

• 聚集索引可以避免数据插入操作集中于表的最后一个数据页

• 一些情况下，索引可以用于避免排序操作

2. 哪些字段能成为索引？适合建立索引？

1、表的主键、外键必须有索引；

2、数据量超过300的表应该有索引；

3、经常与其他表进行连接的表，在连接字段上应该建立索引；

4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

5、索引应该建在选择性高的字段上；

6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替；

8、频繁进行数据操作的表，不要建立太多的索引，因为索引维护需要一定开销；

9、删除无用的索引，避免对执行计划造成负面影响；

3. B树、B+树原理

B树

定义

B树是为实现高效的磁盘存取而设计的多叉平衡搜索树(多路平衡查找树)

记住B树高度计算公式 h <= log_{ceil(m/2)}((N+1) /2) + 1
ceil代表向上取整，m代表阶，N代表关键字个数

注意这个高度是不包含叶子节点层在内的，如果算上还要再加1。

B树的特点

定义：对于一个m阶的B树

• 根节点至少包含两个孩子；

• 树中每个节点最多含有m个孩子（m>=2）

• 除根节点和叶节点外，其他每个节点至少有ceil(m/2)个孩子 ceil表示取上限

• 一个内结点x若含有n[x]个关键字，那么x将含有n[x]+1个子女

• 所有叶子节点位于同一层，叶子节点不包含任何关键字信息(可以看做是外部节点或查询失败的节点，实际上这些节点不存在，指向这些节点的指针都为null)

• 每个非终端节点中包含有n个关键字信息(n，P0，K1，P1，K2，P2，…，Kn，Pn)。

a) Ki (i=1…n)为关键字，且关键字按顺序升序排序K(i-1)< Ki。

b)
Pi为指向子树根的接点，且指针P(i-1)指向子树中所有结点的关键字均小于Ki，但都大于K(i-1)。

c) 关键字的个数n必须满足： [ceil(m / 2)-1]<= n <= m-1

对于每个结点，主要包含一个关键字数组Key[ ]，一个指针数组（指向儿子）Son[ ]。

查找流程

使用顺序查找（数组长度较短时）或折半查找方法查找Key[
]数组，若找到关键字K，则返回该结点的地址及K在Key[ ]中的位置；

否则，可确定K在某个Key[i]和Key[i+1]之间，则从Son[i]所指的子结点继续查找，直到在某结点中查找成功；或直至找到叶结点且叶结点中的查找仍不成功时，查找过程失败。

其他

1. B树的高度计算公式h <= log_{ceil(m/2) ((N+1)/2)} + 1

Ceil代表向上取整，m代表阶数，N代表关键字个数，注意这个高度是不包含叶子节点层在内的，如果算上还要再加1。

B+树

B+树特点

B+树是B树的变体，其定义与B树基本相同，除了：

• 非叶子节点的子树指针与关键字个数相同，能存储更多的关键字

• 非叶子节点的子树指针P[i]，指向关键字值[K[i], K[i+1]]的子树

• 非叶子节点仅用来索引，数据都保存在叶子节点中

• 所有叶子节点均有一个链指针指向下一个叶子节点

4. B+树为什么更适合做存储索引？

B+Tree更适合用来做存储索引，原因：

• B+树磁盘读写代价更低：B+树内部结构并没有指向关键字具体信息的指针，也就是非叶子节点不存放数据，数据都存放在叶子节点中，只存放索引信息，因此其内部节点相对于B树更小，如果把所有同一内部的节点存放在同一板块中，板块能容纳的关键字也越多，一次性读入内存的关键字也就越多，相对来说，IO读写次数也就降低了。

• B+树的查询效率更加稳定：由于内部节点并不指向最终文件内容的节点，而只是叶子节点中关键字的索引，索引任何关键字的查找必须走一条根节点到叶子结点的路，所有关键字查询的长度相同，所以每个关键字的查询效率也几乎是相同的、稳定的O(logn)。

• B+树更有利于对数据库的扫描。B树在提高了磁盘的IO性能的同时并没有解决元素遍历低下的效率问题，而B+树只要遍历叶子节点就可以解决对全部关键字的扫描，所以对于数据库中频繁使用的范围查询，有着更高的性能。

B树必须用中序遍历的方法按序扫库(排序树中序遍历得到的是有序序列)，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持。这是数据库选用B+树的最主要原因。

5. 还有哪些结构可以成为索引存储？

1. 二叉查找树

极端情况下退化成线性的

2. Hash结构

Hash索引效率高，但是缺点也有很多。

缺点：

• 仅仅能满足“=”、“IN”，不能使用范围查询，因为经过Hash运算不能保证和原来键值一样的顺序

• 无法避免对数据的排序操作

• 不能利用部分索引键查询，

• 不能避免表扫描，hash索引是将索引键通过Hash运算之后将运算结果的hash值和所对应的行指针信息存放在一个bucket当中。由于不同索引键存在相同的hash值，所以即使取出满足某个条件的hash数据了，还是要进入bucket当中取出数据进行比较。

• 遇到大量的hash值相等(哈希冲突)的情况后，性能并不一定回避B-Tree索引高

3. BitMap

仅适用于字段取值种类固定的情况，比如性别有男女，经过编码，可以利用极小的空间来表示极大的数据。

但是BitMap有一个很大的缺陷，就是锁的粒度非常大。当尝试新增、修改数据的时候，通常会将在同一个位图的数据都锁住，因为某行所在的数据可能会因为数据的新增或修改而改变。不适合高并发的联机事务处理系统。

4. 倒排索引

是一种索引方法，被用来存储在全文搜索下某个单词在一个文档或者一组文档中的存储位置的映射。它是文档检索系统中最常用的数据结构。

有两种不同的反向索引形式：

• 一条记录的水平反向索引（或者反向档案索引）包含每个引用单词的文档的列表。

• 一个单词的水平反向索引（或者完全反向索引）又包含每个单词在一个文档中的位置。

后者的形式提供了更多的兼容性（比如短语搜索），但是需要更多的时间和空间来创建。

以英文为例，下面是要被索引的文本：

• To = “it is what it is”

• T1 = “what is it”

• T2 = “it is a banana”

我们就能得到下面的反向文件索引：

“a”: {2}

“banana”: {2}

“is”: {0, 1, 2}

“it”: {0, 1, 2}

“what”: {0, 1}

检索的条件"what", “is” 和 “it” 将对应这个集合：{0,1}∩{0,1,2}∩{0,1,2} = {0,1}。

5. 空间数据索引R树

MyISAM
存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据。

6. 全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。

查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

6. 聚集索引和非聚集索引的区别？

参考https://www.cnblogs.com/aspnethot/articles/1504082.html

聚集索引

一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。

**聚集索引确定表中数据的物理顺序。**聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

非聚集索引

也叫二级索引

一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据。

7. 密集索引和稀疏索引的区别？

• 密集索引文件中的每个搜索码都对应一个索引值。索引项包括索引值以及指向该搜索码值的第一条数据记录的指针。由于该索引符合聚集索引，因此记录根据相同的码值排序。

• 稀疏索引文件只为索引码的某些值建立索引项。同理因为稀疏索引也是聚集索引，每一个索引项包括索引值以及指向该搜索码值的第一条数据记录的指针。

在MySQL的InnoDB中，

• 若一个主键被定义，该主键作为密集索引

• 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引

• 若不满足以上条件，InnoDB内部会生成一个隐藏主键(密集索引)

• 非主键索引存储相关键位和其对应的主键值，包含两次查找

8. 索引常见的优化方法有哪些？

1. 索引列不能使表达式的一部分，也不能是函数的参数，否则无法使用索引

2. 需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好

3. 注意索引列的顺序，让选择性最强的索引列放在前面。选择性是指不重复的索引值和记录总数的比值。选择性越高，查询效率越高

4. 对于BLOB、TEXT 和 VARCHAR 类型的列使用前缀索引

9. 联合索引是什么？内部存储结构如何？如何使用的？

概念

两个或更多个列上的索引被称作复合/联合索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。比如对a、b两列建立联合索引，则先对a排序，然后对a相同的按b排序。

对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

存储结构

本质上说，联合索引也是一棵B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。假设有两个字段a和b，创建联合索引(a,b)，那么底层结构如下图所示：

键值都是排序的，通过叶子节点逻辑上可以顺序地读取出所有数据。很明显，

• select * from table where a = xx and b =xxx可以使用(a,b)的联合索引

• Select * from table where a=xxx也可以使用(a,b)索引

• Select * from table where
  b=xxx不可以使用这棵B+树索引。因为叶子节点上的b值为1、2、1、4、1、2不是排序的，故使用不到(a,b)索引。

使用

对于上面的例子，考虑如下SQL语句，

Select xxx from table where a=xxx order by b

此局域可以直接使用联合索引得到结果，无需做一次额外的排序操作。

对于联合索引(a,b,c)来说，下面的SQL语句也可以直接通过联合索引得到结果

Select xxx from table where a=xxx order by b

Select xxx from table where a=xxx and b=xxx order by c

但select xxx from table where a=xxx order by c无法使用，需要一次filesort排序操作

查询性能优化

1. 如何定位慢的SQL语句？

慢查询日志是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件，通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的。

通过使用–slow_query_log[={0|1}]选项来启用慢查询日志。所有执行时间超过long_query_time秒的SQL语句都会被记录到慢查询日志。

• 缺省情况下hostname-slow.log为慢查询日志文件安名，存放到数据目录，同时缺省情况下未开启慢查询日志。

• 缺省情况下数据库相关管理型SQL(比如OPTIMIZE TABLE、ANALYZE TABLE和ALTER
  TABLE)不会被记录到日志。

  对于管理型SQL可以通过–log-slow-admin-statements开启记录管理型慢SQL。

• mysqld在语句执行完并且所有锁释放后记入慢查询日志。记录顺序可以与执行顺序不相同。获得初使表锁定的时间不算作执行时间。

• 可以使用mysqldumpslow命令获得日志中显示的查询摘要来处理慢查询日志。

• 用查询缓存处理的查询不加到慢查询日志中，表有零行或一行而不能从索引中受益的查询也不写入慢查询日志。

2. 如何对SQL进行分析和优化？

2.1 Explain分析

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain
结果来优化查询语句。

比较重要的字段有：

• select_type : 查询类型，有简单查询、联合查询、子查询等

• type：扫描方式

• all：即全表扫描，如果是这个，尽量去优化

• index：按索引次序扫描，先读索引，再读实际的行，结果还是全表扫描，主要优点是避免了排序。因为索引是排好的

• range：以范围的形式扫描

• const 常量查询，查询过程中整个表最多只会有一条匹配的行

• eq_ref 使用唯一索引查找

• ref： 非唯一索引访问

• possible_keys：可能用到的索引

• key : 实际使用的索引

• rows : 扫描的行数

• key_len： 索引字段最大可能使用的长度

• Extra：其他信息

• Using index：此查询使用了覆盖索引，即通过索引就能返回结果，无须访问表

• Using where：表示 MySQL
  服务器从存储引擎收到行后再进行“后过滤”（Post-filter）。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合
  where
  句的条件，符合就留下，不符合便丢弃。因为检查是在读取行后才进行的，所以称为“后过滤”。

• Using temporary：使用到了临时表，MySQL 使用临时表来实现 distinct 操作

• Using filesort：若查询所需的排序与使用的索引的排序一致，因为索引是已排序的，因此按索引的顺序读取结果返回；否则，在取得结果后，还需要按查询所需的顺序对结果进行排序，这时就会出现Using filesort 。

2.2 减少数据访问

1. 减少请求的数据量

• 只返回必要的列，不要使用select * 语句

• 只返回必要的行，使用LIMIT语句来限制返回的数据

• 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的

2. 减少服务端扫描的行数

• 使用索引来覆盖查询

• 检查索引是否使用正确，比如联合索引是否使用正确等

3. 重构查询方式重构SQL

1. 切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);

分解为如下的查询

rows_affected = 0
do {
rows_affected = do_query(
“DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH)
LIMIT 10000”)
} while rows_affected > 0

2. 分解大连接查询

企业中提倡尽量别用join

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

• 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用

• 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询

• 减少锁竞争

• 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩

• 查询本身效率也可能会有所提升。例如下面的例子，使用IN()代替连接查询，可以让
  MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

SELECT * FROM tab
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag=‘mysql’;

拆分为：

SELECT * FROM tag WHERE tag=‘mysql’;
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

锁机制

1. 封锁粒度

MySQL 中提供了两种封锁粒度：行级锁以及表级锁。

应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此封锁粒度越小，系统开销就越大。

在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡。

2. 加锁类型

读写锁

• 排它锁（Exclusive），简写为 X 锁，又称写锁

• 共享锁（Shared），简写为 S 锁，又称读锁

有以下两个规定：

• 一个事务对数据对象 A 加了 X 锁，就可以对 A
  进行读取和更新。加锁期间其它事务不能对 A 加任何锁。

• 一个事务对数据对象 A 加了 S 锁，可以对 A
  进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S
  锁，但是不能加 X 锁。

意向锁

使用意向锁（Intention
Locks）可以更容易地支持多粒度封锁。在存在行级锁和表级锁的情况下，事务 T 想要对表
A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A
中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS
都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S
锁。有以下两个规定：

• 一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；

• 一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A
加了 X/IX/S/IS
锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X
锁失败。

• 任意 IS/IX
  锁之间都是兼容的，因为它们只是表示想要对表加锁，而不是真正加锁；

• S 锁只与 S 锁和 IS 锁兼容，也就是说事务 T 想要对数据行加 S
  锁，其它事务可以已经获得对表或者表中的行的 S 锁。

3. 三级封锁协议

一级封锁协议

事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。

可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。

二级封锁协议

在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。

可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1
级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。

三级封锁协议

在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。

可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X
锁，从而避免了在读的期间数据发生改变。

4. 两段锁协议

加锁和解锁分为两个阶段进行。

可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。

事务遵循两段锁协议是保证可串行化调度的充分条件。例如以下操作满足两段锁协议，它是可串行化调度。

lock-x(A)…lock-s(B)…lock-s©…unlock(A)…unlock©…unlock(B)

但不是必要条件，例如以下操作不满足两段锁协议，但是它还是可串行化调度

lock-x(A)…unlock(A)…lock-s(B)…unlock(B)…lock-s©…unlock©

5. 乐观锁和悲观锁

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

乐观锁

它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。

在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。

一般的实现乐观锁的方式就是记录数据版本，比如使用时间戳或者版本号。

悲观锁

指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度(悲观)，因此，在整个数据处理过程中，将数据处于锁定状态。

悲观锁的实现，往往依靠数据库提供的锁机制。

悲观锁实现流程

1）在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）

2）如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。开发者决定

3）如果加锁成功，就可以对记录做修改，事务完成后就会解锁了。

4）其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常

MySQL
InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住。

6. MVCC 多版本并发控制

基本概念

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB
存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用
MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

锁机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销。

MVCC实现

MVCC是通过保存数据在某个时间点的快照来实现的.
不同存储引擎的MVCC实现是不同的, 典型的有乐观并发控制和悲观并发控制。

版本号

• 系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。

• 事务版本号：事务开始时的系统版本号。

隐藏的列

MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：

• 创建版本号：指示创建一个数据行的快照时的系统版本号；

• 删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。

Undo日志

MVCC 使用到的快照存储在 Undo
日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。

InnoDB的MVCC实现

InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了这个行的创建时间和行的删除时间。这里存储的并不是实际的时间值,而是系统版本号(可以理解为事务的ID)。每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID，该事务的版本号肯定会大于当前所有数据行快照的创建版本号。

SELECT语句

InnoDB会根据以下两个条件检查每行记录:

1. InnoDB只会查找版本早于当前事务版本的数据行(也就是，行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。

2. 行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除。

只有a,b同时满足的记录，才能返回作为查询结果.

DELETE语句

InnoDB会为删除的每一行保存当前系统的版本号(事务的ID)作为删除标识。

UPDATE语句

InnoDB执行UPDATE，实际上是新插入了一行记录，将当前系统版本号作为更新前的数据行快照的删除版本号，并将当前系统版本号作为更新后的数据行快照的创建版本号。

可以理解为先执行 DELETE 后执行 INSERT。

快照读和当前读

快照读

使用 MVCC 读取的是快照中的数据，这样可以减少加锁所带来的开销。

快照读是不加锁(事务隔离条件不为serializable情况下)的非阻塞读，select。在seriablizable情况下，由于是串行读，此时的快照读也退化成当前读，
即select … lock in share mode
模式。之所以出现快照读，是基于提升并发性能的考虑，快照读的实现是基于多版本并发控制MVCC。MVCC是行级锁的变种，但是很多情况下避免了加锁操作，因此开销更低。

当前读

select … lock in share mode, select … for update, update, delete, insert,
不管共享锁还是排它锁，只要是增删改查语句，都是当前读。读取的是记录当前的最新版本，并且读取以后还需要保证其他并发事务不能修改当前记录，对读取的记录加锁。

7. InnoDB可重复读隔离级别下（RR）如何避免幻读？

MVCC 不能解决幻读的问题，Next-Key Locks
就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC

• Next-Key Locks 可以解决幻读问题。

Record locks 行锁

锁定一个记录上的索引，而不是记录本身。如果表没有设置索引，InnoDB
会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。

Gap Locks

Gap就是索引树中插入新纪录的空隙，Gap
lock即锁定一个范围，包括记录本身。Gap锁的目的是为了防止同一事务的两次当前读出现幻读的情况。Gap锁在read-commited以及更低级别的事务隔离级别上是没有的，这也是RC也即read-commited下无法避免幻读的原因。而在RR
repeatable-read和serializable级别上，默认都支持gap锁。

在RR下面，不论删改查，对主键索引或唯一索引不一定用到Gap锁。

• **如果where条件全部命中，则不会用gap锁，只会加记录锁。**所谓全部命中，是指精确查询的时候，所有记录都有，比如where
  id in (a, b,
  c)，a,b,c都命中存在，而不只命中a和b。当我们获取的记录具备唯一性，假设ID是主键或者唯一键，那么在事务A中，将ID作为where筛选的条件去做当前读的时候，比如delete
  from table where
  id=9。那么，事务B此时新增的那条信息，必然会在当前读的范围之外。所以在事务B在新增数据并提交了之后，事务A再去做当前读还是获取到原先的数据集，并不会产生所谓的幻读现象。所以，此时加行锁就足够了。

• 如果where条件部分命中或者全不命中，则会加gap锁，比如两个不同的事务下，

第一个事务：select * from table where id in (5, 7, 9) lock in share mode

此时在第二个事务中插入数据insert into table values(“ii”, 8) 会阻塞
实验发现，5-9之间的数都会被阻塞，插入4、10等不会阻塞。
Gap锁会用在非唯一索引或者不走索引的当前读中
可以参考官方文档
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks
非唯一索引：Gap锁是用来防止插入的。不走索引：类似锁表

Next-key锁

其实就是行锁+gap锁，不仅锁定一个记录上的索引，也锁定索引之间的间隙。例如一个索引包含以下值：10,
11, 13, and 20，那么就需要锁定以下区间：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

8. RC、RR级别下的InnoDB的非阻塞读如何实现？

• 数据行里的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID字段

• Undo日志

• Read view快照

在Mysql中MVCC是在Innodb存储引擎中得到支持的，Innodb为每行记录都实现了三个隐藏字段：

• 6字节的事务ID（DB_TRX_ID）

• 7字节的回滚指针（DB_ROLL_PTR）

• 隐藏的ID

6字节的事物ID用来标识该行所述的事务，7字节的回滚指针需要了解下Innodb的事务模型。

MVCC 在mysql 中的实现依赖的是 undo log 与 read view。

1. undo log: undo
   log中记录的是数据表记录行的多个版本，也就是事务执行过程中的回滚段,其实就是MVCC
   中的一行原始数据的多个版本镜像数据。

2. read view: 快照，主要用来判断当前版本数据的可见性。

存储引擎

MySQL下主要有InnoDB和MyISAM两种。

InnoDB

是 MySQL
默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE
READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ 间隙锁（Next-Key
Locking）防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT
INSERT）。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE
选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

比较

1. 事务：InnoDB 是事务型的，可以使用 Commit和Rollback语句。MyISAM不支持事务

2. 并发：MyISAM 只支持表级锁，InnoDB 支持行级锁+表级锁。

3. 外键：InnoDB 支持外键， MyISAM不支持。

4. 备份：InnoDB 支持在线热备份。

5. 崩溃恢复：MyISAM 崩溃后发生损坏的概率比InnoDB 高很多，而且恢复的速度也更慢。

6. 其它特性：MyISAM 支持压缩表和空间数据索引。

分库分表

水平切分

基本原理

水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。

当一个表的数据不断增多时，Sharding
是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

切分策略

一般的shading策略：

• 哈希取模：hash(key) % N；

• 范围切分：可以是 ID 范围也可以是时间范围进行切分；

优点：单表大小可控，天然水平扩展。
缺点：无法解决集中写入瓶颈的问题。

• 映射表：使用单独的一个数据库来存储映射关系。

优点：ID和库的Mapping算法可以随意更改。
缺点：引入额外的单点。

存在的问题

1. 事务问题

使用分布式事务来解决，比如 XA 接口。

2. 连接查询

可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接。

3. ID唯一性

• 使用全局唯一 ID（GUID）

优点：简单。
缺点：生成ID较长，有重复几率。

• 为每个分片指定一个 ID 范围

优点：最简单。
缺点：单点风险、单机性能瓶颈。

• 分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)

优点：高性能高可用、易拓展。
缺点：需要独立的集群以及ZK。

• 利用数据库集群并设置相应的步长（Flickr方案）

优点：高可用、ID较简洁。
缺点：需要单独的数据库集群。

垂直切分

垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

垂直切分以后，不可再使用join

日志

MySQL日志主要包含：错误日志，查询日志，慢查询日志，事务日志，二进制日志

1. 错误日志 error log

记录Mysql运行过程中的Error、Warning、Note等信息，系统出错或者某条记录出问题可以查看Error日志。

Mysql的错误日志默认以hostname.err存放在Mysql的日志目录，可以通过以下语句查看：

show variables like “log_error”;

2. 查询日志 general query log

记录mysql的日常日志，包括查询、修改、更新等的每条sql。

查看是否开启了查询日志：show global variables like “%genera%”

MySQL打开general log日志后，所有的查询语句都可以在general
log文件中输出，如果打开，文件会非常大，建议调试的时候打开，平时关闭

如果打开了日志功能，但是没有写入日志，那就有可能是mysql对日志文件的权限不够，所以需要指定权限。

3. 二进制日志 binlog

二进制日志，包含一些事件，这些事件描述了数据库的改动，如建表、数据改动等，主要用于备份恢复、回滚操作等。

作用：

• 包含了所有更新了数据或者已经潜在更新了数据(比如没有匹配任何行的一个DELETE)

• 包含关于每个更新数据库(DML)的语句的执行时间信息

• 不包含没有修改任何数据的语句，如果需要启用该选项，需要开启通用日志功能

• 主要目的是尽可能的将数据库恢复到数据库故障点，因为二进制日志包含备份后进行的所有更新

• 用于在主复制服务器上记录所有将发送给从服务器的语句

• 启用该选项数据库性能降低1%，但保障数据库完整性，对于重要数据库值得以性能换完整

格式

Binlog有3种格式

• Statement：每一条会修改数据的SQL都会记录到master的binlog中，salve在复制的时候SQL进程会解析成和原来master端执行相同的SQL再执行

• 优点：在statement模式下首先就是解决了row模式的缺点，不需要记录每一行数据的变化减少了binlog日志量，节省了I/O以及存储资源，提高性能。因为他只需要激励在master上所执行的语句的细节一届执行语句时候的上下的信息。

• 缺点：在statement模式下，由于记录的是执行语句，所以为了让这些语句在salve也能正确执行，那么必须保存记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和master端执行的时候相同的结果。

• Row：日志中会记录成每一行数据被修改的形式，然后再salve端再对相同的数据进行修改，只记录要修改的数据，只有value，不会有多表关联的情况。

• 优点：在row模式下，binlog中可以不记录执行的SQL语句的上下文相关信息，仅仅记录哪一条记录被修改了，修改成什么样，所以row的日志会清楚记录下每一行数据修改的细节。

• 缺点：记录过细，日志非常庞大

• Mixed：MySQL会根据执行的每一条具体的SQL语句来区分对待记录的日志形式，也就是在statement和row之间选择一种

4. 慢查询日志

记录MySQL 慢查询的日志

查看日志功能是否开启：show variables like “%slow%”;

打开慢日志功能：set global slow_query_log = on;

查看下默认设置的慢查询的时间：show variables like “%long_query%”;

修改慢查询的时间：打开/etc/my.cnf , 加入慢查询配置文件

slow-query-log = 1

slow-query-log-file = /tmp/mysql-slow.log

long_query_time = 1 #设置满请求时间, 设置查多少秒的查询算是慢查

5. 事务日志

事务日志（InnoDB特有的日志）可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把改修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。

MySQL复制

主从复制

核心

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

• binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。

• I/O 线程
  ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。

• SQL线程
  ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中执行。

数据同步过程

1. 主库把数据更改记录到二进制日志中

2. 备库将主库的二进制日志写入到自己的中继日志中

3. 备库读取中继日志重放到备库数据上

半同步复制

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，此时主上已经提交的事务可能并没有传到从上，导致新主上的数据不完整。半同步复制即，**主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay
log中才返回给客户端。**半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟。

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

• 主从服务器负责各自的读和写，极大程度缓解了锁的争用；

• 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；

• 增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

参考资料

1. cyc2018 地址
2. 高性能mysql第三版
3. MySQL技术内幕第二版
4. 其他一些博客