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

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

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

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

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

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

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

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

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

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

Проверил, через 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)

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

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

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

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

попробовал сделать синтетический тест
вариант 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 и примерно одинакова в обоих случаях. Думаю что сервачина достаточно шустра, чтобы нельзя было почувствовать разницу. Какой бы мне такой запрос написать, чтобы ее выявить? Что то фантазии не хватает

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

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

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

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

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

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

Я не понимаю использование 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

Я имел в виду следующее:
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).

Поясню, почему такой требование (в 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

запросы:

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)

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