mysql - 提高涉及联合的 MYSQL 查询的性能

Question

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

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

该实用程序能够在给定日期之前将其减少到每天 n 条记录。 (n 的范围为每天 1 到 300 条记录)

我使用的方法如下:

第 1 步:

CREATE TABLE main_table_tmp LIKE main_table;

第 2 步:

ALTER TABLE main_table_tmp ADD COLUMN 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 步:

ALTER TABLE main_table_tmp DROP COLUMN timekey;

DROP TABLE maintable;

RENAME TABLE maintable_tmp TO 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；该表达式不必出现在 SELECT 列表中。例如:

SELECT t.*
  FROM main_table t
 WHERE t.column_name <= '2018-01-01'
 GROUP 
    BY FLOOR(UNIX_TIMESTAMP(t.column_name)/((1440/2)*60))

(请注意，如果 ONLY_FULL_GROUP_BY 包含在 sql_mode 中，此查询将导致错误；这会禁用允许查询运行的 MySQL 特定扩展。)

没有一些表定义(包括存储引擎、列数据类型、索引)并且没有 EXPLAIN 输出，我们只是猜测。

但是一些建议:

在正在填充的空表上删除二级索引，并在加载表后添加它们。

我会避免使用 UNION。鉴于其中一个 SELECT 语句在 column_name 上有谓词，而另一个在完全不同的列 date 上有谓词，我们确实希望将 SELECT 语句分开。

CREATE TABLE main_table_tmp LIKE main_table
;

-- for performance, remove secondary indexes, leave just the cluster index
ALTER TABLE main_table_tmp 
    DROP INDEX noncluster_index_1
  , DROP INDEX noncluster_index_2
  , ...
;

-- for performance, have a suitable index available on main_table 
-- with `column_name` as the leading column
INSERT INTO main_table_tmp 
SELECT h.*
  FROM main_table h
 WHERE h.column_name <= '2018-01-01'
 GROUP 
    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 column
INSERT INTO main_table_tmp
SELECT c.*
  FROM main_table
 WHERE c.date > '????-??-??'
;

-- add secondary indexes 
ALTER TABLE maint_table_tmp
    ADD UNIQUE INDEX noncluster_index_1 (fee,fi,fo)
  , ADD INDEX noncluster_index_2 (fum)
  , ...
;