专业技能（挖坑填坑）——MYSQL的索引、日志、事务、存储引擎、锁机制等相关原理

熟悉MySQL的使用，熟悉MYSQL的索引、日志、事务、存储引擎、锁机制等相关原理。

1.mysql索引，索引的底层数据结构实现

索引就是目录可以帮助快速的找到对应的资源。
MySQL默认的InnoDB存储引擎使用的索引底层数据结构是B+Tree。B+Tree是一种多路搜索查找树。B+Tree其实是B-Tree的一种优化。
标准回答流程：
索引的底层实际上是一个B+Tree。
起初底层数据是以链表形式存储，基于这个数据的主键进行排序。
为了提高查询效率，MySQL创建分页管理，它是基于B+Tree的结构进行存储，对数据一页一页的存储，默认每页的大小为16KB，因为InnoDB默认的存储引擎存储的是16KB。
查找的时候，先在页目录上查找每页的主键值，然后找到指定页，查找数据。
B+Tree 只有叶子结点才存储数据，非叶子结点不存储数据。
一页大概存储1K个键值。一个深度为3的B+Tree可以维护10亿条记录。
一般的项目就2层足够。基于主键去查的话最多动用一个I/O，因为它的顶层是常驻内存的。建表的时候一般主键使用INT类型，方便插入数据的时候底层进行排序。

（1）索引的基本操作（主键索引，普通索引，组合索引，唯一索引）

①索引的优点

    大大加快了数据的查询速度。

②索引的缺点

    1.维护索引需要耗费数据库的资源2.索引需要占用磁盘空间3.当对表的数据进行增删改的时候，增删改的速度会变慢，增删改对底层数据结构进行排序。因为要维护索引，速度会受到影响。

③索引的分类

a.主键索引
数据库表中设定主键后，数据库会自动创建主键索引，在InnoDB引擎中主键索引也称之为局促索引。
【注：也就是说，在创建以下索引表的同时，主键索引也总是被默认创建了。】

b.单值索引（普通索引）
即一个索引只包含单个列，一个表可以有多个单列索引。
共有2种方式：创建表的时候创建，创建表后进行创建索引

--创建普通索引，共有2种方式：创建表的时候创建，创建表后进行创建索引
CREATE INDEX name_index ON t_user(`name`);--创建表的时候构建索引
CREATE TABLE t_user1(`id` VARCHAR(20) PRIMARY KEY,`name` VARCHAR(20),KEY(`name`)
);
-- 索引的名默认是字段名
SHOW INDEX FROM t_user1;

c.唯一索引
索引列的值必须唯一，但允许有空值。

--创建唯一索引:唯一索引允许为null，只允许一个值为null
CREATE TABLE t_user2(`id` VARCHAR(20) PRIMARY KEY,`name` VARCHAR(20),UNIQUE(`name`)
);SHOW INDEX FROM t_user2;

d.组合索引（复合索引、联合索引）
即一个索引包含多个列。

--创建复合索引（组合索引）
CREATE TABLE t_user3(id VARCHAR(20) PRIMARY KEY,`name` VARCHAR(20),age INT,KEY(`name`,age)
);
-- 创建复合索引，
CREATE INDEX nameageindex ON t_user3(`name`,`age`);SHOW INDEX FROM t_user3;

最左前缀
MySQL组合索引“最左前缀”的结果。
简单的理解就是只从最左面的开始组合。组合索引的第一个字段必须出现在查询组句中，并且不能跳跃，这个索引才会被用到。

（2）MySQL索引为什么会失效？

索引失效的情况主要是针对联合索引（组合索引）。普通的B+Tree是一个结点只有一个值，联合索引对应的多路搜索树的结点是多个键值。
使用索引进行查询的时候必须遵循的是最左匹配原则。所谓最左匹配原则，就是查询的条件属性必须从索引的最左边开始，中间不能有间隙。
本质就是因为：B+树是有顺序的，如果没有遵循B+Tree的顺序法则，查询的是无序元素，就无法使用索引，只能扫描全表。

失效的3中情况：
（1）以（a,b）建立索引表为例，B+树中，a是有序的，在a相等的情况下b是有序的，但单看b是无序的，跳着查b就是不遵循做前缀法则，这种情况，只能进行全表扫描。
（2）当出现><范围查询时，索引失效。比如当满足a>1的范围时，b是无序的，此时是一个无序的b+树，索引失效，只能进行全表扫描。(2,1),(2,2,),(3,2)(4,1)
（3）where a like “%%”，以%为开头的查询条件索引会失效。比如%a就是没遵守前缀法则，没有顺序，索引失效。

2.为什么加索引可以加快扫描范围

1.减少数据扫描量

• 没有索引就要逐行扫描整个库，知道知道符合条件的数据，非常耗时。
• 索引通过创建有序的数据结构，使得数据库可以直接定位到范围的起点和终点，然后扫描其中数据即可，大大减少了扫描量提高了查询效率

2.优化数据检索路径

• 索引建立的有序的数据结构，使得数据检索路径更加高效。例如：B树索引是一种平衡树结构，能够在对数时间复杂度内完成数据检索。进行范围查询是，数据库可以利用B树的多级节点跳跃，迅速定位到目标数据行，无需逐行扫描。
• 哈希索引可以通过哈希函数快速定位数据，虽然它更适用于等值查询，但在某些特定场景下也能对范围查询提供一定的优化效果。

3.提高缓存命中率

• 数据库系统通常使用内存缓存来存储经常访问的数据页，以减少磁盘I/O。索引比原始数据表小得多，可以更多的被缓存。

3. 查数据库响应慢怎么排查

1.初步定位问题：

• 利用**应用日志、数据库日志，慢查询日志来初步定位问题，**检查是否有异常错误日志或网络延迟.
• 询问用户具体使用场景和出现问题的小操作，以缩小问题范围

2.深入分析SQL查询：

• 执行计划分析：使用 EXPLAIN或EXPLAIN ANALYZE 来分析慢查询SQL的执行计划
• 索引优化：根据查询条件和排序需求，检查关键字段上是否存在合适索引，没有就考虑添加索引。并确保索引的维护是定期的。
• SQL重构：简化复杂查询逻辑，避免不必要的子查询和多层嵌套

3.系统资源检查

• 检查CPU和内存使用情况，如果资源利用率，考虑增加硬件资源或优化数据库配置
• 检查磁盘I/O，如果性能不足，考虑升级存储设备或优化数据库存储结构

4.网络和配置检查

• 检查数据库服务器与应用服务器间的网络延迟，看是否有网络问题影响数据库响应速度
• 检查数据库配置比如缓存大小、连接数限制、事务隔离级别等。根据实际情况调整配置参数，以优化数据库性能

5.高级优化策略

• 对于读多写少的场景，考虑读写分离，将读请求分散到多个库上，以减轻主库的压力。
• 单个数据库的数据量过大，导致查询性能下降，考虑进行分库分表，以提高查询效率。
• 热点数据，使用缓存技术（Redis等）来减少数据库访问压力

6.持续监控和优化

• 简历长期性能监控机制，定期审查数据库状态和性能指标
• 定期对数据库进行审查优化，包括索引维护、查询优化、配置调整等、

3.1查询长时间不返回可能是什么原因？应该如何处理？

查询速度慢的原因很多，常见如下几种：

• 1、先利用应用日志，慢查询日志，初步定位哪个部分耗时，缩小问题范围

• 1、查询字段没有索引或者没有触发索引查询，没有触发索引查询的情况如下：

  以 % 开头的 like 查询不会使用 b-tree 索引；几个查询条件不符合最左前缀原则，导致索引失效，直接扫描全表；
  没有索引则考虑添加索引，并定期维护，更新。

• 2、简化复杂查询逻辑，避免不必要的子查询和多层嵌套。

• 3、 I/O 压力大，读取磁盘速度变慢。

• 4、内存不足，考虑增加硬件

• 5、网络速度慢 ，提升网速，升级带宽。

• 6、查询出的数据量过大，可以采用多次查询或其他的方法降低数据量

• 7、死锁，一般碰到这种情况的话，大概率是表被锁住了，可以使用 show processlist;命令，看看 SQL 语句的状态，再针对不同的状态做相应的处理。设置死锁的超时时间，限制和避免死锁消耗过多服务器的资源。

4.MySQL 有哪些重要的日志文件

MySQL 中的重要日志分为以下几个：
1、 错误日志：用来记录 MySQL 服务器运行过程中的错误信息，比如，无法加载 MySQL 数据库的数据文件，或权限不正确等都会被记录在此。默认情况下，错误日志是开启的，且无法被禁止。
2、查询日志：在 MySQL 中被称为 general log(通用日志)，查询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录。在并发操作非常多的场景下，查询信息会非常多，那么如果都记录下来会导致 IO 非常大，影响 MySQL 性能，因此如果不是在调试环境下，是不建议开启查询日志功能的。
3、慢日志： 记录下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库性能。默认情况下，慢查询日志是不开启的，手动开启：set global slowquerylog='ON';
4、redo log（重做日志）：为了最大程度的避免数据写入时，因为 IO 瓶颈造成的性能问题，MySQL 采用了这样一种缓存机制，先将数据写入内存中，再批量把内存中的数据统一刷回磁盘。
5、undo log（回滚日志）：用于存储日志被修改前的值，从而保证如果修改出现异常，可以使用 undo log 日志来实现回滚操作。

undo log 和 redo log 记录物理日志不一样，它是逻辑日志，
可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，
反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录，
当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。

6、bin log（二进制日志）：二进制文件，主要记录所有数据库表结构变更。记录了对 MySQL 数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录 SELECT、SHOW 等那些不修改数据的 SQL 语句。binlog 默认是关闭状态，在配置文件中开启。

4.2 redo log 和 binlog 的区别：

redo log（重做日志）和 binlog（归档日志）都是 MySQL 的重要的日志，它们的区别如下：

• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”。
• binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

4.3 redo log 和 binlog 是怎么关联的：

redo log和binlog一起处理恢复崩溃。

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

• 如果碰到既有 prepare（？）、又有 commit 的 redo log，就直接提交；
• 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

这篇博客里介绍了一些面试问答挺详细 MySQL 面试题合集（日志篇）

4.4 Undo Log 如何保障事务的原子性呢？

具体的方式为：在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为 Undo Log），然后进行数据的修改。如果出现了错误或者用户执行了 Rollback 语句，系统可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。

4.5 Redo Log如何保障事务的持久性呢？

具体的方式为：Redo Log 记录的是新数据的备份（和 Undo Log 相反）。在事务提交前，只要将 Redo Log 持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是 Redo Log 已经持久化。系统可以根据 Redo Log 的内容，将所有数据恢复到崩溃之前的状态。

4.6 将MySQL 的数据恢复到过去某个指定的时间节点

只要你备份了这段时间的所有 binlog，同时做了全量数据库的定期备份，就可以恢复。
把之前备份的数据库先还原到测试库，从备份的时间点开始，将备份的 binlog 依次取出来，重放到你要恢复数据的那个时刻，这个时候就完成了数据到指定节点的恢复。
（比如：今天早上 9 点的时候，你想把数据恢复成今天早上 6:00:00 的状态，这个时候你可以先取出今天凌晨（00:01:59）备份的数据库文件，还原到测试库，再从 binlog 文件中依次取出 00:01:59 之后的操作信息，重放到 6:00:00 这个时刻，这就完成了数据库的还原。

5.MYSQL存储引擎

存储引擎是一个数据的存储方式、检索方式、与其他数据库功能交互方式。mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。常见的MySQL存储引擎包括InnoDB、MyISAM、Memory、Federated等。

存储引擎是基于表的，不是基于数据库，MYSQL采用的是插件式架构，支持多种存储引擎，甚至可以为不同的数据库表设置不同的存储引擎以适应不同的场景需要。

知道那些存储引擎

InnoDB存储引擎是最常用的事务型引擎，是MySQL的默认存储引擎，它提供了事务安全（ACID兼容）、行级锁定和外键约束等高级功能。

1. 特点：
   事务安全（ACID兼容）：InnoDB通过事务来确保数据的完整性和一致性。它支持事务的原子性、一致性、隔离性和持久性（ACID属性）。
   行级锁：与MyISAM的表级锁定不同，InnoDB采用行级锁定，这提高了并发性能，减少了锁冲突。
   外键约束：InnoDB支持外键约束，有助于维护数据的引用完整性。通过外键，可以确保相关数据之间的正确关系。
   崩溃恢复：InnoDB具有强大的崩溃恢复能力，可以在数据库意外关闭后恢复数据。
   MVCC（多版本并发控制）：InnoDB使用MVCC技术，允许多个读者和写者并发访问数据，而不会相互干扰。
   聚集索引：InnoDB的表数据按主键的顺序存储，这称为聚集索引。这种存储方式有助于快速检索数据。
2. 使用场景：
   需要事务支持、高并发读写、数据完整性和引用完整性要求较高的场景，如电子商务网站，金融交易，订单管理等。

MyISAM 存储引擎：最常用的非事务型存储引擎，MySQL以前的默认引擎；供了全文索引和压缩功能，适用于主要进行读取操作，不需要事务支持，对存储空间有一定要求的场景如内容管理系统、日志记录。
MEMORY：这是一种易失性非事务型存储引擎，不支持事务，适用于需要快速响应的场景如在线游戏、实时统计。

InnoDB 使用的是哪种隔离级别？

InnoDB 默认使用的是可重复读隔离级别。

6.锁机制

见博客 并发性事务带来的问题+并发控制(MySQL事务隔离级别、MySQL 锁)

7.SQL 语言：完整性约束

数据库的完整性是指数据库正确性和相容性，是防止合法用户使用数据库时向数据库加入不符合语义的数据。保证数据库中数据是正确的，避免非法的更新。数据库完整性重点需要掌握的内容有：完整性约束条件的分类、完整性控制应具备的功能。

完整性约束类别大概分为实体完整性约束、参照完整性约束、自定义完整性约束三类。

• 实体完整性约束：非空值约束（NOT NULL）、默认值（DEFAULT）、唯一性约束（UNIQUE、UNIQUE(列名)）、主键约束（PRIMARY KEY 、PRIMARY KEY(列名)）。
• 参照完整性约束：外键约束（REFERENCES 表名(列)、FOREIGN KEY(列名) REFERENCES 表名(列)）。
• 用户定义完整性约束：约束表达式（CHECK）。

8.表结构设计相关

主键约束和唯一约束的区别

主键约束（PRIMARY KEY），唯一性约束（UNIQUE）
相同点：
他们都属于实体完整性约束。（都不可重复）

不同点：
唯一性约束所在的列允许空值 但是主键约束所在的列不允许空值
可以把唯一性约束放在一个或者多个列上 这些列或者列的组合必须是唯一性的 唯一性约束所在的列并不是表的主键列
唯一性约束强制在指定的列上创建一个唯一性索引 在默认情况下 创建唯一性的非聚簇索引 但是 也可以指定所创建的索引是聚簇索引

为什么要尽量设定一个主键？

主键是数据库确保数据行在整张表唯一性的保障，即使业务上本张表没有主键，也建议添加一个自增长的 ID 列作为主键。设定了主键之后，在后续的删改查的时候会更加快速以及确保操作数据范围安全。

主键使用自增 ID 还是 UUID？

推荐使用自增ID，因为UUID 在数据量较大的情况下，其效率直线下滑。

（1）使用自增长做主键的优点：
1、很小的数据存储空间
2、性能最好
3、容易记忆

（2）使用自增长做主键的缺点：
1、如果存在大量的数据，可能会超出自增长的取值范围
2、很难（并不是不能）处理分布式存储的数据表，尤其是需要合并表的情况下
3、安全性低，因为是有规律的，容易被非法获取数据

（3）使用UUID做主键的优点：
1、它是独一无二的，出现重复的机会少
2、适合大量数据中的插入和更新操作，尤其是在高并发和分布式环境下
3、跨服务器数据合并非常方便
4、安全性较高

（4）使用UUID做主键的缺点：
1、存储空间大（16 byte），因此它将会占用更多的磁盘空间
2、会降低性能
3、很难记忆

（5）那么一般情况下是如何选择的呢？
1、项目是单机版的，并且数据量比较大（百万级）时，用自增长的，此时最好能考虑下安全性，做些安全措施。
2、项目是单机版的，并且数据量没那么大，对速度和存储要求不高时，用UUID。
3、项目是分布式的，那么首选UUID，分布式一般对速度和存储要求不高。
4、项目是分布式的，并且数据量达到千万级别可更高时，对速度和存储有要求时，可以用自增长。

实在难以抉择，那就雪花算法做主键。
（6）SnowFlake是一种介于自增长和UUID之间的一种主键（存储空间小、速度快、分布式、时间序列）它有如下优点
1.所有生成的id按时间趋势递增
2.整个分布式系统内不会产生ID碰撞（重复id，因为有datacenterId和workerId来做区分）
3.id生成的效率高

9.数据库MySQL优化

1、选取最适用的字段属性
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
2、使用连接（JOIN）来代替子查询(Sub-Queries)。使用连接（JOIN）来完成查询，速度将会快很多，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
3、使用联合(UNION)来代替手动创建的临时表
4、事务。事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。
5、锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。
6、使用外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。
7、使用索引
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。
8、优化的查询语句
绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

Lottery项目中？？（不确定）

使用自增ID，用UUID做UNIQUE唯一性约束。
（UUID在计算出来时就能保持唯一性，因为它根据。。，然后再表中，会将UUID设置为UNIQUE添加唯一性约束，即不允许重复添加。）

10.如何实现一个高并发的系统？

• 1.前端优化
  ① 静态资源缓存：将活动页面上的所有可以静态的元素全部静态化，尽量减少动态元素；通过 CDN、浏览器缓存，来减少客户端向服务器端的数据请求。
  ② 禁止重复提交：用户提交之后按钮置灰，禁止重复提交。
  ③ 用户限流：在某一时间段内只允许用户提交一次请求，比如，采取 IP 限流。
• 2.中间层负载分发
  利用负载均衡，比如 nginx 等工具，可以将并发请求分配到不同的服务器，从而提高了系统处理并发的能力。 nginx 负载分发的五种方式：① 轮询（默认）② 按权重（weight）③ IP 哈希值（ip_hash）④ 响应时间（fair）⑤ URL 哈希值（url_hash
• 3.控制层（网关层）
  限制同一个用户的访问频率，限制访问次数，防止多次恶意请求。
• 4.服务层
  ① 业务服务器分离：比如，将秒杀业务系统和其他业务分离，单独放在高配服务器上，可以集中资源对访问请求抗压。
  ② 采用 MQ（消息队列）缓存请求：MQ 具有削峰填谷的作用，可以把客户端的请求先导流到 MQ，程序在从 MQ 中进行消费（执行请求），这样可以避免短时间内大量请求，导致服务器程序无法响应的问题。
  ③ 利用缓存应对读请求，比如，使用 Redis 等缓存分担数据库压力。
• 5.数据库层
  ① 合理使用数据库引擎
  ② 合理设置事务隔离级别，合理使用事务
  ③ 正确使用 SQL 语句和查询索引
  ④ 合理分库分表
  ⑤ 使用数据库中间件实现数据库读写分离
  ⑥ 设置数据库主从读写分离