mariadb 10.2 vs. 10.3 explain wtf?

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?

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

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.

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

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.

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

"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.

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

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.)

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.

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.