20 советов по оптимальному использованию MySQL


Содержание

5 советов по использованию MySQL

1. Тип базы данных

В качестве типа базы данных рекомендуется выбирать InnoDB (можно выбрать при создании таблицы), которая позволяет использовать транзакции и внешние ключи.

С версии MySQL 5.5 данный тип базы данных используется по умолчанию.

MySQL не кэширует запросы с динамичными данными, например при использовании CURDATE() . Поэтому рекомендуется формировать данные для SQL-запроса (например дату) внешне от MySQL.

3. Данные банковских карт

В MySQL нельзя хранить данные банковских карт (например, VISA, MasterCard), и выводить их на сайте. Чтобы иметь возможно хранить и обрабатывать данные банковских карт на своём сервисе, надо пройти сертификацию PCI DSS.

4. Выборка всех полей

Не рекомендуется выбирать данные через символ * , т.к. идёт выборка ненужных полей, что может замедлить работу MySQL.

Рекомендуется использовать кодировку UTF-8, т.к. данная кодировка позволяет использовать различные языки (русский, английский, японский и др), а также использовать символы (стрелки, кавычки, смайлики).

Есть несколько типов кодировки UTF-8, рекомендуется использовать «utf8_general_ci», т.к. она не сильно требовательна к сравнению символов при поиске, из-за чего ускоряется выборка данных.

20 советов по оптимальному использованию MySQL

Это вот тоже из серии «вредные советы»
> 3. DATETIME / TIMESTAMP — Используйте TIMESTAMP, он занимает на диске меньше места.

TIMESTAMP совершенно НЕ предназначен для хранения даты, он предназначен для хранения UPDATED/CREATED.. И не надо его использовать для чего-либо другого. Никогда. Пожалуйста!

Из-за таких советов горе-программисты пишут такой код, который считает, что дату можно класть в TIMESTAMP. Ага, два раза. На андроиде в контакт-листе завести день рождения человека до 1970 года невозможно. Ну не рождались тогда, по мнению гугла! Совершенно реальное поле «день рождения», который оптимизаторы запихнули в аналог TIMESTAMP. Я хоть в IT разбираюсь, мне просто смешно. А вот что далекие от IT люди думают по поводу таких вот ограничений, интересно.

  • 4.4 , Анончик ( ? ), 01:37, 08/06/2011 [^] [^^] [^^^] [ответить]
  • + / –
    [quote]А вот что далекие от IT люди думают по поводу таких вот ограничений, интересно. [/quote]
    «Ух ты».
  • 4.5 , ws ( ok ), 11:18, 08/06/2011 [^] [^^] [^^^] [ответить]
  • + / –
    > Мешают неразумные люди, следующие советам, не подумав. Советам вроде этих. И лучше
    > всего не давать таких вот сомнительных советов. Ведь храня IP-адрес как
    > строку достаточной длины, проблемы уровня хранения потом не возникнет.

    Не согласен. Достоинства хранения IP в int более предпочтительные (объем хранимых данных,
    скорость выборки). А вот недостаток только тот о котором вы говорите, но это решаемо если разработчик оказался недостаточно предусмотрителен (ALTER TABLE. )
    Другими словами вы решаете в ущерб оптимизации возможные перспективы.
    Так давайте тогда все данные хранить в строковых типах так проще по вашей логике.

  • 5.9 , Dmitry ( ?? ), 13:07, 09/06/2011 [^] [^^] [^^^] [ответить]
  • + / –
    Достоинства хранения (да и вообще представления)ip в int более, чем просто сомнительны. Большинство программ ждут, что ip им будет передан как текстовый тип, некоторые готовы принять 4 бинарных октета, int для ip — экзотика. Да и по здравому размышлению не является он таким типом. Не умножайте сущности.
  • 6.10 , ws ( ok ), 19:15, 09/06/2011 [^] [^^] [^^^] [ответить]
  • + / –
    > Достоинства хранения (да и вообще представления)ip в int более, чем просто сомнительны.
    > Большинство программ ждут, что ip им будет передан как текстовый тип,
    > некоторые готовы принять 4 бинарных октета, int для ip — экзотика.
    > Да и по здравому размышлению не является он таким типом. Не
    > умножайте сущности.

    А вы не интересовались как сетевой стек ОС оперирует IP чтобы так утверждать? Да да! Использует все те же целые числа. Так кто плодит сущности?
    IP как мы привыкли видеть нужен только для человека — для удобства использования.

    Для тех программ (и людей тоже), которые хотят видеть в удобном представлении IP и были придуманы функции INET_ATON(), INET_NTOA() http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_

  • 7.22 , Антоним ( ? ), 00:04, 19/06/2011 [^] [^^] [^^^] [ответить]
  • + / –
    Что вы чушь несёте. Стек использует бинарные строки, но никак не ЗНАКОВЫЕ целые
  • 8.25, ws ( ok ), 18:46, 20/06/2011 [^] [^^] [^^^] [ответить]
  • + / –
  • 6.15 , Сергей ( ?? ), 03:05, 17/06/2011 [^] [^^] [^^^] [ответить]
  • + / –
    Сразу видно что не писали ничего серьезного с IP :) Еще одно преимущество это возможность быстрой выборки диапазона, например какие IP входят в определенную подсеть или в определенный диапазон. Делать INET_ATON на каждом поле при выборках хорошо? Мало того я даже MAC-адреса храню в виде INT64 и тоже только из за возможности выбирать диапазоны!
    А насчет v6 можно вобще использовать префикс + последние 4 октета в виде того же INT!
  • 4.6 , angra ( ok ), 13:41, 08/06/2011 [^] [^^] [^^^] [ответить]
  • + / –
    >Ведь храня IP-адрес как строку достаточной длины, проблемы уровня хранения потом не возникнет.

    И что будет достаточной длиной? Предусмотреть разную длину CHAR для IPv4 и IPv6 абсолютно то же самое, что и предусмотреть правильный размер INT для них же. А если писать с расчетом на светлое будущее, то вообще все нужно в TEXT хранить, вот только в суровом настоящем такой проект жрать место, работать будет как черепаха и до светлого будущего не доживет. Кстати как вы собираетесь сортировать или искать диапазоны IP в текстовом виде да еще сразу с учетом разного представления v6 и v4?

  • 5.8 , zoonman ( ok ), 20:27, 08/06/2011 [^] [^^] [^^^] [ответить]
  • + / –
    Плохо, что нет просто UDF типа данных IP-address. А уж там он хоть IPv8.
  • 4.7 , zoonman ( ok ), 20:25, 08/06/2011 [^] [^^] [^^^] [ответить]
  • + / –
    Дополню немного:

    The TIMESTAMP data type has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

    The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

    4.12 , Alex ( ?? ), 16:13, 14/06/2011 [^] [^^] [^^^] [ответить] + / –
    >На андроиде в контакт-листе завести день рождения человека до 1970 года невозможно.

    Уточните какая версия андроида, т.к. на 2.2.1 вполне нормально заносятся в диапазоне от 1902 до 2036

  • 5.16 , Stax ( ok ), 23:29, 17/06/2011 [^] [^^] [^^^] [ответить]
  • + / –
    2.2.2
    Из LG Optimus 2x.
  • 4.13 , Axel ( ?? ), 16:46, 15/06/2011 [^] [^^] [^^^] [ответить]
  • + / –
    Берём signed int и вполне себе записываем даты 5.19 , Stax ( ok ), 23:34, 17/06/2011 [^] [^^] [^^^] [ответить] + / –
    > Берём signed int и вполне себе записываем даты
    > числа.

    Не шутите так :) Во-первых, нестандартно, во-вторых, ну выйграете несколько десятилетий — но даты и до этой бывают.

  • 2.27, Сергей ( ?? ), 02:15, 25/06/2011 [^] [^^] [^^^] [ответить]
  • + / –
    Не все пишут программы для работы в Интернет. Есть программы сбора данных для локальных сетей. Использовать IPv6 в этом случае неразумно, а тратить +12 байт впустую просто глупо — будет БД из одних IP.
  • 2.28, Дмитрий ( ?? ), 03:15, 06/11/2015 [^] [^^] [^^^] [ответить]
  • + / –
    Вот кстати IPv6 хранить в BINARY(16), есть специальные функции для работы с ними
    https://intsystem.org/coding/kak-rabotat-s-ipv6-v-php/

    INET6_ATON(expr) и INET6_NTOA(expr), но они доступны с версии MySQL 5.6.3. До этой версии есть специальное расширение.

    1.11 , Елка ( ? ), 21:06, 13/06/2011 [ответить] [﹢﹢﹢] [ · · · ] +1 + / –
    ip только в int!
    вы пробывали учитывать трафик по подсетям? ;)
    в строковом варианте это изврат
    2.18 , Stax ( ok ), 23:33, 17/06/2011 [^] [^^] [^^^] [ответить] + / –
    > ip только в int!
    > вы пробывали учитывать трафик по подсетям? ;)
    > в строковом варианте это изврат

    Ну, под специфические задачи можно использовать разные способы хранения. Если у вас IP используется для подсчета трафика, храните в INT, никто не запрещает :)

    Некоторые еще хранят в виде «C0A80201» в CHAR(8) — для удобства специфических действий.

    20 советов по оптимальному использованию MySQL

    База данных MySQL — легкая, надежная, но, вместе с тем, полнофункциональная серьезная СУБД. Простота MySQL обеспечивает низкий уровень вхождения разработчиков, однако впоследствии они не особо склонны менять стратегию работы с СУБД. Именно для разработчиков с опытом использования MySQL на практике ориентирована наша статья.

    Большая часть материала представляет собой перевод статьи 32 Tips To Speed Up Your MySQL Queries.

    1. Используйте постоянное соединение с базой данных, чтобы избежать системных издержек.
    2. Проверьте, чтобы на столбцах с высоким количеством уникальных элементов был PRIMARY KEY. Например, у столбца `gender` есть всего 2 варианта (male и female). Уникальный , я понял, что иногда необходимо де-нормализовать некоторые таблицы (кстати, так делает Flickr), то есть дублировать некоторые данные в нескольких таблицах, чтобы избежать JOIN’ов, которые могут дорого обойтись.
    3. Если вы хотите протестировать какую-то функцию или выражение в MySQL, используйте для этого BENCHMARK.

    Оптимизация MySQL

    Да уж, 9 лет занимаюсь веб-разработками, а в оптимизации сайтов и базы данных MySQL в частности, каждый раз нахожу для себя много нового. Проблема в том, что во время разработки сложно оценить все узкие места, они возникают когда в базе появляются тысячи, десятки тысяч или даже миллионы записей в таблицах. Тут я собрал разные полезные фишки и советы, связанные с оптимизацией MySQL открытые мной в последнее время.


    PROCEDURE ANALYSE()

    Стандартная функция, которая анализирует поля таблицы в запросе и выводит советы по оптимальной длине/типу полей. Например SELECT * FROM table PROCEDURE ANALYSE() .

    Известно, что использование типа полей занимающего большую память делает более медленной работу с таблицами. Enum быстрее чем Varchar, varchar быстрее чем text, medium int быстрее чем int и т.д. Часто можно оптимизировать типы в зависимости от данных в таблице.


    Определение не эффективных индексов

    Индексы в таблицах — большое благо, но не стоит забывать что кроме того, что индексы ускоряют выборки из таблицы (SELECT) они замедляют обновление таблиц (UPDATE) и добавление новых полей (INSERT), так как при каждом обновлении данных все индексы перестраиваются. Также индексы занимают место на диске. В таблицах где данные часто обновляются/добавляются использование индексов должно быть сбалансированным.

    С помощью этого хитрого запроса можно увидеть 10 самых малоэффективных индексов во всей базе данных :

    SELECT t.TABLE_SCHEMA AS `db`, t.TABLE_NAME AS `table`, s.INDEX_NAME AS `inde name`, s.COLUMN_NAME AS `field name`, s.SEQ_IN_INDEX `seq in index`, s2.max_columns AS `# cols`, s.CARDINALITY AS `card`, t.TABLE_ROWS AS `est rows`, ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `sel %` FROM INFORMATION_SCHEMA.STATISTICS s INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(SEQ_IN_INDEX) AS max_columns FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA != ‘mysql’ GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) AS s2 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s.TABLE_NAME = s2.TABLE_NAME AND s.INDEX_NAME = s2.INDEX_NAME WHERE t.TABLE_SCHEMA != ‘mysql’ AND t.TABLE_ROWS > 10 AND s.CARDINALITY IS NOT NULL AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01))

    Взято отсюда: http://pastebin.com/f6b1c381c. Уберите LIMIT 10, чтобы увидеть статистику по всем индексам.

    EXPLAIN – Определение используемых индексов и строк, которые нужно обработать MySQL базе данных для выполнения запроса.

    Очень просто, добавляем к нашему запросу слово EXPLAIN. Например: EXPLAIN SELECT * FROM table WHERE var=value ORDER BY field. Очень полезно для просмотра эффективности индексов.

    Полный отказ от ORDER BY rand();

    Конструкция ORDER BY rand(); отдаёт поля из таблицы в случайном порядке. Основная засада в том, что при выполнении этой конструкции не используются индексы и осуществляется полное сканирование таблицы. Уже при нескольких тысячах записей могут начаться проблемы. Разумно переписать код, чтобы все записи брались из базы данных, добавлялись в массив и сортировались в массиве (shuffle в PHP). Если записей очень много, имеет смысл сделать кеширование — например делать случайную сортировку раз в час.

    Отключить InnoDB если он не нужен

    Это движки таблиц. MyISAM – быстрее и проще, InnoDB – умеет много полезного, например транзакции, отсутствие блокировок. Если все эти фишки не нужны, то кто-то советует оставаться на MyISAM, кто-то наоборот переходить на InnoDB. Я пока ещё этот вопрос для себя не решил и использую MyISAM.

    В любом случае если InnoDB не используется стоит отключить его поддержку, т.к. сервер кушает лишнюю память и работает медленее. Для отключения добавляем skip-innodb в конфиг (/etc/mysql/my.cnf).

    Медленные запросы


    Некоторые запросы выполняются очень медленно. Скорей всего о большинстве них вы и не подозреваете, т.к. вы тестировали когда в таблице было несколько сотен записей, а сейчас там несколько десятков тысяч и т.д. Найти такие запросы, на которые стоит обратить самое пристальное внимание, можно если включить логгирование Slow Queries. В конфиг файле (/etc/mysql/my.cnf) прописываем:

    log_slow_queries = /var/log/mysql/mysql-bin.log
    long_query_time = 2

    2 – это количество секунд, больше которых выполняемый запрос будет считаться медленным и добавляться в лог. Если добавить ещё строчку log-queries-not-using-indexes в логи будут добавляться все запросы, для которых не использованы индексы.

    Затем перегружаем MySQL сервер (/etc/init.d/mysql restart) и в /var/log/mysql будут добавлятся файлы логов с медленными запросами за последние 7 дней (по умолчанию).

    Кеширование в MySQL

    Стоит конечно писать скрпиты так, чтобы запросов в базу данных было как можно меньше, но часть ваших проблем на себя может взять MySQL. Правильная настройка кеширования приведёт к тому, что повторяющиеся запросы будут браться из кеша и выполняться очень быстро. У меня например 75% запросов выполняется из кеша. Конечно, этот процент зависит от типа запросов, от размера самого кеша, от частоты запросов к базе данных и т.д. Обычно стандартная конфигурация не всегда подходит, т.к. для разной нагрузки нужны разные настройки. Чтобы узнать что менять, смотрим следующий пункт.

    Автоматический тюнинг

    С помощью простой утилитки mysqltuner можно обнаружить самые основные узкие места в конфигурации MySQL.

    Выполняем на сервере:

    wget http://mysqltuner.com/mysqltuner.pl
    chmod u+x mysqltuner.pl
    ./mysqltuner.pl

    Далее нужно ввести root логин и пароль к MySQL серверу, а скрипт проанализирует данные и выведет советы в автоматическом режиме. Я меняю параметры в конфиге, на которые нужно обратить внимание, а затем через несколько дней смотрю опять.

    Также полезные советы можно посмотреть в phpMyAdmin если нажать ссылку “Текущее состояние MySQL” на главной странице.

    Минимизирование изменения таблиц

    Добавление или изменения данных в таблицы приводит к тому, что перестраиваются все индексы и сбрасывается кэш таблицы. Хорошо, если в таблице немного записей. У меня в нескольких проектах есть таблицы в которых несколько миллионов записей, благодаря индексам выборки из таблиц происходят быстро, но при изменении таблицы и при одновременном обращении к ней, происходит резкое замедление выполнения выборок (запросы SELECT).

    Это я вот к чему, иногда для оптимизации работы с базой данных имеет смысл переделать логику работы скриптов. Например, я сейчас делаю кеширование, чтобы все обновления сохранялись в небольшой временной таблице, а добавление всех данных в большую таблицу (и её изменение) происходило только раз в сутки.

    Оптимизация таблиц

    Иногда следует выполнять OPTIMIZE TABLE, т.к. таблицы со временем фрагментируются из-за изменения, добавления данных и скорость доступа к ним, со стороны дисковой системы сервера уменьшается.

    Полезные ссылки по Оптимизации MySQL:

    PHP Profi

    Квест → Как хакнуть форму

    Оптимизация MySQL: индексы, медленные запросы, конфигурация Перевод

    MySQL по-прежнему является самой популярной в мире реляционной базой данных, но в то же время и наиболее не оптимизированной. Многие люди остаются с настройками по умолчанию, не «копая» глубже. В этой статье мы рассмотрим некоторые советы по оптимизации MySQL в сочетании с некоторыми новинками, которые вышли относительно недавно.

    Оптимизация конфигурации

    Первое, что каждый пользователь MySQL должен сделать для повышения производительности — это настроить конфигурацию. Однако, большинство этот шаг пропускают. В 5.7 (текущая версия) настройки по умолчанию стали намного лучше, чем у её предшественников, но улучшить их по-прежнему можно и несложно.

    Мы надеемся, что вы используете Linux или что-то вроде Vagrant-box (как наш Homestead Improved), а, соответственно, ваш конфигурационный файл будет находиться в /etc/mysql/my.cnf . Вполне возможно, что ваша установка на самом деле будет подгружать дополнительный файл конфигурации в этот. Так что посмотрите, если файл my.cnf содержит немного, то посмотрите в /etc/mysql/mysql.conf.d/mysqld.cnf .

    Ручной тюнинг

    Следующие настройки должны быть сделаны «из коробки». Согласно этим советам, добавьте в файл конфига в раздел [mysqld] :

    • innodb_buffer_pool_size . Пул буферизации (buffer pool) является эдаким «складом» для кэширования данных и индексов в памяти. Он используется, чтобы хранить часто используемые данные в памяти. И когда вы используете выделенный или виртуальный сервер, на котором зачастую именно БД является узким местом, то есть смысл отдать ей бОльшую часть оперативы. Следовательно, мы даем ей 50-70% всей RAM. В документации MySQL есть руководство по настройке этого пула.
    • innodb_log_file_size . Настройка размера лог-файла хорошо описана здесь, но в двух словах это количество хранимых данных в логах, прежде чем его почистят. Обратите внимание, что лог в этом случае – это не записи об ошибках, а некий дельта-слепок изменений, которые ещё не были сброшены на диск в основные файлы innodb. MySQL пишет в фоновом режиме, но это все же влияет на производительность в момент записи. Большой лог-файл означает более высокую производительность из-за малого количества создаваемых новых и небольших контрольных точек, но при этом более длительное время восстановления в случае краша (больше данных должно быть переписано в БД).
    • innodb_flush_log_at_trx_commit описан тут и показывает, что происходит с файлом логов. Значение 1 – самое безопасное, т. к. лог сбрасывается на диск после каждой транзакции. При значениях 0 и 2 – меньше гарантируется ACID, но больше производительность. Разница не является достаточно большой, чтобы перевесить преимущества стабильности при 1.
    • innodb_flush_method . В довершение всего того, что касается сброса данных, эту настройку нужно установить в O_DIRECT – чтобы избежать двойной буферизации. Советую всегда это делать, пока система ввода-вывода остаётся очень медленной. Хотя на большинстве хостингах, типа DigitalOcean, вы будете иметь SSD-диски, поэтому система ввода-вывода будет более производительна.

    Есть инструмент от Percona, который поможет нам найти оставшиеся проблемы автоматически. Обратите внимание, что если мы бы запустили его без этой ручной настройки, то только 1 из 4 настроек была бы определена, т. к. другие 3 зависят от предпочтений пользователя и окружающей среды приложения.

    Variable Inspector

    Установка variable inspector на Ubuntu:

    Для других систем, следуйте этим инструкциям.

    Затем запустите toolkit:

    Вы увидите такой результат:

    Прим. переводчика:
    На моей локальной машине, кроме этого, выдал ещё вот такой ворнинг:

    О том, что параметр innodb_flush_method нужно установить в O_DIRECT и почему говорилось выше. И если вы придерживались последовательности тюнинга как в статье, то вы не увидите это предупреждение.

    Ни одно из этих (прим.пер.: указанных автором) предупреждений не критично, их необязательно исправлять. Единственное, что можно поправить – это настройка бинарного лога для репликации и снапшотов.

    Примечание: в новых версиях размер binlog-а по умолчанию 1G и этого ворнинга не будет.

    • max_binlog_size . Определяет насколько большими будут бинарные логи. В них записываются ваши транзакции и запросы и делаются контрольные точки. Если транзакция превышает максимум, то лог может превышать свой размер при сохранении на диск; в противном случае MySQL будет поддерживать его в рамках этого лимита.
    • log_bin . Эта опция включает запись бинарных логов в целом. Без неё невозможны снапшоты или репликации. Обратите внимание, что это может очень сказаться на дисковом пространстве. server-id – это необходимая опция при включении бинарного лога, поэтому логи «знают» с какого сервера они пришли (для репликации), а binlog-format — это просто способ, которым они записываются.

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

    MySQL Tuner

    Tuner предназначен для мониторинга базы в более длительных интервалах (запускайте его раз в неделю или около того на живом приложении). Он будет рекомендовать изменения, основанные на том, что он увидел в логах.

    Установить его просто. Нужно просто скачать его:

    При запуске ./mysqltuner.pl он спросит вас имя пользователя и пароль администратора базы данных и выведет информацию быстрого сканирования. Например, вот мой раздел InnoDB:

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

    Убедитесь, что вы перезапустили MySQL после любого изменения конфигурации:

    Индексы

    Далее, обратим внимание на индексы – главная болевая точка многих админов БД любителей! Особенно тех, кто сразу стал использовать ORM и никогда не нюхал чистого SQL.

    Примечание: термины ключи и индексы могут быть использованы как взаимозаменяемые.

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

    Как вы можете себе представить, найти по оглавлению намного быстрее, чем пролистывать каждую страницу. Таким образом, добавление индексов в базу в целом ускоряет select-запросы. Однако, этот индекс должен быть создан и сохранен. А значит, запросы update и insert будут медленнее и это займёт немного больше места на диске. В целом вы не заметите разницы при обновлениях и вставках, если вы проиндексировали вашу таблицу правильно, а поэтому важно добавлять индексы в нужные места.

    Таблицы, которые содержат всего несколько строк, на самом деле нет смысла индексировать. Вы можете себе представить, что пролистать 5 страниц — это ненамного медленнее, чем сначала сходить в оглавление, получить номер страницы, а затем открыть эту страницу.

    Так как же мы узнаем, какие индексы нужно добавить, и какие виды индексов существуют?

    Уникальные/первичные индексы

    Первичные индексы являются основными индексами, которые используются по умолчанию при поиске по данным. Для учетной записи пользователя это могут быть идентификатор пользователя, или логин, или даже основной email. Первичные индексы являются уникальными. Уникальные индексы – это индексы, которые не могут иметь повторов на всём наборе данных.

    Например, если пользователь выбрал конкретный username, никто больше не может выбрать его. Добавление уникального индекса на столбец username решает эту проблему. MySQL будет «ругаться», если кто-то повторно попытается вставить строку с именем пользователя (username), которое уже существует.

    Первичные ключи/индексы, как правило, задаются при создании таблицы, а уникальные индексы позднее через изменение ( ALTER ) таблицы.

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

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

    Регулярные индексы

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

    Приведённый выше пример ускоряет поиск по имени пользователя в стране.

    Индексы также помогают увеличить скорость сортировок и группировок.

    Полнотекстовые индексы

    Полнотекстовые индексы ( FULLTEXT ) используются для полнотекстового поиска. Их поддерживают только InnoDB и MyISAM и только для столбцов с типами CHAR , VARCHAR и TEXT .

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

    Обратные индексы (по убыванию)

    Начиная с версии 8+, MySQL поддерживает обратные индексы, что означает, что он может хранить индексы в порядке убывания. Это может пригодиться, когда у вас есть огромные таблицы, из которых чаще всего нужны последние добавленные данные. Конечно, всегда можно отсортировать по убыванию, но это будет немного медленнее. А вот это еще больше ускорит.


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

    Вспомогательные инструменты: Explain

    Если вы смотрите в сторону оптимизации запросов, инструмент EXPLAIN будет бесценен. Запрос, впереди которого вы просто поставили EXPLAIN , будет обрабатываться не как запрос, а как анализ его исполнения. MySql отобразит вам анализ используемых индексов и покажет вам соотношение попаданий и промахов. А также сколько строк он должен пробежать/сравнить, чтобы получить результаты, которые вы ищете.

    Вы можете расширить отчёт с помощью EXTENDED :

    Подробнее вы можете узнать в документации.

    Вспомогательные инструменты: Percona Toolkit для выявления дублирующих индексов

    Ранее установленный нами Percona Toolkit также имеет инструмент для обнаружения дублирующих индексов, который может пригодиться при использовании сторонних CMS или просто проверить себя – вдруг вы случайно добавили больше индексов, чем нужно. Например, установка WordPress по умолчанию имеет дублирующие индексы в таблице wp_posts :

    Как видно из последней строки, этот инструмент также дает вам советы о том, как избавиться от повторяющихся индексов.

    Вспомогательные инструменты: Percona Toolkit для неиспользуемых индексов

    Percona Toolkit может также обнаружить неиспользуемые индексы. Если вы логируете медленные запросы (см. раздел «узкие места» ниже), вы можете запустить утилиту и она будет проверять, используют ли эти запросы индексы в таблицах и как именно.

    Подробную информацию об использовании этой утилиты см. здесь.

    Узкие места

    В этом разделе описывается, как обнаруживать и отслеживать узкие места в базе данных.

    Для начала, давайте включим логирование медленных запросов:

    Строки выше должны быть добавлены в конфигурацию mysql. БД будет отслеживать запросы, которые выполнялись больше чем 1 секунду, и те, которые не используют индексы.

    Как только в этом логе появятся некоторые данные, вы можете проанализировать их на предмет использования индексов с помощью вышеуказанной утилиты pt-index-usage или с помощью pt-query-digest , которая выведет примерно такие результаты:

    Если вы предпочитаете анализировать эти логи вручную, вы можете сделать то же самое, но сначала вам нужно экспортировать лог в более анализируемый формат. Это можно сделать так:

    С дополнительными параметрами можно отфильтровать данные, чтобы экспортировать только нужное. Например, топ-10 запросов, отсортированных по среднему времени выполнения:

    Остальные параметры см. в документации.

    Заключение

    В этом всеобъемлющем посте по оптимизации MySQL мы рассмотрели различные методы и техники, с помощью которых мы можем добиться, чтобы наш MySQL летал.

    Мы разобрались с оптимизацией конфигурации, мы прокачались по индексам, и мы избавились от некоторых узких мест. Все это было в основном теорией, однако, всё это применимо на реальных приложениях.

    Оптимизация производительности MySQL

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

    В сегодняшней статье мы поговорим о том, как выполняется оптимизация производительности mysql. Какие программы для этого лучше использовать и как это работает.

    Скорость работы MySQL

    Оптимизация без аналитики бессмысленна. Перед тем как переходить к оптимизации давайте посмотрим как работает база данных сейчас, есть ли запросы, которые выполняются очень медленно. Все настройки вашего сервиса mysql находятся в файле /etc/my.cnf. Чтобы включить отображение медленных запросов добавьте такие строки в my.cnf, в секцию [mysqld]:

    Здесь первая строка включает запись лога медленных запросов, вторая указывает, что минимальное время запроса для внесения его в этот лог — две секунды. Еще можно включить в лог запросы, которые не используют индексы:

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

    systemctl restart mariadb

    tail -f /var/log/mariadb/slow-queries.log

    Мы можем видеть, что есть запросы, которые выполняются больше, чем 10 секунд. Это, например, запрос

    SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’;

    Можно его выполнить отдельно, в консоли mysql:

    Здесь тоже измеряется время, и мы видим результат — три секунды. Это очень много. И еще ничего, если такие запросы приходят редко, если ваш сайт постоянно под нагрузкой, то тремя секундами вы не отделаетесь, количество необработанных запросов будет расти, а скорость ответа увеличиваться до нескольких минут. Можно пойти двумя путями — оптимизировать код, убрать сложные запросы, или же нужна оптимизация mysql на сервере.

    Оптимизация MySQL

    Конфигурация MySQL достаточно сложная, но, к счастью, вам не нужно в нее сильно углубляться. Есть специальный скрипт под названием MySQLTunner, который анализирует работу MySQL и дает советы какие параметры нужно изменить и какие значения для них установить. Скрипт поддерживает большинство версий MariaDB, MySQL и Percona XtraDB. Нам понадобится загрузить три файла с помощью wget:

    wget http://mysqltuner.pl/ -O mysqltuner.pl
    wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
    wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

    Первый из них — это сам скрипт, написанный на Perl, второй и третий — база данных простых паролей и уязвимостей. Они позволяют обнаружить проблемы с безопасностью. Дальше можно переходить к тестированию. Я использую сервер с настройками mysql по умолчанию, установленными панелью управления VestaCP.

    Буквально за несколько минут скрипт выдаст полную статистику по работе MySQL. Количеству запросов, занимаемому объему памяти и эффективности работы буферов. Вы можете ознакомиться со всем этим, чтобы лучше понять в чем причина проблем. Проблемные места обозначены красными восклицательными знаками. Например, здесь мы видим, что размер буфера движка таблиц InnoDB (InnoDB buffer pool) намного меньше, чем должен быть для оптимальной работы:

    Кроме того, в самом конце вывода утилита предоставит список рекомендаций как исправить ситуацию. Мы рассмотрим все сообщения утилиты из этого примера и почему нужно использовать именно их, а не другие.

    Все параметры нужно добавлять в /etc/my.cnf. Еще раз замечу, что вы не копируете статью, а смотрите что вам выдала утилита. Начнем с query-cache.

    query_cache_size=0
    query_cache_type=0
    query_cache_limit=1M

    Скрипт рекомендует отключить кэш запросов. Query Cache — это кэш вызовов SELECT. Когда базе данных отправляется запрос, она выполняет его и сохраняет сам запрос и результат в этом кэше. И все бы ничего, но при использовании его вместе с InnoDB при любом изменении совпадающих данных кэш будет перестраиваться, что влечет за собой потерю производительности. И чем больше объем кэша, тем больше потери. Кроме того при обновлении кэша могут возникать блокировки запросов. Таким образом, если данные часто пишутся в базу данных — его надежнее отключить.

    Оба параметра устанавливают размер памяти, которая используется для внутренних временных таблиц MySQL. Утилита рекомендует использовать объем больше 16 мегабайт, просто установите это ваше значение для обоих переменных, если у вас достаточно памяти, то можно выделить 32 или даже 64. Но важно чтобы оба значения совпадали, иначе будет использоваться минимальное.

    Этот параметр отвечает за количество потоков, которые будут закэшированны. После того, как работа с подключением будет завершена, база данных не разорвет его, а закэширует, если количество кэшированных потоков не превышает ограничение. Утилита рекомендует больше четырех, например, 16.

    Указывает, что не нужно пытаться определить доменное имя для подключений извне. Ускоряет работу, так как не тратится время на DNS запросы.

    Этот параметр определяет размер буфера InnoDB в оперативной памяти, от этого размера очень сильно зависит скорость выполнения запросов. Значение зависит от размера ваших таблиц и количества данных в них. Если памяти недостаточно, запросы будут обрабатываться дольше. У меня используется стандартный объем 128, а нужно больше 652.

    Размер файла лога innodb должен составлять 25% от размера буфера. В случае 800 мегабайт это будет 200М. Но тут есть одна проблема. Чтобы изменить размер лога нужно выполнить несколько действий. Поскольку мы изменили все нужные параметры перейдем к перезагрузке сервера. Для нашего лога нужно остановить сервис:

    systemctl stop mariadb

    Затем переместите файлы лога в /tmp:

    mv /var/lib/mysql/ib_logfile[01] /tmp

    И запустите сервис:

    systemctl start mariadb

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

    systemctl status mariadb

    Тестирование результата

    Готово оптимизация базы данных mysql завершена, теперь тестируем тот же запрос через клиент mysql:

    > USE база_данных;
    > SELECT option_name, option_value FROM wpfc_options WHERE autoload = ‘yes’;

    Первый раз он выполняется долго, может даже дольше чем обычно, но все последующие разы буквально мгновенно. Результат с более 3 секунд до 0,15. А если брать статистику из slow-log, то от более 12. Если в выводе утилиты для вас были предложены и другие оптимизации, то их тоже стоит применить.

    Выводы

    Как видите, оптимизация mysql это достаточно просто благодаря такому скрипту, но, в то же время, такая операция может очень сильно помочь, особенно высоконагруженным проектам. Еще лучше ускорить работу может только оптимизация запросов mysql. Не забывайте время от времени проверять параметры, чтобы быть уверенным что все в порядке. Если у вас остались вопросы, спрашивайте в комментариях!

    На завершение лекция про производительность MySQL от Percona:

    Массовая оптимизация таблиц Mysql

    Из коробки MySQL использует очень консервативные показатели памяти, поэтому он будет работать везде, каждый раз его нужно настраивать перед началом работы Используйте инструменты анализа, такие как EXPLAIN, чтобы увидеть, сколько индексов попадает в запрос. Если вы можете уменьшить количество индексов, которые нужно изменить, это увеличит производительность вашего обновления Есть способы перестроить или оптимизировать сами индексы Перемещение базы данных может быть рассмотрено, но вы сначала хотите понять проблему и атаковать ваши легкие победы. Базы данных MySQL намного больше, чем у вас, которые работают комфортно, но вы не достигли предела самой технологии. Как и в случае большинства проблем с производительностью — хотя это не всегда самое лучшее решение, его обычно можно решить путем настройки или добавления кэша. Помимо прочего, правильно ли проиндексированы ваши таблицы на table.

    This article describes how to build partitioned tables for fast parallel bulk importing of data to a SQL Server database. При загрузке и передаче больших данных в базу данных SQL с помощью секционированных таблиц и представлений можно оптимизировать импорт информации в эту базу, а также выполнение последующих запросов. Создание новой базы данных и набора групп файловCreate a new database and a set of filegroups Создайте базу данных , если она еще не существует. Добавьте группы файлов базы данных в базу данных, которая содержит секционированные физические файлы.

    База данных MySQL занимает много места?

    Статья обновлена Всем привет! Так получается, что со временем, по мере наполнения блога новыми постами и с появлением новых комментариев снижается свободное дисковое пространство на сервере. И часто виной тому бывает несоразмерно объемная база данных. Посмотрите на свою базу данных, какая из таблиц занимает больше всего места? В прошлый раз мы рассмотрели какие стандартные таблицы присутствуют сразу после установки WordPress, до установки плагинов.

    То есть абсолютно все комментарии, даже помеченные как спам, несут с собой ненужную информацию. А после очистки корзины и спама эти мета-данные не удаляются и продолжают занимать место.

    Вот, посмотрите сколько спама принял на себя блог: Подумать только, более 71 тысячи! И все эти данные до сих пор хранятся в базе данных, не мудрено что она так неприлично разрослась: По сравнению с другими таблицами она имеет наибольший размер — 5. Зачем уменьшать размер базы данных? Странный вопрос, естественно для экономии ресурсов сервера, это и свободное дисковое пространство, и время, затраченное на обращение в данным.

    Очень часто чрезмерно большая база данных может привести к внутренней ошибке сервера Internal Server Error. Требуемый результат достигнут, таблица уменьшилась в размере почти в 5 раз! Теперь она весит 1,1 Мб, вместо 5. Ну вот, пройдет очередной этап, но на этом оптимизация WordPress не заканчивается, подписывайтесь на обновления блога, чтобы не пропустить очередной пост!


    Оптимизация таблиц БД через PhpMyAdmin. Руководство по проведению дефрагментации индексов таблиц в phpMyAdmin. MySQL Базы данных. С одной стороны, это кажется немного сложным вопросом. Разумный ответ: используйте массовую утилиту импорта, такую ​​как MySQL mysqlimport.

    Статья обновлена Всем привет! Так получается, что со временем, по мере наполнения блога новыми постами и с появлением новых комментариев снижается свободное дисковое пространство на сервере. И часто виной тому бывает несоразмерно объемная база данных. Посмотрите на свою базу данных, какая из таблиц занимает больше всего места? В прошлый раз мы рассмотрели какие стандартные таблицы присутствуют сразу после установки WordPress, до установки плагинов. То есть абсолютно все комментарии, даже помеченные как спам, несут с собой ненужную информацию. А после очистки корзины и спама эти мета-данные не удаляются и продолжают занимать место. Вот, посмотрите сколько спама принял на себя блог: Подумать только, более 71 тысячи! И все эти данные до сих пор хранятся в базе данных, не мудрено что она так неприлично разрослась: По сравнению с другими таблицами она имеет наибольший размер — 5.

    Сначала одно колесо снял, потому крышу спилил, затем мотор… В общем, сейчас он пешком ходит. Это все последствия неправильного подхода!

    Массовая вставка MySql из огромного массива: вопрос оптимизации Мне было предложено выбрать, какой из трех вариантов лучше всего выбрать с точки зрения оптимизации ресурсов. Предположим, у меня есть большой файл Excel из тысяч записей, и мне нужно извлечь эти данные и вставить их в базу данных.

    Оптимизация таблиц БД через PhpMyAdmin

    Забота о том, что ваши базы данных MySQL работают настолько хорошо, насколько это вообще возможно — наиважнейший аспект залога хорошей работы растущего приложения. В данной серии статей мы разберемся, как можно оптимизировать работу наших баз данных. Мы рассмотрим, какие оптимизации мы можем применить к нашим базам данных, к настройкам MySQL и как находить потенциальные проблемы, если MySQL не справляется с возложенными на нее обязанностями. Для работы с базой данных мы будем использовать некоторые инструменты из Percona Toolkit. Данная статья фокусируется на улучшении работы индексов. Введение в индексы Если до сих пор вы не имели дела с индексами MySQL, то, скорее всего, у вас либо небольшие базы данных, либо у них проблемы с производительностью.

    Массовая вставка MySql из огромного массива: вопрос оптимизации

    Игорь Серов Вступление По умолчанию, система не удаляет ревизии статей и страниц, неиспользуемые теги, элементы спама. Не всегда удаляет следы плагинов в базе данных и не часто удаляет материалы из корзины. Это приводит к разрастанию базы данных сайта и требует работ по её очистке. Что такое оптимизация базы данных WordPress Работая по созданию и администрированию сайта WordPress, приходится удалять ненужный материал, менять плагины, удалять не актуальные метки и категории. В системе есть все необходимые инструменты для этого. Однако, не всё и не всегда удаляется из базы данных сайта. Например, в базе копятся промежуточные ревизии постов и страниц, материалы в корзине по умолчанию 90 дней , неиспользуемые теги, могут оставаться таблицы удаленных плагинов. Работу по очистке базы данных с целью уменьшения её объема называют оптимизация базы данных. Здесь повторяться не буду, лишь замечу, что ручная чиста неудобна. Упрощать, автоматизировать очистку базы данных можно при помощи ряда плагинов и чём, собственно эта статья.

    При этом мало кто из авторов проверяет реальную полезность полученных советов. Это вдвойне странно, потому как возможность проверить правильность той или иной идеи и сравнить разные мнения по одному и тому же вопросу в Интернете представлена очень широко.

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

    Работа с Mysql в PhpMyAdmin

    MySQL ориентирована в первую очередь на управление с помощью командной стороки или файлов конфигурации, и все настройки и операции приходилось исполнять через консоль или telnet, если сервер был установлен на удаленном компьютере. MySqlManager — это простейшая графическая утилита для работы с таблицами БД. Все базы, таблицы и поля отображаются в виде древовидного списка, но нет никаких средств удобного просмотра содержимого таблиц или изменения свойств полей. Для всех операций предусмотрено только интерактивное окно работы с SQL запросами причем оно, видимо, «живет» своей жизнью: в контекстном меню для каждой БД есть команда «SQL query», которая вызывает окно запроса, но всегда в теле SQL надо прописывать базу и таблицу, к которой обращаемся. Для сколь либо серьезной работы эта утилита совершенно непригодна. Программа отображает сведения о работе сервера, время работы, переменные среды, список доступных баз данных и таблиц без возможности редактирования или просмотра , а также позволяет перезапускать сервер. На отдельную вкладку вынесены настройки производительнсти и редактирования конфигурационного файла my. Она предназначена как раз для администрирования — управления пользователями и правами доступа, а также позволяет сделать ряд глобальных операций над сервером, вроде просмотра переменных, текущих подключений и статистики по каждой базе. Но следует отметить, что MDI-интерфейс не удобен для повседневной работы, да и функциональности недостаточно. Этих утилит не хватало для полноценной работы с сервером, поэтому пользователи чаще всего использовали средства других разработчиков. Но это довольно узкое решение, так как для работы необходм веб-сервер с поддержкой PHP и не все операции по администрированию именно сервера, а не отдельных баз данных, можно делать через веб-интерфейс. Вряд ли я ошибусь, когда скажу, что пользователи MySQL с нетерпением ждали, когда же компания выпустит собственную утилиту для полноценного администрирования СУБД. Недавно такая программа была анонсирована, а сейчас вышел первый релиз.

    20 советов по оптимальному использованию MySQL

    Оптимизация — сложная задача, потому что она, в конечном счете, требует понимания системы в целом. В отдельных случаях для выполнения локальной оптимизации достаточно знать систему или приложение не в полном объеме, однако чтобы сделать систему более оптимальной, нужно разбираться в том, как она устроена.

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

    5.1. Oбзор оптимизации

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

    Ниже приведен список наиболее часто встречающихся «узких мест»:

    Поиск данных на диске. Чтобы найти на диске какой-то фрагмент данных, требуется некоторое время. Для устройств выпуска 1999 года среднее время поиска составляет менее 10мс, так что теоретически можно выполнять приблизительно 100 операций поиска в секунду. Это время можно ненамного уменьшить, заменив диски более новыми. Для одной таблицы поиск на диске оптимизировать очень сложно. Такую оптимизацию можно выполнить путем распределения данных по нескольким дискам.

    Дисковое чтение/запись. После выполнения поиска, когда найдена соответствующая позиция на диске, мы можем считать данные. Для устройств выпуска 1999 года производительность одного диска составляет около 10-20Мб/с. Дисковое чтение/запись легче оптимизировать, чем дисковый поиск, поэтому читать можно параллельно с нескольких дисков.

    Циклы процессора. Когда мы помещаем данные в основную память (или если они уже находятся там), мы должны обработать их, чтобы получить результат. Наличие маленьких по сравнению с объемом ОЗУ таблиц — наиболее часто встречающийся лимитирующий фактор. Но в этом случае, в общем-то, скорость обработки маленьких таблиц значения не имеет.

    Пропускная способность ОЗУ (memory bandwidth). Когда процессору требуется больше данных, чем может вместить его кэш, узким местом становится пропускная способность памяти. В большинстве систем это узкое место встречается редко, однако о нем нужно знать.

    5.1.1. Конструктивные ограничения MySQL/компромиссы

    При использовании обработчика таблиц MyISAM MySQL применяет очень быструю блокировку таблиц (несколько потоков чтения/один поток записи). Самая большая проблема при использовании этого типа таблиц возникает в случае непрерывного потока обновлений в сочетании с медленными выборками из одной и той же таблицы. Если эта проблема касается лишь некоторых таблиц, можно использовать вместо них таблицы другого типа. See Глава 7, Типы таблиц MySQL.

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

    Все столбцы имеют значения по умолчанию.

    Если в столбец вставляется «неправильное» значение (например, NULL в столбец NOT NULL или слишком большое числовое значение — в числовой столбец), MySQL не будет выводить сообщение об ошибке, а просто поместит в столбец «наиболее подходящее возможное значение». Для числовых значений это 0, наименьшие возможные значения или наибольшее возможное значение. Для строк это либо пустая строка, либо самая длинная строка, которая может быть в столбце.

    Все вычисляемые выражения возвращают значение, которое можно использовать вместо того, чтобы сигнализировать об ошибке. Например, выражение 1/0 возвратит NULL

    Существование приведенных выше правил объясняется тем, что перед началом выполнения запроса невозможно проверить, сможет ли он выполниться. Если проблема обнаружится после обновления нескольких строк, мы не можем выполнить полный откат, поскольку это может не поддерживаться типом таблицы. Остановиться в этот момент тоже нельзя, потому что тогда обновления будут выполнены наполовину, что является, вероятно, самым худшим возможным результатом. В данном случае лучше выбрать «наименьшее из зол», а затем продолжать, как будто ничего не произошло.

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

    5.1.2. Вопросы переносимости

    Поскольку все SQL-серверы поддерживают разные части стандарта SQL, то разработка переносимых SQL-приложений занимает время. Для очень простых запросов/вставок это достаточно просто, однако чем сложнее становится ваше приложение, тем сложнее делать запросы переносимыми. Если вы хотите чтобы ваше приложение работало максимально быстро с разными серверами SQL, задача еще более усложняется.

    Чтобы сделать сложное приложение переносимым в области SQL, вам следует выбрать те SQL-серверы, с которыми оно должно работать.

    Чтобы узнать, какие функции, типы и ограничения существуют в выбранных вами серверах, можно воспользоваться приложением MySQL crash-me . crash-me пока еще далека от того, чтобы тестировать все, что возможно, но тем не менее, является достаточно качественным сравнительным тестом по более чем 450 характеристикам.

    Например, если вы хотите использовать Informix или DB2, имена полей не должны быть длиннее 18 символов.

    И тесты MySQL (MySQL benchmarks), и программа crash-me являются достаточно независимыми от конкретной СУБД. Ознакомившись с тем, как мы решили этот вопрос, вы можете получить представление о том, как следует писать переносимые программы для работы с базами данных. Тесты можно найти в каталоге sql-bench в поставке исходных текстов MySQL. Они написаны на Perl с использованием интерфейса DBI (который, кстати, уже решает проблему получения доступа к разным базам данных).

    Как можно видеть по этим результатам, у каждой СУБД есть свои слабые стороны. Все они построены по-разному и спроектированы с учетом различных компромиссов, что приводит к различиям в поведении этих систем.

    Если независимость от СУБД для вас очень важна, вам нужно хорошо ощущать, где находятся слабые места в каждом сервере. MySQL — очень быстрый сервер, если речь идет о выборках/вставках, но у нас все еще есть проблемы, когда с одной таблицей в смешанном режиме работают медленные клиенты, делающие выборки и обновления. С другой стороны, при работе в Oracle возникают большие проблемы, когда вы хотите получить доступ к строке, которую только что обновили (до тех пор, пока она не будет сохранена на диске). Транзакционные базы данных обычно не очень подходят для генерации отчетов по файлам журналов, так как в этом случае блокировки совершенно бесполезны.

    Чтобы сделать свое приложение действительно не зависящим от СУБД, вам следует создать некий быстро расширяемый интерфейс, через который происходит обработка данных. Поскольку C++ доступен на большинстве систем, имеет смысл создать соответствующие классы-интерфейсы к базам данных.

    Если вы используете некоторые специфические функции СУБД (скажем, REPLACE в MySQL), вам следует написать код, реализующий этот метод для других серверов SQL. С MySQL вы можете использовать такой синтаксис для того, чтобы добавить некоторые специфические для MySQL ключевые слова в запрос: /*! */ . Код внутри /* */ будет проигнорирован как комментарий большинством других SQL-серверов.

    Если скорость важнее точности данных, как в некоторых веб-приложениях, то тогда можно создать промежуточный уровень, который кэширует запросы и таким образом дает еще больший выигрыш по скорости. Убирая некоторые запросы из кэша по истечении времени, вы можете держать кэш в достаточно «свежем» состоянии. Таким образом можно избежать пиков повышения нагрузки на сервер, т.к. вы можете динамически увеличить кэш и продолжительность жизни информации, и сохранять эти параметры таковыми, пока ситуация не стабилизируется.

    В этом случае структура таблицы должна содержать информацию об изначальном размере кэша и то, как часто таблица должна быть обновлена в общем случае.

    5.1.3. Для чего мы использовали MySQL?

    На первых этапах развития MySQL его функциональные возможности разрабатывались под потребности самого крупного из наших заказчиков. Это делалось для обслуживания больших хранилищ данных для пары самых крупных продавцов в Швеции.

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

    Объем этих данных весьма значителен (в сумме приблизительно 7 миллионов транзакций в месяц), и, кроме того, мы должны предоставлять пользователям данные за периоды от 4 до 10 лет. Каждую неделю мы получаем от клиентов просьбы предоставить «мгновенный» доступ к новым отчетам на основе этих данных.

    Эта проблема была решена следующим образом. Мы сохраняем всю информацию за месяц в сжатых таблицах «транзакций». У нас есть набор простых макросов (сценарий), генерирующий итоговые таблицы, сгруппированные по различным критериям (группа изделий, идентификатор заказчика, хранилище. ) из таблиц транзакций. Отчеты — это веб-страницы, динамически генерирующиеся небольшим сценарием на Perl, который просматривает веб-страницу, выполняет SQL-операторы, содержащиеся в ней и вставляет результаты. Для этих целей можно было бы использовать PHP или модуль mod_perl , но в то время этих средств еще не существовало.

    Для графических данных мы написали простой инструмент на C, который может создавать GIF-файлы на основе результата SQL-запроса (определенным образом обработав результат). Это также динамически выполняется из создаваемой Perl’ом странички.

    В большинстве случаев новый отчет может быть создан просто путем копирования существующего сценария и модифицирования SQL-запроса в нем. Иногда требуется дополнительно добавить поля в существующую итоговую таблицу или сгенерировать новую таблицу, но это также делается очень просто, поскольку у нас все транзакционные таблицы хранятся на диске (в настоящее время у нас имеется меньшей мере 50Гб транзакционных таблиц и 200Гб других клиентских данных.)

    Кроме того, мы обеспечиваем для наших клиентов возможность обращаться к итоговым таблицам непосредственно через интерфейс ODBC; таким образом, продвинутые пользователи могут самостоятельно экспериментировать с данными.

    У нас не было каких-либо проблем при обработке этих данных на весьма скромном Sun Ultra SPARCstation (2×200 МГц). Недавно мы заменили один из наших серверов на двухпроцессорный UltraSPARC с тактовой частотой 400 МГц и теперь планируем начать обрабатывать транзакции на уровне продукта, что будет означать десятикратное увеличение объема данных. Мы полагаем, что сможем справиться с этим объемом лишь только добавлением соответствующего количества дисков.

    Помимо этого мы экспериментируем с Intel-Linux, чтобы получить больше производительности по низшей цене. Теперь, имея бинарно-переносимый формат базы данных (появившийся в версии 3.23), мы начнем использовать его для некоторых частей приложения.

    Наша интуиция подсказывает, что у Linux производительность значительно выше при низкой и средней загрузке, а у Solaris — когда высокая загрузка начнет возникать из-за критического дискового ввода-вывода. Но у нас нет пока никаких выводов по этому поводу. После обсуждения с разработчиками ядра Linux мы выяснили, что в это может быть побочным эффектом работы ядра: когда Linux дает слишком много ресурсов пакетным заданиям, задачи взаимодействия начинают замедляться. Из-за этого машина работает очень медленно и не реагирует ни на что, пока обрабатываются большие пакеты. Надеемся, что в последующих ядрах Linux этот вопрос найдет свое решение.

    5.1.4. Набор тестов MySQL (The MySQL Benchmark Suite)

    В данном разделе будет находиться техническое описание набора эталонных тестов MySQL (и crash-me ), но оно пока еще не написано. В настоящее время можно получить хорошее представление об эталонном тесте, глядя на код и результаты в каталоге sql-bench любого исходного дистрибутива MySQL.

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

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

    Например (выполнено на одной машине под NT 4.0):

    Чтение 2000000 строк по индексу Секунды Секунды
    mysql 367 249
    mysql_odbc 464
    db2_odbc 1206
    informix_odbc 121126
    ms-sql_odbc 1634
    oracle_odbc 20800
    solid_odbc 877
    sybase_odbc 17614
    Вставка 350768 строк Секунды Секунды
    mysql 381 206
    mysql_odbc 619
    db2_odbc 3460
    informix_odbc 2692
    ms-sql_odbc 4012
    oracle_odbc 11291
    solid_odbc 1801
    sybase_odbc 4802

    В предыдущем тесте MySQL запускался с 8-мегабайтным индексным кэшем.

    Гораздо больше результатов тестов вы сможете найти по адресу http://www.mysql.com/information/benchmarks.html.

    Обратите внимание: данные об Oracle отсутствуют — по просьбе компании Oracle вся информация по их продукту была удалена. Все эталонные тесты для Oracle должны быть пропущены через компанию Oracle! Мы считаем, однако, что при таком способе тестирования результаты эталонных тестов для Oracle будут в очень высокой степени различаться, поскольку приведенные на сайте результаты призваны показывать на что способна стандартная инсталляция для одного клиента.

    Чтобы выполнить набор эталонных тестов, необходимо загрузить исходный дистрибутив MySQL, установить драйвер perl DBI, драйвер perl DBD для той базы данных, которую нужно проверить, а затем выполнить:

    где # — один из поддерживаемых серверов. Список всех опций и поддерживаемых серверов можно получить, выполнив run-all-tests —help .

    Программа crash-me пытается определить, какие функции поддерживаются СУБД, и какие возможности и ограничения имеют эти функции при выполнении запросов. Например, она определяет следующее:

    какие типы столбцов поддерживаются

    сколько индексов поддерживается

    какие функции поддерживаются

    насколько большим может быть запрос


    насколько большим может быть столбец VARCHAR

    Результат, полученные crash-me для большого количества различных СУБД, можно найти по адресу: http://www.mysql.com/information/crash-me.php.

    5.1.5. Использование собственных тестов

    Чтобы найти «узкие места» в своем приложении и базе данных, вы должны их тщательно протестировать. После устранения «узкого места» (или после замены его некой заглушкой) можно легко идентифицировать следующее «узкое место» (и так далее). Даже если общая производительность приложения достаточна, нужно по крайней мере выявить все «узкие места» и определиться с тем, как их устранять, — на будущее, если когда-нибудь потребуется дополнительная производительность.

    Примеры переносимых программ программы для эталонного тестирования можно найти в наборе тестов MySQL. See Раздел 5.1.4, «Набор тестов MySQL (The MySQL Benchmark Suite)». Можно взять любую программу из этого набора и модифицировать ее для своих потребностей. Таким образом можно испытывать различные решения проблемы и проверять, которое из них самое быстрое.

    Зачастую некоторые проблемы проявляются только тогда, когда система очень сильно загружена. К нам часто обращаются клиенты, которые, запустив (протестированную) систему в производство, сталкиваются с проблемами, связанными с нагрузкой. На сегодня причиной каждого из этих случаев были либо проблемы, связанные с базовой конструкцией (при высокой нагрузке нехорошо выполняется сканирование таблиц), либо проблемы ОС/библиотек. И большинство таких проблем было бы намного легче устранить до начала промышленной эксплуатации систем.

    Чтобы избежать подобных проблем, нужно постараться выполнить эталонное тестирование всего приложения при самой плохой возможной нагрузке! Для этого можно использовать программу Super Smack , которая доступна по адресу: http://www.mysql.com/Downloads/super-smack/super-smack-1.0.tar.gz. Эта программа, как следует из ее имени ( smack — шлепок — прим. пер. ), способна поставить систему на колени, так что используйте её только на разрабатываемых системах (проще говоря, в девелопменте).

    5.2. Оптимизация SELECT и других запросов

    Сначала приведем одно правило, касающееся всех запросов: Чем сложнее ваша система привилений, тем больше издержек.

    Если не было выполнено никаких операторов GRANT , MySQL каким-то образом будет оптимизировать проверку полномочий. Таким образом при наличии очень большого объема данных лучше, наверное, будет работать без привилегий. В противном случае при большом количестве полномочий проверка результатов будет происходить с увеличенными издержками.

    Если проблема состоит в некоторой явной функции MySQL, всегда можно протестировать ее в клиенте:

    Из приведенного выше примера видно, что MySQL может выполнять более 1000000 операций сложения за 0,32 секунды на PentiumII 400MHz.

    Все функции MySQL достаточно хорошо оптимизированы, но могут попадаться некоторые исключения, и функция BENCHMARK(число_циклов,выражение) — хороший инструмент для выяснения, присутствует ли проблема в запросе.

    5.2.1. Синтаксис оператора EXPLAIN (получение информации о SELECT )

    EXPLAIN имя_таблицы является синонимом операторов DESCRIBE имя_таблицы и SHOW COLUMNS FROM имя_таблицы .

    Если оператор SELECT предваряется ключевым словом EXPLAIN , MySQL сообщит о том, как будет производиться обработка SELECT , и предоставит информацию о порядке и методе связывания таблиц.

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

    Вы должны почаще запускать ANALYZE TABLE с тем, чтобы обновлялась статистика по таблицам, такая как кардинальность ключей, которые могут повлиять на выбор оптимизатора. See Раздел 4.5.2, «Синтаксис команды ANALYZE TABLE ».

    Можно проверить, насколько удачный порядок связывания таблиц был выбран оптимизатором. Заставить оптимизатор связывать таблицы в заданном порядке можно при помощи указания STRAIGHT_JOIN .

    Для непростых соединений EXPLAIN возвращает строку информации о каждой из использованных в работе оператора SELECT таблиц. Таблицы перечисляются в том порядке, в котором они будут считываться. MySQL выполняет все связывания за один проход (метод называется «single-sweep multi-join»). Делается это так: MySQL читает строку из первой таблицы, находит совпадающую строку во второй таблице, затем — в третьей, и так далее. Когда обработка всех таблиц завершается, MySQL выдает выбранные столбцы и обходит в обратном порядке список таблиц до тех пор, пока не будет найдена таблица с наибольшим совпадением строк. Следующая строка считывается из этой таблицы и процесс продолжается в следующей таблице.

    В MySQL 4.1, вывод EXPLAIN был изменен с тем, чтобы работать лучше с конструкциями типа UNION , подзапросами, и наследованными (вторичными, derived) таблицами. Наиболее заметным изменением стало введение двух новых столбцов: id и select_type .

    Вывод команды EXPLAIN включает следующие столбцы:

    Идентификатор SELECT , последовательный номер этого конкретного SELECT в запросе.

    Тип оператора SELECT , который может быть один из следующих:

    Простая выборка ( SELECT без UNION или подзапросов).

    Второй и дальнейшие UNION SELECT .

    Второй и дальнейшие UNION SELECT , зависящие от внешнего подзапроса.

    Первый SELECT в подзапросе.

    Первый SELECT , зависящий от внешнего подзапроса.

    Наследованная (вторичная) таблица SELECT .

    Таблица, к которой относится выводимая строка.

    Тип связывания. Ниже перечислены различные типы связывания, упорядоченные от лучшего к худшему:

    Таблица содержит только одну строку (= системная таблица). Это — частный случай типа связывания const .

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

    Для каждой комбинации строк из предыдущих таблиц будет cчитываться одна строка из этой таблицы. Это наилучший возможный тип связывания среди типов, отличных от const . Данный тип применяется, когда все части индекса используются для связывания, а сам индекс — UNIQUE или PRIMARY KEY .

    Из этой таблицы будут считываться все строки с совпадающими значениями индексов для каждой комбинации строк из предыдущих таблиц. Тип ref применяется, если для связывания используется только крайний левый префикс ключа, или если ключ не является UNIQUE или PRIMARY KEY (другими словами, если на основании значения ключа для связывания не может быть выбрана одна строка). Этот тип связывания хорошо работает, если используемый ключ соответствует только нескольким строкам.

    При помощи индекса для выборки строк будут извлечены только строки, находящиеся в заданном диапазоне. Используемый индекс указывается в столбце key . Столбец key_len содержит самую длинную часть ключа, которая была использована. Столбец ref будет содержать значения NULL для этого типа.

    Данный тип аналогичен ALL , за исключением того, что просматривается только дерево индексов. Этот тип обычно более быстрый чем ALL , поскольку индексный файл, как правило, меньше файла данных.

    Для каждой комбинации строк из предыдущих таблиц будет производиться полный просмотр этой таблицы. Это обычно плохо, если таблица — первая из не отмеченных как const , и очень плохо во всех остальных случаях. Как правило, можно избегать типа связывания ALL — путем добавления большего количества индексов таким образом, чтобы строка могла быть найдена при помощи константных значений или значений столбца из предыдущих таблиц.

    Столбец possible_keys служит для указания индексов, которые может использовать MySQL для нахождения строк в этой таблице. Обратите внимание: этот столбец полностью независим от порядка таблиц. Это означает, что на практике некоторые ключи в столбце possible_keys могут не годиться для сгенерированного порядка таблиц.

    Если данный столбец пуст, то никаких подходящих индексов не имеется. В этом случае для увеличения производительности следует исследовать выражение WHERE , чтобы увидеть, есть ли в нем ссылки на какой-либо столбец (столбцы), которые подходили бы для индексации. Если да, создайте соответствующий индекс и снова проверьте запрос при помощи оператора EXPLAIN . See Раздел 6.5.4, «Синтаксис оператора ALTER TABLE ».

    Чтобы увидеть, какие индексы есть в таблице, используйте команду SHOW INDEX FROM имя_таблицы .

    Столбец key содержит ключ (индекс), который MySQL решил использовать в действительности. Если никакой индекс не был выбран, ключ будет иметь значение NULL . Чтобы заставить MySQL применить индекс из possible_keys , следует использовать оператор USE INDEX/IGNORE INDEX в запросе. See Раздел 6.4.1, «Синтаксис оператора SELECT ».

    Также, выполнение myisamchk —analyze (see Раздел 4.4.6.1, «Синтаксис запуска myisamchk ») или ANALYZE TABLE (see Раздел 4.5.2, «Синтаксис команды ANALYZE TABLE ») по таблицам даст возможность оптимизатору принимать более правильные решения.

    Столбец key_len содержит длину ключа, которую решил использовать MySQL. Если key имеет значение NULL , то длина ключа ( key_len ) тоже NULL . Обратите внимание: по значению длины ключа можно определить, сколько частей составного ключа в действительности будет использовать MySQL.

    Столбец ref показывает, какие столбцы или константы используются с ключом, указанным в key , для выборки строк из таблицы.

    В столбце rows указывается число строк, которые MySQL считает нужным проанализировать для выполнения запроса.

    Этот столбец содержит дополнительную информацию о том, как MySQL будет выполнять запрос. Ниже объясняется значение каждой из текстовых строк, которые могут находиться в этом столбце:

    После нахождения первой совпадающей строки MySQL не будет продолжать поиск строк для текущей комбинации строк.

    MySQL смог осуществить оптимизацию LEFT JOIN для запроса и после нахождения одной строки, соответствующей критерию LEFT JOIN , не будет искать в этой таблице последующие строки для предыдущей комбинации строк.

    Предположим, что столбец t2.id определен как NOT NULL . В этом случае MySQL просмотрит таблицу t1 и будет искать строки в t2 соответствующие t1.id . Если MySQL находит в t2 нужную строку, он знает, что t2.id никогда не может иметь значение NULL , и не будет искать в t2 оставшуюся часть строк, имеющих тот же самый id . Другими словами, для каждой строки в t1 MySQL должен выполнить только один поиск в t2 , независимо от того, сколько совпадающих строк содержится в t2 .

    range checked for each record (index map: #)

    MySQL не нашел достаточно хорошего индекса для использования. Вместо этого для каждой комбинации строк в предшествующих таблицах он будет проверять, какой индекс следует использовать (если есть какой-либо индекс), и применять его для поиска строк в таблице. Это делается не очень быстро, но таким образом таблицы связываются быстрее, чем без индекса.

    MySQL должен будет сделать дополнительный проход, чтобы выяснить, как извлечь строки в порядке сортировки. Для выполнения сортировки выполняется просмотр всех строк согласно типу связывания ( join type ) и сохраняются ключ сортировки плюс указатель на строку для всех строк, удовлетворяющих выражению WHERE . После этого ключи сортируются и строки извлекаются в порядке сортировки.

    Для извлечения данных из столбца используется только информация дерева индексов; при этом нет необходимости производить собственно чтение записи. Это применимо для случаев, когда все используемые столбцы таблицы являются частью одного индекса.

    Чтобы выполнить запрос, MySQL должен будет создать временную таблицу для хранения результата. Это обычно происходит, если предложение ORDER BY выполняется для набора столбцов, отличного от того, который используется в предложении GROUP BY .

    Выражение WHERE будет использоваться для выделения тех строк, которые будут сопоставляться со следующей таблицей или тех, которые будут посланы клиенту. Если этой информации нет, а таблица имеет тип ALL или index , то, значит, в вашем запросе есть какая-то ошибка (если вы не собираетесь делать выборку/тестирование всех строк таблицы).

    Если нужно, чтобы запросы выполнялись настолько быстро, насколько это возможно, посмотрите, есть ли строки упоминания Using filesort и Using temporary .

    Существует неплохой способ определить, насколько хорошим является тип связывания. Для этого нужно перемножить все значения столбца rows , выводимого командой EXPLAIN . Результатом будет грубая оценка того, сколько строк должен просмотреть MySQL для выполнения запроса. Это же число используется для ограничения запросов в переменной max_join_size . See Раздел 5.5.2, «Настройка параметров сервера».

    В следующем примере показано, как можно постепенно оптимизировать JOIN при помощи информации, выводимой оператором EXPLAIN .

    Предположим, что имеется представленный ниже оператор SELECT , который нужно исследовать при помощи команды EXPLAIN :

    Для этого примера принимается, что:

    Сравниваемые столбцы были объявлены следующим образом:

    Таблица Столбец Тип столбца
    tt ActualPC CHAR(10)
    tt AssignedPC CHAR(10)
    tt ClientID CHAR(10)
    et EMPLOYID CHAR(15)
    do CUSTNMBR CHAR(15)

    Таблицы проиндексированы следующим образом:

    Таблица Индекс
    tt ActualPC
    tt AssignedPC
    tt ClientID
    et EMPLOYID (primary key)
    do CUSTNMBR (primary key)

    Значения tt.ActualPC распределены не равномерно.

    На начальном этапе перед выполнением какой-либо оптимизации оператор EXPLAIN выведет следующую информацию:

    Поскольку каждая таблица имеет тип ( type ) ALL , из приведенного выше вывода видно, что MySQL будет делать полное связывание всех таблиц! Это займет долгое время, поскольку для выполнения такого связывания должно быть рассмотрено произведение числа строк в каждой таблице! Для нашего случая такое произведение — 74 * 2135 * 74 * 3872 = 45268558720 строк. Если таблицы большие, трудно даже представить себе, как долго они будут связываться.

    Одна проблема здесь состоит в том, что MySQL не может (пока еще) эффективно применять индексы к столбцам, если они объявлены по-разному. В этом контексте тип VARCHAR и тип CHAR — одинаковы, если они не объявлены с различной длиной. Поскольку столбец tt.ActualPC объявлен как CHAR(10) , а et.EMPLOYID — как CHAR(15) , имеется несоответствие по длине значений.


    Чтобы устранить это несоответствие между длинами столбцов, следует использовать команду ALTER TABLE для удлинения столбца ActualPC от 10 символов до 15 символов:

    Теперь оба столбца tt.ActualPC и et.EMPLOYID имеют тип VARCHAR(15) . При повторном выполнении оператора EXPLAIN будет выведен следующий результат:

    Это не идеально, но уже намного лучше (произведение значений строк ( rows ) теперь уменьшилось в 74 раза). Такое связывание выполнится за пару секунд.

    Можно сделать еще одно изменение — чтобы устранить несоответствие длин столбцов для сравнений tt.AssignedPC = et_1.EMPLOYID и tt.Client >.

    Теперь оператор EXPLAIN будет выводить такую информацию:

    Это почти идеально.

    Осталась еще одна проблема. Она заключается в том, что по умолчанию MySQL принимает, что значения в столбце tt.ActualPC распределены равномерно, но в таблице tt это не так. К счастью, проинформировать MySQL об этом можно очень просто:

    Теперь связывание совершенно, и оператор EXPLAIN выведет такой результат:

    Обратите внимание: столбец rows в выводе оператора EXPLAIN — опытное предположение оптимизатора связей MySQL. Чтобы оптимизировать запрос, нужно проверить, являются ли числа близкими к действительным. Если нет, можно получить лучшую производительность, используя в операторе SELECT соединение STRAIGHT_JOIN и попытаться задать другой порядок таблиц в выражении FROM .

    5.2.2. Оценка производительности запроса

    В большинстве случаев можно оценивать производительность путем подсчета дисковых операций. Для маленьких таблиц можно обычно принимать 1 строку за 1 операцию дискового поиска (поскольку индекс, скорее всего, в кэше). Для больших таблиц можно считать, что (при использовании индексов типа B++ деревьев) для нахождения строки потребуется

    log(количество_строк) / log(длина_индексного_блока / 3 * 2 / (длина_индекса + длина_указателя_на_данные)) + 1

    дисковая операция для получения строки.

    Обычно в MySQL индексный блок занимает 1024 байта, а указательн — 4 байта. Для таблицы, содержащей 500000 строк и имеющей длину индекса 3 ( medium integer ) потребуется log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 дисковых операции поиска.

    Поскольку вышеупомянутый индекс будет занимать приблизительно 500000 * 7 * 3/2 = 5,2Mб (если учитывать, что индексные буфера обычно заполняются на 2/3), большая часть индекса, скорее всего, окажется в памяти, и для того, чтобы найти строку, потребуется лишь 1-2 обращения к ОС для чтения.

    Для записи, однако, потребуется 4 дисковых запроса (таких, какие рассматривались выше) чтобы найти место для помещения нового индекса, и обычно 2 дисковых операции, чтобы обновить индекс и вставить строку.

    Обратите внимание: сказанное выше не означает, что производительность приложения будет ухудшаться в log N раз! Поскольку все кэшируется в OС или на SQL-сервере, замедление работы при увеличении таблицы будет незначительным. И лишь после того, как данных станет так много, что они перестанут помещаться в кэш, замедление работы там, где работа приложения сводится только к операциям дискового поиска (количество которых растет в log N ), станет гораздо ощутимей. Чтобы избежать этого, следует увеличить индексный кэш так, чтобы он вмещал возросшее количество данных. See Раздел 5.5.2, «Настройка параметров сервера».

    5.2.3. Скорость выполнения запросов SELECT

    В общем случае для того, чтобы заставить медленный SELECT . WHERE работать быстрее, прежде всего нужно выяснить, можно ли добавить индекс. Для всех ссылок между различными таблицами должны, как правило, применяться индексы. Чтобы определить, какие индексы используются для выборки SELECT , можно использовать EXPLAIN . See Раздел 5.2.1, «Синтаксис оператора EXPLAIN (получение информации о SELECT )».

    Вот несколько общих советов:

    Чтобы MySQL лучше оптимизировал запросы, можно выполнить myisamchk —analyze для таблицы после того, как она загружена соответствующими данными. Таким образом для каждой части индекса будет обновлено значение, указывающее среднее число строк, имеющих одинаковые значения (для уникальных индексов это всегда 1, разумеется). MySQL будет использовать это число, чтобы решить, какой индекс следует выбрать для связывания двух таблиц при помощи «неконстантного выражения». Результат работы analyze можно увидеть в столбце Cardinality после выполнения команды SHOW INDEX FROM имя_таблицы .

    Чтобы отсортировать индекс и данные в соответствии с индексом, используйте myisamchk —sort-index —sort-records=1 (если нужно отсортировать по индексу 1). Если имеется уникальный индекс, по которому вы хотите считывать все записи в порядке, соответствующем данному индексу, это — хороший способ ускорить считывание записей. Обратите внимание, однако, что эта сортировка написана не оптимально и для большой таблицы будет выполняться долго!

    5.2.4. Как MySQL оптимизирует выражения WHERE

    Описание оптимизации выражений WHERE помещено в раздел, посвященный SELECT , потому что они главным образом используются в запросах SELECT , но для выражений WHERE в операторах DELETE и UPDATE используются те же способы оптимизации.

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

    Ниже перечислены некоторые из оптимизации, выполняемых MySQL:

    Удаляются ненужные скобки:

    Константы заменяются значениями:

    Удаляются условия для констант (требуется при замене констант значением):

    Константные выражения, используемые индексами, оцениваются только один раз.

    Для таблиц HEAP и MyISAM функция COUNT(*) , которая вызывается для одной таблицы и не содержит предложения WHERE , берется непосредственно из табличной информации. Это делается также для любого выражения NOT NULL , в котором используется только одна таблица.

    Недопустимые константные выражения выявляются на ранних этапах. MySQL быстро обнаруживает, что некоторые операторы SELECT неосуществимы и не возвращают строк.

    Выполняется слияние выражения HAVING с WHERE , если не используется предложение GROUP BY или групповые функции ( COUNT(), MIN(). ).

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

    Все константные таблицы считываются в первую очередь, перед любыми другими таблицами в запросе. К константным таблицам относятся следующие:

    Пустая таблица или таблица с 1 строкой.

    Таблица, которая используется с выражением WHERE для индекса UNIQUE , или PRIMARY KEY , где все части индекса используются с константными выражениями и части индекса определены как NOT NULL .

    Все эти таблицы используются как константные таблицы:

    Лучшая комбинацию связывания для связывания таблиц находится путем испытания всех возможных вариантов. Если все столбцы в предложениях ORDER BY и GROUP BY принадлежат одной таблице, эта таблица рассматривается первой при связывании.

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

    Если используется SQL_SMALL_RESULT , MySQL будет применять временную таблицу, которую разместит в памяти.

    Запрашивается каждый индекс таблицы, и используется лучший, охватывающий менее 30% строк. Если такой индекс найти нельзя, используется быстрое сканирование таблицы.

    В некоторых случаях MySQL может читать данные из индекса даже без обращения к файлу данных. Если все столбцы, используемые в индексе, числовые, то для выполнения запроса будет использоваться только индексное дерево.

    Перед выводом каждой записи пропускаются те, которые не соответствуют выражению HAVING .

    Вот некоторые примеры очень быстрых запросов:

    Для выполнения следующих запросов используется только индексное дерево (предполагается, что индексированные столбцы числовые):

    Следующие запросы используют индексацию, чтобы получить отсортированные строки без дополнительного прохода для сортировки:

    5.2.5. Как MySQL оптимизирует DISTINCT

    DISTINCT преобразовывается к GROUP BY для всех столбцов, для DISTINCT в сочетании с ORDER BY , помимо этого, во многих случаях также требуется временная таблица.

    Если LIMIT # указывается совместно с DISTINCT , MySQL остановится, как только найдет # уникальных строк.

    Если не все столбцы и не во всех таблицах используются, MySQL прекратит сканирование неиспользуемых таблиц, как только найдет первое совпадение.

    В случае, если, предположим, таблица t1 используется перед t2 (это проверяется при помощи EXPLAIN ), MySQL прекратит чтение в t2 (для каждой отдельной строки из t1 ), после того как найдет первую строку в t2 .

    5.2.6. Как MySQL оптимизирует LEFT JOIN и RIGHT JOIN

    Выражение » A LEFT JOIN B » в MySQL реализовано следующим образом:

    Таблица B устанавливается как зависимая от таблицы A и от всех таблиц, от которых зависит A.

    Таблица A устанавливается как зависимая ото всех таблиц (кроме B), которые используются в условии LEFT JOIN .

    Все условия LEFT JOIN перемещаются в предложение WHERE .

    Выполняются все стандартные способы оптимизации соединения, за исключением того, что таблица всегда читается после всех таблиц, от которых она зависит. Если имеется циклическая зависимость, MySQL выдаст ошибку.

    Выполняются все стандартные способы оптимизации WHERE .

    Если в таблице A имеется строка, соответствующая выражению WHERE , но в таблице B ни одна строка не удовлетворяет условию LEFT JOIN , генерируется дополнительная строка B, в которой все значения столбцов устанавливаются в NULL .

    Если LEFT JOIN используется для поиска тех строк, которые отсутствуют в некоторой таблице, и в предложении WHERE выполняется следующая проверка: column_name IS NULL , где column_name — столбец, который объявлен как NOT NULL , MySQL пререстанет искать строки (для отдельной комбинации ключа) после того, как найдет строку, соответствующую условию LEFT JOIN .

    RIGHT JOIN реализован аналогично LEFT JOIN .

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

    Обратите внимание: отсюда следует, что если выполняется запрос типа

    MySQL будет делать полный просмотр таблицы b , поскольку LEFT JOIN заставит его читать эту таблицу перед d .

    В этом случае, чтобы предотвратить полный просмотр таблицы b , нужно изменить запрос таким образом:

    5.2.7. Как MySQL оптимизирует ORDER BY

    В некоторых случаях MySQL может использовать индекс, чтобы выполнить запрос ORDER BY или GROUP BY без выполнения дополнительной сортировки.

    Индекс может также использоваться и тогда, когда предложение ORDER BY не соответствует индексу в точности, если все неиспользуемые части индекса и все столбцы, не указанные в ORDER BY — константы в выражении WHERE . Следующие запросы будут использовать индекс, чтобы выполнить ORDER BY / GROUP BY .

    Ниже приведены некоторые случаи, когда MySQL не может использовать индексы, чтобы выполнить ORDER BY (обратите внимание, что MySQL тем не менее будет использовать индексы, чтобы найти строки, соответствующие выражению WHERE ):


    Сортировка ORDER BY делается по нескольким ключам: SELECT * FROM t1 ORDER BY key1,key2

    Сортировка ORDER BY делается, при использовании непоследовательных частей ключа: SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2

    Смешиваются ASC и DESC . SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 ASC

    Для выборки строк и для сортировки ORDER BY используются разные ключи: SELECT * FROM t1 WHERE key2=constant ORDER BY key1

    Связываются несколько таблиц, и столбцы, по которым делается сортировка ORDER BY , относятся не только к первой неконстантной (const) таблице, используемой для выборки строк (это первая таблица в выводе EXPLAIN , в которой не используется константный, const , метод выборки строк).

    Имеются различные выражения ORDER BY и GROUP BY .

    Используемый индекс таблицы имеет такой тип, который не обеспечивает сортированного хранения строк (как индекс HASH в таблицах HEAP ).

    В тех случаях, когда MySQL должен сортировать результат, он использует следующий алгоритм:

    Считываются все строки согласно ключу или путем сканирования таблицы. Строки, которые не соответствует предложению WHERE, пропускаются.

    Ключ сортировки сохраняется в буфере сортировки (размера sort_buffer )

    Когда буфер заполняется, содержимое буфера сортируется алгоритмом qsort , результаты сохраняются во временном файле. Сохраняется указатель на отсортированный блок (в том случае, когда все строки умещаются в буфере сортировки, временный файл не создается).

    Вышеупомянутое действие повторяется, пока не будут считаны все строки.

    Делается мультислияние до MERGEBUFF (7) областей в один блок в другом временном файле. Это действие повторяется, пока все блоки из первого файла не окажутся во втором файле.

    Предыдущий пункт повторяется, пока не останется менее MERGEBUFF2 (15) блоков.

    При последнем мультислиянии в результирующий файл записывается только указатель на строку (последняя часть ключа сортировки).

    Теперь код в файле sql/records.cc будет использоваться для чтения данных в отсортированном порядке, с использованием указателей на строки из результирующего файла. Чтобы оптимизировать этот процесс, мы считываем большой блок указателей на строки, сортируем их, и затем считываем строки в отсортированном порядке в буфер строк ( record_rnd_buffer ).

    При помощи команды EXPLAIN SELECT . ORDER BY можно проверить, может ли MySQL использовать индексы для выполнения запроса. Если в столбце extra содержится значение Using filesort , то MySQL не может использовать индексы для выполнения сортировки ORDER BY . See Раздел 5.2.1, «Синтаксис оператора EXPLAIN (получение информации о SELECT )».

    Чтобы сортировка ORDER BY выполнялась с большей скоростью, нужно сначала посмотреть, можно ли заставить MySQL использовать индексы взамен дополнительной фазы сортировки. Если это невозможно, то можно сделать следующее:

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

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

    Изменить переменную tmpdir , чтобы она указывала на выделенный диск с большим количеством свободного пространства. Начиная с MySQL 4.1, в tmpdir могут быть указаны несколько путей, разделенных двоеточием : (точкой с запятой на Windows ; ). Эти пути будут использованы в ротации.

    Внимание: Эти пути должны находится на разных физических дисках, не на разных разделах одного и того же диска.

    MySQL по умолчанию сортирует все GROUP BY x,y[. ] запросы так, как если бы вы указали ORDER BY x,y[. ] . MySQL будет оптимизировать любой ORDER BY как сказано выше, без всяких потерь производительности. Если, как в некоторых случаях, вы не хотите иметь результат отсортированным, вы можете указать ORDER BY NULL :

    5.2.8. Как MySQL оптимизирует LIMIT

    В некоторых случаях, когда используется LIMIT # и не используется HAVING , MySQL будет выполнять запрос несколько иначе:

    Если при помощи LIMIT выбираются только несколько строк, MySQL будет использовать индексы в тех некоторых случаях, когда он обычно предпочел бы делать полное сканирование таблицы.

    Если LIMIT # используется с ORDER BY , MySQL закончит сортировку, как только найдет первые # строк, вместо того, чтобы сортировать всю таблицу.

    При сочетании LIMIT # с DISTINCT MySQL остановится, как только найдет # уникальных строк.

    В некоторых случаях группировка GROUP BY может быть выполнена путем упорядоченного считывания ключа (или путем выполнения сортировки по ключу) и последующего вычисления итогового результата пока не изменится значение ключа. В этом случае LIMIT # не будет вычислять какие-либо ненужные предложения GROUP BY .

    После того как MySQL пошлет первые # строк клиенту, он прервет выполнение запроса (если не используется SQL_CALC_FOUND_ROWS ).

    LIMIT 0 всегда будет быстро возвращать пустую выборку. Эта команда полезна для проверки запроса и получения типов столбцов результата.

    Если сервер для выполнения запроса использует временные таблицы, LIMIT # применяется для вычисления того, сколько для них потребуется места.

    5.2.9. Скорость выполнения запросов INSERT

    Время, необходимое для вставки записи, можно грубо разделить на такие промежутки:

    Посылка запроса на сервер: (2)

    Синтаксический анализ запроса: (2)

    Вставка записи: (1 * размер записи)

    Вставка индексов: (1 * число индексов)

    где числа в скобках пропорциональны полному времени. При этом не учитывается время в начале вставки, требующееся для открытия таблиц (таблицы открываются один раз для каждого конкурентно выполняющегося запроса).

    Размер таблицы замедляет вставку индексов в log N раз (B-деревья).

    Некоторые способы ускорения вставки:

    Если с одного клиента одновременно вставляется большое количество строк, используйте операторы INSERT в форме, содержащей множество записей. При этом вставка будет происходить намного быстрее (в некоторых случаях в несколько раз), чем при использовании отдельных операторов INSERT . При добавлении данных в непустую таблицу можно настроить переменную bulk_insert_buffer_size так, чтобы это делалось еще быстрее. See Раздел 4.5.6.4, « SHOW VARIABLES ».

    При вставке нескольких строк с различных клиентов можно повысить скорость, используя оператор INSERT DELAYED . See Раздел 6.4.3, «Синтаксис оператора INSERT ».

    Обратите внимание: при использовании таблиц MyISAM можно вставлять строки во время выполнения операторов SELECT , если в таблицах нет удаленных строк.

    При загрузке таблицы из текстового файла используйте команду LOAD DATA INFILE . При этом обычно вставка будет происходить в 20 раз быстрее, чем при использовании соответствующего количества операторов INSERT . See Раздел 6.4.9, «Синтаксис оператора LOAD DATA INFILE ».

    Если таблица имеет много индексов, можно проделать некоторую дополнительную работу, чтобы команда LOAD DATA INFILE выполнялась еще быстрее. Используйте следующую процедуру:

    При необходимости создайте таблицу при помощи оператора CREATE TABLE (например, используя mysql или Perl-DBI ).

    Выполните оператор FLUSH TABLES или команду оболочки: mysqladmin flush-tables .

    Используйте myisamchk —keys-used=0 -rq /path/to/db/tbl_name . После этого индексы не будут использоваться для данной таблицы.

    Вставьте данные в таблицу при помощи LOAD DATA INFILE . При этом никакие индексы обновляться не будут и, следовательно, скорость будет высокой весьма.

    Если вы собираетесь в будущем только лишь читать таблицу, выполните myisampack для этой таблицы, чтобы уменьшить ее размер. See Раздел 4.7.4, « myisampack , MySQL-генератор сжатых таблиц (только для чтения)».

    Воссоздайте индексы при помощи команды myisamchk -r -q /path/to/db/tbl_name . Эта процедура создает индексное дерево в памяти, перед тем как записать его на диск, что гораздо быстрее за счет исключения большого количества дисковых операций. Индексное дерево, получившееся в результате, к тому же отлично сбалансировано.

    Выполните оператор FLUSH TABLES или команду оболочки: mysqladmin flush-tables .

    Обратите внимание: команда LOAD DATA INFILE также выполняет вышеупомянутую оптимизацию при вставках в пустую таблицу. Главное отличие этой команды от вышеупомянутой процедуры заключается в том, что при помощи myisamchk можно выделить намного больше временной памяти для создания индекса, чем MySQL, по вашему мнению, должен выделять для каждого воссоздания индексов. Начиная с MySQL 4.0 можно также использовать команду ALTER TABLE tbl_name DISABLE KEYS вместо myisamchk —keys-used=0 -rq /path/to/db/tbl_name и ALTER TABLE tbl_name ENABLE KEYS вместо myisamchk -r -q /path/to/db/tbl_name . Таким образом можно также пропускать шаги FLUSH TABLES .

    Можно ускорять операции вставки, выполняемые несколькими операторами, путем установки блокировки таблиц:

    Главный фактор, влияющий на скорость, — то, что буфер индексов сбрасывается на диск только один раз, после завершения всех операторов INSERT . Обычно содержимое индексных буферов сбрасывалось бы на диск столько раз, сколько имеется различных операторов INSERT . Блокировка не нужна, если можно вставить все строки при помощи одного оператора. Для транзакционных таблиц, чтобы повысить скорость, следует использовать BEGIN/COMMIT вместо LOCK TABLES . Блокировка также понизит полное время проверки подсоединений (multi-connection tests), но максимальное время ожидания для некоторых потоков повысится (потому что они ожидают снятия блокировки). Например:

    Если блокировка не используется, 2, 3, и 4 завершат выполнение раньше, чем 1 и 5. Если блокировка используется, 2, 3 и 4, видимо, не закончат выполнение раньше, чем 1 или 5, но общее время должно приблизительно уменьшиться на 40%. Так как в MySQL операции INSERT , UPDATE и DELETE очень быстрые, общая производительность будет улучшаться, если добавлять блокировки ко всем командам, делающим более 5 вставок или обновлений подряд. Если делается очень много вставок строк, можно время от времени сопровождать команду LOCK TABLES командой UNLOCK TABLES (после каждых 1000 строк), чтобы позволить другим потокам обращаться к таблице. Результатом всего этого будет получение хорошей производительности. Конечно, для загрузки данных намного более быстрой является команда LOAD DATA INFILE .

    Чтобы дополнительно повысить скорость выполнения команд LOAD DATA INFILE и INSERT , увеличьте буфер ключа ( key buffer ). See Раздел 5.5.2, «Настройка параметров сервера».

    5.2.10. Скорость выполнения запросов UPDATE

    Запросы UPDATE оптимизируются как запрос SELECT с дополнительными издержками на запись. Скорость записи зависит от размера обновляемых данных и количества обновляемых индексов. Индексы, которые не были изменены, обновлены не будут.

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

    Обратите внимание: при использовании динамического формата записи, если запись обновляется более длинной, может произойти «расслоение» записи. Таким образом, если вы делаете это часто, очень важно время от времени выполнять команду OPTIMIZE TABLE . See Раздел 4.5.1, «Синтаксис команды OPTIMIZE TABLE ».

    5.2.11. Скорость выполнения запросов DELETE

    Чтобы удалить все строки в таблице, нужно использовать команду TRUNCATE TABLE table_name . See Раздел 6.4.7, «Синтаксис оператора TRUNCATE ».

    Время, необходимое для удаления записи, прямо пропорционально числу индексов. Чтобы записи удалялись быстрее, можно увеличить размер индексного кэша. See Раздел 5.5.2, «Настройка параметров сервера».

    5.2.12. Другие советы по оптимизации

    Несортированные советы для повышения скорости систем:

    Используйте постоянные соединения с базой данных, чтобы избежать издержек на подключения. Если невозможно использовать постоянные соединения и осуществляется большое количество новых подключений к базе данных, то можно изменить значение переменной thread_cache_size . See Раздел 5.5.2, «Настройка параметров сервера».

    Всегда проверяйте, чтобы все ваши запросы действительно использовали созданные вами в таблицах индексы. В MySQL это можно сделать с помощью команды EXPLAIN . See Раздел 5.2.1, «Синтаксис оператора EXPLAIN (получение информации о SELECT )».

    Старайтесь избегать сложных запросов SELECT на часто обновляемых таблицах типа MyISAM. Это помогает избежать проблем с блокировкой таблиц.

    Новые таблицы MyISAM могут вносить записи в таблицу без удаляемых записей, которые в то же самое время читает иная таблица. Если это имеет для вас значение, то следует рассмотреть методы, не требующие удаления записей или запускать OPTIMIZE TABLE после удаления большого количества строк.


    Используйте ALTER TABLE . ORDER BY expr1,expr2. если вы большей частью извлекаете записи в порядке expr1,expr2. Можно получить более высокую производительность, используя эту опцию после больших преобразований в таблице.

    В некоторых случаях может оказаться целесообразным введение столбца, базирующегося на «хэшированной» информации из других столбцов. Если этот столбец невелик и достаточно уникален, то он может быть намного быстрее, чем большой индекс на многих столбцах. В MySQL очень просто использовать подобный дополнительный столбец:

    SELECT * FROM table_name WHERE hash=MD5(CONCAT(col1,col2)) AND col_1=’constant’ AND col_2=’constant ‘

    Для часто изменяющихся таблиц следует избегать типов VARCHAR или BLOB для всех столбцов. При использовании единичного столбца VARCHAR или BLOB вы получите динамическую длину строки. See Глава 7, Типы таблиц MySQL.

    Разделение таблицы на несколько различных таблиц просто потому, что строки получаются «большими», обычно не приносит пользы. Чтобы получить доступ к строке, наиболее трудоемким оказывается поиск по диску для нахождения первого байта этой строки. После нахождения этих данных большинство новых дисков могут прочесть всю строку достаточно быстро для большинства приложений. Разделение таблицы имеет значение только в следующих случаях: когда это таблица с динамическим размером строки (смотрите выше), которую можно изменить на строку фиксированного размера, или когда необходимо просматривать таблицу очень часто и нет необходимости в большинстве столбцов. See Глава 7, Типы таблиц MySQL.

    Если очень часто приходится производить вычисления, базирующиеся на информации из большого количества строк (такие как подсчет предметов), то, вероятно, намного лучше ввести новую таблицу и обновлять счетчик в режиме реального времени. Обновление вида UPDATE table SET count=count+1 WHERE index_column=constant является очень быстрым!

    Это действительно важно при использовании типов таблиц MySQL, вроде MyISAM или ISAM, имеющих только блокирование таблиц (многочисленные читающие/единственный записывающий). Для многих баз данных это обеспечит также более высокую производительность, поскольку программа управления блокировкой строк в этом случае будет иметь меньше работы.

    Если необходимо собирать статистические данные из больших журнальных таблиц, то используйте сводные таблицы вместо сканирования целой таблицы. Поддерживать сводные таблицы должно быть намного быстрее, чем пытаться сделать «живую» статистику. Намного быстрее воспроизвести новые сводные таблицы из журналов, когда что-либо изменяется (в зависимости от деловых решений), чем изменять работающее приложение!

    Если возможно, необходимо классифицировать отчеты как «реальные» или «статистические», где данные, необходимые для статистических отчетов, генерируются только на основе сводных таблиц, которые формируются из реальных данных.

    Воспользуйтесь преимуществом того факта, что столбцы имеют значения по умолчанию. Вносите величины явно только тогда, когда значения вносимых величин отличаются от установленных по умолчанию. Это уменьшает объем анализа, который необходимо произвести в MySQL, и улучшает скорость внесения.

    В некоторых случаях удобно упаковывать и хранить данные в столбцах BLOB . В этом случае необходимо добавить дополнительный код для запаковывания в BLOB и распаковывания обратно, но на некотором этапе это может сэкономить много обращений. Это практично, когда ваши данные не согласуются со структурой статической таблицы.

    Обычно следует стремиться сохранять все данные в безизбыточной форме (которая называется 3-й нормальной формой в теории баз данных), но не следует опасаться дублирования данных или создания сводных таблиц, если это необходимо для достижения большей скорости.

    Хранимые процедуры или UDF (функции, определяемые пользователем) могут быть хорошим способом получить большую производительность. В этом случае, однако, следует иметь в запасе некоторый иной (более медленный) путь, если используемая вами база данных не поддерживает этих возможностей.

    Вы всегда можете кое-чего достичь путем кэширования запросов/ответов в своем приложении и стараясь выполнить много вставок/обновлений в одно и то же время. Если ваша база данных поддерживает блокировку таблиц (как MySQL и Oracle), то это должно помочь гарантировать, что кэш индексов сбрасывается только однажды после всех обновлений.

    Используйте INSERT /*! DELAYED */ , если нет необходимости знать, когда ваши данные записываются. Это повысит скорость работы, поскольку многие табличные записи могут быть внесены с помощью одной дисковой записи.

    Используйте INSERT /*! LOW_PRIORITY */ , если хотите сделать ваши выборки более важными.

    Используйте SELECT /*! HIGH_PRIORITY */ , чтобы получить выборки, которые перепрыгивают очередь. То есть, выборка выполняется, даже если кто-либо ожидает, чтобы сделать запись.

    Используйте многострочную команду INSERT для хранения многих строк в одной SQL-команде (многие SQL-серверы поддерживают это).

    Используйте LOAD DATA INFILE для загрузки больших количеств данных. Это быстрее, чем обычные вставки и будет еще быстрее при интеграции myisamchk в mysqld .

    Используйте столбцы AUTO_INCREMENT , чтобы сделать величины уникальными.

    Используйте время от времени OPTIMIZE TABLE , чтобы избежать фрагментации при использовании динамического табличного формата. See Раздел 4.5.1, «Синтаксис команды OPTIMIZE TABLE ».

    Используйте таблицы HEAP , чтобы получить более высокую скорость, когда это возможно. See Глава 7, Типы таблиц MySQL.

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

    Используйте в памяти таблицы для неответственных данных, к которым часто обращаются (таким, как информация о последнем показанном баннере для пользователей, не имеющих cookies).

    Столбцы с идентичной информацией в различных таблицах должны объявляться одинаково и иметь одинаковые имена. До версии 3.23 в противном случае получались медленные соединения (slow joins). Старайтесь сохранять имена простыми (используйте name вместо customer_name в таблице customer ). Чтобы ваши имена были переносимыми на другие SQL-серверы, они должны быть короче, чем 18 символов.

    Если вам действительно нужна высокая скорость, вы должны взглянуть на интерфейсы нижнего уровня для хранения данных, поддерживаемые различными SQL-серверами! Например, обращаясь к таблицам MyISAM в MySQL напрямую, можно было бы получить увеличение скорости в 2-5 раз по сравнению с использованием интерфейса SQL. Для возможности сделать это, данные должны находиться на том же самом сервере, что и приложение, и, обычно, должны иметь доступ только в одном процессе обработки (поскольку внешняя файловая блокировка действительно медленна). Можно было бы избавиться от вышеуказанных проблем введением низко-уровневых команд MyISAM в сервере MySQL (это был бы один из простых путей получить большую производительность, если необходимо). Путем тщательного проектирования интерфейса базы данных было бы достаточно просто поддерживать этот тип оптимизации.

    Во многих случаях быстрее получить доступ к данным из базы данных (используя действующее соединение), чем обращаться к текстовому файлу, просто из-за того, что база данных, вероятно, более компактна, чем текстовый файл (если вы используете числовые данные), и это приведет к меньшему количеству обращений к диску. Вы также сэкономите на коде, поскольку не должны анализировать текстовые файлы, чтобы найти границы строк и столбцов.

    Для увеличения скорости можно также использовать репликацию. See Раздел 4.10, «Репликация в MySQL».

    Объявление таблицы с DELAY_KEY_WRITE=1 сделает обновление индексов более быстрым, так как они не записываются на диск, пока файл закрыт. Обратная сторона этого заключается в том, что необходимо запускать myisamchk на этих таблицах перед началом работы mysqld , для уверенности, что все в порядке, если что-либо уничтожит mysqld в середине работы. Поскольку ключевая информация всегда может быть воспроизведена из данных, то вы не должны что-либо потерять при использовании DELAY_KEY_WRITE .

    Оптимизация MySQL: настраиваем сервер на оптимальную производительность

    Как и все реляционные базы данных, MySQL может оказаться «хитрым зверьком», который может мгновенно «застыть на полпути», оставив ваши приложения в беде и подведя к опасной черте ваш бизнесе или бизнес ваших клиентов.

    По правде говоря, в основе большинства проблем производительности MySQL лежат распространенные ошибки. Чтобы ваш сервер MySQL гудел как паровоз на максимальной скорости, обеспечивая стабильную и соответствующую вашим задачам производительность, важно устранить эти ошибки, которые часто скрываются в некоторых тонкостях вашей рабочей нагрузки сервера или в ловушке, скрытой в конфигурационных файлах СУБД.

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

    Вот 10 советов, которые позволят Вам получить отличную производительность базы данных MySQL. Данные советы охватывает как сферу администрирования сервера, так и нюансы разработки приложений под СУБД MySQL (т. е. зону ответственности программиста).

    Совет по настройке производительности MySQL № 1: профиль вашей рабочей нагрузки

    Лучший способ понять, на что ваш сервер тратит свое драгоценное время, — это профиль рабочей нагрузки сервера. Профилируя свою рабочую нагрузку, вы можете просмотреть наиболее дорогие запросы для дальнейшей настройки. Здесь время является наиболее важной метрикой, потому что, когда вы выдаете запрос на сервер, вы ни о чем не заботитесь так сильно, как о наискорейшем времени его завершения.

    Лучший способ профилировать рабочую нагрузку — это инструмент, такой как анализатор запросов MySQL Enterprise Monitor или утилита pt-query-digest из набора инструментов Percona Toolkit. Эти инструменты захватывают запросы, выполняемые сервером, и возвращают таблицу задач, отсортированную по уменьшению порядка времени отклика, мгновенно поднимая самые дорогие и трудоемкие задачи вверх. Поэтому Вы можете сразу видеть, на чем сосредоточить свои усилия.

    Инструменты профилирования рабочей нагрузки объединяют одинаковые запросы вместе, позволяя вам видеть медленные запросы, а также быстрые, но выполняемые очень часто, что также может заставить сервер «просесть».

    Совет по настройке производительности MySQL № 2: Понимание четырех основных ресурсов для оптимизации

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

    Понимание основных ресурсов важно в двух конкретных областях: выбор оборудования и устранение неполадок.

    Выбирая аппаратные средства для MySQL сервера, обеспечьте наличие хороших компонентов. Так же важно, сбалансировать их достаточно хорошо друг с другом. Часто организации выбирают серверы с быстрыми процессорами и дисками, но с маленьким объемом памяти. В некоторых случаях добавление памяти является дешевым способом увеличения производительности на порядки, особенно при нагрузках, связанных с диском. Это может показаться противоречивым, но во многих случаях диски перенапряжены, потому что недостаточно памяти для хранения рабочего набора данных сервера, в следствие чего происходить кеширование данных на диски.

    Еще один хороший пример этого баланса относится к процессорам. В большинстве случаев MySQL будет хорошо работать с быстрыми процессорами, потому что каждый запрос работает в одном потоке и не может быть распараллелен между процессорами. Много ядер и много потоков в процессоре — это очень хорошо, но еще лучше, когда каждое конкретное ядро было бы быстрым! Важно не ошибиться при выборе процессора. Купить многоядерный, но с медленными потоками или 1-2 ядерный, но с высокой тактовой частотой каждого ядра? Это определяется конкретными Ваши задачами, которые будут выполнятся на сервере. Совет по оптимизации №1 MySQL вам в помощь, как говорится (проанализируйте Ваши приложения и задачи перед покупкой).

    Когда дело доходит до устранения неполадок, проверьте производительность и использование всех четырех ресурсов, тщательно следя за тем, чтобы определить, работают ли все эти компоненты плохо или просто просят делают слишком много работы (аппаратная часть перегружена и требует модернизации). Эти знания помогут быстро решить Ваши проблемы. Вы удивитесь, как часто «бутылочное горлышко» находится именно в несбалансированной конфигурации аппаратной части сервера!

    Совет по производительности MySQL № 3: не используйте MySQL в качестве очереди

    Очереди (Queues) и шаблоны доступа, похожие на очереди, могут проникнуть в ваше приложение, не зная об этом. Например, если вы в приложении (коде) задаете изменение статуса элемента, тогда конкретный рабочий процесс должен его «захватить», прежде чем его обработать, вы невольно создаете очередь. Обычным примером является маркировка писем как неотправленных, отправка их, а затем их маркировка как отправленные.

    Очереди вызывают проблемы по двум основным причинам: они сериализуют вашу рабочую нагрузку, предотвращая параллельное выполнение задач, и они часто приводят к тому, что таблица, содержащая незавершенную работу, а также исторические/ретроспективные данные (historical data ) из заданий, которые были обработаны давно. Оба добавляют латентность к приложению и загружаются в базу данных MySQL.

    Совет по настройке производительности MySQL № 4: сначала делайте менее трудозатратную работу

    Отличный способ оптимизации MySQL — сначала сделать дешевую, неточную работу, а затем сложную и точную работу над меньшим результирующим набором данных.

    Например, предположим, что вы ищете что-то в пределах данного радиуса географической точки. Первым инструментом во множестве инструментов программистов является формула большого круга (Haversine) для вычисления расстояния по поверхности сферы. Проблема с этим методом заключается в том, что для формулы требуется много тригонометрических операций, которые очень интенсивно работают в ЦП. Расчеты с большим кругом, как правило, выполняются медленно и ускоряют загрузку процессора.

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

    Совет по настройке и оптимизации MySQL № 5: помните о двух смертельных ловушках масштабируемости

    Масштабируемость не так расплывчата, как вы можете подумать. На самом деле существуют точные математические определения масштабируемости, которые выражаются в виде уравнений. Эти уравнения подчеркивают, почему системы не масштабируются так, как должны.

    Возьмите Универсальный Закон о Масштабируемости, определение, которое удобно выражать и количественно оценивать характеристики масштабируемости системы. Он объясняет проблемы масштабирования с точки зрения двух фундаментальных затрат: сериализации (serialization) и перекрестных помех (crosstalk).

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

    Избегайте сериализации и перекрестных помех, и ваше приложение будет масштабироваться намного лучше. Что это означает в контексте настройки производительности MySQL? Это может проявляться в различных аспектах, но в некоторых примерах можно избежать эксклюзивных блокировок для строк. Очереди ( см. пункт № 3) имеют тенденцию плохо масштабироваться по этой причине.

    Совет по оптимизации MySQL № 6: не уделяйте слишком много внимания настройке

    Администраторы баз данных, как правило, тратят огромное количество времени на настройки конфигураций. Результат, как правило, не является большим улучшением и иногда может быть даже очень опасным. Я видел много «оптимизированных» серверов, которые постоянно терпели крах, закончились нехваткой памяти и плохо работали, когда рабочая нагрузка стала немного более интенсивной.

    Настройки по умолчанию в конфигурационных файлах MySQL, идут по принципу «одна общая конфигурация подходит для всех задач», и хоть такой подход достаточно устарели, но вам не нужно настраивать все подряд. Лучше правильно настроить базовые параметры и изменять другие настройки только в случае необходимости. В большинстве случаев вы можете получить 95% максимальной производительности сервера, установив примерно 10 параметров. Несколько ситуаций, когда это не применяется, — это крайностные случаи, уникальные для ваших конкретных обстоятельств.

    В большинстве случаев серверные инструменты настройки (например, MySqlTuner) не рекомендуется использовать, поскольку они, как правило, дают рекомендации не имеющие смысла для конкретных случаев. У некоторых даже есть опасные, неточные рекомендации, закодированные в них, такие как отношение кэш-памяти и формулы потребления памяти. Они никогда не были правы, и с течением времени они стали еще менее эффективными.

    Помните, что установочный дистрибутив базы данных MySQL и MariaDb идет с комплектом предустановленных конфигурационных файлов: my-small.cnf, my-medium.cnf, my-large.cnf и my-huge.cnf в зависимости от предполагаемых рабочих нагрузок и имеющихся аппаратных мощностей вашего сервера. Соответственно, для малых, средних, больших и очень больших систем. Выберете один из вариантов в соответствии с Вашими задачами и возможностями. Это дефолтные настройки перекроют от 80 до 95% максимально возможных результатов по оптимизации и настройки производительности.

    Совет по оптимизированию и настройке MySQL № 7: следите за запросами пагинации

    Приложения, которые имеют склонность к пагинации, как правило, «нагибают» сервер по полной.

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

    Источники для оптимизации часто можно найти в самом пользовательском интерфейсе. Вместо того, чтобы показывать точное количество страниц в результатах и ​​ссылки на каждую страницу отдельно, вы можете просто показать ссылку на следующую страницу. Вы также можете запретить людям переходить на страницы слишком далеко от первой страницы.

    На стороне запроса вместо использования LIMIT и offset вы можете выбрать еще одну строку, чем вам нужно, и когда пользователь нажимает ссылку «следующая страница», вы можете назначить эту конечную строку в качестве отправной точки для следующего набора результатов. Например, если пользователь просмотрел страницу со строками с 101 по 120, вы также должны сделать выборку и строки 121, а чтобы вывести следующую страницу, вы запросите сервер для строк больше или равных 121, предел 21.

    Совет по настройке производительности MySQL № 8: неистово накапливайте статистику, но не увлекайтесь чрезмерными оповещениями

    Мониторинг и оповещение необходимы, но что происходит с типичной системой мониторинга? Он начинает отправлять ложные срабатывания, а системные администраторы настраивают правила фильтрации электронной почты, чтобы остановить весь этот спам. Вскоре ваша система мониторинга станет совершенно бесполезной.

    Мы предлагаем воспринимать мониторинг в двух разрезах. Во-первых, как можно подробнее фиксировать показатели и оповещения. Очень важно фиксировать и сохранять все показатели, которые позволяет сделать сервер. Когда-нибудь возникнет странная проблема, и вам несомненно потребуется возможность проанализировать статистику и график изменения рабочей нагрузки сервера.

    Во-вторых, есть тенденция настраивать систему мониторинга на слишком много сигналов оповещения. Мы настоятельно предлагаем этого не делать. Системы мониторинга часто предупреждают о таких вещах, как коэффициент попадания в буфер или количество временных таблиц, созданных за секунду. Проблема в том, что для такого отношения нет хорошего порога чувствительности. Правильный порог отличается не только от сервера к серверу, но и от часа к часу с изменением рабочей нагрузки.


    В результате, необходимо осторожно реагировать на статистику мониторинга и только на условиях, которые указывают на определенную, действующую проблему. Низкий коэффициент попадания в буфер не подлежит действию и не указывает на реальную проблему, но сервер, который не отвечает на попытку подключения, является реальной проблемой, которая должна быть решена.

    Совет по производительности MySQL № 9: Изучите три правила индексирования

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

    Индексы, если они правильно разработаны, служат для трех важных целей на сервере базы данных:

    1. Индексы позволяют серверу находить группы соседних строк вместо отдельных строк. Многие считают, что целью индекса является поиск отдельных строк, но поиск отдельных строк приводит к случайным операциям с дисками, что происходит медленно. Гораздо лучше найти группы строк, все или большинство из которых интересны, чем поиск строк по одиночке.
    2. Индексы позволяют избежать сортировки сервера, читая строки в нужном порядке. Сортировка является дорогостоящей. Чтение строк в желаемом порядке происходит намного быстрее.
    3. Индексы позволяют серверу удовлетворять целые запросы только из индекса, избегая необходимости вообще обращаться к таблице. Это свойство известно по-разному как индекс покрытия (covering index) или запрос только по индексу (index-only query).

    Если вы можете разработать свои индексы и запросы для использования этих трех возможностей, вы можете сделать свои запросы на несколько порядков быстрее!

    Совет по производительности MySQL № 10: используйте опыт ваших коллег

    Не пытайтесь идти в одиночку. Если вы решаете проблему и делаете то, что кажется логичным и разумным для вас, это здорово. И это возможно работает в 19 разах из 20. В 21-ый же раз вы можете загнать себя в тупик, выход из которого будет очень дорогостоящим и трудоемким. При этом вы будите думать, что применяемые Вами меры имеют большой смысл и шансы на успех.

    Посетите несколько ресурсов, связанных с MySQL (например, наш портал), и найдите информацию, выходящую за рамки инструментов и руководств по устранению неполадок. Есть очень знающие люди, скрывающиеся в списках рассылок, форумов, не сайтах в формате Вопрос-ответ (Q & A) и т. д. Конференции, выставки и местные групповые мероприятия предоставляют ценные возможности для получения информации и построения отношений с коллегами, которые могут помочь вам в решении многих задач.

    Настройка MySQL

    Основное конфигурирование MySQL осуществляется с помощью файла /etc/my.cnf или /etc/mysql/my.cnf

    Указание кодировок и collation

    В секцию [mysqld] добавим строки:

    character-set-server=utf8
    collation-server=utf8_general_ci
    init-connect=»SET NAMES utf8″

    • character-set-server — кодировка для всего сервера;
    • collation-server — порядок символов и строк на основе алфавитного порядка и классов эквивалентности;
    • init-connect — строка, выполняемая для каждого клиента при соединении.

    Ограничение количества, таймауты и источники соединений

    bind-address=localhost
    # Отключаем определение доменного имени для IP-адресов
    skip-name-resolve
    # Максимальное количество соединений
    max_connections = 250
    # Отключаем использованием symbolic-links
    symbolic-links=0
    # Таймауты
    interactive_timeout=60
    wait_timeout=60

    Увеличение числа открытых файлов

    В большинстве Linux-систем по умолчанию лимит открытия файловых дескрипторов установлен в 1024, для работы этого недостаточно.

    Проверим текущие опции:

    Внесем требуемые лимиты в /etc/security/limits.conf

    Динамически изменим текущие лимиты:

    Проверим soft limit:

    Текущие лимиты в MySQL проверим SQL-запросом:

    SHOW VARIABLES LIKE ‘%open_files%’

    Оптимизация MySQL для MyISAM

    Оптимизация параметров MySQL позволяет значительно увеличить производительность MyISAM.

    Буферы

    Основными параметрами являются key_buffer_size (буфер для работы с ключами и индексами) и sort_buffer (буфер для сортировки).

    key_buffer_size = 64M
    sort_buffer_size = 32M

    При наличии 16Гб памяти и более, рекомендуется увеличить key_buffer_size до 128M-256M. Если Вы не используете MyISAM таблицы, рекомендуется установить размер key_buffer_size в 32Мб для хранения индексов временных таблиц.

    Кэш запросов указывается в опции query_cache_size, ограничение на кэшируемый элемент в query_cache_limit, кэш открытых таблиц в table_open_cache.

    table_open_cache = 2048
    query_cache_limit = 2M
    query_cache_size = 128M
    query_cache_type = 1
    thread_cache_size = 16

    max_heap_table_size = 128M
    tmp_table_size = 128M

    Будьте внимательны при установке завышенного значения query_cache_size, т.к. это может привести к ожиданию блокировок (Be careful not to set the size of the cache too large. Due to the need for threads to lock the cache during updates, you may see lock contention issues with a very large cache). Мы не рекомендуем устанавливать значение больше 256M.

    Параметр thread_cache_size указывает количество тредов (threads), уходящих в кеш при отключении клиента. При новом подключении тред не создается, а берется из кеша, что позволяет экономить ресурсы при больших нагрузках. При наличии 32Гб памяти и более рекомендуем увеличить thread_cache_size до 32, table_open_cache в диапазон 4096-8192, query_cache_size до 256M.

    Перенос временных файлов MySQL в память

    Проверяем наличие /dev/shm:

    Настройки размещаются в /etc/fstab , рекомендуем указать размер, например, 1G:

    none /dev/shm tmpfs defaults,size=1G 0 0

    Если внесли изменения, то перемонтируем:

    mount -o remount /dev/shm

    В конфигурационном файле указываем:

    В случае, если используется Apparmor, то внесите используемый путь (/dev/shm или /run/mysql) в конфигурационный файл /etc/apparmor.d/usr.sbin.mysqld, например:

    service apparmor restart

    Оптимизация MySQL для InnoDB

    Стандартно все таблицы и индексы хранятся в одном файле, мы рекомендуем использовать опцию innodb_file_per_table для установки хранения каждой таблицы в отдельном файле. Дополнительно необходимо корректно рассчитать параметр innodb_open_files, до версии MySQL 5.6.6 он устанавливался в значение 300, с версии MySQL 5.6.6 рассчитывается автоматически и имеет значение по умолчанию -1.

    Значение innodb_open_files и table_open_cache рассчитывается как количество таблиц во всех базах, умноженное на 2, ориентировочно рекомендуем устанавливать обе опции в 4096 или 8192.

    innodb_file_per_table = 1
    table_open_cache = 4096
    innodb_open_files = 4096

    При использовании только InnoDB часть опций требует корректировки:

    key_buffer_size = 32M
    max_allowed_packet = 1M
    sort_buffer_size = 32M
    read_buffer_size = 256K
    read_rnd_buffer_size = 1M
    thread_stack = 128K
    query_cache_limit = 1M
    query_cache_size = 0
    query_cache_type = 1
    thread_cache_size = 32
    max_heap_table_size = 128M
    tmp_table_size = 128M

    Обратите внимание, при работе с InnoDB мы отключаем query_cache_size установкой его значения в 0, исключающее лишние действия по работе с кэшем, что особенно важно при активной работе с большими объемами данных. С версии MySQL 5.6 query_cache_size отключен, а с версии 8.0 удален.

    Важнейшей настройкой MySQL при работе с InnoDB является innodb_buffer_pool_size, устанавливается по принципу «чем больше, тем лучше». Рекомендуется выделять до 70-80% оперативной памяти сервера под innodb_buffer_pool_size. Не забудьте провести расчет использования памяти Apache и дополнительным программным обеспечением для исключения сваливания системы в swap. Для сервера с 16Гб устанавливается в диапазоне 10-12G и разделяем его на 4 секции, для сервера с 32Гб устанавливаем значение в диапазоне 20-24G и разделяем его на 8 секций, :

    innodb_buffer_pool_size = 10G
    innodb_buffer_pool_instances = 4

    Установка большого размера innodb_log_file_size может привести к увеличению быстродействия, при этом увеличится время восстановления данных, выберите от 258M до 1G.

    innodb_log_file_size = 256M
    innodb_log_buffer_size = 32M

    При установке значения в 2 буфер не сбрасывается на диск, а только в кэш операционной системы. Установка значения в 0 увеличит быстродействие, однако возможна потеря последних данных при аварийном выключении mysql-сервера.

    Количество потоков ввода/вывода файлов в InnoDB задается опциями innodb_read_io_threads, innodb_write_io_threads, обычно этому параметру присваивается значение 4 или 8, на быстрых SSD-дисках установите в 16. Значение innodb_thread_concurrency установите в количество ядер * 2.

    innodb_read_io_threads = 4
    innodb_write_io_threads = 4
    innodb_thread_concurrency = 32

    Для ускорения работы с INFORMATION_SCHEMA, SHOW TABLE STATUS или SHOW INDEX отключим обновление статистики при выполнении таких операций, с версии MySQL 5.6.6 innodb_stats_on_metadata отключено по умолчанию:

    Для версии MySQL 5.7+ укажите дополнительные опции:

    performance_schema = OFF
    skip-log-bin
    sync_binlog = 0

    После применения изменений перезагрузите MySQL:

    service mysqld restart

    Динамическое изменение размера innodb_buffer_pool_size

    С версии MySQL 5.7.5 допускается динамическое изменение размера innodb_buffer_pool_size, однако необходимо помнить, что размер innodb_buffer_pool_size должен быть пропорционален innodb_buffer_pool_chunk_size.

    Запросим размер innodb_buffer_pool_chunk_size:

    Получим значение, например, 128 мегабайт. Нам требуется динамически установить размер innodb_buffer_pool_size в 16G, соответсвенно 16G / 128M = 125, установим значение:

    SET GLOBAL innodb_buffer_pool_size = (SELECT @@innodb_buffer_pool_chunk_size) * 125

    Цукерберг рекомендует:  Node js - Как открыть вторую страницу через express в nodejs
    Понравилась статья? Поделиться с друзьями:
    Все языки программирования для начинающих