MySql Join

Оптимизация

Заливка дампа таблиц файлов

http://forums.mysql.com/read.php?24,508243,508243#msg-508243

Настройка MySQL

Рекомендуемая конфигурация, разбор параметров после. Пример сделан для ОС *NIX типа, как наиболее подходящей для высоконагруженных серверов БД. При необходимости его можно адаптировать для Windows системы, изменив соответственно пути файловой системы и пропустив секцию mysqld.safe.

Кодировка 1251 базы данных по-умолчанию - исторически сложившаяся и не изменяемая как не несущая в себе существенных неудобств. Но при большой необходимости (например, требования хранения в БД символов других алфавитов) может быть изменена на UTF-8 или иную, при этом необходимо также скорректировать .properties файлы приложений биллинга на предмет кодировки БД.

my.cnf

Опции mysqld - общие параметры:

'datadir' - каталог для хранения БД, лучше если это будет отдельный диск (RAID); высокие требования по надёжности и скорости.

'tmpdir' - каталог для хранения временных файлов, лучше сделать отдельным быстрым диском; невысокие требования по надёжности, высокие по скорости; можно использовать RAM диск.

'socket' - файловый сокет, возможность подключения консольным клиентом.

'user' - пользователь ОС, под которым запускается БД.

'log-slow-queries' - логирование запросов, выполняющихся длительное время.

'skip-name-resolve' - отключает DNS резолвинг для устанавливаемых соединений.

'default-storage-engine' - тип хранилища таблиц по-умолчанию, InnoDB - транзакционный тип хранилища.

'default-character-set' - кодировка по-умолчанию для вновь создаваемых таблиц.

'default-collation' - collation (порядок букв, используется при сортировке) по-умолчнию для вновь создаваемых таблиц.

'sql-mode' - запрещается установка режимов 'STRICT_TRANS_TABLES' и 'STRICT_ALL_TABLES', это приведёт к неработоспособности некоторых компонентов биллинговой системы.

'max_allowed_packet' - максимальный размер пакета с запросом, принимаемого сервером.

'max_connections' - ограничение на максимальное число соединений с БД.

'memlock' - запрет на перенос процесса mysqld в свап.

'table_cache' - максимальное количество описаний структур открытых таблиц в кэше.

'thread_cache_size' - размер кэша потоков для предотвращения их повторного создания.

'thread_concurrency' - число одновременно работающих потоков, рекомендуется ставить 2 * 'количество CPU' + количество дисков.

Запрещается установка опции 'skip-networking', т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет.

Опции mysqld - InnoDB:

'innodb_file_per_table' - для возможности использования Backup базы с помощью snapshot'ов (Linux, LVM) в ОС LINUX.

'innodb_log_group_home_dir' - путь к каталогу под журнал транзакций, лучше если это будет отдельный диск; высокие требования по скорости и надёжности, низкие по объёму.

'innodb_buffer_pool_size' - размер буфера под все нужды, он должен составлять порядка 70-80 % от общей памяти сервера БД.

'innodb_additional_mem_pool_size' - параметр можно не изменять, размер буфера под доп. цели.

'innodb_log_files_in_group' - количество файлов журналов транзакций в группе журналов; InnoDB производит запись в файлы по круговому способу; увеличение ускоряет запись но тормозит восстановление информации в случае сбоя.

'innodb_log_file_size' - размер каждого файла журнала в группе журналов (указывается в мегабайтах).

'innodb_log_buffer_size' - размер буфера, который в InnoDB используется для записи информации файлов журналов на диск.

'innodb_lock_wait_timeout' - время простоя (в секундах), на протяжении которого транзакция InnoDB может ожидать прекращения блокировки прежде, чем будет произведен откат.

'innodb_thread_concurrency' - должно совпадать с 'thread_concurrency'; число одновременно работающих потоков, рекомендуется ставить 2 * 'количество CPU' + количество дисков.

'innodb_flush_log_at_trx_commit' - 2 отменяет сброс данных на диск при каждой транзакции, ускорение работы.

'innodb_flush_method' - O_DIRECT отключает двойную буферизацию (самим mysql и ОС).

Опции mysqld -MyIsam

'key_buffer_size' - размер кэша для хранения индексов;

'myisam_recover' -восстановление битых таблиц при старте сервера.

'myisam_repair_threads' - число потоков восстановления.

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

Опции mysqld - репликации, если используется

'server-id' - идентификатор сервера.

'log-bin' - место хранение bin-логов; лучше если это будет отдельный диск, высокие требования по скорости и надёжности, малые по объёму.

'expire_logs_days' - автоматическое удаление старых bin-логов.

'replicate-do-db' - реплицируемая БД.

'relay-log-space-limit' - ограничение на объём bin-логов.

Опции mysqld_safe

'open-files-limit' - лимит количества открытых файлов для ОС Linux.

'log-error' - файл для логирования ошибок.

'pid-file' - PID файл процесса.

Опции mysqldump

'max_allowed_packet' - установка маскимально возможного размера пакета при снятии дампов утилитой mysqldump.

'default-character-set' - кодировка по-умолчанию при снятии дампов БД.

Опции client

'default-character-set' - кодировка по-умолчанию при подключении консольным клиентом.

Ссылки

[http://mysql.ru/docs/man/ http://mysql.ru/docs/man/] - общее руководство по администрированию.

[http://mysql.ru/docs/man/InnoDB_start.html http://mysql.ru/docs/man/InnoDB_start.html] - параметры настройки InnoDb.

[http://mysql.ru/docs/man/Replication_Options.html http://mysql.ru/docs/man/Replication_Options.html] - параметры настройки репликации.

[http://maxq.ru/lib/53/ http://maxq.ru/lib/53/] - рекомендации по настройке InnoDb базы, MyIsam базы.

[http://mysqltuner.pl/mysqltuner.pl http://mysqltuner.pl/mysqltuner.pl] - скрипт автоматической настройки параметров БД.

http://ru.wikipedia.org/wiki/%D0%A3%D1%80%D0%BE%D0%B2%D0%B5%D0%BD%D1%8C_%D0%B8%D0%B7%D0%BE%D0%BB%D0%B8%D1%80%D0%BE%D0%B2%D0%B0%D0%BD%D0%BD%D0%BE%D1%81%D1%82%D0%B8_%D1%82%D1%80%D0%B0%D0%BD%D0%B7%D0%B0%D0%BA%D1%86%D0%B8%D0%B9

уровни изоляции транзакций

Набор Perl утилит для работы с MySQL

В архиве mysql_perl_utilites.zip размещены файлы:

'access.pm' - параметры доступа к MySQL для остальных скриптов.

'extract_myisam.pl' - выбор MyIsam таблиц из базы и вывод в файл 'tables'.

'convert.pl' - конвертация таблиц из файла 'tables' в InnoDb.

'drop.pl' - удаление таблиц, перечисленных в файле 'tables'.

Запросы

INSERT INTO … WHERE

http://stackoverflow.com/questions/485039/mysql-insert-where-query

JOINS

http://www.khankennels.com/blog/index.php/archives/2007/04/20/getting-joins/

DISABLE FOREIGN KEY - Отключение проверки на существование внешних ключей

SET FOREIGN_KEY_CHECKS = 0; UPDATE tbl_user SET id = 1 WHERE id = 133; UPDATE tbl_form SET user_id = 1 WHERE user_id = 133; SET FOREIGN_KEY_CHECKS = 1;

UPDATE tbl_table t SET t.title = 'YYY' WHERE t.id IN (SELECT id FROM tbl_table) - но так работать не будет, для таких обновлений нада использовать временную таблицу

UPDATE tbl_point p2 INNER JOIN tbl_point p1 ON p1.id = p2.id AND p1.location = '' SET p2.location = POINTFROMTEXT(CONCAT('POINT(',p1.latitude,' ',p1.longitude,')'));

Исправление ошибок

Исправление ошибок

MySAM

InnoDB

Оптимизация

<nspages -h1 -subns -exclude:start -textNS="Категории:" -textPages="Страницы:">