問(wèn)題描述
分析

表的信息
估算cost
start-up cost
run cost
執(zhí)行計(jì)劃
實(shí)際執(zhí)行時(shí)間
從內(nèi)核視角來(lái)分析

解決方案

禁用走主鍵掃描
增加(user_id, id)索引

寫(xiě)在最后

Coding過(guò)程中經(jīng)常會(huì)寫(xiě)SQL語(yǔ)句，有時(shí)寫(xiě)的SQL出現(xiàn)慢查詢(xún)而被DBA鄙視。我們一起從使用者，DBA，內(nèi)核開(kāi)發(fā)三個(gè)不同角度來(lái)分析和解決一個(gè)SQL性能問(wèn)題。

問(wèn)題描述

同事A來(lái)問(wèn)我這個(gè)假DBA一條SQL的性能問(wèn)題：

A：兩條SQL語(yǔ)句只有l(wèi)imit不一樣，而limit 1的執(zhí)行比limit 10的慢N倍
我：是不是緩存問(wèn)題，先執(zhí)行limit 10再執(zhí)行limit 1試試
A：......，執(zhí)行了，limit還是很慢

兩條SQL生產(chǎn)環(huán)境執(zhí)行情況

limit 10

select xxx from user_gift where user_id=11695667 and user_type = 'default' order by id desc limit 10;

Execution Time: 1.307 ms

limit 1

select xxx from user_gift where user_id=11695667 and user_type = 'default' order by id desc limit 1;

Execution Time: 144.098 ms

表結(jié)構(gòu)

# \d user_gift;                                         Table "yay.user_gift"    Column    |           Type           | Collation | Nullable |                    Default--------------+--------------------------+-----------+----------+------------------------------------------------ id           | bigint                   |           | not null | nextval('user_gift_id_seq'::regclass) user_id      | integer                  |           | not null | ug_name | character varying(100)   |           | not null | expired_time | timestamp with time zone |           |          | now() created_time | timestamp with time zone |           | not null | now() updated_time | timestamp with time zone |           | not null | now() user_type   | user_type               |           | not null | 'default'::user_typeIndexes:    "user_gift_pkey" PRIMARY KEY, btree (id)    "idx_user_type" btree (user_id, ug_name)    "user_gift_ug_name_idx" btree (ug_name)

分析

執(zhí)行計(jì)劃

既然不是緩存問(wèn)題，那我們先看看執(zhí)行計(jì)劃有什么不一樣的

limit 1

# explain analyze verbose select xxx from user_gift where user_id=11695667 and user_type = 'default' order by id desc limit 1;                                                                                QUERY PLAN--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit  (cost=0.43..416.25 rows=1 width=73) (actual time=135.213..135.214 rows=1 loops=1)   Output: xxx   ->  Index Scan Backward using user_gift_pkey on yay.user_gift  (cost=0.43..368000.44 rows=885 width=73) (actual time=135.212..135.212 rows=1 loops=1)         Output: xxx         Filter: ((user_gift.user_id = 11695667) AND (user_gift.user_type = 'default'::user_type))         Rows Removed by Filter: 330192 Planning Time: 0.102 ms Execution Time: 135.235 ms(8 rows)
Time: 135.691 ms

limit 10

# explain analyze verbose select xxx from user_gift where user_id=11695667 and user_type = 'default' order by id desc limit 10;                                                                        QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------------------------- Limit  (cost=868.20..868.22 rows=10 width=73) (actual time=1.543..1.545 rows=10 loops=1)   Output: xxx   ->  Sort  (cost=868.20..870.41 rows=885 width=73) (actual time=1.543..1.543 rows=10 loops=1)         Output: xxx         Sort Key: user_gift.id DESC         Sort Method: top-N heapsort  Memory: 27kB         ->  Index Scan using idx_user_type on yay.user_gift  (cost=0.56..849.07 rows=885 width=73) (actual time=0.020..1.366 rows=775 loops=1)               Output: xxx               Index Cond: (user_gift.user_id = 11695667)               Filter: (user_gift.user_type = 'default'::user_type) Planning Time: 0.079 ms Execution Time: 1.564 ms(12 rows)
Time: 1.871 ms

可以看到，兩個(gè)SQL執(zhí)行計(jì)劃不一樣：? ?

limit 1語(yǔ)句：使用主鍵進(jìn)行倒序掃描， Index Scan Backward using user_gift_pkey on yay.user_gift
limit 10語(yǔ)句：使用(user_id, user_type)復(fù)合索引直接查找用戶(hù)數(shù)據(jù)，Index Scan using idx_user_type on yay.user_gift

為什么執(zhí)行計(jì)劃不一樣？

total cost

其實(shí)postgreSQL的執(zhí)行計(jì)劃并沒(méi)有“問(wèn)題”，因?yàn)?/span>limit 1的total costLimit (cost=0.43..416.25 rows=1 width=73) 是416，run cost是416-0.43=415.57。而limit 10的total costLimit (cost=868.20..868.22 rows=10 width=73)是868.22。

如果使用Index Scan Backward using user_gift_pkey的方式估算，那么limit 1成本是415, limit 2是415*2=830, limit 3 是 1245，大于868，所以當(dāng)limit 3的時(shí)候會(huì)使用Index Scan using idx_user_type掃索引的計(jì)劃。

?驗(yàn)證

# explain  select xxx from user_gift where user_id=11695667 and user_type = 'default' order by id desc limit 2;                                                       QUERY PLAN------------------------------------------------------------------------------------------------------------------------- Limit  (cost=0.43..831.95 rows=2 width=73)   ->  Index Scan Backward using user_gift_pkey on user_gift  (cost=0.43..367528.67 rows=884 width=73)         Filter: ((user_id = 11695667) AND (user_type = 'default'::user_type))(3 rows)
Time: 0.341 ms# explain  select xxx from user_gift where user_id=11695667 and user_type = 'default' order by id desc limit 3;                                                QUERY PLAN---------------------------------------------------------------------------------------------------------- Limit  (cost=866.19..866.20 rows=3 width=73)   ->  Sort  (cost=866.19..868.40 rows=884 width=73)         Sort Key: id DESC         ->  Index Scan using idx_user_type on user_gift  (cost=0.56..854.76 rows=884 width=73)               Index Cond: (user_id = 11695667)               Filter: (user_type = 'default'::user_type)(6 rows)
Time: 0.352 ms

結(jié)果顯示：

當(dāng)limit 2時(shí)，執(zhí)行計(jì)劃是Index Scan Backward using user_gift_pkey
當(dāng)limit 3時(shí)，就改變計(jì)劃了，Index Scan using idx_user_type on user_gift

實(shí)際執(zhí)行時(shí)間

limit 1時(shí)成本估算的是416.25，比limit 10的868.22還是要快的。
但是實(shí)際limit 1執(zhí)行cost是135.691 ms，而limit 10執(zhí)行cost是1.871 ms，比limit 10慢了70倍！！！

我們重新執(zhí)行下explain，加上buffers選項(xiàng)

# explain (analyze, buffers, verbose)  select xxx from user_gift where user_id=11695667 and user_type = 'default' order by id desc limit 1;                                                                                QUERY PLAN--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit  (cost=0.43..416.29 rows=1 width=73) (actual time=451.542..451.544 rows=1 loops=1)   Output: xxx   Buffers: shared hit=214402 read=5280 dirtied=2302   I/O Timings: read=205.027   ->  Index Scan Backward using user_gift_pkey on yay.user_gift  (cost=0.43..368032.94 rows=885 width=73) (actual time=451.540..451.540 rows=1 loops=1)         Output: xxx         Filter: ((user_gift.user_id = 11695667) AND (user_gift.user_type = 'default'::user_type))         Rows Removed by Filter: 333462         Buffers: shared hit=214402 read=5280 dirtied=2302         I/O Timings: read=205.027 Planning Time: 1.106 ms Execution Time: 451.594 ms(12 rows)

# explain (analyze, buffers, verbose)  select xxx from user_gift where user_id=11695667 and user_type = 'default' order by id desc limit 3;                                                                        QUERY PLAN----------------------------------------------------------------------------------------------------------------------------------------------------------- Limit  (cost=860.51..860.52 rows=3 width=73) (actual time=14.633..14.634 rows=3 loops=1)   Output: xxx   Buffers: shared hit=467 read=321   I/O Timings: read=10.112   ->  Sort  (cost=860.51..862.72 rows=885 width=73) (actual time=14.632..14.632 rows=3 loops=1)         Output: xxx         Sort Key: user_gift.id DESC         Sort Method: top-N heapsort  Memory: 25kB         Buffers: shared hit=467 read=321         I/O Timings: read=10.112         ->  Index Scan using idx_user_type on yay.user_gift  (cost=0.56..849.07 rows=885 width=73) (actual time=0.192..14.424 rows=775 loops=1)               Output: xxx               Index Cond: (user_gift.user_id = 11695667)               Filter: (user_gift.user_type = 'default'::user_type)               Buffers: shared hit=464 read=321               I/O Timings: read=10.112 Planning Time: 0.111 ms Execution Time: 14.658 ms(18 rows)

可以看出：

limit 1時(shí)的IO成本I/O Timings: read=205.027，Rows Removed by Filter: 333462顯示過(guò)濾了333462行記錄
limit 3時(shí)IO成本I/O Timings: read=10.112， ?

從上面輸出Buffers: shared hit=214402 read=5280 dirtied=2302可以看出limit 1的計(jì)劃從磁盤(pán)讀取了5280個(gè)blocks(pages)才找到符合where條件的記錄。

?為什么要讀取這么多數(shù)據(jù)呢？我們來(lái)看看統(tǒng)計(jì)信息：

schemaname             | yaytablename              | user_giftattname                | idinherited              | fnull_frac              | 0avg_width              | 8n_distinct             | -1most_common_vals       | most_common_freqs      | histogram_bounds       | {93,9817,19893,28177,.......}correlation            | 0.788011most_common_elems      | most_common_elem_freqs | elem_count_histogram   | 
schemaname             | yaytablename              | user_giftattname                | user_idinherited              | fnull_frac              | 0avg_width              | 4n_distinct             | -0.175761most_common_vals       | {11576819,10299480,14020501,.......,11695667,......}most_common_freqs      | {0.000353333,0.000326667,0.000246667,......,9.33333e-05,......}histogram_bounds       | {3,10002181,10005599,10009672,......,11693300,11698290,......}correlation            | 0.53375most_common_elems      | most_common_elem_freqs | elem_count_histogram   | 
schemaname             | yaytablename              | user_giftattname                | user_typeinherited              | fnull_frac              | 0avg_width              | 4n_distinct             | 3most_common_vals       | {default, invalid, deleted}most_common_freqs      | {0.997923,0.00194,0.000136667}histogram_bounds       | correlation            | 0.99763most_common_elems      | most_common_elem_freqs | elem_count_histogram   |

從統(tǒng)計(jì)信息里可以看出：

user_id字段的most_common_vals中有11695667(user_id)的值，則可以直接通過(guò)其對(duì)應(yīng)的most_common_freqs來(lái)得到其selectivity是9.33333e-05；
user_type字段為default對(duì)應(yīng)的selectivity是0.997923。?
所以where user_id=11695667 and user_type='default'的selectivity是0.0000933333*0.997923 = 0.0000931394467359。?

那么可以估算出滿(mǎn)足where條件的用戶(hù)數(shù)是0.0000931394467359 * 9499740(總用戶(hù)數(shù)) = ?884.8，和執(zhí)行計(jì)劃(cost=0.43..367528.67 rows=884 width=73)的884行一樣。

?而優(yōu)化器的估算是基于數(shù)據(jù)分布均勻這個(gè)假設(shè)的：

從user_gift_pkey(主鍵id)掃描的話(huà)：只要掃描9499740/884=10746行就能找到滿(mǎn)足條件的記錄，且無(wú)須進(jìn)行排序(order by id desc)
從idx_user_type索引掃描的話(huà)：雖然能很快找到此用戶(hù)的數(shù)據(jù)，但是需要給884行進(jìn)行排序，掃描+排序的cost比從主鍵掃描要高。?

?那么數(shù)據(jù)分布真的均勻嗎？繼續(xù)查看數(shù)據(jù)的實(shí)際分布：

表最大的page=128709

# select max(ctid) from user_gift;     max------------- (128709,29)(1 row)

user id=11695667的最大page=124329

# select max(ctid), min(ctid) from user_gift where user_id=11695667;     max     |    min-------------+----------- (124329,22) | (3951,64)(1 row)

表本身的pages和tuples數(shù)量

# SELECT relpages, reltuples FROM pg_class WHERE relname = 'user_gift'; relpages |  reltuples----------+-------------   128875 | 9.49974e+06(1 row)

每個(gè)page存儲(chǔ)的記錄數(shù)：9.49974e+06 tuples / 128875 pages = 73.713 tuples/page。

計(jì)算：表(main table)的B+tree的最大page是128709，而實(shí)際用戶(hù)11695667的最大page是124329，128709 - 124329 = 4380，需要掃描4380個(gè)page才能找到符合where條件的記錄所在的page，所以過(guò)濾的rows是4380 pages * 73.713 tuples/page ≈ 322862。

?實(shí)際limit 1時(shí)掃描了5280個(gè)pages(包含了主鍵索引的pages)，過(guò)濾了333462萬(wàn)行記錄，和估算的基本一樣：

Rows Removed by Filter: 333462         Buffers: shared hit=214402 read=5280 dirtied=2302         I/O Timings: read=205.027

所以，此用戶(hù)數(shù)據(jù)分布傾斜了：

優(yōu)化器假設(shè)數(shù)據(jù)分布均勻，只需要掃描10746個(gè)記錄
而實(shí)際需要掃描322862個(gè)記錄

那么掃描5280個(gè)pages要多久？

需要讀取的數(shù)據(jù)量：5280pages * 8KB/page = 41.2MB的數(shù)據(jù)。

[root]$ fio -name iops -rw=read -bs=8k -runtime=10 -iodepth=1 -filename /dev/sdb -ioengine libaio -direct=1...Run status group 0 (all jobs):   READ: bw=193MiB/s (202MB/s), 193MiB/s-193MiB/s (202MB/s-202MB/s), io=1928MiB (2022MB), run=10001-10001msec

從fio結(jié)果可以看出，此數(shù)據(jù)庫(kù)機(jī)器磁盤(pán)的順序讀取速度約為 200MB/s，那么掃描40MB數(shù)據(jù)需要約200ms，與實(shí)際需要的時(shí)間205ms基本相等。

到這里問(wèn)題基本定位清楚了：

postgreSQL的優(yōu)化器認(rèn)為數(shù)據(jù)分布是均勻的，只需要倒序掃描很快就找到符合條件的記錄，而實(shí)際上此用戶(hù)的數(shù)據(jù)分布在表的前端，就導(dǎo)致了實(shí)際執(zhí)行start-up time如此慢了。

從內(nèi)核視角來(lái)分析

我們從postgreSQL內(nèi)核的角度來(lái)繼續(xù)分析幾個(gè)問(wèn)題：

優(yōu)化器如何估算cost
優(yōu)化器如何統(tǒng)計(jì)actual time

表的信息

主鍵索引

# SELECT relpages, reltuples FROM pg_class WHERE relname = 'user_gift_pkey'; relpages |  reltuples----------+-------------    40035 | 9.49974e+06(1 row)

user_id 索引

# SELECT relpages, reltuples FROM pg_class WHERE relname = 'idx_user_type'; relpages |  reltuples----------+-------------   113572 | 9.49974e+06(1 row)

表本身的pages是128875

# SELECT relpages, reltuples FROM pg_class WHERE relname = 'user_gift'; relpages |  reltuples----------+-------------   128875 | 9.49974e+06(1 row)

user id=11695667的數(shù)據(jù)775行

=# select count(1) from user_gift where user_id=11695667; count-------   775(1 row)
=#  select count(1)  from user_gift where user_id=11695667 and user_type = 'default' ; count-------   775(1 row)

樹(shù)高度

# 主鍵高度# select * from  bt_metap('user_gift_pkey'); magic  | version | root | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples--------+---------+------+-------+----------+-----------+-------------+------------------------- 340322 |       3 |  412 |     2 |      412 |         2 |           0 |             9.31928e+06(1 row)

// idx_user_type 高度# select * from  bt_metap('idx_user_type'); magic  | version | root  | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples--------+---------+-------+-------+----------+-----------+-------------+------------------------- 340322 |       3 | 15094 |     3 |    15094 |         3 |           0 |             9.49974e+06(1 row)

估算cost

start-up cost

postgreSQL對(duì)于每種索引的成本估算是不一樣的，我們看看B+tree的start-up成本是如何估算的：

// selfuncs.cvoidbtcostestimate(PlannerInfo *root, IndexPath *path, double loop_count,               Cost *indexStartupCost, Cost *indexTotalCost,               Selectivity *indexSelectivity, double *indexCorrelation,               double *indexPages){    ......
    descentCost = ceil(log(index->tuples) / log(2.0)) * cpu_operator_cost;    costs.indexStartupCost += descentCost;
    ......    // This cost is somewhat arbitrarily set at 50x cpu_operator_cost per page touched    descentCost = (index->tree_height + 1) * 50.0 * cpu_operator_cost;    costs.indexStartupCost += descentCost;
    ......}

其實(shí)start-up cost估算很簡(jiǎn)單，只考慮從B+tree的root page遍歷到leaf page，且將這個(gè)page讀入第一個(gè)tuple(記錄)的cost。

start-up估算公式如下：

N(index,tuple) ：索引tuples(記錄)數(shù)量 ?
Height(index) ：索引B+tree的高度
cpu_operator_cost : 默認(rèn)值0.0025

使用user_gift_pkey計(jì)劃的start-up cost

從上面表信息中可以看出：

N(index,tuple) ：9.49974e+06， ? ?
Height(index) ：2

所以：

和postgreSQL估算的start-up cost=0.43 一樣。

使用idx_user_type計(jì)劃的start-up cost

N(index,tuple) ：9.49974e+06， ? ?
Height(index) ：3

和postgreSQL估算的start-up cost=0.56 一樣。

run cost

run cost的估算是比較復(fù)雜的，判斷的條件非常多，無(wú)法用一個(gè)固定的公式計(jì)算出來(lái)，所以這里就不做計(jì)算，有興趣的可以看postgreSQL源碼src/backend/optimizer/path/costsize.c的cost_index函數(shù)。

actual start-up time vs estimated start-up cost

剛剛的分析中有一個(gè)疑問(wèn)被忽略了：estimated start-up cost是開(kāi)始執(zhí)行計(jì)劃到從表中讀到的第一個(gè)tuple的cost(cost is an arbitrary unit)；而actual start-up time則是開(kāi)始執(zhí)行計(jì)劃到從表中讀取到第一個(gè)符合where條件的tuple的時(shí)間。這是為什么呢？

SQL處理流程：postgreSQL將SQL轉(zhuǎn)化成AST，然后進(jìn)行優(yōu)化，再將AST轉(zhuǎn)成執(zhí)行器(executor)來(lái)實(shí)現(xiàn)具體的操作。不進(jìn)行優(yōu)化的執(zhí)行器是這樣的：

簡(jiǎn)化的執(zhí)行流程如下：

index scan executor：掃描到一個(gè)tuple，就返回給selection executor
selection executor：對(duì)tuple進(jìn)行過(guò)濾，如果符合條件則返回給limit executor，如果不符合則繼續(xù)調(diào)用index scan executor
limit executor：當(dāng)達(dá)到limit限制則將數(shù)據(jù)返回給projection executor
projection executor：過(guò)濾掉非select列的數(shù)據(jù)

那么如果進(jìn)行優(yōu)化，一般會(huì)將selection executor和projection executor合并到index scan executor中執(zhí)行，以減少數(shù)據(jù)在executor之間的傳遞。

優(yōu)化后的執(zhí)行流程：

index scan executor：掃描到tuple，然后進(jìn)行selection過(guò)濾，如果符合條件就進(jìn)行projection再返回給limit，如果不符合條件，則繼續(xù)掃描
limit executor：當(dāng)達(dá)到limit限制則將數(shù)據(jù)返回

而通過(guò)下面代碼可以看出，postgreSQL對(duì)于執(zhí)行時(shí)間的統(tǒng)計(jì)是基于executor的，

// src/backend/executor/execProcnode.cstatic TupleTableSlot *ExecProcNodeInstr(PlanState *node){    TupleTableSlot *result;    InstrStartNode(node->instrument);    result = node->ExecProcNodeReal(node);
    // 統(tǒng)計(jì)執(zhí)行指標(biāo)    InstrStopNode(node->instrument, TupIsNull(result) ? 0.0 : 1.0);    return result;}

所以actual time的start-up是從啟動(dòng)executor直到掃描到符合where語(yǔ)句的第一條結(jié)果為止。

再看看實(shí)際的函數(shù)調(diào)用棧，user_id=xxx的過(guò)濾已經(jīng)下沉到index scan executor里面了。

--->    int4eq(FunctionCallInfo fcinfo) (/home/ken/cpp/postgres/src/backend/utils/adt/int.c:379)        ExecInterpExpr(ExprState * state, ExprContext * econtext, _Bool * isnull) (/home/ken/cpp/postgres/src/backend/executor/execExprInterp.c:704)        ExecInterpExprStillValid(ExprState * state, ExprContext * econtext, _Bool * isNull) (/home/ken/cpp/postgres/src/backend/executor/execExprInterp.c:1807)        ExecEvalExprSwitchContext(ExprState * state, ExprContext * econtext, _Bool * isNull) (/home/ken/cpp/postgres/src/include/executor/executor.h:322)--->    ExecQual(ExprState * state, ExprContext * econtext) (/home/ken/cpp/postgres/src/include/executor/executor.h:391)        ExecScan(ScanState * node, ExecScanAccessMtd accessMtd, ExecScanRecheckMtd recheckMtd) (/home/ken/cpp/postgres/src/backend/executor/execScan.c:227)--->    ExecIndexScan(PlanState * pstate) (/home/ken/cpp/postgres/src/backend/executor/nodeIndexscan.c:537)        ExecProcNodeInstr(PlanState * node) (/home/ken/cpp/postgres/src/backend/executor/execProcnode.c:466)        ExecProcNodeFirst(PlanState * node) (/home/ken/cpp/postgres/src/backend/executor/execProcnode.c:450)        ExecProcNode(PlanState * node) (/home/ken/cpp/postgres/src/include/executor/executor.h:248)--->    ExecLimit(PlanState * pstate) (/home/ken/cpp/postgres/src/backend/executor/nodeLimit.c:96)        ExecProcNodeInstr(PlanState * node) (/home/ken/cpp/postgres/src/backend/executor/execProcnode.c:466)        ExecProcNodeFirst(PlanState * node) (/home/ken/cpp/postgres/src/backend/executor/execProcnode.c:450)        ExecProcNode(PlanState * node) (/home/ken/cpp/postgres/src/include/executor/executor.h:248)        ExecutePlan(EState * estate, PlanState * planstate, _Bool use_parallel_mode, CmdType operation, _Bool sendTuples, uint64 numberTuples, ScanDirection direction, DestReceiver * dest, _Bool execute_once) (/home/ken/cpp/postgres/src/backend/executor/execMain.c:1632)        standard_ExecutorRun(QueryDesc * queryDesc, ScanDirection direction, uint64 count, _Bool execute_once) (/home/ken/cpp/postgres/src/backend/executor/execMain.c:350)        ExecutorRun(QueryDesc * queryDesc, ScanDirection direction, uint64 count, _Bool execute_once) (/home/ken/cpp/postgres/src/backend/executor/execMain.c:294)        ExplainOnePlan(PlannedStmt * plannedstmt, IntoClause * into, ExplainState * es, const char * queryString, ParamListInfo params, QueryEnvironment * queryEnv, const instr_time * planduration, const BufferUsage * bufusage) (/home/ken/cpp/postgres/src/backend/commands/explain.c:571)        ExplainOneQuery(Query * query, int cursorOptions, IntoClause * into, ExplainState * es, const char * queryString, ParamListInfo params, QueryEnvironment * queryEnv) (/home/ken/cpp/postgres/src/backend/commands/explain.c:404)        ExplainQuery(ParseState * pstate, ExplainStmt * stmt, ParamListInfo params, DestReceiver * dest) (/home/ken/cpp/postgres/src/backend/commands/explain.c:275)

下面代碼是scan的實(shí)現(xiàn)，其中的ExecQual(qual, econtext)是對(duì)tuple進(jìn)行過(guò)濾，因?yàn)閟election已經(jīng)合并到scan中了。

TupleTableSlot *ExecScan(ScanState *node, ExecScanAccessMtd accessMtd, ExecScanRecheckMtd recheckMtd){    ......    for (;;)    {        TupleTableSlot *slot;        slot = ExecScanFetch(node, accessMtd, recheckMtd);        ......        econtext->ecxt_scantuple = slot;
        // Note : selection判斷        if (qual == NULL || ExecQual(qual, econtext))        {            if (projInfo)            {                return ExecProject(projInfo);            }            else            {                return slot;            }        }        else            InstrCountFiltered1(node, 1);    }}

解決方案

禁用走主鍵掃描

既然計(jì)劃走的是user_gift_pkey倒序掃描，那么我們可以手動(dòng)避免優(yōu)化器使用這個(gè)索引。

# explain analyze verbose select xxx from user_gift where user_id=11695667 and user_type = 'default' order by id+0 desc limit 1;

將order by id改成order by id+0，由于id+0是個(gè)表達(dá)式所以?xún)?yōu)化器就就不會(huì)使用user_gift_pkey這個(gè)索引了。

這個(gè)方案不適合所有場(chǎng)景，如果數(shù)據(jù)分布均勻的話(huà)則某些情況下使用user_gift_pkey掃描更加合理。

增加(user_id, id)索引

create index idx_user_id on user_gift(user_id, id);

通過(guò)增加where條件列和排序鍵的復(fù)合索引，來(lái)避免走主鍵掃描。

寫(xiě)在最后

從排除緩存因素，分析查詢(xún)計(jì)劃，定位數(shù)據(jù)分布傾斜，到調(diào)試內(nèi)核源碼來(lái)進(jìn)一步確定原因，最終成功解決性能問(wèn)題。通過(guò)這個(gè)有趣的SQL優(yōu)化經(jīng)歷，相信能給大家?guī)?lái)收獲。

?END?

分享一下我寫(xiě)的《10萬(wàn)字Springboot經(jīng)典學(xué)習(xí)筆記》中，點(diǎn)擊下面小卡片，進(jìn)入【Java禿頭哥】，回復(fù)：筆記，即可免費(fèi)獲取。
點(diǎn)贊是最大的支持?

一個(gè)非常有趣的SQL優(yōu)化案例

分析