8.MySQL

9.1 数据库隔离级别有哪些，各自的含义是什么，MYSQL默认的隔离级别是是什么。

　　数据库的隔离级别是指多事务并发执行时导致的数据不一致问题，隔离级别包括：读未提交、读已提交、可重复读、串行化，MySQL默认隔离级别时可重复读。

• 读未提交：事务可读到其他事务未提交的数据变更，可能导致脏读
• 读已提交：事务只能读取已提交的数据，解决了脏读问题，但同一事务在不同时间点多次读取同一数据可能得到不同的结果，即不可重复读。
• 可重复读：一个事务在开始直到结束期间，对同一个数据的多次读取结果相同，即使其他事务在这期间对数据进行了修改和提交。但仍然存在幻读，即同一事务内两次执行同样的查询，会因其他事务插入新的行或删除已有行而返回不同的结果集。
• 串行化：最高的隔离级别，系统通过锁定机制确保任何事务都不能同时操作相同数据，保证数据的完整性和一致性，但也降低了系统的并发性能。

9.2 什么是幻读。

　　幻读是指同一事务两次执行相同查询，会因其他事务的插入新的行或删除已有行而返回不同的结果集。串行化通过锁机制保证任何事务都不能同时操作相同数据进而解决幻读问题。

9.3 MYSQL有哪些存储引擎，各自优缺点。

MySQL有Innodb、MYISAM、memory等存储引擎。

Innodb：

• 优点：支持事务，保证数据一致性；支持行级锁，减少锁冲突；提供外键约束，保证表间关联的完整性；通过MVCC支持高并发读写操作；自动崩溃恢复功能，确保数据安全性
• 缺点：相比MyISAM等其他引擎，需要额外的空间来存储事务和索引信息，空间占用相对较大；大量更新和插入操作时，写入密集型场景下可能较慢；

MYISAM

• 优点：对全文索引有很好的支持；数据文件与索引文件分离，查询速度快，适合读取密集型应用；空间占用相对较小
• 缺点：不支持事务和行级锁定，只支持表级锁定；不支持崩溃恢复或高可用性解决方案；不支持外键约束

Memory

• 优点：将所有数据存储在内存中，查询速度快，适用于临时表或者小规模、频繁读写的表
• 缺点：当MySQL服务器关闭或发生故障时，所有数据都会丢失；存储容量受限于系统内存大小，且无法保存BLOB或TEXT类型的大字段；不适用于大表或数据量剧增情况

9.4 高并发下，如何做到安全的修改同一行数据。

　　确保安全地修改同一行数据需要借助于数据库事务的隔离级别、锁机制以及应用程序优化策略

• 合适的事务隔离级别：MySQL中选择可重复读或串行化隔离界别减少并发问题
• 乐观锁：乐观锁并不在修改数据时立即加锁，而是在更新时检查自上次读取以来数据是否有被其他事务修改过，例如通过版本号或者时间戳字段来判断
• 悲观锁：事务开始时对要修改数据进行加锁，直到事务结束释放锁。

9.5 乐观锁和悲观锁是什么，INNODB的标准行级锁有哪2种，解释其含义。

　　乐观锁与悲观锁是并发控制的两种策略。INNODB标准行级锁包含共享锁和排它锁

• 悲观锁认为事务执行过程中很可能发生并发冲突，因此，在读取数据时立即获取锁，并在整个事务操作期间保持锁定状态，直到事务结束释放锁。
• 乐观锁假定在同一时间内不会有很多事务同时修改同一条记录，所以通常不加锁，而是每个事务在读取数据时都假设自己可以成功提交，仅在提交事务前检查在此期间是否有其他事务修改了数据。
• 共享锁：当一个事务对某一行数据加上共享锁后，允许其他事务对该行加共享锁，但不允许加排他锁。共享锁主要用于查询操作，多个事务可以同时读取同一行数据，而不会相互阻塞。
• 排他锁：如果一个事务对某一行数据加了排他锁，则禁止任何其他事务再对此行加任何类型的锁（包括共享锁和排他锁）；排他锁用于数据修改操作，如UPDATE、DELETE等，以防止其他事务在该事务完成之前对同一行进行读写操作，从而保证数据的一致性和完整性。

9.6 SQL优化的一般步骤是什么，怎么看执行计划，如何理解其中各个字段的含义。

1. 通过数据库慢查询日志找出执行效率低的SQL语句
2. 检查SQL语句是否存在全表扫描、过度连接、不必要的子查询、未使用索引等情况
3. 通过在SQL查询语句前面添加explain关键字查看SQL执行计划
4. 根据查询计划的字段分析SQL是否有效利用了索引、是否存在表扫描等问题，并针对性地调整SQL语句或修改表结构（添加或修改索引、拆分大表、重构查询逻辑等）。对于JOIN操作，检查是否建立了合适的联接条件和索引，并考虑是否有必要优化JOIN顺序和方式
5. 如果SQL优化无法解决根本问题，可能需要进一步考虑数据库服务器的硬件资源配置、内存参数设置、存储引擎的选择等硬件资源与服务器配置

9.7 数据库会死锁吗，举一个死锁的例子，mysql怎么解决死锁。

　　数据库会产生死锁，

假设存在两个事务（Transaction A 和 Transaction B），它们分别对两张表进行操作，如下所示：

1. Transaction A：

   • 开始事务
   • 更新行记录 UPDATE table1 SET column = value WHERE id = 1;
   • 尝试更新另一条记录 UPDATE table2 SET column = value WHERE id = 2; （这里假设该行已被Transaction B锁定）

2. Transaction B：

   • 开始事务
   • 更新行记录 UPDATE table2 SET column = value WHERE id = 2;
   • 尝试更新另一条记录 UPDATE table1 SET column = value WHERE id = 1; （这里假设该行已被Transaction A锁定）

现在的情况是：

• Transaction A 持有对 table1 行1的锁，并等待 table2 行2的锁。
• Transaction B 持有对 table2 行2的锁，并等待 table1 行1的锁。

双方都在等待对方释放资源，因此形成了一个循环等待，即死锁。

解决办法：MySQL服务器通过检测并自动解决死锁来防止事务无限期地等待。当检测到死锁时，MySQL会选择其中一个事务作为牺牲者，回滚该事务以打破死锁链。

在InnoDB存储引擎中，可以通过设置以下参数调整死锁检测策略：

• innodb_lock_wait_timeout: 这个参数定义了InnoDB等待锁定的时间（以秒为单位），超过这个时间没有获得所需的锁定，则会返回错误，从而可能解除死锁状态。

9.8 MySQL的索引原理，索引的类型有哪些，如何创建合理的索引，索引如何优化。

　　MySQL索引是一种B+树的数据结构，帮助数据库系统快速地查找和访问表中的行。

1）索引类型主要有：

普通索引、唯一索引（强调列值唯一，不允许重复）、主键索引（特殊的唯一索引，不允许空值）、全文索引（适用于文本类型字段，可进行词组匹配和模糊查询）、空间索引（适用于地理空间数据类型）、哈希索引（进存在于MEMORY存储引擎，基于哈希表实现，主要用于等值查询，不支持范围查询和排序）

2）如何创建合理索引：

• 考虑查询模式：为频繁出现在WHERE子句、JOIN条件以及ORDER BY、GROUP BY语句中的列创建索引。
• 索引的选择性：选择具有高区分度的列作为索引，即该列的唯一值越多越好，比如身份证号优于性别列。
• 避免过度索引：索引虽然能提升查询速度，但也会占用额外的磁盘空间并降低插入、更新和删除操作的速度。因此，不要为所有列都创建索引，只针对那些真正能提高性能的关键列创建索引。
• 联合索引：对于多列查询，可以创建覆盖多个列的联合索引，并遵循最左前缀原则。

3）索引优化：

• 使用覆盖索引：如果索引包含了查询所需的所有列，则无需回表查询，可极大提高查询效率。
• 索引维护与重建：随着数据的增删改，索引可能会变得碎片化，定期分析和优化索引，或者在适当的时候重建索引可以保持其高效性。
• 避免冗余索引：相似的索引可能造成资源浪费，应尽量合并或删除冗余索引。
• 利用EXPLAIN分析SQL执行计划：通过查看执行计划了解MySQL实际使用的索引，调整索引策略以适应实际查询需求。

9.9 聚集索引和非聚集索引的区别。

　　聚集索引和非聚集索引是数据库中两种主要的索引类型，它们的主要区别在于数据行在磁盘上的存储方式以及索引结构与数据行的关系。

聚集索引：

• 在聚集索引中，表中的数据行是按照索引键值物理排序存储的。
• 每个表只能有一个聚集索引，通常默认为主键索引。
• 聚集索引的叶子节点直接包含行数据，这意味着查询时可以直接从索引中获取到所需的数据，无需额外查找。
• 当对具有聚集索引的表进行插入、删除或更新操作时，可能需要做更多的物理数据移动，以保持数据的有序性。

非聚集索引：

• 非聚集索引并不影响数据行在磁盘上的物理存储顺序，数据行可以随机分布。
• 一个表可以有多个非聚集索引。
• 非聚集索引的叶子节点包含的是指向实际数据行的指针，而非数据本身，因此查询时首先找到索引项，然后通过指针定位到实际数据行。
• 使用非聚集索引进行查询时，如果查询涉及到了不在索引覆盖范围内的列，则需要执行额外的“回表”操作来获取其他列的数据。但非聚集索引在覆盖索引情况下无需回表，也能提供较高查询性能。

9.10 select for update 是什么含义，会锁表还是锁行或是其他。

　　select for update 是数据库事务处理中的一种锁定查询语句，常见于支持事务的数据库系统如MySQL、Oracle等。当在事务中执行这样的查询时，它会获取选定行的排他锁（Exclusive Lock），直到当前事务结束（提交或回滚）。它用来实现悲观锁机制，在高并发环境下确保事务安全性和数据一致性。

1. 锁定行：SELECT ... FOR UPDATE 通常会对查询结果集中的每一行数据加行级锁，而不是锁住整个表。这意味着其他事务在当前事务未结束前不能对被锁定的特定行进行更新或删除操作。

2. 防止并发修改：通过这种方式可以确保在读取数据后进行更新操作期间，不会有其他事务对这些数据进行干扰，有效地避免了并发场景下的数据不一致性问题。

3. 非锁定版本：要注意的是，普通的 SELECT 查询不会锁定任何行，即使在事务中也是如此，除非数据库系统具有特定的MVCC（多版本并发控制）机制。

4. 死锁检测：使用 SELECT ... FOR UPDATE 可能会导致死锁，特别是在多个事务相互等待对方释放锁资源的情况下。数据库系统通常会有死锁检测和解决机制来处理这类情况。

5. 全局/范围锁定：在某些情况下，如果查询条件没有利用到索引，MySQL可能会升级为表锁而非行锁，这取决于具体的SQL执行计划和存储引擎特性。但一般来说，优化的查询都会尽可能地保持行锁级别，以提高并发性能。

9.11 为什么要用Btree实现，它是怎么分裂的，什么时候分裂，为什么是平衡的。

1）B-Tree（B树）被广泛用于数据库和文件系统中作为索引结构，因为它具有以下显著优点：

1. 范围查询效率高：B-Tree中的数据是有序存储的，并且通过内部节点可以快速定位到一个范围内的所有记录。

2. 插入、删除操作相对高效：B-Tree在设计时保证了自平衡性，即插入或删除数据后可以通过分裂或合并节点来保持树的高度大致恒定，从而确保大部分操作的时间复杂度接近O(log n)。

3. 空间局部性好：由于B-Tree通常采用页式存储，所以它的节点能很好地利用内存缓存，提高I/O效率。

2）B-Tree的分裂过程： 当某个节点（无论是内部节点还是叶子节点）的数据量超过其预先设定的最大容量时，就需要进行分裂。具体步骤如下：

• 将当前满节点的数据按照中间位置一分为二，形成两个新的节点。
• 如果是叶子节点，则直接将数据分割；如果是内部节点，则需要更新子节点指针信息。
• 父节点根据分裂的情况可能也要进行调整，比如新增一个指向新节点的键值对，如果父节点也满了，则递归上溯至根节点，直至找到无需分裂的位置为止。

分裂时机： 每当有新的元素插入到已满节点时，就会触发该节点的分裂操作。

3）为什么B-Tree是平衡的：

　　 B-Tree的平衡体现在每个节点的所有子节点（包括叶子节点和非叶子节点）都有相同的最大深度差。这意味着从根节点到任何一个叶子节点的路径长度最多相差1个层级。这样，在进行查找、插入和删除等操作时，都能确保查询性能稳定在一个可接受的范围内。而随着数据的增长和变化，B-Tree通过自动的分裂和合并操作维持这种平衡特性，这就是所谓的“自平衡”。

9.12 数据库的ACID是什么。

　　数据库的ACID是指事务在执行时满足的四个属性，分别是原子性、一致性、隔离性、持久性。

1. 原子性（Atomicity）： 一个事务中的所有操作被视为一个不可分割的单元。这意味着事务的所有操作要么全部成功完成，要么全部不执行。如果事务中的任何部分失败，则整个事务都会被回滚到事务开始前的状态，确保数据库从一个一致状态转换到另一个一致状态。

2. 一致性（Consistency）： 事务完成后，数据库将处于一个一致状态，在这个状态下，所有的完整性约束都被满足，比如实体完整性（主键唯一）、参照完整性和用户定义的业务规则等。每个成功的事务都将使数据库从一个有效状态转变为另一个有效状态。

3. 隔离性（Isolation）： 在并发环境中，即使多个事务同时执行，每个事务都应该独立地、仿佛在单线程环境下运行一样。不同的隔离级别有不同的效果，但一般来说，数据库系统会通过锁定机制或其他并发控制手段来防止事务间的相互干扰，保证每个事务看到的数据都是独立且不受其他事务影响的。

4. 持久性（Durability）： 当一个事务成功提交后，它对数据库所做的更改必须是永久性的，即使发生系统崩溃或电源故障等异常情况，一旦事务完成，所作更改也不会丢失。通常这是通过写入日志并定期进行checkpoint来实现的。

9.13 某个表有近千万数据，CRUD比较慢，如何优化。

1.索引优化：

• 确保对频繁用于查询、排序或作为JOIN条件的列创建了适当的索引。
• 对于SELECT查询，特别是WHERE子句中使用的列和ORDER BY、GROUP BY中的字段，应当考虑建立索引以加速查找和排序。
• 使用覆盖索引（covering index），即索引包含了查询所需的全部列，这样可以直接从索引中获取所有信息而无需回表。

2.SQL语句优化：

• 避免全表扫描，确保查询时能有效利用索引。
• 减少不必要的JOIN操作，简化查询复杂度。
• 尽量避免在 WHERE 条件中使用不等于（!=）或 LIKE '%%' 这样的条件，因为这些条件通常无法充分利用索引

3.分区表策略：

• 如果业务允许，可采用水平分区或者垂直分区策略。水平分区是将大表的数据按照某个字段值分割成多个物理表；垂直分区则是根据列的不同属性拆分成多个表，每个表包含原表的一部分列。

4.硬件升级与配置调优

• 增加内存，增大缓冲池大小，提高缓存命中率，减少磁盘I/O。
• 考虑使用更快的SSD硬盘替换传统的HDD硬盘。
• 根据实际情况调整数据库服务器的相关参数，比如innodb_buffer_pool_size、query_cache_size等。

5.读写分离

• 通过主从复制或分库分表实现读写分离，将读操作分散到只读从库上，减轻主库的压力。

6.使用缓存

• 对常用查询结果使用缓存服务（如Redis、Memcached），减少直接查询数据库的次数。

9.14 Mysql怎么优化table scan的。

1. 添加索引：

   • 首先，分析查询语句，找出经常出现在WHERE子句、JOIN条件、ORDER BY或GROUP BY中的列，并为这些列创建合适的索引。索引可以极大地加速对特定行的查找和排序操作，从而避免不必要的全表扫描。

2. 合理使用复合索引：

   • 如果查询涉及到多个列，考虑创建复合索引（组合索引）。在创建复合索引时遵循最左前缀原则，即索引顺序应与查询条件中列的顺序尽量保持一致。

3. 避免不走索引的操作：

   • 不要对索引字段进行函数运算、类型转换等操作，这可能导致无法利用索引。
   • 对于字符串类型的索引，注意LIKE操作符的使用方式，避免以通配符%开头的模糊查询，因为这样的查询通常无法利用索引。

4. 查询优化：

   • 确保SQL语句编写高效，避免无谓的大范围扫描，比如避免在没有WHERE子句的情况下执行SELECT * FROM table。

5. 表设计优化：

   • 分区表：对于非常大的表，可以考虑使用分区表策略，将大表物理分割成多个小块，根据查询需求选择合适的分区键，以便更快地定位数据。

6. 硬件和系统参数调整：

   • 增加内存，增大缓冲池大小，提高缓存命中率，减少磁盘I/O。
   • 根据实际负载情况调整数据库配置参数，例如InnoDB的innodb_buffer_pool_size、innodb_log_file_size等。

7. 监控和统计信息：

   • 使用EXPLAIN命令分析查询计划，了解查询是如何执行的，以及是否正确利用了索引。
   • 定期更新MySQL的统计信息，确保优化器能基于最新的统计做出最优的执行计划。

9.15 如何写sql能够有效的使用到复合索引。

1. 复合索引的创建： 创建复合索引时应考虑查询模式。按照最常用且选择性高的列到选择性较低的列的顺序排列索引键。
2. 查询语句的构造：使用索引中的左前缀匹配原则。
3. 避免在索引列上使用函数或表达式： 在索引列上直接使用函数或者运算符（如LIKE的非前缀匹配 %value%），会导致无法利用索引。
4. 排序操作： 复合索引不仅能用于搜索，还可以用于排序。如果查询中包含了与复合索引相同列序的ORDER BY子句，也能有效利用索引。
5. 避免全值匹配以外的操作： 当索引用于范围查询时，后面的索引列通常无法被利用。

9.16 mysql中in 和exists 区别。

　　in和exists都是在子查询中过滤主要查询结果的条件表达式，它们在功能和性能上存在一些区别：
in操作符：in操作符用于检查某个字段的值是否包含在子查询返回的结果集中。

SELECT * FROM table1 WHERE column1 IN (SELECT column2 FROM table2);

　　这个查询会查找table1中column1的值存在于table2.column2列表中的所有行。

exists操作符：exists操作符用于判断是否存在满足子查询条件的行。如果子查询至少返回一行数据，则 EXISTS 条件为真。

SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.column1 = table2.column2);

　　这个查询会查找table1中那些在table2中有匹配记录（即table1.column1与table2.column2相等）的所有行。

性能差异：

• 对于in操作符，如果子查询结果集较小，并且能够利用索引，那么性能可能较好。但如果子查询结果集较大，MySQL可能需要创建临时表来存储这些结果再进行比较，这会导致性能下降。

• exists通常在处理大量数据时有更好的性能，因为它只需要找到一个匹配项就可以停止搜索，特别是当子查询基于连接条件有效使用索引时。

9.17 数据库自增主键可能的问题。

1. 主键范围限制：如果使用的是整数类型的自增主键，当数据量增长到一定规模时，可能会遇到主键数值上限的问题。例如，在MySQL的INT类型中，UNSIGNED INT的最大值是4294967295，超过这个值将无法继续生成新的主键。

2. 数据迁移和分布式环境中的问题：在进行数据迁移或者在分布式环境下，如果多个数据库实例共享同一个自增序列，则可能出现主键冲突。在分库分表场景下，通常需要采用全局唯一ID生成策略，而不是简单的自增主键。

3. 并发插入性能瓶颈：高并发插入场景下，自增主键可能导致锁争用，特别是对于InnoDB存储引擎，虽然其内部对自增列的管理做了优化，但在极端高并发情况下仍可能成为性能瓶颈。

4. 业务逻辑关联问题：若业务上对主键有特定的业务含义或依赖（比如用户习惯按照顺序查看记录），一旦出现删除、重置主键等操作，可能导致业务逻辑混乱。

5. 恢复数据完整性问题：如果数据库备份与恢复操作不当，可能导致主键重复或丢失，从而破坏数据完整性。

6. 扩展性受限：对于未来可能的数据模型变更或扩展，如合并多表为一张大表，自增主键可能由于原各表的主键不连续而造成空间浪费。

解决方案：

• 使用UUID或雪花算法等来生成全局唯一ID作为主键。
• 为避免主键上限问题，可选择更大的数据类型（如BIGINT）作为自增主键。
• 在分布式系统中，可以使用分布式ID生成服务来确保全局唯一且无序的主键生成。

9.18 MVCC的含义，如何实现的。

　　MVCC即多版本并发控制，是一种数据库管理系统中广泛采用的并发控制机制。它的核心思想是为事务提供数据在某个时间点的快照，使得不同事务之间可以并发读取而不互相阻塞，并且保证每个事务看到的数据视图都是一致的，即使这个视图是基于同一数据的不同版本。

1. 隐藏列：在MySQL InnoDB存储引擎中，每行记录除了实际的数据列外，还包含两个隐藏系统列：事务ID（transaction ID，也称为行版本号或system version number），以及回滚指针（rollback pointer）。事务ID用来标识创建或最后修改该行的事务，而回滚指针指向该行的前一个版本。

2. 快照读与当前读：

   • MVCC在InnoDB中的实现中，SELECT语句默认进行的是“快照读”（非锁定读），只读取在事务开始之前已经提交的数据版本。
   • 当事务需要获取最新的数据时，则会执行“当前读”，例如使用SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE或直接修改数据时，会对数据加锁以确保读取到最新版本。

3. 版本链与清理：

   • 每当有新的事务更新一行数据时，不会直接覆盖原有数据，而是创建一个新的版本，并将旧版本链接起来形成一个版本链。
   • 事务结束后，系统会根据事务ID和undo日志自动清理不再需要的历史版本，以此保持数据库的大小可控。

4. 事务查看规则：

   • 不同隔离级别下，事务对历史版本的可见性规则不同。比如，在可重复读（Repeatable Read）隔离级别下，事务只能看到在它开始之前已经提交的其他事务所做的更改。

9.19 MYSQL的主从延迟怎么解决。

 　　MySQL的主从延迟是指在主从复制（Master-Slave Replication）环境下，当主库（Master）执行了写操作并将其记录到二进制日志（Binary Log）后，从库（Slave）未能及时或完全同步这些更改的情况。由于网络传输、服务器处理能力、IO性能等各种原因，导致从库上的数据更新落后于主库。具体来说，在MySQL的异步复制机制下，主库不会等待从库确认已接收并应用事务，而是直接提交事务并继续处理新的请求。这样就可能出现一种情况：在主库上已经完成的事务在从库上还未执行或者尚未完成。这个时间差就是所谓的“主从延迟”。它会带来数据不一致、高可用问题。

解决方案：

1. 提升从库性能，包括优化硬件配置、提高并发处理能力等。
2. 优化网络环境，确保主从之间数据传输的快速稳定。
3. 调整MySQL参数以适应更高的写入负载，并合理配置复制相关的参数，如innodb_flush_log_at_trx_commit、sync_binlog等，以平衡安全性与写入性能。

　　半同步复制（Semi-Synchronous Replication）： 在MySQL数据库中，半同步复制是一种确保数据强一致性的复制模式。在异步复制中，主库不会等待从库确认事务已接收就进行提交；而在半同步复制模式下，主库在接收到写请求后，会等待至少一个从库将事务写入其二进制日志并发送ACK确认消息之后，才在主库上提交事务。这样可以极大地减少主从之间的数据延迟，并提供一定的数据一致性保障。

组复制（Group Replication）： MySQL Group Replication 是一种基于分布式协议的高可用性和容错性解决方案，它提供了多节点间的强一致性保证和自动故障转移功能。在组复制环境中，所有成员节点共同参与事务处理，并通过 Paxos 协议达成共识。每个事务在提交之前，必须得到集群大多数节点的认可。这意味着在组复制中的所有节点上的数据是严格一致的，不存在主从延迟问题。

两者的主要区别在于：

• 半同步复制确保至少有一个从库接收到事务，适用于主从架构下的数据强一致性要求；
• 组复制则构建了一个多节点组成的对等网络，其中所有节点都是主节点，任何节点都能处理客户端请求，实现了更强的一致性保证，适用于高可用、高并发场景下的分布式数据库系统。