首页 文章

为什么MySQL无法优化此查询？

提问于
浏览 次
9

我有一个问题给我带来了问题，我无法理解为什么MySQL的查询优化器的行为方式如此 . 这是背景信息：

我有3张 table . 两个相对较小，一个较大 .

表1（非常小，727行）：

CREATE TABLE ipa（ipa_id int（11）NOT NULL AUTO_INCREMENT，ipa_code int（11）DEFAULT NULL，ipa_name varchar（100）DEFAULT NULL，payorcode varchar（2）DEFAULT NULL，compid int（11）DEFAULT'2'PRIMARY KEY（ ipa_id），KEY ipa_code（ipa_code））ENGINE = MyISAM

表2（小，59455行）：

CREATE TABLE assign_ipa（assignid int（11）NOT NULL AUTO_INCREMENT，ipa_id int（11）NOT NULL，userid int（11）NOT NULL，username varchar（20）DEFAULT NULL，compid int（11）DEFAULT NULL，PayorCode char（10） ）DEFAULT NULL PRIMARY KEY（assignid），UNIQUE KEY assignid（assignid，ipa_id），KEY ipa_id（ipa_id））ENGINE = MyISAM

表3（大，24,711,730行）：

CREATE TABLE master_final（IPA int（11）DEFAULT NULL，MbrCt smallint（6）DEFAULT'0'，PayorCode varchar（4）DEFAULT'WC'，KEY idx_IPA（IPA））ENGINE = MyISAM DEFAULT

现在进行查询 . 我正在使用前两个较小的表进行3向连接，以便在其中一个索引值上对大表进行子集化 . 基本上，我得到一个用户的ID列表，SJOnes并查询这些ID的大文件 .

mysql>说明SELECT master_final.PayorCode，sum（master_final.Mbrct）AS MbrCt FROM master_final INNER JOIN ipa ON ipa.ipa_code = master_final.IPA INNER JOIN assign_ipa ON ipa.ipa_id = assign_ipa.ipa_id WHERE assign_ipa.username ='SJones'GROUP BY master_final.PayorCode，master_final.ipa \ G; ************* 1.行************* id：1 select_type：SIMPLE表：master_final类型：ALL possible_keys：idx_IPA key：NULL key_len：NULL ref：NULL行：24711730额外：使用临时;使用filesort ************* 2.行************* id：1 select_type：SIMPLE表：ipa类型：ref possible_keys：PRIMARY，ipa_code key： ipa_code key_len：5 ref：wc_test.master_final.IPA行：1额外：使用************* 3.行************* id：1 select_type：SIMPLE表：assign_ipa类型：ref possible_keys：ipa_id key：ipa_id key_len：4 ref：wc_test.ipa.ipa_id rows：37 Extra：使用set中的3行（0.00秒）

此查询需要永远（如30分钟！） . 解释声明告诉我为什么，它正在对大表进行全表扫描，即使有一个非常好的索引 . 它没有使用它 . 我不明白这一点 . 我可以查看查询，看看它只需要查询大表中的几个ID . 如果我能做到，为什么MySQL的优化器不能这样做呢？

为了说明，这里是与'SJones'相关联的ID：

mysql> select username，ipa_id from assign_ipa where username ='SJones'; ---------- -------- |用户名| ipa_id | ---------- -------- | SJones | 688 | | SJones | 689 | ---------- -------- 2行（0.02秒）

现在，我可以重写查询，将where子句中的用户名替换为ipa_id值 . 对我来说，这相当于原始查询 . MySQL看待它的方式不同 . 如果我这样做，优化器会使用大表上的索引 .

mysql>说明SELECT master_final.PayorCode，sum（master_final.Mbrct）AS MbrCt FROM master_final INNER JOIN ipa ON ipa.ipa_code = master_final.IPA INNER JOIN assign_ipa ON ipa.ipa_id = assign_ipa.ipa_id * WHERE assign_ipa.ipa_id in（'688 '，''689'）* GROUP BY master_final.PayorCode，master_final.ipa \ G; ************* 1.行************* id：1 select_type：SIMPLE表：ipa类型：范围possible_keys：PRIMARY，ipa_code键：PRIMARY key_len ：4 ref：NULL行：2额外：使用where;使用临时;使用filesort ************* 2.行************* id：1 select_type：SIMPLE表：assign_ipa类型：ref possible_keys：ipa_id key：ipa_id key_len ：4 ref：wc_test.ipa.ipa_id rows：37 Extra：使用************* 3.行************* id：1 select_type： SIMPLE表：master_final类型：ref possible_keys：idx_IPA键：idx_IPA key_len：5 ref：wc_test.ipa.ipa_code行：34953额外：使用设置中的3行（0.00秒）

我唯一改变的是where子句甚至没有直接击中大表 . 然而，优化器使用大表上的索引'idx_IPA'，不再使用全表扫描 . 像这样重写时的查询非常快 .

好的，那是很多背景知识 . 现在我的问题 . 为什么where子句对优化器很重要？ where子句将从较小的表返回相同的结果集，但我得到了取决于我使用哪一个，结果大不相同 . 显然，我想使用包含用户名的where子句，而不是尝试将所有关联的ID传递给查询 . 如上所述，这是不可能的？

  • 有人可以解释为什么会这样吗？

  • 如何重写查询以避免全表扫描？

谢谢你坚持我 . 我知道这是一个非常长的问题 .

mysql

2 回答

  • 4

    不太确定我是否正确，但我认为以下情况正在发生 . 这个：

    WHERE assign_ipa.username = 'SJones'

    可能会创建一个临时表，因为它需要全表扫描 . 临时表没有索引，它们往往会减慢很多东西 .

    第二种情况

    INNER JOIN ipa ON ipa.ipa_code = master_final.IPA
INNER JOIN assign_ipa ON ipa.ipa_id = assign_ipa.ipa_id
WHERE assign_ipa.ipa_id in ('688','689')

    另一方面，允许加入索引，这很快 . 此外，它可以转换为

    SELECT .... FROM master_final WHERE IDA IN (688, 689) ...

    而且我认为MySQL也是这样做的 .

    在assign_ipa.username上创建索引可能会有所帮助 .

    Edit

    我重新考虑了这个问题，现在有了不同的解释 .

    当然，原因是缺少指数 . 这意味着MySQL不知道查询assign_ipa的结果有多大（MySQL不存储计数），所以它首先从连接开始，它可以在密钥上进行中继 .

    这就是解释日志的第2行和第3行告诉我们的 .

    之后，它尝试通过assign_ipa.username过滤结果，该行没有键，如第1行所述 .

    只要有索引，它就会先使用相应的索引过滤assign_ipa，然后再加入 .

    回复于
  • 2

    这可能不是您问题的直接答案，但您可以做的事情很少：

    • 运行ANALYZE_TABLE ...它将更新表统计信息，这对优化程序将决定执行的操作有很大影响 .

    • 如果您仍然认为联接不符合您希望它们（在您的情况下会发生，因此优化器没有像您期望的那样使用索引），您可以使用STRAIGHT_JOIN ...来自here："STRAIGHT_JOIN forces the optimizer to join the tables in the order in which they are listed in the FROM clause. You can use this to speed up a query if the optimizer joins the tables in nonoptimal order"

    • 对我来说，将“where part”加入连接有时会产生影响并加快速度 . 例如，你可以写：

    ...t1 INNER JOIN t2 ON t1.k1 = t2.k2 AND t2.k2=something...

    代替

    ...t1 INNER JOIN t2 ON t1.k1 = t2.k2 .... WHERE t2.k2=something...

    所以这绝对不是解释为什么你有这种行为而只是一些提示 . 查询优化器是一个奇怪的野兽，但幸运的是有一个EXPLAIN命令可以帮助你欺骗它以你想要的方式行事 .

    回复于

相关问题