Записки MySQL девелопера

Это, конечно, логично, но не всегда сразу очевидно. При работе с таблицей типа MEMORY важно всегда знать, что удаление строк в ней не влияет на размер занимаемой памяти, до тех пор, пока не будет выполнен хотя бы такой фейковый запрос (фейковый потому, что по сути ничего в структуре таблицы не меняется, хотя размер занимаемой ОЗУ при этом пересчитывается):

ALTER TABLE tbl_name engine=memory;

См. также:
Список поддерживаемых типов таблиц в MySQL
Типы таблиц MySQL
Имена таблиц чувствительные к регистру… но не всегда!

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

• MyISAM
• InnoDB
• BerkeleyDB (BDB)
• MERGE

Наиболее важное свойство таблицы - поддержка ею транзакций или нет. Поддержку транзакций обеспечивают только таблицы InnoDB и BDB. Кроме того, только таблицы MyISAM поддерживают полнтекстовый поиск.

MyISAM

MyISAM является типом таблицы по умолчанию. Такие таблицы работают очень быстро, но без обеспечения механизма транзакций. Размер таких таблиц зависит от операционной системы, хоть и файлы таблиц можно смело копировать из одной операционной системы в другую. Максимальное число ключей в таблице - 64, максимальная длина ключа - 1024 байта.

InnoDB

Таблицы InnoDB table поддерживают транзакции и блокировку на уровне строки таблицы. Как и у таблиц MyISAM, файлы данных таблиц InnoDB можно легко копировать из одной операционной системы в другую. Главный недостаток таких таблиц - они требуют больше дискового пространства для хранения данных.

BDB

BDB схожи с InnoDB и также поддерживают транзакции.. Они поддерживают блокировки на уровне старницы, но файлы данных из разных систем у BDB не совместимы.

MERGE

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

См. также:
Экспорт данных большого объема в БД на хостинге
Список поддерживаемых типов таблиц в MySQL
Дата последнего обновления таблицы

Многие ли знают что MySQL, начиная с версии 4.1.1, поддерживает две замечательные функции - COMPRESS и DECOMPRESS? Служат они для сжатия объемных данных. Могут применятся при вставке и выборке данных:

INSERT INTO data VALUES(COMPRESS('ооочень длинная строка'))

SELECT DECOMPRESS(TEXT) FROM data

См. также:
Опция SQL_BUFFER_RESULTS
Быстрый перенос базы данных с одного сервера на другой
VARCHAR, VARCHAR…

Немного советов в копилку разработчика баз данных:

• Выбирайте правильный тип данных для чисел. В MySQL 9 типов числовых данных. В Oracle, например, он всего один.
• Используйте TIMESTAMP вместо DATETIME, первый тип требует для хранения всего 4 байта, по сравнению с 8-мью байтами для второго.
• Используйте по возможности поля ENUM вместо строковых данных.
• Указывайте для полей NOT NULL, это экономит 1 байт для каждой строки.
• Не используйте SELECT *, экономьте память и трафик.
• При оптимизации запросов при помощи EXPLAIN старайтесь достичь отсутствия “using filesort” и “using temporary table”.

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

См. также:
Опция SQL_BUFFER_RESULTS
Медленный ORDER BY RAND()
Регистрозависимый LIKE

Добавление к стандартному INSERT’у инструкции LOW_PRIORITY задаст низкий приоритет для выполнения запроса. Фактически запрос выполнится только после выполнения всех SELECT’ов из очереди. Полезная штука для высоконагруженных систем.

ЗЫ. Купил недавно себе смартфон ASUS P750 в интернет магазине 1Good Екатеринбург. Сижу немогу нарадоваться :).

См. также:
Упаковка/распаковка данных в таблицах
Повозимся с NULL-полями таблиц!
6 важных советов по созданию БД в MySQL

Если вы получили от MySQL сообщение об ошибке №28, то не пугайтесь, проблема банальная - на жестком диске кончилось место и нужно всего лишь освободить какую-то часть и перезапустить MySQL. Вчера потратил пару часов, пока разобрался с аналогичной проблемой у себя на сервере.

См. также:
Функция UUID()
Свой ORDER BY
Опция SQL_BUFFER_RESULTS

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

Так, например, запрос вида SELECT SQL_BUFFER_RESULTS… заставит MySQL сделать временный результат в промежуточном буфере. Как только временные данные в буфере будут сделаны, все блокировки с таблицы будут сняты. Это может помочь, если у вас проблемы с блокировками таблицы или когда требуется много времени для передачи полученных данных к клиенту.

См. также:
Типы таблиц MySQL
SELECT HIGH_PRIORITY…
Кодировки, кодировки, кодировки…

На вопрос JOIN’ить или не JOIN’ить в запросе таблицы ответ зачастую простой, - не JOIN’ить, если требуется конструкция INNER JOIN вида:

SELECT * FROM 
table1 
INNER JOIN 
table2 
ON table1.field1 = table2.field2

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

SELECT * FROM table1, table2 
WHERE table1.field1 = table2.field2

См. также:
Для чего нужен STRAIGHT JOIN?
Быстрая справка по MySQL :)
Свой ORDER BY

Осуществить запрет на кэширование запросов чаще всего требуется при проведении замеров быстродействия того или иного запроса. Для таких ситуаций существует специальная конструкция вида:

SELECT SQL_NO_CACHE field_name FROM table_name

См. также:
Получение данных о стуктуре таблицы
Конструкция SELECT SQL_NO_CACHE…
О конструкции SELECT CASE

Число ситуаций, когда сервер MySQL не будет использовать индексы таблицы довольно ограничено. Итак, индексы не используются, когда:

• используются таблицы типа HEAP
• осуществляется поиск по одному индексированному полю, а сортировка по другому
• осуществляется поиск по полю при помощи конструкции LIKE с маской, начинающейся с символа %
• в запросе осуществляется поиск по частичному индексу

См. также:
Повозимся с NULL-полями таблиц!
Не используйте SELECT * FROM
Как избежать дубликатов в таблице при помощи индексов?

Оказывается конструкция ORDER BY RAND() работает достаточно медленно. В высоконагруженных базах данных рекомендуют вместо, к примеру:

SELECT id, name FROM positions ORDER BY RAND() LIMIT 5

писать запрос вида

SELECT id, name FROM positions WHERE id IN ('2', '14', '5', '3')

где набор id-шников генерируется случайно внешней программой (например, php-скриптом) в диапазоне от 1 до MAX(id).

См. также:
Свой ORDER BY
GROUP BY & ORDER BY null
Быстрая справка по MySQL :)

Наткнулся тут на один оптимизационный скрипт, выдающий советы по тюнингу конфига MySQL. Качать здесь.

Оптимизировал у себя на одном сервере - вроде все ок, правда прироста не заметил особого :).

См. также:
Как сбросить пароль MySQL?
MySQL: error 28
Быстрый перенос базы данных с одного сервера на другой

Собственно, не рекомендуется использовать запись вида "SELECT * FROM…" По ряду причин лучше использовать "SELECT id, name…. FROM":

• экономия памяти и, возможно, трафика между SQL и WWW сервером
• разработчик всегда будет знать какие поля выбираются из таблицы

См. также:
Повозимся с NULL-полями таблиц!
6 важных советов по созданию БД в MySQL
Конструкция SELECT SQL_NO_CACHE…

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

Для решения этой проблемы советуют принудительно дописывать ORDER BY null в конце запроса. Я решил попробовать потестировать оба варианта запроса:

Обычный подход

mysql> EXPLAIN SELECT aff FROM dic GROUP BY aff\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dic
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7888
        Extra: USING temporary; USING filesort
1 row IN SET (0.00 sec)

Оптимизированный подход

mysql> EXPLAIN SELECT aff FROM dic GROUP BY aff ORDER BY NULL\G                 *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dic
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7888
        Extra: USING temporary
1 row IN SET (0.00 sec)

Как видно из результатов запрсоа EXPLAIN, на самом деле второй вариант будет отработан быстрее (я не про секунды в результатах), т.к. в по столбцу Extra видно что в первом варианте осуществляеся сортировка, а во втором - нет.

См. также:
Повозимся с NULL-полями таблиц!
Конструкция SELECT … WITH ROLLUP
6 важных советов по созданию БД в MySQL

Иногда необходимо узнать число строк в таблице MySQL без учета оператора LIMIT. Большинство программистов в этой ситуации просто делают второй запрос - SELECT COUNT(*) FROM …, что, на самом деле, не очень хорошо. Обычно такая ситуация возникает при реализации постраничной прокрутки каких-либо данных.

На самом деле, начиная с 4-й версии MySQL существует возможность быстрого определения числа строк в запросе без учета ограничения по оператору LIMIT:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM positions LIMIT 1\G
*************************** 1. row ***************************
          id: 90
   id_series: 40
        name: MSC-GA25VB/MUH-GA25VB
      params: a:4:{i:2;s:3:"2,6";i:11;s:3:"3,0";i:4;s:11:"815x278x244";i:14;s:4:"0,82";}
   functions: a:0:{}
 description:
spec_comment:
    warranty: 3 ????
       price: 696.817
       FIXED: -1
         new: 0
    invertor: 0
        spec: 0
    pos_type: simple
     id_sort: 60
     visible: 1
1 row IN SET (0.00 sec)
 
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|          399 |
+--------------+
1 row IN SET (0.00 sec)

См. также:
Быстрая справка по MySQL :)
Кодировки, кодировки, кодировки…
Как сбросить пароль MySQL?