Расскажите о 15 советах по оптимизации sql

предисловие

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

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

Итак, как оптимизировать оператор sql?

В этой статье мы делимся некоторыми советами по оптимизации sql по 15 аспектам, которые надеются вам помочь.

Недавно я случайно получил заметку о чистке, написанную крупным производителем BAT, которая открыла мне сразу вторую линейку Ren и Du, и я все больше чувствую, что алгоритм не так сложен, как я себе представлял.

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

1 Избегайте использования выбора *

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

Пример счетчика:

select * from user where id=1;

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

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

И самый главный вопрос:select *не пойдет覆盖索引, будет большое количество回表операции, что приводит к очень низкой производительности запроса sql.

Итак, как его оптимизировать?

Положительный пример:

select name,age from user where id=1;

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

2 Используйте союз всех вместо союза

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

тогда как если вы используетеunion allключевое слово, чтобы получить все данные, включая повторяющиеся данные.

Пример счетчика:

(select * from user where id=1) 
union 
(select * from user where id=2);

Процесс переупорядочивания требует обхода, сортировки и сравнения, что занимает больше времени и потребляет больше ресурсов ЦП.

Поэтому, если вы можете использовать union all, постарайтесь не использовать union.

Положительный пример:

(select * from user where id=1) 
union all
(select * from user where id=2);

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

3 Маленькие таблицы управляют большими столами

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

Если есть две таблицы, order и user, таблица order содержит 10 000 элементов данных, а таблица user содержит 100 элементов данных.

В настоящее время, если вы хотите проверить список заказов, размещенных всеми действительными пользователями.

можно использоватьinРеализация ключевого слова:

select * from order
where user_id in (select id from user where status=1)

также можно использоватьexistsРеализация ключевого слова:

select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)

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

Зачем?

Потому что, если ключевое слово in включено в оператор sql, оно сначала выполнит ключевое слово in.子查询语句, а затем выполните инструкцию снаружи внутри. Если объем данных в in невелик, скорость запроса выше, как условие.

И если оператор sql содержит ключевое слово exists, он сначала выполняет оператор слева от exists (т. е. основной оператор запроса). Затем используйте его как условие для соответствия утверждению справа. Если они совпадают, вы можете запросить данные. Если совпадений нет, данные отфильтровываются.

В этом требовании таблица заказов содержит 10 000 элементов данных, а пользовательская таблица — 100 элементов данных. Таблица заказов — это большая таблица, а таблица пользователей — это маленькая таблица. Если таблица заказов находится слева, лучше использовать ключевое слово in.

в заключении:

inПрименимо к большому столу слева и маленькому столу справа.
existsПрименимо к маленькому столу слева и большому столу справа.

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

4 Пакетные операции

Что делать, если у вас есть пакет данных, который необходимо вставить после бизнес-обработки?

Пример счетчика:

for(Order order: list){
   orderMapper.insert(order):
}

Вставьте данные один за другим в цикле.

insert into order(id,code,user_id) 
values(123,'001',100);

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

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

Так как его оптимизировать?

Положительный пример:

orderMapper.insertBatch(list):

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

insert into order(id,code,user_id) 
values(123,'001',100),(124,'002',100),(125,'003',101);

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

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

5 предел многократного использования

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

Пример счетчика:

select id, create_date 
 from order 
where user_id=123 
order by create_date asc;

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

List<Order> list = orderMapper.getOrderList();
Order order = list.get(0);

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

Итак, как его оптимизировать?

Положительный пример:

select id, create_date 
 from order 
where user_id=123 
order by create_date asc 
limit 1;

использоватьlimit 1, возвращаются только данные с наименьшим временем заказа пользователя.

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

Например:

update order set status=0,edit_time=now(3) 
where id>=100 and id<200 limit 100;

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

6 дюймов это слишком много посередине

Для интерфейса пакетных запросов мы обычно используемinКлючевые слова отфильтровывают данные. Например: я хочу запросить информацию о пользователе в пакетах через некоторые указанные идентификаторы.

Оператор sql выглядит следующим образом:

select id,name from category
where id in (1,2,3...100000000);

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

Что нам делать тогда?

select id,name from category
where id in (1,2,3...100)
limit 500;

Вы можете ограничить данные с помощью limit в sql.

Однако мы больше о добавлении ограничений в бизнес-код, псевдокод выглядит следующим образом:

public List<Category> getCategory(List<Long> ids) {
   if(CollectionUtils.isEmpty(ids)) {
      return null;
   }
   if(ids.size() > 500) {
      throw new BusinessException("一次最多允许查询500条记录")
   }
   return mapper.getCategoryList(ids);
}

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

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

7 Добавочный запрос

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

Пример счетчика:

select * from user;

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

Что нам делать тогда?

Положительный пример:

select * from user 
where id>#{lastId} and create_time >= #{lastCreateTime} 
limit 100;

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

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

8 Эффективный пейджинг

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

Пейджинг обычно используется в mysqllimitКлючевые слова:

select id,name,age 
from user limit 10,20;

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

Например, параметр пагинации теперь выглядит так:

select id,name,age 
from user limit 1000000,20;

MySQL найдет 1 000 020 фрагментов данных, затем отбросит первые 1 000 000 фрагментов данных и проверит только последние 20 фрагментов данных, что является пустой тратой ресурсов.

Итак, как разбить на страницы эти огромные данные?

Оптимизировать sql:

select id,name,age 
from user where id > 1000000 limit 20;

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

все еще можно использоватьbetweenОптимизировать пагинацию.

select id,name,age 
from user where id between 1000000 and 1000020;

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

9 Замена подзапросов запросами на соединение

Если вам нужно запросить данные из более чем двух таблиц в mysql, обычно есть два метода реализации:子查询и连接查询.

Пример подзапроса выглядит следующим образом:

select * from order
where user_id in (select id from user where status=1)

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

Преимущество оператора подзапроса состоит в том, что он прост и структурирован, если число задействованных таблиц невелико.

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

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

select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1

10 Присоединяемых таблиц не должно быть слишком много

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

Пример счетчика:

select a.name,b.name.c.name,d.name
from a 
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id
inner join d on d.c_id = c.id
inner join e on e.d_id = d.id
inner join f on f.e_id = e.id
inner join g on g.f_id = f.id

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

И если совпадения нет, соединение вложенного цикла заключается в чтении строки данных из двух таблиц для попарного сравнения, а сложность равна n^2.

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

Положительный пример:

select a.name,b.name.c.name,a.d_name 
from a 
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id

Если вам необходимо запросить данные в других таблицах при реализации бизнес-сценария, вы можете запросить данные в таблицах a, b и c.冗余专门的字段, например: избыточное поле d_name в таблице a для сохранения данных, которые необходимо запросить.

Тем не менее, я видел некоторые ERP-системы раньше. Параллелизм невелик, но бизнес относительно сложен. Для запроса данных необходимо объединить более дюжины таблиц.

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

11 Будьте внимательны при присоединении

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

Чаще всего используются левые соединения и внутренние соединения.

left join: найти пересечение двух таблиц плюс оставшиеся данные в левой таблице.
inner join: найти данные пересечения двух таблиц.

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

select o.id,o.code,u.name 
from order o 
inner join user u on o.user_id = u.id
where u.status=1;

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

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

select o.id,o.code,u.name 
from order o 
left join user u on o.user_id = u.id
where u.status=1;

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

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

12 Управляет количеством индексов

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

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

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

Структура дерева B+, используемая mysql для сохранения индекса, индекс дерева B+ необходимо обновлять во время операций вставки, обновления и удаления. Если индексов слишком много, это потребует много дополнительной производительности.

Итак, вопрос, а что делать, если индексов в таблице слишком много, больше 5?

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

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

Итак, как система с высокой степенью параллелизма может оптимизировать количество индексов?

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

Для переноса некоторых функций запросов в базы данных других типов, таких как Elastic Search, HBase и т. д., достаточно создать несколько ключевых индексов в бизнес-таблице.

13 Выберите разумный тип поля

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

alter table order 
add column code char(20) NOT NULL;

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

alter table order 
add column code varchar(20) NOT NULL;

Если это поле фиксированной длины, например номер мобильного телефона пользователя, оно обычно составляет 11 бит и может быть определено как тип char длиной 11 байт.

Но если это поле имени предприятия, если оно определено как тип char, возникает проблема.

Если длина определена слишком долго, например, определение становится 200 байт, но фактическая длина всего 50 байт предприятия будут тратить 150 байт дискового пространства.

Если длина слишком короткие определения, такие как определение, становится 50 байт, но фактическое название бизнес составляет 100 байтов, память будет высокой и выбрасывает исключение.

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

Когда мы выбираем типы полей, мы должны следовать следующим принципам:

Если вы можете использовать числовые типы, вам не нужны строки, потому что символы, как правило, обрабатываются медленнее, чем числа.
Используйте маленькие типы как можно чаще, например: используйте бит для хранения логических значений, tinyint для хранения значений перечисления и т. д.
Строковое поле фиксированной длины типа char.
Строковое поле переменной длины типа varchar.
Десятичное число используется для поля количества, чтобы избежать проблемы потери точности.

Есть еще много принципов, которые здесь не перечислены.

14 Повысить эффективность группы за счет

У нас есть много бизнес-сценариев для использованияgroup byКлючевое слово, его основная функция — дедупликация и группировка.

Обычно это следуетhavingВместе с использованием после фильтрации данные представляют пакет в соответствии с определенными условиями.

Пример счетчика:

select user_id,user_name from order
group by user_id
having user_id <= 200;

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

Группировка — относительно трудоемкая операция, почему бы нам сначала не сузить область данных, а затем сгруппировать их?

Положительный пример:

select user_id,user_name from order
where user_id <= 200
group by user_id

Используйте условие where, чтобы отфильтровать избыточные данные перед группировкой, чтобы эффективность группировки была выше.

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

15 Оптимизация индекса

В оптимизации SQL есть очень важное содержание:索引优化.

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

Первым шагом в оптимизации индекса является проверка того, индексирован ли оператор sql.

Итак, как проверить, ушел ли sql в индекс?

можно использоватьexplainКоманда для просмотра плана выполнения mysql.

Например:

explain select * from `order` where code='002';

результат:По этим столбцам можно судить об использовании индекса.Значение столбцов, включенных в план выполнения, показано на следующем рисунке:Если вы хотите узнать больше о подробном использовании объяснения, вы можете прочитать другую мою статью "объясните | Этот несравненный меч оптимизации индексов, вы действительно знаете, как им пользоваться?》

Честно говоря, оператор sql не использует индекс, за исключением того, что индекс не создается, самая большая вероятность того, что индекс недействителен.

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

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

Кроме того, вы когда-нибудь сталкивались с такой ситуацией: это явно один и тот же sql, только входные параметры другие. Иногда идет индекс a, а иногда индекс b?

Да, иногда mysql выбирает неправильный индекс.

можно использовать при необходимостиforce indexЧтобы заставить запрос sql перейти к определенному индексу.

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