[megoldva] MySQL / delete from / Miért lassú?

Adatbázis: SQL, XML DB

Sziasztok,

Tudnátok segíteni? Miért lassú az alábbi MySQL parancs?

Többb mint 4 másodpercig fut egy modern gépen, ahol semmi más művelet nincs - többször futtatva is:

delete from table1 where column1 like 'pattern1%' and column2 in (select column3 from table2 where column4='pattern2')

table1 kb. 1100 recordot tartalmaz, ebből 95% a column1 / pattern1% találat.

table2 kb. 5000 recordot tartalmaz, ebből közel nulla a column4 / pattern2 találat.

Host: MySQL v5.7, Ubuntu 16 64 bit, gyors SSD, 1GB az InnoDB pool mem

Lehetne vajon a fentit egyszerűbben vagy gyorsabban? Köszönöm.

Szerk.: (kicsit olvashatóbb forma):

DELETE FROM t1 WHERE c1 LIKE 'p1%' AND c2 IN (SELECT c3 FROM t2 WHERE c4='p2')

Szerk2.: nem tudtom hogy ilyen gyorsan válaszoltok, amit köszönök, ezért indítottam ezelőtt egy SO fórum szálat is, beteszem ide, hátha segít:
http://stackoverflow.com/questions/40702486/mysql-delete-from-why-is-it…

Szerk.: Végül megoldottam index hozzáadásával a tábla2 column3 oszlopához, így nulla idő alatt fut.

  • 1941 megtekintés

( vl v | 2016. 11. 20., v – 10:25 )

1. kérdés: ugyanez selectben, ami csak előállítja a kérdéses sorok id-ját, mennyi idő alatt fut le? (ugye ennél gyorsabb a delete sem tud lenni)
2. kérdés: ha elértük, hogy a select jó gyors legyen (a full table scan nem a barátja a gyors selectnek sem), és a delete még mindig lassú: hány sort szeretnél törölni? ha sokat, akkor az lassú lesz. az rdbms-ek már csak ilyenek...

( blackluck v | 2016. 11. 20., v – 10:28 )

Elso korben szerintem masolj be explain delete..., show create table

es my.cnf tartalom (legalabbis innodb es bufferekre vonatkozolag) kimeneteket. Akkor talan tobbet lehetne latni.
Egyebkent szebb lenne join-ra atirva a query.

Első felindulásra (tesztelés nélkül): 

DELETE FROM table1
JOIN table2 ON (table1.column2=table2.column3 AND table2.column4='pattern2')
WHERE table1.column1 LIKE 'pattern1%'

A tévedés jogát természetesen fenntartom, szükség esetén blackluck kollega majd kijavít ;)

De hogy teljes legyen a kép, a "talán gyorsabb is"-ra forrás: mysql-doc, részlet:

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better

explain delete...: 


+----+--------------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | DELETE             | table1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1179 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | table2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4601 |     1.00 | Using where |
+----+--------------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

show create table-ben semmit extra, utf8-as InnoDB pár oszloppal.

my.cnf custom része:

innodb_buffer_pool_size = 1G
query_cache_type = 1

( dszakal | 2016. 11. 20., v – 10:36 )

Ne csinalj ilyet.

DELETE FROM az csak IN (primary_key_value1, primary_key_value2, ...(stb.)) feltetellel toroljon tobb sort.
(UPDATE SET is ;) )

Mindenkepp csinalj elotte egy SELECT-et, foleg, ha LIKE query van, amin egy index se segit eleget. Egyeb esetben hosszu masodpercekre lelockolod az egesz tablat ezzel az egesz rendszert feltartva (a "mi mysql-unk" (5.6, Oracle-fele) legalabbis igy viselkedik).

Tehat:

1. query (nem lockol mert csak read):

SELECT DISTINCT table1_id FROM table1 where column1 like 'pattern1%' and column2 in (select column3 from table2 where column4='pattern2')

2. query:

DELETE FROM table1 WHERE table1_id IN ($vesszovelElvalasztvaElozoQueryEredmenyei)

(ez utobbi 10000-nel tobb sort ne toroljon, ugy mar tapasztalatom szerint tobbet arthat mint hasznal a sebessegnek.

AutoDb nelkul is megvan mondjuk 10-15 sorban amit eloszor javasoltam. A SELECT mint fentebb emlitettek akar gyorsabb is lehet JOINokkal, de nehez kontrollalni a MySQL-t, mert a query plan (EXPLAIN) read-only :(

(AutoDb libemben 3 sor amugy: 


$autoDb = AutoDb::init($mysqli_object);
$rowsToDelete = $autoDb->rowsArray('tabla1', "column1 like 'pattern1%' and column2 in (select column3 from table2 where column4='pattern2')");
AutoRecord::deleteMore($rowsToDelete);

{Hol vannak a joinok? Joinokra egy masik (egyelore nem open) libemet hasznalom, leven a joinolt adat az nekem read only, vagy veszelyes vagy "irj sajat query-t, ugyis gyorsabb" kategorias}
)

Composite primary key sincs? (Mondjuk az is soronkent torlest jelentene, vagy egy nagy kigeneralt (... AND ...) OR (... AND ...) tengert jelentene). Valami index amire lehet szukiteni, vagy valami amit nem csak LIKE-kal lehet query-zni es kelloen egyertelmu? Lenyeg hogy aminek a leggyorsabb a SELECT-je, azt add at a DELETE-nek.

for the future: MySQL-t meg nem javaslom PRIMARY KEY nelkul hasznalni. Nagyon nem. Lattam mar par query-t az elmult evekben, amit egy auto_incrementes (big)int PRIMARY KEY nelkul eselytelen lett volna olyan gyorsra meginrom amilyen gyorsan most fut. (EXPLAIN ugye read only, de PRIMARY KEY olvasast altalaban eloreveszi a rendszer)

Millio ok van ra, pelda:

40 millio soros tabla, toroljunk mindent ami regebbi egy X datumnal (egyszeruseg kedveert limit 1000)

1. SELECT FROM
majd
2. DELETE IN (primary key-ek a SELECT-bol)
(vagy akar UPDATE)

Itt az 1-es query tobb masodperc, de csak read, nem lockol semmit. LIKE query eseten akar percekig is eltarthat a dolog
A 2-es meg nehany millisec (es csak arra a rovid idore lockolja a tablat, ergo a mogotte allo site mukodokepes marad).

Primary key nelkul ez egyetlen query - kenytelen vagy percekre lockolni a DB-t amig a DELETE FROM lefut
DELETE FROM WHERE ... LIKE ...

Az mar regen rossz ha ez utobbira ra vagy kenyszerulve, es az egesz alkalmazasban globalis lelassulast fog okozni. Es ez csak egyre rosszzabb lesz. Tedd be azokat a primary key-eket, amig 10000 sor alatt vannak a tablaid. Mondjuk kezdeskeppen mindenhova. :)

( gabrielakos v | 2016. 11. 21., h – 00:01 )

c4-re van a vizsgálat, arra (is) kéne az index, nem?
Amúgy én az AND két felét is megfordítanám, a mostani második rész szelektívebb, célszerű azt előre rakni.

--
Gábriel Ákos
http://ixenit.com