ugrás a tartalomhoz

Kapcsolt táblák mutatózása? (INDEXES in JOIN)

laji · 2011. Jún. 15. (Sze), 12.09
Alapvető, de nem sikerült választ találnom: hogyan egyeztessek össze két követelményt?
1. Ne legyenek fölösen ismétlődő adatok - ki kell vinni őket egy újabb táblába.
2. Meglegyenek a hatékonysághoz kellő mutatók [INDEX] a lekérdezéshez használt táblában.

Látogató-kimutatás (részlete).
HA: hálóazonosító [IP]
OKULCS: országkulcs (a GeoIP alapján)
TKULCS: területkulcs (a GeoIP alapján)

SET NAMES utf8;
# ----------------------------------------------------------------
# Tábla: geoip_ország (4 oszlop)
CREATE TABLE `geoip_ország` (
	`OKULCS`	varchar(2) COLLATE utf8_general_ci NOT NULL COMMENT 'A GeoIP adata',
	`ORSZÁG`	varchar(60) COLLATE utf8_general_ci NOT NULL COMMENT 'A GeoIP adata',
	`ORSZÁG_M`	varchar(60) COLLATE utf8_general_ci NOT NULL COMMENT 'Magyarul',
	PRIMARY KEY (`OKULCS`)
) ENGINE=MyISAM COLLATE=utf8_general_ci COMMENT='A Geoip országnevek magyarul';
# ----------------------------------------------------------------
# Tábla: geoip_terület (4 oszlop)
CREATE TABLE `geoip_terület` (
	`OKULCS`	varchar(2) COLLATE utf8_general_ci NOT NULL COMMENT 'A GeoIP adata',
	`TKULCS`	varchar(2) COLLATE utf8_general_ci NOT NULL COMMENT 'A Geoip területkulcsa',
	`TERÜLET`	varchar(60) COLLATE utf8_general_ci NOT NULL COMMENT 'A GeoIP adata',
	`TERÜLET_M`	varchar(60) COLLATE utf8_general_ci NOT NULL COMMENT 'Magyarul',
	UNIQUE KEY `OKULCS` (`OKULCS`, `TKULCS`)
) ENGINE=MyISAM COLLATE=utf8_general_ci COMMENT='A Geoip területnevek magyarul';
# ----------------------------------------------------------------
# Tábla: geoip_város (4 oszlop)
CREATE TABLE `geoip_város` (
	`OKULCS`	varchar(2) COLLATE utf8_general_ci NOT NULL COMMENT 'A GeoIP adata',
	`TKULCS`	varchar(2) COLLATE utf8_general_ci NOT NULL COMMENT 'A GeoIP adata',
	`VÁROS`	varchar(45) COLLATE utf8_general_ci NOT NULL COMMENT 'A GeoIP adata',
	`VÁROS_M`	varchar(100) COLLATE utf8_general_ci NOT NULL COMMENT 'Magyarul',
	UNIQUE KEY `OKULCS` (`OKULCS`, `TKULCS`, `VÁROS`)
) ENGINE=MyISAM COLLATE=utf8_general_ci COMMENT='A Geoip városnevek magyarul';
# ----------------------------------------------------------------
# Tábla: lap (4 oszlop)
CREATE TABLE `lap` (
	`LAPKULCS`	smallint(3) unsigned NOT NULL,
	`NYELVKULCS`	tinyint(1) unsigned NOT NULL default '1' COMMENT 'A lap nyelvkulcsa',
	`SZÉPNÉV`	varchar(20) COLLATE utf8_general_ci NOT NULL COMMENT 'A lap szép neve',
	`LAP`	varchar(60) COLLATE utf8_general_ci NOT NULL COMMENT 'Útvonal a főkönyvtárból kiindulva',
	UNIQUE KEY `LAPKULCS` (`LAPKULCS`, `NYELVKULCS`),
	KEY `LAP` (`LAP`)
) ENGINE=MyISAM COLLATE=utf8_general_ci COMMENT='A honlap lapjainak kulcsa és állománya';
# ----------------------------------------------------------------
# Tábla: nyelv (3 oszlop)
CREATE TABLE `nyelv` (
	`NYELVKULCS`	tinyint(1) unsigned NOT NULL auto_increment COMMENT 'A nyelv kulcsa',
	`NYELV`	varchar(10) COLLATE utf8_general_ci NOT NULL COMMENT 'A nyelv neve',
	`NYELVKULCS2`	varchar(2) COLLATE utf8_general_ci NOT NULL COMMENT 'Kétbetűs nemzetközi, a fejléchez',
	PRIMARY KEY (`NYELVKULCS`)
) ENGINE=MyISAM COLLATE=utf8_general_ci COMMENT='A honlap lapjainak nyelvei' AUTO_INCREMENT=8;
# ----------------------------------------------------------------
# Tábla: látogató (8 oszlop)
CREATE TABLE `látogató` (
	`IDŐ`	datetime NOT NULL,
	`HA`	varchar(60) COLLATE utf8_general_ci NOT NULL COMMENT 'hálóazonosító',
	`KATT`	tinyint(3) unsigned NOT NULL default '1' COMMENT 'A 24 órán belüli letöltések száma',
	`OKULCS`	varchar(2) COLLATE utf8_general_ci NOT NULL COMMENT 'A GeoIP adata',
	`TKULCS`	varchar(2) COLLATE utf8_general_ci NOT NULL COMMENT 'A GeoIP adata',
	`VÁROS`	varchar(45) COLLATE utf8_general_ci NOT NULL COMMENT 'A GeoIP adata',
	`SZÉLESSÉG`	decimal(10,5) NOT NULL default '0.00000' COMMENT 'A GeoIP adata',
	`HOSSZÚSÁG`	decimal(10,5) NOT NULL default '0.00000' COMMENT 'A GeoIP adata',
	KEY `IDŐ` (`IDŐ`),
	KEY `ALKALOM` (`ALKALOM`),
	KEY `OKULCS` (`OKULCS`, `TKULCS`, `VÁROS`),
	KEY `TKULCS` (`TKULCS`),
	KEY `VÁROS` (`VÁROS`)
) ENGINE=MyISAM COLLATE=utf8_general_ci COMMENT='Egy látogató, 24 órán belüli kattintgatásokkal';
# ----------------------------------------------------------------
# Tábla: látogató_lap (9 oszlop)
CREATE TABLE `látogató_lap` (
	`IDŐ`	datetime NOT NULL,
	`HA`	varchar(60) COLLATE utf8_general_ci NOT NULL COMMENT 'hálóazonosító',
	`LAPKULCS`	smallint(3) unsigned NOT NULL COMMENT 'A lekért lap kulcsa',
	`NYELVKULCS`	tinyint(1) unsigned NOT NULL COMMENT 'A nézett lap nyelvkulcsa',
	`OKULCS`	varchar(2) COLLATE utf8_general_ci NOT NULL COMMENT 'A GeoIP adata',
	`TKULCS`	varchar(2) COLLATE utf8_general_ci NOT NULL COMMENT 'A GeoIP adata',
	`VÁROS`	varchar(45) COLLATE utf8_general_ci NOT NULL COMMENT 'A GeoIP adata',
	`SZÉLESSÉG`	decimal(10,5) NOT NULL default '0.00000' COMMENT 'A GeoIP adata',
	`HOSSZÚSÁG`	decimal(10,5) NOT NULL default '0.00000' COMMENT 'A GeoIP adata',
	KEY `IDŐ` (`IDŐ`),
	KEY `LAPKULCS` (`LAPKULCS`, `NYELVKULCS`),
	KEY `NYELVKULCS` (`NYELVKULCS`),
	KEY `OKULCS` (`OKULCS`, `TKULCS`, `VÁROS`),
	KEY `TKULCS` (`TKULCS`),
	KEY `VÁROS` (`VÁROS`)
) ENGINE=MyISAM COLLATE=utf8_general_ci COMMENT='Egy kattintás, lap- és nyelvadatokkal';
# ----------------------------------------------------------------


SELECT COUNT(látogató.*)      AS LÁTOGATÓ,
      SUM(látogató.KATT)	      AS KATT,
      látogató.VÁROS      AS VÁROS,
      geoip_város.VÁROS_M      AS VÁROS_M
FROM      látogató
LEFT JOIN  geoip_város USING (OKULCS, TKULCS, VÁROS)
GROUP BY      VÁROS
ORDER BY      KATT DESC, VÁROS_M


SELECT      COUNT(látogató_lap.*)      AS KATT,
		CONCAT(
			látogató_lap.LAPKULCS,
			látogató_lap.NYELVKULCS
			)      AS LAPAZONOSÍTÓ,
      lap.SZÉPNÉV      AS SZÉPNÉV,
      nyelv.NYELV      AS NYELV
FROM      látogató_lap
LEFT JOIN      lap USING (LAPKULCS)
LEFT JOIN      nyelv USING (NYELVKULCS)
GROUP BY      LAPAZONOSÍTÓ
ORDER BY      KATT DESC, SZÉPNÉV
A látogatóról készül egy bejegyzés a "látogató" táblába. Amíg 24 órán belül újra kattintgat, csak az adott sor KATT oszlopa értékét növelem. Minden egyes kattintásával készül azonban egy-egy új sor a "látogató_lap" táblába is.

A két fentebbi lekérdezés eddig rendben is van, mert működnek a "látogató" és "látogató_lap" táblák mutatói [INDEX].

Viszont:
1. Egy látogató átlag 4× kattint. 4 sor a "látogató_lap" táblában - UGYANAZON FÖLDRAJZI ADATOKKAL.
2. +1 további sor is lesz a "látogató" táblában - UGYANAZON FÖLDRAJZI ADATOKKAL.

A fölösleges adatismétlés követelményének azzal tehetnék legszebben eleget, ha mindkét látogatótáblából eltüntetném mind a hálóazonosítót, mind az összes földrajzi adatot. Helyettük mindkettő tábla adott sorai kapnának egy (ugyanolyan) véletlenszám látogatás-azonosítót, s ezzel az azonosítóval készülne EGYETLEN sor egy külön táblában (mondjuk "látogatás"), mely tartalmazza eme adatokat. A lekérdezésben fűzéssel [JOIN] kapnám meg őket. (Vagy, ésszerűbben: csak a "látogató"-ban hagynám meg eme adatokat, s onnan fűzném a "látogató_lap"-hoz.)

Itt jön, amit nem értek. A jelen felállásban a mutatóim megfelelőek. De ha kiviszem az országkulcsot, városnevet, stb. az új "látogatás" táblába, akkor hogyan tudok ehhez mutatókat készíteni a "látogató" és "látogató_lap" táblákban? Hiszen ott nem lesznek ilyen oszlopok! Márpedig mutatók kellenek, különben akár szunyálhatok egyet, míg fut a lekérdezés.

Mivel ez alapvető(nek tűnik számomra), gondolom, a megoldás is az - csak én nem ismerem.
‹ MySQL eredmény sorokból HTML table egymás utáni oszlopokban sorokból oszlopok Szerver architektúra tervezés ›
 ■
1

Denormalizáció

Poetro · 2011. Jún. 15. (Sze), 12.25
A teljesítmény javításának az egyik formája a denormalizáció. Ez azt takarja, hogy a normálformához képest egyes adatokat duplikálunk, így tudunk javítani a teljesítményen. Alapvetően jó ötlet az adatbázisunkat harmadik vagy még magasabb normálformára hozni, de a teljesítménybeli igény néha megköveteli, hogy eltérjünk ezektől.
Idézet a Microsoft normalizálásáról szóló cikkből:
KIVÉTEL: Bár a harmadik normálforma használata elméletileg mindig szükséges, a gyakorlatban nem minden esetben célravezető. Ha a Vevők táblában például meg szeretné szüntetni a mezők közötti összes lehetséges függőséget, külön táblát kell létrehoznia a városok, az irányítószámok, az értékesítési képviselők, a vevőosztályok és minden, a rekordok között esetlegesen ismétlődő tényező számára. A normalizálás elméletben minden esetben hasznos. A sok kis tábla kezelése azonban csökkentheti a teljesítményt, és a sok megnyitott fájl feleslegesen foglalhatja a memóriát.
2

Jó hír - ezek szerint még sem

laji · 2011. Jún. 15. (Sze), 12.33
Jó hír - ezek szerint még sem én vagyok annyira tájékozatlan. :)

Másrészt kár, hogy nincs megoldás. Így kb. 3-szorosa az adattár mérete annak, amit el szerettem volna érni. Évi 20-30 MB a kimutatás szükséglete, ezt gondoltam leszorítani 10-re.
3

csatlakozom

joed · 2011. Jún. 16. (Cs), 09.35
Csatlakozom Poetro kollégához. Noha egyes okító intézményekben szeretnek 6., 7. századik normálformáig optimalizált sémát erőltetni, de tapasztalatból mondom, hogy ez az eset az, ahol a kevesebb több. Szóval szerintem is denormalizáció.
5

off: (oktató intézményekhez):

solkprog · 2011. Jún. 16. (Cs), 11.49
oktató intézményekhez hozzászólva: én anno zh előtt közöltem a tanárommal, hogy a papíromon ne keresse majd az első és második normálformát, mert kapásból a harmadikat alakot fogom felírni. -hát hogy is mondjam nem tetszett neki (mert hogy "azt úgy nem lehet, sorban le kell vezetni". majd kiegyeztünk abban hogy oké, vagy max pontot kapok vagy 0-át.)
4

Köszönöm a válaszokat

laji · 2011. Jún. 16. (Cs), 11.27
...meg a türelmet a hosszú leírásom végigolvasásához. :)