сценарий проблемы
есть модельAccount, оператор запроса SQLAlchemy выглядит следующим образом:
query = Account.query.filter(Account.id.in_(account_ids)).order_by(Account.date_created.desc())
Здесь, если uids пуст, выполнение запроса будет иметь следующее предупреждение:
/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/default_comparator.py:35: SAWarning: The IN-predicate on "account.id" was invoked with an empty sequence. This results in a contradiction, which nonetheless can be expensive to evaluate. Consider alternative strategies for improved performance.
return o[0](self, self.expr, op, *(other + o[1:]), **kwargs)
Это означает, что использование пустого списка займет много времени и его необходимо оптимизировать для повышения производительности.
Почему есть эта подсказка? Почему пустой список влияет на производительность?
Первый запрос на печать, чтобы получить следующий оператор sql:
SELECT * // 字段使用 “*” 代替
FROM account
WHERE account.id != account.id ORDER BY account.date_created DESC
Вы обнаружите, что условие фильтра в сгенерированном оператореWHERE account.id != account.id,использоватьPostgreSQL Explain ANALYZE 命令,
- EXPLAIN: отображает план выполнения, сгенерированный планировщиком PostgreSQL для указанного оператора.
- АНАЛИЗ: сбор статистики о содержимом таблиц в базе данных.
Результаты анализа стоимости запроса следующие:
postgres=> EXPLAIN ANALYZE SELECT *
FROM account
WHERE account.id != account.id ORDER BY account.date_created DESC;
QUERY PLAN
----------------------------------------------------------------------------------
Sort (cost=797159.14..808338.40 rows=4471702 width=29) (actual time=574.002..574.002 rows=0 loops=1)
Sort Key: date_created DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on account (cost=0.00..89223.16 rows=4471702 width=29) (actual time=573.991..573.991 rows=0 loops=1)
Filter: (id <> id)
Rows Removed by Filter: 4494173
Planning time: 0.162 ms
Execution time: 574.052 ms
(8 rows)
Сначала посмотрите на план выполнения, созданный оператором, предоставленным Postgresql.Из результатов мы видим, что хотя возвращаемое значение пусто, стоимость запроса все еще очень высока.Почти все время плана выполнения тратится на сортировку, но по сравнению со временем выполнения время планирования запроса незначительно. (Результат состоит в том, чтобы сначала пройтись по всей таблице, узнать все данные, а затем использоватьFilter: (id <> id)Отфильтровать все данные. )
Следуя этой мысли, есть две схемы запросов:
- Если account_ids пуст, верните пустой список напрямую без каких-либо операций, и оператор запроса станет таким:
if account_ids:
query = Account.query.filter(Account.id.in_(account_ids)).order_by(Account.date_created.desc())
- Если account_ids пуст, то метод фильтрации, оператор запроса становится:
query = Account.query
if account_ids:
query = query.filter(Account.id.in_(account_ids))
else:
query = query.filter(False)
query = query.order_by(Account.date_created.desc())
Если account_ids пуст, результатом сгенерированного оператора SQL будет:
SELECT *
FROM account
WHERE 0 = 1 ORDER BY account.date_created DESC
Результат анализа таков:
postgres=> EXPLAIN ANALYZE SELECT *
FROM account
WHERE 0 = 1 ORDER BY account.date_created DESC;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Sort (cost=77987.74..77987.75 rows=1 width=29) (actual time=0.011..0.011 rows=0 loops=1)
Sort Key: date_created DESC
Sort Method: quicksort Memory: 25kB
-> Result (cost=0.00..77987.73 rows=1 width=29) (actual time=0.001..0.001 rows=0 loops=1)
One-Time Filter: false
-> Seq Scan on account (cost=0.00..77987.73 rows=1 width=29) (never executed)
Planning time: 0.197 ms
Execution time: 0.061 ms
(8 rows)
Как видите, и план запроса, и время выполнения значительно улучшились.
Тест
Если просто убрать сортировку по схеме 1, проверить результаты анализа
использоватьPostgreSQL Explain ANALYZE 命令Результаты анализа стоимости запроса следующие:
postgres=> EXPLAIN ANALYZE SELECT *
FROM account
WHERE account.id != account.id;
QUERY PLAN
----------------------------------------------------------------------------
Seq Scan on account (cost=0.00..89223.16 rows=4471702 width=29) (actual time=550.999..550.999 rows=0 loops=1)
Filter: (id <> id)
Rows Removed by Filter: 4494173
Planning time: 0.134 ms
Execution time: 551.041 ms
Видно, что разница между временем и сортировкой незначительна.
Как рассчитать стоимость запроса
Проводится анализ, и результаты следующие:
postgres=> explain select * from account where date_created ='2016-04-07 18:51:30.371495+08';
QUERY PLAN
--------------------------------------------------------------------------------------
Seq Scan on account (cost=0.00..127716.33 rows=1 width=211)
Filter: (date_created = '2016-04-07 18:51:30.371495+08'::timestamp with time zone)
(2 rows)
Данные, приведенные EXPLAIN:
- 0,00 Предполагаемые накладные расходы при запуске (время, прошедшее до начала сканирования вывода, например, время ожидания в очереди в сортировщике).
- 127716,33 Общая сметная стоимость.
- 1 Ожидаемое количество строк, выводимых этим узлом плана.
- Средняя ширина линии прогнозируемого узла 211 (блок: байт).
Здесь накладные расходы (стоимость) — это количество страниц в единицах доступа к диску, например 1,0, указывающее последовательность чтения страниц с диска. Накладной верхний узел, который будет включать стоимость всех его дочерних узлов. Количество Количество выходных строк (строк) здесь не является строкой планирования узла обработки/сканирования, как правило, меньше. Как правило, оценка количества строк верхнего уровня будет ближе к фактическому количеству строк, возвращаемых запросом. Это представлено здесь в случае, когда только одно ядро ЦП, оценка стоимости составляет 127 716,33;
Подсчитайте стоимость, Postgresql сначала смотрит на размер таблицы в байтах.
Здесь размер таблицы счетов:
postgres=> select pg_relation_size('account');
pg_relation_size
------------------
737673216
(1 row)
Просмотр размера блока
Postgresql добавляет точку стоимости для каждого блока, который будет прочитан один раз, используяshow block_sizeПроверьте размер блока:
postgres=> show block_size;
block_size
------------
8192
(1 row)
Подсчет количества блоков
Видно, что размер каждого блока составляет 8 кБ, то последовательное значение стоимости блока, прочитанное из таблицы, может быть рассчитана как:
blocks = pg_relation_size/block_size = 90048
90048количество блоков, занимаемых таблицей account.
Посмотрите, сколько стоит каждый блок
postgres=> show seq_page_cost;
seq_page_cost
---------------
1
(1 row)
Здесь это означает, что Postgresql присваивает каждому блоку единицу стоимости, что означает, что приведенный выше запрос должен начинаться с 90048 единиц стоимости.
Время, необходимое для обработки каждого процессора данных
- CPU_TUPLE_COST: стоимость процессора обработки каждой записи (кортеж: один ряд записей в отношении)
- cpu_operator_cost: Накладные расходы ЦП, вызванные оператором или функцией.
postgres=> show cpu_operator_cost;
cpu_operator_cost
-------------------
0.0025
(1 row)
postgres=> show cpu_tuple_cost;
cpu_tuple_cost
----------------
0.01
(1 row)
рассчитать
Формула расчета себестоимости такова:
стоимость = количество дисковых блоков * стоимость блока (1) + количество строк * cpu_tuple_cost (значение системного параметра) + количество строк * cpu_operator_cost
Теперь используйте все значения для вычисления значения, полученного в операторе объяснения:
number_of_records = 3013466 # account 表 count
block_size = 8192 # block size in bytes
pg_relation_size=737673216
blocks = pg_relation_size/block_size = 90048
seq_page_cost = 1
cpu_tuple_cost = 0.01
cpu_operator_cost = 0.0025
cost = blocks * seq_page_cost + number_of_records * cpu_tuple_cost + number_of_records * cpu_operator_cost
Как снизить стоимость запроса?
Чтобы ответить прямо, используйте индекс.
postgres=> explain select * from account where id=20039;
QUERY PLAN
----------------------------------------------------------------------------------------
Index Scan using account_pkey on account (cost=0.43..8.45 rows=1 width=211)
Index Cond: (id = 20039)
(2 rows)
Из этого запроса видно, что стоимость запроса значительно снижается при использовании запросов с индексированными полями.
Вычисления для сканирования индекса немного сложнее, чем для последовательного сканирования. Он состоит из двух этапов. PostgreSQL будет учитывать переменные random_page_cost и cpu_index_tuple_cost и возвращать значение на основе высоты дерева индексов.