MySQL常见面试题及解答_mysql sql面试题，自学Golang

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Golang全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上Go语言开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注go）

正文

2. 编写存储过程

DELIMITER //
CREATE PROCEDURE get_user_count()
BEGIN
DECLARE user_count INT;
SELECT COUNT(*) INTO user_count FROM users;
SELECT user_count;
END;
//
DELIMITER;

首先使用DELIMITER命令将分隔符设置为//，以便在存储过程中使用分号。然后，我们创建了一个名为get_user_count的存储过程。在存储过程中，我们使用DECLARE声明了一个局部变量user_count，用于存储查询结果。

在存储过程的主体中，我们使用SELECT COUNT(*) INTO user_count查询 users 表中的用户数量，并将结果存储在user_count变量中。然后，我们使用SELECT user_count返回存储过程的结果。最后，我们使用DELIMITER;将分隔符恢复为默认的分号。

3. 测试
   调用存储过程：

CALL get_user_count();

1.3 触发器

参考1：SQL触发器
参考2：Oracle 触发器详解（trigger）

触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

触发器可以从DBA_TRIGGERS，USER_TRIGGERS数据字典中查到。

触发器的处理是可以任意的事情，包括修改目标表的数据，也可以报一个异常，终止数据的处理。

1.3.1 触发器的语法

创建触发器：

create [or replace] trigger 触发器名
触发时间 {before | after} – view 中是 instead of
触发事件 {insert | update | delete} – dml、ddl、database
on 触发对象 – table、view、schema、database
触发频率 {for each row} – 行级触发器。默认：语句级触发器
[follows 其它触发器名] – 多个触发器执行的 前后顺序
[when 触发条件]
begin
pl/sql 语句;
end;

删除触发器：

DROP TRIGGER 触发器名称;—删除触发器

1.3.2 示例

1. 创建数据表

create table emp_tagx as select * from emp; --创建测试表

2. 编写触发器sql

–加工资
create or replace trigger tg_emp_change
before update or insert or delete on emp_tagx —相关的行为发生，都会执行这个触发器
for each row —发生变化的每一行|增量
begin —处理逻辑
dbms_output.put_line(‘修改前工资:’||:old.sal); —打印修改前的工资 :old专用的,固定写法 专指修改前的数据
dbms_output.put_line(‘修改后工资:’||:new.sal); —打印修改后的工资 :new专用的,固定写法 专指修改后的数据
----insert into target_sal(:old.empno,:old.sal,:new.sal);
—commit;
—每次加工资不能超过2000，否则就失败
if :new.sal - :old.sal >2000 then
raise_application_error(-20001,‘加工资步子不要太大’); --报错
end if ;
end;

3. 测试：

update emp_tagx set sal = sal+ 2000 where empno=7788;

1.3.3 Databse 触发器

触发事件
startup：'数据库打开’时，相反的 = shutdown
logon ：当用户连接到数据库并 ‘建立会话’ 时，相反的 = logoff
servererror：发生服务器错误时

示例：

create or replace trigger scott.tr_al_database_login_info
after logon on database
declare
v_option_user varchar2(50) := sys_context(‘USERENV’, ‘OS_USER’); – 电脑域账户
begin
insert into scott.database_login_info
(client_ip,
login_user,
database_name,
database_event,
create_user,
create_data)
values
(dbms_standard.client_ip_address,
dbms_standard.login_user,
dbms_standard.database_name,
dbms_standard.sysevent,
v_option_user,
sysdate);
end;

※1.3.4 实操

1. 创建数据表

– 创建数据表 users
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
);

2. 创建触发器
   提示：DELIMITER //是重新定义sql语句的结束符。

– 1 创建阻止插入用户名为admin的触发器
DELIMITER //
CREATE TRIGGER tr_user_admin_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
IF NEW.username = ‘admin’ THEN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘用户名 “admin” 不允许插入。’;
END IF;
END;
//
DELIMITER;

– 2 创建阻止修改用户名为admin的触发器
DELIMITER //
CREATE TRIGGER tr_user_admin_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
IF old.username = ‘admin’ THEN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘用户名 “admin” 不允许修改。’;
END IF;
END;
//
DELIMITER;

3. 测试

– 这将触发触发器并阻止插入操作
INSERT INTO users (username, email) VALUES (‘admin’, ‘admin@example.com’);

– 这将成功插入记录
INSERT INTO users (username, email) VALUES (‘john’, ‘john@example.com’);

– 阻止修改的测试
UPDATE users SET email = ‘123’ WHERE id = 1;

1.4 视图

参考1：MySQL的视图 具体更多的视图使用示例可以看参考文章。

介绍：

1. 视图（view）是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用。
2. 数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。
3. 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

作用：

1. 简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用。
2. 安全原因，如果一张表中有很多数据，很多信息不希望让所有人看到，此时可以使用视图视，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，可以对不同的用户，设定不同的视图。

1.4.1 视图的语法

1. 创建视图的语法

CREATE [OR REPLACE] [algorithm = {UNDEFINED| MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

参数说明：
（1）algorithm：可选项，表示视图选择的算法。
（2）view_name ：表示要创建的视图名称。
（3）column_list：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
（4）select_statement
：表示一个完整的查询语句，将查询记录导入视图中。
（5）[WITH [CASCADED | LOCAL] CHECK OPTION]：可选项，表示更新视图时要保证在该视图的权限范围之内。

1.4.2 视图的示例

1. 创建数据表

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
);

INSERT INTO users(id, username, email) VALUES (1, ‘admin’, ‘john@example.com’);

2. 编写视图

CREATE OR REPLACE VIEW view1_emp
AS
SELECT id,username,email from users;

3. 查看表和视图

show full tables;

4. 使用视图
   因为视图是基于真实数据表虚拟出来的一个虚拟数据表，所以针对数据表的查询操作也适用于视图。

SELECT * FROM view1_emp;

5. 修改视图
   修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。MySQL中通过CREATE OR REPLACE VIEW语句和ALTER VIEW语句来修改视图。

格式：

alter view 视图名 as select语句

示例：

alter view view1_emp
as
select a.deptno,a.dname,a.loc,b.ename,b.sal from dept a, emp b where a.deptno = b.deptno;

2 MySQL数据库的设计及性能优化策略

参考1：MySQL数据库的设计及性能优化

数据库优化

1. 减少IO次数
2. 降低CPU计算
3. SQL语句优化：具体是尽量少Join、少排序、少使用“or”关键字、尽量用union all代替union、避免类型转换、选择合适的索引类型、从全局出发优化，而不是片面调整等等。

数据库架构优化

1. 负载均衡：MySQL一般部署的是高可用性负载均衡集群，具备读写分离，一般只对读进行负载均衡。
2. 读写分离：读写分离简单的说是把对数据库读和写的操作分开对应不同的数据库服务器，这样能有效地减轻数据库压力，也能减轻IO压力。
3. 数据切分：通过某种特定的条件，将存放在同一个数据库中的数据分散存放到多个数据库上，实现分布存储，通过路由规则路由访问特定的数据库，这样一来每次访问面对的就不是单台服务器了，而是N台服务器，这样就可以降低单台机器的负载压力。

其他优化

1. 适当使用视图加速查询

• 把表的一个子集进行排序并创建视图，有时能加速查询（特别是要被多次执行的查询）。
• 它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。视图中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。

2. 算法优化
3. 封装存储过程：经编译和优化后存储在数据库服务器中，运行效率高，可以降低客户机和服务器之间的通信量，有利于集中控制，易于维护。

2.1 一份非常完整的MySQL规范

一份非常完整的MySQL规范

3 MySQL数据表数据量特别大，怎么分库分表，提高查询效率

参考1：超详细的mysql分库分表方案

3.1 MySQL表大小限制

MySQL一般安装部署在Linux操作系统上（例如CentOS 7.4），默认都是InnoDB存储引擎，且开启了独立表空间选项（参数innodb_file_per_table=1），此时创建一个表orders就会自动生成一个数据文件orders.ibd，文件大小是受操作系统Block大小限制的，下面是ext3文件系统块大小和最大尺寸的对应关系。

操作系统块大小	最大文件尺寸	最大文件系统尺寸
1KB	16GB	2TB
2KB	256GB	8TB
4KB	2TB	16TB
8KB	16TB	32TB

查看操作系统页大小及块大小

这就说明MySQL单表的最大尺寸不能超过2TB，我们简单来算一下，假设一个表的平均行长度为32KB（InnoDB最大行长度限制65536字节，64KB），那么他最大能存储多少行数据？4 x 1024 x 1024 x 1024 / 32 = 134217728大约 1.4 亿不到。

3.2 分表方案

分表的应用场景是单表数据量增长速度过快，影响了业务接口的响应时间，但是 MySQL 实例的负载并不高，这时候只需要分表，不需要分库（拆分实例）。
垂直分表、水平分表、按月分表、MySQL分区表。

一个表大小是满足如下公式的：TABLE_SIZE = AVG_ROW_SIZE x ROWS，从这里可以知道表太大，要么是平均行长度太大，也就说表的字段太多，要么是表的记录数太多。这就产生两种不同的分表方案，即切分字段（垂直分表）和切分记录（水平分表）。

1. 垂直分表：根据业务进行拆分，比如可以将一张表中的多个字段拆成两张表，一张是不经常更改的，一张是经常改的。
2. 水平分表：水平拆分表就是按照表中的记录进行分片，比如orders表可以拆分为orders0、orders1、orders2、orders3、orders4等。举个例子，目前订单表orders有2000w数据，根据业务的增长，估算一年之后会达到1亿，同时参考阿里云RDS for MySQL的最佳实践，单表不建议超过500w，1亿数据分20个子表就够了。

• 问题来了，按照什么来拆分呢？主键id还是用户的user_id？
  按主键ID拆分数据很均匀，但是通过ID查询orders的场景几乎没有，业务访问orders大部分场景都是根据user_id来过滤的，而且user_id的唯一性又很高（一个user_id对应的orders表记录不多，选择性很好），按照user_id来作为Sharding key能满足大部分业务场景，拆分之后每个子表数据也比较均匀。
• 这样就将orders表拆分成20个子表，对应到InnoDB的存储上就是20个数据文件（orders_0.ibd，orders_1.ibd等），这时候执行SQL语句select order_id, order_sn, source from orders where user_id = 1001;就能很快的定位到要查找记录的位置是在orders_1，然后做查询重写，转化为SQL语句select order_id, order_sn, source from orders_01 where user_id = 1001，这种查询重写功能很多中间件都已经实现了，常用的就是sharding-sphere或者sharding-jdbc都可以实现。

3. 按月分表
   对于账务或者计费类系统，每天晚上都会做前一天的日结或日账任务，每月的1号都会做月结或月账任务，任务执行完之后相关表的数据都已静态化了（业务层不需要这些数据），根据业务的特性，可以按月创建表，比如对于账单表 bills，就可以创建按月分表（十月份表bills_202010，202011十一月份表），出完月账任务之后，就可以归档到历史库了，用于数据仓库ETL来做分析报表，确认数据都同步到历史库之后就可以删除这些表释放空间。
4. MySQL分区表
   详见超详细的mysql分库分表方案

3.3 分库方案

MySQL的高可用架构大多都是一主多从，所有写入操作都发生在Master上，随着业务的增长，数据量的增加，很多接口响应时间变得很长，经常出现Timeout，而且通过升级MySQL实例配置已经无法解决问题了，这时候就要分库，通常有两种做法：按业务拆库和按表分库，下面就介绍这两种分库方案啦。

按业务分库
举个例子，交易系统trade数据库单独部署在一台RDS实例，现在交易需求及功能越来越多，订单，价格及库存相关的表增长很快，部分接口的耗时增加，同时有大量的慢查询告警，升级RDS配置效果不大，这时候就需要考虑拆分业务，将库存，价格相关的接口独立出来。

这样按照业务模块拆分之后，相应的trade数据库被拆分到了三个RDS实例中，数据库的写入能力提升，服务的接口响应时间也变短了，提高了系统的稳定性。

按表分库
上面介绍了分表方案，常见的有垂直分表和水平分表（拆分后的子表都在同一个 RDS 实例中存储），对应的分库就是垂直分库和水平分库，这里的分库其实是拆分 RDS 实例，是将拆分后的子表存储在不同的 RDS 实例中，垂直分库实际业务用的很少，就不介绍了，主要介绍下水平分库。

举个例子，交易数据库的订单表orders有2亿多数据，RDS实例遇到了写入瓶颈，普通的insert都需要50ms，时常也会收到CPU使用率告警，这时就要考虑分库了。根据业务量增长趋势，计划扩容一台同配置的RDS实例，将订单表orders拆分20个子表，每个RDS实例10个。

这样解决了订单表orders太大的问题，查询的时候要先通过分区键user_id定位是哪个RDS实例，再定位到具体的子表，然后做 DML操作，问题是代码改造的工作量大，而且服务调用链路变长了，对系统的稳定性有一定的影响。其实已经有些数据库中间件实现了分库分表的功能，例如常见的mycat，阿里云的DRDS等。

3.4 分布式数据库

通过上面的分表和分库方案的介绍，主要会遇到下面三类问题：

1. MySQL单Master的写入性能瓶颈。
2. 分库分表后的SQL解析处理，服务调用链路变长，系统变得不稳定。
3. 分库分表后动态扩容不好实现，例如开始分了20个表，不影响业务的情况下扩容至50个表不好实现。

3.5 拆分后的问题

详见超详细的mysql分库分表方案

3.6 MySQL的分区有了解过吗

MySQL的分区是一种数据库表分割和管理数据的方法，它将表分成多个子表，每个子表被称为一个分区，每个分区可以存储特定范围的数据。分区可以根据特定的列值范围、列值的列表、列值的哈希等方式定义，以满足不同的数据管理和查询需求。以下是关于MySQL分区的一些重要信息：

1. 为什么使用分区： 使用分区可以提高数据库表的性能和可维护性。它可以加速查询操作，减少索引大小，同时还可以使数据备份和维护更加高效。分区还可以帮助管理大量历史数据。
2. 分区类型： MySQL支持多种分区类型，包括：

• 范围分区（Range Partitioning）： 数据根据某一列的范围值划分到不同的分区中。例如，根据订单的创建日期将数据分成每个月一个分区。
• 列表分区（List Partitioning）： 数据根据某一列的值列表划分到不同的分区中。例如，根据地区将数据分成不同的分区。
• 哈希分区（Hash Partitioning）： 数据根据某一列的哈希值划分到不同的分区中。这通常用于均衡数据在分区之间的分布，以避免热点分区。
• 键值分区（Key Partitioning）： 数据根据某一列的键值进行划分，与范围分区和列表分区类似，但更加灵活。
• 子分区（Subpartitioning）： 可以在每个主分区内再次分成子分区，以更细粒度地管理数据。

3. 分区管理： 创建、维护和删除分区是关键的操作。MySQL提供了一些SQL语句和工具来执行这些操作，如ALTER TABLE语句来添加或删除分区，以及REORGANIZE PARTITION来重新组织分区。
4. 分区键： 分区键是用于决定数据分区的列。合适的分区键选择对于性能至关重要。通常，分区键应该是经常用于查询和过滤的列，以充分利用分区的性能优势。
5. 分区限制： 分区引入了一些限制和注意事项，如不能在分区表上使用外键、全文索引等。在设计和实现分区时需要注意这些限制。

总结：
MySQL的分区是一种强大的数据库管理技术，可以帮助处理大量数据和优化查询性能。但需要谨慎设计和管理分区，以确保满足业务需求并避免潜在的问题。不同版本的MySQL可能会在分区功能上有一些差异，因此建议参考使用的MySQL版本的官方文档以获取详细信息。

3.7 BAT 大厂在用的分库分表方案，一看就懂！

BAT 大厂在用的分库分表方案，一看就懂！

4 MySQL数据库结构

4.1 MySQL的索引底层是怎么存数据的【底层结构】

来自GPT3.5的回答。

MySQL默认的存储引擎InnoDB是使用B+树（B-Plus Tree） 数据结构来实现索引的。无论是主键索引还是辅助索引（非主键索引），InnoDB存储引擎都是使用B+树数据结构。B+树在InnoDB中用于实现高效的数据存储和检索，特别适用于处理大量数据和范围查询。

但需要注意的是，MySQL支持多种存储引擎，不同的存储引擎可以使用不同的数据结构。例如，MyISAM存储引擎使用的是B树数据结构实现索引。因此，具体使用哪种数据结构取决于所选的存储引擎。

4.2 红黑树

参考1：深入理解MySQL索引底层数据结构

本质上还是一个二叉树，叫做自平衡二叉树。平衡树在插入和删除的时候，会通过旋转操作将树的左右节点达到平衡。
红黑树的定义(规则)：

1. 任何一个节点都有颜色，红色或黑色。
2. 根节点是黑色的。
3. 父子节点之间不能出现两个连续的红色节点。
4. 任何一个根节点，遍历到它的子孙节点，所经过的黑色节点数必须相同。
5. 空节点被认为是黑色的。

红黑树通过这些规则来保持树的平衡，从而确保在最坏情况下（比如所有插入都是递增或递减的）也能保持 O(log n) 的操作复杂度。

若采用红黑树作为索引的数据结构，当数据量特别大的时候，黑红树的高度就特别大了。假如有500w条数据，那么红黑树的高度可能达到几十甚至更多，要经过大量的磁盘I/O操作，性能太差，要解决这一问题，就要减少I/O的次数。

4.3 B树(B-Tree)特点

参考1：数据结构-B树
参考2：B树详解

B树的定义：
B树，又称多路平衡查找树，B树中所有结点的孩子个数的最大值称为B树的阶，通常用m表示。 一棵m阶B树或为空树，或为满足如下特性的m叉树:

1. 树中每个结点至多有m棵子树（即至多含有m-1个关键字）。
2. 若根结点不是终端结点，则至少有两棵子树。
3. 除根结点外和叶节点外，所有非叶结点至少有m/2(向上取整）棵子树，即至少含有，m/2-1(向上取整）个关键字。
4. 所有的叶结点都在最下面一层，叶结点是不带任何信息的。叶结点其实也就是查找失败的结点。
5. 终端结点，就是叶子结点上面那一层。终端结点是有数据的，而叶子结点是没有数据的。
6. 对于每一个节点来说，它的任何一个子树的高度都一定是相同的。

B树的优点：
来自GPT3.5的回答。

1. 自平衡性：B树是一种自平衡的数据结构，当插入或删除操作导致节点的键值数量超过一定阈值时，B树会进行节点分裂和合并操作来保持树的平衡，这不仅能使树的高度保持在一个较小的范围，也使得查找操作的时间复杂度可以维持在O(log n)级别，插入和删除也能保持较低的时间复杂度。
2. 多路搜索树： B树的每个节点都可以包含多个子节点，这使得B树相对于二分搜索树来说更加“矮胖”，在相同高度的情况下可以存储更多的数据，提升了查找操作性能。
3. 顺序访问：B树的节点通常按照键值的顺序排列，这样使得范围查询非常高效。在数据库等应用中，B树的这个特点尤其重要，可以提升区间查询性能。
4. 适应磁盘存储： B树的设计考虑了磁盘存储的特点，因为B树矮胖，可以最小化磁盘访问次数。这在数据库和文件系统中非常重要，因为磁盘访问通常是性能的瓶颈。
5. 支持随机访问和顺序访问：B树支持随机和顺序访问操作，这对于数据库等应用中的不同查询模式都非常有用。

为什么B树要保持平衡？
答：为了保证查找效率。因为B树是需要访问磁盘，树的层数越高，则访问磁盘的次数也就越多，而访问磁盘是比较慢、比较费时间的。所以为了保证查找效率，在构建B树的时候，会对其高度进行限制，尽可能地让其保持平衡。

怎么保持B树的平衡？
答：回忆下AVL（二叉平衡树）中是这么要求的，左右两棵子树的高度差不超过1，即平衡因子不超过1。但是对于B树而言，高度差不超过1，太难为它了，毕竟有那么多的子树，因此，做出了一个简单粗暴的决定，规定在 m 叉查找树（B树）中，对于任何一个节点，它所有子树的高度都要相同，就是不能有高度差。那如果能做到这一点，就可以保证我们的这棵多差查找数它是平衡的，也就可以保证它不会有太多层，从而就保证了查找的效率。

B树的查找：
由于B树相对于二叉树来说矮胖了许多，所以它所涉及的IO操作也相对少了许多。但是其在查找数据的时候并没有减少比较次数。因为比较数据是在内存中进行的，所以相对来说时间上会更加迅速，几乎可以忽略。

B树的插入：
对高度为ｋ的m阶B树，新结点一般是插在叶子层。通过检索可以确定关键码应插入的结点位置。然后分两种情况讨论：

1. 若该结点中关键码个数小于m-1，则直接插入即可。
2. 若该结点中关键码个数等于m-1，则将引起结点的分裂。以中间关键码为界将结点一分为二，产生一个新结点，并把中间关键码插入到父结点(ｋ-1层)中。
3. 重复上述工作，最坏情况一直分裂到根结点，建立一个新的根结点，整个B树增加一层。

B树的删除：
B树的删除操作相对较为复杂，这里不做整理了。

4.4 B+Tree特点

参考1：数据结构-B树
参考2：B+树总结

来自GPT3.5的回答。

B+树（B+Tree）是一种自平衡的树状数据结构，类似于B树，但在数据库和文件系统中更常见。B+树在数据库系统中广泛应用于实现索引结构，以提供高效的数据检索和范围查询。以下是B+树的主要定义特点：

1. B+树是一种自平衡的树状结构，通过分裂和合并等操作来保持树的平衡，每个节点可以包含多个键值。
2. 节点中的键值按照从小到大的顺序排列。
3. 每个节点可以有多个子节点，这些子节点分别指向比键值小和比键值大的数据。
4. B+树的根节点可能只有一个键值，而其他非根节点至少有一个键值。
5. 任意非叶子节点的子节点数在m到2m之间，其中m是最小度数。
6. 叶子节点（最底层节点）具有相同的深度，即都位于同一层，这使得所有查询操作具有相同的时间复杂度。
7. 所有数据都存储在叶子节点上，而非叶子节点仅用于路由和索引，不存储实际数据。
8. 每个叶子节点都有一个指针，指向下一个数据，从而形成一个有序链表。可以通过链表进行范围查询，这对数据库的范围查询非常有用。

B+树的设计使其特别适用于数据库索引。由于非叶子节点仅用于路由和索引，而所有的数据都存储在叶子节点上，这使得B+树的范围查询非常高效。另外，由于叶子节点之间形成有序链表，B+树可以支持快速的范围查询操作，这对于数据库的区间查询操作非常重要。

4.5 BTree和B+Tree区别

来自GPT3.5的回答。

1. 节点内容：
   B树：节点中除了包含键值，还包含对应的子节点指针。
   B+树：节点中只包含键值，而对应的子节点指针只存在于叶子节点。
2. 数据存储：
   B树：数据存储可以在非叶子节点和叶子节点中都存在。
   B+树：数据只存储在叶子节点中，非叶子节点仅用于索引，使得叶子节点形成一个有序链表。
3. 叶子节点连接：
   B树：叶子节点之间没有特定的连接。
   B+树：叶子节点之间形成一个有序链表，便于范围查询。
4. 节点分裂和合并：
   B树和B+树都通过节点的分裂和合并来保持平衡。
5. 查询性能：
   B树和B+树都能高效地进行查找操作，但是在范围查询方面，B+树更具优势，因为数据仅存储在叶子节点上，形成有序链表。
6. 应用领域：
   B树：适用于文件系统等需要范围查询的场景，也适用于数据库索引，但在范围查询方面相对较弱。
   B+树：在数据库索引等需要高效范围查询的应用中表现出色。

总结：
B+树在数据库系统中更为常见，因为它在范围查询等方面的优势。B树和B+树在实现细节上有一些差异，这些差异使得它们适用于不同的应用场景。选择适合的树状数据结构取决于具体的需求和应用环境。

4.6 Hash结构

来自GPT3.5的回答。
定义：
哈希数据结构通过使用哈希函数，将数据映射为固定大小的索引值，然后再将数据存储在数组或哈希表中，从而实现高效的数据存储和检索。

特点：

1. 快速查找：哈希数据结构具有快速查找的特性，因为通过哈希函数计算索引值，可以直接定位到数据存储的位置。
2. 固定大小索引： 哈希函数将数据映射为固定大小的索引值，这使得数据在存储时可以直接映射到数组或哈希表中的特定位置。
3. 冲突处理： 由于不同的数据通过哈希函数计算后可能得到相同的索引值，从而产生冲突。解决冲突的常见方法包括拉链法（Chaining）和开放地址法（Open Addressing）等。
4. 均匀分布： 好的哈希函数会使数据均匀地分布在数组或哈希表中，从而减少冲突的可能性，提高查找效率。
5. 高效性： 在理想情况下，哈希数据结构可以实现常数时间的查找操作，即使在最坏情况下也是近似常数时间。
6. 不适合排序： 哈希数据结构不适合对数据进行排序操作，因为通过哈希函数得到的索引值是随机的，会使数据在数组或哈希表中无序存储。
7. 内存消耗： 哈希数据结构可能会消耗相对较多的内存，特别是在需要解决冲突时，可能需要更多的空间来存储链表或其他结构。

Hash的数组设置的多大比较合适？
最好是和存储数据的大小一样，但是最好事先设置小一点，后面根据实际的数据量再进行扩容。

4.7 为什么不使红黑树来实现索引

来自GPT3.5的回答。
MySQL使用B+树（B-Plus Tree）而不是红黑树来实现索引的主要原因是B+树在数据库系统中更适合用于实现高效的数据存储和检索。

以下是一些解释为什么 MySQL 选择使用 B+树 而不是红黑树：

1. 范围查询效率： B+树在范围查询方面具有优势。数据库中经常需要执行范围查询，例如查找某个范围内的数据。B+树的叶子节点形成有序链表，使得范围查询非常高效，而红黑树不具备这种特点。
2. 磁盘访问优化： B+树的设计考虑了磁盘访问的特点。在数据库中，数据通常存储在磁盘上，而不是内存中。B+树的叶子节点之间形成一个有序链表，可以减少磁盘I/O的次数，提高磁盘访问效率。
3. 适用于数据库索引： B+树是一种用于实现数据库索引的理想结构。数据库索引需要支持高效的数据检索和范围查询，而B+树在这些方面都表现出色。
4. 内存管理：红黑树在每个节点上都需要存储颜色信息，而B+树不需要在非叶子节点上存储数据，从而节省了内存。这对于大规模的数据库系统来说非常重要。
5. 性能平衡： 虽然红黑树在某些操作上可能比B+树效率稍高，但B+树在大多数数据库操作（如范围查询、插入、删除等）上表现更稳定且更优秀。

红黑树在某些情况下也可以用于数据库索引，但B+树的设计和特点使得它更适合数据库系统的需求，因为它可以提供高效的数据存储和检索，特别适用于大量数据和范围查询。

4.8 为什么mysql的索引使用B+树而不是hash

来自GPT3.5的回答。

MySQL 使用 B+树 而不是哈希（Hash）索引的主要原因是 B+树 在数据库系统中更适合用于实现高效的数据存储和检索，特别是满足了数据库操作的多样性需求。

以下是为什么MySQL选择 B+树 而不是B树或哈希索引的一些关键原因：

1. 范围查询效率： B+树在范围查询方面具有优势。数据库经常需要执行范围查询操作，例如查找某个范围内的数据。B+树的叶子节点形成有序链表，这使得范围查询非常高效，而B树则相对较弱。
2. 磁盘访问优化： B+树的设计考虑了磁盘访问的特点。在数据库中，数据通常存储在磁盘上，而不是内存中。B+树的叶子节点之间形成有序链表，可以减少磁盘I/O的次数，从而提高磁盘访问效率。相比之下， 哈希索引需要随机访问，不适合在磁盘上进行。
3. 内存管理： B+树非常适合用于内存和磁盘的混合存储，因为非叶子节点不存储实际数据，而只存储索引信息。这对于大规模的数据库系统非常重要，因为内存是有限的。
4. 支持多样的查询需求： B+树不仅仅支持查找，还包括范围查询、排序等操作，而哈希索引在这些方面的支持不如B+树。
5. 哈希冲突： 哈希索引在遇到哈希冲突时需要解决冲突问题，这可能会引入性能问题和复杂性。

虽然B树也能够提供高效的数据存储和检索，但B+树在数据库系统中更适合处理大量数据和多样化的查询需求。综合考虑性能、数据存储和查询需求，MySQL选择使用B+树来实现索引，以满足数据库操作的多样性和高效性。

5 MySQL数据库索引

5.1 有哪些索引

参考1：MySQL数据库之索引详解
MySQL数据库有如下索引类型：

1. 主键索引：是数据库的所有索引中查询速度最快的，并且每个数据表只能有1个主键索引列。用于唯一标识每一行数据，并确保该列的值不重复且不为空。
2. 唯一索引：确保索引列的值在整个表中是唯一的，允许有空值。一个表可以有多个唯一索引。
3. 普通索引：最基本的索引类型，用于加速查询速度，但不要求索引列的值唯一。
4. 全文索引：主要用于解决大数据量的情况下模糊匹配的问题。如果数据库中某个字段的数据量非常大，使用like+通配符的方式进行查找，速度就会变得非常慢。针对这种情况，就可以使用全文索引的方式，来加快模糊查询的速度。全文索引的原理便是通过分词技术，分析处文本中关键字及其出现的频率，并依次建立索引。只能在支持的存储引擎（如 MyISAM 和 InnoDB）上使用。
5. 复合索引：又叫联合索引，它是在多个列上创建的索引，用于加速联合查询，例如在WHERE子句中同时使用多个条件。
6. 前缀索引：对索引列的前几个字符创建的索引，可以节省空间，但可能降低查询性能。
7. 哈希索引：基于哈希函数的索引，用于快速精确匹配，因为哈希函数生成的索引值是随机的，所以不适用于范围查询。
8. 空间索引：是MySQL5.7之后的版本支持的索引类型，用于支持空间数据类型（如地理坐标点、线和多边形）的查询，以及空间范围的搜索。
9. 覆盖索引：一个包含所有查询所需数据的索引，从而避免了回表操作，提高查询性能。

5.2 前缀索引

来自GPT3.5的回答。

前缀索引的语法：

CREATE INDEX index_name ON table_name (column_name(length));

• index_name：是索引的名称；
• table_name：是表名；
• column_name：是要创建索引的列名；
• length：是指定要索引的前缀长度。通过设置length，可以指定要索引的前几个字符或字节。

需要注意以下几点：

1. 索引选择：前缀索引适用于字符串类型的列，例如VARCHAR、CHAR、TEXT等。一般应该根据实际需求选择适当的前缀长度。较短的前缀可以节省存储空间，但可能会减少索引的选择性，从而影响查询性能。
2. 索引大小：前缀索引可以减小索引的大小，这对于大表和长文本列非常有用。然而，过度缩小前缀长度可能会导致索引选择性下降，进而影响查询性能。
3. 查询使用：前缀索引只能在满足前缀条件的查询中起作用。例如，创建了一个前缀索引，只有在查询中使用了与索引前缀相匹配的条件时，才能充分利用索引。否则，MySQL可能无法使用索引。
4. 字符集和排序规则：在创建前缀索引时，需要考虑字符集和排序规则，以确保索引正常工作。不同的字符集和排序规则可能会影响索引的大小和查询效果。

（跳过）5.3 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键

来自GPT3.5的回答。

1. 唯一性和数据完整性：主键是表中的唯一标识，确保每一行数据都有唯一的标识符。这有助于维护数据的完整性和准确性，防止重复数据的插入。
2. 快速查找： 主键的设计可以大大提高查询效率，特别是使用整型的自增主键。整型主键的值通常是递增的，使得数据在物理上更有序，从而减少磁盘访问次数，提高查询性能。
3. 聚簇索引： 在InnoDB中，主键是聚簇索引，意味着数据行的物理存储顺序与主键的顺序一致。这有助于范围查询和连续读取的性能提升。
4. 避免额外索引： 使用自增整型主键可以减少对其他字段的索引需求。这是因为主键本身已经提供了快速查找的能力，不需要额外的索引来支持查询操作。
5. 减少存储空间和内存使用： 整型的自增主键通常占用较小的存储空间，这有助于减少磁盘和内存的使用。
6. 插入性能： 使用自增主键可以减少插入时的索引维护开销。由于主键的递增特性，插入操作不会频繁触发索引重建。这个节点满了，直接新增一个节点就好了；若不是递增的，有个节点存储满了（5, 9），但是新插入了一个数据（7）在这个节数据的中间，则需要将这个节点先分裂，再平衡去满足B+Tree的结构；
7. 避免热点问题： 使用自增主键可以避免数据插入的热点问题。如果使用随机生成的主键，可能会导致频繁的节点分裂和调整。

总结：
建议在InnoDB表中建立主键，并且使用整型的自增主键，是为了优化数据库的性能、提高查询效率，并确保数据的完整性。不过，在某些特殊情况下，可能需要根据具体应用需求来选择适合的主键策略。

5.4 MySQL索引失效有哪些情况

来自GPT3.5的回答。

1. 不使用索引字段： 如果查询中没有使用到索引字段，数据库优化器可能会选择不使用索引，而进行全表扫描。
2. 使用LIKE查询： 如果在LIKE查询中使用通配符%在索引字段的开头，索引也可能会失效。例如，WHERE column_name LIKE '%value'。
3. 使用OR条件： 在查询中使用OR条件可能导致索引失效，特别是当OR条件涉及多个字段时。
4. 索引列使用IS NULL或IS NOT NULL： 在一些情况下，对于索引列使用IS NULL或IS NOT NULL也可能导致索引失效。
5. 使用函数或表达式： 当在查询中对索引字段使用函数或表达式时，索引可能无法有效使用。例如，WHERE YEAR(timestamp_column) = 2023，这种情况下，索引可能会失效。
6. 表连接查询： 在表连接查询中，如果连接的字段没有适当的索引，索引可能会失效。
7. 数据类型不匹配： 当索引字段和查询条件的数据类型不匹配时，索引可能失效。比如将字符串和数字进行比较。
8. 索引选择性低： 如果索引字段的选择性很低，即字段中有很多重复值，数据库优化器可能会认为全表扫描更快。
9. 顺序性问题： 当查询需要返回大部分或全部数据时，优化器可能认为进行全表扫描更有效，而不是使用索引。
10. 小表全表扫描： 对于非常小的表，使用索引可能不如全表扫描快速。

总结：
索引失效的情况有很多，很多情况下都涉及到查询条件、字段数据类型、函数使用等。为了避免索引失效，应该理解查询优化的原则，根据具体情况优化查询语句和索引设计。可以通过EXPLAIN命令来分析查询的执行计划，以确定是否有效使用了索引。

5.5 Like导致的失效怎么解决

来自GPT3.5的回答。

MySQL中的LIKE查询可能导致索引失效，特别是在通配符%出现在查询的开头时。这是因为通配符在开头的情况下，索引无法有效地进行前缀匹配。为了解决这个问题，可以考虑以下几种方法：

1. 使用前缀索引： 如果知道LIKE查询通配符不会出现在开头，可以考虑使用前缀索引。前缀索引是对索引列的前几个字符建立索引，这样可以使得索引在一定程度上有效。
2. 使用全文索引： 如果需要在文本数据中进行模糊查询，全文索引可能更适合。MySQL提供了全文索引功能，可以在文本字段上进行更复杂的模糊搜索。
3. 使用搜索引擎： 对于复杂的全文搜索需求，可以考虑使用专门的搜索引擎，如Elasticsearch，这些引擎针对全文搜索进行了优化。
4. 考虑缓存： 对于一些相对固定的查询，可以考虑使用缓存来减少数据库的负载。将常用的查询结果缓存下来，以减少LIKE查询的频率。
5. 数据预处理： 如果查询的数据量不是特别大，可以在应用层对数据进行预处理，将需要进行LIKE查询的字段进行标准化，然后进行匹配。
6. 反向LIKE查询： 反向LIKE查询意味着将查询的字符串进行反转，然后使用索引。这可以有效地利用索引进行匹配，尤其是在查询开头使用通配符的情况。
7. 使用其他查询方式： 如果可能，尽量避免使用通配符%在 LIKE 查询的开头。可以考虑使用其他查询方式，如精确匹配、范围查询等。

总结：
针对LIKE查询导致的索引失效，需要根据具体情况选择合适的解决方法。这可能包括索引优化、查询重构、使用全文索引或搜索引擎等，取决于数据和查询需求。

5.6 主键索引比较快是为什么

在数据库中，最快的索引通常是主键（Primary Key）索引。主键是唯一标识表中每一行记录的列，而且它不能包含空值。主键索引的快速性主要基于以下几个原因：

1. 唯一性： 主键要求每个值都是唯一的，这使得数据库引擎能够迅速定位具有特定主键值的行。
2. 聚集索引（Clustered Index）： 在一些数据库系统中，主键索引通常被实现为聚集索引，这意味着数据行的物理顺序与索引的顺序一致。这使得按主键进行范围查询更为高效。
3. 默认情况下的索引： 大多数数据库系统在创建主键时会自动创建一个相应的索引，因此无需额外的操作。

虽然主键索引是通常情况下最快的索引，但实际性能也受到多个因素的影响，包括查询的类型、表的大小、硬件性能等。在某些情况下，其他类型的索引（如唯一索引或复合索引）也可能提供高效的查询性能，具体取决于查询模式和数据访问模式。因此，在设计数据库时，需要根据实际需求和查询模式选择适当的索引类型。

---

答：MySQL的主键索引是B+Tree结构。同 4.8 为什么mysql的索引使用B+树而不是B树或hash

1. B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。
2. mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树每个节点的key和data在一起，无法进行区间查找。

5.7 MySQL可以没有主键索引吗

来自GPT3.5的回答。

MySQL中的表可以没有主键索引，但建议为表添加主键索引以提高数据的完整性和查询性能。

1. 主键索引是一种用于唯一标识表中每行数据的索引，它能够确保每个数据行都有唯一的标识符。主键索引可以帮助维护数据的完整性，防止重复数据的插入。此外，主键索引还会在InnoDB存储引擎中作为聚簇索引，这意味着数据行的物理存储顺序与主键的顺序一致，从而提高范围查询和连续读取的性能。
2. 如果表没有主键索引，MySQL仍然会为每一行数据分配一个隐含的rowid（或物理地址）作为唯一标识符。但是，这种隐含的标识符不像主键那样具有明确的业务含义，而且不会自动帮助维护数据的唯一性。此外，没有主键索引也可能导致一些查询操作变得更加低效，因为数据库优化器可能需要使用更多的资源来处理没有主键索引的表。

总结：
尽管MySQL的表可以没有主键索引，但在大多数情况下，建议为表添加主键索引，以确保数据的完整性，并提高查询性能。主键索引是数据库设计中的一项重要指导原则。

5.8 MySQL的主键索引和唯一性索引有什么区别

来自GPT3.5的回答。
主键索引（Primary Key Index）：

1. 唯一性： 主键索引要求所有的值都是唯一的，确保表中每一行数据都有一个唯一的标识符。
2. 非空性： 主键索引的值不能为NULL，这意味着每一行数据都必须具有主键值。
3. 物理存储顺序： 在InnoDB存储引擎中，主键索引同时也是聚簇索引。这意味着数据行的物理存储顺序与主键的顺序一致。
4. 自动增长： 主键索引通常使用整型的自增值，这可以提高插入性能和减少碎片。
5. 只能有一个： 每个表只能有一个主键索引，但主键可以由多个列组成，这称为复合主键。

唯一性索引（Unique Index）：

1. 唯一性： 唯一性索引要求所有的值都是唯一的，但可以有一个NULL值。这意味着表中可以有多行包含NULL值。
2. 多个索引： 一个表可以有多个唯一性索引，每个索引都可以包含一个或多个列。
3. 物理存储顺序： 唯一性索引在InnoDB存储引擎中是一个辅助索引（Secondary Index），数据行的物理存储顺序与唯一性索引无关。

总结：
主键索引和唯一性索引都用于确保数据的唯一性，但主键索引更严格，要求唯一性和非空性，并且在 InnoDB 中通常作为聚簇索引。唯一性索引可以允许一个NULL值，而且在InnoDB中是辅助索引。选择使用哪种索引取决于数据需求和业务逻辑。

5.9 SQL会命中联合索引的所有列吗

参考1：MySQL-联合索引

两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。

对于复合索引：MySQL从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

5.10 如果SQL命中联合索引的a字段，在查到数据后是直接返回还是回表

来自GPT3.5的回答。

当 SQL 命中了联合索引的前缀列（例如，a），在查找到数据后，MySQL 可能会根据需要执行以下两种不同的操作：

1. 覆盖索引扫描（Covering Index Scan）： 如果查询所需的数据都可以从索引本身获取，而不需要额外的表访问，那么称之为覆盖索引扫描。在这种情况下，MySQL 可以直接从索引中获取所需数据，并且不需要回表（即不需要访问原始数据表）。这可以显著提高查询性能，因为减少了对表的额外访问。
2. 回表（Table Lookup）： 如果查询所需的数据不仅包含在索引中，还包含在数据表中其他列中，那么 MySQL 需要执行回表操作。在回表时，MySQL 使用从索引中获取的主键值去表中检索相应的数据。这会增加额外的 I/O 操作和访问开销，可能会降低查询性能。

总结：
是否会直接返回或者执行回表取决于具体情况，主要取决于查询所需的数据是否能够通过索引本身获取（即覆盖索引扫描）。设计合适的联合索引和查询方式可以最大程度地减少回表操作，从而提高查询性能。

5.11 MySQL中索引有个前缀匹配原则，为什么是前缀匹配原则

参考1：mysql最左原则,最左前缀匹配原则
参考2：MySQL：最左前缀匹配原则
MySQL只有联合索引（复合索引）才会使用最左匹配原则。

最左匹配原则的成因：

1. Mysql创建联合索引是首先会对最左边，也就是第一个索引字段进行排序。
2. 在第一个排序的基础上，再对第二个索引字段进行排序，其实就像是实现了Order by字段1，再Order by 字段2这样一种排序规则。
3. 所以第一个字段是绝对有序的，而第二个字段就是无序的了。
4. 因此通常情况下，直接使用第二个字段进行条件判断是用不到索引的。这就是为什么mysql要强调最左匹配原则的成因。

---

面试官回答：索引是按a，b，c三个字段排序的，跳过一个就没有顺序了，所以就不能再走后面的索引了。

5.12 a，b，c三个字段是一个复合索引，where条件中有a= AND c= 能做索引吗？

参考：mysql 复合索引(联合索引) a b c的使用

是否用到索引，与查询条件中这三个列的出现顺序无关，而是与查询条件描述的索引key范围有关。含a的都可以用到索引，优化器自动调整顺序，不含a的用不到索引。

在用到 where 条件为：

1. a && b && c：用到复合索引。
2. a && b：用到复合索引。
3. a && c：用到复合索引。
4. b && c：不会用到索引。

最左原则：（来自GPT3.5的回答）
复合索引的最左匹配规则指的是，当在WHERE条件中使用复合索引的多个列时，数据库会优先利用索引的最左边的列开始匹配，然后根据查询条件从左往右依次逐个列进行匹配。这个规则影响了索引的选择和查询优化。

复合索引的最左匹配规则有以下几个要点：

1. 最左前缀匹配： 数据库系统会首先尝试使用复合索引的最左边的列来匹配查询条件。如果满足查询条件，将继续检查下一个列。
2. 范围查询和等值查询： 复合索引可以在范围查询和等值查询中使用最左匹配。例如，索引 (a, b, c) 可以支持 WHERE a = ?、WHERE a = ? AND b = ?，以及 WHERE a = ? AND b = ? AND c = ? 这样的查询。
3. 不适用于中间列： 如果查询条件没有涉及到复合索引的左边列，那么索引很可能无法被有效利用。例如，索引 (a, b, c) 无法直接支持 WHERE b = ? 的查询。
4. 优化器决策： MySQL 优化器会根据查询条件、索引的选择性和统计信息等，决定是否使用复合索引以及如何使用。
5. 覆盖索引： 在某些情况下，复合索引的最左匹配规则还可以实现覆盖索引扫描，即从索引中获取所需数据，而不需要回表。

最左匹配规则对于索引的设计和查询性能优化非常重要。在设计复合索引时，要根据查询模式和使用频率选择合适的列顺序，以便让索引可以在查询中被最大程度地利用。

5.13 where条件中a= AND c= 走索引了吗？

答：走了，看5.12。

（略）5.14 SELECT * FROM table where条件中a= AND c= 的执行顺序？

答：使用到了回表，根据查询条件先查询主键，然后再使用主键做条件去查询具体的数据。

（略）5.15 继续追问5.14 主键key是怎么查询出来的？

不知道，未找到。

（略）5.16 继续追问5.15 where条件中c= 是在哪一步过滤掉的？

面试官的回答：是在第一步把c=的条件过滤掉后，采取做回表的。

5.17 如何对数据表里的身份证号建立索引？

那比如说我现在有一张表，存储了我们所有用户的数据，按只有杭州用户的数据，那大概就两三千多数据吧。那现在我的索引是基于我的数据，假如是基于身份证号，那现在身份证号又比较长了，又字符串，然后整个索引就很大，那我要优化这个所有方法。怎么去优化这个索引？

1. 选择索引类型：身份证号一般是一个较长的字符串，选择适当的索引类型很重要。如果需要唯一性，可以使用UNIQUE INDEX，如果只是为了提高查询速度，可以使用普通的INDEX。
2. 考虑查询模式：如果身份证号经常用于查询，排序或者连接操作，建立索引可能是一个好的选择。然而，过多的索引也可能带来性能问题，因此需要根据具体查询模式进行权衡。
3. 谨慎使用唯一索引：唯一索引会强制字段的唯一性，插入和更新时需要进行唯一性检查，因此在高并发的写入场景下，可能会对性能产生一定的影响。

6 MySQL的主从复制

参考1：MySQL的主从复制
参考2：MySQL主从复制

6.1 为什么需要主从复制？

来自GPT3.5的回答。

MySQL主从复制（Master-Slave Replication）是一种数据库架构，其中一个数据库服务器（主服务器）上的数据会被异步复制到一个或多个其他数据库服务器（从服务器）。主从复制在许多情况下都是非常有用的，以下是一些主要的原因和优势：

1. 读写分离： 主从复制可以实现读写分离，即将读操作和写操作分散到不同的服务器上，从而提高数据库的读取性能。主服务器负责写操作，而从服务器负责读操作，从而减轻主服务器的负担。
2. 高可用性： 如果主服务器发生故障或停机，从服务器仍然可以继续提供读取服务，从而提高了系统的可用性。从服务器可以被快速提升为新的主服务器，实现快速故障恢复。
3. 备份和恢复： 从服务器可以用于备份数据。备份可以在不影响主服务器性能的情况下进行。如果主服务器数据丢失或损坏，可以从从服务器上进行数据恢复。
4. 分担主服务器压力： 主服务器通常会承受更多的写操作，而从服务器负责读操作，这可以减轻主服务器的压力，提高整体系统性能。
5. 地理位置分布： 主从复制可以支持在不同地理位置部署从服务器，从而提供更好的用户体验，减少跨地理位置的延迟。
6. 负载均衡： 通过在从服务器之间进行负载均衡，可以进一步优化读操作的性能。
7. 数据分析和报表： 从服务器可以用于数据分析、报表生成等任务，而不会干扰主服务器的正常运行。

需要注意的是，主从复制也有一些潜在的问题和挑战，例如复制延迟、数据一致性等。在设计和实施主从复制架构时，需要仔细考虑数据的一致性、安全性和性能需求。

6.2 MySQL主从复制模式

参考1：【数据库】MySQL的主从复制模式

MySQL的主从复制模式包括异步复制，全同步复制，半同步复制。MySQL默认为异步复制。

1. 异步复制：MySQL的默认复制方案，主库在执行完客户端提交的事务后会立刻将执行结果返回给客户端，并不关心从库是否已经接收处理，这样带来的问题就是当主库死掉了，此时主库上提交的事务可能还没有传到从库上。而强行将从库提升为主库就会导致新主上的数据不完整。
2. 全同步复制：当主库执行完一个事务，所有的从库都执行了该事务才会将结果返回给客户端。这样保证了数据的安全性，但是因为需要等待所有从库执行完该事务才能返回客户端结果，所以全同步复制的性能必然会受到很大的影响。

对于全同步复制而言，当主库提交一个事务后，要求所有从库节点必须收到，执行并提交这些事务，然后主库线程才能继续做后续操作，而因此带来的问题就是主库完成一个事务的时间被大幅度拉长，性能降低。
3. 半同步复制：介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时也会造成一定程度的延迟，这个延迟为一个TCP/IP往返的时间。所以半同步复制需要在低延时的网络中使用。

对于半同步复制而言，是介于同步复制和异步复制之间的一种，主库需要等待至少一个从库节点收到并且刷新bin log到relay log中，主库不需要等待所有从库给主库反馈，同时这里只是收到反馈而不是和完全执行并且提交事务的反馈，这样会节省很多的时间。

6.3 MySQL主从复制原理

MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点，如下图所示：

主节点log dump线程作用
当从节点连接主节点时，主节点会创建一个log dump线程，用于发送bin log的内容。在读取bin log操作时，此线程会对主节点上的bin log加锁，当读取完成，甚至在发送给从节点之前，锁都不会被释放。

从节点I/O线程作用
当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin log。I/O线程接收到主节点log dump线程发来的更新之后，保存在本地relay log中。

从节点SQL线程作用
SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

三个线程：
对于每一个主从连接，都需要三个线程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个log dump线程，而每个从节点都有自己的I/O线程、SQL线程。

从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时I/O线程可以很快从主节点获取更新，尽管SQL线程还没有执行。如果在SQL线程执行之前从节点服务停止，至少I/O线程已经从主节点拉取到了最新的变更并且保存在本地relay log日志中，当服务再次起来之后，就可以完成数据的同步。

6.4 MySQL主从复制的过程

要实现主从复制，首先必须打开主库的binary log（bin log）功能，否则无法实现。因为整个复制过程实际上就是从库从主库获取该日志，然后再在自己身上完全顺序的执行日志中所记录的各种操作。

1. 从库通过手工执行change master to语句连接主库，提供了连接主库的相关信息（user 、password、port、ip）。
2. 从库的IO线程和主库的dump线程建立连接。
3. 从库根据change master to语句提供的file名和position号，IO线程向主库发起bin log的请求，请求从主库的指定bin log日志文件的指定位置(或者从最开始位置)开始同步日志内容。
4. 主库dump线程根据从库的请求，将本地指定文件名称中指定位置之后bin log内容发给从库IO线程。发送信息中除了日志信息之外，还包括本次读取的bin log file文件名称以及bin log position结束位置；
5. 从库IO线程接收主库发送的bin log events，并存放到本地relay log中的末尾位置，传送过来的信息，会记录到master.info中，以便在下一次读取的时候能够清楚的告诉主库我需要从某个bin log的哪个位置开始往后的日志内容，请发给我”；
6. 从库SQL线程读取relay log，并且把读取过的记录到relay-log.info中，默认情况下，已经应用过的relay会自动被清理。

6.5 MySQL的复制方式

MySQL 主从复制有三种方式：基于SQL语句的复制（statement-based replication，SBR），基于行的复制（row-based replication，RBR)，混合模式复制（mixed-based replication,MBR)。

对应的bin log文件的格式也有三种：STATEMENT、ROW、MIXED。

1. 基于SQL语句的复制（statement-based replication，SBR）：主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。

• 优点：是只需要记录会修改数据的sql语句到bin log中，减少了bin log日质量，节约I/O，提高性能。
• 缺点：是在某些情况下，会导致主从节点中数据不一致（比如sleep(),now()等）。

2. 基于行的复制（row-based replication，RBR)：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。也就是只记录哪条数据被修改了，修改成什么样。

• 优点：是不会出现某些特定情况下的存储过程、或者函数、或者trigger的调用或者触发无法被正确复制的问题。
• 缺点：是会产生大量的日志，尤其是修改table的时候会让日志暴增,同时增加bin log同步时间。也不能通过bin log解析获取执行过的sql语句，只能看到发生的data变更。

3. 混合模式复制（mixed-based replication,MBR)：默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。MySQL会根据执行的SQL语句选择日志保存方式。

7 SQL语句的执行原理

参考1：SQL语句的执行原理

1. 客户端把语句发给服务器端执行：客户端是不会做任何的操作，它的主要任务就是把客户端产生的一些SQL语句发送给服务器端。服务器上的数据库进程才会对SQL语句进行相关的处理。
2. 查询高速缓存(library cache)：服务器进程在接到客户端传送过来的SQL语句时，不会直接去数据库查询。而是会先在数据库的高速缓存中去查找，是否存在相同语句的执行计划。如果在数据高速缓存中存在，则服务器进程就会直接执行这个SQL语句，省去后续的工作。
3. SQL语句语法检查(data dict cache)：主要是对SQL语句的语法进行检查，看看其是否合乎语法规则。如果服务器进程认为这条SQL语句不符合语法规则的时候，就会把这个错误信息反馈给客户端。
4. SQL语句含义检查(data dict cache)：若SQL语句符合语法上的定义的话，则服务器进程接下去会对语句中的字段、表等内容进行检查。看看这些字段、表是否在数据库中。如果表名与列名不准确的话，则数据库会就会反馈错误信息给客户端。
5. 获得对象解析锁(control structer)：当语法、语义都正确后，系统就会对我们需要查询的对象加锁。这主要是为了保障数据的一致性，防止我们在查询的过程中，其他用户对这个对象的结构发生改变。
6. 数据访问权限的核对(data dict cache)：当语法、语义通过检查之后，客户端还不一定能够取得数据。服务器进程还会检查，所连接的用户是否有这个数据访问的权限。若连接上服务器的用户不具有数据访问权限的话，则客户端就不能够取得这些数据。
7. 确定最佳执行计划 ：当语句与语法都没有问题,权限也匹配的话,服务器进程还是不会直接对数据库文件进行查询。服务器进程会根据一定的规则，对这条语句进行优化。
8. 执行SQL语句：等到语句解析完成之后,数据库服务器进程才会真正的执行这条 SQL 语句。这个语句执行也分两种情况。

• 若被选择行所在的数据块已经被读取到数据缓冲区的话，则服务器进程会直接把这个数据传递给客户端，而不是从数据库文件中去查询数据。
• 若数据不在缓冲区中，则服务器进程将从数据库文件中查询相关数据，并把这些数据放入到数据缓冲区中(buffer cache)。

9. 提取数据：当语句执行完成之后，查询到的数据还是在服务器进程中，还没有被传送到客户端的用户进程。所以，在服务器端的进程中，有一个专门负责数据提取的一段代码。他的作用就是把查询到的数据结果返回给用户端进程，从而完成整个查询动作。

8 MySQL各存储引擎及对应的底层数据结构

1. InnoDB存储引擎：

• InnoDB是MySQL的默认存储引擎，它支持事务处理（ACID兼容）、行级锁定和外键约束。
• 它使用B+树索引结构来存储数据，包括主索引和辅助索引。
• 数据存储在聚集索引（主键索引）下，这使得InnoDB在处理大量随机读写操作时非常高效。

2. MyISAM存储引擎：

• MyISAM是另一种常见的MySQL存储引擎，它不支持事务处理和行级锁定。
• MyISAM使用B树索引结构，但它的数据存储方式不同于InnoDB。它将数据和索引分开存储，因此在一些读密集型应用中可能性能较好。

3. MEMORY存储引擎：

• MEMORY存储引擎将数据存储在内存中，对于需要快速读取的临时数据非常有用。
• 它使用哈希索引来加快数据检索速度，但它不支持持久化数据，因此在服务器重启时数据会丢失。

4. NDB Cluster存储引擎（也称为NDB存储引擎）：

• NDB Cluster存储引擎用于MySQL集群，支持高可用性和分布式数据存储。
• 它使用哈希索引，并且数据存储在内存中，以实现高速的数据访问。
• NDB存储引擎支持分片和分布式事务处理。

5. TokuDB存储引擎：

• TokuDB存储引擎是一个用于大容量数据的高性能存储引擎。
• 它使用了Fractal树索引结构，这是一种高效的索引结构，特别适用于写密集型工作负载。

6. CSV存储引擎：

• CSV存储引擎用于将数据存储为纯文本的逗号分隔值（CSV）文件。
• 数据以文本形式存储，不支持索引和事务。

每个存储引擎都有其独特的优势和限制，选择适合特定应用需求的存储引擎非常重要。数据库管理员和开发人员通常会根据他们的应用要求来选择合适的存储引擎。

9 MySQL的回表操作

参考1：mysql

回表操作是指在使用索引进行查询时，如果需要获取索引列之外的其他列的数据，数据库引擎需要通过索引找到对应的行，然后再根据行的主键ID获取其他列的数据。这个过程就称为回表操作。

所以回表的产生也是需要一定条件的，如果一次索引查询就能获得所有所需的查询列记录就不需要回表，如果查询列中有其他的非索引列，就会发生回表动作。即基于非主键索引的查询需要多扫描一棵索引树。

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Go）

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
级锁定和外键约束。

• 它使用B+树索引结构来存储数据，包括主索引和辅助索引。
• 数据存储在聚集索引（主键索引）下，这使得InnoDB在处理大量随机读写操作时非常高效。

2. MyISAM存储引擎：

• MyISAM是另一种常见的MySQL存储引擎，它不支持事务处理和行级锁定。
• MyISAM使用B树索引结构，但它的数据存储方式不同于InnoDB。它将数据和索引分开存储，因此在一些读密集型应用中可能性能较好。

3. MEMORY存储引擎：

• MEMORY存储引擎将数据存储在内存中，对于需要快速读取的临时数据非常有用。
• 它使用哈希索引来加快数据检索速度，但它不支持持久化数据，因此在服务器重启时数据会丢失。

4. NDB Cluster存储引擎（也称为NDB存储引擎）：

• NDB Cluster存储引擎用于MySQL集群，支持高可用性和分布式数据存储。
• 它使用哈希索引，并且数据存储在内存中，以实现高速的数据访问。
• NDB存储引擎支持分片和分布式事务处理。

5. TokuDB存储引擎：

• TokuDB存储引擎是一个用于大容量数据的高性能存储引擎。
• 它使用了Fractal树索引结构，这是一种高效的索引结构，特别适用于写密集型工作负载。

6. CSV存储引擎：

• CSV存储引擎用于将数据存储为纯文本的逗号分隔值（CSV）文件。
• 数据以文本形式存储，不支持索引和事务。

每个存储引擎都有其独特的优势和限制，选择适合特定应用需求的存储引擎非常重要。数据库管理员和开发人员通常会根据他们的应用要求来选择合适的存储引擎。

9 MySQL的回表操作

参考1：mysql

回表操作是指在使用索引进行查询时，如果需要获取索引列之外的其他列的数据，数据库引擎需要通过索引找到对应的行，然后再根据行的主键ID获取其他列的数据。这个过程就称为回表操作。

所以回表的产生也是需要一定条件的，如果一次索引查询就能获得所有所需的查询列记录就不需要回表，如果查询列中有其他的非索引列，就会发生回表动作。即基于非主键索引的查询需要多扫描一棵索引树。

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Go）
[外链图片转存中…(img-frBudlul-1713146941246)]

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！