1. Заявление LIMIT
Запросы с разбивкой на страницы являются одним из наиболее распространенных сценариев, но они также часто являются наиболее проблематичными. Например, для следующего простого оператора обычный администратор баз данных считает, что нужно добавить составной индекс к полям типа, имени и времени создания. Таким образом, условная сортировка может эффективно использовать индекс, и производительность быстро повышается.
SELECT *
FROM operation
WHERE type = 'SQLStats'
AND name = 'SlowLog'
ORDER BY create_time
LIMIT 1000, 10;
Ну, наверное, 90%+ администраторов баз данных решают эту проблему и все. Но когда предложение LIMIT становится «LIMIT 1000000,10», программист все еще жалуется: почему это все еще медленно, когда я извлекаю только 10 записей?
Чтобы знать, что БД не знает, где начинается 1000000-я запись, даже если есть индекс, его нужно вычислять с нуля. Когда возникает эта проблема с производительностью, в большинстве случаев программист ленив.
В таких сценариях, как внешний просмотр данных и перелистывание страниц или пакетный экспорт больших данных, максимальное значение предыдущей страницы можно использовать в качестве параметра в качестве условия запроса. Редизайн SQL выглядит следующим образом:
SELECT *
FROM operation
WHERE type = 'SQLStats'
AND name = 'SlowLog'
AND create_time > '2017-03-16 14:00:00'
ORDER BY create_time limit 10;
В новом дизайне время запроса в основном фиксировано и не изменится с ростом объема данных.
2. Неявное преобразование
Еще одна распространенная ошибка — несоответствие типов переменных запроса и определений полей в операторах SQL. Например, следующее утверждение:
mysql> explain extended SELECT *
> FROM my_balance b
> WHERE b.bpn = 14000000123
> AND b.isverified IS NULL ;
mysql> show warnings;
| Warning | 1739 | Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn'
Поле bpn определено как varchar(20), и стратегия MySQL заключается в преобразовании строк в числа перед их сравнением. Функция действует на поле таблицы, а индекс недействителен.
Вышеупомянутая ситуация может быть параметрами, автоматически заполненными структурой приложения, а не первоначальным намерением программиста. Сейчас существует множество сложных фреймворков приложений, которые просты в использовании и в то же время должны быть осторожны, чтобы не прорыть себе яму.
3. Обновление и удаление ассоциации
Хотя в MySQL 5.6 появилась функция материализации, следует отметить, что в настоящее время она оптимизирована только для операторов запросов. Для обновления или удаления его необходимо вручную переписать как JOIN.
Например, в следующем операторе UPDATE MySQL фактически выполняет циклический/вложенный подзапрос (DEPENDENT SUBQUERY), и можно представить себе время его выполнения.
UPDATE operation o
SET status = 'applying'
WHERE o.id IN (SELECT id
FROM (SELECT o.id,
o.status
FROM operation o
WHERE o.group = 123
AND o.status NOT IN ( 'done' )
ORDER BY o.parent,
o.id
LIMIT 1) t);
План реализации:
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| 1 | PRIMARY | o | index | | PRIMARY | 8 | | 24 | Using where; Using temporary |
| 2 | DEPENDENT SUBQUERY | | | | | | | | Impossible WHERE noticed after reading const tables |
| 3 | DERIVED | o | ref | idx_2,idx_5 | idx_5 | 8 | const | 1 | Using where; Using filesort |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
После перезаписи на JOIN режим выбора подзапроса был изменен с DEPENDENT SUBQUERY на DERIVED, а скорость выполнения значительно увеличилась, с 7 секунд до 2 миллисекунд.
UPDATE operation o
JOIN (SELECT o.id,
o.status
FROM operation o
WHERE o.group = 123
AND o.status NOT IN ( 'done' )
ORDER BY o.parent,
o.id
LIMIT 1) t
ON o.id = t.id
SET status = 'applying'
План выполнения упрощается до:
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
| 1 | PRIMARY | | | | | | | | Impossible WHERE noticed after reading const tables |
| 2 | DERIVED | o | ref | idx_2,idx_5 | idx_5 | 8 | const | 1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
4. Смешанная сортировка
MySQL не может использовать индексы для смешанных сортировок. Но в некоторых сценариях все же есть возможность использовать специальные методы для повышения производительности.
SELECT *
FROM my_order o
INNER JOIN my_appraise a ON a.orderid = o.id
ORDER BY a.is_reply ASC,
a.appraise_time DESC
LIMIT 0, 20
План выполнения выглядит как полное сканирование таблицы:
+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+
| 1 | SIMPLE | a | ALL | idx_orderid | NULL | NULL | NULL | 1967647 | Using filesort |
| 1 | SIMPLE | o | eq_ref | PRIMARY | PRIMARY | 122 | a.orderid | 1 | NULL |
+----+-------------+-------+--------+---------+---------+---------+-----------------+---------+-+
Так как is_reply имеет только два состояния, 0 и 1, после того, как мы перепишем его следующим образом, время выполнения уменьшится с 1,58 секунды до 2 миллисекунд.
SELECT *
FROM ((SELECT *
FROM my_order o
INNER JOIN my_appraise a
ON a.orderid = o.id
AND is_reply = 0
ORDER BY appraise_time DESC
LIMIT 0, 20)
UNION ALL
(SELECT *
FROM my_order o
INNER JOIN my_appraise a
ON a.orderid = o.id
AND is_reply = 1
ORDER BY appraise_time DESC
LIMIT 0, 20)) t
ORDER BY is_reply ASC,
appraisetime DESC
LIMIT 20;
5. СУЩЕСТВУЕТ заявление
Когда MySQL обрабатывает предложение EXISTS, он по-прежнему использует вложенные подзапросы. Например, следующий оператор SQL:
SELECT *
FROM my_neighbor n
LEFT JOIN my_neighbor_apply sra
ON n.id = sra.neighbor_id
AND sra.user_id = 'xxx'
WHERE n.topic_status < 4
AND EXISTS(SELECT 1
FROM message_info m
WHERE n.id = m.neighbor_id
AND m.inuser = 'xxx')
AND n.topic_type <> 5
План выполнения таков:
+----+--------------------+-------+------+-----+------------------------------------------+---------+-------+---------+ -----+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+
| 1 | PRIMARY | n | ALL | | NULL | NULL | NULL | 1086041 | Using where |
| 1 | PRIMARY | sra | ref | | idx_user_id | 123 | const | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | m | ref | | idx_message_info | 122 | const | 1 | Using index condition; Using where |
+----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+
Удаление существующих и изменение их для объединения позволяет избежать вложенных подзапросов и сократить время выполнения с 1,93 секунды до 1 миллисекунды.
SELECT *
FROM my_neighbor n
INNER JOIN message_info m
ON n.id = m.neighbor_id
AND m.inuser = 'xxx'
LEFT JOIN my_neighbor_apply sra
ON n.id = sra.neighbor_id
AND sra.user_id = 'xxx'
WHERE n.topic_status < 4
AND n.topic_type <> 5
Новый план выполнения:
+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+
| 1 | SIMPLE | m | ref | | idx_message_info | 122 | const | 1 | Using index condition |
| 1 | SIMPLE | n | eq_ref | | PRIMARY | 122 | ighbor_id | 1 | Using where |
| 1 | SIMPLE | sra | ref | | idx_user_id | 123 | const | 1 | Using where |
+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+
6. Условие нажать вниз
Условия внешнего запроса нельзя передать в сложные представления или подзапросы:
- Совокупные подзапросы;
- Подзапросы с LIMIT;
- UNION или UNION ALL подзапросы;
- подзапросы в полях вывода;
Как и в следующем операторе, из плана выполнения видно, что его условия применяются после совокупного подзапроса:
SELECT *
FROM (SELECT target,
Count(*)
FROM operation
GROUP BY target) t
WHERE target = 'rm-xxxx'
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 514 | const | 2 | Using where |
| 2 | DERIVED | operation | index | idx_4 | idx_4 | 519 | NULL | 20 | Using index |
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
Убедившись, что условия семантического запроса могут быть переданы напрямую вниз, перепишите следующим образом:
SELECT target,
Count(*)
FROM operation
WHERE target = 'rm-xxxx'
GROUP BY target
План выполнения становится:
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
| 1 | SIMPLE | operation | ref | idx_4 | idx_4 | 514 | const | 1 | Using where; Using index |
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
7. Заранее сузьте область применения
Начните с исходного оператора SQL:
SELECT *
FROM my_order o
LEFT JOIN my_userinfo u
ON o.uid = u.uid
LEFT JOIN my_productinfo p
ON o.pid = p.pid
WHERE ( o.display = 0 )
AND ( o.ostaus = 1 )
ORDER BY o.selltime DESC
LIMIT 0, 15
Первоначальный смысл оператора SQL: сначала выполните серию левых соединений, а затем отсортируйте и выберите первые 15 записей. Также из плана выполнения видно, что расчетное количество отсортированных записей на последнем шаге равно 900 000, а затраты времени — 12 секунд.
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 909119 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL |
| 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
Поскольку последнее условие WHERE и сортировка предназначены для самой левой основной таблицы, вы можете сначала отсортировать my_order, чтобы заранее уменьшить объем данных, а затем выполнить левое соединение. После перезаписи SQL следующим образом время выполнения сокращается примерно до 1 миллисекунды.
SELECT *
FROM (
SELECT *
FROM my_order o
WHERE ( o.display = 0 )
AND ( o.ostaus = 1 )
ORDER BY o.selltime DESC
LIMIT 0, 15
) o
LEFT JOIN my_userinfo u
ON o.uid = u.uid
LEFT JOIN my_productinfo p
ON o.pid = p.pid
ORDER BY o.selltime DESC
limit 0, 15
Еще раз проверьте план выполнения: примите участие в JOIN после материализации подзапроса (select_type=DERIVED). Хотя предполагаемое сканирование строк по-прежнему составляет 900 000, фактическое время выполнения становится очень небольшим после использования индекса и предложения LIMIT.
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 15 | Using temporary; Using filesort |
| 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL |
| 1 | PRIMARY | p | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |
| 2 | DERIVED | o | index | NULL | idx_1 | 5 | NULL | 909112 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
8. Нажмите на промежуточный набор результатов
Рассмотрим следующий пример, предварительно оптимизированный (основная таблица в левом объединении имеет приоритет по условию запроса):
SELECT a.*,
c.allocated
FROM (
SELECT resourceid
FROM my_distribute d
WHERE isdelete = 0
AND cusmanagercode = '1234567'
ORDER BY salecode limit 20) a
LEFT JOIN
(
SELECT resourcesid， sum(ifnull(allocation, 0) * 12345) allocated
FROM my_resources
GROUP BY resourcesid) c
ON a.resourceid = c.resourcesid
Есть ли другие проблемы с этим утверждением? Нетрудно заметить, что подзапрос c является агрегированным запросом на всю таблицу, что приведет к снижению производительности всего оператора при особенно большом количестве таблиц.
Фактически, для подзапроса c окончательный набор результатов левого соединения заботится только о тех данных, которые могут соответствовать идентификатору ресурса основной таблицы. Таким образом, мы можем переписать оператор следующим образом, время выполнения уменьшается с 2 секунд до 2 миллисекунд.
SELECT a.*,
c.allocated
FROM (
SELECT resourceid
FROM my_distribute d
WHERE isdelete = 0
AND cusmanagercode = '1234567'
ORDER BY salecode limit 20) a
LEFT JOIN
(
SELECT resourcesid， sum(ifnull(allocation, 0) * 12345) allocated
FROM my_resources r,
(
SELECT resourceid
FROM my_distribute d
WHERE isdelete = 0
AND cusmanagercode = '1234567'
ORDER BY salecode limit 20) a
WHERE r.resourcesid = a.resourcesid
GROUP BY resourcesid) c
ON a.resourceid = c.resourcesid
Но подзапрос a появляется несколько раз в нашем операторе SQL. Такой способ написания не только имеет дополнительные накладные расходы, но и значительно усложняет весь оператор. Перепишите снова, используя оператор WITH:
WITH a AS
(
SELECT resourceid
FROM my_distribute d
WHERE isdelete = 0
AND cusmanagercode = '1234567'
ORDER BY salecode limit 20)
SELECT a.*,
c.allocated
FROM a
LEFT JOIN
(
SELECT resourcesid， sum(ifnull(allocation, 0) * 12345) allocated
FROM my_resources r,
a
WHERE r.resourcesid = a.resourcesid
GROUP BY resourcesid) c
ON a.resourceid = c.resourcesid
Суммировать
Компилятор базы данных генерирует план выполнения, который определяет, как на самом деле выполняется SQL. Но компиляторы просто делают все возможное, а компиляторы для всех баз данных не идеальны.
Большинство сценариев, упомянутых выше, также имеют проблемы с производительностью в других базах данных. Только поняв характеристики компилятора базы данных, вы сможете избежать его недостатков и писать высокопроизводительные операторы SQL.
Когда программисты проектируют модели данных и пишут операторы SQL, они должны привносить идею или понимание алгоритмов.
При написании сложных операторов SQL выработайте привычку использовать оператор WITH. Краткие и четкие операторы SQL также могут снизить нагрузку на базу данных.
Наконец
Прошу всех обратить внимание на мой официальный аккаунт [Программист в погоне за ветром], в нем будут обновляться статьи, а также размещаться отсортированная информация.