提高涉及联合的 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 上部署在低等级系统上时非常慢我为解决此问题所采取的步骤：查看了所有表的索引。尝试删除索引并运行该实用程序。删除索引使实用程序加快了 5 秒，但这也不算多。分阶段执行实用程序：如果总记录超过 100 万条，则一次运行 100 万条实用程序但毕竟这些努力看起来主要问题在于查询本身。只是速度不够快。我只是需要一种方法来提高查询效率任何帮助表示赞赏，谢谢大家！！

Answer 1

为什么我们先添加timekey然后删除它？将其添加到空表中速度很快，但是在填充后将其从表中删除，这就像表的额外副本。如果我们不需要的话，那是不必要的工作。

我们可以GROUP BY对表达式执行 a；该表达式不必出现在 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 输出，我们只是猜测。

但一些建议：

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

我会避开工会。鉴于其中一个 SELECT 语句有一个谓词，column_name而另一个 SELECT 语句有一个完全不同的列上的谓词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)

  , ...

;