Как написать элегантные нативные операторы SQL?

MySQL

Предисловие:

В предыдущей статье, когда я говорил о базовой структуре Mysql, я использовал «как оператор запроса sql выполняется в структуре Mysql». Дано исчерпывающее объяснение. Я знаю конкретный процесс выполнения оператора запроса sql в архитектуре MySql, но для того, чтобы писать оператор sql лучше и быстрее, я думаю, что очень необходимо знать порядок выполнения каждого предложения в операторе sql. Друзья, читавшие предыдущую статью, должны знать, что выполнение каждого предложения в конце оператора SQL должно завершаться в исполнителе, а механизм хранения предоставляет интерфейс чтения и записи данных для исполнителя. Начните наше исследование прямо сейчас

Сводка полного порядка выполнения каждого предложения в операторе (выполняется в соответствии с порядковым номером)

  1. from (Примечание: сюда также входят подоператоры from)
  2. join
  3. on
  4. where
  5. group by (начните использовать псевдоним в select, который можно использовать в последующих операторах)
  6. avg,sum.... и т.д. агрегатные функции
  7. having
  8. select
  9. distinct
  10. order by
  11. limit

Выполните последовательный анализ каждого пункта

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

1. from

форма является началом оператора запроса.

  • Если это таблица, с этой таблицей будут непосредственно манипулировать;
  • Если за этим from следует подзапрос, содержимое подзапроса будет выполнено первым, а результатом подзапроса будет первая виртуальная таблица T1. (Примечание: поток выполнения в подзапросе также соответствует порядку, описанному в этой статье).
  • Если вам нужно связать таблицы, используйте объединение, см. 2, 3

2. join

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

3. on

Фильтр ON выполняется для виртуальной таблицы T1, и только те строки, которые совпадают, будут записаны в виртуальную таблицу T2. (Обратите внимание, что если с ней связана третья таблица, декартово произведение T2 и третьей таблицы будет использоваться для создания таблицы T3, а шаг 3. on будет повторяться для создания таблицы T4, но следующая последовательность пока не будет объясняться. Для T3 и T4 здесь просто свяжите запрос T2 из таблицы, чтобы продолжить)

4. where

Выполните фильтрацию условия WHERE для виртуальной таблицы T2. Только совпадающие записи будут вставлены в виртуальную таблицу T3.

5.group by

Предложение group by объединяет уникальные значения в группу, в результате чего получается виртуальная таблица T4. Если применяется группировка по, то все последующие шаги могут работать только со столбцами T4 или выполнять 6. Агрегирующие функции (счетчик, сумма, среднее и т. д.). (Примечание. Причина в том, что окончательный результирующий набор после группировки содержит только одну строку в каждой группе. Помните, иначе здесь будет много проблем, и будут специально упомянуты следующие ошибки кода.)

6. avg, sum.... и т.д. агрегатные функции

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

7. having

Примените фильтр для генерации T5. Предложение HAVING в основном используется в сочетании с предложением GROUP BY, а фильтр наличия — это первый и единственный фильтр, применяемый к сгруппированным данным.

8. select

Выполните операцию выбора, выберите указанный столбец и вставьте его в виртуальную таблицу T6.

9. distinct

Записи в T6 дедуплицированы. Удалите ту же строку, в результате чего получится виртуальная таблица T7.(Примечание: на самом деле, Different является избыточным, если применяется предложение group by, потому что та же причина заключается в том, что при группировании уникальные значения в столбце группируются вместе, и только для Каждой группы возвращается строка записей, тогда все записи будут разными.)

10. order by

Применить порядок по пункту. Отсортируйте T7 в соответствии с порядком_по_условию и верните курсор вместо виртуальной таблицы. sql основан на теории множеств, набор не упорядочивает свои строки, это просто логический набор элементов, порядок элементов не имеет значения. Запрос, сортирующий таблицу, может вернуть объект, содержащий логическую организацию в определенном физическом порядке. Этот объект называется курсором.
Несколько замечаний по заказу

  • Поскольку возвращаемое значение order by является курсором, запросы, использующие предложение order by, нельзя применять к табличным выражениям.
  • порядок сортировкой очень затратен, если вы не должны сортировать, лучше не указывать порядок,
  • Два параметра порядка по asc (по возрастанию) desc (по убыванию)

11. limit

Извлеките записи указанной строки, сгенерируйте виртуальную таблицу T9 и верните результат.

Параметр после предела может быть пределом m или пределом m n, что означает от m-го до n-го данных.

(Примечание. Многие разработчики любят использовать этот оператор для решения проблем с разбиением на страницы. Для небольших данных нет проблем с использованием предложения LIMIT. Когда объем данных очень большой, использование LIMIT n, m очень неэффективно. Из-за механизма ПРЕДЕЛ Это сканирование с начала каждый раз.Если вам нужно начать с 600 000-й строки и прочитать 3 фрагмента данных, вам нужно сначала отсканировать и найти 600 000 строк, а затем прочитать их, и процесс сканирования очень неэффективный процесс.Поэтому для обработки больших данных очень необходимо установить некий механизм кэширования на прикладном уровне)

Разработайте кусок sql, написанный для определенного требования

SELECT `userspk`.`avatar` AS `user_avatar`, 
`a`.`user_id`, 
`a`.`answer_record`, 
 MAX(`score`) AS `score`
FROM (select * from pkrecord  order by score desc) as a 
INNER JOIN `userspk` AS `userspk` 
ON `a`.`user_id` = `userspk`.`user_id`
WHERE `a`.`status` = 1 
AND `a`.`user_id` != 'm_6da5d9e0-4629-11e9-b5f7-694ced396953' 
GROUP BY `user_id`
ORDER BY `a`.`score` DESC 
LIMIT 9;

результат поиска:

  • Позвольте мне кратко сказать, что я ищу:

Я хочу запросить 9 пользовательских записей с наивысшими баллами и их аватары в таблице записей pk.

  • Через этот sql я действительно думаю о порядке выполнения каждого оператора sql.

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

Некоторые пояснения к этому sql:

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

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

//不使用子查询
SELECT `userspk`.`avatar` AS `user_avatar`, 
`pkrecord`.`user_id`, 
`pkrecord`.`answer_record`, 
`pkrecord`.`id`, 
 MAX(`score`) AS `score`
FROM pkrecord
INNER JOIN `userspk` AS `userspk` 
ON `pkrecord`.`user_id` = `userspk`.`user_id`
WHERE `pkrecord`.`status` = 1 
AND `pkrecord`.`user_id` != 'm_6da5d9e0-4629-11e9-b5f7-694ced396953' 
GROUP BY `user_id`
ORDER BY `pkrecord`.`score` DESC 
LIMIT 9;

результат поиска

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

псевдоним в операторе sql

Когда используются псевдонимы

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

  • псевдоним обозначения имени таблицы

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

SELECT article.title,article.content,user.username FROM article, user

WHERE article.aid=1 AND article.uid=user.uid

После установки псевдонима:

SELECT a.title,a.content,u.username FROM article AS a, user AS u where a.aid=1 and a.uid=u.uid

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

  • В поле запроса указан псевдоним

Запросите таблицу и напрямую установите псевдоним для поля запроса.

SELECT username AS name,email FROM user

запросить две таблицы

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

SELECT a.title AS atitle,u.username,u.title AS utitle FROM article AS a, user AS u where a.uid=u.uid

  • При ассоциированном запросе, когда ассоциирована сама таблица, некоторые таблицы классификации должны использовать псевдонимы.

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

  • Псевдонимы можно использовать при заказе по заказу

    Просмотрите приведенный выше абзац sql

  • delete , update MySQL может использовать псевдонимы, псевдонимы особенно полезны при многотабличном (каскадном) удалении

delete t1,t2 from t_a t1 , t_b t2 where t1.id = t2.id

  • Для результатов подзапроса требуется псевдоним

    Просмотрите приведенный выше абзац sql

Меры предосторожности при использовании псевдонимов

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

Примечания по написанию операторов sql

Примечания к написанию

  • Строковый тип должен быть заключен в одинарные кавычки
  • Каждое поле после select должно быть разделено запятой, но поле, связанное с from в конце, не должно быть разделено запятой
  • Используйте псевдоним при создании временной таблицы с подзапросом, иначе будет сообщено об ошибке.

Внимание к повышенной производительности

  • Не используйте "выбрать * из..." для возврата всех столбцов, а извлекайте только требуемые столбцы, что позволяет избежать ненужных модификаций программы, вызванных последующими изменениями структуры таблицы, а также может уменьшить дополнительное потребление ресурсов.
  • Не извлекать известные столбцы
select  user_id,name from User where user_id = ‘10000050’
  • Используйте параметризуемые условия поиска, такие как =, >, >=, , !=, !>, !
  • Когда вам нужно проверить, есть ли записи, соответствующие условиям, используйте exists вместо count(*), первая возвращает первую совпадающую запись, а второй должен пройти все совпадающие записи.
  • Порядок столбцов в предложении Where соответствует порядку используемого индекса. Не все оптимизаторы баз данных могут оптимизировать этот порядок и поддерживать хорошие привычки программирования (связанные с индексами).
  • Не работайте и не работайте с полями в предложениях where (связанных с индексом)
  1. Например, если сумма / 2 > 100, даже если у поля суммы есть индекс, его нельзя использовать. Если изменить его на сумму > 100 * 2, можно использовать индекс в столбце суммы.
  2. Например, если подстрока (Фамилия, 1, 1) = 'F' не может использовать индекс в столбце Фамилия, и где Фамилия, такая как 'F%', или где Фамилия >= 'F' и Фамилия

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

  • Будьте осторожны при использовании операции или, любое предложение в операции и может использовать индекс для повышения производительности запроса, но ни одно из условий или не может использовать индекс, что приведет к снижению производительности запроса, например, где member_no = 1 или provider_no = 1, в member_no или Если в каком-либо поле provider_no нет индекса, это приведет к сканированию таблицы или кластеризованному индексному сканированию (связанному с индексом).

  • Между, как правило, намного эффективнее, чем in/или.Если вы можете выбирать между условиями между и в/или, всегда выбирайте между и заменяйте предложение между комбинацией условий >= и = и

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

Мощные статьи:

Чего вы не знаете об инфраструктуре Mysql!

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

Думаете, эта статья была вам полезна? пожалуйста, поделитесь с большим количеством людей

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

Категории