mysql - cmd 和 workbench mysql 之间查询的性能差异

Question

我有两个问题:

1. 如我的标题，对于对大量数据运行大型查询，哪个更有效？

我看过 MySQL 文档，它在 https://www.mysql.com/products/workbench/performance/ 中解释了工作台的性能。但是我似乎找不到任何资源专门讨论在 cmd 上运行查询和在工作台上运行查询之间的效率差异。

1. 如何优化这个查询

   select
     r.user_id,
     k.id as kickscooter_id,
     st_astext(k.location) as location,
     k.created_at,
     k.serial_number,
     k_st.serial_number as states_serial_number,
     st_astext(k_st.gps) as gps_location,
     k_st.gps_updated_at,
     r.start_time,
     r.end_time
   from kickscooters k
   join rents r
     on k.id= r.kickscooter_id
   join kickscooter_states_190614 k_st
     on k.serial_number = k_st.serial_number
   order by r.rent_date
   limit 999;
   

我了解到创建索引可以让 mysql 快速排序，因此我添加了索引

ALTER TABLE `tablename` ADD INDEX `indexname` (`columnname`);

以下 SO 帖子之一的回答 "order by" taking too much time in mysql

按照我执行的评论中的建议

analyze <my query> 

因为我的服务器是 MariaDB。

这给了我 ERROR CODE 2013: LOST connection to server during query.

当我运行时

explain <my query>

它工作并输出:

id    select_type   table   type          possible_keys                                                  
1     SIMPLE        k_st    ALL  kickscooter_states_190614_serial_number_date_index             
1     SIMPLE        k       ref  PRIMARY,kickscooters_serial_number_unique,kickscooters_serial_number_index     
1     SIMPLE        r        ref    rents_kickscooter_id_foreign    

-table continued
/       key                       key_len      ref                        rows         extra

        null                        null       null                      192818947      Using temporary; Using filesort
   kickscooters_serial_number_unique 27     kickgoing_db.k_st.serial_number 1   
    rents_kickscooter_id_foreign     4      kickgoing_db.k.id              143  

Answer 1

根据 Explain 计划，优化器无法为 ORDER BY rent 使用任何索引。所以请尝试以下操作:

1. 确保索引存在于 rents 表的 rent_date 列中。该索引将用于优化 ORDER BY 子句。可以是单列索引，也可以是多列索引(用于其他场景)。但是，在多列的情况下，您需要确保 rent 列是索引顺序中的第一列。
2. 确保索引存在于 kickscooters 表的 id 列中。有关单列/多列索引的详细信息与第 1 点相同。
3. 确保索引存在于 kickscooter_states_190614 表的 serial_number 列中。有关单列/多列索引的详细信息与第 1 点相同。

现在，确保这些索引后，尝试您的原始查询。最有可能的是，优化器应该能够优化连接顺序。此外，上述查询，您可以使用 STRAIGHT_JOIN 强制执行连接顺序优化器提示。因此，也请尝试以下查询，并在两者之间进行基准测试:

select
  r.user_id,
  k.id as kickscooter_id,
  st_astext(k.location) as location,
  k.created_at,
  k.serial_number,
  k_st.serial_number as states_serial_number,
  st_astext(k_st.gps) as gps_location,
  k_st.gps_updated_at,
  r.start_time,
  r.end_time
from kickscooters k
straight_join rents r
  on k.id= r.kickscooter_id
straight_join kickscooter_states_190614 k_st
  on k.serial_number = k_st.serial_number
order by r.rent_date
limit 999;