Mysql面试题

⚫ 熟悉Mysql底层存储结构，索引，事务，mvcc工作原理，学习过sql调优及分库分表优化；

事务的四大特性和隔离级别

原子性（Atomicity）

原子性确保事务中的所有操作要么全部完成，要么全部不完成。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。事务是一个不可分割的最小工作单元。

例子：假设有一个银行转账操作，事务包括从账户A中扣钱和向账户B中加钱。如果任何一个操作失败，整个事务都会回滚，账户A和账户B的余额不会发生变化。

一致性（Consistency）

一致性确保事务在完成后，数据库从一个一致状态转换到另一个一致状态。事务开始前和结束后，数据库的完整性约束没有被破坏。

例子：在银行转账操作中，一致性确保转账后总金额保持不变。如果账户A减少了100元，账户B就应该增加100元。

隔离性（Isolation）

隔离性确保并发执行的事务彼此隔离开来，避免相互干扰。不同的事务之间的操作是互不可见的，直到事务提交。

例子：还是上面的银行转账操作，a 正在银行扣 100 块的时候，b 不能往里面加 100 块。

隔离级别：

1. 读未提交（Read Uncommitted）：一个事务可以读到另一个未提交事务的数据，可能导致脏读。

脏读：一个事务读取了另一个事务未提交的数据。如果该事务回滚，那么读到的数据将是无效的。

例如：张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。与此同时，事务B正在读取张三的工资，读取到张三的工资为8000。随后，事务A发生异常，而回滚了事务。张三的工资又回滚为5000。最后，事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。

1. 读已提交（Read Committed）：一个事务只能读到已提交事务的数据，避免脏读,但可能会出现不可重复读。

不可重复读：一个事务在读取同一行数据时，可能因为另一个事务的提交而得到不同的结果。

例如：在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。与此同时，事务B把张三的工资改为8000，并提交了事务。随后，在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

1. 可重复读（Repeatable Read）：一个事务在整个过程中看到的数据是一致的，避免不可重复读，但可能会出现幻读。

幻读：一个事务在读取某个范围内的行时，另一个事务在该范围内插入了新的行，导致前一个事务再次读取时发现了“幻影”行。

例如：目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。此时，事务B插入一条工资也为5000的记录。这是，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。

不可重复读的重点是修改：同样的条件，你读取过的数据，再次读取出来发现值不一样了。

幻读的重点在于新增或者删除：同样的条件，第 1 次和第 2 次读出来的记录数不一样。

1. 串行化（Serializable）：最高隔离级别，事务完全串行化执行，避免幻读，代价是并发性大大降低，事务可能会因为锁等待而阻塞。

隔离级别	脏读	不可重复读	幻读	原理
Read uncommitted (读未提交)	存在	存在	存在	直接读取数据，不处理并发问题
Read committed (读已提交)	×	存在	存在	读操作不加锁，写操作加排他锁
Repeatable read (可重复读)	×	×	存在	MVCC实现，事务开始时创建ReadView，之后事务的其他查询都用这个ReadView
Serializable (串行化)	×	×	×	使用锁，读加共享锁，写加排他锁，串行执行

持久性（Durability）

持久性确保事务一旦提交，其结果是永久性的，即使系统发生故障，数据也不会丢失。

例子：在银行转账操作中，一旦事务提交，转账操作的结果（账户A和账户B的余额变化）将永久保存在数据库中，即使之后系统崩溃，数据也不会丢失。

MyISAM与InnoDB区别及选择

数据存储结构

MyISAM 将数据存储在三个文件中（.frm文件存储表结构，.MYD文件存储数据，.MYI文件存储索引）；

InnoDB 将数据和索引存储在表空间文件中，支持自动扩展和多表空间。（.frm文件存储表结构定义，.ibd存储数据和索引）

由于MyISAM的数据和索引是分开存储的，所以查询的时候，MyISAM的叶子节点存储的是数据所在的地址，而不是直接存储数据。

事务支持

MyISAM 不支持事务，无法进行回滚和提交操作

InnoDB 支持事务，提供 ACID 特性（原子性、一致性、隔离性、持久性）。

锁机制

MyISAM 使用表级锁，每次操作都会锁定整个表，适合读多写少的应用

InnoDB 使用行级锁，每次操作只锁定相关的行，适合高并发和写操作频繁的应用。

外键支持

MyISAM 不支持外键，无法在表间建立引用完整性约束

InnoDB 支持外键，可以在表间建立引用完整性约束，保证数据的一致性和完整性。

全文索引

MyISAM 原生支持全文索引，适合需要全文搜索的应用

InnoDB 从 MySQL 5.6 开始支持全文索引，但性能和功能上仍不如 MyISAM。

存储空间

MyISAM 存储空间效率较高，数据文件较小

InnoDB 可能占用更多存储空间，特别是需要存储事务日志和外键约束信息。

表的大小限制

MyISAM 受文件系统限制，单个表最大可达 256TB（取决于文件系统）

InnoDB 最大表大小可达 64TB，适合处理大规模数据集。

如何选择

1、 需要事务支持必选InnoDB，不需要事务选择MyISAM

2、如果表的大部分操作都是查询，数据不大，并发不大，可以考虑选择MyISAM，但是一般还是选择 innodb，有写又有读则选择InnoDB

3、如果系统奔溃导致数据难以恢复，且成本高，选择 innodb

自适应hash索引

自适应哈希索引是InnoDB存储引擎的一项独特性能优化特性，旨在针对高频访问的热点数据提升查询效率。与传统的静态哈希索引不同，自适应哈希索引采纳了一种自动化、动态调整的机制：InnoDB自主监控查询模式及数据访问频次，据此智能决策哈希索引的创建与否，并能依据数据集变动及查询模式的演进灵活地调整索引结构，无需人工介入。
其运作原理如下：当InnoDB检测到特定索引键值频繁遭受等值查询操作，系统便会在内存中自发为这些热点键值生成哈希索引，以此来加速后续的等值查询响应时间。这一系列优化行为均在后台自动执行，无需用户的直接参与或配置。

优点

自动优化：自适应哈希索引会自己悄悄地建立和打理，你不需要专门去做什么或者管它。
性能提升：在找一些特定相同值的时候，这个自适应哈希索引能大大加快寻找速度，它的查找速度超级快，差不多是瞬间完成的（专业点说就是查询时间是O(1)）。
省资源：它还减少了去二级索引树那里找信息的次数，这样就节约了系统资源。

缺点

内存消耗： 自适应哈希索引完全在内存中构建，需要足够的内存资源。

不可预测性：由于是基于运行时查询模式的，所以哈希索引的存在和组成是不可预测的。

不适用于所有查询：自适应哈希索引主要优化等值查询，对于范围查询或排序操作没有帮助。

hash自适应索引会占用innodb buffer pool；

总的来说，自适应哈希索引是 InnoDB 存储引擎为了提高特定类型查询性能而自动构建的一种内存中的哈希索引结构。它可以根据查询模式和数据访问频率自动调整，以优化数据库的性能。

Mysql使用规范

数据库设计规范

范式化设计：根据业务需求，合理选择符合一定范式（如1NF、2NF、3NF或BCNF）进行表结构设计，减少数据冗余，保证数据的一致性。

表命名规范：采用有意义的、区分大小写的表名，通常使用小写字母，并用下划线_分隔单词，如user_info。

字段命名规范：同样遵循有意义的命名原则，使用小写字母加下划线，避免使用数据库保留字。

索引设计规范

适度索引：为频繁查询和参与JOIN操作的字段创建索引，但需注意过多的索引会影响插入、删除和更新操作的性能。

索引类型选择：根据数据特性和查询需求选择合适的索引类型，如BTREE索引适用于范围查询，哈希索引适合等值查询。

复合索引：合理安排复合索引中字段的顺序，一般将区分度高的字段放在前面以提高索引利用率。

SQL编写规范

避免SELECT *：明确指定需要查询的字段，减少不必要的数据传输量。

使用参数化查询：防止SQL注入攻击，提高代码安全性和可读性。

事务处理：对相关操作使用事务管理，确保数据一致性，尤其是在涉及多表操作时。

数据类型选择

根据实际存储需求选择最合适的的数据类型，避免过度使用大容量类型，比如合理使用INT而非BIGINT，使用VARCHAR并限制长度而非无限制的TEXT。

性能优化

定期分析和优化表：使用ANALYZE TABLE和OPTIMIZE TABLE命令来分析表的状态并进行必要的优化。

查询优化：利用EXPLAIN分析查询计划，优化复杂的查询语句，减少子查询的使用，适当情况下使用连接（JOIN）代替。

定期进行数据库备份，包括全备和增量备份，确保在数据丢失或损坏时能够快速恢复。

安全管理

最小权限原则：为不同用户或应用分配仅满足其功能所需的最小权限。

加密敏感数据：对敏感信息如密码进行加密存储，不直接保存明文。

监控与日志

启用并监控MySQL的日志系统（如错误日志、慢查询日志），及时发现并解决问题。
遵循这些规范，可以帮助开发团队构建更加健壮、高效、易于维护的MySQL数据库应用系统。

建立索引需要考虑的因素

选择合适的列

并非所有列都适合创建索引。通常，那些频繁用于查询条件（如WHERE子句）、JOIN操作或者ORDER BY、GROUP BY语句中的列是建立索引的首选。同时，考虑到索引维护的成本，应避免在具有高更新频率或大量重复值的列上建立索引。

索引覆盖

如果一个查询的所有需要的数据都能从索引中直接获取而无需回表查询（即索引覆盖查询），那么这样的查询将更加高效。因此，在设计索引时，考虑是否能通过包含额外的列来实现索引覆盖。

索引类型

根据数据特性和查询需求选择合适的索引类型。例如，B-Tree索引是最常见的，适用于范围查询和等于操作；哈希索引适合等值查询；位图索引在数据只有几种固定值时效率较高；而全文索引则用于文本的全文搜索。

复合索引

当查询涉及多个列时，可以考虑创建复合索引（即包含多个列的索引）。合理安排复合索引中列的顺序至关重要，一般将区分度高的列放在前面以提高筛选效率。

索引的稀疏性

索引应该尽可能地“瘦”，即只包含对查询有帮助的列，避免不必要的数据冗余，减少索引的大小和维护成本。

系统资源与性能影响

创建和维护索引会占用磁盘空间，并可能影响数据插入、删除和更新的性能。需要权衡索引带来的查询加速与资源消耗之间的平衡，尤其是在数据量大、写操作频繁的场景下。索引会占用额外的存储空间，特别是对大表和多列索引，需要权衡存储成本。

评估和优化

随着业务需求和数据量的变化，定期评估现有索引的有效性，删除不再使用的索引，添加新的索引。使用数据库的监控工具（如 MySQL 的慢查询日志、性能模式等）监控索引的使用情况和性能。

数据库的三范式

数据库设计中的三范式（3NF）是关系数据库理论中的一种规范化标准，用于减少数据冗余和提高数据一致性。

第一范式 (1NF)

第一范式要求每个表中的字段值都是原子的，即每个字段只能包含一个值，不能包含重复的组或数组。

1. 每个列中的值必须是不可分割的原子值。
2. 表中的每一行必须是唯一的，即每一行都应该有一个唯一的标识符（通常是主键）。

第二范式 (2NF)

第二范式在满足第一范式的基础上，要求表中的每个非主键字段必须完全依赖于主键，消除部分依赖。

1. 表必须满足第一范式。
2. 非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分（如果主键是复合主键）。

第三范式 (3NF)

第三范式在满足第二范式的基础上，要求表中的非主键字段之间不能有传递依赖。

1. 表必须满足第二范式。
2. 非主键字段之间不能存在传递依赖，即非主键字段必须直接依赖于主键，而不能通过其他非主键字段间接依赖于主键。

Mysql的explain有哪些列

EXPLAIN命令用于分析 SQL 查询的执行计划，帮助优化查询性能。通过 explain 命令获取 select 语句的执行计划，通过 explain 我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。

idM

查询的标识符。一个查询中的每个子查询或联合查询都会有一个唯一的id。id相同时，执行顺序由上至下。如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。主要用于区分查询中的不同部分。

select_type

查询的类型，描述 SELECT 的类型。

常见值：

>SIMPLE：简单的 SELECT 查询，不包含子查询或 UNION。
>PRIMARY：最外层的 SELECT。
>UNION：UNION 中的第二个或后续的 SELECT 查询。
>DEPENDENT UNION：UNION 中的第二个或后续的 SELECT 查询，依赖于外部查询。
>UNION RESULT：UNION 的结果。
>SUBQUERY：子查询中的第一个 SELECT。
>DEPENDENT SUBQUERY：子查询，依赖于外部查询。
>DERIVED：派生表（子查询的结果作为临时表）。

当通过union来连接多个查询结果时，第二个之后的select其select_type为UNION。

当union作为子查询时，其中第二个union的select_type就是DEPENDENT UNION。第一个子查询的select_type则是DEPENDENT SUBQUERY。

table

这一列表示 explain的这一行正在访问哪个表。

1）当 from 子句中有子查询时，如果table列是 <derivenN> 格式，则表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。

2）当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id。

partitions

如果该查询是基于分区表的查询，partitions字段会显示查询所访问的分区。

type(重点)

这一列表示关联类型或访问类型，即，Mysql决定通过哪种方式查找数据表中的数据。

从优到最差分别为：system > const > eq_ref > ref > range > index > ALL

一般来说，至少需要保证查询达到range级别，最好达到ref级别。

类型	解释
system	表只有一行（等于系统表）
const	表最多有一行匹配
eq_ref	对于每个来自前一个表的行，最多一行与之匹配，比如一个订单表的对应另一张订单信息扩充表，扩充表的记录和订单表肯定是 1 对 1 的，就是 eq
ref	对于每个来自前一个表的行，可能有多行与之匹配，如果上面的例子是一对多，那么就是 ref
range	使用索引查找指定范围的行
index	全索引扫描，扫描索引树
ALL	全表扫描

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被使用（该查询可以利用的索引，如果没有任何索引显示 null）

key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

可能出现这种情况，possible_keys有显示列，而key显示NULL的情况，这种情况是因为表中数据不多，Mysql优化器认为查询时走索引对此查询语句帮助不大，从而优化器会选择全表扫描（扫描聚簇索引），而不是走索引来查询。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

显示MySQL认为它执行查询时检查的行数。多行之间的同组数据相乘可以估算要处理的行数，不同组的相加

filtered

该列是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

Extra

附加信息。

Using index：查询使用了覆盖索引（索引包含所有需要的数据）。

Using where：使用了WHERE子句进行过滤。

Using temporary：使用了临时表。

Using filesort：使用了文件排序。

例子

假设我们有一个电子商务数据库，包含以下两个表：

1. orders表：

>CREATE TABLE orders (
   order_id INT PRIMARY KEY,
   customer_id INT,
   order_date DATE,
   total_amount DECIMAL(10, 2)
);

1. customers表：

>CREATE TABLE customers (
   customer_id INT PRIMARY KEY,
   customer_name VARCHAR(100),
   email VARCHAR(100)
>);

我们有一个查询，想要获取在特定日期范围内下单的客户信息：

>SELECT c.customer_name, c.email, o.order_date, o.total_amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31';

使用 EXPLAIN 分析查询

执行EXPLAIN命令来分析这个查询：

>EXPLAIN SELECT c.customer_name, c.email, o.order_date, o.total_amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31';

EXPLAIN 输出示例

假设EXPLAIN返回以下结果：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	o	NULL	ALL	NULL	NULL	NULL	NULL	10000	10.00	Using where
1	SIMPLE	c	NULL	eq_ref	PRIMARY	PRIMARY	4	e_commerce.o.customer_id	1	100.00	NULL

分析 EXPLAIN 输出

orders 表扫描（o 表）

type: ALL表示全表扫描，这意味着 MySQL 需要扫描orders表的所有行。

possible_keys:NULL表示没有可用的索引。

rows: 10000 表示 MySQL 估计需要扫描 10,000 行。

Extra:Using where表示 MySQL 使用了WHERE子句来过滤行。

customers 表扫描（c 表）

type:eq_ref表示对customer_id列的精确查找。

key:PRIMARY表示使用了customers表的主键索引。

rows: 1 表示每次查找只返回一行。

优化建议

为orders表的order_date列创建索引：

>CREATE INDEX idx_order_date ON orders(order_date);

再次运行 EXPLAIN查看效果：

>EXPLAIN SELECT c.customer_name, c.email, o.order_date, o.total_amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31';

优化后的 EXPLAIN 输出示例

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	o	NULL	range	idx_order_date	idx_order_date	3	NULL	1000	100.00	Using index
1	SIMPLE	c	NULL	eq_ref	PRIMARY	PRIMARY	4	e_commerce.o.customer_id	1	100.00	NULL

结果分析

orders 表扫描（o 表）

type:range表示使用索引扫描特定范围的行。

key:idx_order_date表示使用了新创建的索引。

rows: 1000 表示 MySQL 估计需要扫描 1,000 行，而不是之前的 10,000 行。

通过创建索引，查询性能得到了显著提升，因为 MySQL 不再需要对orders表进行全表扫描，而是使用索引快速定位符合条件的行。

Mysql语句种类

数据定义语言 (DDL)

DDL 语句用于定义和管理数据库结构，例如创建、修改和删除数据库和表。

CREATE：创建数据库、表、索引等。

ALTER：修改现有数据库对象的结构。

DROP：删除数据库、表、索引等。

TRUNCATE：清空表中的所有数据，但保留表结构。

数据操作语言 (DML)

DML 语句用于操作数据库中的数据，例如插入、更新、删除和查询数据。

INSERT：插入数据。

UPDATE：更新数据。

DELETE：删除数据。

SELECT：查询数据。

数据控制语言 (DCL)

DCL 语句用于控制数据库的访问权限。

GRANT：授予用户权限。

REVOKE：撤销用户权限。

事务控制语言 (TCL)

TCL 语句用于管理事务，确保数据的一致性和完整性。

START TRANSACTION：开始一个事务。

COMMIT：提交事务。

ROLLBACK：回滚事务。

SAVEPOINT：设置保存点。

RELEASE SAVEPOINT：释放保存点。

ROLLBACK TO SAVEPOINT：回滚到保存点。

数据查询语言 (DQL)

DQL 主要包括SELECT语句，用于查询数据库中的数据。

SELECT：查询数据。

其他

EXPLAIN：解释查询执行计划。

SHOW：显示数据库对象的信息。

DESCRIBE：显示表的结构。

Mysql查询优化

避免全表扫描

优化查询时，应避免全表扫描，优先在 WHERE 和 ORDER BY 涉及的列上建立索引。

避免 NULL 值判断

避免在 WHERE 子句中对字段进行 NULL 值判断。创建表时，尽量使用 NOT NULL 或使用特殊值（如 0 或 -1）作为默认值。

Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam 中固定大小的索引变成可变大小的索引。

还有几点原因：

1、所有使用NULL值的情况，都可以通过一个有意义的值的表示，这样有利于代码的可读性和可维护性，并能从约束上增强业务数据的规范性。

2、NULL值到非NULL的更新无法做到原地更新，更容易发生索引分裂，从而影响性能。

3、NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回永远为空结果，查询容易出错

避免 != 或 <> 操作符

避免在 WHERE 子句中使用 != 或 <> 操作符。MySQL 仅对 <，<=，=，>，>=，BETWEEN，IN 以及某些情况下的 LIKE 使用索引。

避免 OR 条件

避免在 WHERE 子句中使用 OR 连接条件，因这会导致全表扫描。可以使用 UNION 合并查询：

>SELECT id FROM t WHERE num=10 UNION ALL SELECT id FROM t WHERE num=20;

谨慎使用 IN 和 NOT IN

谨慎使用 IN 和 NOT IN，因其可能导致全表扫描。对于连续数值，用 BETWEEN 替代 IN：

>SELECT id FROM t WHERE num BETWEEN 1 AND 3;

LIKE 查询优化

避免使用%abc%或%abc的 LIKE 查询，这会导致全表扫描。可以考虑使用全文检索。只有abc%的 LIKE 查询会使用索引。

避免参数化查询导致的全表扫描

避免在 WHERE 子句中使用参数，这会导致全表扫描。可以强制查询使用索引：

>SELECT id FROM t WITH (INDEX(索引名)) WHERE num=@num;

避免表达式操作

避免在 WHERE 子句中对字段进行函数操作。

不要WHERE YEAR(order_date) = 2024使用 ，而要使用

>WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

使用 EXISTS 替代 IN

使用 EXISTS 替代 IN，可以提高查询效率：

>SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE num=a.num);

Mysql聚集索引

MySQL 中的聚集索引是指数据表的记录按照索引的顺序进行物理存储。也就是说，表中的数据行和索引行一起存储，并且数据行的顺序与索引的顺序相同。聚集索引的特点是索引的叶节点包含了实际的数据行。一个表中只能有一个聚集索引，因为只能以一种物理顺序存放。

在 MySQL 中，InnoDB 存储引擎默认使用聚集索引。InnoDB 表必须有一个聚集索引，如果没有显式定义主键，InnoDB 会选择一个唯一的非空索引作为聚集索引。如果没有这样的索引，InnoDB 会自动生成一个名为row_id隐藏的聚集索引。

我们通过InnoDB把数据存放到B+树中，而B+树中的键值就是主键，那么在B+树中的叶子节点存储的就是表中的所有数据（即该主键对应的整行数据），数据文件和索引文件是同一个文件，找到了索引便找到了数据，所以称之为聚集索引。

特点

1. 物理顺序存储：数据行按照索引键的顺序存储在数据页中。
2. 叶节点包含数据：聚集索引的叶节点包含了实际的数据行。
3. 只能有一个：每个数据表只能有一个聚集索引，因为数据行只能按一种顺序存储。

优点

1. 查询效率高：对于基于聚集索引的范围查询（between、<、<=、>、>=）和排序操作（group by、order by），效率较高，因为数据行是按索引顺序存储的。因为聚集索引找到包含第一个值的行后，后续索引值的行在物理上毗连在一起而不必进一步搜索，避免大范围扫描，大大提高查询速度。
2. 覆盖索引：在某些查询中，聚集索引可以充当覆盖索引，从而减少读取数据的次数。

缺点

1. 插入速度较慢：在插入新记录时，可能需要移动大量数据行以保持索引顺序。
2. 更新代价高：更新聚集索引键值时，可能导致数据行的重新排序。移动行可能还会面临页分裂问题（即页已满），存储引擎会将该页分裂成两个页面来容纳，页分裂会占用更多磁盘空间。即索引重排，造成资源浪费。
3. 占用更多存储空间：由于数据行和索引一起存储，可能占用更多的存储空间。

聚集索引不要选择频繁更新的列

数据重排

由于聚集索引的特点是数据行按照索引键的顺序进行物理存储，当聚集索引键的值发生变化时，数据库需要将该行移动到新的位置以保持索引顺序。这种数据重排操作会消耗大量的 I/O 和 CPU 资源，导致性能下降。

页分裂和合并

当新的数据行插入到现有页面中而页面已经满了时，InnoDB 需要进行页分裂（Page Split），将一个页面分成两个页面，以腾出空间存储新数据。这种操作不仅耗时，还会导致磁盘碎片，影响查询性能。频繁更新聚集索引键会增加页分裂的频率。

二级索引的维护

InnoDB 中的二级索引会引用聚集索引的键作为指向数据行的指针。当聚集索引键发生变化时，所有引用该键的二级索引也需要更新。这会增加额外的维护成本，影响整体性能。

锁争用和死锁

频繁更新聚集索引键可能导致更多的锁争用和死锁问题。数据行的移动和页分裂操作需要持有独占锁，这会阻塞其他事务的读写操作，增加系统的锁争用和死锁风险。

Mysql非聚集索引

MySQL 的非聚集索引（Non-Clustered Index），也称为辅助索引或二级索引，是指索引的叶节点不包含实际的数据行，而是包含指向数据行的指针（通常是聚集索引键）。通俗理解，以主键以外的列作为键值构建的B+树索引。非聚集索引的主要作用是加速特定列上的查询操作，而不改变数据行的物理存储顺序。记录的物理顺序与逻辑顺序没有必然的联系。

非聚集索引表数据存储顺序与索引数据无关，叶节点包含索引字段值及指向数据页数据行的逻辑指针（其行数量与数据表数据量相同），所以想要查找数据还需要根据主键再去聚集索引中查找，根据聚集索引查找数据的过程就称为回表。

一个表中可以拥有多个非聚集索引。实际上每个表最多可以建立249个非聚集索引，但是每次给字段建一个新索引，字段中的数据就会被复制出来一份用于生成索引，因此给表添加索引会增加表的体积，占据大量磁盘空间和内存。所以若磁盘空间和内存有限，应限制非聚集索引数量。每当你改变了一个建立非聚集索引的表中数据时，必须同时更新索引，所以非聚集索引会降低插入和更新速度。

特点

1. 独立于数据存储顺序：非聚集索引不会改变数据行的物理存储顺序，数据行的顺序由聚集索引决定。
2. 叶节点包含指针：非聚集索引的叶节点存储的是指向实际数据行的指针（如聚集索引键），而不是数据行本身。
3. 可以有多个：一个表可以有多个非聚集索引，每个非聚集索引加速不同列上的查询操作。

优点

加速查询：非聚集索引可以显著加速特定列上的查询操作，特别是那些经常出现在 WHERE 子句中的列。

多样性：可以在多个列上创建非聚集索引，以满足不同查询需求。

缺点

占用空间：非聚集索引需要额外的存储空间来维护索引结构。

维护成本：在插入、更新和删除操作时，需要维护非聚集索引，可能会增加开销。

Mysql的回表查询

聚集索引叶子结点会存数据，非聚集索引存的是主键值。知道这个前提后，在 MySQL 中，回表查询（也称为回表操作）是指在使用非聚集索引进行查询时，需要从索引中获取行的指针（通常是主键值），然后再根据这些指针访问实际的数据行。这种操作通常发生在查询中需要访问的列不完全包含在索引中的情况下。如果查询的列，包含在索引中，那么就是覆盖索引的概念了。

回表查询的过程

1. 使用非聚簇索引查找：首先，MySQL 使用非聚簇索引查找满足查询条件的索引项。
2. 获取行指针：从非聚簇索引的叶节点获取指向实际数据行的指针（例如主键值）。
3. 访问数据行：根据获取的指针，访问实际的数据行以获取所需的列数据。

回表查询的步骤

1. 使用索引查找：MySQL 使用idx_last_name索引查找last_name为 ‘Smith’ 的索引项。
2. 获取主键值：从索引项中获取对应的emp_id（因为emp_id是主键）。
3. 访问数据行：根据emp_id，访问实际的数据行以获取first_name和hire_date列的数据。

Mysql的覆盖索引

在 MySQL 中，覆盖索引（Covering Index）是指一个索引包含了查询所需的所有列，从而使查询可以完全从索引中获取数据，而不需要访问实际的数据行。这种技术可以显著提高查询性能，因为它减少了对表数据的访问次数。

如果我的非聚集索引，在建立的时候，有一个 name 列，而我们查询的时候，也是 select name 这样就在非聚集索引就可以查到。那就不用回表了。

索引覆盖的好处

1. 减少I/O操作：查询可以直接从索引中读取所需的数据，避免了访问磁盘上的数据行，从而减少了I/O操作。
2. 提高查询性能：由于减少了对数据行的访问，查询速度会更快。
3. 减少回表操作：避免了回表操作，因为所有需要的数据都在索引中。

示例 Demo

假设有一个表employees，并且我们在last_name列上创建了一个非聚簇索引：

>CREATE TABLE employees (
   emp_id INT PRIMARY KEY,
   first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE
) ENGINE=InnoDB;

>CREATE INDEX idx_last_name ON employees(last_name);

现在我们执行一个查询：

>SELECT first_name, hire_date FROM employees WHERE last_name = 'Smith';

在这种情况下，idx_last_name索引只包含last_name列的信息，因此 MySQL 需要通过索引找到满足条件的行指针（例如主键值），然后回表访问实际的数据行以获取first_name和hire_date列的数据。

使用覆盖索引

为了避免回表操作，可以创建一个覆盖索引，包含所有需要的列：

>CREATE INDEX idx_last_name_full ON employees(last_name, first_name, hire_date);

覆盖索引的工作原理

1. 索引查找：MySQL 使用idx_last_name_full索引查找last_name为 ‘Smith’ 的索引项。
2. 直接获取数据：因为索引已经包含了first_name和hire_date列，MySQL 可以直接从索引中获取所有需要的数据，而不需要回表。

适用场景

覆盖索引特别适用于那些经常需要查询少量列而这些列可以包含在一个索引中的场景。

查询经常使用的列，需要高查询性能的读密集型应用。

Mysql前缀索引

前缀索引（Prefix Index）是一种索引类型，用于对字符串类型的列（如CHAR、VARCHAR、TEXT等）进行部分索引。通过索引字符串的前一部分，而不是整个字符串，可以显著减少索引的大小，从而提高查询性能，特别是在处理大量数据时。比如一个字符串有 30 个字符，建立索引的时候，发现取前 10 个字符就可以区分出来，这样就可以减少索引大小，也就是前缀索引。

使用场景

字符串列的长度较长，且前几位字符就能区分大多数记录。

需要减少索引的存储空间。

索引选择性

这里要了解一个概念，索引的选择性（Index Selectivity），是指不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高。

前缀索引的目的其实就是性能和空间中找到一个平衡。假设现在有几条记录。

vceina12312-ceince

vceina1demiwad

dwianfgein-ceicenm

假设我们取前 7 位来建立前缀索引，就得到如下结果：

vceina1

vceina1

dwianfg

也就是有两条记录是相同的，那么 2/3 = 0.666666 这就是选择性得到的值。

那假设扩展到 8 位来建立索引呢。

vceina12

vceina1d

dwianfge

用 8 位我们就可以区分出来，找到一条记录。那相比最上面的数据，我们的空间就变小了。所以前缀索引的选择性，最好要接近于正常数据的量。

可以通过如下公式计算：

全列的数为

>SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

某一长度前缀的选择性

>SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

如果二者计算结果约等于全列选择性的时候，就是最佳结果。

如何创建前缀索引

创建前缀索引时，需要指定要索引的前缀长度。前缀长度应根据实际数据分布和查询需求来确定，一般选择能够有效区分数据的最小长度。

假设有一个包含URL的表websites，其结构如下：

>CREATE TABLE websites (
     id INT AUTO_INCREMENT PRIMARY KEY,
     url VARCHAR(255)
>);

如果我们希望对url列创建前缀索引，可以使用如下的SQL语句：

>CREATE INDEX idx_url_prefix ON websites (url(10));

上述语句创建了一个前缀长度为10的索引，即只对url列的前10个字符进行索引。

前缀索引的优缺点

优点

减少索引大小：前缀索引只索引字符串的前部分字符，显著减少了索引占用的存储空间。

提高性能：在某些情况下，较小的索引可以提高查询性能，特别是在内存有限的情况下。

缺点

精确性降低：前缀索引可能无法完全区分所有记录，特别是前缀部分相同的记录。在这种情况下，可能需要进行额外的行扫描来完成查询。

适用场景有限：前缀索引主要适用于字符串列，对于其他类型的列（如整数、日期等）不适用。

注意事项

选择合适的前缀长度：前缀长度过短可能导致索引选择性差，前缀长度过长则无法充分减少索引大小。需要根据数据分布和查询需求选择合适的前缀长度。

不适用于ORDER BY和GROUP BY：前缀索引在某些查询（如ORDER BY和GROUP BY）中可能无法使用，因为这些操作需要完整的列值。

什么情况下应不建或少建索引

表的数据量较小

对于小表，扫描整个表的速度可能比通过索引查找还要快。在这种情况下，索引带来的性能提升非常有限，甚至可能增加不必要的开销。小表一般不需要额外的索引，除非有特定的查询需要优化。

表的写操作频繁

每次插入、更新或删除操作时，索引也需要更新，这会增加额外的开销。如果表的写操作非常频繁，索引的维护成本可能会超过其带来的查询优化收益。对于写操作频繁的表，应谨慎添加索引，尽量减少不必要的索引。

索引列的基数低

基数低的列是指列中重复值较多的列，如性别（男/女）、布尔值（是/否）等。对于这种列，索引的选择性较差，使用索引进行查询可能不会显著提高性能。

频繁的批量数据加载

在进行批量数据加载（如批量插入或更新）时，索引的维护会显著增加操作时间。如果数据加载频繁，索引的维护成本会非常高。可以考虑在批量数据加载前暂时删除索引，加载完成后再重新创建索引。

查询模式不确定或多变

如果表的查询模式不确定或经常变化，创建索引的效果可能无法持续优化查询性能，反而可能带来不必要的维护开销。在查询模式稳定前，不要急于创建索引，先观察和分析实际查询情况，再决定是否需要索引。

索引过多导致的维护开销

一个表上创建过多的索引会增加插入、更新和删除操作的开销，因为每次数据修改都需要更新所有相关的索引。此外，过多的索引也会占用更多的存储空间。合理规划和管理索引数量，避免创建过多的冗余索引。

使用不当的复合索引

复合索引是指在多个列上创建的索引。如果查询中未使用索引的前导列，索引可能不会被使用，从而无法发挥其优化作用。在创建复合索引时，确保查询中会使用到索引的前导列，否则应重新考虑索引的设计。

Mysql常见索引失效的情况

使用函数或表达式

在索引列上使用函数或表达式（如UPPER(column)、column + 1）会导致索引失效。

隐式类型转换

当查询条件中的数据类型与索引列的数据类型不匹配时，MySQL 可能会进行隐式类型转换，从而导致索引失效。

使用OR条件

如果OR条件中的列没有索引或无法同时使用索引，也会导致索引失效。

前导模糊查询：

在 LIKE 查询中，如果模式以通配符（如%）开头，索引将失效。

不等于操作

使用不等于操作符（如!=或<>）通常会导致索引失效。

范围条件后再使用等值条件

复合索引中，如果使用了范围条件（如<、>、BETWEEN），后面的等值条件可能无法使用索引。

不满足最左前缀原则

对于复合索引，查询条件必须满足最左前缀原则，否则索引将失效。

查询条件中包含负向查询

例如NOT IN、NOT LIKE等负向查询条件会导致索引失效。

数据分布不均匀

即使有索引，如果数据分布非常不均匀，MySQL 优化器可能会选择全表扫描而不是使用索引。

唯一索引比普通索引快吗

在数据库中，唯一索引和普通索引在性能上的差异主要体现在插入和更新操作上，而不是查询操作上。

唯一索引与普通索引的区别

唯一索引（Unique Index）：保证索引列中的值是唯一的，即不允许重复值。数据库系统在插入或更新数据时，会检查索引列的值是否已经存在，如果存在则会拒绝该操作。

普通索引（Non-Unique Index）：不强制索引列中的值唯一，可以有重复值。插入和更新操作不需要进行唯一性检查。

查询性能

对于查询操作，唯一索引和普通索引的性能通常是相似的，因为查询操作主要依赖于索引结构（如 B+ 树或哈希表）来快速定位数据。无论是唯一索引还是普通索引，查询操作的复杂度和效率主要取决于索引的深度和数据分布情况。

插入和更新性能

唯一索引：需要进行唯一性检查，以确保新插入或更新的数据不会违反唯一性约束。这意味着每次插入或更新操作都需要额外的步骤来检查索引列中的值是否已经存在，这会增加一定的开销。

普通索引：不需要进行唯一性检查，只需插入或更新索引数据即可。由于没有额外的唯一性检查步骤，插入和更新操作通常会比唯一索引更快。

删除操作

删除操作对唯一索引和普通索引的性能影响较小，因为删除操作通常只涉及到从索引中移除数据，而不需要进行额外的检查。

A,B,C三个字段组成联合索引，AB,AC,BC三种情况下查询是否能命中索引？

在数据库中，联合索引（也称为复合索引）是由多个列组成的单个索引。假设我们有一个包含字段 A、B 和 C 的联合索引(A, B, C)，我们来分析在不同情况下查询是否能命中索引。

联合索引的基本原则

1. 最左前缀原则：联合索引会按照定义的字段顺序进行排序。查询必须从联合索引的最左边开始，才能利用索引。
2. 连续性原则：如果跳过了联合索引中的某个字段，那么索引在跳过的字段之后就无法继续利用。

查询情况分析

假设我们有一个联合索引(A, B, C)，我们来分析以下几种查询情况是否能命中索引：

1. 查询条件为(A, B)

>SELECT * FROM table WHERE A = ? AND B = ?;

能命中索引：这个查询条件从最左边开始使用联合索引的前两个字段(A, B)，因此可以利用索引。

2. 查询条件为(A, C)

>SELECT * FROM table WHERE A = ? AND C = ?;

3. 查询条件为(B, C)

>SELECT * FROM table WHERE B = ? AND C = ?;

不能命中索引：查询条件没有从联合索引的最左边字段A开始，因此不能利用联合索引(A, B, C)。

其他查询情况

4. 查询条件为(A)

>SELECT * FROM table WHERE A = ?;

能命中索引：查询条件只包含了联合索引的第一个字段A，可以完全利用索引。

5. 查询条件为(B)

>SELECT * FROM table WHERE B = ?;

不能命中索引：查询条件没有包含联合索引的最左边字段A，因此不能利用联合索引(A, B, C)。

6. 查询条件为(A, B, C)

>SELECT * FROM table WHERE A = ? AND B = ? AND C = ?;

能命中索引：查询条件包含了联合索引的所有字段(A, B, C)，可以完全利用索引。

查询条件	是否命中索引	说明
(A, B)	是	完全匹配联合索引的前缀部分。
(A)	是	只使用了联合索引的第一列，可以命中。
(A, B, C)	是	完全匹配整个联合索引。
(A, C)	部分	能够利用索引中A的部分，但C不在B之后直接出现，因此只能部分利用索引。
(B, C)	否	没有从最左边开始连续匹配索引定义，故无法利用索引。
(B)	否	未包含联合索引中最左侧的列A，所以不能利用索引。

B+树索引和哈希索引的区别

B+树索引

B+树是一种平衡树数据结构，所有数据都存储在叶节点上，叶节点通过指针相连形成一个链表。内部节点只存储键值和子节点指针。

在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。

B+树的高度较低，通常为2到4层，因此查找操作的时间复杂度为 O(log n)。

特点

有序性：B+树索引中的数据是有序的，适合范围查询（如BETWEEN、<、>等）。

节点分裂和合并：在插入和删除数据时，B+树会保持平衡，通过节点的分裂和合并来维持树的高度。

磁盘友好：B+树节点的大小通常与磁盘页大小匹配，减少磁盘 I/O 操作次数。

哈希索引

哈希索引基于哈希表实现，通过哈希函数将键值映射到哈希表中的位置。哈希表中的每个位置存储一个指向数据记录的指针。

特点

无序性：哈希索引中的数据是无序的，不适合范围查询。

高效等值查询：哈希索引对于等值查询（如=、IN）非常高效，查找时间复杂度为 O(1)。

冲突处理：可能会发生哈希冲突，需要通过链表或其他方法来解决冲突。

内存友好：通常哈希索引会将哈希表存储在内存中，以提高访问速度。

适用场景

适合等值查询非常频繁的场景。

不适合需要范围查询或排序的场景。

适合单字段查询，不适合多字段组合查询。

区别

特性	B+树索引	哈希索引
结构	基于平衡树结构，数据有序。	基于哈希表结构，数据无序。
查询性能	支持等值查询和范围查询；查找时间复杂度为 O(log n)。	等值查询性能高；查找时间复杂度为 O(1)，不支持范围查询。
适用场景	适合范围查询、排序操作以及多字段组合查询。	适合高频等值查询；不适合范围查询及排序操作。
维护成本	插入和删除时需维护树的平衡状态，可能涉及节点分裂或合并。	插入和删除相对简单，但需处理哈希冲突问题。
存储方式	通常存储在磁盘上，且节点大小设计以匹配磁盘页大小。	一般位于内存中，以此来加速数据访问过程。

如果应用场景中需要频繁的范围查询、排序操作或多字段组合查询，选择 B+树索引。

如果应用场景中等值查询非常频繁且不需要范围查询或排序操作，选择哈希索引。

为什么说B+比B树更适合实际应用中作为数据库索引？

范围查询效率更高

B树的数据存储在内节点和叶节点中，范围查询需要遍历多个节点，效率较低。B+树所有数据都存储在叶节点中，叶节点通过链表相连，形成一个有序的链表。这种结构使得范围查询和顺序访问非常高效，只需遍历叶节点链表即可完成范围查询。

内存和磁盘I/O效率更高

B树数据存储在所有节点中，内存使用较为分散，内节点和叶节点都存储数据，导致更多的磁盘I/O操作。

B+树内节点只存储索引键和指针，数据集中存储在叶节点中。这种结构使得内节点更小，可以在内存中存储更多的内节点，减少磁盘I/O操作，提高查询效率。

树的高度更低、更均匀

B树叶节点不一定在同一层，可能会导致树的高度不均匀，增加查询的复杂度。B+树所有叶节点都在同一层，树的高度更加均匀。更低、更均匀的树高意味着更少的磁盘I/O操作，查询效率更高。

更适合数据库的插入和删除操作

B树插入和删除操作可能会导致内节点和叶节点的分裂和合并，影响树的平衡性。

B+树插入和删除操作主要影响叶节点，内节点的结构相对稳定。叶节点通过链表相连，插入和删除操作在叶节点上进行，不会影响内节点的平衡性。

更高的空间利用率

B树：由于数据存储在所有节点中，内节点和叶节点的空间利用率较低。B+树：内节点只存储索引键和指针，空间利用率更高。叶节点通过链表相连，可以更高效地利用磁盘空间。

Mysql分库分表

垂直分表（Vertical Sharding）

将一个大的表按照列拆分成多个较小的表，每个表只包含部分列。根据业务关系，将不同业务字段的数据分散到不同的表中。例如，用户信息和订单信息可以拆分为用户表和订单表，每个表都只包含其相应的字段。

垂直拆分后业务清晰，数据维护简单。减少了I/O、锁争用和查询的字段数，提高了查询性能。

不过如果单表的数据量、读写压力大，垂直分表可能无法解决问题。部分业务可能无法直接通过 SQL 关联查询，增加了开发复杂度。

举一个例子，原有的一张用户表，user 表，里面有 ID，name，sex，drive_lincense。

像这种情况如果要做到垂直分表，就拆成，user 表和 user_ext 表。drive_lincense 这种信息输入附属的不重要的信息，就可以放在user_ext 内。

水平分表（Horizontal Sharding）

将一个大的表按照某个字段的取值范围或哈希值拆分成多个较小的表，每个表存储部分数据。根据业务需求和数据量的分布情况选择合适的字段进行分片。例如，订单表可以按照订单创建时间或订单ID的哈希值拆分为多个表。

这种方式解决了单一表数据量过大的问题，提高了系统性能和可扩展性。但是增加了跨表查询的复杂性。需要考虑数据路由和跨库查询优化。

分库分表联合使用

同时进行分库和分表操作，将数据分散到多个数据库和表中。根据业务需求，将数据拆分为多个数据库。在每个数据库中，再根据业务或数据量情况进行水平或垂直分表。

实现注意事项

数据分片策略：选择合适的分片策略对数据库进行分库分表操作，例如按照用户ID、订单创建时间等字段进行分片。

数据访问路由：通过中间件（如 MyCAT、ShardingSphere、Cobar 等）来实现数据访问的路由和分片规则的管理。

跨库查询优化：优化跨库查询，减少跨库查询的次数和复杂度，提高查询效率。

mysql主从同步原理

MySQL 主从同步（Replication）是一种将数据从一个 MySQL 数据库服务器（称为主服务器）复制到一个或多个 MySQL 数据库服务器（称为从服务器）的过程。这种机制通常用于提高数据冗余、负载均衡和数据备份。

主从同步的核心原理是将主服务器上的二进制日志复制到从服务器，并在从服务器上执行这些日志中的操作。相对于传输大量的SQL语句，只传输二进制日志可以节省网络带宽，提高同步性能。

主服务器（Master）

主服务器负责处理写入操作（INSERT、UPDATE、DELETE 等）并记录这些操作的日志（称为二进制日志，Binary Log）。

二进制日志（Binary Log）

在主服务器上，每次数据更改操作都会被记录到二进制日志文件中。二进制日志包含了所有对数据库进行的更改操作的详细记录。

从服务器（Slave）

从服务器连接到主服务器，并从主服务器获取二进制日志。它会将这些日志应用到自己的数据库中，以保持与主服务器的数据一致。

中继日志（Relay Log）

从服务器将从主服务器获取的二进制日志写入到中继日志文件中，然后再从中继日志中读取并应用这些更改。

复制过程

1、 主服务器记录更改到二进制日志

当主服务器上的数据发生更改时，这些更改会被记录到二进制日志文件中。

2、从服务器读取二进制日志

从服务器上的 I/O 线程负责连接到主服务器并读取二进制日志，将其写入到从服务器上的中继日志文件中。中继日志可以作为主服务器的副本。

3、从服务器应用中继日志

从服务器上的 SQL 线程读取中继日志文件，并将其中的更改应用到从服务器的数据库中。这样就和主服务器保持了一致。在这个过程中，会生成一个称为复制事件（Replication Event）的记录，并将其更新到主服务器上的复制信息表中。这个复制事件包含了从服务器接收和应用二进制日志事件的相关信息。

4、心跳检查

主服务器和从服务器之间会定期进行心跳检查，以确保连接的正常和可靠性。如果从服务器在一定时间内没有收到来自主服务器的二进制日志事件，或者发生连接错误，它将尝试重新连接主服务器并继续复制过程。

复制如何配置

配置主服务器

启用二进制日志记录（在my.cnf文件中设置log_bin），设置服务器唯一 ID（在my.cnf文件中设置server-id）。

配置从服务器

设置服务器唯一 ID（在my.cnf文件中设置server-id）。

配置从服务器连接到主服务器（使用CHANGE MASTER TO语句）。

启动从服务器的复制线程（使用START SLAVE语句）。

mysql主从同步延迟的原因和解决办法

MySQL 主从同步延迟（Replication Lag）是指从服务器与主服务器之间的数据复制存在时间差，导致从服务器上的数据不够新鲜。这种延迟可能会影响应用程序的性能和数据一致性。

延迟原因

主服务器负载过高：主服务器的高负载会影响二进制日志的生成和发送速度，从而导致从服务器的延迟。

从服务器性能瓶颈：从服务器的硬件资源（如 CPU、内存、磁盘 I/O）不足，导致处理中继日志的速度慢。

网络延迟：主从服务器之间的网络延迟或带宽不足，会影响二进制日志的传输速度。

大事务：大事务（如批量插入或更新）会生成大量的二进制日志，从而增加从服务器的处理时间。

从服务器上的锁争用：从服务器在应用中继日志时，可能会遇到锁争用问题，导致延迟。

配置不当：MySQL 配置不当（如缓冲区大小、线程数等）会影响复制性能。

解决办法

优化主服务器性能：减轻主服务器的负载，减少不必要的查询。可以使用缓存来减少数据库查询次数。

提升从服务器性能：升级从服务器的硬件资源，如增加 CPU 核心数、内存容量和磁盘 I/O 性能，确保从服务器的配置（如innodb_buffer_pool_size、innodb_log_file_size）适合其硬件资源。

优化网络性能：确保主从服务器之间的网络连接稳定且带宽充足。使用低延迟、高带宽的网络连接。

拆分大事务：将大事务拆分为多个小事务，以减少单个事务的处理时间。

调整复制配置：增加从服务器上的 I/O 线程和 SQL 线程数量（适用于 MySQL 8.0 及更高版本），调整slave_parallel_workers参数以启用并行复制。

监控和调整锁争用：使用监控工具（如SHOW PROCESSLIST、SHOW ENGINE INNODB STATUS）监控锁争用情况，并优化应用程序的锁使用策略。

使用半同步复制：启用半同步复制（Semi-Synchronous Replication），确保主服务器在提交事务后等待至少一个从服务器确认已收到二进制日志，从而减少延迟。

mysql的全复制、半复制、异步复制

异步复制

MySQL 默认使用异步复制。主服务器（Master）在提交事务后，不需要等待从服务器（Slave）的确认，事务即被认为完成。由于主服务器不需要等待从服务器的响应，性能较好。存在复制延迟的可能性，因为从服务器可能会滞后于主服务器。这种方式可以理解为主提交完之后，不确保是否从都收到了，直接成功。

工作原理

主服务器将事务写入二进制日志（binlog）。从服务器读取主服务器的二进制日志，并将其应用到自己的数据副本中。注意：一会对比看到其他的方式，你会发现本方式不需要任何回应。主要适用于对数据一致性要求不高，但对性能要求较高的场景或者读操作较多的应用，通过读写分离提高性能。

半同步复制

在主服务器提交事务时，至少需要等待一个从服务器确认已经接收到并写入中继日志（relay log）后，事务才被认为完成。相对于异步复制，性能稍有下降，因为主服务器需要等待从服务器的确认。减少了数据丢失的风险，但仍然存在一定的复制延迟。

工作原理：

主服务器将事务写入二进制日志。主服务器等待至少一个从服务器确认已接收到该事务并写入中继日志。收到确认后，主服务器才向客户端确认事务提交成功。适用于需要较高的数据一致性，但仍希望有较好的性能。

全同步复制

在主服务器提交事务时，必须等待所有从服务器确认已经接收到并应用了该事务后，事务才被认为完成。性能较差，因为主服务器需要等待所有从服务器的确认。延迟较高，但保证了数据的一致性和可靠性。

工作原理

主服务器将事务写入二进制日志。主服务器等待所有从服务器确认已接收到并应用了该事务。收到所有确认后，主服务器才向客户端确认事务提交成功。

适用场景

对数据一致性要求极高且可以容忍较高延迟的场景。适用于极端关键的数据系统，如某些金融系统、军事系统等。

drop,delete与truncate的区别

DROP

DROP用于删除数据库对象，如表、视图、索引等。可以删除表、数据库、视图、索引等。

特点

完全删除表及其所有数据。删除表后，表的结构和数据都将永久消失，且无法恢复（除非有备份）。不会触发DELETE触发器。无法回滚（不可逆操作）。

DELETE

DELETE用于删除表中符合条件的行。只能删除表中的数据行。

特点

可以通过WHERE子句指定删除哪些行，如果没有WHERE子句，将删除表中所有行，但表结构和索引保留。删除操作会记录在事务日志中，因此可以回滚（可逆操作）。可以触发DELETE触发器。相对较慢，因为每行删除操作都会记录在日志中，并且需要考虑事务处理。

TRUNCATE

TRUNCATE用于快速删除表中的所有行。直接操作表中的所有数据行。

特点

删除所有行，但保留表结构及其列、索引等元数据。比DELETE快，因为它不逐行删除数据，而是直接释放数据页。无法回滚（不可逆操作），但在某些数据库系统中，TRUNCATE可能被视为DDL操作，具体行为取决于数据库系统的实现。重置自增列（AUTO_INCREMENT）计数器。

操作对象	是否可回滚	性能	触发器
DROP	不可回滚	通常比DELETE快，因为它不逐行处理数据。	不会触发DELETE触发器
DELETE	可回滚	较慢，特别是当删除大量数据时，因为每行操作都会被记录在日志中。	会触发DELETE触发器
TRUNCATE	通常不可回滚（视数据库实现而定）	通常比DELETE快，因为它不逐行处理数据。	不会触发DELETE触发器

多版本并发控制MVCC

MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照（隔离级别RC下），所以MVCC为数据库解决了以下问题：

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能。

解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题。

MVCC 的工作原理

MVCC 通过在数据库中存储每个数据行的多个版本来实现并发控制。每个事务在读取数据时，可以看到一个一致性快照，而不会被其他事务的并发修改所干扰。这种机制主要依赖于以下几个核心概念：

版本控制：每个数据行都有多个版本，每个版本与一个特定的事务相关联。每次数据行被修改时，都会创建一个新版本，而旧版本则被保留。

事务快照：每个事务在开始时都会获取一个一致性快照，这个快照包含了事务开始时数据库的状态。事务在执行期间，只能看到属于这个快照的数据版本，而不会看到其他事务提交的修改。

事务ID：每个事务都有一个唯一的事务ID，用于标识事务的开始和结束时间。事务ID 用于确定哪些数据版本对当前事务可见。

可见性规则：数据库系统使用事务ID 和版本号来决定哪些数据版本对当前事务可见。通常，事务只能看到在其开始之前已经提交的版本，而看不到在其开始之后创建的版本。

MVCC 的优势

提高并发性：MVCC 允许多个事务同时读取和写入数据，而不会相互阻塞。这大大提高了数据库的并发性和性能。

减少锁争用：由于每个事务在读取数据时不需要加锁，MVCC 减少了锁争用和死锁的可能性。

一致性视图：每个事务在开始时获取一致性快照，确保在整个事务期间看到的数据是一致的。

MVCC 的实现细节

不同的数据库系统对 MVCC 的实现细节有所不同，但通常包括以下几个方面：

版本链：每个数据行维护一个版本链，链中的每个节点表示一个版本。每个版本包含数据行的值、创建该版本的事务ID 和删除该版本的事务ID（如果适用）。

垃圾回收：随着时间的推移，旧的版本会变得不再需要。数据库系统需要定期进行垃圾回收，删除不再需要的旧版本，以释放存储空间。

快照隔离：MVCC 通常与快照隔离级别结合使用，确保事务在执行期间看到一致的快照。快照隔离级别是介于可重复读和串行化之间的一种隔离级别。

MVCC作用

提高并发性

MVCC 允许多个事务同时执行读写操作，而无需互相阻塞或等待。这极大地提高了数据库的并发处理能力，特别是在高并发环境中。

减少锁争用

在传统的锁机制中，读写操作需要加锁，容易导致锁争用和死锁问题。MVCC 通过版本控制，使得读取操作不需要加锁，从而减少了锁争用的频率。

提供一致性视图

MVCC 为每个事务提供一个一致性的快照视图，使得事务在执行过程中看到的数据是固定的，不会受到其他并发事务的影响。这简化了应用程序的开发，因为开发者无需担心数据在事务执行过程中发生变化。

避免读写冲突

在 MVCC 中，读操作不会阻塞写操作，写操作也不会阻塞读操作。这避免了读写冲突，提高了系统的整体性能和响应速度。

减少死锁

由于读操作不需要加锁，MVCC 减少了死锁的可能性。死锁通常发生在多个事务互相等待资源释放的情况下，而 MVCC 的无锁读操作减少了这种情况发生的机会。

提高读性能

MVCC 提供了无锁的读操作，读操作直接读取数据的快照版本，不需要等待其他事务完成。这极大地提高了读操作的性能，尤其是在读操作频繁的场景下。

实现更高的隔离级别

MVCC 支持实现更高的隔离级别，如快照隔离（Snapshot Isolation），在这种隔离级别下，事务可以看到一个一致的快照视图，同时避免了脏读和不可重复读等问题。

假设有一个银行账户表accounts：

+----+--------+
| id | balance|
+----+--------+
| 1  | 1000   |
+----+--------+

场景 1：高并发读写

• 事务 A：读取账户余额
• 事务 B：更新账户余额

在传统的锁机制下，事务 A 和事务 B 可能会互相阻塞。然而在 MVCC 中：

1. 事务 A 开始，读取balance = 1000。
2. 事务 B 开始，更新balance为 1200。
3. 事务 A 继续读取，仍然看到balance = 1000。
4. 事务 B 提交后，新的事务可以看到balance = 1200。

这种情况下，事务 A 和事务 B 可以并发执行，互不影响。

场景 2：避免读写冲突

• 事务 C：读取账户余额
• 事务 D：更新账户余额

在 MVCC 中：

1. 事务 C 开始，读取balance = 1000。
2. 事务 D 开始，更新balance为 1500。
3. 事务 D 提交后，新的事务可以看到balance = 1500。
4. 事务 C 继续读取，仍然看到balance = 1000。

通过 MVCC，读取操作和写入操作可以并发执行，避免了读写冲突。

MySQL中有哪几种锁

表级锁（Table Locks）

表锁（Table Lock）：表级锁是对整个表进行加锁。它有两种类型：

• 读锁（Read Lock）：多个事务可以同时对表加读锁，但不能对表进行写操作。
• 写锁（Write Lock）：当一个事务对表加写锁时，其他事务不能对该表进行读或写操作。

表锁的优点是实现简单，开销较小，但在高并发情况下可能会导致较多的锁等待和性能瓶颈。

行级锁（Row Locks）

行级锁是对单行记录进行加锁，适用于 InnoDB 存储引擎。它的粒度更细，可以提高并发性能。行级锁主要有两种类型：

共享锁（Share Lock，S 锁）：允许事务读取一行数据，但不允许修改。其他事务也可以获得该行的共享锁。

排他锁（Exclusive Lock，X 锁）：允许事务读取和修改一行数据，但不允许其他事务获取该行的任何锁。

间隙锁（Gap Locks）

间隙锁是 InnoDB 存储引擎特有的锁，用于防止幻读现象。间隙锁不仅锁定现有的行，还锁定一个范围内的间隙，以防止其他事务在这个间隙内插入新行。

间隙锁（Gap Lock）：锁定一个范围内的间隙，但不锁定间隙中的行。

临键锁（Next-Key Lock）：是行锁和间隙锁的组合，锁定一个行和它前面的间隙。

意向锁（Intention Locks）

意向锁是 InnoDB 存储引擎用来支持多粒度锁定的机制，主要用于表级锁和行级锁的协调。意向锁有两种类型：

意向共享锁（Intention Share Lock，IS 锁）：事务打算对某些行加共享锁。

意向排他锁（Intention Exclusive Lock，IX 锁）：事务打算对某些行加排他锁。

意向锁本身不会阻塞其他事务，但会阻止其他事务对表加相冲突的表级锁。

自增锁（Auto-Increment Locks）

自增锁用于处理自增列（AUTO_INCREMENT）的并发问题。InnoDB 存储引擎使用一种特殊的表级锁来确保自增列的值在并发插入时的正确性。

元数据锁（Metadata Locks）

元数据锁用于保护表结构的变化，确保在表结构变更（如ALTER TABLE）时不会有其他事务对表进行操作。主要用于 DDL（数据定义语言）操作。

外键锁（Foreign Key Locks）

外键锁用于保证外键约束的完整性。InnoDB 存储引擎在插入、更新或删除涉及外键的记录时，会自动加锁以确保外键约束不被破坏。

临时锁（Temporary Locks）

临时锁是 MySQL 在某些特定操作（如复制、备份等）过程中使用的锁，用于确保操作的原子性和一致性。

Mysql的常用函数

字符串函数

CONCAT(str1, str2, …)：连接多个字符串。

SUBSTRING(str, pos, len)：从字符串str的pos位置开始，截取长度为len的子字符串。

LENGTH(str)：返回字符串的长度（字节数）。

UPPER(str)：将字符串转换为大写。

LOWER(str)：将字符串转换为小写。

TRIM(str)：去除字符串两端的空格。

REPLACE(str, from_str, to_str)：将字符串str中的from_str替换为to_str。

数值函数

ABS(x)：返回x的绝对值。

CEIL(x)或CEILING(x)：返回大于或等于x的最小整数。

FLOOR(x)：返回小于或等于x的最大整数。

ROUND(x, d)：将x四舍五入到d位小数。

RAND()：返回一个 0 到 1 之间的随机数。

POWER(x, y)或POW(x, y)：返回x的y次幂。

日期和时间函数

NOW()：返回当前日期和时间。

CURDATE()或CURRENT_DATE()：返回当前日期。

CURTIME()或CURRENT_TIME()：返回当前时间。

CURTIME()或CURRENT_TIME()：返回当前时间。

DATEDIFF(date1, date2)：返回两个日期之间的天数差。

ADDDATE(date, interval)或DATE_ADD(date, interval)：在日期上加上一个时间间隔。

SUBDATE(date, interval)或DATE_SUB(date, interval)：在日期上减去一个时间间隔。

聚合函数

COUNT(expression)：返回满足条件的行数。

SUM(expression)：返回数值列的总和。

AVG(expression)：返回数值列的平均值。

MAX(expression)：返回列的最大值。

MIN(expression)：返回列的最小值。

其他常用函数

IF(condition, true_value, false_value)：条件判断函数。

COALESCE(value1, value2, …)：返回第一个非 NULL 的值。

IFNULL(expression, alt_value)：如果expression为 NULL，返回alt_value。

NULLIF(expr1, expr2)：如果expr1等于expr2，返回 NULL，否则返回expr1。

Mysql的常用数据类型

数值类型

整数类型

TINYINT: 1 字节，范围 -128 到 127 或 0 到 255（无符号）。

SMALLINT: 2 字节，范围 -32,768 到 32,767 或 0 到 65,535（无符号）。

MEDIUMINT: 3 字节，范围 -8,388,608 到 8,388,607 或 0 到 16,777,215（无符号）。

INT: 4 字节，范围 -2,147,483,648 到 2,147,483,647 或 0 到 4,294,967,295（无符号）。

BIGINT: 8 字节，范围 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 或 0 到 18,446,744,073,709,551,615（无符号）。

浮点数类型

FLOAT: 单精度浮点数，4 字节。

DOUBLE: 双精度浮点数，8 字节。

定点数类型

DECIMAL: 精确的定点数，用户可以指定精度和小数位数。适用于需要高精度的金融计算。

字符串类型

CHAR: 固定长度字符串，最多 255 个字符。

VARCHAR: 可变长度字符串，最多 65,535 个字符（实际长度受行大小限制）。

TINYTEXT: 最多 255 个字符。

TEXT: 最多 65,535 个字符。

MEDIUMTEXT: 最多 16,777,215 个字符。

LONGTEXT: 最多 4,294,967,295 个字符。

二进制类型

BINARY: 固定长度二进制数据，最多 255 字节。

VARBINARY: 可变长度二进制数据，最多 65,535 字节。

TINYBLOB: 最多 255 字节的二进制数据。

BLOB: 最多 65,535 字节的二进制数据。

MEDIUMBLOB: 最多 16,777,215 字节的二进制数据。

LONGBLOB: 最多 4,294,967,295 字节的二进制数据。

日期和时间类型

DATE: 日期，格式为 ‘YYYY-MM-DD’，范围 ‘1000-01-01’ 到 ‘9999-12-31’。

TIME: 时间，格式为 ‘HH:MM:SS’，范围 ‘-838:59:59’ 到 ‘838:59:59’。

DATETIME: 日期和时间，格式为 ‘YYYY-MM-DD HH:MM:SS’，范围 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。

TIMESTAMP: 时间戳，格式为 ‘YYYY-MM-DD HH:MM:SS’，范围 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC。

YEAR: 年份，格式为 ‘YYYY’，范围 ‘1901’ 到 ‘2155’。

JSON 类型

JSON: 用于存储 JSON 格式的数据，可以方便地进行 JSON 数据的存储和查询。

枚举和集合类型

ENUM: 枚举类型，字符串对象的集合，每次只能存储一个值。

SET: 集合类型，字符串对象的集合，每次可以存储多个值。

特殊类型

BOOLEAN: 布尔类型，实际上是 TINYINT(1) 的别名，0 表示 FALSE，非 0 表示 TRUE。

SERIAL: 自动递增的整数类型，实际上是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的别名。

Mysql的char和varchar的区别

存储方式

CHAR：CHAR是固定长度的字符串类型。无论实际存储的字符串长度是多少，CHAR类型的字段都会占用固定的空间。例如，CHAR(10)类型的字段，无论存储的字符串是 “abc” 还是 “abcdefghij”，都会占用 10 个字符的空间。如果存储的字符串长度小于定义的长度，MySQL 会在字符串的末尾填充空格以达到指定的长度。

VARCHAR：VARCHAR是可变长度的字符串类型。VARCHAR类型的字段根据实际存储的字符串长度来分配空间。例如，VARCHAR(10)类型的字段，存储 “abc” 只占用 3 个字符的空间（加上一个额外的字节用于存储字符串的长度）。VARCHAR类型的字段在存储时会记录实际字符串的长度，因此不会有额外的空格填充。

存储效率

CHAR：由于是固定长度，CHAR类型的字段在存储和检索时效率较高，特别适用于存储长度固定的字符串（如国家代码、邮政编码等）。但对于长度变化较大的字符串，CHAR类型可能会浪费大量的存储空间。

VARCHAR：VARCHAR类型的字段在存储空间上更节省，因为它只分配实际需要的空间。对于长度变化较大的字符串，VARCHAR类型更加合适。

性能

CHAR：由于固定长度，CHAR类型的字段在进行比较和检索时速度较快。适用于需要频繁查询和比较的字段。

VARCHAR：VARCHAR类型的字段在存储和检索时需要额外的长度信息，因此在某些情况下性能可能稍逊于CHAR。适用于长度不固定且不需要频繁比较的字段。

使用场景

CHAR：适用于存储长度固定的字符串，如固定长度的编码、标识符等。例如，存储国家代码（如 “USA”、”CHN”）或邮政编码（如 “12345”）。

VARCHAR：适用于存储长度可变的字符串，如姓名、地址、描述等。例如，存储用户的姓名、电子邮件地址或文章内容。

mysql中int(1)和int(10)的区别

INT(1)和INT(10)的区别并不在于它们能存储的数值范围，而是在于显示宽度（display width）。

数值范围

无论是INT(1)还是INT(10)，它们的数值范围都是由INT类型决定的，而不是括号中的数字。

INT类型的数值范围（无符号）是-2147483648到2147483647。

显示宽度

INT(1)和INT(10)中的数字（1 和 10）表示的是显示宽度，而不是存储的数值范围。

显示宽度是指当你使用ZEROFILL属性时，MySQL 会将数字填充到指定的宽度。例如，如果你定义了INT(5) ZEROFILL，并插入值42，它会显示为00042。

影响

存储大小：显示宽度不会影响实际的存储大小。INT类型始终占用 4 个字节的存储空间。

显示效果：显示宽度主要影响的是数据的显示效果，尤其是在使用ZEROFILL属性时。没有ZEROFILL属性时，显示宽度的影响很小。

blob和text的区别

BLOB 和 TEXT 是两种用于存储大数据的列类型。

存储内容

BLOB（Binary Large Object）：用于存储二进制数据，如图像、音频、视频等。BLOB字段中的数据以二进制格式存储，不进行字符集转换。

TEXT：用于存储大文本数据，如长文章、日志等。TEXT字段中的数据以字符格式存储，受字符集和排序规则的影响。

类型和大小

BLOB和TEXT都有四种不同的类型，每种类型支持不同的数据大小：

TINYBLOB/TINYTEXT：最大长度 255 字节

BLOB/TEXT：最大长度 65,535 字节（约 64 KB）

MEDIUMBLOB/MEDIUMTEXT：最大长度 16,777,215 字节（约 16 MB）

LONGBLOB/LONGTEXT：最大长度 4,294,967,295 字节（约 4 GB）

字符集和排序规则

BLOB：不使用字符集和排序规则。数据按字节存储和比较。

TEXT：使用字符集和排序规则。数据按字符存储和比较，字符集转换会影响数据的存储和检索。

使用场景

BLOB：适用于存储二进制数据，如图像、音频、视频文件等。

TEXT：适用于存储大文本数据，如文章、日志、HTML 内容等。

索引和性能

索引：BLOB和TEXT类型的列不能被索引，除非你指定一个前缀长度。例如：

性能：由于BLOB和TEXT类型的数据可能非常大，它们的读写性能可能会受到影响。在设计数据库时，应尽量避免在这些字段上进行频繁的搜索和排序操作。

存储和检索

BLOB：存储和检索时不会进行字符集转换，适合存储二进制数据。

TEXT：存储和检索时会进行字符集转换，适合存储需要字符集支持的文本数据。

什么是表分区

在MySQL中，表分区是一种将表的数据按照某种规则分成多个较小的独立部分（分区）的技术。每个分区可以独立存储在不同的文件或磁盘上，从而提高查询性能、简化管理和优化存储资源的利用。表分区特别适用于处理大型表的数据管理和查询优化。

表分区的类型

MySQL支持几种不同的分区方法，每种方法适用于不同的应用场景：

范围分区（Range Partitioning）

根据列值的范围将数据分配到不同的分区。适用于日期或数值范围的分区。

例如，可以按年份、月份或其他数值范围进行分区。

CREATE TABLE sales (
  id INT,
  sale_date DATE,
  amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024)
);

列表分区（List Partitioning）

根据列值的具体列表将数据分配到不同的分区。适用于离散值的分区。

例如，可以按地区代码或类别进行分区。

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  region_code CHAR(2)
)
PARTITION BY LIST (region_code) (
  PARTITION pNorth VALUES IN ('NA', 'EU'),
  PARTITION pSouth VALUES IN ('SA', 'AF'),
  PARTITION pEast VALUES IN ('AS', 'OC')
);

哈希分区（Hash Partitioning）

根据列值的哈希值将数据分配到不同的分区。适用于均匀分布数据的分区。

例如，可以使用主键或其他列的哈希值进行分区。

CREATE TABLE logs (
  id INT,
  log_message TEXT
)
PARTITION BY HASH (id) PARTITIONS 4;

键分区（Key Partitioning）

类似于哈希分区，但使用MySQL内部的哈希函数。适用于需要更灵活的哈希分区策略。

CREATE TABLE users (
  id INT,
  username VARCHAR(50)
)
PARTITION BY KEY (id) PARTITIONS 4;

复合分区（Composite Partitioning）

结合两种分区方法，例如范围分区和哈希分区的组合。适用于更复杂的分区需求。

CREATE TABLE orders (
  id INT,
  order_date DATE,
  customer_id INT
)
PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH (customer_id) SUBPARTITIONS 4 (
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023)
);

表分区的优点

性能提升：通过将大表分成多个较小的分区，可以提高查询和操作的性能。查询可以在特定的分区上执行，减少了扫描的数据量。

管理简化：分区表可以更容易地进行维护和管理，例如分区的添加、删除和归档。可以方便地进行备份和恢复操作。

存储优化：不同的分区可以存储在不同的存储设备上，优化存储资源的利用。可以为不同的分区设置不同的存储引擎和参数。

并行处理：分区表可以更好地利用并行处理能力，提高多线程查询的性能。

使用表分区的注意事项

分区键的选择

选择合适的分区键非常重要，直接影响查询和操作的性能。

分区键应当是查询中经常使用的列。

分区管理

定期维护分区，例如合并小分区、拆分大分区、归档历史数据等。

表分区与分表的区别

分区（Partitioning）

分区是将一个大表按照某种规则分成多个较小的部分，每个部分称为一个分区。这些分区在逻辑上仍然属于同一个表，但在物理上可以独立存储。

实现方式：

内部实现：分区是在数据库内部实现的，用户在查询或操作时不需要关注分区的存在。

分区类型：MySQL支持多种分区类型，如范围分区、列表分区、哈希分区和键分区等。

优点：

1. 查询优化：通过分区裁剪（Partition Pruning），查询可以只在相关的分区上执行，减少扫描的数据量。
2. 管理简化：可以方便地进行分区的添加、删除和归档等操作。
3. 并行处理：分区可以更好地利用并行处理能力，提高多线程查询的性能。
4. 存储优化：不同的分区可以存储在不同的存储设备上，优化存储资源的利用。

分表（Sharding）

分表是将一个大表按照某种规则拆分成多个较小的表，每个表称为一个分片（Shard）。这些分片在逻辑上是独立的表，但在应用层面上被视为一个整体。

实现方式：

外部实现：分表通常需要在应用层面实现，应用程序需要知道数据如何分布在不同的表中。

分片规则：分表的规则可以是基于某个列的范围、哈希值或者其他自定义规则。

优点：

1. 水平扩展：分表可以将数据分布到多个数据库实例上，实现水平扩展。
2. 负载均衡：通过分表可以将负载均衡到不同的数据库实例上，减少单个实例的压力。
3. 独立管理：每个分片可以独立进行备份、恢复和优化等操作。

特性	分区	分表
实现层面	在数据库内部实现，用户透明。	在应用层面实现，应用程序需要处理数据分布。
管理复杂度	管理相对简单，可以通过SQL语句直接操作分区。	管理复杂，需要应用程序和数据库层面共同处理。
性能优化	通过分区裁剪和并行处理优化查询性能。	通过将数据分布到多个实例上减少单个实例的负载。
扩展性	适用于单个数据库实例内的数据管理和性能优化。	适用于跨多个数据库实例的水平扩展和负载均衡。
适用场景	适用于大表的数据管理和查询优化，特别是需要处理大量数据的单个数据库实例。	适用于需要水平扩展和负载均衡的大规模分布式系统。

什么情况下会产生死锁

不同顺序的锁定

当两个或多个事务以不同的顺序请求相同的资源时，容易引发死锁。

1、 事务A先锁定表1的记录，然后尝试锁定表2的记录。

2、 事务B先锁定表2的记录，然后尝试锁定表1的记录。

3、 如果事务A和事务B同时执行，就可能导致死锁。

间隙锁（Gap Lock）

在InnoDB存储引擎中，间隙锁用于防止幻读。在范围查询中，间隙锁可能会导致死锁。

1、 事务A执行SELECT * FROM table WHERE id > 10 FOR UPDATE，锁定了id大于10的所有记录及其间隙。

2、 事务B执行INSERT INTO table (id) VALUES (15)，试图插入一个新记录。

3、 如果这两个事务并发执行，可能会导致死锁。

自增列的死锁

在高并发情况下，当多个事务同时插入自增列时，可能会导致死锁。

1、 事务A和事务B同时插入数据到包含自增主键的表中。

2、MySQL在分配自增值时可能会导致锁争用，从而引发死锁。

外键约束

在涉及外键约束的表中，更新或删除操作可能会导致死锁。

1、 事务A在表1中删除一条记录，该表有一个外键引用表2。

2、 事务B在表2中更新或删除与表1中记录相关的记录。

3、 如果这两个事务并发执行，可能会导致死锁。

锁升级

当MySQL从行级锁升级到表级锁时，可能会导致死锁。

1、 事务A和事务B分别锁定同一表中的不同行。

2、 如果某个操作需要将行级锁升级为表级锁，这可能会导致死锁。

混合使用不同类型的锁

在同一个事务中混合使用不同类型的锁（如读锁和写锁）时，可能会导致死锁。

1、事务A持有一个读锁，并试图获取一个写锁。

2、事务B持有一个写锁，并试图获取一个读锁。

3、如果这两个事务并发执行，可能会导致死锁。

大量并发事务

在高并发环境中，大量事务同时操作同一资源，可能会导致死锁。

1、多个事务同时对同一行数据进行更新操作。

2、事务之间相互等待资源释放，可能会导致死锁。

处理和预防死锁的方法

1. 自动检测和回滚：InnoDB存储引擎能够自动检测死锁，并回滚其中一个事务以解除死锁。
2. 查看死锁信息：使用命令SHOW ENGINE INNODB STATUS查看最近一次死锁的信息，以帮助诊断问题。
3. 合理的事务设计：尽量避免长时间持有锁，确保事务尽可能短小和高效。
4. 一致的锁定顺序：确保所有事务以相同的顺序请求资源，以减少死锁的可能性。
5. 减少并发事务：通过优化应用程序逻辑，减少同时并发的事务数量。

死锁常见解决方案

MySQL中的死锁是指两个或多个事务相互等待对方持有的锁，从而导致这些事务无法继续执行。死锁是并发控制中的一个常见问题，特别是在高并发环境下。

分析和优化SQL语句

减少锁的持有时间：优化SQL语句，使其执行时间更短，从而减少锁的持有时间，降低死锁发生的概率。

避免大事务：将大事务拆分为多个小事务，减少每个事务持有锁的时间。

索引优化：确保查询使用了合适的索引，减少全表扫描，从而减少锁冲突。

调整事务的顺序

统一访问顺序：确保所有事务以相同的顺序访问资源。例如，如果多个事务都需要访问表A和表B，确保所有事务都先访问表A，再访问表B。

减少循环等待：通过调整事务的执行顺序，避免事务之间形成循环等待。

使用合适的隔离级别

降低隔离级别：在某些情况下，可以考虑使用较低的隔离级别（如READ COMMITTED）来减少锁冲突。但需要权衡数据一致性和并发性能。

使用锁机制：在某些情况下，可以显式使用LOCK IN SHARE MODE或FOR UPDATE来控制锁的粒度和范围。

合理使用索引

覆盖索引：使用覆盖索引（covering index）来减少锁的争用。例如，如果查询只需要访问索引列，可以避免访问数据行，从而减少锁冲突。

避免索引缺失：确保查询使用了合适的索引，避免全表扫描导致的大量锁争用。

监控和分析

启用死锁日志：在MySQL配置中启用死锁日志，记录死锁信息以便分析。

分析死锁日志：通过分析死锁日志，找出导致死锁的SQL语句和事务，进行针对性的优化。

锁等待超时

设置合理的锁等待超时：通过设置合理的锁等待超时参数（如innodb_lock_wait_timeout），避免长时间等待锁，从而减少死锁影响。

视图

视图（View）是SQL数据库中的一种虚拟表，它是基于SQL查询结果定义的。视图本身不存储数据，而是存储了一个SQL查询，当你访问视图时，数据库会执行该查询并返回结果。视图可以包含来自一个或多个表的列和行，并且可以包含过滤、排序、连接等复杂的SQL操作。

1. 简化复杂查询

视图可以将复杂的SQL查询封装起来，使得用户在使用时只需简单地查询视图，避免重复编写复杂的SQL语句。

1. 提高数据安全性

视图可以限制用户访问底层表中的敏感数据。例如，可以创建一个不包含敏感信息的视图，用户只能访问视图而不是底层表。

1. 提供数据抽象

视图可以提供数据的不同视角，抽象出底层表的复杂性。例如，可以创建一个视图，将多个表的联合结果呈现为一个单一的逻辑表。

1. 实现数据重用

视图可以被多个应用程序或用户重用，确保数据逻辑的一致性和可维护性。如果查询逻辑发生变化，只需修改视图定义，而不需要修改所有使用该查询的地方。

注意事项

视图的更新：并不是所有视图都可以更新。只有满足一定条件（如视图基于单个表，没有聚合函数等）的视图才可以进行插入、更新和删除操作。

性能影响：视图本身不存储数据，每次访问视图时都会执行底层查询。如果视图定义的查询非常复杂，可能会影响性能。

依赖管理：视图依赖于底层表结构，如果底层表结构发生变化（如列名更改、表删除等），视图也需要相应调整。

特点

视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。

视图是由基本表(实表)产生的表(虚表)。

视图的建立和删除不影响基本表。

对视图内容的更新(添加，删除和修改)直接影响基本表。

当视图来自多个基本表时，不允许添加和删除数据。

视图的操作包括创建视图，查看视图，删除视图和修改视图。

缺点

性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。

修改限制。当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，是不可修改的

存储过程

存储过程（Stored Procedure）是数据库中一组预编译的SQL语句和控制结构的集合，可以在数据库中保存并执行。存储过程可以包含逻辑控制、循环、条件判断等复杂的逻辑，并且可以接受参数和返回结果。存储过程的主要目的是将业务逻辑封装在数据库层，以提高性能和简化应用程序的实现。

创建存储过程的基本语法

CREATE PROCEDURE procedure_name (IN parameter1 datatype, OUT parameter2 datatype, INOUT parameter3 datatype)
BEGIN
    -- SQL 语句
END;

DELIMITER //

CREATE PROCEDURE increase_salary(IN emp_id INT, IN increment DECIMAL(10, 2))
BEGIN
    UPDATE employees
    SET salary = salary + increment
    WHERE id = emp_id;
END //

DELIMITER ;

存储过程的优缺点

优点

预编译：存储过程在首次执行时会被编译，并存储在数据库中，后续执行时无需再次编译，减少了解析和优化的开销。

权限控制：可以通过设置存储过程的执行权限，限制用户直接访问底层表，只允许通过存储过程进行操作。

隐藏复杂逻辑：存储过程可以封装复杂的业务逻辑，用户无需了解具体实现细节。

封装业务逻辑：将常用的业务逻辑封装在存储过程内，多个应用程序可以重用这些逻辑，减少代码重复。

缺点

调试工具有限：与应用程序代码相比，存储过程的调试工具和方法较为有限，调试和排错较为困难。

错误处理复杂：存储过程中的错误处理通常比应用程序代码中的错误处理更复杂。

数据库依赖性：存储过程通常依赖于特定数据库的语法和功能，不同数据库之间的存储过程可能无法直接移植。

资源消耗：存储过程在数据库服务器上执行，可能会占用大量服务器资源，导致性能瓶颈。

union和union all区别

UNION和UNION ALL是 SQL 中用于合并两个或多个结果集的操作符。它们的主要区别在于是否去除重复的行。

UNION

去除重复行：UNION操作会自动去除合并结果中的重复行。

排序操作：由于UNION需要去除重复行，因此它会在内部执行一个排序操作来识别和删除重复行，这可能会影响性能。

UNION ALL
保留重复行：UNION ALL操作不会去除重复行，所有的结果行都会被保留。
性能较好：由于UNION ALL不需要进行去重操作，因此通常比UNION性能更好，特别是在处理大数据量时。

binlog

MySQL 的二进制日志（binlog）是一个重要的日志文件，用于记录对数据库进行的所有更改操作。二进制日志的主要功能包括数据恢复、复制和审计。

二进制日志的作用

数据恢复：在发生崩溃或数据丢失时，可以使用二进制日志恢复数据。通过重放日志中的更改，可以将数据库恢复到某个时间点。

复制：二进制日志是 MySQL 复制机制的基础。主服务器（master）将其上的所有更改记录到二进制日志中，从服务器（slave）通过读取这些日志来复制数据变化，从而保持数据同步。

审计和分析：二进制日志可以用来审计数据库上的更改操作，帮助管理员了解谁在何时对数据库进行了哪些更改。

二进制日志的组成

二进制日志由多个日志文件组成，这些文件按照顺序记录了所有的更改操作。每个日志文件都有一个唯一的编号，MySQL 会自动轮换和生成新的日志文件。

配置和管理二进制日志

1. 启用二进制日志：在 MySQL 配置文件（通常是my.cnf或my.ini）中，添加以下配置项以启用二进制日志：

[mysqld]
log-bin=mysql-bin

1. 查看二进制日志状态：使用以下 SQL 命令查看二进制日志的状态：

SHOW BINARY LOGS;

查看当前正在使用的二进制日志文件：

SHOW MASTER STATUS;

1. 管理二进制日志：手动刷新二进制日志文件：

FLUSH LOGS;

删除旧的二进制日志文件：

PURGE BINARY LOGS TO 'mysql-bin.000010';
或
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

1. 配置二进制日志保留策略：可以在配置文件中设置保留策略，例如自动删除7天前的二进制日志：

[mysqld]
expire_logs_days = 7

解析二进制日志

MySQL 提供了mysqlbinlog工具，用于读取和解析二进制日志文件。以下是一些常用的命令：

1. 显示二进制日志内容：

mysqlbinlog mysql-bin.000001

1. 将二进制日志内容导入数据库：

mysqlbinlog mysql-bin.000001 | mysql -u username -p

redolog

MySQL 的重做日志（redo log）是 InnoDB 存储引擎中的一个关键组件，用于保证数据的持久性和一致性。重做日志记录了所有对数据库进行的修改操作，这些操作在实际写入数据文件之前先写入重做日志。

重做日志的作用

1. 数据恢复：在数据库崩溃或意外关机时，重做日志可以用于恢复未完成的事务。通过重做日志，InnoDB 可以在重新启动时重新应用未完成的事务，确保数据的一致性。
2. 提高性能：重做日志的存在使得 InnoDB 可以将事务提交的更改快速写入日志文件，而不是立即写入数据文件。这种机制减少了磁盘 I/O 操作，提高了数据库的性能。

重做日志的工作原理

1. 写入重做日志：当一个事务开始时，所有的修改操作都会首先记录到内存中的重做日志缓存（redo log buffer）。当事务提交时，重做日志缓存中的内容会被刷新到磁盘上的重做日志文件中，以确保事务的持久性。
2. 重做日志应用：在数据库重启时，InnoDB 会检查重做日志文件中的内容，并重新应用所有未完成的事务。这一过程称为“重做（redo）”。

重做日志的组成

重做日志由一组固定大小的日志文件组成，这些文件循环使用。具体配置项包括：

1. 重做日志文件路径和大小：可以在 MySQL 配置文件（my.cnf或my.ini）中设置重做日志文件的路径和大小：

[mysqld]
innodb_log_group_home_dir = /var/lib/mysql/  # 重做日志文件的存储路径
innodb_log_file_size = 512M  # 每个重做日志文件的大小
innodb_log_files_in_group = 2  # 重做日志文件的数量

1. 重做日志缓冲区大小：设置重做日志缓冲区的大小：

[mysqld]
innodb_log_buffer_size = 16M

管理重做日志

1. 查看重做日志状态：可以通过以下命令查看重做日志的状态：

SHOW ENGINE INNODB STATUS;

1. 调整重做日志配置：调整重做日志文件的大小和数量需要先关闭 MySQL 服务，删除现有的重做日志文件，然后重新启动 MySQL 服务。具体步骤如下：

关闭 MySQL 服务：

sudo systemctl stop mysql

删除现有的重做日志文件（通常位于数据目录中，例如/var/lib/mysql）：

sudo rm /var/lib/mysql/ib_logfile*

修改 MySQL 配置文件中的重做日志设置。

重新启动 MySQL 服务：

sudo systemctl start mysql

其他的什么log

错误日志（Error Log）

记录 MySQL 服务器启动、运行和停止期间发生的错误和重要事件。

[mysqld]
log_error = /var/log/mysql/mysql-error.log

错误日志可以通过查看配置的日志文件来检查，例如：

cat /var/log/mysql/mysql-error.log

查询日志（General Query Log）

记录所有客户端连接和执行的 SQL 语句。这对于调试和审计非常有用，但由于记录了所有查询，可能会对性能产生影响。

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/mysql-query.log

查询日志可以通过查看配置的日志文件来检查

cat /var/log/mysql/mysql-query.log

慢查询日志（Slow Query Log）

记录执行时间超过指定阈值的查询。这对于优化性能和识别慢查询非常有用。

可以在 MySQL 配置文件中启用慢查询日志并设置日志文件路径和阈值时间

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2  # 记录执行时间超过 2 秒的查询

慢查询日志可以通过查看配置的日志文件来检查

cat /var/log/mysql/mysql-slow.log

中继日志（Relay Log）

在 MySQL 复制环境中，从服务器（slave）使用中继日志记录从主服务器（master）接收到的二进制日志事件。中继日志用于应用这些事件以保持数据同步。

事务日志（Undo Log）

记录事务的撤销操作，用于支持事务的回滚和 MVCC（多版本并发控制）。撤销日志通常是 InnoDB 存储引擎的一部分。

表空间日志（Tablespace Log）

记录表空间的扩展、收缩等操作。主要用于管理 InnoDB 的物理存储。

表空间日志由 InnoDB 自动管理，通常不需要手动配置。