Keskustelu: Nettisivujen teko: Tietokannan optimointi

Toisessa ketjussa jo hiukan sivuttiin tätä tietokannan optimointia ja se olikin sen hetkisillä kyselyillä riittävän nopea minulle. Nyt teen kuitenkin hiukan laajennusta koodiini ja jostain syystä SQL-kysely vie paljon aikaa, vaikka minusta indeksit näyttäisivät olevan kohdillaan.
Kyseessä on siis sama 25 miljoonaa riviä sisältävä user_logs taulu, josta haen tietoa seuraavalla kyselyllä.

SELECT COUNT(*) AS hits, DATE_FORMAT(FROM_UNIXTIME(timecreated), '%Y-%m-%d') AS timestamp
FROM user_logs
WHERE userid = ? AND action NOT IN ('loggedinas', 'loggedout') AND realuserid IS NULL
GROUP BY timestamp

Tietokannalla kestää yli kymmenen sekuntia suorittaa tämä kysely käyttäjien kohdalla, jotka ovat olleet hyvin aktiivisia sivuilla siitä huolimatta, että tietokannassa on indeksi

CREATE INDEX user_actions
ON user_logs(userid,action,realuserid);

Missä kohtaa indeksini tai kyselyni menee pieleen?

Onkos tuo COUNT(*) sellainen, jossa ei indexejä pysty hyödyntämään?

En ole vielä kovin sinut näiden indeksien kanssa. Tuo COUNT(*):ko sen tekee, ettei kysely voi käyttää tätä määrittelemääni indeksiä? Miten indeksit toimivat tuon COUNT(*) kanssa?

Ok, ei johtune siitä:

So overall is always better to leave it as COUNT(*) and let the optimizer choose.

Mitähän EXPLAIN sanoo tuosta kyselystä?

Olisi järkevää hankkiutua eroon DATE_FORMAT-kutsusta ja merkkijonoista, nimittäin DATE(x) näyttää olevan todella paljon nopeampi kuin DATE_FORMAT(x).

Kuten toisessa keskustelussa mainitsin, jos käytetään paljon juuri päivämäärää, voisi olla fiksua laittaa tauluun suoraan päivämäärä, niin ei tarvitsisi tehdä muunnosta. Tällöin myös päivämäärän ottaminen mukaan indeksiin saattaisi auttaa.

Niin tosiaan tuo DATE_FORMAT -> DATE muutos unohtui tehdä. Tein sen nyt ja kyllä se hiukan nopeutti hakua, mutta ei mitenkään merkittävästi (10s -> 9s).

Muutin hakua enemmän tarkoituksiini sopivaksi ja muutin indeksin sen mukaan, mutta mikään ei muuttunut kyselyn keston suhteen. Kysely näyttää nyt tältä:

SELECT COUNT(*) AS hits, DATE(FROM_UNIXTIME(timecreated, '%Y-%m-%d')) AS timestamp
FROM user_logs
WHERE action NOT IN ('loggedinas', 'loggedout') AND realuserid IS NULL AND target NOT IN ('user_login', 'webservice_login') AND userid = ?
GROUP BY timestamp

ja EXPLAIN sanoo:

id	select_type	table		partitions	type	possible_keys			key			key_len	ref		rows	filtered	Extra
1	SIMPLE		user_logs	NULL		range	user_actions,loggedin	loggedin	410		NULL	2138871	8.00		Using index condition; Using where; Using temporary; Using filesort

Nyt user_actions ja loggedin ovat:

CREATE INDEX user_actions
ON user_logs(action,realuserid,target,userid);

CREATE INDEX loggedin
ON user_logs(userid,action);

EDIT1: Jotenkin jäi huomaamatta tuo ohje jättää merkkijonot pois. Pudotin '%Y-%m-%d' pois ja kysely nopeutui, mutta jälleen vain marginaalisen vähän. Kaikkein aktiivisimman käyttäjän kohdalla kysely nopeutui 29s -> 28s.

EDIT2: Jos laitan tauluun suoraan päivämäärän, niin millä kyselyllä saan lisättyä sen päivämäärän näille valmiiksi taulussa oleville 25 miljoonalle riville? Veikkaan, että

UPDATE user_logs SET datecreated = DATE(timecreated)

EXPLAIN kertoo, että kysely käyttää loggedin-indeksiä, eli jostain syystä ensimmäinen indeksi ei tule käyttöön.

Mitähän action ja target ovat tyypiltään? Toivottavasti eivät merkkijonoja! Tekstin käsittely on käytännössä aina hitaampaa kuin lukujen, ja toistuva teksti myös vie turhaa tilaa tietokannassa. Jos nuo ovat merkkijonoja, kannattaa siirtyä joko ENUMin käyttöön tai järjestelyyn, jossa vaihtoehdot on listattu toisessa taulussa ja muualle tallennetaan vain id. Eli esimerkiksi actions-taulussa olisi lista näistä (1=loggedinas, 2=loggedout) ja user_log-taulussa olisi vain viittaus action_id. ENUM-tyypillä voi välttää ylimääräisen taulun, mutta ENUM tuo omat rajoituksensa: vaihtoehtojen muuttaminen tai lisääminen jälkikäteen on vaikeampaa, ja samoja vaihtoehtoja ei voi suoraan käyttää useassa eri taulussa (ts. ne pitää määritellä aina erikseen).

Päivämäärien lisäämisessä muista edelleen se FROM_UNIXTIME, koska DATE(luku) tuottaa vain arvon NULL, paitsi jos luku sattuu olemaan muotoa YYYYMMDD.

timestamp bigint(10), action varchar(100), realuserid bigint(10), target varchar(100), userid bigint(10)

Eli kyllä tässä nimenomaan tutkitaan kahden merkkijonon arvoja. Sanotko, että tämä 28 sekuntia tulee siitä, että haetaan noista kahdesta merkkijonosta? Pitänee sitten työstää, että saa ne erillisiin tauluihin.

Ei mitään erillisiä tauluja vaan opettelet käyttämään enumeja. Tästäkin on jo aiemmin sanottu.

Tuo 28 sekuntia tulee ensisijaisesti siitä, ettei kysely käytä sellaista indeksiä, minkä pitäisi auttaa nopeuttamaan kyselyä. Silloin kun kanta ei osaa tai halua käyttää indeksejä, niin kyselyt ovat aina hitaita, olipa sarakkeiden tietotyyppi mitä tahansa.

NOT IN -kyselyistä joku on sanonut, että MySQL ei käytä indeksiä sen vuoksi, että sen pitää kuitenkin selvittää, mitä muita arvoja kyseisellä sarakkeella voi olla.

Jos tuo selitys pitää paikkansa, niin enumien käyttö luultavasti korjaisi ongelman automaattisesti, sillä silloin MySQL tietää suoraan enum-listan perusteella, mitä arvoja sarakkeessa voi olla. Kun käytetään avoimia varchareja, niin kanta ei voi tietää arvojoukkoa lukematta kaikkia rivejä läpi.

(Omaan mutukorvaani kuulostaa siltä, että jos lisäät erilliset indeksit action- ja target-sarakkeille, niin sekin voi helpottaa tilannetta, mutta en todellakaan tiedä varmaksi. Eikä se silti välttämättä ole yhtä nopea kuin jos käyttäisit enumeja. Indeksien laatiminen on oma taitolajinsa, eikä niitä kannata roiskia sinne tänne ns. varmuuden vuoksi.)

Toki yleensä ottaen on järkevämpi olla käyttämättä NOT IN -kyselyitä, koska todennäköisesti haluat kuitenkin lukea vain ne rivit, joilla on jokin sallittu arvo, kuin minkä tahansa rivin, jolla ei ole noita kiellettyjä arvoja. (Ehkä myöhemmin lisäät kantaan uuden kielletyn arvon ja sitten on hankala päivitellä noita NOT IN -kyselyitä.)