TPCH数据库MySQL查询性能优化求助：索引无效问题

我正在使用tpch数据库，希望优化一条查询语句以提升运行速度。我尝试为li.l_orderkey、o.o_custkey和c.c_mktsegment添加索引，但性能并未得到改善，恳请各位提供优化建议。感谢！

连接信息：

conn = mysql.connect(host = 'relational.fit.cvut.cz', port = int(3306), user = 'guest', passwd = 'relational', db = 'tpch')

查询语句：

SELECT
  c.c_mktsegment,
  COUNT(o.o_orderkey) AS num_orders,
  SUM(li.l_quantity) AS total_quantity,
  SUM(li.l_extendedprice) AS total_price
FROM lineitem li
JOIN orders o
  ON li.l_orderkey = o.o_orderkey
JOIN customer c
  ON o.o_custkey = c.c_custkey
WHERE li.l_commitdate BETWEEN '1997-01-01T00:00:00Z' AND '1997-12-31T00:00:00Z'
GROUP BY c.c_mktsegment;

优化建议 #

• 优化过滤字段的覆盖索引：查询中WHERE子句通过li.l_commitdate过滤数据，这是性能瓶颈的核心点。创建包含过滤字段和查询所需所有lineitem表字段的覆盖索引，避免回表查询：

  CREATE INDEX idx_lineitem_commitdate_cover ON lineitem(l_commitdate, l_orderkey, l_quantity, l_extendedprice);
  

• 调整关联表的索引策略：
  • orders表需要关联lineitem的l_orderkey和关联customer的o_custkey，创建联合索引：

    CREATE INDEX idx_orders_orderkey_custkey ON orders(o_orderkey, o_custkey);
    

  • customer表的关联字段是c_custkey，而非分组字段c_mktsegment，创建包含关联字段和分组字段的索引：

    CREATE INDEX idx_customer_custkey_mktsegment ON customer(c_custkey, c_mktsegment);
    

• 分析执行计划定位问题：执行EXPLAIN命令查看查询执行路径，确认是否存在全表扫描、索引未命中的情况：

  EXPLAIN
  SELECT
    c.c_mktsegment,
    COUNT(o.o_orderkey) AS num_orders,
    SUM(li.l_quantity) AS total_quantity,
    SUM(li.l_extendedprice) AS total_price
  FROM lineitem li
  JOIN orders o
    ON li.l_orderkey = o.o_orderkey
  JOIN customer c
    ON o.o_custkey = c.c_custkey
  WHERE li.l_commitdate BETWEEN '1997-01-01T00:00:00Z' AND '1997-12-31T00:00:00Z'
  GROUP BY c.c_mktsegment;
  

• 检查数据类型匹配：确保li.l_commitdate的字段类型与查询中的日期字符串格式一致，避免隐式数据转换导致索引失效。
• 利用索引完成分组：由于分组字段c.c_mktsegment已包含在customer表的联合索引中，MySQL可直接通过索引完成分组操作，减少额外的排序开销。

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