MySQL：覆盖索引与回表

后端 MySQL

发布时间 : 2024-09-25 00:00

字数:534 阅读 :

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。也就是不需要回表再去查询其他列的数据

尽量使用覆盖索引，减少select *


mysql> explain select id, profession from tb_user where profession = '软件工程' and age = 31 and status = '0' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_user
   partitions: NULL
         type: ref
possible_keys: idx_user_pro_age_sta,idx_user_age,idx_user_pro
          key: idx_user_pro_age_sta
      key_len: 42
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

mysql> explain select id,profession,age, status from tb_user where profession = '软件工程' and age = 31 and status = '0'\G ;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_user
   partitions: NULL
         type: ref
possible_keys: idx_user_pro_age_sta,idx_user_age,idx_user_pro
          key: idx_user_pro_age_sta
      key_len: 42
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

上面的Extra字段都是Using index，意思是查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

mysql> explain select id,profession,age, status, name from tb_user where profession = '软件工程' and age = 31 and status = '0' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_user
   partitions: NULL
         type: ref
possible_keys: idx_user_pro_age_sta,idx_user_age,idx_user_pro
          key: idx_user_pro_age_sta
      key_len: 42
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tb_user where profession = '软件工程' and age = 31 and status= '0'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_user
   partitions: NULL
         type: ref
possible_keys: idx_user_pro_age_sta,idx_user_age,idx_user_pro
          key: idx_user_pro_age_sta
      key_len: 42
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

Using index condition这个在MySQL5以上的版本才会有。

Using index condition：查找使用了索引，但是需要回表查询数据

关于回表的问题，可以查看：索引的原理那块内容

思考题：一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对以下SQL语句进行优化, 该如何进行才是最优方案:

select id,username,password from tb_user where username ='itcast';

答案: 针对于 username, password建立联合索引, sql为：

create index idx_user_name_pass on tb_user(username,password);

这样可以避免上述的SQL语句，在查询的过程中，出现回表查询。

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 1909773034@qq.com