mysql - 大表的SQL优化和基于键的选择

Question

我试图将我们服务器上的响应时间降低到 250 毫秒范围内，大多数查询花费的时间不到一毫秒，这个查询本身需要很长时间。由于行数较多，超过 1500 毫秒。这是数据结构，是否可以采取任何措施来减少此查询的时间，但使用不同的查询或更快地设置我的数据？

mysql> describe variant_bikes;

+------------+-----------------------+------+-----+---------+-------+
| Field      | Type                  | Null | Key | Default | Extra |
+------------+-----------------------+------+-----+---------+-------+
| variant_id | mediumint(8) unsigned | NO   | PRI | 0       |       |
| bike_id    | mediumint(8) unsigned | NO   | PRI | 0       |       |
+------------+-----------------------+------+-----+---------+-------+

mysql> describe cscart_product_option_variants;

+----------------------+-----------------------+------+-----+---------+----------------+
| Field                | Type                  | Null | Key | Default | Extra          |
+----------------------+-----------------------+------+-----+---------+----------------+
| variant_id           | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
+----------------------+-----------------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)

mysql> describe bikefilter_cache;
+---------+-----------------------+------+-----+---------+----------------+
| Field   | Type                  | Null | Key | Default | Extra          |
+---------+-----------------------+------+-----+---------+----------------+
| bike_id | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| year    | smallint(6) unsigned  | NO   | PRI | NULL    |                |
| make    | varchar(20)           | NO   | PRI | NULL    |                |
| line    | varchar(20)           | NO   | PRI | NULL    |                |
| model   | varchar(90)           | NO   | PRI | NULL    |                |
+---------+-----------------------+------+-----+---------+----------------+

mysql> select count(*) from variant_bikes;

+----------+
| count(*) |
+----------+
|  7577597 |
+----------+
1 row in set (1.85 sec)

mysql> select variant_id from variant_bikes where bike_id=112;
9366 rows in set (2.30 sec)

我之前尝试过的一件事是让variant_bikes成为一个带有variant_id的表，但我认为至少在当时的大小下要慢得多，但bike_ids字段是varchar和/或文本，并且您通过逗号进行搜索分隔列表。

我想到的另一件事可能是将所有表排列成不同的更有效的数据结构。

Answer 1

为了获得该查询的最佳性能，请添加索引ONvariant_bikes (bike_id,variant_id)。

这将是查询的“覆盖”索引，MySQL 将能够仅满足“使用索引” block 的查询，而无需引用(查找)表中的任何数据 block 。

问:当(variant_id, bike_id)上已经有索引时，为什么我们还需要这个索引？

A:它与索引中列的顺序有关。查询中的谓词(WHERE 子句)位于 bike_id 列上。为了使 MySQL 使用索引，该列需要显示为索引中的前导列。

问:如果我仅在 (bike_id) 列上有索引会怎样。

A: MySQL 很可能使用该索引。查询还需要从 variant_id 列返回值...为了获得最佳性能，我们希望仅满足来自索引 block 的查询。根据 MySQL 和存储引擎的版本，MySQL 可能能够从 (bike_id) 上的索引返回 variant_id，因为主键列的值是“指针”返回到数据 block 。

您可以尝试添加该索引，然后运行EXPLAIN SELECT ...，您希望看到的最后一列(额外)是“使用索引”，这表明查询是从索引中即可满足，无需引用表中的 block 。

... key             Extra        
--- -------------   -------------
    bike_id_index   Using index