MySQL шпаргалки

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

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

Работа с бекапами

Делаем бекап
mysqldump -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql

Создаём структуру базы без данных
mysqldump --no-data - u USER -pPASSWORD DATABASE > /path/to/file/schema.sql

Заливаем бекап в базу данных
mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql

Создаём новую базу данных
mysqladmin -u USER -pPASSWORD create NEWDATABASE

Подробнее

Общие факты

  • Полезно под каждую базу на боевом сервере создавать своего пользователя
  • Кодировка базы может быть любой, если она UTF8
  • В большинстве случаев лучше использовать движок InnoDB
  • В php лучше забыть про сильно устаревшее расширение mysql и по-возможности использовать pdo или mysqli
  • Новую копию MySQL всегда можно настроить и оптимизировать
  • Без особой нужды не стоит открывать MySQL наружу. Вместо этого можно сделать проброс портов
    ssh -fNL LOCAL_PORT:localhost:3306 REMOTE_USER@REMOTE_HOST

Работа с данными

Числа
  • Не ставьте для типа INTEGER свойство UNSIGNED, если вы не знаете что делайте. Из коробки php не поддерживает числа такого большого размера
  • Связанные таблицы («Foreign keys») должны иметь полное сходство по структуре ключей. Т.е. если у нас на одной таблице для поля указано «INTEGER UNSIGNED DEFAULT 0 NOT NULL» то и на другой должно быть указано аналогично
  • Для хранения булевых значений, нужно использовать TINYINT(1)
  • А деньги лучше хранить в DECIMAL(10, 2), где первое число обозначает количество всех знаков, включая запятую, а второе — количество знаков после запятой… Итого, у нас получится что DECIMAL(10,2) Может сохранить 9999999,99
Строки
  • VARCHAR ограничена 255 символами, поэтому нет никакого смысла указывать VARCHAR(300)
  • Помните, что тип TEXT ограничен только 64 килобитами, поэтому что бы сохранять «Войну и Мир» пользуйтесь «LONGTEXT»
  • Самая правильная кодировка для вашей БД UTF8
Даты

Не забывайте, что

  • DATE, TIME, DATETIME — это по-сути строки, поэтому поиск и сравнение дат происходит через преобразование
  • TIMESTAMP — хранится в виде INTEGER UNIXTIMESTAMP, что существенно облегчает выборку и сортировку
Перечисления
  • Для перечислений правильно использовать тип ENUM
  • Правильно пишется так: ENUM('мама', 'мыла', 'раму')
  • Можно ставить значение по-умолчанию, как и для любой строки
  • В базе поле с перечислением хранится как число, поэтому скорость работы — потрясающе высокая
  • Количество перечислений ~ 65 тысяч

dev.mysql.com/doc/refman/4.1/en/storage-requirements.html
help.scibit.com/mascon/masconMySQL_Field_Types.html

Отладка

  • Если запросы тормозят, то можно включить лог для медленных запросов в /etc/mysql/my.cnf
  • А потом оптимизировать запросы через EXPLAIN
  • И наблюдать за запросами удобно через программу mytop

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

Автор материала: ukko



Опубликовал admin
13 Окт, Среда 2010г.



Программирование для чайников.