MySQL常用技术与InnoDB与MyISAM的区别

“mysql总结.docx 提供了关于MySQL数据库的常见技术和概念，包括索引创建的原则和失效情况，以及InnoDB和MyISAM存储引擎的区别。” 在MySQL数据库管理中，索引是提升查询性能的关键元素。创建索引的原则主要包括以下几点： 1. 对于频繁进行排序、分组和联合操作的字段，应建立索引，因为这些操作通常涉及全表扫描，索引能显著提高执行效率。 2. 经常用作查询条件的字段应设置索引，以避免全表扫描，提高查询速度。 3. 尽量选择唯一索引，因为重复值会降低索引的效能。 4. 索引数量要适度，过多的索引会影响插入和更新操作的速度。 5. 数据量少的索引更高效，因为索引本身也需要存储空间。 6. 使用字段的前缀创建索引可以减少索引大小，但可能会导致部分覆盖索引，影响查询效率。 7. 定期删除不再使用的或使用频率极低的索引，以维护数据库的整洁和高效。 8. 遵循最左前缀匹配原则，这在使用复合索引时尤其重要。 9. 选择区分度高的列作为索引，可以减少索引的重复，提高查询效率。 10. 索引列不应参与计算，确保列值的纯粹性。 然而，索引并非总是有效，以下情况下索引可能失效： 1. 当索引列使用函数、表达式或运算符（如替换、子串、连接、求和、平均等）时。 2. 字符串未用引号包裹，可能导致隐式类型转换。 3. LIKE操作符在模式中以百分比符号（%）开头，导致全表扫描。 4. 联合索引中，前面列的范围查询会导致后续列的索引失效。 5. 查询结果占比超过一定阈值（约30%）时，MySQL可能不使用索引。 6. NOT LIKE、!=（<>）和NOT IN在某些特定情况下也可能不使用索引。 接下来，我们讨论InnoDB和MyISAM这两个存储引擎的主要区别： 1. 事务处理和外键支持：InnoDB支持ACID事务和外键约束，保证数据的安全性和完整性，适合有大量增删改操作的场景；而MyISAM不支持这些特性，适合读操作频繁的应用。 2. 锁机制：InnoDB采用行级锁，允许更高的并发性能，而MyISAM使用表级锁，对写操作并发性较差，但在读操作上表现良好。 3. 索引结构：InnoDB使用聚集索引，索引和数据存储在同一块，提高访问速度；MyISAM是非聚集索引，索引和数据分开存储。 4. 并发处理：InnoDB通过MVCC（多版本并发控制）支持高并发，而MyISAM的并发处理能力相对较弱。 5. 存储文件：InnoDB表由.frm表结构文件和.ibd数据文件组成，支持更大容量的表（最大64TB）；MyISAM则有.frm、.MYD（数据）和.MYI（索引）三个文件。 理解这些基本概念和原则对于优化MySQL数据库性能至关重要，尤其是在设计和调整索引策略时，以及在选择合适存储引擎时。