Еще 10 распространенных ошибок, которые делают Java-разработчики при написании SQL (вы не поверите последнему)

(Извините за заголовок этой наживки. Не удержался!)

Мы на миссии. Чтобы научить вас SQL. Но в основном мы хотим научить вас ценить SQL. Тебе это понравится!

Правильный или неправильный SQL-запрос не должен относиться к тому подходу «Ты делаешь это неправильно», который часто встречается, когда евангелисты продвигают свой объект евангелизации. Правильный выбор SQL-кода должен доставлять вам удовольствие, если вы все сделаете правильно. Вещи, которые вы начинаете ценить, когда замечаете, что вы можете легко заменить 2000 строк медленного, сложного в обслуживании и безобразного императивного (или объектно-ориентированного) кода на 300 строк обедненного функционального кода (например, с помощью Java 8 ), или даже лучше с 50 строками SQL.

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

Время владыки Михаила освободиться от мышления SQL-92
Откровения Петри Кайнулайнена, которые побудили его начать серию уроков по jOOQ (среди прочих причин)
Евгений Параскив (из Baeldung) разбирается с Es-Queue-El

Итак, после наших предыдущих, очень популярных постов:

… мы принесем вам:

Еще одна распространенная ошибка Java-разработчика при написании SQL

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

Не используя оконные функции

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

Если для вас важен бесплатный и / или открытый исходный код, у вас нет абсолютно лучшего выбора, чем использование PostgreSQL (и вы даже можете использовать бесплатное jOOQ Open Source Edition , если вы являетесь разработчиком Java).

И если вам повезет работать в среде с лицензиями Oracle или SQL Server (или DB2, Sybase), вы получите еще больше от своего нового любимого инструмента.

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

Лечение:

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

Не объявлять ограничения NOT NULL

Этот уже был частью предыдущего списка, в котором мы заявляли, что вы должны добавить как можно больше метаданных в свою схему, потому что ваша база данных сможет использовать эти метаданные для оптимизации. Например, если вашей базе данных известно, что значение внешнего ключа в BOOK.AUTHOR_ID также должно содержаться ровно один раз в AUTHOR.ID , тогда в сложных запросах может быть достигнут целый набор оптимизаций.

Теперь давайте еще раз посмотрим на ограничения NOT NULL . Если вы используете Oracle, значения NULL не будут частью вашего индекса. Это не имеет значения, если вы выражаете ограничение IN , например:

SELECT * FROM table
WHERE value IN (
  SELECT nullable_column FROM ...
)

Но что происходит с ограничением NOT IN ?

SELECT * FROM table
WHERE value NOT IN (
  SELECT nullable_column FROM ...
)

Из-за немного неинтуитивного способа обработки NULL в SQL существует небольшой риск того, что второй запрос вообще не даст никаких результатов, а именно, если в подзапросе будет хотя бы одно значение NULL . Это верно для всех баз данных, которые правильно понимают SQL.

Но поскольку индекс в nullable_column не содержит значений NULL , Oracle должен найти весь контент в таблице, что приведет к полному FULL TABLE SCAN таблицы. Теперь это неожиданно! Подробности об этом можно увидеть в этой статье .

Лечение:

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

Инструмент:

Если вы используете Oracle, используйте этот запрос, чтобы обнаружить все обнуляемые, но индексированные столбцы:

SELECT
  i.table_name,
  i.index_name,
  LISTAGG(
    LPAD(i.column_position,  2) || ': ' || 
    RPAD(i.column_name    , 30) || ' '  ||
    DECODE(t.nullable, 'Y', '(NULL)', '(NOT NULL)'), 
    ', '
  ) WITHIN GROUP (ORDER BY i.column_position) 
    AS "NULLABLE columns in indexes"
FROM user_ind_columns i
JOIN user_tab_cols t
ON (t.table_name, t.column_name) = 
  ((i.table_name, i.column_name))
WHERE EXISTS (
  SELECT 1
  FROM user_tab_cols t
  WHERE (t.table_name, t.column_name, t.nullable) = 
       ((i.table_name, i.column_name, 'Y'       ))
)
GROUP BY i.table_name, i.index_name
ORDER BY i.index_name ASC;

Пример вывода:

TABLE_NAME | INDEX_NAME   | NULLABLE columns in indexes
-----------+--------------+----------------------------
PERSON     | I_PERSON_DOB | 1: DATE_OF_BIRTH (NULL)

А потом исправь это !

(Случайная критика Maven здесь не имеет значения!)

Если вам интересно узнать больше подробностей, посмотрите также эти посты:

Использование PL / SQL State State

Теперь это скучно, если вы не используете Oracle, но если вы (и вы Java-разработчик), очень осторожно относитесь к состоянию пакета PL / SQL. Вы действительно делаете то, что думаете?

Да, PL / SQL имеет состояние пакета , например

CREATE OR REPLACE PACKAGE pkg IS
  -- Package state here!
  n NUMBER := 1;
 
  FUNCTION next_n RETURN NUMBER;
END pkg;
 
CREATE OR REPLACE PACKAGE BODY pkg IS
  FUNCTION next_n RETURN NUMBER
  IS
  BEGIN
    n := n + 1;
    RETURN n;
  END next_n;
END pkg;

Прекрасно, вы создали счетчик в памяти, который генерирует новый номер каждый раз, когда вы вызываете pkg.next_n . Но кому принадлежит этот счетчик? Да, сессия. Каждый сеанс имеет свой собственный инициализированный «экземпляр пакета».

Но нет, вероятно, это не та сессия, о которой вы могли подумать.

Мы, Java-разработчики, подключаемся к базам данных через пулы соединений. Когда мы получаем соединение JDBC из такого пула, мы перезапускаем это соединение из предыдущего «сеанса», например, предыдущего HTTP-запроса (не HTTP-сеанса!). Но это не то же самое. Сеанс базы данных (вероятно) переживает HTTP-запрос и будет унаследован от следующего запроса, возможно, от совершенно другого пользователя. Теперь представьте, что у вас был номер кредитной карты в этом пакете …?

Не лекарство:

Нет. Не просто переходите к использованию пакетов SERIALLY_REUSABLE

CREATE OR REPLACE PACKAGE pkg IS
  PRAGMA SERIALLY_REUSABLE;
  n NUMBER := 1;
 
  FUNCTION next_n RETURN NUMBER;
END pkg;

Потому как:

Теперь вы даже не можете использовать этот пакет из SQL (см. ORA-06534 ).
Смешивание этой PRAGMA с обычным состоянием пакета из других пакетов только усложняет ситуацию.

Так что не надо.

Не лекарство:

Я знаю. PL / SQL может быть зверем. Это часто кажется таким странным языком. Но смирись с этим. Многие вещи работают намного быстрее, когда написаны на PL / SQL, так что пока не сдавайтесь. Удаление PL / SQL также не является решением.

Лечение:

Любой ценой старайтесь избегать состояния пакета в PL / SQL. Думайте о состоянии пакета как о static переменных в Java. Хотя они могут быть полезны для кэшей (и, конечно, констант) время от времени, на самом деле вы можете не получить доступ к тому состоянию, которое хотели. Подумайте о балансировщиках нагрузки, внезапно перенося вас в другую JVM Подумайте о загрузчиках классов, которые по какой-то причине могли бы загрузить один и тот же класс дважды.

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

Или, очевидно, при сохранении состояния к какой-то таблице.

Выполнение одного и того же запроса все время

Основные данные скучно. Вы, вероятно, написали какую-то утилиту для получения последней версии ваших основных данных (например, языка, локали, переводов, арендатора, настроек системы), и вы можете запрашивать их каждый раз, когда они становятся доступны.

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

Содержание таблицы / столбца
Содержание индекса
Запрос / материализованные результаты просмотра
Результаты процедуры (если они детерминированные)
курсоры
Планы выполнения

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

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

Лечение:

Пожалуйста, потратьте 10 минут, скачайте Guava и используйте его отличный и простой в настройке кеш , который поставляется с различными встроенными стратегиями аннулирования. Выберите аннулирование на основе времени (т. Е. Опрос), выберите Oracle AQ или Streams или NOTIFY PostgreSQL для аннулирования на основе событий или просто сделайте свой кэш постоянным, если это не имеет значения. Но не выполняйте одинаковый запрос основных данных все время.

… Это, очевидно, подводит нас к:

Не зная о проблеме N + 1

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

ORM (например, Hibernate , EclipseLink )
SQL (например, через JDBC , MyBatis или jOOQ )
Обе

Итак, очевидно, что вы выбрали ORM, потому что в противном случае вы не будете страдать от «N + 1» . Что означает «N + 1»?

Принятый ответ на этот вопрос переполнения стека это хорошо объясняет . По сути, вы бежите:

SELECT * FROM book
 
-- And then, for each book:
SELECT * FROM author WHERE id = ?
SELECT * FROM author WHERE id = ?
SELECT * FROM author WHERE id = ?

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

SELECT * 
FROM   book
JOIN   author 
  ON   book.author_id = author.id

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

Возможно, вы могли бы перейти на JPA 2.1 и использовать новый @NamedEntityGraph для выражения красивых деревьев аннотаций, подобных этому:

@NamedEntityGraph(
    name = "post",
    attributeNodes = {
        @NamedAttributeNode("title"),
        @NamedAttributeNode(
            value = "comments", 
            subgraph = "comments"
        )
    },
    subgraphs = {
        @NamedSubgraph(
            name = "comments",
            attributeNodes = {
                @NamedAttributeNode("content")
            }
        )
    }
)

Пример был взят из этого поста в блоге Ханци Бая . Затем Хантис продолжает объяснять, что вы можете использовать вышеописанную красоту посредством следующего утверждения:

em.createQuery("select p from Post p where p.id=:id",
               Post.class)
  .setHint("javax.persistence.fetchgraph", 
           postGraph)
  .setParameter("id", this.id)
  .getResultList()
  .get(0);

Давайте все со всем уважением примем вышеупомянутое применение стандартов JEE, а затем рассмотрим…

Лечение:

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

Поскольку вы уже используете ORM, это может означать просто обращение к собственному SQL — или, возможно, вам удастся выразить свой запрос с помощью JPQL. Конечно, мы согласны с Алессио Харри в том, что вы должны использовать jOOQ вместе с JPA:

Еда на вынос:

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

Альтернатива — простой и понятный SQL. А с Java 8 мы добавим функциональное преобразование через Streams API. Это трудно победить .

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

Не используется общие табличные выражения

Хотя обычные табличные выражения, очевидно, предлагают улучшения читабельности, они также могут предлагать улучшения производительности. Рассмотрим следующий запрос, с которым я недавно столкнулся в клиентском пакете PL / SQL (не фактический запрос):

SELECT round (
  (SELECT amount FROM payments WHERE id = :p_id)
    * 
  (
    SELECT e.bid
    FROM   currencies c, exchange_rates e
    WHERE  c.id     = 
      (SELECT cur_id FROM payments WHERE id = :p_id)
    AND    e.cur_id = 
      (SELECT cur_id FROM payments WHERE id = :p_id)
    AND    e.org_id = 
      (SELECT org_id FROM payments WHERE id = :p_id)
  ) / (
    SELECT c.factor
    FROM   currencies c, exchange_rates e
    WHERE  c.id     = 
      (SELECT cur_id FROM payments WHERE id = :p_id)
    AND    e.cur_id = 
      (SELECT cur_id FROM payments WHERE id = :p_id)
    AND    e.org_id = 
      (SELECT org_id FROM payments WHERE id = :p_id)
  ), 0
) 
INTO amount 
FROM dual;

Так, что это делает? Это по существу конвертирует сумму платежа из одной валюты в другую. Давайте не будем слишком углубляться в бизнес-логику, давайте сразу перейдем к технической проблеме. Приведенный выше запрос приводит к следующему плану выполнения (в Oracle):

------------------------------------------------------
| Operation                         | Name           |
------------------------------------------------------
| SELECT STATEMENT                  |                |
|  TABLE ACCESS BY INDEX ROWID      | PAYMENTS       |
|   INDEX UNIQUE SCAN               | PAYM_PK        |
|   NESTED LOOPS                    |                |
|    INDEX UNIQUE SCAN              | CURR_PK        |
|     TABLE ACCESS BY INDEX ROWID   | PAYMENTS       |
|      INDEX UNIQUE SCAN            | PAYM_PK        |
|    TABLE ACCESS BY INDEX ROWID    | EXCHANGE_RATES |
|     INDEX UNIQUE SCAN             | EXCH_PK        |
|      TABLE ACCESS BY INDEX ROWID  | PAYMENTS       |
|       INDEX UNIQUE SCAN           | PAYM_PK        |
|      TABLE ACCESS BY INDEX ROWID  | PAYMENTS       |
|       INDEX UNIQUE SCAN           | PAYM_PK        |
|     NESTED LOOPS                  |                |
|      TABLE ACCESS BY INDEX ROWID  | CURRENCIES     |
|       INDEX UNIQUE SCAN           | CURR_PK        |
|        TABLE ACCESS BY INDEX ROWID| PAYMENTS       |
|         INDEX UNIQUE SCAN         | PAYM_PK        |
|      INDEX UNIQUE SCAN            | EXCH_PK        |
|       TABLE ACCESS BY INDEX ROWID | PAYMENTS       |
|        INDEX UNIQUE SCAN          | PAYM_PK        |
|       TABLE ACCESS BY INDEX ROWID | PAYMENTS       |
|        INDEX UNIQUE SCAN          | PAYM_PK        |
|  FAST DUAL                        |                |
------------------------------------------------------

Фактическое время выполнения в этом случае незначительно, но, как вы можете видеть, одни и те же объекты снова и снова доступны в запросе. Это нарушение распространенной ошибки № 4: выполнение одного и того же запроса все время .

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

-- We're always accessing a single payment:
  FROM payments WHERE id = :p_id
 
-- Joining currencies and exchange_rates twice:
  FROM currencies c, exchange_rates e

Итак, давайте сначала рассмотрим оплату:

-- "payment" contains only a single payment
-- But it contains all the columns that we'll need
-- afterwards
WITH payment AS (
    SELECT cur_id, org_id, amount
    FROM   payments
    WHERE  id = :p_id
)
SELECT round(p.amount * e.bid / c.factor, 0)
 
-- Then, we simply don't need to repeat the
-- currencies / exchange_rates joins twice
FROM   payment p
JOIN   currencies c     ON  p.cur_id = c.id
JOIN   exchange_rates e ON  e.cur_id = p.cur_id
                        AND e.org_id = p.org_id

Обратите внимание, что мы также заменили списки таблиц на ANSI JOIN, как предложено в нашем предыдущем списке

Вы не поверите, что это тот же запрос, не так ли? А как насчет плана выполнения? Вот!

---------------------------------------------------
| Operation                      | Name           |
---------------------------------------------------
| SELECT STATEMENT               |                |
|  NESTED LOOPS                  |                |
|   NESTED LOOPS                 |                |
|    NESTED LOOPS                |                |
|     FAST DUAL                  |                |
|     TABLE ACCESS BY INDEX ROWID| PAYMENTS       |
|      INDEX UNIQUE SCAN         | PAYM_PK        |
|    TABLE ACCESS BY INDEX ROWID | EXCHANGE_RATES |
|     INDEX UNIQUE SCAN          | EXCH_PK        |
|   TABLE ACCESS BY INDEX ROWID  | CURRENCIES     |
|    INDEX UNIQUE SCAN           | CURR_PK        |
---------------------------------------------------

Нет сомнений, что это намного лучше.

Лечение:

Если вам повезло, и вы используете одну из тех баз данных, которая поддерживает оконные функции, шансы невероятно высоки (100%), что у вас также есть поддержка общих табличных выражений. Это еще одна причина для вас перейти с MySQL на PostgreSQL или оценить тот факт, что вы можете работать с потрясающей коммерческой базой данных.

Общие табличные выражения похожи на локальные переменные в SQL. В каждом крупном утверждении вы должны рассмотреть возможность их использования, как только почувствуете, что написали что-то раньше.

Еда на вынос:

Некоторые базы данных (например, PostgreSQL или SQL Server ) также поддерживают общие табличные выражения для операторов DML. Другими словами, вы можете написать:

WITH ...
UPDATE ...

Это делает DML невероятно более мощным.

Не использовать выражения значения строки для ОБНОВЛЕНИЙ

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

Но мало кто знает, что они также могут использоваться в операторе UPDATE в большинстве баз данных. Посмотрите на следующий запрос, который я снова нашел в клиентском PL / SQL-пакете (опять же, конечно, еще проще):

UPDATE u
SET n = (SELECT n + 1    FROM t WHERE u.n = t.n),
    s = (SELECT 'x' || s FROM t WHERE u.n = t.n),
    x = 3;

Таким образом, этот запрос принимает подзапрос в качестве источника данных для обновления двух столбцов, а третий столбец обновляется «регулярно». Как это работает? Умеренно:

-----------------------------
| Operation          | Name |
-----------------------------
| UPDATE STATEMENT   |      |
|  UPDATE            | U    |
|   TABLE ACCESS FULL| U    |
|   TABLE ACCESS FULL| T    |
|   TABLE ACCESS FULL| T    |
-----------------------------

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

На помощь: выражения значений строк. Давайте просто перефразируем наше UPDATE к этому:

UPDATE u
SET (n, s) = ((
      SELECT n + 1, 'x' || s FROM t WHERE u.n = t.n
    )),
    x = 3;

Давайте проигнорируем забавный, специфичный для Oracle синтаксис двойных скобок для правой части такого назначения выражения значения строки, но давайте оценим тот факт, что мы можем легко назначить новое значение для кортежа (n, s) за один раз ! Обратите внимание, что мы могли бы также написать это и назначить x :

UPDATE u
SET (n, s, x) = ((
      SELECT n + 1, 'x' || s, 3 
      FROM t WHERE u.n = t.n
    ));

Как и следовало ожидать, план выполнения также улучшился, и T доступен только один раз:

-----------------------------
| Operation          | Name |
-----------------------------
| UPDATE STATEMENT   |      |
|  UPDATE            | U    |
|   TABLE ACCESS FULL| U    |
|   TABLE ACCESS FULL| T    |
-----------------------------

Лечение:

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

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

Использование MySQL, когда вы можете использовать PostgreSQL

Для некоторых это может показаться чем-то вроде хипстерской дискуссии. Но давайте рассмотрим факты:

MySQL претендует на звание «самой популярной базы данных с открытым исходным кодом».
PostgreSQL претендует на звание «самой совершенной базы данных с открытым исходным кодом».

Давайте рассмотрим немного истории. MySQL всегда был очень прост в установке, обслуживании, и у него было отличное и активное сообщество. Это привело к тому, что MySQL по-прежнему остается предпочтительной СУБД практически для каждого веб-хостера на этой планете. Эти хостеры также поддерживают PHP, который был одинаково прост в установке и обслуживании.

НО!

Мы, Java-разработчики, склонны иметь мнение о PHP, верно? Это суммировано этим изображением здесь:

PHP Молот

Ну, это работает, но как это работает?

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

Многие люди выбирают MySQL в первую очередь из-за его цены (0,00 долларов США). Но часто одни и те же люди находят MySQL медленным и быстро приходят к выводу, что SQL сам по себе медленный — без оценки параметров. По этой же причине все хранилища NoSQL сравнивают себя с MySQL, а не с Oracle, базой данных, которая почти всегда выигрывает тесты производительности Совета по обработке транзакций (TPC) . Несколько примеров:

Хотя последняя статья прямо добавляет «(и другие СУБД)», в ней нет подробностей, что делают эти «другие СУБД» неправильно. Это действительно только сравнивает MongoDB с MySQL.

Лечение:

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

(шучу насчет последнего, конечно)

Еда на вынос:

Не поддавайтесь агрессивному маркетингу NoSQL. 10gen — чрезвычайно хорошо финансируемая компания, даже если MongoDB продолжает разочаровывать технически.

То же самое верно и для Datastax .

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

Тем временем PostgreSQL стал еще лучше, и вы, как читатель этого блога / поста, собираетесь сделать ставку на команду-победителя!

… просто чтобы процитировать Марка Мэдсена еще раз:

Отказ от ответственности:

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

Забыть о журналах UNDO / REDO

Мы утверждали, что заявления MERGE или массовые / пакетные обновления хороши. Это правильно, но, тем не менее, вы должны быть осторожны при обновлении огромных наборов данных в транзакционных контекстах. Если ваша транзакция «занимает слишком много времени» , то есть если вы обновляете 10 миллионов записей одновременно, вы столкнетесь с двумя проблемами:

Вы увеличиваете риск состояния гонки, если другой процесс также записывает данные в ту же таблицу. Это может вызвать откат их или вашей транзакции, возможно, заставит вас снова развернуть огромное обновление
Вы вызываете много параллелизма в вашей системе, потому что каждая транзакция / сеанс, которые хотят видеть данные, которые вы собираетесь обновить, должны будут сначала временно откатить все ваши обновления, прежде чем они достигнут состояния на диске. это было там до вашего огромного обновления. Это цена КИСЛОТЫ.

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

Другой способ обойти эту проблему — часто фиксировать свою собственную работу, например, после 1000 вставок / обновлений.

В любом случае из-за теоремы CAP вам придется пойти на компромисс. Частые коммиты создадут риск несовместимости базы данных в случае сбоя многомиллионного обновления после 5 миллионов (подтвержденных) записей. Откат тогда означал бы возврат всех изменений базы данных в сторону резервной копии.

Лечение:

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

Еда на вынос:

К этому времени поклонники NoSQL будут утверждать (опять же из-за чрезмерного маркетинга вышеупомянутыми компаниями), что NoSQL решил эту проблему, отбросив схемы и безопасность типов. «Не обновляйте, просто добавьте другое свойство!» — Они сказали.

Но это не правда !

Во-первых, я могу добавлять столбцы в свою базу данных без каких-либо проблем. Оператор ALTER TABLE ADD выполняется мгновенно в действующих базах данных. Заполнение столбца данными также никого не беспокоит, потому что никто еще не читает столбец (помните, не SELECT * !). Таким образом, добавление столбцов в RDBMS столь же дешево, как и добавление свойств JSON в документ MongoDB.

Но как насчет изменения столбцов? Удалить их? Слияние их?

Это просто неправда, что денормализация уводит вас куда-то далеко. Денормализация — это всегда краткосрочная победа для разработчика. Вряд ли это долгосрочная победа оперативных команд. Наличие избыточных данных в вашей базе данных ради ускорения оператора ALTER TABLE — это все равно, что подметать грязь под ковром.

Не верьте маркетологам. И пока вы занимаетесь этим, подумайте дважды и забудьте, что мы сами являемся поставщиками инструментов SQL ! Вот снова «правильное» сообщение:

Не правильно использовать тип BOOLEAN

Это не совсем ошибка как таковая. Это просто то, что вряд ли кто-нибудь знает. Когда стандарт SQL: 1999 представил новый тип данных BOOLEAN , они действительно сделали это правильно. Потому что раньше у нас уже было что-то вроде логического значения в SQL. У нас есть <search condition> в SQL-92 , которые по сути являются предикатами для использования в WHERE , ON и HAVING , а также в выражениях CASE .

SQL: 1999, однако, просто определил новое <boolean value expression> как регулярное <value expression> и переопределил <search condition> следующим образом:

<search condition> ::=
    <boolean value expression>

Готово! Теперь, для большинства из нас, Java / Scala / и т.д. разработчиков, это не похоже на такое новшество. Черт возьми, это boolean . Очевидно, что он может быть взаимозаменяемо использован как предикат и как переменная.

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

Теперь, что это значит? Это означает, что вы можете использовать любой предикат также в качестве столбца! Например:

SELECT a, b, c
FROM (
  SELECT EXISTS (SELECT ...) a,
         MY_COL IN (1, 2, 3) b,
         3 BETWEEN 4 AND 5   c
  FROM   MY_TABLE
) t
WHERE a AND b AND NOT(c)

Согласен, это немного глупый запрос, но знаете ли вы, насколько это мощно?

К счастью, PostgreSQL снова полностью поддерживает это (в отличие от Oracle, у которого до сих пор нет типа данных BOOLEAN в SQL).

Лечение:

Время от времени использование BOOLEAN типов кажется очень правильным, так что сделайте это! Вы можете преобразовать выражения логических значений в предикаты, а предикаты — в выражения логических значений. Они одинаковые. Это делает SQL таким мощным.

Вывод

В последние годы SQL постоянно развивался благодаря таким прекрасным стандартам, как SQL: 1999 , SQL: 2003 , SQL: 2008 и теперь SQL: 2011 . Это единственный сохранившийся основной декларативный язык, теперь, когда XQuery можно считать довольно мертвым для основного потока. Это может быть легко смешано с процедурными языками, как показали PL / SQL и T-SQL (и другие процедурные диалекты). Он может быть легко смешан с объектно-ориентированными или функциональными языками, как показал jOOQ .

В Data Geekery мы считаем, что SQL — лучший способ запроса данных. Вы не согласны с любым из вышеперечисленного? Это нормально, вам не нужно. Иногда даже мы согласны с Уинстоном Черчиллем, который, как известно, сказал:

SQL — худшая форма запросов к базе данных, за исключением всех других форм.

Но, как недавно сказал Яков Файн:

Вы можете запустить из SQL, но вы не можете скрыть

Итак, давайте лучше вернемся к работе и изучим этого зверя! Спасибо за чтение.

Ссылка:

Еще 10 распространенных ошибок, которые делают Java-разработчики при написании SQL (вы не поверите последнему) от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и JOOQ .

Еще 10 распространенных ошибок, которые делают Java-разработчики при написании SQL (вы не поверите последнему)

Еще одна распространенная ошибка Java-разработчика при написании SQL

Не используя оконные функции

Лечение:

Не объявлять ограничения NOT NULL

Лечение:

Инструмент:

Использование PL / SQL State State

Не лекарство:

Не лекарство:

Лечение:

Выполнение одного и того же запроса все время

Лечение:

Не зная о проблеме N + 1

Лечение:

Еда на вынос:

Не используется общие табличные выражения

Лечение:

Еда на вынос:

Не использовать выражения значения строки для ОБНОВЛЕНИЙ

Лечение:

Использование MySQL, когда вы можете использовать PostgreSQL

Лечение:

Еда на вынос:

Отказ от ответственности:

Забыть о журналах UNDO / REDO

Лечение:

Еда на вынос:

Не правильно использовать тип BOOLEAN

Лечение:

Вывод

Категории

Последние статьи

Рефакторинг Hudson God Class

Альтернативы синтаксиса Java лямбда

Morphia и MongoDB: развивающиеся структуры документов

OpenShift Express: развертывание приложения Java EE (с поддержкой AS7)

Интеграция jqGrid, REST, AJAX и Spring MVC