MySQL и тайпкастинг

Рубрика: MySQL | 17 September 2007, 15:36 | Vadim Voituk

Так как MySQL производит автоматическую конверсию типов в тектсе SQL-запросов, расскажу немного о том, как можно этой конверсией управлять.
Предположим у нас есть 2 одинаковых даты, но в разных форматах:
’2007-07-01′ и ’20070701′.
Необходимо средствами БД (например в SQL-запросе) ставнить их на равенство: [Далее...]

MySQL LOAD DATA INFILE

Рубрика: MySQL | 12 September 2007, 11:18 | Vadim Voituk

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

Наверное все наслышаны про “very high speed” оператора LOAD DATA INFILE, потому я применяю его там, где позволяет логика системы и критична нагрузка на БД.

В конкретном примере данные о поступающих запросах пишутся в comma-separated лог-файл, который раз в 5 минут загружается в БД с помощью “LOAD DATA INFILE”.
При этом даже не возникает проблем с репликацией данных – достаточно чтоб загружаемый файл был только на одном сервере. [Далее...]

Нотификатор окончания запроса MySQL

Рубрика: Development, MySQL | 6 May 2007, 19:35 | Vadim Voituk

Думаю любому, кто хоть как-то работает с СУБД MySQL, приходилось выполнять запросы, которые обрабатывают огромные массивы данных и в результате их время выполнения достигает нескольких часов, а иногда и суток.
Чаще всего без предварительных тестов оценить время выполнения такого запроса нереально, а ещё чаще такие тесты проводить некогда.
Потому в какой-то момент я написал небольшой нотификатор, какой уведомляет меня по емайл об окончании выполнения такого запроса: [Далее...]

Наверное сама распостраненная профессиональная болезнь PHP-программистов – это незакрытое подключение к MySQL. Правда болезнь эта быстро самовылечивается у тех, кто, как говорится, “наступал на эти грабли”.
В принципе MySQL и сам провоцирует это, позволяя плодить соединения сотнями, а также по умолчанию небольшим временем таймаута.

[Далее...]

Groovy + JSON + Prototype in Action

Рубрика: Development, Groovy, Java, JavaScript/Ajax, MySQL | 6 April 2007, 18:53 | Vadim Voituk

Дабы у читателя не возникало мысли, что Groovy подходит только для написания простеньких утилит и prebuild-скриптов предлагаю рассмотреть пример создания серверного приложения на Groovy с использованием коктейля Web2.0-ппопулярных технологий.

[Далее...]

MySQL: Table is full

Рубрика: Development, MySQL | 5 February 2007, 15:29 | Vadim Voituk

Есть в MySQL такая неприятная вещь как “Table is full”.
Суть её заключается в том, что в какой-то наступает момент количество данных в таблице превышает 4 гигабайта, и MySQL отказывается выполнять INSERT/UPDATE-запросы.

Ноги этой “бяки” ростут из ограничения на максимальный размер файла на некоторых платформах.
Решается достаточно просто:

ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

где MAX_ROWS – ограничение на количество записей в таблице, AVG_ROW_LENGTH – усредненное количество данный в одной записи (используется только для таблиц с BLOB и TEXT полями).

Но это в случае если ваша файловая система поддерживает файлы размером более 4Гб. Если же это не так, то решение не столь примитивное – необходимо разбивать таблицу на разделы (partitions).

Теперь расскажу почему “table is full” доставляет кучу проблем:

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

2. При указании очень большого значения MAX_ROWS, MySQL молча “ложит” на это значение и прописывает “max_rows=4294967295″. Таким образом выше 4-х миллиардов записей все-равно не прыгнешь.

3. Решение с проблемы с помошью PARTITIONS работает только на MySQL >= 5.1

4. И как вы думаете сколько времени выполняется указанный выше ALTER TABLE на 4 Гб данных? А сервис все это время находится в состоянии, близкому к коме.

Целью данной заметки не было формирование какого-либо мнения/вывода о MySQL, а скорее желание предупредить рядового разработчика о возможной проблеме и ознакомить с некоторыми способами её решения.

MySQL index usage and LIMIT statement

Рубрика: Development, MySQL, Работа | 5 January 2007, 10:28 | Vadim Voituk

Хочу рассказать об одной достаточно важной особенности SQL-оптимизатора MySQL.
Предположим в таблице почти 390 миллионов (389239897) записей.

Обычный запрос, выбирающий 10 записей за октябрь 2006 г.

EXPLAIN
SELECT *
FROM my_table
WHERE
    c_time>='2006-10-01 00:00:00' AND
    c_time<'2006-11-01 00:00:00'
LIMIT 10;

Обращаем внимаени на значения:
key: i_c_time
rows: 89221170

Такой же запрос, но без указания LIMIT:

EXPLAIN
SELECT *
FROM my_table
WHERE
    c_time>='2006-10-01 00:00:00' AND
    c_time<'2006-11-01 00:00:00';

key: NULL
rows: 389239897

Видно что разница в том, что во втором запросе не используется индекс по полю c_time (key:NULL)
Следовательно происходит полное сканирование таблицы.

Насильно указываем использовать индекс по c_time:

EXPLAIN
SELECT *
FROM my_table FORCE INDEX (i_c_time)
WHERE
  c_time>='2006-10-01 00:00:00' AND
  c_time<'2006-11-01 00:00:00';

Получаем заветные
key: i_c_time
rows: 89221170

Выясняем каким образом LIMIT влияет на использование индексов в запросах.

Указываем в LIMIT число, меньшее чем количество записей в таблице

... LIMIT 389239890

Получаем заветные
key: i_c_time
rows: 89221170

Указываем в LIMIT число равное количеству записей в таблице:
[sql]… LIMIT 389239897[/sql]
Имеем
key: NULL
rows: 389239897

И аналогичный запрос с указанием LIMIT бОльшим чем записей в таблице

... LIMIT 389239899

И аналогичный результат
key: NULL
rows: 389239897
т.е. полное сканирование всей таблицы

Следовательно при указании значения LIMIT >= количества записей,
оптимизатор MySQL выполняет полное сканирования таблицы.

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

Если же в запросе указать значение оператора LIMIT меньше чем записей в таблице,
то в выборке будет участвовать индекс.

За неименением свободного времени я не стал проводить тесты,
указывая в LIMIT диапазоны значений (например LIMIT 20000,30000),
но что-то мне подсказывает что ситуация будет аналогичная.

Variables on MySQL-replication

Рубрика: MySQL | 17 September 2006, 15:39 | Vadim Voituk

Часто в проектах приходится строить рейтинг чего-либо.
Например рейтинг пользоватлей за день по критерию (количество баллов, сообщений и тд и тп). [Далее...]

Правильный count() в MySQL

Рубрика: Development, MySQL | 27 July 2006, 18:37 | Vadim Voituk

Время от времени, читая чужой код, вижу что некоторые разработчики указывают разные параметры в функцию COUNT() для получения количетсва строк.
Например: count(*), count(id) (где id – это primary key), count(0).
Возникает вопрос: “Какой count() правильный? А какие лже-count-ы?”
За основу “правильности” взял скорость выполнения запроса. [Далее...]

MySQL ignore index

Рубрика: Development, MySQL | 21 July 2006, 15:47 | Vadim Voituk

Есть в MySQL такая, на мой взгляд достаточно важная, и тем не менее оставленная без внимания, функция как игнорирование индексов при SELECT-запросах.
Выглядит это достаточно просто: [Далее...]

Страница 2 из 3123