【八股】MySQL篇

林子漾

已于 2024-10-10 20:10:11 修改

阅读量1.2k

点赞数 8

分类专栏：八股项目文章标签： mysql 后端数据库

于 2024-04-27 20:07:20 首次发布

本文链接：https://blog.csdn.net/qq_44768318/article/details/138256387

版权

八股同时被 2 个专栏收录

13 篇文章 8 订阅 ¥9.90 ¥99.00

订阅专栏

超级会员免费看

项目

13 篇文章 0 订阅

订阅专栏

本文深入探讨了MySQL的索引原理，包括B+树、聚簇与二级索引、覆盖索引和索引下推。同时，讲解了超大分页的解决方案，以及索引创建和优化的原则。此外，还涵盖了慢查询的定位与优化，事务的ACID特性，以及数据库引擎的选择和SQL基础语句。最后，讨论了如何应对大量QPS对数据库的影响。

摘要由CSDN通过智能技术生成

入门

优化、视图、触发器、锁、InnoDB引擎、事务高级

基础

CHAR和VARCHAR有什么区别?

CHAR是固定长度的字符串类型，定义时需要指定固定长度，存储时会在末尾补足空格。CHAR适合存储长度固定的数据，如固定长度的代码、状态等，存储空间固定，对于短字符串效率较高。
VARCHAR是可变长度的字符串类型，定义时需要指定最大长度，实际存储时根据实际长度占用存储空间。VARCHAR适合存储长度可变的数据，如用户输入的文本、备注等，节约存储空间。

Text数据类型可以无限大吗?

● TEXT: 65,535 bytes ~ 64kb
● MEDIUMTEXT: 16,777,215 bytes ~ 16 Mb
● LONGTEXT: 4,294,967,295 bytes ~ 4Gb

主键和外键的区别

主键是用于唯一标识表中每一行数据的字段，不允许为空且必须唯一。定义主键时会自动创建唯一约束（UNIQUE Constraint）。
外键是用于建立表之间关系的字段，它引用另一个表的主键，允许重复且可以为空，确保引用的完整性。定义外键时会创建引用完整性约束，确保外键值必须在主表中存在。

外键约束

外键约束的作用是维护表与表之间的关系，确保数据的完整性和一致性。
假设有一个是学生表，一个是课程表。这两个表之间有一个关系，即一个学生可以选修多门课程，而一门课程也可以被多个学生选修。这种情况就可以在学生表中定义一个指向课程表的外键，如下所示：

CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
course_ id INT,
FOREIGN KEY (course_ id) REFERENCES courses(id)
);

这里，students表中的course_ id字段是一个外键，它指向courses表中的id字段。这个外键约束确保了每个学生所选的课程在courses表中都存在，从而维护了数据的完整性和一致性。如果没有定义外键约束，那么就有可能出现学生选了不存在的课程或者删除了一个课程而忘记从学生表中删除选修该课程的学生的情况，这会破坏数据的完整性和一致性。因此，使用外键约束可以帮助我们避免这些问题。

in和exist

👉 IN 用于判断某个值是否在一个给定的集合中，集合可以是一个明确的列表或者是一个子查询的结果。

SELECT * FROM table_name WHERE column_name IN (value1, value2, ...);

特点：

IN 适用于小型的子查询结果集。
IN 语句的子查询会在执行之前先执行并缓存所有结果。
当子查询的结果集较小或明确指定列表时，IN 的性能通常较好。
如果子查询返回的结果集较大，IN 可能会导致性能问题。

👉 EXISTS 用于判断是否存在满足条件的记录，它通常结合子查询使用。当子查询返回至少一行数据时，EXISTS 返回 TRUE。

SELECT * FROM table_name WHERE EXISTS (subquery);

特点：

EXISTS 子查询在遇到符合条件的第一条记录时就会停止执行，因此在处理大型数据集时可能比 IN 更高效。
EXISTS 常用于子查询返回大量数据且只需判断数据是否存在的场景。
当子查询涉及复杂的计算或返回大量数据时，EXISTS 的性能通常优于 IN。

👉二者区别：

使用场景：
- IN 适合用于子查询结果较小的情况，或者明确的值列表。
- EXISTS 更适合用于判断子查询是否有记录返回的情况，尤其是子查询结果集较大时。
性能：
- IN 子查询会将所有结果集先缓存下来，这对小数据集是可以的，但对大数据集性能较差。
- EXISTS 子查询在找到第一个匹配结果时就停止执行，因此通常在大数据集上比 IN 更高效。
执行逻辑：
- IN 是基于列表匹配的操作。
- EXISTS 是基于布尔判断（是否存在）的操作。

SQL查询语句的执行顺序

FROM：首先选择数据来源的表或视图，并进行连接（如果有多个表）。
JOIN：执行表连接操作，合并符合连接条件的多表数据。
ON：应用连接条件过滤连接的结果集。
WHERE：对从表中选取的记录进行过滤，仅保留满足WHERE条件的记录。
GROUP BY：将数据按指定的列进行分组，通常用于聚合操作。
HAVING：对分组后的数据进行过滤，仅保留满足HAVING条件的分组。
SELECT：选择并返回所需的列或表达式。
DISTINCT：在SELECT中去除重复的记录。
ORDER BY：对结果集进行排序。
LIMIT：限制返回的记录数量。

执行一条SQL请求的过程是什么

建立连接：客户端向数据库服务器发送连接请求，连接器校验用户身份，建立连接。
检查查询缓存（MySQL 8.0前）：检查查询语句是否在缓存中命中，如果命中则直接返回结果。
解析SQL：通过解析器对SQL语句进行词法分析、语法分析，构建语法树（用于后续的处理，如优化器读取表名、字段名和语句类型）。
预处理SQL：会检查查询中所涉及的表、字段、权限等，确保它们存在并且用户有权限访问。对于SELECT 查询，预处理阶段会将扩展为表上的所有列。
优化SQL：生成多个执行计划，选择最优执行计划。（优化过程考虑了多种因素，例如索引的使用、数据表大小、数据的分布等，确保查询能够以最快的方式执行）
执行SQL：按照执行计划执行SQL语句，调用存储引擎接口，从存储中读取或写入数据。
返回结果：将查询结果返回给客户端。

数据库数据文件分类（opt\frm\ibd）

一个含表：t_order的数据库文件目录下会包含以下内容：

db.opt，用来存储当前数据库的默认字符集和字符校验规则。
_t order.frm，t_order的表结构会保存在这个文件。在MySQL中建立一张表都会生成一个.frm文件,该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
t_order.ibd，t_order的表数据会保存在这个文件。表数据既可以存在共享表空间文件(文件名：ibdata1)里，也可以存放在独占表空间文件(文件名:名字.ibd)。这个行为是由参数innodb_ file_ per_table控制的，若设置了为1，则会将存储的数据、索引等信息单独存储在一个独占表空间。

数据库三大范式

1NF：数据库表的每一列是不可分割的原子数据项

2NF：非码属性必须完全依赖于候选码

在1NF的基础上，非码属性必须完全依赖于候选码，消除非主属性对主码的部分函数依赖。即需要确保数据库表中的每一列都和主键相关，而不能只和主键的某一部分相关（主要针对联合主键）。

3NF：任何非主属性不依赖于其他非主属性

在2NF的基础上，数据表中的每一列数据都和主键直接相关，而不能间接相关，消除传递依赖。

索引

什么是索引

👉帮助数据库快速查找定位数据的数据结构
👉优点：提高检索效率 | 降低磁盘IO | CPU消耗低
👉缺点：创建索引的时候需要一点时间、占用物理空间大、维护索引时也会耗费时间、会降低表的增删改的效率（因为B+树会进行动态维护来保证索引的有序性）

索引底层的数据结构，为什么用B+树，为什么不用跳表？

👉MySQL的InnoDB用的是B+树，非叶子节点只存储指针，叶子节点存储索引，且为一个双向链表。
👉 B+树特性：所有叶子节点都在同一层 / 自平衡 / 非叶子节点只存储指针 / 叶子节点存储索引
👉首先说一下不用二叉搜索树的原因，第一个是因为二叉搜索树需要在内存中进行查找，在数据量很大的时候，是无法把磁盘中存的索引数据全部装载到内存中的，第二个是就算是平衡二叉树或者红黑树，它树的高度还是不低，而树越高意味着磁盘IO的次数越多，性能较差。基于此选用B树或者B+树，不用B树的原因是B+树的非叶子节点只存储索引，这样可以更好的缩小树的层高，而且叶子节点还是双向链表，使得返回查找的效率更高了。第二是在B树中进行范围查询时，首先找到要查找的下限，然后对B树进行中序遍历，直到找到查找的上限；而B+树的范围查询，只需要对链表进行遍历即可。第三B树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程。
👉B+树的高度在3层时存储的数据可能已达千万级别，但对于跳表而言同样去维护千万的数据量那么所造成的跳表层数过高而导致的磁盘io次数增多，也就是使用B+树在存储同样的数据下磁盘io次数更少。

页分裂和页合并

页分裂：发生在 B+树的叶子节点上。当插入一条记录，且该记录所在的页已经满了，就会触发页分裂。页分裂的过程如下：
- 过程：当插入的数据超过页的存储上限（例如一页大小为 16KB），会将该页一分为二。
  数据将被均匀地分配到两个新的页中。同时需要在父节点中插入一个新的键值指向新分裂出来的页，保证 B+ 树的有序性。
- 影响：页分裂会导致 B+ 树的层级增高，从而使得索引查找的路径变长，降低查询效率。
  另外，由于页分裂涉及到对父节点的更新操作，也会带来额外的磁盘 I/O。
页合并：通常发生在删除操作中。当某个页中的数据量减少到一定阈值时（例如删除操作导致页的利用率很低），InnoDB 会尝试将该页和相邻的页合并，减少空闲空间。
- 过程：当一个页中的记录太少，且与相邻的页总数据量可以容纳在一个页中时，会将两个页的数据合并到一个页中。然后会释放另一个空闲的页，并更新父节点中的键值，使其指向合并后的新页。
- 影响：可以减少 B+树的层级，缩短索引查找路径，提升查询效率。还可以回收磁盘空间，减少空间浪费。
优化建议
控制数据插入的顺序：如果数据按照索引顺序插入，可以减少页分裂的发生几率，因为按顺序插入的记录会被存放在同一页中，直到该页填满再分裂。
批量删除时谨慎操作：大规模删除操作可能会导致大量页合并，从而增加系统的负载。如果要进行批量删除操作，建议分批执行，或者在非高峰期进行。
合理设置页大小：在 MySQL 中可以通过调整页大小（例如使用 16KB 页）来影响页分裂和合并的频率，进而控制索引结构的平衡。

聚簇索引和二级索引是什么，什么是回表查询

👉聚簇索引是指数据和索引一起存储，B+树的叶子节点存储了所有的行数据，有且只有一个
👉二级索引是指数据和索引分开存储，B+树的叶子节点只存储数据对应的主键，一般用户自己创建的索引都是二级索引，可以有多个
👉回表查询发生在使用二级索引查询的时候，找到对应的主键值再到聚簇索引中查找整行数据。

主键与聚簇索引的关系: 在许多数据库系统中（如MySQL的InnoDB），主键索引通常就是聚簇索引。如果表有主键，数据库会默认将主键作为聚簇索引。如果没有主键，数据库可能会选择一个唯一的非空字段或创建一个内部的行ID作为聚簇索引。

什么是覆盖索引

👉指查询使用了索引且需要返回的列在索引中能够全部找到，不需要回表查询，比如使用id聚簇索引，一次索引扫描返回行的所有数据

什么是索引下推

👉索引下推是一种 MySQL 优化技术，它可以将部分where条件下推到存储引擎中执行，从而减少回表次数，提高查询效率。

建好索引后再插入一条数据会发生什么变化

插入新数据可能导致B+树结构的调整和索弓|信息的更新，以保持B+树的平衡性和正确性，这些变化由数据库系统自动处理，如果插入的数据导致叶子节点已满，可能会触发叶子节点的分裂操作，以保持B +树的平衡性。

什么情况下索引会失效

违反最左匹配原则
👉只有最左边的索引列能够实现快速的定位，而右边的索引列只能在左边的索引列匹配的情况下才会生效。
👉举个例子，假设有一个联合索引 (A, B, C)，如果查询语句中包含了字段 A 和字段 B，那么数据库会尽可能地使用这个索引，直到找到第一个不匹配的字段（这里是字段 C）为止。如果查询语句中只包含字段 B 和字段 C，那么数据库无法使用这个联合索引。
范围查询右边的列
like模糊查询时，%在写开头【索引是有序的，%开头无法确定从哪个位置开始匹配】
在索引上进行运算操作（表达式计算、函数）
字符串索引未加单引号【MySQL在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索|列会发生隐式类型转换，由于隐式类型转换是通过CAST函数实现的，等同于对索引列使用了函数，所以就会导致索引失效】

索引优化原则

前缀索引优化：在一些大字符串的字段作为索引时，为了减小索引字段大小，可以增加一个索引页中存储的索引值，提高索引的查询速度。
覆盖索引优化：覆盖索引是指SQL中query的所有字段，在索引B+Tree的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索弓|查询获得，可以避免回表的操作。
主键索引最好是自增的：如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不要重新移动数据，因此这种插入数据的方法效率非常高。但如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
防止索引失效（见上一题）

索引创建原则

数据量很大的表一定要创建索引
频繁查询、排序、分页的字段一定要创建索引
尽量使用联合索引而不是分页索引，很多时候可以减少回表查询的情况发生
要控制索引的数量，不是越多越好
尽量选择区分度高的列作为索引，最好是唯一的
字符串类型的索引如果太长了，可以根据字符串的特点来创建前缀索引
索引不能存储NULL值，在建表时要声明NOT NULL约束

区分度：某个字段不同值的个数 / 表的总行数

什么情况下适合建索引，什么样的字段适合建索引

高选择性：字段的值非常分散，具有高区分度。经常参与查询条件、排序、分组或连接操作。需要保证唯一性或覆盖查询。需要全文搜索。

频繁的查询操作
过滤条件: 当某个字段经常出现在WHERE子句中作为过滤条件时，建立索引可以显著提高查询性能。例如，用户ID、订单ID等常用于查询的字段通常适合建索引。
排序操作: 当某个字段经常用于ORDER BY排序时，索引可以加速排序操作。如果查询经常需要对某个字段进行排序，比如按创建时间排序，考虑对该字段建立索引。
分组操作: 如果某个字段经常用于GROUP BY操作，可以考虑建立索引以加速分组查询。
连接操作
外键字段: 当两个或多个表进行连接操作时，如果连接条件是某个字段（如外键字段），通常在该字段上建立索引可以显著提高连接查询的性能。
主键关联: 在主键和外键关联的场景下，通常在外键字段上建立索引，以加快连接操作。
唯一性约束: 对于需要唯一性约束的字段（如电子邮件、用户名等），建立唯一索引可以同时保证数据唯一性和提高查询速度。
在包含大量数据的表中，对常用于查询过滤的字段建立索引，可以避免全表扫描，提高查询速度
如果某些查询经常涉及多个条件组合，可以考虑建立复合索引（联合索引）以提高查询效率。例如，一个表经常按(地区, 年龄)组合查询，可以考虑在这两个字段上建立联合索引。
全文搜索：如果需要对文本字段进行全文搜索，可以考虑使用全文索引。例如，商品描述或文章内容等字段需要进行全文搜索时，可以使用全文索引（如MySQL的FULLTEXT索引）。
使用数据库提供的查询性能分析工具（如MySQL的EXPLAIN）查看查询执行计划。如果发现某些查询的性能较低，且没有使用索引，可以考虑为相关字段建立索引。

创建联合索引时应该注意什么

字段顺序
最左前缀原则：联合索引是按顺序排列的，查询时只有符合最左前缀的查询条件才能使用索引。例如，一个 (A, B, C) 的联合索引可以加速 (A)、(A, B) 和 (A, B, C) 的查询，但不能加速 (B, C) 或单独 © 的查询。
频率高的字段靠前：将最常用于过滤的字段放在索引的最前面。比如，如果查询中A字段的过滤条件经常出现，那么将A放在索引的第一位。
查询的覆盖性
覆盖索引：尽量让联合索引包含在查询中所需的字段，以实现覆盖索引（Covering Index），这样查询就可以直接从索引中获取所需数据，而不必回表查询。比如，如果你的查询语句总是包含 A、B 和 C 三个字段，可以创建一个 (A, B, C) 的联合索引。
字段选择
区分度高的字段：将区分度高的字段放在索引前面。区分度高的字段能够更有效地过滤数据，减少扫描的记录数，从而提高查询性能。
避免在变动频繁的字段上建索引：频繁更新的字段可能会导致索引频繁更新，影响写入性能。因此，应尽量避免在这种字段上建立索引。
索引的选择性
考虑查询的类型：如果你的查询包含范围查询（如>、<），那么该字段最好放在联合索引的末尾，因为范围查询之后的字段在索引中无法被有效利用。
索引的长度
索引字段的长度：如果是字符串类型的字段（如VARCHAR），应考虑索引的长度，以避免过长的索引占用太多的存储空间。可以通过指定前缀长度来缩短索引的长度，比如 VARCHAR(255) 字段可以只索引前20个字符。
性能监控
定期分析查询和索引：使用数据库提供的性能分析工具（如MySQL的EXPLAIN命令）定期检查查询计划，确保联合索引被有效使用，并根据实际情况调整索引。
避免冗余索引
减少重复索引：一个联合索引可以代替多个单字段索引，减少不必要的重复索引以节省存储空间和维护成本。

索引合并导致死锁的问题

😀索引合并是指 MySQL 查询优化器将多个单列索引合并成一个覆盖索引或多列索引来加速查询的过程

死锁发生场景及解决方案
- 当多个事务同时更新了索引所涉及的列时
  👉使用合适的事务隔离级别，如 READ COMMITTED 或者 SERIALIZABLE，以减少并发更新导致的死锁。
  👉尽量减少事务持有锁的时间，避免长时间持有锁，从而降低死锁的发生概率。
- 当一个事务持有锁的时间过长，其他事务无法获得所需的锁时
  👉尽量减少事务中的锁等待时间，尽快释放不必要的锁。
  👉对查询进行优化，尽量减少锁的竞争，避免长时间的锁等待。
- 在设计表结构时，选择合适的索引，避免不必要的索引合并。
  👉确保每个查询都能够有效地使用已经存在的单列索引或多列索引，而不需要进行额外的索引合并操作。

慢查询和优化

如何定位慢查询

①借助工具：Prometheus、Skywalking。以Skywalking为例，它可以对语句执行速度进行一个排序，还可以跟踪某条语句看它的执行情况
②MySQL慢日志查询：开启慢查询日志功能slow_query_log = 1，设置时间阈值long_query_time = 2【单位是秒】，执行时间超过这个时间阈值的SQL语句将被记录在慢日志中localhost_slow.log

如何分析/优化执行慢的语句

使用MySQL自带的EXPLAIN或者DESC分析

key/keylength：是否命中了索引，没有命中说明索引失效，需要去则去优化一下索引
type：出现 index 或 all 则需优化SQL语句
extra：出现 using index condition 说明产生了回表查询的情况，需要添加索引或者修改返回字段

type扫描类型（执行效率从低到高）
all（全表扫描） 👉 index（全索引扫描） 👉 range（索引范围扫描🎯） 👉 ref（非唯一索引扫描） 👉 eq_ref（唯一索引扫描） 👉 const（结果只有一条的主键或唯一索引扫描）

explain用到的索引不正确有什么方法干预？

使用force index走强制索引，比如

EXPLAIN SELECT name, price 
FROM products FORCE INDEX(idx_price) 
WHERE price BETWEEN 10 AND 20 ORDER BY price.

如何知道模糊查询没有走索引

使用EXPLAIN命令
- 执行EXPLAIN命令查看查询的执行计划。
- 分析EXPLAIN的输出，重点查看type、key、rows字段。
  - type: 如果type是ALL，说明查询执行了全表扫描，没有使用索引。如果是range、index，则可能使用了索引
  - key: 这个字段显示实际使用的索引名称。如果为空，说明没有使用索引
查看查询响应时间
- 如果模糊查询的响应时间很长，尤其是在大数据量的表上，可能意味着查询没有使用索引，导致全表扫描。
- 比如：查询name LIKE '%keyword%'的响应时间明显比查询name LIKE 'keyword%'长，通常表明%keyword%模式下没有使用索引。
查看数据库状态变量
- 使用SHOW STATUS命令查看数据库的状态变量，特别是Handler_read_rnd_next变量。如果该变量的值较高，表明数据库进行了大量的行扫描，可能是由于模糊查询没有使用索引导致的。
分析查询日志
- 配置MySQL慢查询日志，然后在日志中查找包含LIKE条件的查询，可以找到执行时间较长的模糊查询。如果日志中存在大量的慢查询，可能是由于这些查询没有使用索引。

SQL优化的经验

表优化
👉参考《阿里巴巴开发手册（嵩山版）》，设置合适的数值类型(tinyint int bigint)建表 | 设置合适的字符串类型建表(char vachar)
索引优化：使用explain命令分析SQL执行情况，找出慢查询的原因，比如是否使用了全表扫描、是否有索引未被利用等。创建合适的索引避免索引失效
👉选用需要频繁查询、排序、分页的字段创建索引；
👉字符串索引较长时根据字符串特点创建前缀索引；
👉尽量使用联合索引而不是单列索引；
👉尽量选择区分度较高的列创建索引，最好是唯一的；
👉索引下推
SQL语句优化
👉尽量避免selcect *这样的查询语句，而是查询具体字段【原因：1. 会返回表中所有的列，即使你并不需要所有列。这可能导致额外的网络传输开销和内存使用。当明确指定需要的列时，数据库可以通过查询优化器选择最优的执行计划，利用索引进行高效查询。而 SELECT * 可能无法使用最优索引，导致全表扫描，进而降低查询速度。2. 字段变更的隐式影响：如果表结构发生变化（比如添加、删除、修改列），使用 SELECT * 的查询可能会引发潜在问题。例如，添加了一个不需要的列，或者删除了查询依赖的列时，SELECT * 不会明确暴露出问题，容易导致隐式错误。3. 不能直接反映出查询所依赖的具体列，增加了代码的模糊性和可维护性问题。其他开发者或者将来维护代码的自己很难通过查询语句清晰地理解业务逻辑。】
👉在where子句中避免使用表达式操作字段
👉避免出现索引失效的写法
👉Joint联表时尽量使用内连接，大表放里面，小表放外面
👉针对limit分页的查询优化，可以把limit查询转换成某个位置的查询，如select * from tb_sku where id > 20000 limit 10 该方案适用于主键自增的表
主从复制、读写分离
👉复制一张数据库表作为从表，读数据时从从表读，写数据时从主表写，再同步到主表
【扩展】怎么同步？
👉核心是二进制文件（BINLOG）。主表中有一个BINLOG，当有写操作时，会将语句记录到BINLOG中。从表有一个IO线程读取这个BINLOG，并记录到从表的二进制文件RelayLog中。从表还有一个SQL线程去执行RelayLog中的语句，这样就完成了同步
分库分表：如果单表的数据超过了千万级别，考虑是都需要进行分解
👉垂直
分库：根据业务进行拆分 - 高并发下提高磁盘IO和网络连接数
分表：冷热数据分离 - 多表之间互不影响
👉水平
分库：一个库中的数据拆分到多个库 - 解决海量存储和高并发问题
分表：一个表中的数据拆分到多个表 - 解决但表存储和性能问题
缓存技术：加一层中间层，如Redis，存储热点数据和频繁查询的结果。但需要考虑一下缓存一致性的问题（比如对于读操作选择旁路缓存策略，写操作先更新db再删除缓存）

水平分库分表需要注意的关键点

数据分片策略：选择分片键时要确保其高区分度，以避免数据倾斜。同时分片策略要适合业务场景，并且考虑到未来数据增长的趋势。
- Hash 取模分片：通过某个字段的哈希值对表数量取模，将数据分布到不同的库或表中。这种方式分布均匀，但可能导致查询跨多个表或库，影响查询效率。
- 范围分片（Range-based Sharding）：根据字段值的范围分片，将数据按照一定范围分布到不同的库或表。查询效率较高，但可能导致数据倾斜。
- 时间分片：根据时间（如按月或按年）将数据分片，适用于时间序列数据，但需要处理好过期数据的归档或清理。
- 业务分片：根据业务字段（如用户ID、订单ID等）进行分片，可以避免跨库查询，但需要保证业务字段的均匀性，避免数据集中在某些库上。
全局唯一ID生成：分库分表后，单库内的自增ID可能不再适用，因为不同库/表的自增ID可能会重复。因此，需要使用全局唯一ID生成方案：
- UUID：虽然能保证唯一性，但字符串型 UUID 长度较大，不适合做主键。
- 雪花算法（Snowflake）：Twitter 开发的分布式 ID 生成算法，能够生成有序、唯一的 64 位整型 ID，适合高并发场景。
- 数据库主键生成表：通过一个独立的数据库表存储全局的自增主键值，但这种方式会增加数据库的单点压力。
- Redis：通过 Redis 实现分布式 ID 生成，能够避免数据库单点问题。
跨库跨表查询：分库分表后，查询可能需要访问多个库或表。跨库跨表查询会带来复杂性，影响查询性能。对于简单查询，尽量选择分片键以避免跨库查询。对于聚合查询，可以通过异步聚合、在应用层进行数据汇总，或使用中间件支持跨库查询。
- 聚合查询问题：如果查询需要在多个库/表中进行聚合操作（如 SUM、COUNT 等），数据库无法直接支持，需要通过应用层进行处理。
- 跨库事务问题：水平分库后，分布式事务会涉及多个数据库，常规的事务管理机制如 ACID（原子性、一致性、隔离性、持久性）可能无法直接保证一致性。需要使用分布式事务协议（如 2PC、TCC）或采用最终一致性策略。
- 数据路由：分片后查询需要先通过路由规则定位到具体的库或表，这会增加应用程序的复杂性。可以使用中间件（如 ShardingSphere、MyCAT）来帮助实现数据路由。
事务管理：分库分表后，事务的一致性和隔离性成为一大挑战。尽量将事务控制在单个库内，减少跨库事务。对非强一致性要求的场景，可以采用基于 MQ 的最终一致性方案。
- 单库内事务：同一库内的事务依旧可以通过数据库原生的事务机制进行管理。
- 分布式事务：跨库事务管理是复杂的，需要使用分布式事务解决方案，如：2PC（Two-phase Commit，二阶段提交协议）：保证事务的强一致性，但性能较差，延迟较高。TCC（Try-Confirm-Cancel）：适用于需要对并发和延迟有较高要求的场景，但实现较复杂。
- 最终一致性：通过消息队列等机制，保证在一段时间后数据最终达到一致状态，适合不需要强一致性的场景。
数据热点问题：是指某个分片存储了过多的数据或承担了过多的访问请求，导致负载不均衡，比如分片键选择不合理（如用户ID连续增长），容易导致某些分片的负载过高。又或者是高并发场景下的写入热点：如使用时间分片，在特定时间段内，某些分片会受到大量写入压力。建议是选择区分度高且符合业务场景的分片键，避免数据集中到某一分片。对于写入热点问题，可以通过缓存或预分片（将热点分散到多个表）来缓解。
分片扩展性：分库分表后，随着业务增长，可能需要进一步进行扩容，最好提前设计好分片扩展方案，避免单次分片承载过多数据。定期评估库表负载，并在负载临界点前进行分片扩容。
一致性和数据完整性：在分布式环境中，需要特别关注数据的一致性和完整性。可以使用分布式事务或者消息队列来确保数据同步，或者采用最终一致性的策略来保证数据的完整性。

事务ACID

原子性

👉是最小的不可分割的单位，一个事务中的语句要么同时成功要么同时失败

一条update是不是原子性的? 为什么?

是原子性，主要通过锁+undolog 日志保证原子性的
执行update的时候，会加行级别锁，保证了一个事务更新一条记录的时候，不会被其他事务干扰。事务执行过程中，会生成undolog，如果事务执行失败，就可以通过undolog日志进行回滚。

一致性

👉事务完成后，必须所有的数据都保持一致的状态

原子性和一致性是基于undo log保证的，undo log提供回滚和MVCC，记录的是相反的操作，用于事务回滚时提供逆操作。

隔离性

👉事务和事务之间是相互隔离的，不受外界并发影响
并发事务问题

脏读：一个事务读到了另外一个事务还没有提交的数据
不可重复读：一个事务先后读取同一条数据，但是两次读取的数据不同
幻读：一个事务按照条件查询时，没有对应的数据行，但在插入数据时，发现这行数据已经存在

隔离级别

读未提交：允许读取尚未提交的数据😶‍🌫️不能解决任何问题
读已提交：允许读取并发事务已经提交的数据😶‍🌫️可以解决脏读
可重复读【默认】：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改😶‍🌫️可以解决脏读、不可重复读
串行化：所有的事务依次逐个执行😶‍🌫️可以解决所有问题

隔离级别的实现

基于锁和MVCC机制
- 排他锁：一个事务获得了一个行的排他锁（S型的next-key锁），那么其他事务就获取不了了，也是串行化隔离级别的实现原理
- MVCC：多版本并发控制，指通过版本链维护一个数据的多个版本，使读写操作没有冲突

MVCC原理

对于读已提交和可重复读来说，是通过Read View实现的，区别在于创建Read View的时机不同。前者在每个select语句执行前都会重新生成一个Read View，后者在执行第一条select时，生成一个Read View，然后整个事务期间都在用这一个RV。

Read View中有四个重要字段
而InnoDB存储引擎的数据表，在它的聚簇索引记录中包含两个隐藏列
👉trx_id：当一个事务对某条聚簇索引记录进行改动时，就会事务id记录在trx. id里
👉roll pointer：每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到undo日志中，这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。
当创建了Read View后，trx_id中的字段就可以划分成三种情况

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

记录的trx_ id值 < Read View中的min_ trx_ id值，表示这个版本的记录是在创建Read View前已经提交的事务生成的，所以该版本的记录对当前事务可见。
记录的trx_ id值 ≥ Read View中的max_trx_id值，表示这个版本的记录是在创建Read
View后才启动的事务生成的，所以该版本的记录对当前事务不可见。
记录的trx_ id值在Read View的min_trx_ id和max_trx_id之间，需要判断trx_id是否在m_ids列表中：
- 如果在，表示生成该版本记录的活跃事务依然活跃着(还没提交事务)，所以该版本的记录对当前事务不可见。
- 不在，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

持久性

👉事务一旦提交或者回滚，对数据库的改变是永久的

redo log
- why redo log ❓ 在InnoDB引擎中的内存结构中，主要的内存区域就是缓冲池，在缓冲池中缓存了很多的数据页。当我们在一个事务中，执行多个增删改的操作时，InnoDB引擎会先操作缓冲池中的数据，如果缓冲区没有对应的数据，会通过后台线程将磁盘中的数据加载出来，存放在缓冲区中，然后将缓冲池中的数据修改，修改后的数据页我们称为脏页。而脏页则会在一定的时机，通过后台线程刷新到磁盘中，从而保证缓冲区与磁盘的数据一致。而缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，假如刷新到磁盘的过程出错了，而提示给用户事务提交成功，而数据却没有持久化下来，这就出现问题了，没有保证事务的持久性。
- redo log由两部分组成，一个是redo log buffer，它和缓冲池一样都存储在主内存中，另外一个是redo log file，它存储在磁盘中。当对缓冲区的数据进行增删改之后，会首先将操作的数据页的变化，记录在redo log buffer中。在事务提交时，会将redo log buffer中的数据刷新到redo log file中。过一段时间之后，如果刷新缓冲区的脏页到磁盘时，发生错误，此时就可以借助于redo log进行数据恢复，这样就保证了事务的持久性。而如果脏页成功刷新到磁盘或者涉及到的数据已经落盘，此时redo log就没有作用了，就可以删除了，所以存在的两个redo log文件是循环写的
  为什么明明都是同步，但是redo log的同步要比缓冲池与数据页同步的性能好呢？（为什么每一次提交事务，要刷新redo log 到磁盘中呢，而不是直接将buffer pool中的脏页刷新到磁盘呢）
  因为redo log的同步是一个顺序磁盘IO，而缓冲池和数据页同步是随机磁盘IO。顺序写的效率，要远大于随机写。这种先写日志的方式，称之为 WAL（Write-Ahead Logging）。
undo log
- 作用：提供回滚（保证原子性）和MVCC（多版本并发控制）
- 记录的是相反的操作，用于事务回滚时提供逆操作
- 比如delete时，记录一条对应的insert
- 销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。
- 存储：undo log采用段的方式进行管理和记录，存放在rollback segment 回滚段中，内部包含1024个undo log segment。

redo log 和 undo log的区别

他们都是MySQL的日志文件，但是用途不一样
redo log 记录的是事务提交时数据页的物理修改，用于实现事务的持久性
undo log 记录的是事务被修改前的信息，是逻辑日志，用于保证事务的原子性和一致性

事务的一些命令

开启事务：start transaction
提交事务：commit
回滚事务：rollback
查看事务隔离级别：SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别：SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

滥用事务、或者一个事务里有很多sql的弊端

如果一个事务特别多sql，锁定的数据太多，容易造成大量的死锁和锁超时。
回滚记录会占用大量存储空间，事务回滚时间长。在MySQLC 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值，sql 越多，所需要保存的回滚数据就越多。
执行时间长，容易造成主从延迟，主库上必须等事务执行完成才会写入binlog, 再传给备库。所以，如果一个主库上的语句执行10分钟，那这个事务很可能就会导致从库延迟10分钟

数据库引擎

InnoDB

支持事务：提供ACID（原子性、一致性、隔离性、持久性）属性，支持事务回滚和提交。
行锁：使用行级锁来提高并发性能，特别适合多用户环境中的高并发写操作。
外键：支持外键约束，确保数据的完整性和一致性。
崩溃恢复：通过重做日志（redo log）和撤销日志（undo log）实现崩溃后的数据恢复。
MVCC（多版本并发控制）：用于实现高效的并发访问，减少锁的争用。
使用场景：适合需要事务支持和数据一致性的通用场景，尤其适用于在线事务处理系统（OLTP，如银行系统中的账户查询和交易处理、电子商务平台的订单系统）。
👉索引结构：聚簇索引、必须有主键

MyISAM

不支持事务：无法进行事务回滚或提交。
表锁：使用表级锁，所有的读写操作都会锁住整张表，适合读多写少的场景。
不支持外键：无法维护表之间的参照完整性。
不具备崩溃恢复能力：在崩溃时，可能导致数据丢失或损坏。
使用场景：适用于对事务完整性要求不高但对读性能要求高的场景，如数据仓库和只读的分析系统（OLAP，如商业智能（BI）工具的数据分析和决策支持系统。）。
👉索引结构：非聚簇索引、数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的

Memory

数据存储在内存中：所有数据保存在内存中，访问速度极快。
数据持久性低：在服务器重启或崩溃时，所有数据都会丢失。
使用场景：适合小数据量、高读写性能要求的场景，如缓存表、临时数据存储。

NDB（Network Database）

支持分布式事务：能将数据分布在多个节点上，支持分布式事务。高可用性：具备高可用性和容错能力。使用场景：适用于分布式数据库场景，尤其是需要高可用性和快速响应的环境，如电信计费系统和银行交易系统。

SQL的一些基础语句

分组查询

• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

• 执行顺序：where > 聚合函数 > having
where与having区别
👉执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
👉判断条件不同：where不能对聚合函数进行判断，而having可以。
i.e. 查询入职时间在 ‘2015-01-01’ (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位

select job, count(*)
from tb_emp
where entrydate <= '2015-01-01'   -- 分组前条件
group by job                      -- 按照job字段分组
having count(*) >= 2;             -- 分组后条件

分页查询起始索引怎么计算

（查询页码 - 1）* 每页显示记录数

查询语句案例

select id, username
from tb_emp
where name like '张%' and gender = 1 and entrydate between '2000-01-01' and '2015-12-31'
order by update_time desc
limit 0 , 10;

增删改

insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);
delete from tb_emp where id = 1;
update tb_emp set name='张三',update_time=now() where id=1;

连接

内连接：返回两个表中符合连接条件的所有匹配记录。只有在两个表中都有匹配记录的行才会出现在结果中。

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

左外连接：返回左表中的所有记录，以及左表中所有匹配的记录。即使右表中没有匹配记录，左表中的记录也会被保留，右表的列将填充NULL。

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

右外连接：返回右表中的所有记录，以及右表中所有匹配的记录。即使左表中没有匹配记录，右表中的记录也会被保留，左表的列将填充NULL。

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

全连接：全连接返回两个表中的所有记录。如果没有匹配，则返回NULL。MySQL没有直接的全连接语法，但可以通过使用LEFT JOIN和RIGHT JOIN的UNION来实现。

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id
UNION
SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

查询不存在01课程但存在02课程的学生的成绩

学生表Student、课程成绩表Score

select s.sid, s.name, sc2.cid, sc2,name
from Student s
left join Score as sc1 on s.sid = sc1.sid and sc1.cid = '01'
left join Score as sc2 on s.sid = sc2.sid and sc2.cid = '02'
where sc1.cid is null and sc2.cid is not null;

select s.sid, s.name, sc.cid, sc.name
form Student s
join Score as sc on s.sid = sc.sid and sc.cid = '02'
where not exists(select 1 from Score sc1 where sc1.sid = s.sid and sc1.cid = '01' );

查询总分排名在5-10名的学生id及对应的总分

学生表student_score (stu_ id, subject_ id, score)

with StudentTotalScores as{
	select stu_id, sum(score) as total_score
	from student_score
	group by stu_id
},
RankedStudent as{
	select stu_id, total_score, rank() over(order by total_score desc) as ranking
	from StudentTotalScores
}
select stu_id, total_score
from RankedStudents
where ranking between 5 and 10

场景题

大量请求打到数据库

如果不用redis，直接把mysql暴露在外，这时有很多请求数据库怎么办

限流和负载均衡：可以使用限流和负载均衡技术来控制数据库的请求量，防止数据库被过多的请求压垮。可以使用反向代理服务器（如 Nginx）进行请求的负载均衡，将请求分发到多个数据库节点上，以提高并发处理能力。
可以考虑使用数据库中间件（如 MySQL Proxy、Cobar、MyCat 等）来作为数据库的代理层，对外提供服务，并提供连接池管理、负载均衡、读写分离等功能。
判断是不是有大量无效请求：
- 监控数据库负载：监控数据库的负载情况，包括 CPU 使用率、内存使用率、磁盘 I/O 等。如果数据库的负载突然增加，但是业务量没有相应增加，可能是因为存在大量无效请求。
- 分析访问日志，查看请求的来源、请求的内容、访问频率等信息。如果发现某些 IP 地址频繁访问相同的接口，但是没有实际业务逻辑支撑，可能是无效请求。
- 设置访问限制和阈值，限制单个 IP 地址的访问频率，或者设置最大连接数等。如果某个 IP 地址频繁超过了访问限制，可能是无效请求。

大量QPS直接打到数据库怎么保证数据库不会直接宕机

QPS 是指每秒钟的查询数量（Queries Per Second），用于衡量系统处理请求的能力。例如，一个 Web 服务器的 QPS 是 1000，则表示该服务器每秒钟能够处理 1000 个请求。

使用连接池来管理数据库连接，避免每次请求都建立新的数据库连接。连接池可以控制同时打开的连接数量，并且可以重用已经建立的连接，减少连接的开销和数据库的压力。
对数据库的配置进行优化，包括调整数据库的参数和缓冲区大小，以提高数据库的性能和稳定性。
将频繁访问的数据缓存到内存中，减少对数据库的访问次数。可以使用内存数据库（如 Redis）作为缓存，将热门数据缓存到内存中，从而减轻数据库的压力。
使用读写分离技术，将读操作和写操作分别路由到不同的数据库节点上。读操作可以路由到只读数据库节点，从而分担主数据库的压力，提高数据库的并发处理能力。
使用限流和负载均衡技术来控制数据库的请求量，防止数据库被过多的请求压垮。可以使用反向代理服务器（如 Nginx）进行请求的负载均衡，将请求分发到多个数据库节点上，以提高并发处理能力
垂直分片或水平分片，将数据库分成多个部分，并分别存储在不同的数据库节点上。这样可以将数据分散到多个节点上，减轻单个节点的压力。

建表考虑什么

建一张表会考虑什么，比如商品这张表，会怎么建索引
需要考虑的因素包括表的结构设计、字段类型、索引的设计、以及可能的查询和更新操作

表结构设计
主键：通常会有一个唯一标识商品的主键，如商品ID，可以是自增ID或UUID。
字段选择：包含商品的基本信息，比如：商品名称 (VARCHAR)、商品描述 (TEXT)、价格 (DECIMAL)、库存数量 (INT)、分类ID (关联到分类表)、创建时间、更新时间 (DATETIME)其他自定义属性（如品牌、规格等）
字段类型
确保选择合适的字段类型，以优化存储和查询性能。
例如，价格使用DECIMAL类型，数量使用INT，时间使用DATETIME。
索引设计
索引的设计与查询模式密切相关，需要平衡查询性能和写入性能。
主键索引：通常在商品ID字段上创建主键索引，它是唯一且快速的查询依据。
唯一索引：如果某些字段需要唯一性约束，比如商品的SKU编码，可以为其创建唯一索引。
普通索引：根据常见的查询条件创建索引：为商品名称、分类ID创建索引，以加速按名称和分类的查询。为价格创建索引，可以支持范围查询（如按价格区间筛选商品）。
复合索引：如果查询中经常涉及多个字段，可以创建复合索引。比如，如果查询经常按分类ID和价格进行过滤，可以创建(分类ID, 价格)的复合索引。
全文索引：如果需要对商品描述或名称进行全文搜索，可以考虑使用MySQL中的全文索引。
其他考虑
外键约束：如果有其他表如分类表或品牌表，可以考虑使用外键来保证数据一致性。
分区：如果数据量非常大，可以考虑对表进行分区，按时间、ID区间或其他策略分区。
性能优化：考虑商品表的读写比例，使用合适的缓存机制（如Redis）以减轻数据库负担。
数据安全和备份：配置备份策略，以确保数据的安全性。

MySQL的超大分页是什么，怎么解决

👉是指在数据量很大的时候，还需要用到limit分页查询的情况

使用覆盖索引（Covering Index）优化分页。仅在分页查询中选择索引列，而不是 SELECT *。例如，如果你按主键 ID 分页，可以这样优化SELECT id, title FROM articles WHERE ... ORDER BY id LIMIT 10000, 10;MySQL 会从索引中扫描直接获取需要的列，而不需要回表极大减少 I/O 开销。
记住上一次查询的游标。基于该游标查询下一页结果，如SELECT id, title FROM articles WHERE id > last_id ORDER BY id LIMIT 10;
👉用子查询+覆盖查询解决。第一步：首先通过一个简单的索引查询（例如只查询主键 ID 或部分列），快速获取分页需要的主键 ID 列表。第二步：再基于这些主键 ID 进行子查询，返回完整的数据。

-- 第一步，使用覆盖索引查询 ID 列表
SELECT id FROM articles WHERE some_condition ORDER BY id LIMIT 100000, 10;

-- 第二步，使用子查询查询完整数据
SELECT * FROM articles WHERE id IN (SELECT id FROM articles WHERE some_condition ORDER BY id LIMIT 100000, 10);

MySQL如何避免重复插入数据

使用主键或唯一约束：在表的设计阶段，通过为字段设置主键或唯一约束UNIQUE来防止重复数据插入。如果尝试插入重复的数据，MySQL会返回一个错误。

# 假设有一个users表，其中email字段需要是唯一的。
CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(50) UNIQUE
);

使用 INSERT IGNORE：如果数据违反了唯一约束（如主键或唯一索引），则会忽略该条插入操作并继续执行，不会抛出错误。
```
INSERT IGNORE INTO users (username, email) VALUES ('Alice', 'alice@example.com');
```
如果表中已经存在alice@example.com，则该行插入操作将被忽略。
使用 REPLACE INTO：如果记录存在（根据主键或唯一索引判断），MySQL将先删除旧记录，然后插入新记录。
```
REPLACE INTO users (user_id, username, email) VALUES (1, 'Bob', 'bob@example.com');
```
如果表中存在 user_id = 1 或 email = ‘bob@example.com’ 的记录，MySQL将删除旧记录并插入新记录。
使用 INSERT ... ON DUPLICATE KEY UPDATE：允许在插入重复键（如主键或唯一索引）时更新现有记录的某些字段，而不是插入新记录。
```
INSERT INTO users (username, email) VALUES ('Charlie', 'charlie@example.com')
ON DUPLICATE KEY UPDATE username = VALUES(username);
```
如果email字段存在重复，该语句不会插入新记录，而是更新现有记录的username字段。

使用事务和锁定：在某些情况下，可以使用事务和表锁定来确保数据不重复插入。例如，在插入前检查是否存在数据，如果不存在则执行插入操作。

START TRANSACTION;

-- 检查是否已经存在相同的email
SELECT COUNT(*) INTO @count FROM users WHERE email = 'david@example.com';

-- 如果不存在，则插入
IF @count = 0 THEN
  INSERT INTO users (username, email) VALUES ('David', 'david@example.com');
END IF;

COMMIT;

MySQL 数据库宕机后故障恢复的过程

检查备份文件：确认是否有最新的数据备份文件（如 mysqldump 文件、冷备份文件等）。
查看日志文件：
- 错误日志：位于 MySQL 数据目录（通常是 /var/lib/mysql/）中的 error.log 文件，检查是否有与宕机相关的错误信息。
- 二进制日志（binlog）：记录了所有修改数据的 SQL 语句，可以用于恢复到最新状态。
- 重做日志（redo log）：InnoDB 引擎用来记录事务日志，保证事务的持久性。
- 回滚日志（undo log）：用于回滚未提交的事务，保证数据的一致性。

根据具体的情况，选择合适的恢复方法：

从备份文件恢复：如果有完整的备份文件，先清空或删除损坏的数据库文件，再从备份恢复数据，如果有二进制日志文件，可以将其应用到备份文件，以恢复到最新状态。

   rm -rf /var/lib/mysql/* #清空
   mysql -u root -p < backup.sql # 恢复
   mysqlbinlog binlog.000001 | mysql -u root -p #恢复二进制

使用重做日志和回滚日志恢复

如果使用的是 InnoDB 引擎，MySQL 可以自动使用重做日志和回滚日志来恢复未提交的事务：启动 MySQL 服务，InnoDB 会自动进行崩溃恢复（Crash Recovery）。启动过程中观察错误日志（error.log），查看恢复的进度和状态，如果日志显示 “InnoDB: Starting crash recovery”，表示 MySQL 正在进行崩溃恢复。

sudo systemctl start mysql #启动，或者用sudo service mysql start

具体过程
😀 重做阶段（Redo Phase）：目的是重做所有已提交事务的更改，保证所有已提交事务的数据持久化。具体过程如下：InnoDB 从磁盘上读取重做日志文件（如 ib_logfile0、ib_logfile1 等），从重做日志的 checkpoint（检查点）位置开始，向前扫描日志，直到日志的最末端。Checkpoint 是重做日志中的一个标记点，表示数据页最近一次同步到磁盘的时间点。对于重做日志中记录的每一条操作，检查该操作是否已持久化到数据文件。如果没有，则根据重做日志中的信息，将该操作重新应用到数据文件中。每条记录的写入日志顺序号（LSN，Log Sequence Number）比检查点大的记录被重做。（重做的过程可以并行化，以提高性能。）
😀回滚阶段（Undo Phase）：目的是回滚所有未提交的事务，保证数据的一致性。具体过程如下：InnoDB 会根据回滚日志（Undo Log）和事务信息，找到所有在崩溃时尚未提交的事务。事务状态存储在 InnoDB 的事务系统表中，重做日志中也有标记事务的状态。从回滚日志中读取未提交事务的修改记录，根据这些记录撤销对数据文件的更改。回滚过程是从回滚日志的最新记录开始，按照逆序撤销数据操作。每个回滚操作会将数据页恢复到修改前的状态。（回滚操作可以并发执行，以提高恢复效率。）

数据库宕机恢复后应该做什么

修复和检测数据库的完整性：使用 mysqlcheck 或 CHECK TABLE 命令检测和修复损坏的表。

mysqlcheck -u root -p --auto-repair --check --all-databases
mysql -u root -p -e "CHECK TABLE your_table_name"

修复损坏的表：对于 MyISAM 表，可以使用 REPAIR TABLE 命令。

REPAIR TABLE your_table_name;

备份恢复后的数据：立刻创建新的数据备份，以防止再次出现问题。
优化和调整 MySQL 配置：根据宕机原因，调整 MySQL 配置文件（my.cnf 或 my.ini），如调整 innodb_buffer_pool_size、max_connections、innodb_log_file_size 等参数，防止再次发生类似问题。
监控 MySQL 性能：使用监控工具（如 mysqladmin status、SHOW PROCESSLIST）检查 MySQL 性能，防止负载过高导致再一次宕机。
建立更完善的备份和灾难恢复机制：【定期备份】使用 mysqldump、mysqlpump、或者 Percona XtraBackup 等工具，定期进行全量备份和增量备份。【主从复制或居群架构】

下列联合索引语句是否失效

在这里插入图片描述

数据库分库分表，多大规模分，以及如何路由

😀 分库分表的时机取决于具体的业务需求和数据库的负载情况，一般考虑以下几个因素：

单表数据量大小：
- 百万级：通常不需要分库分表，索引和查询优化即可。
- 千万级：如果查询和写入压力较大，可以考虑分表（水平或垂直拆分）。
- 亿级或以上：需要分库分表以降低单表的压力和改善数据库性能。
数据库磁盘容量限制：
- 数据库服务器的磁盘容量有限，如果单库数据量增长过大（例如超过 100GB - 500GB），需要分库以减轻单库存储压力，避免 IO 瓶颈。
业务需求和性能指标：
- 高并发：在高并发场景下，需要分库分表来分散数据库写入压力，提升整体吞吐量和响应速度。
- 业务模块隔离：按业务模块（如订单、用户、库存）将数据分库，可以实现更好的模块隔离和业务解耦。
单库最大连接数限制：
- 当单个数据库实例的连接数接近上限（如 MySQL 默认的 151 个连接）时，可以通过分库来降低单实例的连接压力。

更具体的考虑步骤和因素：

评估当前数据规模和增长速度：了解当前数据库的表数据量，分析每天、每小时新增的数据量，以及未来一段时间的增长趋势。明确业务场景下数据的冷热分布情况（比如用户数据、订单数据等），识别哪些表的数据增长速度快且访问频繁。了解业务的高峰访问期，比如每天的某个时段或特定活动期间的流量，以确定系统所需的最大承载能力。
确定数据库分库分表的阈值
硬件配置：假设使用一台配置为32核CPU，128GB内存，1TB SSD存储的服务器。通常在单表数据量达到500万~1000万条记录时，数据库查询性能会开始明显下降，因此可以考虑分库分表。
TPS/QPS：监控数据库的每秒事务数（TPS）和每秒查询数（QPS）。如果TPS/QPS持续接近服务器的硬件限制（比如SSD的读写IOPS达到极限），就需要分库分表。
选择分库分表策略
具体服务器配置和路由策略：假设有3台数据库服务器，每台服务器配置为64核CPU，256GB内存，2TB NVMe SSD存储。可以考虑在每台服务器上运行一个MySQL实例，分布处理不同的分库。路由层设计：使用数据库中间件（如MyCat、ShardingSphere），在业务应用层面实现路由策略。具体实现包括：分库分表规则：基于user_id进行水平分表时，可以采用user_id % 3决定数据路由到哪台服务器，然后再根据user_id % 10决定插入到哪张表中。缓存策略：在应用服务器上引入Redis缓存，用于存储热门数据，减少数据库的直接访问压力。
高可用和容灾设计：主从复制：每台数据库服务器配置一主多从架构，比如一主两从，分别部署在不同的物理机房或可用区，保证数据安全。自动故障转移：结合MHA（Master High Availability）等工具，实现主从自动切换和快速故障恢复，确保分库分表后的高可用性。

😀数据库的分库分表方案可以分为水平分库分表和垂直分库分表两种常见方式。

垂直分库分表（按字段或业务模块拆分）
- 垂直分表（按字段拆分）：根据表中的字段进行拆分，将一个表拆成多个表。例如，将用户表 user 按照基础信息表 user_info 和账户信息表 user_account 进行拆分。
  👉适用场景：表中的字段较多，有些字段经常被访问，而有些字段访问频率较低时；可以将高频字段和低频字段分开。
  👉优点：减少单表宽度，减少 I/O 操作，提升查询效率；便于缓存和热点数据的控制。
  👉缺点：每次查询可能需要联表操作，增加了系统复杂度。
- 垂直分库（按业务模块拆分）：按照业务模块进行拆分，例如用户模块、订单模块、商品模块等分布到不同的数据库实例上。
  👉适用场景：系统业务模块清晰，模块间耦合度低时。
  👉优点：不同模块可以独立扩展，互不影响；易于根据业务划分开发和维护团队。
  👉缺点：数据库数量增多，增加了运维管理成本；跨库事务处理较复杂。
水平分库分表（按数据范围拆分）
- 水平分表（按行拆分）：将数据按某种规则（如 ID、时间、地理位置等）分散到多张表中，例如将用户数据按 ID 进行哈希取模后，分散到多个表 user_001, user_002, …。
  👉适用场景：单表数据量过大（如超过 1000 万条记录）。
  👉优点：降低单表的数据量，提高查询效率；减少单表的写入压力。
  👉缺点：跨分片查询复杂；需要额外的分表逻辑和路由机制。
- 水平分库（按行拆分）：类似水平分表，将数据按某种规则分散到多个数据库实例中，例如按用户 ID 哈希取模，将数据分散到 db_001, db_002, …。
  👉适用场景：单库数据量大，单库并发高时。
  👉优点：降低单库数据量和压力，提升数据库性能；方便数据库集群扩展。
  👉缺点：跨库事务处理复杂；增加运维和管理成本。

😀 分库分表后的路由策略
分库分表之后，系统需要通过某种策略来找到数据所在的具体库和表，这就涉及到数据路由。常见的路由策略有以下几种：

哈希取模（Hash Mod）：根据某个字段（如用户 ID、订单 ID 等）的哈希值对分片数量取模。例如，将用户 ID 进行哈希运算后取模，结果为 0 的存入 user_0，结果为 1 的存入 user_1。
👉优点：数据分布均匀，路由计算简单。
👉缺点：扩容不方便（需要重新计算哈希值并迁移数据）；对数据访问热点控制较差。
范围分片（Range Partition）：根据某个字段的值范围进行分片，例如将用户 ID 小于 10000 的数据存入 user_001，10000 到 20000 的存入 user_002，以此类推。
👉优点：扩展方便，可以根据数据增长情况扩展新表或库；容易定位分片。
👉缺点：数据分布可能不均匀，导致某些分片压力大；需要定期调整分片规则。
映射表（Lookup Table）：维护一张映射表来存储分库分表的路由信息。例如在中心路由表中记录每个用户 ID 对应的数据所在库和表。
👉优点：灵活性高，可以根据实际需要动态调整分片策略；容易扩展和管理。
👉缺点：需要维护映射表，可能存在单点问题和性能瓶颈；更新操作复杂。
一致性哈希（Consistent Hashing）：将所有数据库和数据分布在一个哈希环上，数据的哈希值决定其存储位置；当扩容或缩容时，只需要调整少量数据。
👉优点：扩展性好，扩容时影响较小；数据均匀分布。
👉缺点：实现复杂；可能有数据倾斜问题。
中间件或代理层：使用数据库中间件（如 MyCat、ShardingSphere）或代理层（如 Vitess、ProxySQL）来自动处理路由、分库分表、分布式事务等复杂逻辑。
👉优点：屏蔽底层的分库分表细节，对应用程序透明；支持多种路由规则和策略。
👉缺点：引入中间层可能增加延迟；需要额外的配置和维护。

JDBC

用途：建立数据库连接、执行 SQL 语句、提供了处理和操作从数据库返回的结果集的方法。除此以外支持一些事务管理，包括数据库事务的提交、回滚等操作，确保数据的一致性和完整性。访问数据库元数据，如表结构、支持的 SQL 功能、数据库的限制等。
体系结构：
- DriverManager（驱动管理器）：负责加载和管理数据库驱动程序。它用于建立与数据库的连接，并对从数据库获取的连接进行管理。
- Driver（驱动程序）：每个数据库厂商都需要提供特定的 JDBC 驱动程序，它是实现 java.sql.Driver 接口的类，用于将 JDBC API 调用转换为数据库特定的调用。
- Connection（连接）：代表一个与数据库的连接。通过这个连接，可以执行 SQL 查询和更新操作。
- Statement（语句）：用于发送 SQL 语句到数据库，包括 Statement、PreparedStatement 和 CallableStatement 等。它们分别用于执行普通 SQL 语句、预编译的 SQL 语句（带参数化）、以及调用存储过程。
- ResultSet（结果集）：保存从数据库查询返回的数据。ResultSet 提供了多种方法来迭代和访问结果集的数据。
- SQLException（异常处理）： JDBC 提供了 SQLException 类来处理数据库操作中可能发生的异常。

工作原理

加载驱动程序：在 Java 程序中使用 Class.forName(“com.database.Driver”) 来加载数据库的 JDBC 驱动程序类。
建立数据库连接：使用 DriverManager.getConnection(url, user, password) 获取 Connection 对象，建立与数据库的连接。
创建 SQL 语句：使用 Connection 对象创建 Statement、PreparedStatement 或 CallableStatement 对象。
执行 SQL 语句：使用 Statement 或 PreparedStatement 对象执行 SQL 查询或更新操作。
处理结果集：通过 ResultSet 对象处理查询结果。
关闭连接：操作完成后，关闭 ResultSet、Statement 和 Connection 对象，释放资源。

public class JdbcExample {
    public static void main(String[] args) {
        // 数据库连接信息
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String user = "root";
        String password = "password";

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            // 1. 加载JDBC驱动程序
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2. 建立数据库连接
            connection = DriverManager.getConnection(url, user, password);

            // 3. 创建Statement对象
            statement = connection.createStatement();

            // 4. 执行SQL查询
            resultSet = statement.executeQuery("SELECT * FROM users");

            // 5. 处理结果集
            while (resultSet.next()) {
                System.out.println("User ID: " + resultSet.getInt("id"));
                System.out.println("User Name: " + resultSet.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                // 6. 关闭资源
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

JDBC怎么开启事务

获取数据库连接：通过 DriverManager 或数据源（如 DataSource）获取数据库连接。

Connection connection = DriverManager.getConnection(url, user, password);

关闭自动提交模式：默认情况下，JDBC 的连接是自动提交模式（auto-commit mode），每条 SQL 语句在执行后会立即提交。要开启事务管理，需要先将自动提交模式关闭。

connection.setAutoCommit(false);

执行 SQL 操作

在事务中执行多个 SQL 语句操作，例如插入、更新或删除。

try {
    // 执行SQL操作
    Statement statement = connection.createStatement();
    statement.executeUpdate("INSERT INTO table_name (column1, column2) VALUES (value1, value2)");
    statement.executeUpdate("UPDATE table_name SET column1 = new_value WHERE condition");

    // 如果所有操作成功，提交事务
    connection.commit();
} catch (SQLException e) {
    // 如果发生异常，回滚事务
    connection.rollback();
    e.printStackTrace();
} finally {
    // 关闭连接
    connection.close();
}