Czy istnieje sposób zoptymalizowania sortowania według kolumn połączonych tabel?

To jest moje wolne zapytanie:

SELECT `products_counts`.`cid`
FROM
  `products_counts` `products_counts`

  LEFT OUTER JOIN `products` `products` ON (
  `products_counts`.`product_id` = `products`.`id`
  )
  LEFT OUTER JOIN `trademarks` `trademark` ON (
  `products`.`trademark_id` = `trademark`.`id`
  )
  LEFT OUTER JOIN `suppliers` `supplier` ON (
  `products_counts`.`supplier_id` = `supplier`.`id`
  )
WHERE
  `products_counts`.product_id IN
  (159, 572, 1075, 1102, 1145, 1162, 1660, 2355, 2356, 2357, 3236, 6471, 6472, 6473, 8779, 9043, 9095, 9336, 9337, 9338, 9445, 10198, 10966, 10967, 10974, 11124, 11168, 16387, 16689, 16827, 17689, 17920, 17938, 17946, 17957, 21341, 21352, 21420, 21421, 21429, 21544, 27944, 27988, 30194, 30196, 30230, 30278, 30699, 31306, 31340, 32625, 34021, 34047, 38043, 43743, 48639, 48720, 52453, 55667, 56847, 57478, 58034, 61477, 62301, 65983, 66013, 66181, 66197, 66204, 66407, 66844, 66879, 67308, 68637, 73944, 74037, 74060, 77502, 90963, 101630, 101900, 101977, 101985, 101987, 105906, 108112, 123839, 126316, 135156, 135184, 138903, 142755, 143046, 143193, 143247, 144054, 150164, 150406, 154001, 154546, 157998, 159896, 161695, 163367, 170173, 172257, 172732, 173581, 174001, 175126, 181900, 182168, 182342, 182858, 182976, 183706, 183902, 183936, 184939, 185744, 287831, 362832, 363923, 7083107, 7173092, 7342593, 7342594, 7342595, 7728766)
ORDER BY
  products_counts.inflow ASC,
  supplier.delivery_period ASC,
  trademark.sort DESC,
  trademark.name ASC
LIMIT
  0, 3;

Średni czas zapytania wynosi 4,5 s w moim zestawie danych i jest to niedopuszczalne.

Rozwiązania, które widzę:

Dodaj wszystkie kolumny z klauzuli kolejności do products_countstabeli. Ale mam około 10 rodzajów zamówień w aplikacji, więc powinienem utworzyć wiele kolumn i indeksów. Plus products_countsmają bardzo intensywne aktualizacje / wstawiania / usuwania, więc muszę natychmiast zaktualizować wszystkie kolumny związane z zamówieniem (używając wyzwalaczy?).

Czy jest inne rozwiązanie?

Wyjaśnić:

+----+-------------+-----------------+--------+---------------------------------------------+------------------------+---------+----------------------------------+------+----------------------------------------------+
| id | select_type | table           | type   | possible_keys                               | key                    | key_len | ref                              | rows | Extra                                        |
+----+-------------+-----------------+--------+---------------------------------------------+------------------------+---------+----------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | products_counts | range  | product_id_supplier_id,product_id,pid_count | product_id_supplier_id | 4       | NULL                             |  227 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | products        | eq_ref | PRIMARY                                     | PRIMARY                | 4       | uaot.products_counts.product_id  |    1 |                                              |
|  1 | SIMPLE      | trademark       | eq_ref | PRIMARY                                     | PRIMARY                | 4       | uaot.products.trademark_id       |    1 |                                              |
|  1 | SIMPLE      | supplier        | eq_ref | PRIMARY                                     | PRIMARY                | 4       | uaot.products_counts.supplier_id |    1 |                                              |
+----+-------------+-----------------+--------+---------------------------------------------+------------------------+---------+----------------------------------+------+----------------------------------------------+

Struktura tabel:

CREATE TABLE `products_counts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) unsigned NOT NULL,
  `supplier_id` int(11) unsigned NOT NULL,
  `count` int(11) unsigned NOT NULL,
  `cid` varchar(64) NOT NULL,
  `inflow` varchar(10) NOT NULL,
  `for_delete` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `cid` (`cid`),
  UNIQUE KEY `product_id_supplier_id` (`product_id`,`supplier_id`),
  KEY `product_id` (`product_id`),
  KEY `count` (`count`),
  KEY `pid_count` (`product_id`,`count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `external_id` varchar(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `category_id` int(11) unsigned NOT NULL,
  `trademark_id` int(11) unsigned NOT NULL,
  `photo` varchar(255) NOT NULL,
  `sort` int(11) unsigned NOT NULL,
  `otech` tinyint(1) unsigned NOT NULL,
  `not_liquid` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `applicable` varchar(255) NOT NULL,
  `code_main` varchar(64) NOT NULL,
  `code_searchable` varchar(128) NOT NULL,
  `total` int(11) unsigned NOT NULL,
  `slider` int(11) unsigned NOT NULL,
  `slider_title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `external_id` (`external_id`),
  KEY `category_id` (`category_id`),
  KEY `trademark_id` (`trademark_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `trademarks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `external_id` varchar(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `country_id` int(11) NOT NULL,
  `sort` int(11) unsigned NOT NULL DEFAULT '0',
  `sort_list` int(10) unsigned NOT NULL DEFAULT '0',
  `is_featured` tinyint(1) unsigned NOT NULL,
  `is_direct` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `external_id` (`external_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `suppliers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `external_id` varchar(36) NOT NULL,
  `code` varchar(64) NOT NULL,
  `name` varchar(255) NOT NULL,
  `delivery_period` tinyint(1) unsigned NOT NULL,
  `is_default` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `external_id` (`external_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Informacje o serwerze MySQL:

mysqld  Ver 5.5.45-1+deb.sury.org~trusty+1 for debian-linux-gnu on i686 ((Ubuntu))

Przejrzenie definicji tabeli pokazuje, że masz indeksy pasujące do zaangażowanych tabel. Powinno to spowodować, że złączenia będą realizowane tak szybko, jak to możliwe, w granicach MySQL'slogiki łączenia.

Jednak sortowanie z wielu tabel jest bardziej złożona.

W 2007 roku Siergiej Petrunia opisał 3 MySQLalgorytmy sortowania według prędkości dla MySQL: http://s.petrunia.net/blog/?m=201407

1. Użyj metody dostępu opartej na indeksie, która daje uporządkowane dane wyjściowe
2. Użyj filesort()na 1. niestałym stole
3. Umieść wynik łączenia w tymczasowej tabeli i użyj filesort()go

Z przedstawionych powyżej definicji tabel i złączeń widać, że nigdy nie uzyskasz najszybszego sortowania . Oznacza to, że będziesz zależał od filesort()kryteriów sortowania, których używasz.

Jeśli jednak zaprojektujesz i wykorzystasz widok zmaterializowany , będziesz mógł użyć algorytmu najszybszego sortowania.

Aby zobaczyć szczegóły zdefiniowane dla MySQL 5.5metod sortowania, patrz: http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html

Aby MySQL 5.5(w tym przykładzie) zwiększyć ORDER BYprędkość, jeśli nie możesz MySQLużyć indeksów zamiast dodatkowej fazy sortowania, wypróbuj następujące strategie:

• Zwiększ sort_buffer_sizewartość zmiennej.

• Zwiększ read_rnd_buffer_sizewartość zmiennej.

• Używaj mniej pamięci RAM na wiersz, deklarując kolumny tylko tak duże, jak to konieczne do przechowywania rzeczywistych wartości. [Np. Zmniejsz varchar (256) do varchar (ActualLongestString)]

• Zmień tmpdirzmienną systemową, aby wskazywała na dedykowany system plików z dużą ilością wolnego miejsca. (Inne szczegóły są dostępne w linku powyżej).

MySQL 5.7Dokumentacja zawiera więcej szczegółów w celu zwiększenia ORDERprędkości, z których niektóre mogą być nieco ulepszonymi zachowaniami:

http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

Widoki zmaterializowane - inne podejście do sortowania połączonych tabel

Nawiązałeś do widoków zmaterializowanych, a twoje pytanie dotyczyło użycia wyzwalaczy. MySQL nie ma wbudowanej funkcji tworzenia widoku zmaterializowanego, ale masz potrzebne narzędzia. Używając wyzwalaczy do rozłożenia obciążenia, możesz zachować widok zmaterializowany do chwili obecnej.

Widok zmaterializowany w rzeczywistości jest tabela , która jest wypełniana przez kodeksu postępowania zbudować lub odbudować widok zmaterializowany i utrzymywana przez wyzwalacze zachować dane up-to-date.

Ponieważ budujesz tabelę, która będzie miała indeks , wówczas w widoku zapytań zmaterializowanych można użyć najszybszej metody sortowania : Użyj metody dostępu opartej na indeksie, która daje uporządkowane dane wyjściowe

Ponieważ MySQL 5.5używa wyzwalaczy do utrzymania widoku zmaterializowanego , potrzebny będzie również proces, skrypt lub procedura składowana, aby zbudować początkowy widok zmaterializowany .

Ale jest to oczywiście zbyt ciężki proces, aby można go było uruchomić po każdej aktualizacji tabel podstawowych, w których zarządzasz danymi. Właśnie wtedy uruchamiane są wyzwalacze, które aktualizują dane w miarę wprowadzania zmian. W ten sposób każdy insert, updatei deletebędzie propagować ich zmian, przy użyciu wyzwalaczy, na widok zmaterializowany .

Organizacja FROMDUAL na stronie http://www.fromdual.com/ ma przykładowy kod do obsługi widoku zmaterializowanego . Zamiast pisać własne próbki, wskażę ich próbki:

http://www.fromdual.com/mysql-materialized-views

Przykład 1: Budowanie zmaterializowanego widoku

DROP TABLE sales_mv;
CREATE TABLE sales_mv (
    product_name VARCHAR(128)  NOT NULL
  , price_sum    DECIMAL(10,2) NOT NULL
  , amount_sum   INT           NOT NULL
  , price_avg    FLOAT         NOT NULL
  , amount_avg   FLOAT         NOT NULL
  , sales_cnt    INT           NOT NULL
  , UNIQUE INDEX product (product_name)
);

INSERT INTO sales_mv
SELECT product_name
    , SUM(product_price), SUM(product_amount)
    , AVG(product_price), AVG(product_amount)
    , COUNT(*)
  FROM sales
GROUP BY product_name;

To daje widok zmaterializowany w momencie odświeżania. Ponieważ jednak masz szybko zmieniającą się bazę danych, chcesz również zachować ten widok tak aktualny, jak to możliwe.

Dlatego zmienione tabele danych podstawowych muszą mieć wyzwalacze do propagowania zmian z tabeli podstawowej do tabeli widoku zmaterializowanego. Jako przykład:

Przykład 2: Wstawianie nowych danych do zmaterializowanego widoku

DELIMITER $$

CREATE TRIGGER sales_ins
AFTER INSERT ON sales
FOR EACH ROW
BEGIN

  SET @old_price_sum = 0;
  SET @old_amount_sum = 0;
  SET @old_price_avg = 0;
  SET @old_amount_avg = 0;
  SET @old_sales_cnt = 0;

  SELECT IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0)
       , IFNULL(amount_avg, 0), IFNULL(sales_cnt, 0)
    FROM sales_mv
   WHERE product_name = NEW.product_name
    INTO @old_price_sum, @old_amount_sum, @old_price_avg
       , @old_amount_avg, @old_sales_cnt
  ;

  SET @new_price_sum = @old_price_sum + NEW.product_price;
  SET @new_amount_sum = @old_amount_sum + NEW.product_amount;
  SET @new_sales_cnt = @old_sales_cnt + 1;
  SET @new_price_avg = @new_price_sum / @new_sales_cnt;
  SET @new_amount_avg = @new_amount_sum / @new_sales_cnt;

  REPLACE INTO sales_mv
  VALUES(NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg
       , @new_amount_avg, @new_sales_cnt)
  ;

END;
$$
DELIMITER ;

Oczywiście będziesz potrzebować wyzwalaczy, aby utrzymać usuwanie danych z widoku zmaterializowanego i aktualizować dane w widoku zmaterializowanym . Próbki są również dostępne dla tych wyzwalaczy.

OSTATNIE: Jak to sprawia, że ​​sortowanie połączonych tabel jest szybsze?

Widok zmaterializowany budowany jest stale jako aktualizacje są do niego. Dlatego możesz zdefiniować Indeks (lub Indeksy ), którego chcesz użyć do sortowania danych w widoku zmaterializowanym lub tabeli .

Jeśli narzut związany z utrzymywaniem danych nie jest zbyt duży, to wydajesz pewne zasoby (CPU / IO / itp.) Na każdą istotną zmianę danych, aby zachować widok zmaterializowany, a tym samym dane indeksu są aktualne i łatwo dostępne. Dlatego wybór będzie szybszy, ponieważ:

1. Wydano już przyrostowy procesor i We / Wy, aby przygotować dane do SELECT.
2. Indeks w widoku zmaterializowanym może korzystać z najszybszej metody sortowania dostępnej dla MySQL, a mianowicie z użyciem metody dostępu opartej na indeksie, która wytwarza uporządkowane dane wyjściowe .

W zależności od okoliczności i tego, co czujesz do całego procesu, możesz odbudować Widoki zmaterializowane każdej nocy w wolnym czasie.

Uwaga: W Microsoft SQL Server zmaterializowane perspektywy określane są widoki indeksowane i są automatycznie aktualizowane w oparciu o wyżej widok indeksowany w metadanych.

Nie ma tu wiele do zrobienia, ale domyślam się, że głównym problemem jest to, że tworzysz dość duży tymczasowy stół i sortujesz plik na dysku za każdym razem. Powodem jest:

1. Używasz UTF8
2. Do sortowania używasz dużych pól varchar (255)

Oznacza to, że twoja tabela tymczasowa i plik sortowania mogą być dość duże, ponieważ podczas tworzenia tabeli tymczasowej pola są tworzone na MAKSYMALNEJ długości, a podczas sortowania rekordy są na MAKSYMALNEJ długości (a UTF8 ma 3 bajty na znak). Wykluczają one również prawdopodobnie stosowanie tabeli tymczasowej w pamięci. Aby uzyskać więcej informacji, zobacz szczegóły wewnętrznych tabel temp .

LIMIT również nam tu nie służy, ponieważ musimy się zmaterializować i uporządkować cały zestaw wyników, zanim będziemy wiedzieć, jakie są pierwsze 3 rzędy.

Czy próbowałeś przenieść swój tmpdir do systemu plików tmpfs ? Jeśli / tmp nie używa już tmpfs (MySQL tmpdir=/tmpdomyślnie używa * nix), możesz użyć bezpośrednio / dev / shm. W twoim pliku my.cnf:

[mysqld]
...
tmpdir=/dev/shm  

Następnie musisz zrestartować mysqld.

To może mieć ogromną różnicę. Jeśli najprawdopodobniej będziesz narażony na presję pamięci w systemie, prawdopodobnie chcesz zmniejszyć rozmiar (zwykle Linux domyślnie ogranicza tmpfs do 50% całkowitej pamięci RAM), aby uniknąć zamiany segmentów pamięci na dysk, a nawet gorzej sytuacja OOM . Możesz to zrobić, edytując linię w /etc/fstab:

tmpfs                   /dev/shm                tmpfs   rw,size=2G,noexec,nodev,noatime,nodiratime        0 0

Możesz także zmienić rozmiar „online”. Na przykład:

mount -o remount,size=2G,noexec,nodev,noatime,nodiratime /dev/shm

Możesz także zaktualizować do MySQL 5.6 - który ma wydajne podkwerendy i tabele pochodne - i pobawić się zapytaniem nieco więcej. Nie sądzę jednak, że zobaczymy wielkie wygrane na tej trasie, z tego, co widzę.

Powodzenia!