提高涉及联合的 MYSQL QUERY 的性能

Question

拥有一个 Golang 实用程序，能够减少包含历史数据的表中每天的数据点。

记录范围为每天 20 到 400 条记录。总共至少有1亿条记录。

该实用程序能够在给定日期之前将其减少到每天 n 条记录。（n 的范围可以是每天 1 到 300 条记录）

我使用的方法如下：

步骤1：

创建表 main_table_tmp 像 main_table;

第2步：

更改表 main_table_tmp 添加列 timekey INT；

步骤 3：

INSERT INTO main_table_tmp 

SELECT * FROM (

  SELECT *,FLOOR(UNIX_TIMESTAMP(column_name)/((1440/2)*60)) AS timekey 

  FROM main_table

  WHERE column_name <= '2018-01-01' 

  GROUP BY timekey

) m 

UNION ALL 

(SELECT * ,0 As timekey FROM main_table where column_name > 'date') ;

步骤4：

更改表 main_table_tmp 删除列时间键；

DROP TABLE 维护表；

将表 maintable_tmp 重命名为 maintable；

我正在使用 golang 实现上述目标。

func somefuncname(){

  ---- 

  ----

  ----

  q := "CREATE TABLE " + *tablename + "_tmp LIKE " + *tablename + ";"

  rows, err := db.Query(q)

  if err != nil {

  fmt.Println(err)

  }

//--ALTER ADD timekey

//--INSERT INTO SELECT *....

//--ALTER DROP timekey ,DROP table and rename

}

当前该查询的响应时间非常慢

部分结果： 总记录： 200 万条
执行时间： 180 秒

这是在 16Gb RAM CPU 上部署在低等级系统上时非常慢

我为解决此问题所采取的步骤：

1. 查看了所有表的索引。尝试删除索引并运行该实用程序。删除索引使实用程序加快了 5 秒，但这也不算多。

2. 分阶段执行实用程序：如果总记录超过 100 万条，则一次运行 100 万条实用程序

但毕竟这些努力看起来主要问题在于查询本身。

只是速度不够快。我只是需要一种方法来提高查询效率

任何帮助表示赞赏，谢谢大家！！

Answer 1

为什么我们先添加timekey然后删除它？将其添加到空表中速度很快，但是在填充后将其从表中删除，这就像表的额外副本。如果我们不需要的话，那是不必要的工作。我们可以GROUP BY对表达式执行 a；该表达式不必出现在 SELECT 列表中，例如：SELECT t.*&nbsp; FROM main_table t&nbsp;WHERE t.column_name <= '2018-01-01'&nbsp;GROUP&nbsp;&nbsp; &nbsp; BY FLOOR(UNIX_TIMESTAMP(t.column_name)/((1440/2)*60))ONLY_FULL_GROUP_BY（请注意，如果包含在 sql_mode 中，此查询将导致错误；这将禁用允许查询运行的 MySQL 特定扩展。）如果没有一些表定义（包括存储引擎、列数据类型、索引）并且没有 EXPLAIN 输出，我们只是猜测。但一些建议：删除正在填充的空表上的二级索引，并在表加载后添加它们。我会避开工会。鉴于其中一个 SELECT 语句有一个谓词，column_name而另一个 SELECT 语句有一个完全不同的列上的谓词date，我们确实希望分隔 SELECT 语句。CREATE TABLE main_table_tmp LIKE main_table;-- for performance, remove secondary indexes, leave just the cluster indexALTER TABLE main_table_tmp&nbsp;&nbsp; &nbsp; DROP INDEX noncluster_index_1&nbsp; , DROP INDEX noncluster_index_2&nbsp; , ...;-- for performance, have a suitable index available on main_table&nbsp;-- with `column_name` as the leading columnINSERT INTO main_table_tmp&nbsp;SELECT h.*&nbsp; FROM main_table h&nbsp;WHERE h.column_name <= '2018-01-01'&nbsp;GROUP&nbsp;&nbsp; &nbsp; BY FLOOR(UNIX_TIMESTAMP(h.column_name)/((1440/2)*60));-- for performance, have a suitable index available on main_table-- with `date` as the leading columnINSERT INTO main_table_tmpSELECT c.*&nbsp; FROM main_table&nbsp;WHERE c.date > '????-??-??';-- add secondary indexes&nbsp;ALTER TABLE maint_table_tmp&nbsp; &nbsp; ADD UNIQUE INDEX noncluster_index_1 (fee,fi,fo)&nbsp; , ADD INDEX noncluster_index_2 (fum)&nbsp; , ...;