产生原因
1. 什么是回表
回表查询(回表)是指在数据库查询过程中,数据库引擎通过索引找到符合条件的记录后,还需要回到数据表中获取实际需要的列数据的过程。
这通常会增加额外的I/O操作,从而降低查询性能。通过合理的索引设计,可以避免或减少回表查询,提升查询效率。
2. MySQL的InnoDB引擎的回表
2.1 主键索引
在InnoDB中,索引分为主键索引(聚簇索引)和非主键索引,主键索引默认是自动创建的,分为显式创建和隐式创建。当你在表中明确指定了一个主键(PRIMARY KEY),InnoDB会自动为该主键创建一个聚簇索引,这就是显示创建主键索引。如果没有指定主键,那么InnoDB会去寻找有没有UNIQUE索引,如果存在,并且列全部为非空,那么InnoDB将使用该索引为聚簇索引,也就是主键索引。如果不存在这样的UNIQUE索引,那么InnoDB会自动创建一个隐藏的列作为主键,该列通常是一个6字节的行ID,然后为该列创建聚簇索引。
不过还是推荐显示创建主键索引,因为隐式主键索引存在一些问题,比如:无法用于优化查询,难以维护,一些高级功能(分区表,外键约束等)无法使用。
主键索引的叶子节点存储的是完整的行数据,可以通过主键索引直接获取完整的行数据。
2.2 非主键索引
非主键索引也叫二级索引,它和主键索引不同的是,它的叶子节点存放的不是完整的行数据,而是主键的值,所以通过二级索引查找数据时,需要先通过二级索引查找到主键的值,再通过主键值访问主键索引的B+树,获取完整的行数据,这个过程就是回表
例如:
SELECT * FROM user WHERE name='liming';
需要先通过二级索引(如name
列的索引)找到对应的主键值,然后再通过主键值访问主键索引的B+树,获取完整的行数据。这需要两次B+树的访问,即一次通过二级索引查找主键值,再一次通过主键索引查找完整数据。这就是InnoDB中的回表查询。
优化方法
1、编写SQL查询语句时,优先使用主键索引
2、创建覆盖索引(即包含所有需要查询的列的索引),覆盖索引包含查询所需的所有列,从而可以直接从索引中获取所有需要的数据,无需回表。例如:
CREATE INDEX idx_name_age ON users (name, age);
3、调整索引列的顺序,把常用的列放到前面