MySQL覆盖索引

覆盖索引（Covering Index）是数据库优化中的一种重要技术
覆盖索引是指一个查询语句在执行时，所需的数据可以完全通过索引来获取，而无需访问实际的数据行。也就是说，查询语句所需的列都包含在了创建的索引中，从而避免了读取实际数据行的过程，提高了查询性能。

原理

索引存储：覆盖索引要存储索引列的值。在数据库中，索引通常用于快速定位数据行的位置。当索引包含了查询所需的所有字段数据时，数据库就可以直接从索引中获取所需数据，而无需访问数据表。
减少数据访问：由于覆盖索引包含了查询所需的所有列，因此数据库无需再次访问表的数据，从而减少了磁盘I/O操作。
提高查询效率：覆盖索引减少了数据库需要读取的数据量，从而提高了查询速度。同时，由于索引按值顺序存储，范围查找的I/O操作比随机读取每一行数据的I/O要少得多。
特点
非聚集复合索引的一种形式：覆盖索引通常是非聚集复合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列。
避免回表操作：在InnoDB表中，如果二级索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询，即避免回表操作。
使用B+树索引：MySQL使用B+树索引做覆盖索引，因为哈希索引、全文索引不存储索引列的值。

应用场景

查询列较少：当查询的列较少，且这些列都包含在索引中时，可以使用覆盖索引来提高查询性能。
高并发环境：在高并发环境下，减少对数据表的访问可以减少锁的竞争，从而提高并发性能。覆盖索引通过直接从索引中获取所需数据，减少了对数据表的访问次数，因此适用于高并发环境。
应用
假设有一个学生表（student），包含以下字段：id（主键）、name、age、score。
创建覆盖索引：在name列上创建一个索引，并假设我们需要查询name为“张三”的学生的分数信息。由于我们只需要查询分数信息，而索引中已经包含了分数信息（假设在创建索引时包含了score列，但实际操作中通常不会这样做，这里仅为说明覆盖索引的原理），所以数据库无需再次访问表的数据，可以直接从索引中获取分数信息。然而，在实际应用中，我们更可能在name列上创建索引，并在查询时使用SELECT *或选择多个列，此时如果索引不能覆盖所有查询列，则无法形成覆盖索引。但以下示例仅用于说明覆盖索引的概念：

CREATE INDEX idx_name_score ON student(name, score); – 假设为了说明覆盖索引而这样创建，实际中可能不这样做

SELECT score FROM student WHERE name=‘张三’; – 此时可以使用覆盖索引
注意：上述SQL语句中的索引创建方式仅为说明覆盖索引的原理，实际中我们不会为了一个查询而特意创建一个包含所有查询列的索引，因为这会导致索引过大且维护成本增加。通常，我们会根据实际的查询需求和表结构来合理地创建索引。
undefined 使用联合索引形成覆盖索引：在name和age列上创建一个联合索引，并查询name为“张三”且age为20岁的学生信息。虽然这个查询没有直接使用覆盖索引（因为查询了所有列），但如果查询只涉及name和age列，则联合索引可以形成覆盖索引，提高查询性能。

CREATE INDEX idx_name_age ON student(name, age);

SELECT name, age FROM student WHERE name=‘张三’ AND age=20; – 此时联合索引可以形成覆盖索引（如果查询只涉及这两个列）

注意事项

权衡存储和维护成本：虽然覆盖索引可以提高查询性能，但它也可能带来额外的存储和维护成本。因此，在创建覆盖索引时需要权衡这些因素。
避免过度索引：不要为了形成覆盖索引而过度创建索引，因为过多的索引会导致写操作性能下降和存储空间增加。
根据实际情况创建索引：应根据实际的查询需求和表结构来合理地创建索引，以平衡查询性能和存储成本。
综上所述，覆盖索引是一种有效的数据库优化技术，通过减少数据访问和降低I/O操作次数来提高查询性能。然而，在创建和使用覆盖索引时，需要权衡存储和维护成本，并根据实际情况进行合理的索引设计。