【MySQL 进阶之路】存储引擎和SQL优化技巧分析

1.InnoDB和MyISAM存储引擎的区别是什么？你在哪些场景下选择InnoDB？

• Innodb是高并发，支持事务跟行级锁，myisam不支持事务和行级锁，支持表级锁，不支持高并发。innodb底层是B+树，适合范围查询，快速查询的性能都不错，myisam是哈希索引，使用与精确查找。

• 理解：在讨论 InnoDB 和 MyISAM 的区别时，首先要明确两者在事务支持、锁机制以及存储结构上的差异。InnoDB 支持事务、行级锁以及外键约束，这使得它适用于对数据一致性和高并发要求较高的场景。相比之下，MyISAM 使用表级锁，并且不支持事务，这在写入操作较少，查询频繁的环境下更为高效。MyISAM的哈希索引（在某些情况下）与B+树索引并存，这对于快速的查找和检索有所帮助，适合读多写少的应用。选择 InnoDB 时，通常是在需要保证数据一致性、执行复杂查询、以及高并发的场景下。而 MyISAM 更适合读取频繁但数据修改较少的场合。需要注意的是，虽然 MyISAM 在查询性能上有优势，但在数据安全性和恢复方面不如 InnoDB。

2.请描述什么是"死锁"，你如何解决死锁问题？

• 死锁就是两个或多个事务互相等待对方释放锁，导致业务停摆。想要解决死锁，1.减少事务持有锁时间 2.设置事务访问顺序，避免事务交叉冲突 3. 设置事务对超时锁进行释放 4.设置合理隔离级别和锁粒度，合理使用行锁表锁。 5. 设置死锁检测和事务回滚机制。

• 理解：在处理 死锁 问题时，死锁通常是由于两个或更多的事务在等待对方释放锁，导致无法继续执行。解决死锁的方法包括减少事务持有锁的时间、按相同顺序访问数据表、以及合理的事务设计。InnoDB 存储引擎内置了死锁检测机制，能自动发现死锁并回滚其中一个事务，这是一种非常有效的应对策略。为了避免死锁，可以通过合理设计事务的粒度，避免嵌套事务，并确保事务访问数据的顺序一致。此外，尽量减少锁的竞争，避免长时间持有锁，是预防死锁的重要手段。

3.你在SQL优化中最常用的技巧有哪些？举例说明。

• sql优化技巧：我认为sql优化的根本就是避免全表查询，实现覆盖索引。对sql优化，这些事sql优化注意事项，我们可以通过explain查看一条sql执行计划，通过type和extra查看这条sql具体性能，做sql优化就是使其type往ref这边靠。

• 理解：在进行 SQL 优化 时，首先要学会使用 EXPLAIN 命令分析查询计划。通过分析 EXPLAIN 输出中的 type、key 和 extra 字段，可以帮助我们识别潜在的性能瓶颈。通常，ALL 类型表示全表扫描，index 或 range 表示索引扫描，这些信息能帮助我们判断查询是否需要优化。优化策略包括避免全表扫描、创建合适的索引、使用覆盖索引等。可以通过合适的查询重写来提高查询效率，比如使用 JOIN 替代 IN 查询，或者在适当的情况下，使用 UNION ALL 替代 UNION，避免去重操作。除此之外，还可以通过合理选择索引列的顺序，优化查询性能。例如，针对复合索引，首先应该放置在查询中使用最频繁、选择性最高的列。

4.在事务的隔离级别中，"可重复读"和"串行化"的区别是什么？

• RR保障了事务访问数据库查询数据时，不会出现其他事务干涉当前数据。但是不能避免幻读，会查询到数据这个区间内，其他事务增删改后的数据。而串行化是最高隔离等级，事务和事务之间均不可干涉，并发性能较差，安全系数较高。而幻读是由于一个事务读取数据时，其他事务修改了这个数据结果集内的某些数据后，再次读取的数据就是幻读数据。

• 理解： 事务的隔离级别 对数据库性能的影响也非常重要。可重复读（REPEATABLE READ） 和 串行化（SERIALIZABLE） 是两种常见的事务隔离级别。可重复读确保事务在执行期间读取的记录始终保持一致，避免了不可重复读的现象，但它仍然可能受到幻读的影响。为了避免幻读，InnoDB 在可重复读隔离级别下采用了 Gap Lock 技术，这能够防止在事务执行期间其他事务插入符合条件的记录。相比之下，串行化 隔离级别提供最强的隔离性，它通过强制事务按照顺序执行来避免脏读、不可重复读和幻读。然而，这也会显著降低并发性，因此只应在数据一致性要求极高的场合使用。

5.假设一个查询的执行计划显示一个全表扫描，但你觉得它可以优化。你会采取什么措施？

• 如果通过EXPLAIN显示其type是all，我觉得可以对这个查询字段添加索引，多个字段添加联合索引，避免Select * 这种写法，改成写具体索引字段。避免索引字段进行计算，避免like头部模糊查询导致的索引字段失效。当分组时，要对分组字段和其他查询字段创建联合索引，对多个排序字段创建联合索引并设置对应顺序。尽量避免使用Force Index强制索引，多表连接时需要注意外连接条件字段是否为索引字段，避免全表查询，并且必须是小表驱动大表。

• 理解：在优化查询时，尤其是全表扫描的情况，可以通过多种方式提高查询效率。首先，确保查询条件字段上有合适的索引，尤其是在 WHERE 子句和 JOIN 操作中使用的字段。避免使用 SELECT *，这样可以减少不必要的数据传输和内存占用。对于 LIKE 查询，尤其是带有前缀通配符的情况，可能会导致全表扫描，因此应尽量避免。使用联合索引时，正确的索引顺序也很关键。联合索引的顺序应根据查询条件的使用频率来决定，这样可以提高查询效率。例如，如果查询条件中经常用到 A 和 B，但查询中大部分时间只用到 A，那么联合索引应首先包含 A，然后是 B。此外，尽量避免使用 FORCE INDEX，因为它强制数据库使用特定的索引，这在很多情况下会降低查询性能，尤其是当数据库优化器本身已经选择了最佳索引时。