02-Mysql-眼熟能聊_fetchmany在处理最后几条数据时会出现遗漏或重复吗-CSDN博客

本文链接：https://blog.csdn.net/weixin_42658491/article/details/149154166

===SQL基础===

创建表？

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

升序/降序？

SELECT id, name, salary
FROM employees
ORDER BY salary DESC, name ASC;

MySQL出现性能差的原因有哪些？

可能是 SQL 查询使用了全表扫描，也可能是查询语句过于复杂，如多表 JOIN 或嵌套子查询。

也有可能是单表数据量过大。

通常情况下，添加索引就能解决大部分性能问题。对于一些热点数据，还可以通过增加 Redis 缓存，来减轻数据库的访问压力。

1 数据库的三大范式是什么？

第一范式：每一列都是不可分割的的原子数据项
第二范式：在一基础上，非码属性必须完全依赖于候选码
第三范式：在2基础上，任何非主属性不依赖于其他非主属性。确保任何一列都和主键直接相关，不能间接相关

第一范式：每一列都是不可分割的的原子数据项
第二范式：在一基础上，非码属性必须完全依赖于候选码

举例：

同一个订单可能包含不同的产品，主键必须是订单号+产品号

产品折扣、价格、数量和产品号相关。

订单金额和时间仅和订单号相关。

不符合第二范式，拆表

第三范式：在2基础上，任何非主属性不依赖于其他非主属性。确保任何一列都和主键直接相关，不能间接相关

举例：

所有属性都依赖学号，，但班主任性别年龄直接依赖于班主任姓名。拆表！

MySQL怎么连表查询？

内连接：返回两个表中有匹配关系的行

左外连接：返回左表中所有行，未匹配的右表列包含NULL

右外连接：和左相反

全外连接：所有行，包括未匹配的

说一下外键约束？

一个表中的外键是另一个表的主键

维护表与表间关系，确保数据的完整性和一致性的机制。

举例：

两个表，学生表，课程表。学生和课程是多对多

在学生表中定义一个指向课程表的外键。学生表的课程id是外键，同时这个列是课程表中的课程id（课程表的主键）

确保课程选的课在课程表中存在。

若没有外键约束，可能导致选了不存在的课程or删除了课程而忘记从学生表中删除选修该课程的学生的情况。

===存储引擎===

说说MySQL的基础架构？

MySQL的架构大致可以分为三层，从上到下依次是：连接层、服务层、和存储引擎层。

①、连接层主要负责客户端连接的管理，包括验证用户身份、权限校验、连接管理等。可以通过数据库连接池来提升连接的处理效率。

②、服务层是MySQL的核心，主要负责查询解析、优化、执行等操作。在这一层，SQL语句会经过解析、优化器优化，然后转发到存储引擎执行，并返回结果。这一层包含查询解析器、优化器、执行计划生成器、缓存（如查询缓存）、日志模块等。

③、存储引擎层负责数据的实际存储和提取，是MySQL架构中与数据交互最直接的层。MySQL支持多种存储引擎，如InnoDB、MyISAM、Memory 等。

Mysql的默认存储引擎为什么是InnoDB?

事务支持：ACID特性
并发性能：行级锁
崩溃恢复：redolog日志。数据的一致性和持久性

3 SQL语句的执行流程是什么样的？6步（团淘

解析—— 语义分析—— 查询优化—— 生成执行计划—— 执行—— 返回结果

sql 查询的执行顺序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT/OFFSET

例子分析：

对于以下 SQL 查询：


SELECT department, COUNT(*) 
FROM employees
WHERE age > 30
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY department DESC
LIMIT 10;

FROM：从 employees 表获取数据。
WHERE：筛选出 age > 30 的员工。
GROUP BY：按 department 列对数据进行分组。
HAVING：过滤分组后员工数大于 5 的部门。
SELECT：选择 department 和员工数量。
ORDER BY：按 department 降序排序。
LIMIT：只返回前 10 个部门。

===索引===

mysql 聚合索引解决什么问题，使用时的注意事项？

聚合索引（Clustered Index）在 MySQL 中指的是数据表的 主键索引 或 唯一索引，它决定了数据表中行的物理存储顺序。聚合索引的主要作用是 提高查询效率，尤其是范围查询和排序操作，因为数据表中的数据按照索引顺序存储，能够快速定位数据。

如果聚簇索引的数据更新，它的存储要不要变化？

非索引数据，结构不变。索引数据，B+树要保持有序性，结构变化。

mysql中的主键，自增字段和UUID的区别是什么？

主键，每个表都要有的，具备唯一约束，简单的话，可以理解成行号。主键可以是数值，字符串，或者多个字段的联合主键。
自增字段，按指定起点和步长自增的数值字段，通常为了保证主键的唯一性，会用自增列作为主键。但这不是个合理的做法，自增列，大部分情况下与其他字段是没有关系的。而且主键默认会建立聚集索引，是效率最高的。
uuid，自增字段只适合于单节点数据库。多节点的唯一性就比较麻烦了。uuid是结合了时间戳，机器码，随机数等信息生成的字符编码，在多节点的集群数据库也能保证唯一性。好像是有极低的记录会重复，可以忽略的。

1 那假设我不用MySQL自增的主键，你觉得会有什么问题呢？

候选者：首先主键得保证它的唯一性和长度尽可能短吧，这两块是需要考虑的。

候选者：另外，由于索引的特性（有序），如果生成像uuid类似的主键，那插入的的性能是比自增的要差的

候选者：因为生成的uuid，在插入时有可能需要移动磁盘块（比如，块内的空间在当前时刻已经存储满了，但新生成的uuid需要插入已满的块内，就需要移动块的数据）

1 表中有十个字段，主键用自增id还是uuid，为什么？

1 什么字段适合做主键? 4点

唯一性：主键必须确保每一行数据的标识都是唯一的，避免重复。通常使用自增主键或 UUID 等生成唯一值的字段。
非空性：主键字段不能为 NULL，确保每一条记录都有明确的标识。
稳定性：主键值一旦设置后应尽量保持不变。避免使用可能会更改的业务数据（如用户名、邮箱等）作为主键，否则会影响数据库的引用完整性。
较小数据量：主键应尽量简短，因为它通常会被其他表作为外键引用。如果主键数据量较大，会增加存储和索引的负担。通常自增整数是最佳选择，因为它体积小、计算简单。
自增字段对单机系统可以。多台服务器要考虑分布式id

索引的优缺点？

优点：加快查找速度
缺点：占用存储空间。创建和维护耗费时间。增删改调整B+树有代价。

常见的索引结构哪些能够作为索引使用？

B+树的叶子的节点的链表是双向的有什么好处？

实现倒序遍历或排序。

给了一条SQL语句，这个你如何创建索引呢？

分析查询：根据 SQL 查询的 WHERE 条件、JOIN、ORDER BY 和 GROUP BY 等子句，确定需要索引的列。
创建索引：根据查询条件的结构，选择单列索引或复合索引。
优化索引：避免冗余索引，确保选择性较高的列作为索引对象，以提高查询性能。

索引能解决所有问题吗？

不能，索引有局限性：

写操作性能下降：

- 当进行 INSERT、UPDATE 或 DELETE 操作时，索引会增加额外的维护开销。例如，插入一条数据时，数据库需要更新相关的索引，这会增加额外的时间消耗。

占用额外的存储空间：

- 每个索引都会占用额外的磁盘空间。对于大表，多个索引可能会占用大量的存储空间，尤其是如果数据库表频繁发生变化时，索引会持续增长。

查询优化问题：

- 索引并不是对所有查询都有帮助。如果查询的 字段没有索引 或查询方式不适合索引，索引反而可能降低查询性能。
- 索引对于 复杂查询（如涉及多个表的 JOIN 或复杂的子查询）可能不够有效。

不适用于小表：

- 对于小表，使用索引可能并不会带来太大性能提升，甚至可能会增加额外的开销。因为小表的数据量少，数据库可以轻松完成全表扫描。

频繁更新的字段：

- 如果查询的字段频繁更新，索引会导致性能下降，因为每次更新时都需要重新调整索引。

创建联合索引需要注意什么？

把区分度大的字段排在前面

mysql什么时候创建联合索引，什么时候创建单列索引？

在 MySQL 中，选择创建联合索引还是单列索引，通常取决于查询的模式和性能需求。具体来说：

单列索引的情况

单列索引适用于查询条件中仅涉及一个列的情况。具体场景包括：

单列查询：当查询只涉及某个列时，单列索引是高效的。例如，查询条件如 WHERE age = 30。
字段选择性高：对于字段值分布较均匀的列（例如 ID 或邮箱），单列索引能够显著提高查询性能。

联合索引的情况

联合索引适用于查询条件涉及多个列的场景，特别是：

多列查询：当查询同时涉及多个列时，创建联合索引能显著提高查询效率。例如，查询条件为 WHERE first_name = 'John' AND last_name = 'Doe'，此时对 first_name 和 last_name 创建联合索引会加速查询。
ORDER BY 多列：如果查询需要对多个列排序，联合索引能加速排序操作。
联合索引的前缀匹配：联合索引的顺序非常关键，MySQL 会根据索引的前缀进行匹配。如果查询条件顺序与联合索引一致，索引会更高效。

为什么MongoDB索引用B树，而MySQL用B+树？

MySQL 属于关系型数据库，所以范围查询会比较多，所以采用了 B+树；但 MongoDB 属于非关系型数据库，在大多数情况下，只需要查询单条数据，所以 MongoDB 选择了 B 树。

为什么用B+不用跳表呢？

跳表基于链表，节点分布不连续，会频繁触发随机磁盘访问，性能较差。
跳表需要逐节点遍历链表，范围查询性能不如 B+ 树。

===事务===

读已提交和可重复读的底层区别？

读已提交（Read Committed）
在该级别下，每个 SQL 语句执行时都会获取当时最新的已提交数据。也就是说，同一事务中的两次查询，若期间其它事务提交了数据修改，后一次查询就可以看到最新的值。
底层实现：
多数数据库（例如 Oracle、PostgreSQL、MySQL InnoDB）依靠 多版本并发控制（MVCC） 来实现。
每执行一次查询时，数据库会根据当前时刻的提交信息构造一个 数据快照，确保只读取到已提交的数据。
由于快照是每个语句单独构造的，所以同一事务内不保证两次查询看到的数据一致性，这样就可能出现 不可重复读（Non-repeatable read） 问题。
可重复读（Repeatable Read）
在该级别下，事务开始时便确定了一个数据快照，在整个事务期间所有查询都基于这一快照视图。
底层实现：
同样通常依赖 MVCC 技术，但与读已提交不同，可重复读在事务开始时固定一个快照，即使其他事务提交了修改，也不会对当前事务内已读取的数据产生影响。
这样保证了在同一事务内多次读取同一数据时，结果保持一致，从而避免了不可重复读的问题。
在 MySQL InnoDB 中，还会利用 间隙锁（Next-key Locks） 防止幻读现象；而在其他数据库系统中，幻读的防护程度可能会有所不同。

MySQL 写入时如何确保数据一定写入了数据库？

在 MySQL 中，确保数据被写入数据库通常通过事务和 确认机制（如 COMMIT）来实现。使用事务可以确保数据的持久化和一致性，同时通过 binlog（二进制日志）记录数据库操作，确保即使系统崩溃，数据也能恢复。

redolog的刷盘机制是怎样的？

InnoDB 的 redo log 刷盘机制遵循写前日志原则（先写日志，再写磁盘），在事务提交时先将日志写入内存缓冲区，通过组提交策略批量 fsync 到磁盘，同时利用检查点同步内存数据，以保证数据持久性和高性能。

===锁===

表级锁和行级锁的区别和应用场景？

表：粒度大，整体控制，大批量操作

行：粒度小，减少锁冲突，使用于频繁的单行操作

===日志===

有了undolog为什么还要redolog呢？

udolog解决的是事务回滚和一致性

redolog记录的是事务执行后的数据状态，保证事务的持久性。mysql在任何时间崩溃，重启之后提交的记录都不会丢失。

将写操作从随写改为顺序写

缓冲池提高了读写效率低，但缓冲池是基于内存的，一旦发生掉电故障，没来得及写入磁盘的脏页就会丢失。为解决这个问题，对于数据的更新，先更新内存，同时将操作存储到redolog，更新完成。后续，将缓冲池中的脏页写入磁盘。这就是WAL技术（write ahead logging）。即先写日志，再写磁盘。

在提交事务的时候，只需将redolog写入磁盘，脏页可以后续写入。

系统崩溃，虽脏页没持久化，但redolog持久化了，通过redolog恢复。

redolog是追加操作，所以是顺序写。写数据是随机写，顺序写更高效，redolog写入磁盘开销更小。

Redolog怎么保证持久性？

WAL
顺序写入
Checkpoint机制：定期将内存中数据写入磁盘。

能不能只用binlog不用redolog？

不行，binlog是server层，不能记录哪些脏页没写入磁盘。崩溃恢复的时候用redolog

Update的具体执行过程是怎样的？(整合三个log的写入过程)

通过聚簇索引搜主键
看更新前后值是否变化
开启事务，先更新undolog
InnoDB层更新，先更新内存，再写入redolog
至此，一条记录
记录binlog
事务提交
Update语句完成。

===性能调优===

如果expain用到的索引不正确的话，有什么办法干预吗？

Force index 强制走索引

===架构===

===场景===

讲一下分库分表的设计？分库分表组件的选择？为什么使用这个方案？

常见的分库分表方案包括 垂直分库分表 和 水平分库分表。

分库分表的常用组件有 ShardingSphere、MyCat 和 Cobar。

使用分库分表的主要原因是提升性能、增强系统可扩展性，避免单一数据库的性能瓶颈和单点故障问题。

3.1 ShardingSphere

ShardingSphere 是一个开源的分布式数据库中间件，支持分库分表、读写分离、分布式事务等功能。它支持多种数据库类型（MySQL、PostgreSQL、Oracle等）和多种分片策略（如范围分片、哈希分片）。
特点：

- 兼容性好，可以与现有数据库无缝集成。
- 提供强大的分片策略、分布式事务和分库分表功能。
- 提供了丰富的配置选项，支持自定义分片规则。

3.2 MyCat

MyCat 是一个开源的数据库中间件，主要用于数据库的分库分表。它支持 分布式数据库的透明访问，可以在不修改应用程序代码的前提下，实现在多个数据库实例间进行数据路由。
特点：

- 支持多种分片策略（哈希、范围等）。
- 适用于大规模的分布式数据库系统。
- 与应用程序的配合度较高，方便配置和管理。

3.3 Cobar

Cobar 是阿里巴巴开源的一个数据库中间件，支持分库分表、读写分离等功能。它主要用作数据访问层的负载均衡和分片。
特点：

- 支持多种数据库的分片和路由。
- 性能较为优越，适合高并发应用。
- 提供了灵活的路由规则，支持复杂的数据库拆分。

分库分表的场景下如果有跨库的查询你会怎么解决？

"在分库分表场景中，跨库查询通常会带来一定的挑战。可以通过以下几种方式来解决跨库查询问题：

应用层合并：将各个库的数据查询分开，在应用层合并结果。
分布式查询引擎：使用分布式查询引擎（如 ShardingSphere、MyCat）来实现跨库查询的路由和聚合。
数据冗余和同步：在多个库中维护冗余数据，减少跨库查询的需求。
异步查询与最终一致性：通过异步查询和补偿机制，保证查询的一致性，同时减少跨库查询的性能瓶颈。"

1000w条数据，发现用 user_id 去查没有走索引（索引也没有失效）走的全表扫描为什么？

全表扫描发生的原因通常与查询优化器的决策、数据分布以及查询条件的复杂性有关。通过查看执行计划、更新统计信息或优化查询条件，通常可以解决这一问题。

查询优化器的决策：即使存在索引，数据库优化器可能会认为全表扫描比使用索引更高效，特别是在索引选择性较低的情况下（比如 user_id 的值重复率高）。
数据分布问题：如果 user_id 的值分布不均匀，某些 user_id 值出现频率非常高，索引的使用可能不如全表扫描高效。
统计信息不准确：数据库优化器依赖统计信息来做决策，如果统计信息过时或不准确，可能导致优化器选择不使用索引。
查询条件复杂性：如果查询条件比较复杂，例如涉及到 OR、范围查询或其他函数操作，可能会导致索引失效，进而执行全表扫描。