Оптимизация сервера MySQL 5

Резервное копирование данных и аварийное восстановление информации.

//с помощью командной строки
mysqldump -uroot -ppassword > backup.sql //dump
mysqldump -uroot -ppassword -T C:/myFiles/myBackup/2012 nameBackup //dump
mysql -uroot -ppassword world < backup.sql //восстановление

Проверка таблиц на ошибки. CHECK — провераяет таблицу на повреждения. REPARE — восстанавливает таблицу, при этом выбрасывает поврежденную страницу, т.е. часть данных может потеряться, но сама таблица останется работоспособной. Reapre работает не на всех движках (например, InnoDB). Команда OPTIMIZE — что-то типа дефрагментации, т.е. реально удаляет удаленные записи (при удалении записи сервер физически их не удаляет, а помечает как удаленные, т.е. не учитывает их; это сделан, чтобы не нагружать лишний раз сервер).

CHECK TABLE mytable;
REPARE TABLE mytable;
REPARE TABLE mytable quick; //быстрая переиндексация всех индексов
OPTIMIZE TABLE mytable;

Управление привилегиями пользователей.

//зададим привилегию SELECT, UPDATE для user на все таблицы нашей БД
GRANT SELECT, UPDATE
   ON database1.*
   TO 'user'@'%';
//заберем все привилегии для user для таблицы superTable в нашей БД
REVOKE ALL PRIVILEGES
   ON database.superTable
   FROM 'user'@'%';

Оптимизация запросов. Создание индексов может в несколько раз повысить скорость обработки запроса к БД. Индексы не работают в фактографических таблицах (таблицы логов).

//создание индексов
CREATE TABLE demo SELECT * FROM database.demo1;
CREATE INDEX myInd ON demo;

Оптимизация баз данных. Сюда входят: оптимизация хранилищ, нормализация БД (уменьшает скорость работы сервера, но сокращает объем, избыточность) и использование агрегаторов (замедляет работу сервера, но можно создавать предрассчитанные значения в момент простоя сервера, и потом использовать их, что значительно повысит скорость выборки).
Оптимизация сервера: кэширование данных, query_сache — возможность сервера кэшировать результаты запроса (т.е. результаты выборки SELECT).

//query_cache
query_cache_limit; //max объем кэшируемого запроса
query_cache_min_res_unit; //min объем кэшируемого запроса
query_cache_size; //размер кэша

Метки: mysql5