本文共 1579 字，大约阅读时间需要 5 分钟。

MySQL索引基础知识

索引是什么？

索引是MySQL用于快速定位数据的数据结构。它存储在磁盘上，帮助数据库快速找到所需数据。索引通过预先排序数据，使得查询时可以更快地定位所需记录，而不是从头开始扫描整个表。

为什么需要索引？

在大型数据库中，查询操作往往需要扫描整个表才能找到匹配的记录，这种方法时间复杂度很高。通过使用索引，数据库可以快速定位到目标记录，显著提高查询效率。

MySQL中的索引类型

B+树索引

MySQL默认使用B+树索引。B+树是一种多路搜索树，每个节点可以有多个子节点。这种结构能够有效降低树的高度，减少磁盘IO次数。

B+树的优点：

主键索引与辅助索引

主键索引：

• 每个表必须有一个主键索引。
• 主键索引用于快速定位记录，通常用于PRIMARY KEY或UNIQUE约束的字段。

辅助索引：

• 辅助索引以主键索引的值为关键词。
• 辅助索引用于支持WHERE、ORDER BY和GROUP BY等操作，尤其是涉及多个字段的复杂查询。

索引的使用场景

精确匹配查询

SELECT * FROM titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

• 索引使用情况：这个查询使用了emp_no、title和from_date三个字段的索引。
• 优化效果：精确匹配查询效率极高，通常直接访问叶子节点。

范围查询

SELECT * FROM titles WHERE emp_no > '10001';

• 索引使用情况：使用emp_no字段的索引。
• 优化效果：通过B+树结构快速定位起始记录，减少磁盘IO次数。

联合索引

CREATE INDEX idx_emp_title ON titles (emp_no, title);

• 索引结构：联合索引同时包含emp_no和title字段。
• 查询优化：联合索引在满足最左前缀原理时，能够显著提升查询效率。

最左前缀原理

最左前缀原理是联合索引优化的关键。数据库在查询时，首先使用联合索引的最左边字段进行匹配。如果匹配成功，才会继续检查后续字段。例如：

EXPLAIN SELECT * FROM employees WHERE NAME = 'LiLei' AND age = 22 AND POSITION ='manager';

• 索引使用情况：如果NAME字段是联合索引的一部分，查询会优先使用该索引。
• 优化效果：最左前缀匹配成功后，后续字段查询仍然可以利用索引，提高效率。

如何选择索引

选择合适的索引类型

• 单字段索引：适用于单字段查询，如WHERE emp_no = '10001'。
• 联合索引：适用于多字段查询，尤其是涉及范围或排序的查询。
• 覆盖索引：用于包含所有查询字段的索引，减少索引查找次数。

避免常见错误

• 避免过度索引：不为每个字段都创建索引，尤其是复合索引。
• 避免长文本字段索引：对长文本字段（如varchar(500)）创建索引通常没有实际意义。

EXPLAIN命令的使用

使用EXPLAIN命令可以分析查询执行计划，观察是否使用了索引。执行计划中的type字段表示查询类型，值从优到差依次为：system > const > eq_ref > ref > range > index > ALL。

总结

通过合理设计和使用索引，可以显著提升MySQL数据库的查询效率。理解索引的原理和使用场景，是数据库优化的关键。

转载地址：http://cadfk.baihongyu.com/