Параллельный запрос для MySQL с помощью Shard-Query

Эта статья была первоначально написана Джастином Суонхартом

В то время как Shard-Query может работать с несколькими узлами, этот блог посвящен использованию Shard-Query с одним узлом. Shard-Query может добавить параллелизм к запросам, которые используют многораздельные таблицы. Очень большие таблицы часто могут быть разделены довольно легко. Shard-Query может использовать разбиение для добавления паралеллизма, поскольку каждый раздел может запрашиваться независимо. Поскольку MySQL 5.6 поддерживает подсказку раздела, Shard-Query может добавить параллелизм к любому методу разделения (даже разделению на части) в 5.6, но он ограничен методами разделения RANGE / LIST в ранних версиях.

Вывод Shard-Query производится из клиента командной строки, но вы также можете использовать прокси-сервер MySQL для взаимодействия с Shard-Query.

В примерах я собираюсь использовать схему из теста Star Schema. Я сгенерировал данные для коэффициента масштабирования 10, что означает около 6 ГБ данных в самой большой таблице. Я собираюсь показать несколько разных запросов и объяснить, как Shard-Query выполняет их параллельно.

Вот DDL для таблицы lineorder, которую я буду использовать для демонстрационных запросов:

CREATE TABLE IF NOT EXISTS lineorder
(
 LO_OrderKey bigint not null,
 LO_LineNumber tinyint not null,
 LO_CustKey int not null,
 LO_PartKey int not null,
 LO_SuppKey int not null,
 LO_OrderDateKey int not null,
 LO_OrderPriority varchar(15),
 LO_ShipPriority char(1),
 LO_Quantity tinyint,
 LO_ExtendedPrice decimal,
 LO_OrdTotalPrice decimal,
 LO_Discount decimal,
 LO_Revenue decimal,
 LO_SupplyCost decimal,
 LO_Tax tinyint,
 LO_CommitDateKey int not null,
 LO_ShipMode varchar(10),
 primary key(LO_OrderDateKey,LO_PartKey,LO_SuppKey,LO_Custkey,LO_OrderKey,LO_LineNumber)
) PARTITION BY HASH(LO_OrderDateKey) PARTITIONS 8;

Обратите внимание, что таблица lineorder разделена HASH (LO_OrderDateKey) на 8 разделов. Я использовал 8 разделов, и в моем тестовом боксе 4 ядра. Не больно иметь больше разделов, чем ядер. Обычно лучше всего работает количество разделов, в два-три раза превышающее число ядер, поскольку при этом размер каждого раздела остается небольшим, а меньшие разделы быстрее сканируются. Если у вас очень большая таблица, может быть приемлемо большее количество разделов. Shard-Query отправит запрос в Gearman для каждого раздела, и количество рабочих Gearman контролирует параллелизм.

SQL для первой демонстрации:

SELECT COUNT(DISTINCT LO_OrderDateKey) FROM lineorder;

Вот объяснение от обычного MySQL:

mysql> explain select count(distinct LO_OrderDateKey) from lineorder\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineorder
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 25
          ref: NULL
         rows: 58922188
        Extra: Using index
1 row in set (0.00 sec)

Так что это в основном полное сканирование таблицы. Это займет много времени:

mysql> select count(distinct LO_OrderDateKey) from lineorder;
+---------------------------------+
| count(distinct LO_OrderDateKey) |
+---------------------------------+
|                            2406 |
+---------------------------------+
1 row in set (4 min 48.63 sec)

Shard-Query выполняет этот запрос не так, как MySQL. Он отправляет запрос каждому разделу параллельно, как следующие запросы:

Array
(
    [0] => SELECT LO_OrderDateKey AS expr_2839651562
FROM lineorder  PARTITION(p0)  AS `lineorder`   WHERE 1=1  AND 1=1  GROUP BY LO_OrderDateKey
    [1] => SELECT LO_OrderDateKey AS expr_2839651562
FROM lineorder  PARTITION(p1)  AS `lineorder`   WHERE 1=1  AND 1=1  GROUP BY LO_OrderDateKey
    [2] => SELECT LO_OrderDateKey AS expr_2839651562
FROM lineorder  PARTITION(p2)  AS `lineorder`   WHERE 1=1  AND 1=1  GROUP BY LO_OrderDateKey
    [3] => SELECT LO_OrderDateKey AS expr_2839651562
FROM lineorder  PARTITION(p3)  AS `lineorder`   WHERE 1=1  AND 1=1  GROUP BY LO_OrderDateKey
    [4] => SELECT LO_OrderDateKey AS expr_2839651562
FROM lineorder  PARTITION(p4)  AS `lineorder`   WHERE 1=1  AND 1=1  GROUP BY LO_OrderDateKey
    [5] => SELECT LO_OrderDateKey AS expr_2839651562
FROM lineorder  PARTITION(p5)  AS `lineorder`   WHERE 1=1  AND 1=1  GROUP BY LO_OrderDateKey
    [6] => SELECT LO_OrderDateKey AS expr_2839651562
FROM lineorder  PARTITION(p6)  AS `lineorder`   WHERE 1=1  AND 1=1  GROUP BY LO_OrderDateKey
    [7] => SELECT LO_OrderDateKey AS expr_2839651562
FROM lineorder  PARTITION(p7)  AS `lineorder`   WHERE 1=1  AND 1=1  GROUP BY LO_OrderDateKey
)

Вы заметите, что существует один запрос для каждого раздела. Эти запросы будут отправлены в Gearman и выполнены параллельно как можно большим количеством работников Gearman (в данном случае 4.) Выходные данные запросов поступают в таблицу координаторов, а затем другой запрос выполняет окончательное агрегирование. Этот запрос выглядит так:

SELECT COUNT(distinct expr_2839651562) AS `count`
FROM `aggregation_tmp_73522490`

Время осколка-запроса:

select count(distinct LO_OrderDateKey) from lineorder;
Array
(
    [count ] => 2406
)
1 rows returned
Exec time: 0.10923719406128

Это не опечатка, это действительно меньше секунды по сравнению с минутами в обычном MySQL.

Это потому, что Shard-Query использует GROUP BY для ответа на этот запрос и возможно свободное сканирование индекса PRIMARY KEY:

mysql> explain partitions SELECT LO_OrderDateKey AS expr_2839651562
    -> FROM lineorder  PARTITION(p7)  AS `lineorder`   WHERE 1=1  AND 1=1  GROUP BY LO_OrderDateKey
    -> \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineorder
   partitions: p7
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 80108
        Extra: Using index for group-by
1 row in set (0.00 sec)

Затем будет проверен еще один простой запрос, сначала на обычном MySQL:

mysql> select count(*) from lineorder;
+----------+
| count(*) |
+----------+
| 59986052 |
+----------+
1 row in set (4 min 8.70 sec)

Опять же, EXPLAIN показывает полное сканирование таблицы:

mysql> explain select count(*) from lineorder\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineorder
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 25
          ref: NULL
         rows: 58922188
        Extra: Using index
1 row in set (0.00 sec)

Теперь Shard-Query не может сделать ничего особенного, чтобы ускорить этот запрос, кроме как выполнить его параллельно, аналогично первому запросу:

[0] => SELECT COUNT(*) AS expr_3190753946
FROM lineorder PARTITION(p0) AS `lineorder` WHERE 1=1 AND 1=1
[1] => SELECT COUNT(*) AS expr_3190753946
FROM lineorder PARTITION(p1) AS `lineorder` WHERE 1=1 AND 1=1
[2] => SELECT COUNT(*) AS expr_3190753946
FROM lineorder PARTITION(p2) AS `lineorder` WHERE 1=1 AND 1=1
[3] => SELECT COUNT(*) AS expr_3190753946
FROM lineorder PARTITION(p3) AS `lineorder` WHERE 1=1 AND 1=1
...

SQL агрегации аналогичен, но на этот раз функция агрегирования изменяется на SUM для объединения COUNT из каждого раздела:

SELECT SUM(expr_3190753946) AS ` count `
FROM `aggregation_tmp_51969525`

И запрос немного быстрее на 140,24 секунды по сравнению с результатом MySQL 248,7 секунды:

Array
(
[count ] => 59986052
)
1 rows returned
Exec time: 140.24419403076

Наконец, я хочу взглянуть на более сложный запрос, который использует объединения и агрегирование.

mysql> explain select d_year, c_nation,  sum(lo_revenue - lo_supplycost) as profit  from lineorder
join dim_date  on lo_orderdatekey = d_datekey
join customer  on lo_custkey = c_customerkey
join supplier  on lo_suppkey = s_suppkey
join part  on lo_partkey = p_partkey
where  c_region = 'AMERICA'  and s_region = 'AMERICA'
and (p_mfgr = 'MFGR#1'  or p_mfgr = 'MFGR#2')
group by d_year, c_nation  order by d_year, c_nation;
+----+-------------+-----------+--------+---------------+---------+---------+--------------------------+------+---------------------------------+
| id | select_type | table     | type   | possible_keys | key     | key_len | ref                      | rows | Extra                           |
+----+-------------+-----------+--------+---------------+---------+---------+--------------------------+------+---------------------------------+
|  1 | SIMPLE      | dim_date  | ALL    | PRIMARY       | NULL    | NULL    | NULL                     |    5 | Using temporary; Using filesort |
|  1 | SIMPLE      | lineorder | ref    | PRIMARY       | PRIMARY | 4       | ssb.dim_date.D_DateKey   |   89 | NULL                            |
|  1 | SIMPLE      | supplier  | eq_ref | PRIMARY       | PRIMARY | 4       | ssb.lineorder.LO_SuppKey |    1 | Using where                     |
|  1 | SIMPLE      | customer  | eq_ref | PRIMARY       | PRIMARY | 4       | ssb.lineorder.LO_CustKey |    1 | Using where                     |
|  1 | SIMPLE      | part      | eq_ref | PRIMARY       | PRIMARY | 4       | ssb.lineorder.LO_PartKey |    1 | Using where                     |
+----+-------------+-----------+--------+---------------+---------+---------+--------------------------+------+---------------------------------+
5 rows in set (0.01 sec)

Вот запрос на обычном MySQL:

mysql> select d_year, c_nation,  sum(lo_revenue - lo_supplycost) as profit  from lineorder  join dim_date  on lo_orderdatekey = d_datekey  join customer  on lo_custkey = c_customerkey  join supplier  on lo_suppkey = s_suppkey  join part  on lo_partkey = p_partkey  where  c_region = 'AMERICA'  and s_region = 'AMERICA'  and (p_mfgr = 'MFGR#1'  or p_mfgr = 'MFGR#2')  group by d_year, c_nation  order by d_year, c_nation;
+--------+---------------+--------------+
| d_year | c_nation      | profit       |
+--------+---------------+--------------+
|   1992 | ARGENTINA     | 102741829748 |
...
|   1998 | UNITED STATES |  61345891337 |
+--------+---------------+--------------+
35 rows in set (11 min 56.79 sec)

Опять же, Shard-Query разделяет запрос для выполнения по каждому разделу (я не буду утомлять вас подробностями), и он выполняет запрос быстрее, чем MySQL, за 343,3 секунды по сравнению с ~ 720:

Array
(
    [d_year] => 1998
    [c_nation] => UNITED STATES
    [profit] => 61345891337
)
35 rows returned
Exec time: 343.29854893684

Я надеюсь, вы понимаете, как использование Shard-Query может ускорить запросы без использования шардинга на одном сервере. Все, что вам действительно нужно сделать, это добавить разделение.

Вы можете получить Shard-Query от GitHub по адресу http://github.com/greenlion/swanhart-tools

Обратите внимание: Сконфигурируйте и установите Shard-Query как обычно, но просто используйте один узел и задайте для параметра столбца (столбца сегмента) значение «nocolumn» или false, поскольку вам не требуется использовать столбец сегмента, если вы не разделяете.

Параллельный запрос для MySQL с помощью Shard-Query

Категории

Последние статьи

Рефакторинг Hudson God Class

Альтернативы синтаксиса Java лямбда

Morphia и MongoDB: развивающиеся структуры документов

OpenShift Express: развертывание приложения Java EE (с поддержкой AS7)

Интеграция jqGrid, REST, AJAX и Spring MVC