Van egy ilyesmi tablam:
MariaDB [clapf]> show create table token;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| token | CREATE TABLE `token` (
`token` bigint(20) unsigned NOT NULL,
`uid` smallint(5) unsigned NOT NULL DEFAULT 0,
`nham` int(11) DEFAULT 0,
`nspam` int(11) DEFAULT 0,
`timestamp` int(10) unsigned DEFAULT 0,
KEY `token_idx1` (`token`),
KEY `token_idx2` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
A 10.2-es mariadb hoszton az alabbi select explain eredmenyt kapom:
MariaDB [clapf]> explain SELECT token, nham, nspam FROM token WHERE token in (13036551635545399192,5805177638834534979,5887110958975549203,48491888334302022) and uid=0;
+------+-------------+-------+-------+-----------------------+------------+---------+------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+-----------------------+------------+---------+------+------+------------------------------------+
| 1 | SIMPLE | token | range | token_idx1,token_idx2 | token_idx1 | 8 | NULL | 4 | Using index condition; Using where |
+------+-------------+-------+-------+-----------------------+------------+---------+------+------+------------------------------------+
1 row in set (0.00 sec)
A db-t kidumpoltam, attettem egy 10.3-as mariadb hosztra, ami viszont ugyanerre select-re egeszen mast ad vissza:
MariaDB [clapf]> explain SELECT token, nham, nspam FROM token WHERE token in (13036551635545399192,5805177638834534979,5887110958975549203,48491888334302022) and uid=0;
+------+-------------+-------+------+-----------------------+------------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+-----------------------+------------+---------+-------+--------+-------------+
| 1 | SIMPLE | token | ref | token_idx1,token_idx2 | token_idx2 | 2 | const | 837534 | Using where |
+------+-------------+-------+------+-----------------------+------------+---------+-------+--------+-------------+
Ennek megfeleloen (a full table scan-nek hala) sokkal tovabb tart, mig a select eredmenyet megkapom.
Meg annyi erdekesseg, hogy a 10.2 es 10.3 optimizer_switch valtozoja csak abban ter el, hogy 10.3-on meg a split_materialized=on is benne van.
A show index from token mindket helyen ugyanazt adja vissza:
show index from token;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| token | 1 | token_idx1 | 1 | token | A | 1675388 | NULL | NULL | | BTREE | | |
| token | 1 | token_idx2 | 1 | uid | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
A kerdesem ezek utan csak annyi, hogy wtf?
- 1718 megtekintés
Hozzászólások
meg annyi update, hogy a 10.2 xenial-on van, a 10.3 meg bionic-on. Banatomban feldobtam 1 xenial-t 10.2-vel, es ott is pont olyan fos (=az explain ugyanazt adja vissza, mint 10.3+bionic-on), szoval valami nagy arulas lesz itt valahol...
--
"dolgozni mar reg nem akarok" - HZuid_7086 'iddqd' zoli berserk mode-ba kapcsol
- A hozzászóláshoz be kell jelentkezni
Rendszer/session szinten memoria es buffer limitek ugyanazok?
Analyze -al futtatva a 2 rendszerben a query-t mit mond?
Ha kikapcsolod optimizer-bol az extra parametert valtoztat az eredmenyen?
Egyebkent token_idx2 indexnek nem latom ertelmet cardinality 2-vel, talan tobbet hasznalna osszevonnad a masik indexel (token,uid).
Esetleg atirhatod a query IN reszet temp tablaba vagy sub select-be es ahhoz join-al keresve, bar ez nem olyan hosszu lista.
- A hozzászóláshoz be kell jelentkezni
probaltam osszetett indexet is, nem segitett. Probaltam kivenni az extra parametert, nem segitett. Rohogni fogsz, de xenial + 10.2.16 egy hetzner vps-en gagyi eredmenyt ad, ld. fentebb. Viszont a xenial 10.2.16 egy vultr vps-en mar hasznalja az indexet ugyanannal a query-nel.
A kerdes tehat az, hogy lehet ravenni a mariadb-t, hogy a "select a,b,c from xxx where d in (1,2,3,4) query eseten hasznalja a d-bol keszitett indexet?
Szoval ez a mereg, nem a cian:
MariaDB [aaa]> explain extended select token, nham, nspam FROM token WHERE token in (1,2,3) ;
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | token | ALL | token_idx1 | NULL | NULL | NULL | 5 | 60.00 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
MariaDB [aaa]> show index from token;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| token | 1 | token_idx1 | 1 | token | A | 5 | NULL | NULL | | BTREE | | |
| token | 1 | token_idx2 | 1 | uid | A | 5 | NULL | NULL | | BTREE | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
Btw. a temp table-be iras nem jatszik, mert sok query mehet, akar parhuzamosan is.
--
"dolgozni mar reg nem akarok" - HZuid_7086 'iddqd' zoli berserk mode-ba kapcsol
- A hozzászóláshoz be kell jelentkezni
Lehet kenyszeriteni index hasznalatot ha annyira azt akarod:
https://mariadb.com/kb/en/library/index-hints-how-to-force-query-plans/
De jo esellyel okkal dont ugy az optimizer, egyebkent meg ugyanazokkal az adathalmazokkal teszteld (legalabbis ez utobbin latszik hogy teljesen mas meretek, tippre 5 ertek van benne amibol 3-hoz tartozo adatokat akarna kikeresni nyilvan le fogja tojni az indexet mert gyorsabb ilyenkor neki egy full table scan-t tolni.
- A hozzászóláshoz be kell jelentkezni
az index force-olasaval egybol mas a leanyzo fekvese, de amint lentebb is irtam, ~1 millio rekordnal en az optimizer helyeben mar belekukkantanek az indexekbe is.
De lehet, hogy vissza kene menni a kalyhahoz, es a tablat ill.a query-t ujratervezni. Szoval egy spamszuro db backend-jerol van szo, ahol a 'token' egy unsigned bigint (unique ertek*), van mellette 2 counter (=integer) nham es nspam. Tovabba multiuser-es kornyezetre is gondolva van minden rekordnal egy uid (=integer) oszlop. Nalam minden rekordnal uid=0 szerepel, de mashol lehet uid>0 ertek is, pl.
token | uid | nham | nspam
------+-----+------+-------
1 | 0 | 15 | 0
2 | 0 | 2 | 1
1 | 1 | 0 | 2
... ...
Szoval egy ilyesmi tablabol kene lekerdezni tobb szaz tokent (a level meretevel aranyos a tokenek szama). ha 1-esevel kerdeznem le, akkor egybol ott lenne a token oszlopra keszitett index, de ez egy elemszam folott nem szep, igy 1 lepesben akartam eddig lekerdezni kb.
select token, nham, nspam from tablename where token in (1,2,3,......,2588);
ami ugy tunik, 2018-ban mar nem koser a mysql szamara. Hogyan modositsam akar a tablat, akar a query-t, vagy akar a mysql demont (:-))?
--
"dolgozni mar reg nem akarok" - HZuid_7086 'iddqd' zoli berserk mode-ba kapcsol
- A hozzászóláshoz be kell jelentkezni
"Szoval egy ilyesmi tablabol kene lekerdezni tobb szaz tokent"
Mi alapjan dontod el, hogy melyik tokeneket kellene lekerdezni, levelazonosito, datum vagy mi donti el hogy melyik tokeneket kered le?
Ha jol ertem 1 levelhez tartozik tobb ilyen token is es ha jol tippelek az 1 levelhez tartozokat akarod lekerdezni, akkor szamomra az lenne logikus hogy letarolni a level azonositot a tokenekhez es mail_id (vagy mail_id,token ha kulon tokeneket is akarsz kikeresni) alapon indexelni meg keresni benne.
- A hozzászóláshoz be kell jelentkezni
Mi alapjan dontod el, hogy melyik tokeneket kellene lekerdezni,
token = szo a levelben (valojaban a szobol kepzett hash ertek)
akkor szamomra az lenne logikus hogy letarolni a level azonositot a tokenekhez es mail_id
nem, ez igy total ertelmetlen lenne. Ugy kell megoldani, hogy az osszes token 1 tablaban legyen.
--
"dolgozni mar reg nem akarok" - HZuid_7086 'iddqd' zoli berserk mode-ba kapcsol
- A hozzászóláshoz be kell jelentkezni
Ha ez nem alavult info: https://mariadb.com/kb/en/library/engine-independent-table-statistics/
akkor a maria a statisztikai táblák tartalmát eltérő politikák mentén használhatja.
Ha elavultak a statisztikák (kis rekordhalmazt tükröznek), és arra támaszkodva dönt az optimalizáló a lekérdezés módjáról, akkor aszondja, hogy "ebben a táblában alig van valami, nem is érdemes miatta az indexhez fordulni, hiszen a blokkos olvasás miatt úgyis be fogom nyalni a nagyját. Kevesebb IO behozni az egészet, mint az indexet is és a fát bejárni".
Valószínűleg létezik az a helyzet, amikor hasonlót eredményez friss statisztika mellett a statisztikák (relatív) ignorálása.
(diszklémer: még csak nem is ugatom a mariadb-t.)
- A hozzászóláshoz be kell jelentkezni
mysql-nel egy alter table; parancs regeneralta az indexeket es az optimizer stat-okat. lehet, hogy erdemes lenne kiprobalni.
- A hozzászóláshoz be kell jelentkezni
a topikinditoban kb. 1 millio rekord szerepel, ahol ~100-200 token lekerdezese is jo fel percig tart. Szoval van benne azert adat.
--
"dolgozni mar reg nem akarok" - HZuid_7086 'iddqd' zoli berserk mode-ba kapcsol
- A hozzászóláshoz be kell jelentkezni
Az a kérdés, hogy az optimalizáló is tud-e arról, hogy annyi rekord van benne.
Ami neked egy trivi select count(*) az az optimalizálónak lehet egy attól szignifikánsan eltérő N.
A marisnál lényegesen drágább motornál is külön művelet a statisztikák időszakos frissítése, amit érdemes végrehajtani a kardinalitás(oka)t lényegesen befolyásoló műveletek után.
- A hozzászóláshoz be kell jelentkezni
Az a kérdés, hogy az optimalizáló is tud-e arról, hogy annyi rekord van benne.
a jelenseget sem ertem: hogy nem tudhat arrol az optimizer, hogy mi alapjan dont?
--
"dolgozni mar reg nem akarok" - HZuid_7086 'iddqd' zoli berserk mode-ba kapcsol
- A hozzászóláshoz be kell jelentkezni
Az optimalizáló azt látja, ami a statisztikákat gyűjtő táblákban van - ami viszont eltérhet attól, ami azokban az adat- és indextáblákban van, amelyekről a statisztikák szólnak.
Annyira eltérhet, hogy lehetőséged van a táblatartalmaktól függetlenül bármit beírni a statisztikai táblákba, ami arra jó, hogy olyan rekordhalmazzal is tudod tesztelni a végrehajtást, amilyened nincs.
- A hozzászóláshoz be kell jelentkezni
ok, mariadb-nel pontosan mit nezzek?
--
"dolgozni mar reg nem akarok" - HZuid_7086 'iddqd' zoli berserk mode-ba kapcsol
- A hozzászóláshoz be kell jelentkezni