（1）MySQL 索引的最左前缀匹配原则是什么？

MySQL索引的最左前缀匹配原则指的是在使用联合索引时，查询条件必须从索引的最左侧开始匹配。如果一个联合索引包含多个列，查询条件必须包含第一个列的条件，然后是第二个列，以此类推。

底层原理：因为联合索引在 B+ 树中的排列方式遵循“从左到右”的顺序，例如联合索引(first_name，last_name，age)会按照(first_name，last_name，age)的顺序在 B+ 树中进行排序。

MySQL在查找时会优先使用first_name作为匹配依据，然后依次使last_name和age。因此，组合索引能够从左到右依次高效匹配，跳过最左侧字段会导致无法利用该索引。

（2）数据库的脏读、不可重复读和幻读分别是什么？

脏读：一个事务读取另一个事务未提交的数据
幻读：一个事务内进行了多次读取，数据总量不一致
不可重复读：一个事务对同一数据进行读取，前后数据内容不一致

区别：

1. 脏读指的是一个事务读到了其他事务未提交的数据。
2. 不可重复读指的是一个事务中多次读到同一条数据发生了变化，重点在于表中已经存在的数据被其他事务修改了。
3. 幻读指的是一个事务被其他事务插入或者删除的数据有影响，重点在于事务开始后，其他事务插入或删除了数据。

（3）MySQL 的存储引擎有哪些？它们之间有什么区别？

1. InnoDB(重点)：
   • 支持事务、行级锁和外键。
   • 提供高并发性能，适用于高负载的OLTP应用。
   • 数据以聚集索引的方式存储，提高检索效率。
2. MyISAM(重点)：
   • 不支持事务和外键，使用表级锁。
   • 适合读取多、更新少的场景，如数据仓库。
   • 具有较高的读性能和较快的表级锁定。
3. MEMORY：
   • 数据存储在内存中，速度快，但数据在服务器重启后丢失。
   • 适用于临时数据存储或快速缓存。
4. NDB (NDBCluster)：
   • 支持高可用性和数据分布，适合大规模分布式应用
   • 提供行级锁和自动分区。
5. ARCHIVE：
   • 用于存储大量历史数据，支持高效的插入和压缩。
   • 不支持索引，适合日志数据存储。

OLTP（Online Transaction Processing，在线事务处理）是一种数据库处理技术，专注于实时处理和管理大量日常事务数据。

（4）MySQL 的覆盖索引是什么？

覆盖索引是指一个查询可以仅通过索引就能够返回所需的所有列，而无需再次到表中进行查找。
传统的索引通常只包含关键字和指向实际数据的指针，因此在查找时需要再次到表中进行查找，以获取其他列的数据。而覆盖索引则将覆盖列也包含在了索引中，可以直接从索引中返回所有需要的列，从而避免了二次查找的开销，提高了查询效率。

（5）MySQL 的索引类型有哪些？

从数据结构角度来看，MySQL索引可以分为以下几类：

• B+树索引：树形结构存储数据，支持范围查询（BETWEEN、>、<）和精确查询（=），数据有序存储，便于排序和聚合操作。
• 哈希索引：基于哈希表，仅支持等值查询（=），查询速度极快O(1)，但不支持范围查询和排序。
• 倒排索引(即全文索引Full-Text)：通过分词建立词与文档的映射，支持模糊匹配和自然语言搜索。
• R-树索引(多维空间树)：多维数据结构，支持地理空间数据查询（如距离计算、区域覆盖）。

从常见的基于InnoDBB+树索引角度来看，可以分为：

• 聚簇索引(Clustered Index)：主键索引即聚簇索引，叶子节点存储完整数据行，数据按主键顺序物理存储。
• 非聚簇索引(Non-clustered Index)：叶子节点仅存储索引列和主键值，查询完整数据需“回表”到聚簇索引。

从索引性质的角度来看，可以分为：

• 主键索引：唯一且非空，每个表仅一个，InnoDB中作为聚簇索引。
• 唯一索引：确保列值唯一，允许NULL值（但只能有一个NULL）。
• 联合索引：多列组合索引，遵循最左前缀原则。
• 普通索引（二级索引、辅助索引）：无唯一性约束，仅加速查询。
• 全文索引：支持文本内容的模糊搜索和关键词匹配。
• 空间索引：专为地理数据设计，支持GIS查询（如ST_Distance）。

（6）MySQL 的索引下推是什么？

索引下推指的是把 WHERE 子句中的一些条件下推到存储引擎层面进行过滤，减少要读取的行数。当查询语句使用覆盖索引时，MySQL 可以在索引中直接检测 WHERE 子句中的条件，而不必去加载表中的行，从而减少了 I/O 操作和 CPU 计算等开销。

（7）MySQL InnoDB 引擎中的聚簇索引和非聚簇索引有什么区别？

聚簇索引：
索引叶子节点存储的是数据行，可以直接访问完整数据。
每个表只能有一个聚簇索引，通常是主键索引，适合范围查询和排序。

非聚簇索引：
索引叶子节点存储的是数据行的主键和对应的索引列，需通过主键才能访问完整的数据行。
一个表可以有多个非聚簇索引（称之为非主键索引、辅助索引、二级索引)，适用于快速查找特定列的数据。

（8）MySQL 中的回表是什么？

“回表”是指在使用二级索引（非聚簇索引）作为条件进行查询时，由于二级索引中只存储了索引字段的值和对应的主键值，无法得到其它数据。如果要查询数据行中的其它数据，需要根据主键去聚簇索引查找实际的数据行，这个过程被称为回表。

（9）MySQL 中使用索引一定有效吗？如何排查索引效果？

索引不一定有效。

例如查询条件中不包含索引列、低基数列索引效果不佳，或查询条件复杂且不匹配索引的顺序。
对于一些小表，MySQL可能选择全表扫描而非使用索引，因为全表扫描的开销可能更小。
最终是否用上索引I是根据MySQL成本计算决定的，评估CPU和I/O成本最终选择用辅助索引还是全表扫描。有时候确实是全表扫描成本低所以没用上索引。但有时候由于一些统计数据的不准确，导致成本计算误判，而没用上索引。

排查索引效果的方法：使用EXPLAIN命令，通过在查询前加上EXPLAIN，可以查看MySQL选择的执行计划，了解是否使用了索引、使用了哪个索引、估算的行数等信息。

主要观察EXPLAIN结果以下几点：

• type(访问类型)：这个属性显示了查询使用的访问方法，例如ALL、index、range等。当查询使用索引时，这个属性通常会显示为index或range，表示查询使用了索引l访问。如果这个值是ALL，则表示查询执行了全表扫描，没有使用索引。
• key（使用的索引l)：这个属性显示了查询使用的索引，如果查询使用了索引，则会显示索引的名称。如果这个值是NULL，则表示查询没有使用索引。
• rows（扫描的行数）：这个属性显示了查询扫描的行数，需要评估下扫描量。

（10）MySQL 中的索引数量是否越多越好？为什么？

索引并不是越多越好。因为索引不论从时间还是空间上都是有一定成本的。

1. 从时间上

• 每次对表中的数据进行增删改(INSERT、UPDATE或DELETE)的时候，索引也必须被更新，这会增加写入操作的开销。例如删除了一个name为面试鸭的记录，不仅主键索引上需要修改，如果name字段有索引，那么name索引也需要修改，所以索引越多需要修改的地方也就越多，时间开销就大了，并且B+树可能会有页分裂、合并等操作，时间开销就会更大。
• 还有一点需要注意：MySQL有个查询优化器，它需要分析当前的查询，选择最优的计划，这过程就需要考虑选择哪个索引的查询成本低。如果索引过多，那么会导致优化器耗费更多的时间在选择上，甚至可能因为数据的不准确而选择了次优的索引。

2. 从空间上
每建立一个二级索引，都需要新建一个B+树，默认每个数据页都是16kb，如果数据量很大，索引又很多，占用的空间可不小。

（11）请详细描述 MySQL 的 B+ 树中查询数据的全过程

1. 数据从根节点找起，根据比较数据键值与节点中存储的索引键值（用了二分），确定数据落在哪个区间，从而确定分支，从上到下最终定位到叶子节点。
2. 叶子节点存储实际的数据行记录，但是一页有16KB大小，存储的数据行不止一条。
3. 叶子节点中数据行以组的形式划分，利用页目录结构，通过二分查找可以定位到对应的组。
4. 定位组后，利用链表遍历就可以找到对应的数据行。

（12）为什么 MySQL 选择使用 B+ 树作为索引结构？

B+树在数据库系统中具有以下几个显著优势：

1. 高效的查找性能：
B+树是一种自平衡树，每个叶子节点到根节点的路径长度相同，B+树在插入和删除节点时会进行分裂和合并操作，以保持树的平衡，但它又会有一定的冗余节点，使得删除的时候树结构的变化小，更高效。查找、插入、删除等操作的时间复杂度为O(Iogn)，能够保证在大数据量情况下也能有较快的响应时间。

2. 树的高度增长不会过快，使得查询磁盘的I/0次数减少：
B+树不像红黑树，数据越多树的高度增长就越快。它是多叉树，非叶子节点仅保存主键或索引值和页面指针，使得每一页能容纳更多的记录，因此内存中就能存放更多索引，容易命中缓存，使得查询磁盘的I/O次数减少。

3. 范围查询能力强：
B+树特别适合范围查询。因为叶子节点通过链表链接，从根节点定位到叶子节点查找到范围的起点之后，只需要顺序扫描链表即可遍历后续的数据，非常高效。

（13）MySQL 三层 B+ 树能存多少数据？

在MySQL的InnoDB存储引擎中，B+树默认数据页大小为16KB。

参数：

• 每个节点页大小为16KB(即16384字节)。
• 假设每个数据记录的主键和数据大小为1KB（一般会比这个小，但这里取整方便计算）。
• 每个内部节点（非叶子节点）存储的是指向子节点的指针和索引键。

三层B+树的存储计算：

• 叶子节点：第三层为叶子节点，每个叶子节点页可存储16条数据记录（16KB：1KB）。
• 第二层（中间层）：假设每个指针6字节和索引键（一般为bigint)的大小为8字节，那么每个中间
  节点页可以指向1170个叶子节点，16KB转成字节需要乘以1024，因此(16*1024+(6+8)字节）。
• 第一层（根节点）：根节点可以指向1170个中间节点。

由此，三层B+树大致能存储的数据总量为：1170 * 1170 * 16=21902400，一棵三层的B+树在MySQL中可以存储大约2000万条记录。

这里要注意这个只是估算值，具体数量会因实际的数据大小、页大小等因素略有不同。

（14）详细描述一条 SQL 语句在 MySQL 中的执行过程。

一条SQL语句在MySQL中的执行过程包括多个步骤，从接收到SQL命令到返回结果，这些步骤涉及SQL解析、优化、执行以及最终的结果返回。

1. 客户端发送SQL请求

• 客户端应用程序：首先，客户端（如JDBC、命令行工具等）通过网络向MySQL服务器发送一条SQL语句。这条SQL语句可能是一个查询、插入、更新或删除操作。

2. 服务器接收请求并解析SQL语句

• 查询缓存（Query Cache）检查（MySQL 8.0之前版本）：MySQL首先会检查查询缓存。如果查询缓存中已经存在相同的SQL语句，并且结果集也是最新的，那么MySQL直接返回缓存中的结果集，跳过后续的执行步骤。然而，由于查询缓存可能导致一致性问题和性能瓶颈，从MySQL 8.0开始，查询缓存功能已被移除。
• SQL解析（Parsing）：如果查询缓存不可用或被禁用，MySQL会对SQL语句进行解析。
  解析阶段包括词法分析和语法分析：
  • 词法分析：将SQL语句分解成单独的标记（Tokens），如关键字、表名、列名等。
  • 语法分析：根据SQL语法规则检查语句的结构是否正确。如果语法正确，生成解析树（Parse Tree）。
• 预处理（Preprocessing）：在解析树基础上进行预处理，包括权限检查、表和列的存在性检查等。预处理阶段还会进行一些简单的语义检查，如确保列的类型和运算符之间的兼容性。

3. 查询优化

• 查询重写（Query Rewriting）：有些SQL语句可能会被重写为更高效的等价语句。例如，将某些子查询转换为连接查询。
• 优化器（Optimizer）：优化器是MySQL中的关键组件，它决定了SQL语句的执行计划（Execution Plan）。执行计划是描述如何访问和处理数据的步骤。优化器的主要任务包括：
  • 选择最优的访问路径：决定是使用全表扫描、索引扫描还是其他访问方法。
  • 选择连接顺序：如果查询涉及多个表的连接（JOIN），优化器会决定表的连接顺序，这对性能影响很大。
  • 选择适合的算法：优化器还会选择最适合的算法来执行查询，如嵌套循环连接、哈希连接等。

4. 执行计划生成
执行计划（Execution Plan）：经过优化器选择之后，MySQL生成执行计划。执行计划是MySQL实际执行SQL语句的详细步骤和方法。执行计划包括：

• 选择的数据读取方式（如全表扫描、索引扫描）。
• 表之间的连接顺序和连接方法。
• 应用的过滤条件（WHERE子句）。
• 聚合、排序等操作的执行顺序。

5. SQL执行

• 存储引擎层（Storage Engine Layer）：MySQL的存储引擎负责实际的数据存储和检索。MySQL支持多种存储引擎（如InnoDB、MyISAM等），每种存储引擎有自己的工作方式和优化方法。
  • 表和索引访问：存储引擎根据执行计划访问表和索引，检索或修改数据。
  • 事务管理：如果SQL语句是事务的一部分，存储引擎将进行相应的事务管理，如事务开始、提交、回滚等。
• 执行结果生成：在数据检索或修改完成后，MySQL会根据需要对结果集进行进一步处理，如排序、聚合等。

6. 结果返回给客户端

• 返回结果集：MySQL服务器将执行结果集返回给客户端。如果是查询操作，结果集将包含符合条件的数据行；如果是插入、更新或删除操作，结果集通常包含受影响的行数。
• 客户端接收和处理：客户端接收服务器返回的结果集，进行显示、进一步处理或存储。

7. 结束执行

• 资源释放：MySQL在执行完SQL语句后，会释放在解析、优化和执行过程中占用的资源，如内存、锁等。
• 事务处理：如果SQL语句是事务的一部分，事务的处理可能会继续等待其他SQL语句，直到事务提交或回滚。

（15）MySQL 是如何实现事务的？

MySQL主要通过锁、redo log、undo log、MVCC来实现事务。
MySQL利用锁(行锁、间隙锁等等）机制，使用数据并发修改的控制，满足事务的隔离性。
redo log（重做日志）会记录事务对数据库的所有修改，当MySQL发生启机或崩溃时，通过重放redo log就可以恢复数据，用来满足事务的持久性。
undo log（回滚日志)，它会记录事务的反向操作，简单地说就是保存数据的历史版本，用于事务的回滚，使得事务执行失败之后可以恢复之前的样子，实现原子性和隔离性。
MVCC（多版本并发控制），满足了非锁定读的需求，提高了并发度，实现了读已提交和可重复读两种隔离级别，实现了事务的隔离性。

（16）MySQL 事务的二阶段提交是什么？

MySQL事务的二阶段提交是指在MySQL中，为了确保redo log(重做日志)和bin log(二进制日志)之间的一致性，使用的一种机制。MySQL通过二阶段提交来保证在crash recovery（崩溃恢复）时，不会出现数据丢失或数据不一致的情况。

二阶段提交的两个阶段：

• 准备阶段：在事务提交时，MySQL的InnoDB引擎会先写入redo log，并将其状态标记为prepare，表示事务已经准备提交但还未真正完成。此时的redo log是预提交状态，还未标记为完成提交。
• 提交阶段：当redo log 的状态变为 prepare后，MySQL Server会写入bin log(记录用户的DML操作)。bin log写入成功后，MySQL会通知InnoDB,将redo log状态改为commit，完成整个事务的提交过程。

（17）MySQL 中长事务可能会导致哪些问题？

1. 长时间的锁竞争，阻塞资源：
长事务持有锁的时间较长，容易导致其他事务在尝试获取相同锁时发生阻塞，从而增加系统的等待时间和降低并发性能。
业务线程也会因为长时间的数据库请求等待而阻塞，部分业务的阻塞可能还会影响到别的服务，导致产生雪崩，最终使得服务全面崩盘，导致非常严重的线上事故。

2. 死锁风险：
长事务更容易产生死锁，因为多个事务可能在互相等待对方释放锁，导致系统无法继续执行。

3. 主从延迟：
主库需要长时间执行，然后传输给从库，从库又要重放好久，期间可能有很长一段时间数据是不同步 的。

4. 回滚导致时间浪费：
如果长事务执行很长一段时间，中间突发状况导致抛错，使得事务回滚了，之前做的执行都浪费了。

（18）MySQL 中的 MVCC 是什么？

MVCC（Multi-Version Concurrency Control,多版本并发控制）是一种并发控制机制，允许多个事务同时读取和写入数据库，而无需互相等待，从而提高数据库的并发性能。

在MVCC中，数据库为每个事务创建一个数据快照。每当数据被修改时，MySQL不会立即覆盖原有数据，而是生成新版本的记录。每个记录都保留了对应的版本号或时间戳。

多版本之间串联起来就形成了一条版本链，这样不同时刻启动的事务可以无锁地获得不同版本的数据（普通读）。此时读（普通读）写操作不会阻塞。

写操作可以继续写，无非就是会创建新的数据版本（但只有在事务提交后，新版本才会对其他事务可见。未提交的事务修改不会影响其他事务的读取），历史版本记录可供已经启动的事务读取。

（19）MySQL 中的事务隔离级别有哪些？

在MySQL中，事务隔离级别主要有以下四种：

1. 读未提交

• 这是最低的隔离级别，在该级别下，一个事务可以看到另一个事务尚未提交的数据修改。这可能会导致脏读问题，即读取到其他事务未提交的数据。

2. 读已提交

• 在这个级别下，一个事务只能看到已经提交的其他事务所做的修改。这可以避免脏读问题，但是可能会引发不可重复读问题，即在同一个事务中，相同的查询可能返回不同的结果。

3. 可重复读

• 在这个级别下，确保在一个事务中的多个查询返回的结果是一致的。这可以避免不可重复读问题，但是可能会引发幻读问题，即在同一个事务中，多次查询可能返回不同数量的行（MySQL默认的隔离级别）。

4. 串行化

• 并发SQL事务在SERIALIZABLE隔离级别下的执行被保证是可串行化的。可串行化执行被定义为：并发执行的SQL事务的操作，其效果与这些SQL事务按某种顺序串行执行的效果相同。串行执行是指每个SQL事务在下一个SQL事务开始之前完成其全部操作。
• 这是最高的隔离级别，在这个级别下，保证事务间的操作结果相当于一个按顺序执行的单线程操作。这可以避免所有的并发问题，但是会大大降低并发性能。

（20）MySQL 默认的事务隔离级别是什么？为什么选择这个级别？

MySQL默认的隔离级别是可重复读。

原因是为了兼容早期binlog的statement格式问题，如果是使用读已提交、读未提交等隔离级别，使用了statement格式的binlog会导致主从（备）数据库数据不一致问题。

为什么选择可重复读作为默认级别？

1. 平衡一致性与性能：可重复读在保证较高数据一致性的同时，性能开销相对合理，是MySQL在一致性和性能之间的折中选择。
2. 避免不可重复读问题：在可重复读级别下，一个事务内多次读取同一数据会得到相同的结果，避免了读已提交级别下的不可重复读问题。
3. MySQL的MVCC实现：MySQL通过多版本并发控制（MVCC）机制实现了可重复读，这种实现方式：
   • 读操作不阻塞写操作
   • 写操作不阻塞读操作
   • 通过版本链维护数据的历史版本
4. 与InnoDB存储引擎的适配：
   • 使用next-key锁避免幻读
   • 高效的undo日志管理
   • 非锁定一致性读

数据库的脏读、不可重复读和幻读分别是什么？

脏读：一个事务读取另一个事务未提交的数据
幻读：一个事务内进行了多次读取，数据总量不一致
不可重复读：一个事务对同一数据进行读取，前后数据内容不一致

区别：

1. 脏读指的是一个事务读到了其他事务未提交的数据。
2. 不可重复读指的是一个事务中多次读到同一条数据发生了变化，重点在于表中已经存在的数据被其他事务修改了。
3. 幻读指的是一个事务被其他事务插入或者删除的数据有影响，重点在于事务开始后，其他事务插入或删除了数据。

（21）MySQL 中有哪些锁类型？

在MySQL中，主要有以下几种锁类型：

1. 表级锁（重点）：对整个表加锁，其他事务无法对该表进行任何读写操作，适用于需要保证完整性的小型表。
2. 行级锁（重点）：仅对特定的行加锁，允许其他事务并发访问不同的行，适用于高并发场景。
3. 意向锁：一种表锁，用于表示某个事务对某行数据加锁的意图，分为意向共享锁(IS）和意向排它锁(IX)，主要用于行级锁与表级锁的结合。
4. 共享锁（重点）：允许多个事务并发读取同一资源，但不允许修改。只有在释放共享锁后，其他事务才能获得排它锁。
5. 排它锁（重点）：只允许一个事务对资源进行读写，其他事务在获得排它锁之前无法访问该资源。
6. 元数据锁（MDL）：用于保护数据库对象（如表和索引)的元数据，防止在进行DDL操作时其他事务对这些对象进行修改。
7. 间隙锁（重点）：针对索引中两个记录之间的间隙加锁，防止其他事务在这个间隙中插入新记录，以避免幻读。间隙锁不锁定具体行，而是锁定行与行之间的空间。
8. 临键锁（重点）：是行级锁和间隙锁的结合，锁定具体行和其前面的间隙，确保在一个范围内不会出现幻读。常用于支持可重复读的隔离级别。
9. 插入意向锁：一种等待间隙的锁，用于指示事务打算在某个间隙中插入记录，允许其他事务进行共享锁，但在插入时会阻止其他的排它锁。
10. 自增锁： 在插入自增列时，加锁以保证自增值的唯一性，防止并发插入导致的冲突。通常在插入操作时被使用，以确保生成的自增ID是唯一的。

（22）MySQL 的乐观锁和悲观锁是什么？

悲观锁(Pessimistic Locking)：

• 假设会发生冲突，因此在操作数据之前就对数据加锁，确保其他事务无法访问该数据。常见于对数据一致性要求较高的场景。
• 实现方式：使用行级锁或表级锁，例如可以使用SELECT …FOR UPDATE或LOCK IN SHAREMODE语句来加锁。

乐观锁 (Optimistic Locking)：

• 假设不会发生冲突，因此在操作数据时不加锁，而是在更新数据时进行版本控制或校验。如果发现数据被其他事务修改，则会拒绝当前事务的修改，需重新尝试。
• 实现方式：通常通过版本号或时间戳来实现，每次更新时检查版本号或时间戳是否一致。

（23）MySQL 中如果发生死锁应该如何解决？

自动检测与回滚：

• MySQL自带死锁检测机制，当检测到死锁时，数据库会自动回滚其中一个事务，用来解除死锁。通常会回滚事务中持有最少资源的那个。
• 也有锁等待超时的参数(innodb_lock_waittimeout)，当获取锁的等待时间超过阈值时，就释放锁 进行回滚。

手动kill发生死锁的语句：

• 可以通过命令，手动快速地找出被阻塞的事务及其线程ID，然后手动kill它，及时释放资源。

（24）MySQL 中 count(*)、count(1) 和 count(字段名) 有什么区别？

在MySQL中，count(*)、count(1)和count(字段名)都是用来统计行数的聚合函数，但它们有些许的区别：

功能上：

• count(*)会统计表中所有行的数量，包括null值（不会忽略任何一行数据）。由于只是计算行数，不需要对具体的列进行处理，因此性能通常较高。
• count(1)和count(*)几乎没差别，也会统计表中所有行的数量，包括null值。
• count（字段名）会统计指定字段不为null的行数。这种写法会对指定的字段进行计数，只会统计字段值不为null的行。

效率上：

• count(1)和count(*)效率一致。
• count(字段）的查询就是全表扫描(如果对应的字段没有索引，如果有索引则用索引)，正常情况下它还需要判断字段是否是null值，因此理论上会比count(1)和count(*)慢。
• 但是如果字段不为null，例如是主键，那么理论上也差不多，而且本质上它们的统计功能不一样，在需要统计null 的时候，只能用count(1)和count(*)，不需要统计null的时候只能用count(字段)，所以也不用太纠结性能问题。

（25）MySQL 中如何进行 SQL 调优？

平时进行SQL调优，主要是通过观察慢SQL，然后利用explain分析查询语句的执行计划，识别性能瓶颈，优化查询语句。

1. 合理设计索引，利用联合索引进行覆盖索引的优化，避免回表的发生，减少一次查询和随机I/O。
2. 避免SELECT *，只查询必要的字段。
3. 避免在SQL中进行函数计算等操作，使得无法命中索引。
4. 避免使用% LIKE，导致全表扫描。
5. 注意联合索引需满足最左匹配原则。
6. 不要对无索引字段进行排序操作。
7. 连表查询需要注意不同字段的字符集是否一致，否则也会导致全表扫描。

除此之外，还可以利用缓存来优化，一些变化少或者访问频繁的数据设置到缓存中，减轻数据库的压力，提升查询的效率。
还可以通过业务来优化，例如少展示一些不必要的字段，减少多表查询的情况，将列表查询替换成分页分批查询等等。

（26）如何使用 MySQL 的 EXPLAIN 语句进行查询分析？

explain主要用来SQL分析，它主要的属性详解如下：

• id：查询的执行顺序的标识符，值越大优先级越高。简单查询的id通常为1，复杂查询（如包含子查 询或UNION)的 id会有多个。
• select_type(重要）：查询的类型，如SIMPLE(简单查询）、PRIMARY(主查询）、SUBQUERY（子查询）等。
• table：查询的数据表。
• type(重要）：访问类型，如ALL(全表扫描）、index(索引扫描）、range(范围扫描）等。一般来说，性能从好到差的顺序是:const>eq_ref >ref >range >index >ALL。
• ref：显示索引的哪一列被使用。
• rows（重要）：估计要扫描的行数，值越小越好。
• filtered：显示查询条件过滤掉的行的百分比。一个高百分比表示查询条件的选择性好。
• Extra(重要）：额外信息，如Using index(表示使用覆盖索引)、Using where（表示使用WHERE条件进行过滤)、Using temporary(表示使用I临时表)、Using filesort(表示需要额外的排序步骤）。

type 详解：

• system：表示查询的表只有一行（系统表）。这是一个特殊的情况，不常见。
• const：表示查询的表最多只有一行匹配结果。这通常发生在查询条件是主键或唯一索引，并且是常量比较。
• eq_ref：表示对于每个来自前一张表的行，MySQL仅访问一次这个表。这通常发生在连接查询中使用 主键或唯一索引的情况下。
• ref：MySQL使用非唯一索引扫描来查找行。查询条件使用的索引是非唯一的(如普通索引)。
• range：表示MySQL会扫描表的一部分，而不是全部行。范围扫描通常出现在使用索引l的范围查询中(如 BETWEEN、,>=,<=）。
• index：表示MySQL扫描索引中的所有行，而不是表中的所有行。即使索引列的值覆盖查询，也需要 扫描整个索引。
• all(性能最差)：表示MySQL需要扫描表中的所有行，即全表扫描。通常出现在没有索引的查询条件中。

（27）MySQL 中如何解决深度分页的问题？

什么是深度分页问题

深度分页指的是在数据量非常大的表中，当用户需要访问靠后的页面（如第1000页）时，MySQL需要扫描并跳过大量数据才能返回所需结果，导致查询性能急剧下降的问题。

深度分页的性能问题原因

MySQL执行LIMIT offset,size时，实际需要先读取offset + size条记录，然后丢弃前offset条，只返回剩下的size条。当offset很大时，这个操作会非常低效。

解决方案：

1. 使用主键或唯一索引优化
   如果表有自增主键或唯一索引，可以通过记录上一页最后一条记录的ID来优化：

   -- 第一页
   SELECT * FROM large_table ORDER BY id LIMIT 20;
   
   -- 获取上一页最后一条记录的ID（假设为100）
   -- 下一页查询
   SELECT * FROM large_table WHERE id > 100 ORDER BY id LIMIT 20;
   

2. 使用子查询优化

   SELECT * FROM large_table 
   WHERE id >= (SELECT id FROM large_table ORDER BY id LIMIT 10000, 1)
   LIMIT 20;
   

这种方法先快速定位到偏移位置的ID，然后再查询完整记录。

3. 使用JOIN优化

   SELECT t.* FROM large_table t
   JOIN (SELECT id FROM large_table ORDER BY id LIMIT 10000, 20) tmp
   ON t.id = tmp.id;
   

4. 使用覆盖索引
   如果查询的列都在索引中，可以直接使用索引而不需要回表：

   SELECT id FROM large_table ORDER BY id LIMIT 10000, 20;
   

5. 预先计算分页结果
   对于静态或变化不频繁的数据，可以预先计算并缓存分页结果。

不同解决方案的性能对比

方法	优点	缺点	适用场景
主键优化	性能最好	需要有序主键	有自增主键的表
子查询	比直接LIMIT快	仍需要扫描索引	有合适索引的表
JOIN优化	减少回表操作	需要两次索引扫描	大表分页
覆盖索引	避免回表	只能返回索引列	只需要索引列时
预先计算	查询最快	数据更新时需要重建	静态或低频更新数据

（28）什么是 MySQL 的主从同步机制？它是如何实现的？

MySQL的主从同步机制是一种数据复制技术，用于将主数据库(Master)上的数据同步到一个或多个从数据库(Slave)中。

主要是通过binlog实现数据的复制。主数据库在执行写操作时，会将这些操作记录到binlog中，然后推送给从数据库，从数据库重放对应的日志即可完成复制。

MySQL主从同步机制是构建高可用、高性能MySQL架构的基础技术。通过将主服务器的数据变更同步到一个或多个从服务器，可以实现读写分离、负载均衡、数据备份等多种应用场景。理解其工作原理和配置方法，对于MySQL数据库的管理和优化至关重要。

随着MySQL版本的更新，复制技术也在不断发展，如组复制(Group Replication)、半同步复制(Semi-Synchronous Replication)等更高级的复制方案，为不同场景提供了更多选择。

（29）如何处理 MySQL 的主从同步延迟？

首先需要明确一个点延迟是必然存在的，无论怎么优化都无法避免延迟的存在，只能减少延迟的时间。

常见解决方式有以下几种：

• 二次查询。如果从库查不到数据，则再去主库查一遍，由API封装这个逻辑即可，算是一个兜底策略，比较简单。不过等于读的压力又转移到主库身上了，如果有不法分子故意查询必定查不到的查询，这就对主库产生冲击了。
• 强制将写之后立马读的操作转移到主库上。这种属于代码写死了，比如一些写入之后立马查询的操作，就绑定在一起，写死都走主库。不推荐，比较死板。
• 关键业务读写走主库，非关键还是读写分离。比如上面我举例的用户注册这种，可以读写主库，这样就不会有登陆报该用户不存在的问题，这种访问量频次应该也不会很多，所以看业务适当调整此类接口。
• 使用缓存。主库写入后同步到缓存中，这样查询时可以先查询缓存，避免了延迟的问题，不过又引入了缓存数据一致性的问题。

除此之外，也可以提一提配置问题，例如主库的配置高，从库的配置太低了，可以提升从库的配置等。如果面试官对MySQL比较熟，可能会追问一些偏DBA侧的问题，例如并行复制等。

（30）MySQL 中如果我 select * from 一个有 1000 万行的表，内存会飙升么？

当 MySQL 执行一个查询百万行数据的 SELECT * FROM 操作时，通常不会一次性将所有数据加载到内存中，而是通过逐批次处理的方式来管理内存使用，即“边查边发送数据”给客户端。这个过程涉及获取一行数据，然后写入到 net_buffer 中，并重复此操作，直到查询完成。分批的大小与 net_buffer_length 参数有关，默认值为 16384 字节（16 KB）。

内存不会飙升的原因

• 逐批次处理：MySQL 不会一次性将所有 1000 万行数据全部读入内存。
• 流式处理：它通过将数据一行一行地从存储引擎读取，然后放入 net_buffer，再发送给客户端。
• 内存缓冲区：net_buffer_length 参数定义了用于缓存网络数据的缓冲区大小。

数据流动的过程

1. MySQL 从存储引擎读取一行数据。
2. 将该行数据写入到 net_buffer 中。
3. 重复这个过程，不断获取新行数据并写入 net_buffer。
4. 当 net_buffer 达到其容量时（或当客户端接收完数据时），数据会被发送到客户端，并清空 net_buffer。

通过这种方式，内存占用被限制在 net_buffer 的范围内，避免了内存飙升，确保了对内存的高效利用。

（31）在 MySQL 中建索引时需要注意哪些事项？

简单总结了以下六点：

1. 不能盲目建立索引，索引并不是越多越好，索引会占用空间，且每次修改的时候可能都需要维护索引的 数据，消耗资源。
2. 对于字段的值有大量重复的不要建立索引。比如说：性别字段，在这种重复比例很大的数据行中，建立索引也不能提高检索速度。但是也不绝对，例如定时任务的场景，大部分任务都是成功，少部分任务状态是失败的，这时候通过失败状态去查询任务，实际上能过滤大部分成功的任务，效率还是可以的。
3. 对于一些长字段不应该建立索引。比如text、longtext这种类型字段不应该建立索引。因为占据的内存大，扫描的时候大量加载至内存中还耗时，使得提升的性能可能不明显，甚至可能还会降低整体的性能，因为别的缓存数据可能因为它被踢出内存，下次查询还需要从磁盘中获取。
4. 当数据表的修改频率远大于查询频率时，应该好好考虑是否需要建立索引。因为建立索引会减慢修改的效率，如果很少的查询较多的修改，则得不偿失。
5. 对于需要频繁作为条件查询的字段应该建立索引。在where关键词后经常查询的字段，建立索引能提高查询的效率，如果有多个条件经常一起查询，则可以考虑联合索引，减少索引数量。
6. 对经常在orderby、groupby、distinct后面的字段建立索引。这些操作通常需要对结果进行排序、分组或者去重，而索引可以帮助加快这些操作的速度。

（32）在什么情况下，不推荐为数据库建立索引？

一般有以下几种情况下不推荐建立索引：

1. 对于数据量很小的表

• 当表的数据量很小（如几百条记录）时，建立索引并不会显著提高查询性能，反而可能增加管理的复 杂性。

2. 频繁更新的表

• 对于频繁进行插入、更新和删除操作的表，索引会导致额外的维护开销，因为每次数据变更时都需要 更新索引，这会影响性能。

3. 执行大量的 SELECT *

• 此时二级索引可能不会显著提升性能，因为需要大量的回表查询，开销大，数据库最终可能会选择走 全表扫描。

4. 低选择性字段（高度重复值的列）

• 当索引字段的取值重复度高（如性别字段“男”、“女”），索引的效果不明显，且会增加存储空间 的浪费。
• 但是，还有一种场景可以考虑，比如表里任务status列就2个类型，90%都是1(已完成），10%（待执行）是2，这个场景会频繁查询2（待执行）的任务来执行，此时可以建立索引，毕竟能过滤90%的数据。

5. 低频查询的列

• 对于查询频率极低的字段，建立索引的成本和维护负担可能超过带来的性能提升。

6. 长文本字段（非常长的varchar或JSON、BLOB和TEXT类型，这些类型的列通常包含大量数据)

• 数据量大排序时都无法用内存排，只能利用磁盘文件，排序很慢。
• 数据量大，每个页能存放的行数就少，扫描查询可能会涉及大量的1/O。
• 文本字段过大都需要额外blob页存储，每次查询还需要查额外的页，也是随机I/O效率低。
• 这种类型的数据如果有查询需求，不应该放到MySQL中，可以需要采用es等组件来实现查询。