SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 27.10.2007 18:05:45

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Процедура разбора таблицы

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

Пока что я имею алгоритм в голове, намереваюсь начать писать, поэтому хотелось бы выслушать  мнения и идеи других.

основная идея:
После появления views стало возможным без вмешательства в софт, который использует некую таблицу разбить её на справочники по критериям а саму таблицу собирать обратно в представление с тем же именем, что и исходная таблица, используя join.

Нужно это бывает довольно часто, поскольку ошибки начального проектирования(если оно вообще было) начинают накапливаться при большом увеличении данных. Наприер есть таблица с текстовыми/строковыми полями, среди которых достаточно много повторяющихся значений, которые существенно увеличивают объем и время выборки из таблицы. Впрочем зачем нужны справочники все я думаю знают.
При необходимости ускорить работу сервера БД раньше надо было переделывать таблицу, при этом ессно переписывать софт ее использующий(запросы к БД становятся многотабличными). С использованием view рефакторинг можно проводить двумя независимыми этапами.

Основные требования
1. Процедура должна уметь самостоятельно анализировать необходимость выноса в справочник столбца, например при количество уникальных значений <50% всего набора
2. Процедура должна уметь самостоятельно определять тип данных и делать вынесенный справочник соответствующего типа
3. Если тип данных является неоптимальным - справочник оптимизируется, т.е. тип данных приводится к оптимальному (например varchar -> char)
4. После разнесения процедура создает предствление с именем исходной таблицы в определении которого идет сборка с помощью join

Примерный план работы процедуры

1. Процедура принимает параметром имя обрабатываемой таблицы.
2. По имени таблицы делается выборка из иформационной схемы (или show columns)
3. Цикл по полученной таблице
   3-. если в поле extra видим auto_increment или данный стоблец имеет индекс unique/primary - выходим из цикла, столбец остается на месте.
   3а. запоминаются данные столбца
   3б. анализируется исходный столбец (например procedure analise()?) на основании которого делается новая таблица с префиксированным именем, нпример create table ext_colname (id int auto_increment ...., colname type unique index). Если по результатам анализа количество уникальных значений не превышает заданного порога, например 50% - то выходим из цикла
   3г. из исходной вынимаются данные и вставляются в справочник (insert into ext_colname select from ... group by colname)
   3д. исходная таблица апдейтится - все значения в столбце заменяются на id из справочника
   3е. в массив помещается имя новой таблицы (например во временную таблиу) для последующей сборки представления
4. переименовываем исходную таблицу напрмиер в ext_tname
5. собираем представление со старым именем в "котором собираем" таблицу с разнесенными справочниками
          create view as source_tname SELECT * FROM ext_tname,ext_colname1,ext_colname2... WHERE ext_tname.colname1=ext_colname1.id AND ...


Собсна вот...

у кого какие мысли?

P.S.
1. Собственно все это можно сделать с помощью скриптов - но имхо, вынесение этой логики на сам сервер - оптимальнее.
2. подозреваю, что какие то вещи сделать не получится, именно поэтому написал это ДО реализации, чтобы не тратить время на поиск возможных неизлечимых подводных камней.
3. Изначально подразумевается read-only таблица, хотя можно модумать и о записи, по идее не должно быть проблем.

Отредактированно Shopen (27.10.2007 18:13:55)

Неактивен

 

#2 27.10.2007 18:32:57

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Процедура разбора таблицы

Хорошая идея сделать нормализацию средствами SQL.

Возникли такие мысли

1. нормализация не всегда приводит к улучшению производительности - напротив, часто быстрее работает, если все хранить в одной таблице. Тем не менее, нормализация значительно сокращает количество ошибок, несуразностей и несогласованностей в базе.

2. PROCEDURE ANALYZE() похоже нерабочая вещь, так как она не сочетается с подзапросами
mysql> select * from (select * from rating procedure analyse());
ERROR 1221 (HY000): Incorrect usage of PROCEDURE and subquery

Эту часть придется реализовывать руками, тем не менее не вижу принципиальных проблем.

3. Что оптимальнее - char или varchar зависит от движка и от типа запросов. Может быть стоит сделать параметр OPTIMIZE_FOR у процедуры с двумя значениями PERFORMANCE и  STORAGE.

4. пункт 3д - это сначала создание новой колонки типа int (tmp_int), затем присвоение, удаление старой колонки и переименование tmp_int

5. о записи подумать будет сложно (без изменения записывающих в БД программ), так как VIEW, связанная с несколькими таблицам - необновляема, и на VIEW пока что нельзя повесить триггер

Неактивен

 

#3 27.10.2007 18:51:21

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Процедура разбора таблицы

1. это понятно, что не всегда. Но если нормализация замедляет конкретную задачу - то процедура просто не используется, или используется осознанно для повышения "стройности" базы, даже в ущерб скорости. Это дело выбора и конкретной задачи.

на самом деле очень интересно при каких условиях кроме очевидных нормализация понижает скорость?

очевидно неэффективный пример - выносить в справочник почти неповторяющиеся значения (hits ~80-100%)

вот например у меня база ~3-5млн записей. Среди них большая часть текстово-строковых, причем по ним делается поиск "по слову". Повторы значений могут достигать катастрофически-минимальных значений например из таблицы в 4.5 млн записей - 11000 вариантов. Понятно, что индексы помогут, но во первых я не уверен, что fulltext например не будет на такой таблице хранить ссылки на все строки, и потом эта колонку занимает недопустимо больше места, чем такая же колонка с id (int) и маленькой таблицчкой из 11000 строк.  + такой справочник вообще можно засосать в память и брать данные из нее, что шустрее намного.

2. а зачем нужен подзапрос в данном случае? я же анализирую одну таблицу - analyse нужен только для одного стоблбца (или в начале для всей таблицы сразу, так быстрее, а результат сохранять опять таки во временную таблицу, с которой потом работать)

3. да, это понятно, хотя я пока думаю только об myIsam. по поводу типа запросов не понял - какая разница между char или varchar с точки зрения запроса?

4. отличная идея smile

5. хм.. а мне рассказывали, что view read-only только в случае неоднозначности, а тут она не присутствует, ведь id обновляемойзаписи mysq-ю известен

Отредактированно Shopen (27.10.2007 18:53:44)

Неактивен

 

#4 27.10.2007 19:09:03

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Процедура разбора таблицы

1. нормализация замедляет, например, во всех случаях, когда выборка производится не по выносимому полю. Скажем, все запросы к таблице City или по имени города или по Population. В таком случае нормализация по полю CountryCode  замедляет запросы, так как требует подключение дополнительной таблице. Однако не всегда, например, если Ваши запросы требуют full table scan, то они будут выполняться быстрее, если объем таблицы - меньше. В InnoDB нормализация позволяет более эффективно использовать buffer_pool

2. Я имел в виду, что procedure analyze плохо работает в контексте SQL. Тем не менее, Вы правы, в контексте вставки результатов в новую таблицу она работает: create temporary table xxx select ball from rating procedure analyse();

3. Например, если много запросов типа UPDATE, то varhar приводит к фрагментированности таблицы.

5. да, это так, но есть ограничения: http://dev.mysql.com/doc/refman/5.1/en/ … tions.html
В частности "You cannot use UPDATE to update more than one underlying table of a view that is defined as a join.". То есть VIEW будет updatable, но все поля связанные JOIN, нельзя будет обновить (и нельзя вставить строку с информацией в этих полях).

Неактивен

 

#5 27.10.2007 21:26:50

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Процедура разбора таблицы

Проверил, через update view можно измнить только одно поле, т.е. только в одной связанной таблице, жаль...

кстати, это ограничение мне кажется несколько искусственным и скорее всего потом будет реализовано полноценное оновление. Ведь при простом JOIN парсеру известно из каких таблиц у него что собрано, и поэтому можно распарсерить запрос и сделать update по всем таблицам, хоть это и не так просто как может показаться...

Поскольку триггеров на view нет - значит собранная таблица будет readonly, ну и ладно...


все таки с производительностью хотелось бы понять. на простом примере
Допустим что у нас нет кэширования вообще.
У нас есть таблица 2 млн записей с двумя столбцами - id auto_increment и col1 char(32) index в котором лежит например md5()
коэффициент уникальности ~5%, т.е. 100000 вариантов.

вариант 1. - одна таблица.
выборки делаются вида SELECT col1 FROM t1 WHERE col1=md5(data)
при этом хорошо используется индекс и нет обьединений.
обьем данных в такой таблице sum(length(id)) + count(*)*32 = sum(length(id)) + 64Mb
если принять что id - всегда число 7-значное (т.е. zerofill) - табличка примерно 78Mb (14+64) (не учитывая служебной информации)

вариант 2.
таблицы две в первой два INT столбца (id, id_ext)
во второй id Mediumint, char(32) UNIQUE
запрос соответственно SELECT col1 FROM t1,t1_ext WHERE t1.id_ext=t1_ext.id
объем первой таблицы ~14M+10M= 24M
объем второй ~10M+32M= 42M

суммарый 66Мб - что примерно на 20% меньше чем в первом варианте. Это по идее уже хорошо, потому что при ЛЮБЫХ запросах объем читаемых данных будет меньше, т.е. меньше шуршаний головой винта. Плюс в справочнике индекс уникальный, т.е. более шустрый и более компактный.  А в основной таблице индекс по id_ext - по числу, не уникальный, но с другой стороны тоже, как мне кажется, более оптимальный чем по строке(32)

Но зато обединение, т.е. его надо просчитать, пусть и с индексами.

понятное дело что преимущества будут нарастать с падением коэффициента уникальности строки и с увеличением ее объема.

Но в общем виде хотелось бы для себя понять - какой в данном случае вариант быстрее??

Про организационные преимущества связанные с нормализацией таблиц (например обновление одной записи маленького справочника <!!> обновлению кучи строк основной таблицы)- это другая тема, хоть и очень важная, интересует именно скорость выборки.

Неактивен

 

#6 27.10.2007 21:55:49

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Процедура разбора таблицы

Во втором варианте объем меньше, что даст определенное преимущество. Однако второй вариант не будет однозначно быстрее - действительно, выборка из второй таблицы будет идти по ключу достаточно быстро, но затем JOIN будет выполняться практически так же медленно, как запрос в первом случае (если есть ключ на первой таблице, если его нет - то будет выполняться быстрее обратно пропорционально объему). Вы можете достичь аналогичной экономии, если создадите индекс на первые 15 символов поля varchar
ADD KEY(t1(15));

В таком случае индекс прочитается быстро, а данные будут считываться только выбранные, так что объем таблицы на диске не будет играть роли.

Оптимизация - дело экспериментальное. Вы сказали про таблицы - создайте их и посмотрите какие запросы с какой скоростью будут выполняться.

Неактивен

 

#7 28.10.2007 00:45:34

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Процедура разбора таблицы

попробовал сделать синтетический тест smile
вариант 1.

CREATE TABLE `test_t1` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `hash` char(32) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `hash` (`hash`(15))
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

таблица заполняется ~2Млн записями, вариантов md5 - 10000.
объем данных 115Мб, вместе с индексами.


вариант 2.

CREATE TABLE `test_t2` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `hash` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `hash` (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

CREATE TABLE `test_t2_ext` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `hash` char(32) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `hash` (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

заполняется соответственно тоже 2Млн записей, во вторую попадает 10000.
Объем данных 59Мб, т.е. почти в 2 раза меньше.


Первый вариант:

SELECT id,hash
FROM test_t1
WHERE test_t1.hash = md5(1550)

второй:

SELECT test_t2.id,test_t2_ext.hash
FROM test_t2 JOIN test_t2_ext ON test_t2.hash = test_t2_ext.id
WHERE test_t2_ext.hash = md5(1550)


explain второго запроса говорит, что у меня из справочника выбирается одна строка(const), потом джойнится к основной таблице(ref) причем в первой перебирается только 196 строк (это из двух то миллионов)..

Выяснилось что скорость в пределах погрешности меньше 80ms и примерно одинакова в обоих случаях. Думаю что сервачина достаточно шустра, чтобы нельзя было почувствовать разницу. Какой бы мне такой запрос написать, чтобы ее выявить? Что то фантазии не хватает smile

Отредактированно Shopen (28.10.2007 00:46:20)

Неактивен

 

#8 28.10.2007 00:54:34

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Процедура разбора таблицы

Вполне возможно, что производительности сравнимая - const выборка - почти мгновенно, а скорость JOIN и скорость выборки по неуникальному индексу сравнимы. Думаю, стоит увеличить объем данных в 20 раз, тогда время будет порядка секунды, что можно уже сравнивать.

Неактивен

 

#9 28.10.2007 00:57:40

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Процедура разбора таблицы

Если хотите замедлить запросом, то сделайте запрос типа in ("A","B",....)

Неактивен

 

#10 28.10.2007 03:11:45

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Процедура разбора таблицы

Увеличил базу до ~40 млн. записей + чуть усложнил запрос, получил существенную разницу.

запрос для одной таблицы:

SELECT id,hash
FROM test_t1
WHERE test_t1.hash IN (md5(1500),md5(1600),md5(7723),md5(3614),md5(36))
ORDER BY id ASC

объем 2.2Г
explain говорит что тип range и будет перебирать примерно 24000 строк
запрос выполняется стабильно около 10 секунд.

запрос для обьединения

SELECT test_t2.id,test_t2_ext.hash
FROM test_t2 JOIN test_t2_ext ON test_t2.hash = test_t2_ext.id
WHERE test_t2_ext.hash IN (md5(1550),md5(100),md5(723),md5(3614),md5(36))
ORDER BY id ASC

explain показывает, что справочник - range c перебором 5 строк, основная таблица - ref с перебором 4000 строк
объем 1.2Г
запрос выполняется за 0.2-0.4 секунды

Вот такая вот разница в производительности получилась в пользу справочника

еще интересно, что для одной таблицы, сама она весит 1.5Г (MYD), а таблица test_t2 весит всего 366М зато файл индексов почти 1Г, почему он такой здоровый?

Отредактированно Shopen (28.10.2007 03:16:25)

Неактивен

 

#11 28.10.2007 11:40:24

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Процедура разбора таблицы

Странно что оптимизатор говорит про перебор разного количества строк. По идее должно перебираться одинаковое количество строк. Ваши запросы возвращают одинаковое количество строк в итоге?

Индекс может быть большим, так как в нем кроме значений еще указатели на ветви дерева. Думаю, во втором случае индекс должен быть меньше, чем в первом.

Неактивен

 

#12 29.10.2007 20:33:51

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Процедура разбора таблицы

количество возвращаемых строк одинаковое

вот эксплейн обоих запросов:

EXPLAIN
SELECT id,hash
FROM test_t1
WHERE test_t1.hash IN (md5(1519),md5(1600),md5(7723),md5(3614),md5(46))


id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    test_t1    range    hash    hash    32        19295    Using where


------------------------------------------------------------------------------------
EXPLAIN
SELECT test_t2.id,test_t2_ext.hash
FROM test_t2 JOIN test_t2_ext ON test_t2.hash = test_t2_ext.id
WHERE test_t2_ext.hash IN (md5(1519),md5(1600),md5(7723),md5(3614),md5(46))

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    test_t2_ext    range    PRIMARY,hash    hash    32        5    Using where
1    SIMPLE    test_t2    ref    hash    hash    4    test.test_t2_ext.id    4170    ""


Видно, что во втором случае серверу приходится перебирать чуть больше строк, но итоговая скорость существенно выше.
я даже погорячился в прошлый раз, первый запрос у меня иногда работает существенно дольше - порядка полутора минут, особенно первый. Видимо это связано с тем, что в прошлый раз я достатоно на запрашивался, чтобы OS засосала таблицу в память, что сказалось на производительности. Вторая-третья попытки первого запроса уже шустрее - ~ 20 секунд.

Тогда как первый запрос к связанным таблицам отработал за 1,2 секунды, последующие ~0.5 секунды.

Влияние кэша запросов я исключаю, каждый раз в одну из md5 вбивая  новое число.

Неактивен

 

#13 29.10.2007 23:45:57

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Процедура разбора таблицы

Наверное здесь действительно выйгрыш от сокращения длины ключа - скорее всего он эффективно кэшируется в key_buffer. Попробуйте кстати, для чистоты отключить key_buffer. Выйгрыш должен остаться, так как ключ должен все равно считываться быстрее.

С другой стороны, если в первой таблице на поле hash есть индекс, то непонятно почему поиск по ключу занимает 20 секунд.

Неактивен

 

#14 07.11.2007 18:57:43

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Процедура разбора таблицы

Продолжаем разговор.

Если текстовых полей , которые выносятся отдельно достаточно много, то, имхо довольно невыгодно апдейтить ее по колонкам, а лучше сначала вынести все справочники, а потом собрать их по id в новую общую таблицу которую потом переименовать на место старой...

сейчас делаю так, для наглядности с одним текстовым столбцом:
исходная таблица:

Код:

CREATE TABLE source (id INT, text1 TEXT NOT NULL, text2 ...., ....)

1. для каждого столбца делаем таблицу вида:

Код:

CREATE TABLE text1_ext (id INT AUTO_INCREMENT PRIMARY KEY, text TEXT NOT NULL, hash CHAR(32) NOT NULL UNIQUE KEY)

2. вставляем в них данные:

Код:

INSERT [IGNORE] INTO text1_ext SELECT test,MD5(text) AS hash FROM source [GROUP BY hash]

3. делаем новую таблицу

Код:

CREATE TABLE result (id INT, id_text1 INT NOT NULL, id_text2....., .....)

4. делаем сборку обратно

Код:

INSERT INTO 
      result 
SELECT 
      source.id, text1_ext.id, text2_ext.id, ....
FROM 
     source 
 JOIN  text1_ext ON MD5(text1) = text1_ext.hash
 JOIN  text2_ext ON MD5(text2) = text2_ext.hash
               .....................

5.удаляем source,  переименовываем таблицу result в source, добавляем индексы на все id_text1, id_text2....

Насколько правильный алгоритм?

на большом количестве текстовых полей тормозит существенно, может есть что оптимизнуть?

в пункте 2 показаны два подварианта - либо группировать выборку, либо пихать всю таблицу игнорируя повторы при вставке дублирующихся ключей - что оптимальнее?

Отредактированно Shopen (07.11.2007 19:00:08)

Неактивен

 

#15 07.11.2007 23:49:17

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Процедура разбора таблицы

Я не понимаю использование GROUP BY - в запросе у Вас получается смешивание групповых и негрупповых элементов (что неправильно с точки зрения SQL - правильно писать select max(text), hash .. group by hash). Лучше использовать SELECT distinct. Кроме того, не обрабатывается случай совпадающего хэша для разных значений текстового поля (такое редко будет, но в принципе возможно).

Здесь можно использовать курсор для перебора исходной таблицы, возможно будет быстрее. Если хочется оптимизировать, можно заюзать HANDLER, это low-level курсор. http://dev.mysql.com/doc/refman/5.1/en/handler.html

Неактивен

 

#16 08.11.2007 03:31:09

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Процедура разбора таблицы

GROUP BY здесь потому, что если не учитывать возможные совпадения md5 у разных текстов, т.е. каждому ункальному тексту соответствует уникальный md5, то в данном запросе
GROUP BY md5(text) == GROUP BY text == GROUP BY text, md5(text)

но мне кажется что группировать по текстовому полю серверу существенно сложнее, чем по 32-байтной строке. С другой стороны ее надо вычислять, но по скольку это надо делать все равно, то эти затраты можно не учитывать. или нет?

SELECT DISTINCT в данном случае ведь будет выглядеть так: SELECT DISTINCT text, md5(text) FROM ....
что опять же эквивалент GROUP BY text, md5(text).

А вариант INSERT IGNORE совсем не катит?

Неактивен

 

#17 08.11.2007 11:05:58

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Процедура разбора таблицы

Я имел в виду следующее:
mysql> create table x (text text);
mysql> insert into x values ('a');
mysql> insert into x values ('b');
mysql> set @@sql_mode='ONLY_FULL_GROUP_BY';
mysql> select text,md5(text) from x group by md5(text);
ERROR 1055 (42000): 'test.x.text' isn't in GROUP BY
mysql> select text,md5(text) from x group by text;
ERROR 1055 (42000): 'md5(text)' isn't in GROUP BY
mysql> select text,md5(text) from x group by text,md5(text);
+------+----------------------------------+
| text | md5(text)                        |
+------+----------------------------------+
| a    | 0cc175b9c0f1b6a831c399e269772661 |
| b    | 92eb5ffee6ae2fec3ad71c777531578f |
+------+----------------------------------+
2 rows in set (0.00 sec)

mysql> select text,max(md5(text)) from x group by text;
+------+----------------------------------+
| text | max(md5(text))                   |
+------+----------------------------------+
| a    | 0cc175b9c0f1b6a831c399e269772661 |
| b    | 92eb5ffee6ae2fec3ad71c777531578f |
+------+----------------------------------+
2 rows in set (0.00 sec)

Соответственно только последние 2 запроса синтаксически корректны. DISTINCT должен быть быстрее, чем GROUP BY или IGNORE.

mysql> explain select text,md5(text) from x group by text,md5(text)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: x
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using temporary; Using filesort
1 row in set (0.00 sec)

mysql> explain select distinct text,md5(text) from x\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: x
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using temporary
1 row in set (0.00 sec)


Думаю курсор будет эффективнее, так как не потребует памяти для JOIN (которые могут происходить во временных таблицах на диске, как и GROUP BY).

Неактивен

 

#18 08.11.2007 12:20:38

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Процедура разбора таблицы

rgbeast написал:

Я имел в виду следующее:

Соответственно только последние 2 запроса синтаксически корректны.

вопрос на самом деле интересный. А где написано что это нарушает синтаксис?
по умолчанию в mysql это разрешается

ONLY_FULL_GROUP_BY - это опция, но само ее наличие не говорит о том, что это более синтаксически правильно, это может быть просто фичей, типа safe_mode.

Кстати из режиме ANSI (т.е. соответсвия стандартам) эту опцию, начиная с 5.0.3 - выкинули, что тоже наводит на мысли, что если это и считалось нарушением стандарта раньше, то сейчас уже таковым не является.
Я не знаток стандартов, может чего и пропустил.



rgbeast написал:

DISTINCT должен быть быстрее, чем GROUP BY или IGNORE.

mysql> explain select text,md5(text) from x group by text,md5(text)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: x
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using temporary; Using filesort
1 row in set (0.00 sec)

mysql> explain select distinct text,md5(text) from x\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: x
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using temporary
1 row in set (0.00 sec)

Пример точно не верный smile
Во первых, к сожалению, в mysql есть, с сугубо соей личной точки зрения, большой баг. Он НЕЯВНО сортирует результаты GROUP BY запроса, хотя его никто об этом не просит. И хотя это и описано в документации - тем не менее мне кажется такое поведение очень вредным.

если в вашем запросе после GROUP BY добвать ORDER BY NULL - то explain-ы станут идентичными, ведь filesort появляется как раз из за неявного ORDER BY.

Более того, как я и говорил GROUP BY fiels_list == DISTINCT fiels_list, вот цитата из доки:

Про IGNORE мне тоже показалось что тормознее, хоть убейте не понимаю почему. ведь таже самая проверка ключей, только не требуется временная таблица... Хм. может при вставке с IGNORE не надо DISABLE KEYS делать на таблице в которую вставляются данные?

Код:

6.2.13. DISTINCT Optimization
.......................
In most cases, a DISTINCT clause can be considered as a special case of GROUP BY. For example, the following two queries are equivalent:

SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const;

SELECT c1, c2, c3 FROM t1
WHERE c1 > const GROUP BY c1, c2, c3;

rgbeast написал:

Думаю курсор будет эффективнее, так как не потребует памяти для JOIN (которые могут происходить во временных таблицах на диске, как и GROUP BY).

курсоры это интересно - где нибудь можно почитать по русски про них? хотя бы вводные какие то вещи, чтобы въехать в технологию их использования?

Отредактированно Shopen (08.11.2007 12:23:13)

Неактивен

 

#19 08.11.2007 12:42:22

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Процедура разбора таблицы

Поясню, почему такой требование (в MySQL раньше совсем все было можно, сейчас идут к стандартам). По ANSI не знаю, что именно в нем.
Пусть есть запрос
INSERT INTO x SELECT a,b FROM t GROUP BY a;

в этом случае - b произвольное значение из группы для заданного a. Если мы делаем репликацию, данный запрос выполнится на slave и нет никакой гарантии, что значение b будет таким же. В таком случае реплики разойдутся.

Про DISTINCT мне казалось, что он для оптимизатора проще, так как в нем нет групповых функций (и нет возможности их использования). Про IGNORE - надо просто тестировать что быстрее. Я не думаю, что IGNORE кто-то специально оптимизировал.

Про курсоры на русском не знаю, но про них написано на языке SQL (пример программы):
http://dev.mysql.com/doc/refman/5.1/en/cursors.html

HANDLER должен работать быстрее, чем курсор, так как low-level

Неактивен

 

#20 08.11.2007 12:53:40

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Процедура разбора таблицы

А разве b - это действительно произвольное значение?

Если учесть, что DISTINCT == GROUP BY, то не группируемое поле будет возвращатся первое по порядку следования в таблице...(или возможно по порядку какой-нибудь внутренней сортировки, хто ж их знает там в AB, но порядок этот если и есть то везде одинаков) т.е. если порядок запросов на реплике такой же как на мастере и базы полностью синхронизованы - то результат должен быть один

иначе
SELECT a,b FROM t GROUP BY b
SELECT a,b FROM t GROUP BY b
SELECT a,b FROM t GROUP BY b
SELECT a,b FROM t GROUP BY b
SELECT a,b FROM t GROUP BY b

должен пять раз дать разный результат


более того, если тут и возможна какая то неоднозначность то row-based репликация всех спасет smile

я думал курсоры и хэндлеры - оддно и тоже...

тогда чуть другой вопрос - где можно прочитать про handler-ы c хоть каким(и) то примером?

Неактивен

 

#21 08.11.2007 13:26:07

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Процедура разбора таблицы

запросы:

SELECT a,b FROM t GROUP BY b
SELECT a,b FROM t GROUP BY b
SELECT a,b FROM t GROUP BY b
SELECT a,b FROM t GROUP BY b
SELECT a,b FROM t GROUP BY b

дают одно и то же подряд, но мы не можем гарантировать, что внутренний порядок на мастере и реплике один (хотя так обычно происходит) по нескольким причинам:
1. никто не запрещает досинхронизовывать слейв руками в случае сбоев - тогда порядок может стать другим
2. на мастере и на слейве может быть разный storage engine, следовательно разный внутренний порядок
3. движок кластера (ndb) делает внутренную сортировку по нодам, поэтому порядок будет зависеть от текущей конфигурации кластера (и будет меняться, если изменится конфигруация онлайн). См. обсуждение в баге http://bugs.mysql.com/bug.php?id=32084
4. в круговой репликации порядок может быть разным

Похоже в процедурах нужно использовать именно курсоры. HANDER будет работать внутри приложения, так как я не нашел способа результат команды HANDLER использовать в подразпросе или записать в переменные внутри процедуры. Наверное,  реализация курсора использует HANDLER, а HANDLER следует использовать в приложениях (например, использующих C API).

Синтаксис такой:
mysql> create table f (id int(11) primary key, t1 text, t2 text);
mysql> insert into f VALUES (1,"a","c"), (2,'aa','cc'), (3, 'aaa','ccc'), (4,'aaaa','cccc');
mysql> HANDLER f OPEN fh;
mysql> HANDLER fh READ FIRST;
+----+------+------+
| id | t1   | t2   |
+----+------+------+
|  1 | a    | c    |
+----+------+------+
1 row in set (0.00 sec)

mysql> HANDLER fh READ NEXT;
+----+------+------+
| id | t1   | t2   |
+----+------+------+
|  2 | aa   | cc   |
+----+------+------+
1 row in set (0.00 sec)

mysql> HANDLER fh READ NEXT;
+----+------+------+
| id | t1   | t2   |
+----+------+------+
|  3 | aaa  | ccc  |
+----+------+------+
1 row in set (0.00 sec)

mysql> HANDLER fh READ NEXT;
+----+------+------+
| id | t1   | t2   |
+----+------+------+
|  4 | aaaa | cccc |
+----+------+------+
1 row in set (0.00 sec)

mysql> HANDLER fh READ NEXT;
Empty set (0.00 sec)

mysql> HANDLER fh CLOSE;   
Query OK, 0 rows affected (0.00 sec)

Неактивен

 

#22 08.11.2007 23:51:25

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Процедура разбора таблицы

по поводу GROUP BY понял smile

HANDLER и CURSOR думаю для меня рановато пока smile

Неактивен

 

#23 09.11.2007 02:43:35

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Процедура разбора таблицы

Курсор - это совсем несложно. Это просто итератор по таблице, который читает ее строчка за строчкой. Почему это может быть быстрее: Вам нужно перегнать одну таблицу в другую - вы считываете запись, обрабатываете ее - записываете в другую таблицу. При этом не требуется загружать в память всю таблицу для GROUP BY или JOIN. Используемые операции - это поиск во вторичных таблицах по ключу и вставка во вторичные таблицы. Обе операции не требуют памяти, временных таблиц и дисковых таблиц. Недостаток в том, что таких операций очень много. Интересно протестировать производительность для Вашего примера.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson