MySQL课堂笔记：索引与查询优化

"b站动力节点2020版mysql课堂笔记day04" 在MySQL数据库中，索引（index）是提升查询性能的关键工具。索引可以理解为数据库中的目录，帮助数据库系统快速找到数据的位置，从而减少数据检索的时间。本节课主要讲解了索引的类型、创建方法以及不同索引的特点。 1. 索引的分类： - 主键索引（Primary Key Index）：每个表只能有一个主键，它确保了数据的唯一性和完整性。例如，`t_user`表中的`id`字段可能就是主键，其对应的索引称为`idIndex`。 - 唯一索引（Unique Index）：不允许出现重复的值，但允许NULL。如`nameIndex`，`emailIndex`和`emailAddressIndex`可能是唯一索引。 - 普通索引（Index）：无唯一性限制，是最基本的索引类型，如`addressIndex`。 2. 索引的存储结构： - B-Tree索引：这是最常见的索引结构，适用于全值匹配查询，如`SELECT * FROM t_user WHERE name='jack'`，此查询将利用`nameIndex`快速定位到`jack`所在的位置。 - 聚集索引（Clustered Index）：索引的顺序决定了数据行在磁盘上的物理存储顺序。主键索引通常是聚集索引，如`idIndex`，数据行按照`id`的值排序。 - 非聚集索引（Non-Clustered Index）：索引结构与数据行分开存储，索引项指向数据行的实际位置。`nameIndex`可能是非聚集索引，因为它不按`name`的顺序存储数据行。 3. 索引的选择： - 选择性高的列（如`name`列）更适合创建索引，因为不同的值越多，索引的效果越好。 - 对于经常出现在`WHERE`子句中的列，创建索引可以显著提高查询速度。 - 但是，索引也有其缺点，比如会占用额外的存储空间，并且在插入、删除和更新数据时需要维护索引，可能导致性能下降。 4. 索引的其他类型： - 全文索引（Full-text Index）：用于全文搜索，MySQL中MyISAM存储引擎支持全文索引。 - 哈希索引（Hash Index）：适用于等值查询，但不支持范围查询，MyISAM和InnoDB存储引擎中都有使用。 5. 实例分析： - 在示例中，`t_user`表包含`id`、`name`、`email`和`address`四个字段，`id`是主键，其他字段可能有相应的唯一索引。 - 查询语句`SELECT * FROM t_user WHERE name='jack'`利用`nameIndex`快速定位到`jack`，提高了查询效率。 6. 实际应用： - 为了提高查询效率，可以考虑对`name`这样的常用查询条件创建索引。 - 当存储引擎如MyISAM使用B-Tree索引时，数据文件（`.MYD`）和索引文件（`.MYI`）是分离的。 - 对于`id`这样的主键，其索引通常是聚集索引，数据行按照`id`的升序排列。 索引是优化数据库查询性能的重要手段，但需根据实际需求和数据特性谨慎选择，以达到最佳效果。在设计数据库时，应充分考虑索引策略，以平衡查询速度和存储成本。