索引下推

  1. CREATE TABLE `inventory_3` (
  2.   `inventory_id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  3.   `film_id` smallint unsigned NOT NULL,
  4.   `store_id` tinyint unsigned NOT NULL,
  5.   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  6.   PRIMARY KEY (`inventory_id`),
  7.   KEY `idx_store_id_film_id` (`store_id`, `film_id`)
  8. ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;
  10. INSERT into inventory_3 SELECT * from inventory;
  1. SELECT * FROM `inventory_3` WHERE store_id in (1,2) and film_id = 3;

请问上面这条语句会走索引吗？不能

image.png
带头大哥是store_id，同一个大哥，小弟才排序。小弟是次要排序手段。主要排序是一样的时候，次要排序才是排好序的。这样上面的sql语句都是等号，才能走联合索引。
所以这个步骤是这样的：
先把store_id in (1,2)找出来，找出4条记录，然后获取inventory_id，根据inventory_id回表，去解析film_id是否等于3，而不是直接在这条索引中判断film_id = 3，所以这里回表了4次。那可不可以直接在索引里找film_id = 3的呢？这样就避免了回表之后的解析操作。虽然film_id不是按顺序来的，但也总比回表一个个解析好。
其实是可以的，5.6之后，这样叫做索引下推。
就是说在联合索引中筛选出来store_id in (1,2)的记录，然后直接在索引里一个个择出film_id，判断是否等于3，等于3的话就拿出inventory_id进行回表操作，这样就减少了回表的次数。
索引下推：using index condition

  • 为什么扫描行数非常少？
  • MySQL5.6之前，需要先使用索引查询store_id in (1,2)再全部回表校验film_id = 3

  • MySQL5.6之后，如果索引可以判断，直接使用索引过滤

    松散索引扫描

    Select film_id from inventory_3where film_id = 3;
    带头大哥没了。

  • 带头大哥不能死，中间兄弟不能丢？为什么不管带头大哥了？

  • MySQL 8.0新特性

image.png

总结

  • 联合索引可以加速一些特殊查询场景
  • 索引下推可以大大减少回表次数
  • 松散索引扫描可以打破“左侧原则”，解决带头大哥丢失的问题