我有一个SQL查询，执行大约需要1.81s秒。我在phpMyAdmin中对其进行了分析，发现99%的时间都花在了“统计”上。
有趣的是，如果我再添加一个join，时间将增加到大约23秒，如果删除1个join，时间将减少到大约0.26秒。
我试图将product_id = 放在联接中的attribute_id = 位置。它并没有提高性能，而且“统计”仍然使用99%的时间。
我已经尝试过group by p.id这确实能稍微提高性能，但“统计”仍然使用98%的时间。
有没有办法提高“统计”性能，或者只是禁用它？
注意：我对所有PK/FK都有索引。
有用的信息
sqlfiddlehere
查询返回大约150行。
ss_product table has 1k rows
ss_product_attributes has 30 rows
ss_product_attribute_varchars has 5000 rows
ss_product_attribute_decimals has 10000 rows
架构
ss_products has many ss_product_attribute_varchars
ss_products has many ss_product_attribute_decimals
ss_product_attribute_varchars belongs to ss_product_attributes
ss_product_attribute_decimals belongs to ss_product_attributes
-------------
-ss_products-
-------------
- PK - id -
-------------
--------------------------------
-ss_product_attribute_varchars-
--------------------------------
- PK - id -
- FK - product_id -
- FK - attribute_id -
--------------------------------
--------------------------------
-ss_products_attribute_decimals-
--------------------------------
- PK - id -
- FK - product_id -
- FK - attribute_id -
--------------------------------
------------------------
-ss_products_attributes-
------------------------
- PK - id -
------------------------
剖析
Starting 101 µs
Checking Permissions 6 µs
Checking Permissions 2 µs
Checking Permissions 2 µs
Checking Permissions 2 µs
Checking Permissions 1 µs
Checking Permissions 2 µs
Checking Permissions 2 µs
Checking Permissions 2 µs
Checking Permissions 2 µs
Checking Permissions 3 µs
Opening Tables 47 µs
System Lock 10 µs
Init 35 µs
Optimizing 21 µs
Statistics 1.8 s //1.8seconds!!!!
Preparing 46 µs
Executing 3 µs
Sending Data 41 ms
End 7 µs
Query End 4 µs
Closing Tables 12 µs
Freeing Items 463 µs
Logging Slow Query 3 µs
Cleaning Up 9 µs
询问
select
aa.value as attribute_a,
bb.value as attribute_b,
cc.value as attribute_c,
dd.value as attribute_d,
ee.value as attribute_e,
ff.value as attribute_f,
gg.value as attribute_g,
hh.value as attribute_h,
ii.value as attribute_i
from ss_products as p
inner join ss_product_attribute_varchars as aa
on p.id = aa.product_id
inner join ss_product_attribute_varchars as bb
on p.id = bb.product_id
inner join ss_product_attribute_varchars as cc
on p.id = cc.product_id
inner join ss_product_attribute_decimals as dd
on p.id = dd.product_id
inner join ss_product_attribute_decimals as ee
on p.id = ee.product_id
inner join ss_product_attribute_decimals as ff
on p.id = ff.product_id
inner join ss_product_attribute_varchars as gg
on p.id = gg.product_id
INNER JOIN ss_product_attribute_varchars AS hh
ON p.id = hh.product_id
INNER JOIN ss_product_attribute_varchars AS ii
ON p.id = ii.product_id
where
aa.attribute_id = 8 AND
bb.attribute_id = 6 AND
cc.attribute_id = 7 AND
dd.attribute_id = 9 and
ee.attribute_id = 10 and
ff.attribute_id = 11 AND
gg.attribute_id = 20 AND
hh.attribute_id = 2 AND
ii.attribute_id = 3
解释
id| select_type| table | type | possible_keys |key |key_len| ref | rows| Extra
1 | SIMPLE | gg | ref | fk_product_attribute_varchars_products1_idx,fk_pro... | attribute_id | 4 | const | 143 |
1 | SIMPLE | aa | ref | fk_product_attribute_varchars_products1_idx,fk_pro... | fk_product_attribute_varchars_products1_idx | 4 | my_id.gg.product_id | 2 | Using where
1 | SIMPLE | cc | ref | fk_product_attribute_varchars_products1_idx,fk_pro... | fk_product_attribute_varchars_products1_idx | 4 | my_id.gg.product_id | 2 | Using where
1 | SIMPLE | bb | ref | fk_product_attribute_varchars_products1_idx,fk_pro... | fk_product_attribute_varchars_products1_idx | 4 | my_id.gg.product_id | 2 | Using where
1 | SIMPLE | ii | ref | fk_product_attribute_varchars_products1_idx,fk_pro... | fk_product_attribute_varchars_products1_idx | 4 | my_id.aa.product_id | 2 | Using where
1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | my_id.aa.product_id | 1 | Using where; Using index
1 | SIMPLE | dd | ref | fk_product_attribute_decimals_products1_idx,fk_pro... | fk_product_attribute_decimals_products1_idx | 4 | my_id.cc.product_id | 2 | Using where
1 | SIMPLE | ee | ref | fk_product_attribute_decimals_products1_idx,fk_pro... | fk_product_attribute_decimals_products1_idx | 4 | my_id.ii.product_id | 2 | Using where
1 | SIMPLE | ff | ref | fk_product_attribute_decimals_products1_idx,fk_pro... | fk_product_attribute_decimals_products1_idx | 4 | my_id.p.id | 2 | Using where
1 | SIMPLE | hh | ref | fk_product_attribute_varchars_products1_idx,fk_pro... | fk_product_attribute_varchars_products1_idx | 4 | my_id.cc.product_id | 2 | Using where
完整的个人资料（如雷蒙德·尼兰德所要求）
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------+---------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------+---------------+-------------+
| starting | 0.000151 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| checking permissions | 0.000005 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_parse.cc | 4751 |
| checking permissions | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_parse.cc | 4751 |
| checking permissions | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_parse.cc | 4751 |
| checking permissions | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_parse.cc | 4751 |
| checking permissions | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_parse.cc | 4751 |
| checking permissions | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_parse.cc | 4751 |
| checking permissions | 0.000005 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_parse.cc | 4751 |
| checking permissions | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_parse.cc | 4751 |
| checking permissions | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_parse.cc | 4751 |
| checking permissions | 0.000005 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_parse.cc | 4751 |
| Opening tables | 0.000051 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_base.cc | 4838 |
| System lock | 0.000016 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | lock.cc | 299 |
| init | 0.000046 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_select.cc | 2560 |
| optimizing | 0.000031 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_select.cc | 869 |
| statistics | 1.419911 | 1.404009 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_select.cc | 1060 |
| preparing | 0.000050 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_select.cc | 1082 |
| executing | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_select.cc | 1829 |
| Sending data | 0.018508 | 0.015600 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_select.cc | 2371 |
| end | 0.000007 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_select.cc | 2596 |
| query end | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_parse.cc | 4440 |
| closing tables | 0.000018 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_parse.cc | 4492 |
| freeing items | 0.000110 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_parse.cc | 5640 |
| logging slow query | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_parse.cc | 1461 |
| cleaning up | 0.000008 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | <unknown> | sql_parse.cc | 1417 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+------------------+-------------------+-------------------+-------+-----------------+---------------+-------------+
最佳答案
因为一个注释不支持代码格式化，所以给出了一个答案。
配置文件输出中的统计信息在MySQL中的C++源代码的这一部分中定义。
/* Calculate how to do the join */
thd_proc_info(thd, "statistics");
if (make_join_statistics(this, select_lex->leaf_tables, conds, &keyuse) ||
thd->is_fatal_error)
{
DBUG_PRINT("error",("Error: make_join_statistics() failed"));
DBUG_RETURN(1);
}
但你还是得跑
SHOW PROFILE ALL FOR QUERY n
所以我们可以看到查询是CPU还是磁盘I/O有界。
我建议使用UNION ALL或使用分而治之策略来分离查询