数据库QA | Olimi’s Blog

MySQL QA

MyISAM 和 InnoDB 是 MySQL 数据库最常见的两种存储引擎，每种都有其优点和缺点。以下是它们之间的一些主要差异：

事务支持：InnoDB 支持事务（Transaction），而 MyISAM 不支持。事务是一种可以由 DBMS （数据库管理系统）访问并可能更新各种数据库表中数据的程序执行单元。MyISAM 在不支持事务的情况下，如果在写入过程中发生崩溃，可能会导致数据的不一致。

锁定机制：MyISAM 只支持表级锁定（table-level locking），而 InnoDB 还支持行级锁定（row-level locking）。行级锁定允许更高的并发性，尤其是在多用户并发读写的情况下。

数据完整性和外键：InnoDB 支持外键（foreign key）和参照完整性（referential integrity），这是实现级联删除和级联更新的重要功能。而 MyISAM 不支持。

全文索引：在 MySQL 5.6 版本之前，只有 MyISAM 支持全文索引（Full-text Search）。但从 MySQL 5.6 开始，InnoDB 也支持全文索引。

在选择存储引擎时，需要根据应用的需求进行权衡。如果需要事务支持、崩溃恢复和高并发写入，那么应该选择 InnoDB。如果应用主要是只读的，或者需要全文索引（在 MySQL 5.6 之前的版本），那么可以使用 MyISAM。

小林Coding：

MySQL InnoDB 引擎的可重复读隔离级别（默认隔离级），根据不同的查询方式，分别提出了避免幻读的方案：

我举例了两个发生幻读场景的例子。

第一个例子：对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。

第二个例子：对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。

所以，MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select ... for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

慢查询日志：MySQL可以记录查询执行时间超过一定阈值的查询，这些查询被称为"慢查询"。你可以通过配置MySQL服务器来启用慢查询日志，并设置所需的阈值。

启用慢查询日志可以帮助你找出性能问题，因为你可以看到哪些查询需要花费很长时间来执行。然后，你可以使用EXPLAIN语句和其他优化技术来改进这些查询的性能。

以下是如何启用和使用MySQL慢查询日志的一些步骤：

启用慢查询日志：你可以在MySQL配置文件 (通常是my.cnf 或 my.ini) 中将 slow_query_log 设置为 ON 来启用慢查询日志。你也可以在运行中的MySQL服务器中动态地设置它：

设置慢查询阈值：你可以设置 long_query_time 系统变量来定义什么样的查询应该被记录在慢查询日志中。例如，如果你想记录所有运行时间超过1秒的查询，你可以这样设置：

设置慢查询日志文件：你还需要告诉MySQL将慢查询日志写入到哪个文件。你可以将 slow_query_log_file 系统变量设置为你想要的文件名：

一旦你配置并启用了慢查询日志，MySQL就会开始记录运行时间超过long_query_time的查询。然后，你可以定期检查这个日志，找出运行缓慢的查询，并尝试优化它们。

需要注意的是，慢查询日志可能会占用大量磁盘空间，特别是在一个繁忙的系统中，或者当long_query_time被设置得很低时。因此，你应该定期旋转和/或清理慢查询日志，以防止它占用过多的磁盘空间。你也可以考虑只在需要进行性能调优时才启用慢查询日志。

在MySQL中，特别是在使用InnoDB存储引擎的情况下，索引可以分为两类：聚集索引和非聚集索引。

聚集索引（Clustered Index）：在InnoDB中，表的数据按照主键的顺序存放，即按照聚集索引存放。因此，每个InnoDB表都有一个聚集索引。如果在创建表时没有明确指定主键，MySQL系统会寻找第一个非空的唯一索引作为主键，即作为聚集索引。如果表中没有这样的列，InnoDB会自动为每行生成一个6字节的ROWID，然后以此作为聚集索引。因为聚集索引决定了表中数据的物理存储顺序，所以一个表中只能有一个聚集索引。

非聚集索引（Non-Clustered Index，也被称为二级索引、辅助索引）：非聚集索引是不包含所有数据的索引。非聚集索引包含索引的键值和一个指向每行数据实际位置的指针。在查找过程中，数据库首先找到索引，然后使用索引中的指针找到数据。非聚集索引的键值和实际数据存储是分开的，所以在一个表里可以有多个非聚集索引。