摘要:收集有關(guān)數(shù)據(jù)庫(kù)中表的內(nèi)容的統(tǒng)計(jì)信息。預(yù)計(jì)的該規(guī)劃節(jié)點(diǎn)的行平均寬度單位字節(jié)。其中上層節(jié)點(diǎn)的開銷將包括其所有子節(jié)點(diǎn)的開銷。一般而言,頂層的行預(yù)計(jì)數(shù)量會(huì)更接近于查詢實(shí)際返回的行數(shù)。
問題場(chǎng)景
有model Account,SQLAlchemy 查詢語句如下:
query = Account.query.filter(Account.id.in_(account_ids)).order_by(Account.date_created.desc())
這里 uids 如果為空,執(zhí)行查詢會(huì)有如下警告:
/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)
這里的意思是使用一個(gè)空的列表會(huì)花費(fèi)較長(zhǎng)的時(shí)間,需要優(yōu)化以提高性能。
為什么會(huì)有這個(gè)提示呢?一個(gè)空列表為什么會(huì)影響性能呢?
首先打印 query 可得到如下 sql 語句:
SELECT * // 字段使用 “*” 代替 FROM account WHERE account.id != account.id ORDER BY account.date_created DESC
會(huì)發(fā)現(xiàn)生成的語句中過濾條件是 WHERE account.id != account.id,使用 PostgreSQL Explain ANALYZE 命令,
EXPLAIN:顯示PostgreSQL計(jì)劃程序?yàn)樘峁┑恼Z句生成的執(zhí)行計(jì)劃。
ANALYZE:收集有關(guān)數(shù)據(jù)庫(kù)中表的內(nèi)容的統(tǒng)計(jì)信息。
分析查詢成本結(jié)果如下:
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提供的語句生成的執(zhí)行計(jì)劃,通過結(jié)果可以看到,雖然返回值為空,但是查詢成本卻還是特別高,執(zhí)行計(jì)劃部分幾乎所有的時(shí)間都耗費(fèi)在排序上,但是和執(zhí)行時(shí)間相比,查詢計(jì)劃的時(shí)間可以忽略不計(jì)。(結(jié)果是先遍歷全表,查出所有數(shù)據(jù),然后再使用 Filter: (id <> id) 把所有數(shù)據(jù)過濾。)
按照這個(gè)思路,有兩種查詢方案:
1.如果 account_ids 為空,那么直接返回空列表不進(jìn)行任何操作,查詢語句變?yōu)椋?/p>
if account_ids: query = Account.query.filter(Account.id.in_(account_ids)).order_by(Account.date_created.desc())
2.如果 account_ids 為空,那么過濾方式,查詢語句變?yōu)椋?/p>
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 為空,此時(shí)生成的 SQL 語句結(jié)果為:
SELECT * FROM account WHERE 0 = 1 ORDER BY account.date_created DESC
分析結(jié)果為:
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)
可以看到,查詢計(jì)劃和執(zhí)行時(shí)間都有大幅提高。
一個(gè)測(cè)試如果只是去掉方案1排序,查看一下分析結(jié)果
使用 PostgreSQL Explain ANALYZE 命令分析查詢成本結(jié)果如下:
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
可以看到,時(shí)間和有排序時(shí)差別不大。
如何計(jì)算查詢成本執(zhí)行一個(gè)分析,結(jié)果如下:
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引用的數(shù)據(jù)是:
0.00 預(yù)計(jì)的啟動(dòng)開銷(在輸出掃描開始之前消耗的時(shí)間,比如在一個(gè)排序節(jié)點(diǎn)里做排續(xù)的時(shí)間)。
127716.33 預(yù)計(jì)的總開銷。
1 預(yù)計(jì)的該規(guī)劃節(jié)點(diǎn)輸出的行數(shù)。
211 預(yù)計(jì)的該規(guī)劃節(jié)點(diǎn)的行平均寬度(單位:字節(jié))。
這里開銷(cost)的計(jì)算單位是磁盤頁面的存取數(shù)量,如1.0將表示一次順序的磁盤頁面讀取。其中上層節(jié)點(diǎn)的開銷將包括其所有子節(jié)點(diǎn)的開銷。這里的輸出行數(shù)(rows)并不是規(guī)劃節(jié)點(diǎn)處理/掃描的行數(shù),通常會(huì)更少一些。一般而言,頂層的行預(yù)計(jì)數(shù)量會(huì)更接近于查詢實(shí)際返回的行數(shù)。
這里表示的就是在只有單 CPU 內(nèi)核的情況下,評(píng)估成本是127716.33;
這里 account 表的大小為:
postgres=> select pg_relation_size("account"); pg_relation_size ------------------ 737673216 (1 row)查看塊的大小
Postgresql 會(huì)為每個(gè)要一次讀取的快添加成本點(diǎn),使用 show block_size查看塊的大小:
postgres=> show block_size; block_size ------------ 8192 (1 row)計(jì)算塊的個(gè)數(shù)
可以看到每個(gè)塊的大小為8kb,那么可以計(jì)算從表從讀取的順序塊成本值為:
blocks = pg_relation_size/block_size = 90048
90048 是account 表所占用塊的數(shù)量。
查看每個(gè)塊需要的成本postgres=> show seq_page_cost; seq_page_cost --------------- 1 (1 row)
這里的意思是 Postgresql 為每個(gè)塊分配一個(gè)成本點(diǎn),也就是說上面的查詢需要從90048個(gè)成本點(diǎn)。
處理每條數(shù)據(jù) cpu 所需時(shí)間cpu_tuple_cost:處理每條記錄的CPU開銷(tuple:關(guān)系中的一行記錄)
cpu_operator_cost:操作符或函數(shù)帶來的CPU開銷。
postgres=> show cpu_operator_cost; cpu_operator_cost ------------------- 0.0025 (1 row) postgres=> show cpu_tuple_cost; cpu_tuple_cost ---------------- 0.01 (1 row)計(jì)算
cost 計(jì)算公式為:
cost = 磁盤塊個(gè)數(shù) 塊成本(1) + 行數(shù) cpu_tuple_cost(系統(tǒng)參數(shù)值)+ 行數(shù) * cpu_operator_cost
現(xiàn)在用所有值來計(jì)算explain 語句中得到的值:
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)
通過這個(gè)查詢可以看到,在使用有索引的字段查詢時(shí),查詢成本顯著降低。
索引掃描的計(jì)算比順序掃描的計(jì)算要復(fù)雜一些。它由兩個(gè)階段組成。參考鏈接
PostgreSQL會(huì)考慮random_page_cost和cpu_index_tuple_cost 變量,并返回一個(gè)基于索引樹的高度的值。
sqlalchemy-and-empty-in-clause
PostgreSQL查詢性能分析和優(yōu)化
PostgreSQL學(xué)習(xí)手冊(cè)(性能提升技巧)
PostgreSQL 查詢成本模型
PostgreSQL 查詢計(jì)劃時(shí)間的計(jì)算詳解
文章版權(quán)歸作者所有,未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址:http://specialneedsforspecialkids.com/yun/42526.html
摘要:收集有關(guān)數(shù)據(jù)庫(kù)中表的內(nèi)容的統(tǒng)計(jì)信息。預(yù)計(jì)的該規(guī)劃節(jié)點(diǎn)的行平均寬度單位字節(jié)。其中上層節(jié)點(diǎn)的開銷將包括其所有子節(jié)點(diǎn)的開銷。一般而言,頂層的行預(yù)計(jì)數(shù)量會(huì)更接近于查詢實(shí)際返回的行數(shù)。 問題場(chǎng)景 有model Account,SQLAlchemy 查詢語句如下: query = Account.query.filter(Account.id.in_(account_ids)).order_by(...
摘要:收集有關(guān)數(shù)據(jù)庫(kù)中表的內(nèi)容的統(tǒng)計(jì)信息。預(yù)計(jì)的該規(guī)劃節(jié)點(diǎn)的行平均寬度單位字節(jié)。其中上層節(jié)點(diǎn)的開銷將包括其所有子節(jié)點(diǎn)的開銷。一般而言,頂層的行預(yù)計(jì)數(shù)量會(huì)更接近于查詢實(shí)際返回的行數(shù)。 問題場(chǎng)景 有model Account,SQLAlchemy 查詢語句如下: query = Account.query.filter(Account.id.in_(account_ids)).order_by(...
摘要:首先安裝包數(shù)據(jù)庫(kù)的默認(rèn)編碼為,修改數(shù)據(jù)表的默認(rèn)編碼是的一個(gè)基本操作,這是需要預(yù)先掌握的。生成測(cè)試數(shù)據(jù)接下來創(chuàng)建個(gè)課程作者,也就是個(gè)類的實(shí)例,每個(gè)作者對(duì)應(yīng)兩個(gè)課程,共個(gè)類實(shí)例。 首先安裝包sudo pip3 install sqlalchemy 數(shù)據(jù)庫(kù)的默認(rèn)編碼為 latin1,修改數(shù)據(jù)表的默認(rèn)編碼是 MySQL 的一個(gè)基本操作,這是需要預(yù)先掌握的。不過學(xué)習(xí)本課程時(shí)并不需要這么做,在創(chuàng)建...
閱讀 695·2021-11-15 11:37
閱讀 3316·2021-10-27 14:14
閱讀 6039·2021-09-13 10:30
閱讀 2961·2021-09-04 16:48
閱讀 1926·2021-08-18 10:22
閱讀 2125·2019-08-30 14:19
閱讀 729·2019-08-30 10:54
閱讀 1745·2019-08-29 18:40