WHERE查询太慢了，怎么办

覆盖索引

• 覆盖索引指的是：查询语句从执行到返回结果均使用同一个索引
• 覆盖索引可以有效减少回表

  CREATE TABLE inventory_1 (
  inventory_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  film_id SMALLINT UNSIGNED NOT NULL,
  store_id TINYINT UNSIGNED NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (inventory_id),
  KEY idx_fk_film_id (film_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  INSERT INTO inventory_1 SELECT * FROM inventory;

  CREATE TABLE inventory (
  inventory_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  film_id SMALLINT UNSIGNED NOT NULL,
  store_id TINYINT UNSIGNED NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (inventory_id),
  KEY idx_fk_film_id (film_id),
  KEY idx_store_id_film_id (store_id,film_id),
  CONSTRAINT fk_inventory_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_inventory_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  

  查询语句1：

  explain select store_id,film_id from sakila.`inventory_1` where store_id = 1;
  

  +----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
  | id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  +----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
  |  1 | SIMPLE      | inventory_1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4581 |    10.00 | Using where |
  +----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
  1 row in set, 1 warning (0.00 sec)
  

  
  type是all，没有走索引。

查询语句2：

explain select store_id,film_id from sakila.`inventory` where store_id = 1;

+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | inventory | NULL       | ref  | idx_store_id_film_id | idx_store_id_film_id | 1       | const | 2270 |   100.00 | Using index |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

查看Extra这里，是Using index，在查询计划里就叫索引覆盖，就是说使用了同一条索引，不需要回表，使用一个索引就能完成对数据的搜索和对数据结果的返回这个操作。
查看两张表的索引：

mysql> show index from inventory;
+-----------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name             | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| inventory |          0 | PRIMARY              |            1 | inventory_id | A         |        4581 |     NULL | NULL   |      | BTREE      |         |               |
| inventory |          1 | idx_fk_film_id       |            1 | film_id      | A         |         958 |     NULL | NULL   |      | BTREE      |         |               |
| inventory |          1 | idx_store_id_film_id |            1 | store_id     | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| inventory |          1 | idx_store_id_film_id |            2 | film_id      | A         |        1521 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

mysql> show index from inventory_1;
+-------------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name       | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| inventory_1 |          0 | PRIMARY        |            1 | inventory_id | A         |        4581 |     NULL | NULL   |      | BTREE      |         |               |
| inventory_1 |          1 | idx_fk_film_id |            1 | film_id      | A         |         958 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

我们来查看分析这个查询语句
select store_id,film_id from sakila.inventory where store_id = 1;
联合索引是idx_store_id_film_id，“带头大哥”是store_id，所以走了索引，查询语句要查询两个字段，一个是store_id一个是film_id，按理说，先在联合索引这棵B+树中，现根据store_id为key，查找出value是主键id，然后再回表去根据主键id去聚簇索引中查找表数据中，返回film_id这个字段。但是我们是一个联合索引，有store_id和film_id，我们刚好要查film_id，所以就不需要回表了。
查询语句3：

mysql> explain mysql> explain select inventory_id, store_id, film_id from sakila.`inventory` where store_id = 1;
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | inventory | NULL       | ref  | idx_store_id_film_id | idx_store_id_film_id | 1       | const | 2270 |   100.00 | Using index |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

为什么查询这三个字段也能走索引覆盖？为什么不用回表？
inventory_id明明不在联合索引里面，为什么也不用回表查询这个字段。因为inventory_id是主键，本来是来回表用的。回表的操作就是，先查出主键，再利用主键去查询其他字段。
查询语句4：

mysql> explain select inventory_id,store_id,film_id,last_update from sakila.`inventory` where store_id = 1;
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | inventory | NULL       | ref  | idx_store_id_film_id | idx_store_id_film_id | 1       | const | 2270 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

走了索引，但是Extra为NULL，说明回表了。先根据store_id查找出主键，在根据主键去聚簇索引中查找这条记录，并解析出来last_update这个字段。

总结

• 覆盖索引通过取消回表操作，提升查询效率
• 若数据的查询不只使用了一个索引，则不是覆盖索引
• 可以通过优化SQL语句或优化联合索引，来使用覆盖索引

这就是为什么不使用select * 这种操作。