Много раз «проблема с производительностью», которая возникает в нашей программе, на самом деле является проблемой с «массовым» кодом, который мы написали, проблемой с нашим собственным кодом и проблемой с использованием операторов DML Mysql. Ниже приводится краткое изложение моментов, на которые следует обратить внимание при использовании операторов MySQL DML.
заselect *Всегда будьте осторожны
В большинстве случаев не требуетсяselect *из. После использования такого оператора оптимизация, такая как сканирование покрытия индекса, не может быть выполнена оптимизатором, и будут добавлены дополнительные операции ввода-вывода, памяти и потребления ЦП.
Конечно, используяselect *Не все так плохо, используйте это с умомselect *Это может упростить разработку и повысить возможность повторного использования одного и того же кода.
Было ли просканировано слишком много дополнительных записей
Иногда обнаруживается, что некоторым запросам может потребоваться прочитать тысячи строк данных, но вернуть лишь несколько или очень мало результатов. Для оптимизации можно использовать следующие методы:
- Посмотрите, можете ли вы изменить структуру таблицы. Например, с помощью сводной таблицы
- Посмотрите, является ли способ получения результатов данных лучшим, и является ли дорога уже самой короткой.
- Используя покрывающий индекс, поместите все необходимые столбцы в индекс, чтобы сократить шаги выборки данных из соответствующей строки в возвращаемой таблице.
Разделить некоторые операторы SQL
В традиционной интернет-системе подчеркивается, что сетевых соединений должно быть как можно меньше, а уровень данных должен выполнять как можно больше работы за одно соединение, чтобы предотвратить установление множественных соединений.Однако эта идея не применима к MySQL.MySQL спроектирован так, чтобы разрешать соединения, а Disconnects очень легкие и могут поддерживать более 100 000 запросов в секунду на типичном сервере.
Поэтому в некоторых сценариях большой запрос можно «разделять и властвовать», разбивать на маленькие запросы, а затем объединять. Например следующая ситуация:
- Для полного запроса данных он становится разбиением на страницы. Если в таблице десятки миллионов фрагментов данных, выбрать все за один раз определенно невозможно. Его можно заменить, принимая порцию за раз и разделяя давление на один раз.
- При удалении большого количества старых данных рекомендуется не очищать большую выписку за один раз.
一次删一万条. Если для одновременного выполнения используется большой оператор, может потребоваться заблокировать большой объем данных за один раз, занимая большое количество транзакций журнала, и позволить Mysql остановиться на этом.Чтобы избежать этой ситуации, он лучше всего удалять около 10 000 штук данных за один раз, а затем делать паузу на некоторое время после каждого удаления, чтобы разогнать разовую нагрузку на сервер.
Примечание. Хотя Mysql устанавливает соединение очень легко, это не означает, что его можно запрашивать один за другим в цикле, а затем соединять, поэтому эффективность по-прежнему очень низкая, и обычно это точка оптимизации sql в работе.
Используйте с осторожностьюjoinдействовать
Это считается табу. Многие интернет-продукты компаний запрещают операцию объединения. Вместо этого сначала извлеките идентификатор данных из одной таблицы, а затем используйте его из другой таблицы.where inДве операции запроса одной таблицы для запроса. В основном по следующим причинам:
- Сделайте кеш приложения (redis, memcache и т. д.) более эффективным. Например, если некоторые идентификаторы запрашиваются в первой таблице, если кеш попал, оператор where in можно опустить.
- Легче заниматься развитием бизнеса, удобно разбивать базу данных, проще добиться высокой производительности и высокой расширяемости.
- После сортировки идентификаторов в порядке возрастания эффективность запроса становится более эффективной, чем случайная ассоциация соединения.
- Сократите избыточные запросы. Двойной запрос на уровне приложения означает, что приложению нужно запросить запись только один раз, в то время как при использовании объединения может потребоваться повторное сканирование для доступа к части данных.
- Запрос к одной таблице может снизить количество конфликтов при блокировках.
Если его необходимо использовать, его можно оптимизировать следующими способами:
- удостовериться
ONилиusingВ предложении есть индекс столбца - обеспечить, чтобы любой
group byиorder byВыражения в относятся только к одному столбцу в таблице.
В сценариях с высокими требованиями к производительности избегайте его использования в запросах.临时表
Временное представление MySQL не имеет индексов, использование временных таблиц обычно означает снижение производительности, поэтому в сценариях с высокими требованиями к производительности лучше не использовать операции с временными таблицами:
- на неиндексированных полях
group byработать. -
UNIONЗапрос. - Подзапросы в запросах.
- часть
order byтакие операции, какdistinctфункция иorder byиспользуется вместе иdistinctиorder byто же поле. Например, в некоторых случаяхgroup byиorder byПоля разные.
Использовать ли временную таблицу, вы можете передатьexplainвидеть, видетьExtraрезультат столбца, если он присутствуетUsing temporaryвам нужно обратить внимание.
count()оптимизация функций
count()Функция требует особого внимания: она не считает поля со значением NULL! Следовательно: вы не можете указать столбец результата запроса для подсчета количества строк результата. которыйcount(xx column)не слишком хорошо.
Если вы хотите подсчитать набор результатов, используйтеcount(*), производительность также будет хорошей.
Старайтесь не использовать подзапросы
Старайтесь не использовать подзапросы, вместо этого используйте ассоциации
Оптимизировать разбиение на страницыlimit
Обычно, когда мы разбиваем страницы, мы обычно используемlimit 50, 10такое заявление. Меньше данных — это хорошо, но производительность может быть проблемой, когда смещение данных очень велико, например.select xx,xxx from test_table limit 100000020, 20. После сканирования 100000020 фрагментов данных было возвращено только 20 фрагментов данных. В настоящее время мы можем использовать следующие два способа оптимизации:
использоватьbetween andи индекс первичного ключа
Используя первичный ключ для увеличения идентификатора, если мы знаем верхнюю границу пейджинга, приведенный выше запрос можно переписать как:select xxx, xxx from test_table where id between xxxxx and xxxx.
Используйте самоувеличивающийся индекс первичного ключа,order byдобавлятьlimit, без использования смещения
limitиoffsetПроблема, собственно, в том, чтоoffsetПроблема заключается в том, что MySQL сканирует большое количество ненужных строк, а затем отбрасывает их. Если вы используете определенную метку для записи позиции данных, снятых в прошлый раз, вы можете начать сканирование непосредственно с позиции закладки в следующий раз, чтобы избежать использованияoffset.
Например, приведенный выше запрос можно изменить на:
Первый набор данных: «выбрать xxx, xxxx из таблицы test_table по id desc limit 20;
Таким образом, получается значение идентификатора декомпозиции текущих данных и следующих данных, и запрос следующей страницы может знать, что:select xxx, xxx from test_table where id < '上页id分界值' order by id desc limit 20
Знакомый и гибкийexplain
Ниже приведен весь процесс выполнения запроса mysql,explainВы можете видеть красную часть рисунка,
explainБудет отображаться много полей и содержимого, и содержимое часто трудно запомнить.При использовании вы можете просмотреть следующие диаграммы:объяснить диаграмму