Сухой товар! Оптимизация производительности SQL, написание качественных операторов SQL

задняя часть

При написании операторов SQL мы часто обращаем внимание на результаты выполнения SQL, но действительно ли мы обращаем внимание на эффективность выполнения SQL и обращаем ли мы внимание на спецификацию написания SQL?

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

1. Ограничьте оптимизацию страниц

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

ВЫБЕРИТЕ id ИЗ ОГРАНИЧЕНИЯ 1000,10 скоро

SELECT id FROM A LIMIT 90000,10 медленный

Вариант первый:

select id from A order by id limit 90000,10;

Если мы используем в сочетании с order by. Очень быстро, 0,04 секунды нормально. Потому что первичный ключ id используется для индексации! Конечно, можно ли использовать индекс, еще нужно определить по бизнес-логике, просто чтобы напомнить всем, что его нужно использовать с осторожностью при подкачке!

Вариант 2

select id from A order by id  between 90000 and 90010;

2. Используйте лимит 1 и топ 1, чтобы получить линию

Когда какая-то бизнес-логика выполняет операции запроса (особенно по определенному полю DESC, берите самое большое), вы можете использовать limit 1 или top 1, чтобы остановить [индекс базы данных] и продолжить сканирование всей таблицы или индекса.

Контрпример

SELECT id FROM A LIKE 'abc%'

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

SELECT id FROM A LIKE 'abc%' limit 1

3. Ни в коем случае не используйте select * from table, замените "*" конкретным списком полей, не возвращайте неиспользуемые поля и избегайте полного сканирования!

Контрпример

SELECT * FROM A

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

SELECT id FROM A

4. Оптимизация массовой вставки

Контрпример

INSERT into person(name,age) values('A',24)
INSERT into person(name,age) values('B',24)
INSERT into person(name,age) values('C',24)

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

INSERT into person(name,age) values('A',24),('B',24),('C',24),

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

5. Оптимизация подобных заявлений

Контрпример

SELECT id FROM A WHERE name like '%abc%'

Поскольку «%» используется перед abc, запрос должен проходить через запрос полной таблицы, если это не необходимо (нечеткий запрос должен содержать abc), в противном случае не добавляйте % перед ключевым словом

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

SELECT id FROM A WHERE name like 'abc%'

пример

версия mysql: 5.7.26

select nick_name from member where nick_name like '%小明%'

как «% Сяо Мин%»Индексы не используются!

select nick_name from member where nick_name like '小明%'

как «Сяо Мин%»Успешного использования индекса!

6. Оптимизация предложения where с использованием или

Обычно лучше заменить «или» союзом всех или союзом. Если ключевое слово or используется в предложении where, индекс будет отброшен.

Контрпример

SELECT id FROM A WHERE num = 10 or num = 20

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

SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=20

7. Оптимизация использования IS NULL или IS NOT NULL в предложении where

Контрпример

SELECT id FROM A WHERE num IS NULL

Используйте IS NULL или IS NOT NULL в предложении where, чтобы судить, индекс будет заброшен, и будет выполнен полный запрос таблицы.

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

Оптимизирован для установки значения по умолчанию 0 на num, чтобы убедиться, что в таблице нет нулевого значения в num, использование IS NULL чрезвычайно высоко при использовании SQL в реальных бизнес-сценариях, мы должны обратить вниманиеИзбегайте полного сканирования таблицы

SELECT id FROM A WHERE num=0

8. Оптимизация операций выражений над полями в предложениях where

Не выполняйте функции, арифметические операции или другие операции с выражениями слева от «=» в предложении where, иначе система может неправильно использовать индекс.

  • 1
SELECT id FROM A WHERE datediff(day,createdate,'2019-11-30')=0

оптимизирован для

SELECT id FROM A WHERE createdate>='2019-11-30' and createdate<'2019-12-1'
  • 2
SELECT id FROM A WHERE year(addate) <2020

оптимизирован для

SELECT id FROM A where addate<'2020-01-01'

9. Проблема с отсортированным индексом 

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

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

10. Попробуйте заменить union на union all

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

11. Внутреннее соединение и левое соединение, правое присоединение, подзапросы

  • Во-первых: внутреннее соединение также называется эквисоединением, левое/правое соединение — внешним соединением.
SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;

SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;

SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

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

SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

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

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

Контрпример

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

Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);

Время выполнения: около 2 с

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

Select* from A inner join B ON A.uuid=B.uuid where b.uuid>=3000;  这个语句执行测试不到一秒；

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

  • Третье: при использовании JOIN вы должны использовать небольшие результаты для достижения больших результатов.

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

Контрпример

Select * from A left join B A.id=B.ref_id where  A.id>10

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

select * from (select * from A wehre id >10) T1 left join B on T1.id=B.ref_id;

12.существует и в оптимизации

SELECT * from A WHERE id in ( SELECT id from B )

SELECT * from A WHERE id EXISTS ( SELECT 1 from A.id= B.id )

анализировать:

in состоит в том, чтобы пройти сравнение в памяти

Exist должен запрашивать базу данных, поэтому, когда количество данных в B относительно велико, эффективность exists лучше, чем in**

in() выполняется только один раз, кэширует все поля id в таблице B, а затем проверяет, равен ли id таблицы A id в таблице B. Если id равен, записи таблицы A добавляются к результату устанавливается до завершения обхода Все записи в таблице А.

Принцип работы In подобен следующему коду

    List resultSet={};

    Array A=(select * from A);
    Array B=(select id from B);

    for(int i=0;i<A.length;i++) {
          for(int j=0;j<B.length;j++) {
          if(A[i].id==B[j].id) {
             resultSet.add(A[i]);
             break;
          }
       }
    }
    return resultSet;

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

Например: таблица A имеет 10 000 записей, таблица B имеет один миллион записей, тогда можно пройти до 10000 * 1000000, низкая эффективность.

Другой пример: таблица A имеет 10 000 записей, а таблица B имеет 100 записей, поэтому можно пройти не более 10 000 * 100 раз, количество обходов значительно уменьшается, а эффективность значительно повышается.

  Вывод: in() подходит для случая, когда данных в таблице B меньше, чем в таблице A

exists() будет выполнять A.length() раз, а код процесса выполнения выглядит следующим образом.


List resultSet={};
Array A=(select * from A);
for(int i=0;i<A.length;i++) {
    if(exists(A[i].id) {  //执行select 1 from B where B.id=A.id是否有记录返回
       resultSet.add(A[i]);
    }
}return resultSet;

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

Например, если в таблице A 10 000 записей, а в таблице B — 1 000 000 записей, функция exists() будет выполнена 10 000 раз, чтобы определить, равен ли идентификатор в таблице A идентификатору в таблице B.

Такие, как: таблица имеет 10 000 записей, таблицу b Существует 100 миллионов записей, то существует () выполняется в 10 000 раз, потому что это время выполнения дожитостей, тем более видимым таблицей данных B, для существующих () эффекта Отказ

Другой пример: таблица A имеет 10 000 записей, а таблица B имеет 100 записей, тогда exists() по-прежнему выполняется 10 000 раз, лучше использовать in() для обхода 10 000 * 100 раз, потому что in() предназначен для обхода и сравнения в память, и exists() должен запросить базу данных,

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

Вывод: exists() подходит для случая, когда данных таблицы B больше, чем данных таблицы A.

Категории