MySql Использование

Anonymous (anonymous@undisclosed.example.com) — 2021-10-13T10:41:13+00:00

MySql Использование

Оптимизация

Настройка 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
- MySQL - UPDATE query based on SELECT 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

Оптимизация

выбрать новости с определёнными id соблюдая порядок

Anonymous (anonymous@undisclosed.example.com) — 2016-08-11T12:19:50+00:00

выбрать новости с определёнными id соблюдая порядок

Задача: выбрать новости с id = [2, 10, 3, 88, 23] соблюдая порядок.

SELECT *
FROM news
WHERE id IN (2, 10, 3, 88, 23)
ORDER BY FIELD (id, 2, 10, 3, 88, 23)

Или

 ORDER BY FIND_IN_SET(id, '2,10,3,88,23')

Или

 ORDER BY id = 2 DESC, id = 10 DESC, id = 3 DESC, id = 88 DESC, id = 23 DESC

Библиотека знаний - sql

MySql Использование

MySql Использование

Оптимизация

Настройка MySQL

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

Опции mysqld - InnoDB:

Опции mysqld -MyIsam

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

Опции mysqld_safe

Опции mysqldump

Опции client

Ссылки

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

Запросы

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

MySAM

InnoDB

Оптимизация

выбрать новости с определёнными id соблюдая порядок

выбрать новости с определёнными id соблюдая порядок