mysql - 加快创建mysql表(from select)

Question

我有一个大约运行 1.6 秒并返回 150Kish 行的查询。我一直在从这个查询构建一个基于内存引擎的临时表，这个操作大约需要 6.5 秒。我筛选了 mysql 站点上的大量文档以及临时表上的索引(当我有索引和没有索引时几乎没有任何区别)试图找到一些创建表的方法表现更好。关于可以加速创建内存临时表(或非临时表)的配置选项，是否有人有任何建议？要弄清楚我到底在寻找什么(我认为)是某种可能会提高创建速度的表配置选项。我已经浏览了 mysql 5.7 站点上列出的大部分内容，但似乎没有什么可以改变在内存中创建临时/标准表所需的时间。

CREATE TEMPORARY TABLE 
IF NOT EXISTS myTempTable 
(ugID INT,stID INT, INDEX st (`st`) ) 
ENGINE=MEMORY AS 
select ugID, stID 
from a_complex_query_that_runs_in_under_2_seconds_but_takes_6_to_write_to_a_temptable_with_150k_rows


id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived15> ALL NULL    NULL    NULL    NULL    559 
1   PRIMARY creatorEntity   eq_ref  PRIMARY PRIMARY 4   creatorAssignment.OwnerObjectID 1   Using index
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    152580  Using where; Using join buffer
15  DERIVED VMAssignment    ref AssociatedObjectID,AssociatedObject AssociatedObject    2       2902    Using where
2   DERIVED test_   range   AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssociatedObjectID  4   NULL    214 Using where; Using temporary; Using filesort
2   DERIVED cfv_    ref CustomFieldID,EntityID,CFEntity,CFValue,CFEntityValue   CustomFieldID   4   testCloud.test_.AssignmentValue 4232    Using where
2   DERIVED ent_    ref VMStoreID   VMStoreID   4   testCloud.cfv_.EntityID 1   Using index
3   UNION   test_   ref AssociatedObjectID,AssociatedObject AssociatedObject    2       2902    Using where; Using temporary; Using filesort
3   UNION   cfv_    ref CustomFieldID,EntityID,CFEntity,CFValue,CFEntityValue   CFValue 772 func,func   4   Using where
3   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.cfv_.EntityID 1   Using index
4   UNION   test_   const   PRIMARY PRIMARY 4       1   Using index
5   UNION   test_   index_merge AssociatedObjectID,AssociatedObject AssociatedObjectID,AssociatedObject 4,6 NULL    1   Using intersect(AssociatedObjectID,AssociatedObject); Using where; Using index; Using temporary; Using filesort
5   UNION   entity_ ref PRIMARY,ClassName   ClassName   2       23326   Using where
5   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.entity_.ID    1   Using index
6   UNION   entity_ ref PRIMARY,ClassName   ClassName   2       23326   Using where; Using temporary; Using filesort
6   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.entity_.ID    1   Using index
6   UNION   test_   ref AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssignmentValue 768 testCloud.entity_.State 1   Using where
7   UNION   entity_ ref PRIMARY,ClassName   ClassName   2       23326   Using where; Using temporary; Using filesort
7   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.entity_.ID    1   Using index
7   UNION   test_   ref AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssignmentValue 768 testCloud.entity_.Zip   1   Using where
8   UNION   test_   range   AssociatedObjectID,AssociatedObject AssociatedObjectID  4   NULL    150 Using where; Using temporary; Using filesort
8   UNION   entity_ ref PRIMARY,ClassName,Address   Address 456 func,func   4   Using where
8   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.entity_.ID    1   Using index
9   UNION   test_   range   AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssociatedObjectID  4   NULL    203 Using where; Using temporary; Using filesort
9   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.test_.AssignmentValue 1   Using where; Using index
10  UNION   test_   const   PRIMARY PRIMARY 4       1   Using index
11  UNION   test_   ref AssociatedObjectID,AssociatedObject AssociatedObjectID  4       1   Using where; Using temporary; Using filesort
11  UNION   entity_ ref PRIMARY,ClassName   ClassName   2       23326   Using where
11  UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.entity_.ID    1   Using index
12  UNION   test_   ref AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssociatedObject    2       2902    Using where; Using temporary; Using filesort
12  UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.test_.AssignmentValue 1   Using where; Using index
13  UNION   test_   range   AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssociatedObjectID  4   NULL    239 Using where; Using temporary; Using filesort
13  UNION   list_stores ref VMListID,VMStoreID,VMStoreID_2,VMStoreID_3,VMStoreID_4,VMStoreID_5,VMStoreID_6,VMStoreID_7,VMStoreID_8,VMStoreID_9,VMStoreID_10,VMStoreID_11,VMStoreID_12,VMStoreID_13,VMStoreID_14,VMStoreID_15,VMStoreID_16   VMListID    4   testCloud.test_.AssignmentValue 318 Using where
13  UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.list_stores.VMStoreID 1   Using index
14  UNION   test_   range   AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssociatedObjectID  4   NULL    70  Using where; Using temporary; Using filesort
14  UNION   sto_    ref PRIMARY,RetailerID  RetailerID  4   testCloud.test_.AssignmentValue 63  Using where; Using index
14  UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.sto_.ID   1   Using index
NULL    UNION RESULT    <union2,3,4,5,6,7,8,9,10,11,12,13,14>   ALL NULL    NULL    NULL    NULL    NULL    

Answer 1

没有太多可以使用的。 (SHOW CREATE TABLE 和 EXPLAIN)但我确实看到了一件事:

Using intersect(AssociatedObjectID,AssociatedObject)

这几乎总是可以通过使用“复合”索引来改进:

INDEX(AssociatedObjectID, AssociatedObject)

复合索引可能还有其他情况，但我们需要查看查询。

“但是查询填充临时表所花费的时间”——创建 152K 行可能比插入那么多行花费的时间更长。

尝试使用 ENGINE=InnoDB 而不是 ENGINE=MEMORY。 InnoDB 取得了性能上的飞跃。

也尝试不在 CREATE 中包含 INDEX st (st)，而是在之后添加它。增量构建索引可能是CREATE 中最慢的部分。

可能没有影响 MEMORY 性能的可调参数。