mysql trwa zbyt długo, aby wysłać dane

Mam prostą tabelę z milionem rekordów (14 000 000) i na proste zapytanie spędza zbyt dużo czasu na „wysyłaniu danych”.

Stół

CREATE TABLE IF NOT EXISTS details (
  id int(11) NOT NULL,
  date date NOT NULL,
  time int(2) NOT NULL,
  minutes_online decimal(5,0) NOT NULL,
  minutes_playing decimal(5,0) NOT NULL,
  minutes_chatting decimal(5,0) NOT NULL,
  minutes_away decimal(5,0) NOT NULL
  PRIMARY KEY (id,date,time)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Proste zapytanie

mysql> SELECT * FROM details WHERE id = 3014595;

Wyjaśnić

mysql> EXPLAIN SELECT * FROM details WHERE id = 3014595;
+----+-------------+-----------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table     | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | details   | ref  | PRIMARY       | PRIMARY | 4       | const | 1482 |       |
+----+-------------+-----------+------+---------------+---------+---------+-------+------+-------+

Profil zapytania

mysql> SHOW PROFILE FOR QUERY 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000024 |
| checking query cache for query | 0.000078 |
| checking permissions           | 0.000014 |
| Opening tables                 | 0.000126 |
| System lock                    | 0.000011 |
| Table lock                     | 0.000030 |
| init                           | 0.000027 |
| optimizing                     | 0.000117 |
| statistics                     | 0.040077 |
| preparing                      | 0.000029 |
| executing                      | 0.000006 |
| Sending data                   | 7.536960 |
| end                            | 0.000013 |
| query end                      | 0.000004 |
| freeing items                  | 0.000037 |
| storing result in query cache  | 0.000006 |
| logging slow query             | 0.000003 |
| cleaning up                    | 0.000006 |
+--------------------------------+----------+

Jak widać, SELECTinstrukcja używała indeksu i odczytywała tylko 1482 wiersze. Jednak zapytanie spędziło 7,536960 sekund na przesłanie danych. To tak, jakby zapytanie odczytało znacznie więcej potrzebnych wierszy.

Jest to proste zapytanie, z zaledwie 7 polami (wiersz średnio 59 bajtów) i bez wymyślnej funkcji. Masz pojęcie, co może być tego przyczyną?

Uwaga: id to identyfikator użytkownika. Każdy użytkownik może mieć co najmniej jeden wpis na każdą godzinę każdego dnia. Dlatego identyfikator nie jest unikalny.

Edycja: Mam inną tabelę o tej samej strukturze i znacznie większej liczbie wierszy (34 miliony). Jeśli uruchomię to samo zapytanie w tej większej tabeli, wyniki będą zwracane w mniej niż 1 sekundę.

Jedyną różnicą jest to, że większy stół nie otrzymuje tylu zapytań, co mniejszy stół.

• Czy to możliwe, że liczba zapytań spowalnia proces? Pamięć podręczna MySQL jest włączona. Mam również CakePHP buforowanie zapytań, aby zmniejszyć liczbę zapytań.
• Czy to możliwe, że plik, w którym zapisana jest tabela, jest uszkodzony?

Aktualizacja Problem został rozwiązany przez oddzielenie warstwy danych od warstwy internetowej. Warstwa danych również została zaktualizowana do pamięci RAM i działa na raid10.

Dla każdego, kto natknie się na to pytanie i zastanawia się, nawet bez aktualizacji pamięci RAM, dlaczego wysyłanie danych trwało tak długo. Dzieje się tak, ponieważ wysyłanie danych faktycznie obejmuje czas wyszukiwania danych, które mają zostać wysłane.

https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

Wątek odczytuje i przetwarza wiersze instrukcji SELECT i wysyła dane do klienta. Ponieważ operacje zachodzące w tym stanie mają tendencję do wykonywania dużej ilości dostępu do dysku (odczytów), często jest to stan najdłużej działający w czasie trwania danego zapytania.

Spróbuj Zoptymalizuj tabelę za pomocą Optymalizuj tabelę tablename i sprawdź status.

Należy dokonać dużych zmian:

Alter table tablename engine = 'INNODB'

Pomoże ci to dużo i być może powinien być jeden klucz podstawowy w tabeli, ale dodałeś trzy kolumny jako klucz podstawowy.

Utwórz osobny indeks dla identyfikatora:

zmień szczegóły tabeli dodaj klucz d1 (id);

Aby zastosować ten indeks, uruchom ponownie MySQL lub

analizować szczegóły tabeli;

Jeśli to możliwe, możesz również zmienić bazę danych na InnoDB w celu obsługi transakcji i innych korzyści.