здравейте, забелязал съм следния проблем с локване на таблиците в MySQL.

за да не навлизам в подробности и същностите в моя случай ще опиша с един бърз пример, при който релацията е същата:

например: имаме отдели, служители и инвентар (да речем). релациите са 1 към много навсякъде (т.е. служител има отдел-ИД, инвентар има служител-ИД)

ако пусна следната SQL заявка, която да извади инвентара от даден отдел (т.е. принадлежащ на служителите в даден отдел) ще имаме следното:

select inventory.* from inventory
left join employee on inventory.employee_id=employee.employee_id
where employee.department_id=123

това търсещо в JOIN-натата таблица по поле установих че заключва таблиците (поне в MySQL 3.xx). Става въпрос за случаите когато таблицата invenotory е вече със стотици хиляди редове (тогава се забелязва) особено ако почнат повече юзъри да ръчкат по справката.

а в този момент в inventory и employee (в примера, който съм дал) не може да се прави нищо (insert/update/delete/etc).

решението е ясно за мен - в случая с примера - слагам department_id и в invenotory и малко тъпичко, но иначе заявката върви без да локва таблиците (така става само върху една таблица).

някой да може да даде съвет в такива случаи как е по-правилно да се процедира и по принцип, дали така разрешението е ОК, или да се търси чрез друг вид SQL заявки да се заобиколи ?

аз съм детенце, което чете от книжки и там пише, че така прави при myisam таблици. твоите такива ли са?

баш такива ;) съшите.

а какви таблици да използвам тогава. аз си обичам и предпочитам MyISAM таблиците. а и какви са тези книжки, които четеш ?

Ако можеш мини на MySQL 5 и използвай таблици от тип InnoDB, вместо MyISAM - така ще имаш възможност да заключваш поредово, а не цялата таблица, което в твоя случай ще реши проблема.

Everything louder than everything else...

мога. но ще е голямо приключение да мигрирам всичко на MySQL 5.

иначе когато едно време тествах с InnoDB забелязах че таблиците стават значително по-големи по размер.

така или иначе съм намерил едно решение, а ми хрумна и второ евентуално ... та ще си остана на MySQL 3.

Какъв е смисъла от тази заявка, че нещо ми убягва

select inventory.* from inventory
left join employee on inventory.employee_id=employee.employee_id
where employee.department_id=123

За какво точно ти е LEFT JOIN в случая, че нещо не сващам?

А иначе браво за откритието! Нарича се "топла вода" т.е. това дето MyISAM винаги заключва цялата таблица. Пише го не само в книгите ами дори и в такива простички неща като ръководството на MySQL

Съдържаниет е скрито
Влезте за да го видите

Може да струва усилия, но мисля че си струва (минаването на 5-та версия). Ще имаш допълнителни удобства, а и рано или късно ще преминеш някой ден. Защо да не е по-навреме? MySQL v3 си е ретро-дърво, влез малко от малко в пътя :)
И докато "мигрираш" ще ти препоръчам покрай другите мизерии да минеш на UTF-8.

Като цяло миграция към InnoDB е най доброто което можеш да направиш. Не е нужно да мигрираш цялата база ако те притеснява размера на базата. Можеш да мигрираш само част от таблиците - ALTER TABLE <table_name> TYPE=INNODB; При големи таблици това може да отнеме доста време така че ако ще го правиш на production сървър по добре си създай нова таблица INNODB и копирай данните в нея на групи от по 1000-2000, че да избегнеш locks.

Ако не ти се занимава с ugrade на първо място обаче мисля че е добре да пробваш да оптимизираш заявките и да пипнеш малко индексите на таблиците и настройките на MySQL. Дори и при няколко милиона записа няма да имаш проблем да вадиш данните който ти трябват без да имаш проблеми с locks.
1. Сложи индекси на полетата по който търсиш - в случая ти трябват индекси на inventory.employee_id и employee.department_id . Пусни EXPLAIN за да видиш какви индекси използват заявките
2. MySQL сам си оптимизира заявките но за аз лично тази заявка бих я написал като

select inventory.* from employee
left join inventory on inventory.employee_id=employee.employee_id
where employee.department_id=123

Така или иначе първо се прави търсенето в employee и после вадиш записите от inventory

3. Поразгледай малко настройките на MySQL - има няколко книги на Jeremy Zawodny за оптимизация. Ако можеш вдигни размера на
key_buffer_size, read_buffer_size, read_rnd_buffer_size, sort_buffer_size като внимавай да не изполваш повече памет отколкото имаш. Идеалния вариант е размера на key_buffer_size да е колкото размера на всички индекс (MYI) файлове

select inventory.* from inventory
left join employee on inventory.employee_id=employee.employee_id
where employee.department_id=123

е всъщност

select inventory.* from inventory
where inventory.employee_id=employee.employee_id and employee.department_id=123

в онлайн документациите пише, че MySQL сам си преобразува LEFT JOIN в WHERE клауза.

аз обаче съм свикнал да си ги записвам с LEFT JOIN.

за повече инфо:

Съдържаниет е скрито
Влезте за да го видите

мерси много за съветите, ще погледна настройките.
колкото до InnoDB таблиците въпроса е малко принципен, че съм свикнал с MyISAM, но явно ще трябва да си надвия над ината и да седна да поекспериментирам с InnoDB (ще се наложи май да преправя някои полета и т.н. че по-инерция му давам VARCHAR винаги 255 в MyISAM).

иначе примерната заявка която ми даде (с разменените места) я пуснах върху тестова таблица само с 415 реда (в примера налогията с inventory). и ми върна 424 (9 реда в повече с всичките полета NULL, NULL, NULL и т.н.)

но както и да е. мерси много за останалите съвети с настройките, ще седна да попрочета нещо по въпроса и да тествам, че това са опции, за които нищичко не знам, а така като гледам ще ми е доста интересно да понауча повече.

InnoDB има и в MySQL след 3.23.34a, така че може да не ти се наложи да ъпгрейдваш много.

Колкото до заявката - не се бях сетил за тоя случай . Можеш да направиш заявката

select inventory.* from employee
left join inventory on inventory.employee_id=employee.employee_id
where employee.department_id=123 and inventory.employee_id is not NULL

Ако имаш индекс на inventory.employee_id няма да има почти никакво забавяне. Все пак използвай EXPLAIN за да видиш как точно ще сте изпълни заявкатa.

ако е с where - няма да имаш полетата с NULL.
Ако си с left joint, трябва допълнително - where my_field IS NOT NULL

Bеer? Mоre?

Съдържаниет е скрито
Влезте за да го видите

... но съществено откъм бързодействие


"При големи таблици това може да отнеме доста време така че ... по добре си създай нова таблица INNODB и копирай данните в нея на групи от по 1000-2000, че да избегнеш locks."


INSERT INTO innodb_tbl SELECT * FROM myisam_tbl ORDER BY <Primary_Key>;

Скоростта на INSERT в InnoDB много силно се влияе от реда на вмъкване. Най-бързо е ако редовете са подредени според първичния ключ. Ако са разбъркани може да стане мнаго по-бавно. Виждал съм да се бави да 15 пъти! При големи таблици разликана може да е между няколко дена и няколко часа така, че си заслужава да се има предвид

(С малко четене на документацията е очевидно защо има такава зависимост)

Съдържаниет е скрито
Влезте за да го видите

Не е много добра идея да разчиташ на оптимицатола да преобразува заявките.

Разликата между LEFT (OUTER) и обикновения (INNER) е съществена както явно знаеш и просто не е здравословно да използваш не това което ти трябва.

ОЩЕ повече ако минеш на InnoDB

InnoDB заключва както на ниво ред така и на ниво таблица.

Когато заявката се опитва да заключва отделни редове и те станат "много" InnoDB започва да изпитва проблеми.

В определени ситуации можеш много бързо да се убедиш, че заключването на цяла таблица както при MyISAM може да е многократно по-бързо от заключването на Всички Редове както може да се случи в InnoDB.

Особено с LEFT JOIN. При него както знаеш лявата таблица се претърсва цялата.

Съдържаниет е скрито
Влезте за да го видите

Всъщност това което предложих беше да пусне един цикъл от сорта на

for(x=0;x<[num_rows];x+=1000){
INSERT INTO innodb_tbl SELECT * FROM myisam_tbl ORDER BY <Primary_Key> LIMIT x,x+1000;
}

Така ще избегне локването на цялата таблица за времето на копирането. Това разбира се работи само при положение че някой междувременно не променя по старите записи. Иначе забавянето при INSERT идва основно от индексите, така ако се търси скорост на копиране на данните е по-добре новата таблица да е със спряни индекси:
ALTER TABLE new_table DISABLE KEYS;
INSERT ...;
ALTER TABLE new_table ENABLE KEYS;

"Особено с LEFT JOIN. При него както знаеш лявата таблица се претърсва цялата."

Хъм - тук не си много прав. MySQL прави full table scan при Left JOIN само ако няма индекс който да ползва в ON или в WHERE клаузите.

http://dev.mysql.com/doc/refman/4.1/en/how-to-avoid-table-scan.html

Е очевидно не се изразих съвсем ясно.

Независимо дали търси само в индекс или не накрая в резултатите трябва да се изведат колонките от всички редове в таблицата.

Да ако всичите изброени колонки са част от индекс стойностите могат да се извадят от индекса, но пак са всички.

И разбира се с уговорката, че зависи от това какво точно има във WHERE клаузата де. Виждал съм "сериозни" продукти които изпращат огромни заявки с по много закаечни таблици и куп сложнотии и накрая на 50-тия ред:

... WHERE .... t1.id = t2.id AND t1.id = 1 AND t2.id = 0 ...

Да питаш после що са му плащали заплата на този дето го е писал

Съдържаниет е скрито
Влезте за да го видите

"Това разбира се работи само при положение че някой междувременно не променя по старите записи."

т.е. цялото ти предложение би трябвало да изглежда ей така:

LOCK TABLES myisam_tbl READ;

for(x=0;x<[num_rows];x+=1000){
INSERT INTO innodb_tbl SELECT * FROM myisam_tbl ORDER BY <Primary_Key> LIMIT x,x+1000;
}

UNLOCK TABLES;



С което цялото упражнение става малко безсмислено защото крайния ефект е пак като на единичен INSERT без цикъл.


ALTER TABLE new_table DISABLE KEYS;

Пропускаш факта, че DISABLE / ENABLE KEYS работи само с MyISAM

Съдържаниет е скрито
Влезте за да го видите