非自增非连续ID的MySQL表查询加速方案咨询

问题背景 #

我在MySQL中维护一组数据表，通过PHP程序频繁执行查询操作，其中两张最大的表（food和portions）约包含16万条数据。一个逻辑复杂度不高的查询耗时却达到约3秒，示例查询语句如下：

select 
  if(
    p.gram_weight is null 
    or p.gram_weight = 0, 
    100, 
    cast(p.gram_weight as unsigned)
  ) as gms, 
  if(
    p.gram_weight is null 
    or p.gram_weight = 0, 
    '100 grams', 
    if(
      p.amount = 0, 
      p.portion_description, 
      concat_ws(
        ' ', 
        p.amount, 
        if(
          p.modifier = '', m.name, p.modifier
        )
      )
    )
  ) as unit 
from 
  food f 
  left join portions p on f.id = p.fdc_id 
  left join measures m on p.measure_unit_id = m.id 
where 
  f.description = $key 
order by 
  p.gram_weight;

主表food中的ID并非连续自增（大部分是），且这些ID具有业务意义，无法修改。核心疑问：最有效的查询加速方式是什么？是否需要添加索引列（仅主表？还是所有涉及的表？），或者无需调整ID的连续性即可使用现有ID进行优化？

EXPLAIN 执行结果 #

> explain select if(p.gram_weight is null or p.gram_weight = 0, 100, cast(p.gram_weight as unsigned)) as gms,          if(p.gram_weight is null or p.gram_weight = 0, '100 grams', if(p.amount = 0, p.portion_description,          concat_ws(' ', p.amount, if(p.modifier = '', m.name, p.modifier)))) as unit from food f left join portions p          on f.id=p.fdc_id left join measures m on p.measure_unit_id = m.id where f.description=quote('W and m, Roasted Coconut Chips') order by p.gram_weight\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: f
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 158406
     filtered: 10.00
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 52560
     filtered: 100.00
        Extra: Using where; Using join buffer (Block Nested Loop)
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: health.p.measure_unit_id
         rows: 1
     filtered: 100.00
        Extra: NULL
3 rows in set, 1 warning (0.00 sec)

数据表结构 #

*** ben@localhost health> describe food;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| id          | int(11) | NO   | PRI | NULL    |       |
| description | text    | YES  |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

*** ben@localhost health> describe measures;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| name  | text    | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

*** ben@localhost health> describe portions;
+---------------------+---------+------+-----+---------+-------+
| Field               | Type    | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| id                  | int(11) | NO   | PRI | NULL    |       |
| fdc_id              | int(11) | YES  |     | NULL    |       |
| amount              | float   | YES  |     | NULL    |       |
| measure_unit_id     | int(11) | YES  |     | NULL    |       |
| portion_description | text    | YES  |     | NULL    |       |
| modifier            | text    | YES  |     | NULL    |       |
| gram_weight         | float   | YES  |     | NULL    |       |
+---------------------+---------+------+-----+---------+-------+
7 rows in set (0.00 sec)

优化方案分析 #

从EXPLAIN结果可以明确当前查询的性能瓶颈：

1. food表执行全表扫描（type: ALL），无可用索引，需扫描15万+行，这是核心瓶颈。
2. portions表同样全表扫描，依赖连接缓冲区（Block Nested Loop）关联，需扫描5万+行，且排序触发Using filesort。
3. measures表通过主键关联（eq_ref），已处于最优状态，无需调整。

1. 针对food表添加索引 #

查询通过f.description = $key过滤数据，description为TEXT类型，需创建适配的索引：

• 前缀索引：如果是精确匹配，且description实际长度不长，创建前缀索引避免全表扫描：

CREATE INDEX idx_food_description ON food(description(100));

• 覆盖索引：将id包含进索引，避免回表查询（关联需要用到f.id），进一步提升效率：

CREATE INDEX idx_food_description_id ON food(description(100), id);

注：如果description内容长度较短，可考虑将其改为VARCHAR类型，索引效率会更高。

2. 针对portions表添加复合索引 #

portions表通过fdc_id关联food.id，且查询需按gram_weight排序，创建复合索引同时满足关联和排序需求：

CREATE INDEX idx_portions_fdcid_gramweight ON portions(fdc_id, gram_weight);

该索引可快速定位对应fdc_id的所有记录，且直接从索引中获取排序后的数据，消除Using filesort和全表扫描的开销。

3. 关于ID连续性的问题 #

food表的ID是否连续不影响优化效果，主键本身已是索引，我们需要的是针对查询条件、关联和排序场景的专用索引，无需调整ID连续性，也不用修改现有业务ID。

可选优化（优先级较低） #

• 对于查询中的多层IF逻辑，可考虑用**生成列（Generated Column）**预先计算结果，但这部分对性能的提升远小于索引优化，可在索引调整后按需尝试。

内容的提问来源于stack exchange，提问作者bluewater_sailor