mysql - 如何避免MySQL中的 "Using index; Using temporary; Using filesort "，21表JOIN

Question

mysql Ver 14.14 Distrib 5.1.58，用于使用 readline 5.1 的 redhat-linux-gnu (x86_64)

我正在接手一个旧项目。我被要求加快速度。我通过启用慢查询日志。我正在检查出现在慢查询日志中的查询。此查询需要 10 秒才能运行，并返回 11,000 行。

  select 
    substring_index(ob1.literal,'/',1) AS sku4,
    substring_index(
    substring_index(ob1.literal,'/',2),'/',-(1)) AS sku5,
    substring_index(
    substring_index(ob1.literal,'/',3),'/',-(1)) AS sku6,
    substring_index(ob2.literal,'/',1) AS sku7,
    substring_index(
    substring_index(ob2.literal,'/',2),'/',-(1)) AS sku8,
    substring_index(
    substring_index(ob2.literal,'/',3),'/',-(1)) AS sku9,concat_ws(',',ob.level_one,
    substring_index(ob1.literal,'/',1),
    substring_index(ob2.literal,'/',1)) AS sku1_filter,concat_ws(',',ob.level_two,
    substring_index(
    substring_index(ob1.literal,'/',2),'/',-(1)),
    substring_index(
    substring_index(ob2.literal,'/',2),'/',-(1))) AS sku2_filter,concat_ws(',',ob.level_three,
    substring_index(
    substring_index(ob1.literal,'/',3),'/',-(1)),
    substring_index(
    substring_index(ob2.literal,'/',3),'/',-(1))) AS sku3_filter,
    t.title_id AS title_id,
    t.us_list_price AS us_list_price,
    t.uk_list_price AS uk_list_price,
    t.can_list_price AS can_list_price,
    t.aus_list_price AS aus_list_price,
    t.min_age AS min_age,
    t.max_age AS max_age,
    t.book_club AS book_club,
    t.best_seller AS best_seller,
    t.award_winner AS award_winner,
    t.asin AS asin,
    t.format AS format,
    ob.literal AS literal_1,
    ob1.literal AS literal_2,
    ob2.literal AS literal_3,
    t.series AS series,
    t.volume AS volume,
    group_concat(distinct concat(u.first_name,  ' ',  u.last_name) separator ',   ') AS marketer,
    a.group_letter AS group_letter,
    group_concat(distinct concat(u2.first_name,  ' ',  u2.last_name) separator ',   ') AS editor,
    oi.imprint_name AS imprint_name,
    ob.level_one AS level_one,
    ob.level_two AS level_two,
    ob.level_three AS level_three,
    ot1.territory_name AS rights,  
  (case when (isnull(t.active) or (t.active = '')) 
          then '' when (t.active = 'Y') 
          then 'Active' when (t.active = 'N') 
          then 'In Development' when (t.active = 'X') 
          then 'Terminated' when (t.active = 'T') 
          then 'Transmittal' end) AS status,
  (case when (isnull(t.format) or (t.format = '')) 
          then '' when (t.format = 4) 
          then 'Ebook' when (t.format = 8) 
          then 'Print Book' when (t.format = 9) 
          then 'Audio Book' end) AS format_name,
  t.title AS title,t.primary_isbn13 AS primary_isbn13,
  group_concat(distinct a.display_name order by a.display_name ASC separator ', ') AS contributors,
  group_concat(distinct c1.display_name order by c1.display_name ASC separator ', ') AS publishers,
  t.publish_date AS pub_date,
  group_concat(distinct g.name order by g.name ASC separator ', ') AS category,
  group_concat(distinct g0.id order by g0.id ASC separator ', ') AS secondary_category_ids,
  group_concat(distinct g0.name order by g0.name ASC separator ', ') AS secondary_categories
  from ((((((((((((((((((((wawa_title t 
        left join wawa_title_to_imprint tti on((t.title_id = tti.title_id))) 
        left join wawa_imprint oi on((tti.imprint_id = oi.imprint_id))) 
        left join wawa_title_to_supplier t2a on((t2a.title_id = t.title_id))) 
        left join wawa_territories ot1 on((t.territory_id = ot1.territory_id))) 
        left join wawa_supplier a on((a.supplier_id = t2a.supplier_id))) 
        left join wawa_sku ob on((ob.code = t.sku1))) 
        left join wawa_sku ob1 on((ob1.code = t.sku2))) 
        left join wawa_sku ob2 on((ob2.code = t.sku3))) 
        left join bll_formats tf on((t.format = tf.id))) 
        left join bll_suppliers_to_wawa_editors aoe on((a.supplier_id = aoe.supplier_id))) 
        left join bll_suppliers_to_wawa_marketing_contacts amc on((a.supplier_id = amc.supplier_id))) 
        left join bll_contacts c0 on((c0.id = amc.id))) 
        left join users u on((amc.user_id = u.id))) 
        left join users u2 on((aoe.user_id = u2.id)))
        join wawa_title_to_publisher t2p on((t2p.title_id = t.title_id)))
        join wawa_publisher p on((p.publisher_id = t2p.publisher_id)))
        join bll_contacts c1 on((c1.id = p.org_contact_id))) 
        left join wawa_title_to_genre t2g on((t2g.title_id = t.title_id))) 
        left join wawa_genres g on((g.id = t2g.genre_id_2))) 
        left join wawa_genres g0 on((g0.id = t2g.genre_id_3))) where ((t.title_id = t.title_id) and (t.active <> 'X'))
  group by t.title_id

EXPLAIN 的输出是:

+----+-------------+-------+--------+-----------------------------------------------------------------------------------------------+-------------------------------------+---------+------------------------------------------+------+----------------------------------------------+
  | id | select_type | table | type   | possible_keys                                                                                 | key                                 | key_len | ref                                      | rows | Extra                                        |
  +----+-------------+-------+--------+-----------------------------------------------------------------------------------------------+-------------------------------------+---------+------------------------------------------+------+----------------------------------------------+
  |  1 | SIMPLE      | p     | index  | PRIMARY,org_contact_fk                                                                        | org_contact_fk                      | 5       | NULL                                     |   66 | Using index; Using temporary; Using filesort |
  |  1 | SIMPLE      | c1    | eq_ref | PRIMARY                                                                                       | PRIMARY                             | 4       | wawa_ripple_development.p.org_contact_id |    1 |                                              |
  |  1 | SIMPLE      | t2p   | ref    | idx_title_id,idx_publisher_id                                                                 | idx_publisher_id                    | 4       | wawa_ripple_development.p.publisher_id   |   66 | Using where                                  |
  |  1 | SIMPLE      | t     | eq_ref | PRIMARY,active_index,idx_title_active_isprimary                                               | PRIMARY                             | 4       | wawa_ripple_development.t2p.title_id     |    1 | Using where                                  |
  |  1 | SIMPLE      | t2g   | ref    | idx_title_id                                                                                  | idx_title_id                        | 4       | wawa_ripple_development.t2p.title_id     |    1 |                                              |
  |  1 | SIMPLE      | g     | eq_ref | PRIMARY                                                                                       | PRIMARY                             | 4       | wawa_ripple_development.t2g.genre_id_2   |    1 |                                              |
  |  1 | SIMPLE      | g0    | eq_ref | PRIMARY                                                                                       | PRIMARY                             | 4       | wawa_ripple_development.t2g.genre_id_3   |    1 |                                              |
  |  1 | SIMPLE      | tti   | ref    | PRIMARY                                                                                       | PRIMARY                             | 4       | wawa_ripple_development.t.title_id       |    1 | Using index                                  |
  |  1 | SIMPLE      | oi    | eq_ref | PRIMARY                                                                                       | PRIMARY                             | 4       | wawa_ripple_development.tti.imprint_id   |    1 |                                              |
  |  1 | SIMPLE      | t2a   | ref    | title_id                                                                                      | title_id                            | 4       | wawa_ripple_development.t.title_id       |    1 | Using index                                  |
  |  1 | SIMPLE      | ot1   | eq_ref | PRIMARY                                                                                       | PRIMARY                             | 4       | wawa_ripple_development.t.territory_id   |    1 |                                              |
  |  1 | SIMPLE      | a     | eq_ref | PRIMARY,wawa_supplier_venue_id_supplier_id                                                        | PRIMARY                             | 4       | wawa_ripple_development.t2a.supplier_id    |    1 |                                              |
  |  1 | SIMPLE      | ob    | eq_ref | PRIMARY                                                                                       | PRIMARY                             | 29      | wawa_ripple_development.t.sku1         |    1 |                                              |
  |  1 | SIMPLE      | ob1   | eq_ref | PRIMARY                                                                                       | PRIMARY                             | 29      | wawa_ripple_development.t.sku2         |    1 |                                              |
  |  1 | SIMPLE      | ob2   | eq_ref | PRIMARY                                                                                       | PRIMARY                             | 29      | wawa_ripple_development.t.sku3         |    1 |                                              |
  |  1 | SIMPLE      | tf    | eq_ref | PRIMARY                                                                                       | PRIMARY                             | 4       | wawa_ripple_development.t.format         |    1 | Using index                                  |
  |  1 | SIMPLE      | aoe   | ref    | idx_by_supplier_and_editor,index_bll_suppliers_to_wawa_editors_on_supplier_id                       | idx_by_supplier_and_editor            | 4       | wawa_ripple_development.a.supplier_id      |    1 | Using index                                  |
  |  1 | SIMPLE      | amc   | ref    | idx_by_supplier_and_marketing_contact,index_bll_suppliers_to_wawa_marketing_contacts_on_supplier_id | idx_by_supplier_and_marketing_contact | 4       | wawa_ripple_development.a.supplier_id      |    1 | Using index                                  |
  |  1 | SIMPLE      | c0    | eq_ref | PRIMARY                                                                                       | PRIMARY                             | 4       | wawa_ripple_development.amc.id           |    1 | Using index                                  |
  |  1 | SIMPLE      | u     | eq_ref | PRIMARY                                                                                       | PRIMARY                             | 4       | wawa_ripple_development.amc.user_id      |    1 |                                              |
  |  1 | SIMPLE      | u2    | eq_ref | PRIMARY                                                                                       | PRIMARY                             | 4       | wawa_ripple_development.aoe.user_id      |    1 |                                              |
  +----+-------------+-------+--------+-----------------------------------------------------------------------------------------------+-------------------------------------+---------+------------------------------------------+------+----------------------------------------------+

我担心两件事:

1.) 使用索引；使用临时的；使用文件排序

2.) 使用哪里

我担心这些是否正确？

有什么建议可以加快速度吗？

一些变量:

  mysql> show variables like '%buffer%';
  +-------------------------+---------+
  | Variable_name           | Value   |
  +-------------------------+---------+
  | bulk_insert_buffer_size | 8388608 |
  | innodb_buffer_pool_size | 8388608 |
  | innodb_log_buffer_size  | 1048576 |
  | join_buffer_size        | 131072  |
  | key_buffer_size         | 8384512 |
  | myisam_sort_buffer_size | 8388608 |
  | net_buffer_length       | 16384   |
  | preload_buffer_size     | 32768   |
  | read_buffer_size        | 131072  |
  | read_rnd_buffer_size    | 262144  |
  | sort_buffer_size        | 2097144 |
  | sql_buffer_result       | OFF     |
  +-------------------------+---------+

Answer 1

• 使用索引(也称为 type=index)——这是一件“好事”。它说该表的工作可以完全在索引的 BTree 中完成，而无需触及数据。
• Using temporary -- 这表示执行查询需要至少一个临时表。有些查询绝对必须有一个 tmp 表。所以忍受它。注意:此短语在 EXPLAIN 中的位置并不表示哪个表真正需要 tmp 或 filesort。有关此类详细信息，请参阅 EXPLAIN FORMAT=JSON SELECT ...。
• 使用文件排序——这表示至少有一个步骤需要排序。它没有说明那种类型是否必须写入磁盘。同样，这可能是绝对必要的。
• 使用 where -- 没有什么意义
• 使用索引条件 -- 这与“使用索引”不同。 ICP 意味着对一个复杂的WHERE 正在使用一定的效率；这仅适用于较新的版本。
• eq_ref - 只需下表中的一行即可。好。
• ref - 不是 1:1，而是 1:many。从EXPLAIN来看，似乎往往更接近1:1。

至于加速...

• 你需要 LEFT 吗？如果不摆脱它；优化器可能更喜欢其他一些表顺序。
• EXPLAIN 表示需要获取的行不多。 (注意:Explain 的“行”数字是近似值。)因此，我在这里看不到太多帮助。
• 看看您是否可以为某些 JOIN 创建“覆盖索引”——但仅针对“ref”情况执行此操作，而不是“eq_ref + PRIMARY”情况。看来您已经彻底完成了这项工作。
• 您将如何处理 11000 行？这对于 UI 来说似乎“不合理”？如果它注定要进行一些进一步的处理，那么它多久完成一次？ (也就是说，10 秒真的很重要吗？)
• 您使用的是什么引擎？你有多少内存？ SHOW VARIABLES LIKE '%buffer%'; -- 我正在钓鱼 memory utilization .
• JOIN + GROUP BY -- 这通常意味着行爆炸，然后是内爆。将JOIN 的一些 更改为SELECT 中的子查询可能 改进:( SELECT ... FROM ... WHERE ... LIMIT 1 ) AS whatever.
• 是否有任何字段TEXT？这迫使“文件排序”超出内存(因此速度较慢)。给我更多详细信息，也许我们可以解决这个问题。

总而言之，EXPLAIN 看起来很干净。大量的 JOIN 是另一回事。

两个可能糟糕的模式设计:

• 跨表数组 (*_price)
• 跨列数组(level*、sku*、user*、genre*)

附录

对于 16GB RAM 和 InnoDB 和 MyISAM 的混合，建议 key_buffer_size = 1500M 和 innodb_buffer_pool_size = 5G。在 my.cnf(或 my.ini)中设置它们并重新启动 mysqld。