Jak poprawić wykonywanie liczby zapytań za pomocą replikacji mySql?


0

W moim raporcie muszę używać kwerend liczących (na przykład „user_activity”), a następnie grupować według innej kolumny (typ_użytkownika) w innej tabeli (tabela użytkownika).

I to jest naprawdę czasochłonne, jeśli chodzi o milion danych rekordów. Więc zdecydowałem się użyć mySql Replicate, aby utworzyć podrzędną bazę danych dla celów raportowania.Ale wciąż walczę z tymi rzeczami:

  1. Czy chcesz powielić część swojej bazy danych w zamian za szybkość zapytania?
  2. Czy istnieje możliwość automatycznego dodawania danych do kolumny user_type w tabeli „user_activity” (db slave) podczas synchronizacji?
  3. I jak zoptymalizować bazę danych do celów czytania?(dane sprawozdawcze, analizy, ..)

aktualizacja: to jest mój stworzony sql

Użytkownik tabeli

CREATE TABLE 'library_th'.'user' ( 
'id' INT UNSIGNED NOT NULL , 
'username' VARCHAR(100) NOT NULL , 
'password' VARCHAR(32) NOT NULL , 
'user_type' TINYINT NOT NULL DEFAULT '0' COMMENT 'this user is created automatically by system or by them himself?' , 
PRIMARY KEY ('id')
) ENGINE = InnoDB;

Aktywność użytkownika tabeli:

CREATE TABLE IF NOT EXISTS 'user_activity' (
    'id' int(10) unsigned NOT NULL,
    'user_id' int(10) unsigned NOT NULL,
    'action' int(11) NOT NULL DEFAULT '0' COMMENT 'what activity did user do?',
    'datetime_created' datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE 'user_activity'
    ADD KEY 'user_id' ('user_id');

a moje zapytanie o liczbę, którego chcę użyć, to:

select count(ua.id) as 'total', u.user_type, ua.action from user_activity ua
inner join user u on ua.user_id = u.id
group by ua.action, u.user_type
  0

Pokaż nam aktualne zapytanie i 'SHOW CREATE TABLEs'.Może być proste rozwiązanie. 16 lip. 162016-07-16 05:57:28

  0

Zaktualizowałem moje zapytania dotyczące tworzenia i liczenia 16 lip. 162016-07-16 15:30:24

0

Spróbuj:

Dodaj ten złożony indeks douser_activity:INDEX(user_id, action).Następnie

SELECT SUM(x.ct), u.user_type, x.action
     FROM (
       SELECT user_id, action, COUNT(*) AS ct
         FROM user_activity
         GROUP BY user_id, action
        ) x
     JOIN user u ON x.user_id = u.id
     GROUP BY x.action, u.user_type;
  0

Jest szybszy niż moje stare zapytanie, ale wciąż jest wolny na 2 miliony rekordów.Wykonanie po raz pierwszy zajęło ~ 7s. 16 lip. 162016-07-16 18:05:01

  0

Skanowanie 2 milionów rekordów wymaga czasu. 17 lip. 162016-07-17 16:18:14

  0

Tak, ale czy i tak chcesz przyspieszyć ten skan?lubię akceptować duplikowanie danych, umieszczając „user_type” w tabeli „user_activity” lub inne metody ... 17 lip. 162016-07-17 17:53:13


0

W hurtowni danych „Tabele podsumowań” są bardzo ważne dla wydajności.

Tabela podsumowań zawiera liczby i sumy częściowe dla każdego dnia (lub innego okresu).„Raporty”, takie jak zapytanie, są następnie uruchamiane przy użyciu tabeli podsumowań i mogą działać znacznie szybciej niż w przypadku danych „surowych”.

Dla twojego przypadku zbuduj i stopniowo utrzymuj tabelę, powiedzmy:
dzień, typ_użytkownika, akcja, liczba
Pierwsze 3 kolumny toPRIMARY KEY, ostatni będzie sumą cząstkową.

Potrzebujesz indeksudatetime_createdaby sprawniej wykonać nocną aktualizację.

  0

Pozwól mi to wyjaśnić: muszę utworzyć tabelę, która ma 4 pola, a dla każdej kwerendy wstawiania do mojej tabeli user_activity zwiększ odpowiednie pole „count” do 1, albo wykonam zadanie cron, aby je policzyć koniec dnia? 17 lip. 162016-07-17 17:50:43

  0

Zaplanuj A: 'WSTAWIĆ. NA DUPLIKOWANEJ AKTUALIZACJI KLUCZA ...', aby wstawić liczbę wypukłości.Plan B: nocne zadanie crona do obliczania statystyk dnia.Plan C: (mogą istnieć inne sposoby.) [Więcej dyskusji] (http://mysql.rjweb.org/doc.php/summarytables). 17 lip. 162016-07-17 19:04:35