Блог

Главная - Вопросы по программированию - Оптимизируема ли эта группа транспозиции MySQL по запросу?

Оптимизируема ли эта группа транспозиции MySQL по запросу?

#mysql #query-optimization #pivot-table #transpose #query-performance

#mysql #оптимизация запроса #сводная таблица #транспонировать #запрос-производительность

Вопрос:

my_table содержит 290 миллионов строк, и я хочу оптимизировать следующий запрос

 select
  col1,
  col2,
  group_concat(distinct case when col3=1 then col4 end) c1,
  group_concat(distinct case when col3=2 then col4 end) c2,
  ...
  group_concat(distinct case when col3=70 then col4 end) c70
from my_table
group by col1,col2
order by null

Я уже пробовал запускать запросы меньшего размера, подобные этому, но в целом все еще хуже

 select
  col1,
  col2,
  group_concat(distinct case when col3=1 then col4 end) c1
from my_table
group by col1,col2
order by null

Есть ли способ это сделать?

Ответ №1:

Это сложная задача, потому что вы выполняете запрос только к одной таблице. Я могу предложить следующий индекс:

 CREATE INDEX idx ON my_table (col1, col2, col3, col4);

MySQL может использовать этот индекс на том основании, что для каждой (col1, col2) группы он может выполнить сканирование индекса, чтобы найти каждое значение col3 , а затем объединить вместе различные значения col4 .

Комментарии:

1. Будет ли explain отображаться сканирование индекса? Может ли a сделать это на примере (последний созданный мной индекс занял 5 часов)?

2. Да, вы можете попробовать использовать меньшую выборку ваших данных.

Ответ №2:

(Пожалуйста, используйте реальные имена столбцов; часто там есть полезные подсказки.)

Возможно, так будет быстрее…

Во-первых, давайте посмотрим, насколько быстро можно выполнить все GROUP_CONCATs одновременно:

 SELECT col3,
       GROUP_CONCAT(DISTINCT col4) AS list
    FROM my_table
    GROUP BY col3;

Для этого потребуется полное сканирование таблицы (290 млн строк), но его можно ускорить с помощью

 INDEX(col3, col4)  -- in this order

которая является «покрывающей».

Однако, поскольку вы col1 и col2 запутали работу, давайте изменим на

 SELECT col1, col2, col3,
       GROUP_CONCAT(DISTINCT col4) AS list
    FROM my_table
    GROUP BY col1, col3, col3;

и

 INDEX(col1, col2, col3, col4)   -- in this order

На этом этапе у вас есть все данные, но вам нужно их «развернуть». (Смотрите [pivot] тег.)