Некоторые практические методы оптимизации баз данных MySQL на одном компьютере

Оптимизация базы данных имеет много аспектов. В зависимости от объема данных, поддерживаемых, она может быть разделена на две фазы:单体数据库 и разделение базы данных и таблиц. Первый обычно может поддерживать данные до 5 миллионов или 10 ГБ, и если это значение превышено,则需要 рассмотреть разделение базы данных и таблиц. Кроме того, при интервьюировании в крупных компаниях часто начинается с单体 базы данных и постепенно переходит к разделению базы данных и таблиц, при этом穿插 много вопросов по оптимизации базы данных. Эта статья пытается описать некоторые практики оптимизации单体 базы данных, базирующиеся на MySQL. Если есть不合理ные моменты, пожалуйста, исправьте.

1、表结构优化

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

1.1、字符集

В общем случае лучше выбирать UTF-8, хотя при хранении данных GBK использует меньше места для хранения, чем UTF-8. Однако UTF-8 совместим с языками всех стран, и на самом деле нам не нужно жертвовать этим объемом места для расширяемости. На самом деле, если нужно перейти от GBK к UTF-8,代价是非常高的，требуется миграция данных, а объем места для хранения можно решить, расширив жесткий диск.

1.2、Primary ключ

При использовании MySQL innodb,底层 модель хранения innodb - это B+ дерево, он использует ключ PRIMARY как клумерное индексирование, использует вставленные данные в качестве узлов листа, через ключ PRIMARY можно быстро найти узел листа и быстро получить запись. Поэтому при разработке таблицы необходимо добавить ключ PRIMARY, и лучше всего, чтобы он был auto-increment. Потому что auto-increment PRIMARY ключ позволяет вставленным данным вставляться в листья B+ дерева по порядку PRIMARY ключа, так как это по порядку, этот ввод почти не требует перемещения существующих других данных, поэтому ввод очень эффективен. Если PRIMARY ключ не auto-increment, то значение PRIMARY ключа будет примерно случайным, в этом случае может потребоваться перемещение большого объема данных, чтобы обеспечить свойства B+ дерева, что увеличит不必要的 затраты.

1.3、Столбцы

1.3.1、Столбцы, созданные с индексом, должны иметь ограничение not null и значение по умолчанию

1.3.2、Не рекомендуется использовать float, double для хранения десятичных чисел, чтобы предотвратить потерю точности, рекомендуется использовать decimal

1.3.3、Не рекомендуется использовать Text/blob для хранения большого объема данных, так как чтение и запись больших текстов может привести к значительным затратам на ввод-вывод, а также к использованию кэша MySQL, что может значительно снизить производительность базы данных при высоком уровне конcurrency, рекомендуется хранить большие текстовые данные в специальных системах файлового хранения, в MySQL хранить только адрес доступа к файлу, например, статьи в блоге можно хранить в файлах, а в MySQL сохранять только относительный адрес файла.

1.3.4、Длина типа varchar не должна превышать 8K.

1.3.5、Для временных типов рекомендуется использовать Datetime, а не timestamp, хотя Datetime занимает 8 байт, а timestamp только 4 байта, но последний должен быть не пустым и чувствителен к часовым поясам.

1.3.6、Рекомендуется добавить в таблицу два поля gmt_create и gmt_modified, чтобы записывать время создания и изменения данных. Причина создания этих полей в том, чтобы было удобнее искать проблемы.

1.4、Создание индексов

1.4.1、На этой стадии, так как бизнес не изучен, лучше не добавлять индексы бездумно, добавлять только индексы для полей, которые определенно будут использоваться.

1.4.2、Длина индексов, созданных для инnoDB-одиночных столбцов, не должна превышать 767 байт, если длина больше, то используется как префикс индекса первые 255 байт

1.4.3、Длина индексов, созданных для инnoDB- 组ированных индексов, не должна превышать 767 байт, всего не должно быть больше 3072 байт

2、SQL оптимизация

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

2.1、基本查询

Обычно запросы должны использовать индексы, если индексов нет, рекомендуется изменить запрос, добавив поле с индексом, если это невозможно из-за бизнес-сценария, то необходимо посмотреть, как часто используется этот запрос, если часто, например, более 100000 в день, то необходимо добавить индекс, если не часто, например, более 100 в день, то можно рассмотреть возможность сохранения текущего состояния. Кроме того, следует минимизировать использование select *, использовать только необходимые поля в SQL запросе, не необходимые поля не должны быть выбраны, чтобы не浪费 I/O и памяти.

2.2, эффективная разбивка на страницы

limit m,n по сути это сначала выполняется limit m+n, затем выбираются n строк из m-го ряда, поэтому при увеличении страницы limit m grows, что снижает производительность. Например

select * from A limit 100000,10, такое SQL предложение имеет низкую производительность, рекомендуется изменить его на следующую версию:

selec id,name,age from A where id >=(select id from A limit 100000,1) limit 10

2.3, поисковые запросы в диапазоне

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

2.4, неопределенный поиск like

Использование таких предложений, как like %name%, не использует индексы, что эквивалентно полному сканированию таблицы, при малом объеме данных это не создает больших проблем, но при увеличении объема данных производительность сильно снижается, рекомендуется использовать поисковые системы для замены таких неопределенных поисков, если это невозможно, то перед неопределенным поиском рекомендуется добавить условие, которое использует индекс.

2.5, многотабельное соединение

Подзапросы и join могут быть использованы для получения данных между несколькими таблицами, но производительность подзапросов较差, рекомендуется изменить подзапрос на join. Для join MySQL используется алгоритм Nested Loop Join, то есть результат предыдущей таблицы используется для поиска в следующей таблице, например, если результат предыдущей таблицы составляет 100 строк, а следующая таблица содержит 100000 строк, то нужно фильтровать результат в集合е 100 * 100000 строк. Поэтому, лучше использовать таблицы с малыми результатами для join с большими таблицами, а также устанавливать индексы на полях join. Если индексы установить невозможно,则需要 установить достаточный размер буфера join. Если все эти хитрости не решают проблему снижения производительности join, то лучше не использовать join, а разбить один запрос на два простых запроса. Кроме того,尽量避免多表连接超过三张表，因为在 большинстве случаев это приведет к снижению производительности, рекомендуется разбивать SQL.

3, Оптимизация базового пула подключений к базе данных

Базовый пул подключений к базе данных по сути является кэшем, это средство для抵抗 высокого параллелизма. Оптимизация базового пула подключений к базе данных заключается в оптимизации параметров. Обычно мы используем пул подключений DBCP, его конкретные параметры такие:

3.1 initialSize

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

3.2, minIdle

Минимальное количество сохраненных свободных подключений. DBCP запускает в фоновом режиме поток для сбора свободных подключений. Когда этот поток собирает свободные подключения, он сохраняет количество подключений minIdle. Обычно это устанавливается в 5, но если параллелизм действительно низок, можно установить 1.

3.3, maxIdle

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

3.4, maxActive

Максимальное количество активных подключений, устанавливается в зависимости от максимального возможного параллелизма, который можно接受的. Например, максимальный возможный параллелизм для одного сервера можно принять равным 100. Тогда после установки maxActive в 100 можно обслуживать одновременно только 100 запросов, излишние запросы будут отклонены после максимального времени ожидания. Этот параметр необходимо установить, чтобы предотвратить злонамеренные атаки на параллелизм и защитить базу данных.

3.5, maxWait

Время ожидания максимального подключения, рекомендуется устанавливать короткое время, например, 3с, чтобы запросы быстро失败了, так как в процессе ожидания подключения для запроса线程 не может быть освобожден, а количество параллельных потоков в одном сервере ограничено. Если это время установлено слишком долго, например, как рекомендуется в Интернете, 60с, то этот поток в течение 60с не может быть освобожден. Если таких запросов много, то количество доступных потоков в приложении уменьшается, и служба становится недоступной.

3.6, minEvictableIdleTimeMillis

Время, в течение которого соединение остается空闲 и не возвращается в пул, по умолчанию 30 минут.

3.7, validationQuery

SQL-запрос для проверки состояния соединения, обычно это простая команда SQL, рекомендуется устанавливать.

3.8, testOnBorrow

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

3.9, testOnReturn

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

3.10, testWhileIdle

После включения, в фоновом режиме线程 для очистки соединений будет периодически проверять空闲 соединения на validateObject, если соединение неактивно, то оно будет удалено, что не повлияет на производительность, рекомендуется включить.

3.11, numTestsPerEvictionRun

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

3.12, Предварительный нагрев пула соединений

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

4, Оптимизация индексов

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

4.1, Первый уровень индексов

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

4.2, Второй уровень индексов

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

4.3, Третий уровень индексов

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

4.4. Избирательность индекса

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

5. Архивация исторических данных

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

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

Заявление: содержание этой статьи взято из Интернета, авторские права принадлежат соответствующему автору, материалы предоставлены пользователями Интернета в добровольном порядке, сайт не имеет права собственности, материалы не редактировались вручную, и сайт не несет ответственности за соответствующие юридические вопросы. Если вы обнаружите подозрительное нарушение авторских прав, пожалуйста, отправьте письмо по адресу: notice#oldtoolbag.com (при отправке письма замените # на @),并提供相关证据. В случае подтверждения, сайт немедленно удалят涉嫌侵权的内容。