浅谈 MyISAM 和 InnoDB 的区别与优化

在MySQL数据库中，MyISAM和InnoDB是两种常见的存储引擎。它们之间有很多区别，在不同的场景下选用不同的存储引擎可以提高系统的性能和可靠性。

MyISAM

特点

• 不支持事务处理，也就是说，不支持ACID的特性。
• 表级锁定：在对MyISAM表进行读写操作时，MySQL会对整张表进行锁定，避免了悲观锁的开销。但是表级锁定对于高并发的写操作不友好。
• 支持全文索引：MyISAM引擎可以在文本字段上建立全文索引，可以快速地进行文本内容的搜索。
• 适用于读密集型的系统。

优化

• 合理使用缓存：MyISAM表的读操作可以借助于缓存机制，节省了对于磁盘的访问。因此，在高并发、读操作频繁的场景下可以通过合理使用缓存降低磁盘的IO开销，从而提高读取数据的速度。具体的做法可以通过配置MySQL的参数 key_buffer_size 来调整缓存的大小。
• 垂直拆分：在读写操作比较平衡、表访问量较大时，可以考虑将大的表拆分成多个小的表，避免了表级锁定，从而提高并发性。
• 全文索引的合理使用：MyISAM引擎可以在文本字段上建立全文索引，可以快速地进行文本内容的搜索。但是全文索引的建立会对写入性能产生一定的影响。在使用全文索引时需要权衡好读写的比例，避免过多的建立全文索引导致写入性能下降。

示例

• 假设有一个博客网站，需要针对标题和内容字段进行搜索，可以使用MyISAM引擎，并建立全文索引，提高搜索内容的速度。

CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title_idx` (`title`),
  FULLTEXT KEY `content_idx` (`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

• 在读取数据的场景中，使用MyISAM引擎可以通过调整 key_buffer_size 参数来提高缓存的命中率。假设有一个用户表，用于存储用户信息，可以通过 SHOW TABLE STATUS 命令查看表的索引情况。

SHOW TABLE STATUS LIKE 'user';

可以看到表的索引情况：

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| user | MyISAM |      10 | Dynamic    |    0 |              0 |        1024 |    281474976710655 |         1024 |         0 |                | 2022-01-01 00:00:00 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

可以看到 Index_length 的值为1KB，说明用户表的索引比较小，可以通过调整 key_buffer_size 参数来增大缓存的容量。

SET GLOBAL key_buffer_size = 1024*1024*10;

InnoDB

特点

• 支持事务处理和ACID的特性。
• 行级锁定：InnoDB引擎可以实现行级锁定，避免了表级锁定对高并发操作的影响。
• 支持外键：InnoDB支持外键约束，可以保证数据的完整性。
• 适用于写密集型的系统。

优化

• 合理设置缓存：InnoDB引擎支持内部缓存池，数据的读取和写入都可以先放在缓存中，减少IO开销，提高性能。可以通过调整 innodb_buffer_pool_size 参数来控制InnoDB缓存的大小，提高缓存命中率。
• 垂直拆分：在写入操作比较平衡、表访问量较大时，可以考虑将大的表拆分成多个小的表，以降低锁定的粒度，避免了行级锁定对于写入性能的影响。
• 合理使用外键：使用外键可以保证数据的完整性，但是也会对写入性能产生影响，因此需要权衡好完整性和性能。

示例

• 假设有一个在线支付平台，需要记录用户的交易记录，可以使用InnoDB引擎，并使用外键约束来保证用户ID的完整性。

CREATE TABLE `transaction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 在写入数据的场景中，使用InnoDB引擎可以通过调整 innodb_buffer_pool_size 参数来提高缓存的命中率。假设有一个商城系统，在处理订单信息时，经常需要读取订单表中的数据。

SHOW TABLE STATUS LIKE 'order';

可以看到表的索引情况：

+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| order | InnoDB |      10 | Dynamic    |    0 |              0 |        1024 |    281474976710655 |         1024 |         0 |                | 2022-01-01 00:00:00 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

可以看到 Index_length 的值为1KB，说明订单表的索引比较小，可以通过调整 innodb_buffer_pool_size 参数来增大缓存的容量。

SET GLOBAL innodb_buffer_pool_size = 1024*1024*10;

总结

• MyISAM适用于读密集型的系统，优化重点在于缓存机制和全文索引。
• InnoDB适用于写密集型的系统，优化重点在于缓存机制和行级锁定。
• 在实际应用中，应根据具体的应用场景选择合适的存储引擎，综合考虑表的读写比例、并发访问量、数据完整性等因素。