关于后端:索引选择度问题优化整理

1次阅读

共计 11925 个字符，预计需要花费 30 分钟才能阅读完成。

之前在搞宜搭元数据底层索引优化的时候，针对一些查问时快时慢，以及一些索引抉择的问题，钻研过，也基于看过的一些案例以及本身演绎思考，上面整顿分享下；

个别咱们为了放慢查问速度，会设计索引，当然有索引状况下，大多是会命中去走索引查问；然而呢:

存储优化器去执行，就算加了索引，在肯定时候有可能没用到索引，速度会更慢点，这是为什么不必？
有些时候同一个用户不同工夫去申请，产生雷同 SQL 语句去查问也可能呈现不同的快慢性能，这又是为什么？
就算命中了索引，速度可能更慢，这最初又是为什么？

先举几个之前晓得的例子加以分析；

-- 创立测试表
CREATE TABLE `t` (
  `id` int primary key auto_increment,
  `a` int default null,
  `b` int default null,
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

-- 插入 10w 行测试数据
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

mysql 全表扫描
explain select from t where a between 10000 and 20000;

通过 explain 的执行后果咱们能够看出，下面的 SQL 语句并没有走咱们的索引 a，而是间接应用了全表扫描。
— 强制走索引 a explain select from t force index(a) where a between 10000 and 20000;

通过 explain 的执行后果咱们能够看出，下面的 SQL 语句咱们通过 force index(a)当前，的确应用了索引。
— 开启慢日志 set global slow_query_log = true; set long_query_time = 0; — 别离执行不走索引和走索引的 SQL select from t where a between 10000 and 20000; select from t force index(a) where a between 10000 and 20000;

能够看出走索引的查问比不走索引的查问快了将近 10ms。
然而存储优化器默认没走索引的查问，尽管加了索引

针对某个平台有张音讯发送交换的表，规模达到数千万行级，PG 存储；音讯表上的主查问通常极快，然而也遇到了一些间歇的慢查问超时。慢查问岂但影响了音讯性能的用户体验，而且加大了整个零碎的负荷，拖慢了其余性能的用户体验。
这个查问长这样：

SELECT messages.* FROM messages  WHERE messages.deleted_at IS NULL AND messages.namespace = ?  AND (jsonb_extract_path_text(context, 'topic') IN (?, ?)        OR jsonb_extract_path_text(context, 'topic') LIKE ?      )  AND (context @> '{"involved_parties":[{"id":1,"type":1}]}'::jsonb ) ORDER BY messages.created_at ASC

在 context 上有两个索引

context 列上的 GIN 索引
jsonb_extract_path_text(context,‘topic’)表达式上的 BTREE 表达式索引

看下下面语句偶然慢的时候 QUERY PLAN：

UERY PLAN
------------------------------------------------------------------------------
 Sort  (cost=540.08..540.09 rows=3 width=915)
   Sort Key: created_at
   ->  Bitmap Heap Scan on messages  (cost=536.03..540.06 rows=3 width=915)
         Recheck Cond: (((jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) = ANY ('{?,?}'::text[])) OR (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~~ '?%'::text)) AND (context @> '{"involved_parties": [{"id": 1,"type": 1}]}'::jsonb))
         Filter: ((deleted_at IS NULL) AND ((namespace)::text = '?'::text) AND ((jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) = ANY ('{?,?}'::text[])) OR (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~~ '?%'::text)))
         ->  BitmapAnd  (cost=536.03..536.03 rows=1 width=0)
               ->  BitmapOr  (cost=20.13..20.13 rows=249 width=0)
                     ->  Bitmap Index Scan on index_messages_on_topic_key_string  (cost=0.00..15.55 rows=249 width=0)
                           Index Cond: (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) = ANY ('{?,?}'::text[]))
                     ->  Bitmap Index Scan on index_messages_on_topic_key_string  (cost=0.00..4.57 rows=1 width=0)
                           Index Cond: ((jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~>=~ '?'::text) AND (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~<~ '?'::text))
               ->  Bitmap Index Scan on index_messages_on_context  (cost=0.00..515.65 rows=29820 width=0)
                     Index Cond: (context @> '{"involved_parties": [{"id": 1,"type": 1}]}'::jsonb)
(这个查问打算来自 EXPLAIN，因为 EXPLAIN ANALYZE 超时)

看下下面语句快的时候 QUERY PLAN：

QUERY PLAN
------------------------------------------------------------------------------
 Sort  (cost=667.75..667.76 rows=3 width=911) (actual time=0.093..0.094 rows=7 loops=1)
   Sort Key: created_at
   Sort Method: quicksort  Memory: 35kB
   ->  Bitmap Heap Scan on messages  (cost=14.93..667.73 rows=3 width=911) (actual time=0.054..0.077 rows=7 loops=1)
         Recheck Cond: ((jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) = ANY ('{?,?}'::text[])) OR (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~~ '?%'::text))
         Filter: ((deleted_at IS NULL) AND (context @> '{"involved_parties": [{"id": 1,"type": 1}]}'::jsonb) AND ((namespace)::text = '?'::text) AND ((jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) = ANY ('{?,?}'::text[])) OR (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~~ '?%'::text)))
         Heap Blocks: exact=7
         ->  BitmapOr  (cost=14.93..14.93 rows=163 width=0) (actual time=0.037..0.037 rows=0 loops=1)
               ->  Bitmap Index Scan on index_messages_on_topic_key_string  (cost=0.00..10.36 rows=163 width=0) (actual time=0.029..0.029 rows=4 loops=1)
                     Index Cond: (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) = ANY ('{?,?}'::text[]))
               ->  Bitmap Index Scan on index_messages_on_topic_key_string  (cost=0.00..4.57 rows=1 width=0) (actual time=0.007..0.007 rows=7 loops=1)
                     Index Cond: ((jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~>=~ '?'::text) AND (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~<~ '?'::text))
 Planning time: 0.313 ms
 Execution time: 0.138 ms

图中执行打算能够看出，(context @> ‘{“involved_parties”: [{“id”: 1, “type”: 1}]}’::jsonb) 没走索引更快，走了索引还更慢；而且同一个 SQL 查问，有些时候走索引，有些时候又不走；

这里针对案例二进行剖析:
理解下索引区别

GIN 是 PostgreSQL 提供的一款用于简单值的索引引擎，个别用于数组、JSON 或文本等的数据结构。GIN 的设计用处是索引那些可对内部结构做细分的数据，这样就能够查找数据外部的子数据了。BTREE 是 PostgreSQL 的默认索引引擎，能对简略值做相等性比拟或范畴查问。表达式索引是 PostgreSQL 提供的一种强力的索引类型，能对一个表达式（而不是一个列）做索引。JSONB 类型个别只能用 GIN 这样的索引引擎，因为 BTREE 只反对标量类型（能够了解为“没有内部结构的简略值类型”）。因而，context 列上的 jsonb_extract_path_text(context,‘topic’)表达式能够用 BTREE 索引，因为它返回字符串类型。不同于 BTREE 索引对立而统一的示意格局，GIN 索引的内容能够因所用数据类型和操作符类型的不同而极为不同。而且思考到查问参数的抉择度有较高的多样性，GIN 索引更实用于一些特定的查问，不像 BTREE 索引宽泛实用于相等性比拟和范畴查问。

预剖析
一个查问通常会先做索引扫描以初筛，再对筛选后的范畴做表扫描（一个特例是，当索引扫描足以笼罩所需的所有数据列时，则无需表扫描）。为了最大化性能，索引要有较好的抉择度来放大范畴，以缩小甚至防止之后的表扫描。条件 context @>‘{“involved_parties”:[{“id”:1,”type”:1}]}’::jsonb 能应用 context 列上的 GIN 索引，然而这并不是一个好抉择，因为 {“id”:1,”type”:1} 这个值是存在于大多数行中的一个非凡值（这数字就很非凡，像管理员的号码）。因而，GIN 索引对于这个条件的抉择度很差。实际上，这个查问中的其余条件已能提供很好的抉择度，所以永远不须要为这个条件应用索引。
针对快慢查问剖析
慢查问门路快查问门路

如图可见，这个慢查问打算比快查问打算更简单。它多了一个”BitmapAnd”和一个扫描 index 3 的”Bitmap Index Scan”节点（index 3 是 context 列上的 GIN 索引）。若 index 3 低效率，总体性能就会升高。
当老本预计精确时，查问打算器工作得很好。然而 JSONB 上的 GIN 索引的老本预计不是很精确的。由观测可见，它认为这个索引的抉择度为 0.001（这是一个硬编码的固定值），也就是说它假如任何相干的查问都会抉择表中所有行的 0.1%，但在咱们这个场景它理论会抉择 90% 的行，所以这个假如不成立。谬误的假如使查问打算器低估了慢查问打算的老本。尽管 JSONB 类型的列也有一些统计信息，但如同没有起到作用。

所以说有些时命中索引不肯定就快，而且索引优化器也不肯定是精确的，可能会执行更慢；

治理好索引
更多的索引并不意味着更好的性能。事实上，每减少一个索引都会升高写操作的性能。如果查问打算器抉择了不高效的索引，那么查问依然会很慢。
不要沉积索引（例如每一列都建索引就是不可取的）。试着尽可能删除一些索引吧。而且每改变一个索引都要监控其对性能的影响。
优选简略的数据库设计
RDBMS（关系型数据库系统）中的数据个别都宜用范式化设计。JSON 或 JSONB 则是 NoSQL 格调的反范式化设计。
范式化和反范式化哪个更好呢？从业务的角度，要具体情况具体分析。从 RDBMS 的角度，范式化总是更简略更好，而反范式化则能够在某些状况作为补充。
倡议 1：思考从 DDD（畛域驱动设计）的角度来设计数据模型。

实体总是能够建模为表，值对象总是能够嵌入保留在实体中（而有时为了性能，大型值对象也能够建模为表）。
某个关联的指标实体若为聚合根，就肯定不能嵌入保留在别处（而要自成一表）。但如果关联的指标实体不是聚合根，并且关联的源实体是自蕴含的聚合根，那么指标实体就能够被嵌入保留。

倡议 2: 古代 RDBMS 中的可空列 (nullable column) 很高效，不必过于放心性能，如果多个可空列是对于可选属性 (optional attribute) 最扼要的建模形式，就不要犹豫了，更别把 JSONB 当作对可空列的“优化”形式。

PostgreSQL 保护每一张表的统计信息，包含而不限于元组数 (tuple number)，页数(page number)，最常见的值(most common values)，柱状图界线(histogram bounds) 和不同值的个数 (number of distinct values, 可能相当于集的基数 set cardinality)。有一些统计信息是采样失去的而不够精确。查问打算器会对查问生成多个可能的打算，依据统计信息和规定来预计老本，再抉择最高效的那个打算。查问打算的品质取决于统计数据的准确性。精确的数据带来优良的执行（这也是数据迷信和数据驱动业务的一个好准则）。
正如所提到的，JSONB 上的 GIN 的老本预计不是很精确的。而标量类型上的 BTREE 的老本预计则精确得多，但不是齐全筹备。因而 JSONB 不适宜某些状况。为了谋求效率，作为变通方法，能够对 JSONB 的某个标量类型属性建一个 BTREE 表达式索引。来自 ScaleGrid 的这片文章很好地介绍了怎么高效应用 JSONB 和 GIN。
倡议：PostgreSQL 有一些个性，如表达式索引和局部索引都是弱小而有老本效益的。只有基于数据分析认为有效益，都值得选用之。

无论咱们是否对问题的潜在根因有揣测，进步可察看性都是最好的做法。查问日志能证实导致慢申请的是慢查问，而不是利用程序代码或连贯期待。主动 EXPLAIN 能捕捉慢查问所用的实在的查问打算。
像 Datadog 这样的 APM（应用程序性能治理）也是一个重要的工具。它能提供很多洞察：

这个问题是由资源有余所致吗？不，资源有余应平等影响任何 SQL CRUD 语句，但咱们只察看到慢的 SELECT。
这个问题产生于每天的同一时间吗？不，它能产生于任何工夫。
每一次产生是独立事件吗？不，会在某个小的工夫窗汇集产生多个事件。那时肯定是产生了什么事才导致这个问题。

一些优化的措施

针对宜搭自身，一些可能有参考价值的优化措施，当然这里不波及缓存，次要讲存储层面的

宜搭作为钉钉上低代码开发平台，下面承载着上百万利用，实例数据总共达到几十亿规模；不同的利用会成长出不同的场景业务，不同的场景业务也会衍生出很多业务组件，比方单行文本组件，老本组件等；这些组件对应存储会有很多不同的索引去减速查问；

这里优化措施大体有 3 类

调整 SQL 语句，使得之前特定组件没走索引，充沛走索引；比方上面 gin 语句的查问

针对一些特定组件的查问，优化了查问语句，使得充分利用索引，在数据量大时候查问更快；
正如下面案例说的，有了索引不肯定快，没索引可能更快，所以须要依据查问场景管制并判断；
优化前

                           QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------
 Limit  (cost=4085.57..4086.38 rows=36 width=51) (actual time=100.118..100.121 rows=1 loops=1)
   ->  Gather Motion 128:1  (slice1; segments: 128)  (cost=4085.57..4086.38 rows=36 width=51) (actual time=100.111..100.111 rows=1 loops=1)
         Merge Key: gmt_create, pid
         ->  Limit  (cost=4085.57..4085.66 rows=1 width=51) (actual time=52.273..52.276 rows=1 loops=1)
               ->  Sort  (cost=4085.57..4085.66 rows=1 width=51) (actual time=52.271..52.273 rows=1 loops=1)
                     Sort Key: gmt_create, pid
                     Sort Method:  top-N heapsort  Memory: 4224kB
                     ->  Index Scan Backward using idx_app_type_table_name_gmt_create on yida_entity_instance a  (cost=0.20..4084.66 rows=1 width=5
1) (actual time=0.361..52.258 rows=1 loops=1)
                           Index Cond: (((app_type)::text = 'APP_GXUUGZJ1ZPPBIJKLE9BH'::text) AND ((model_uuid)::text = 'FORM-EX866CB1E6TV7F6SZME2Y
QA5IKWO1052BFOWKK'::text))
                           Filter: (is_deleted = 'n'::bpchar) AND (json_data -> 'employeeField_kw0b5hyf_code'::text) = '["050323"]'::jsonb) 
 Planning time: 0.254 ms
   (slice0)    Executor memory: 180K bytes.
   (slice1)    Executor memory: 188K bytes avg x 128 workers, 188K bytes max (seg0).  Work_mem: 33K bytes max.
 Memory used:  2047000kB
 Optimizer: Postgres query optimizer
 Execution time: 101.799 ms
(16 rows)

优化后

                                                                                                                   QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------
---------
 Limit  (cost=5.55..6.36 rows=36 width=51) (actual time=28.342..28.344 rows=1 loops=1)
   ->  Gather Motion 128:1  (slice1; segments: 128)  (cost=5.55..6.36 rows=36 width=51) (actual time=28.339..28.339 rows=1 loops=1)
         Merge Key: gmt_create, pid
         ->  Limit  (cost=5.55..5.64 rows=1 width=51) (actual time=5.173..5.175 rows=1 loops=1)
               ->  Sort  (cost=5.55..5.64 rows=1 width=51) (actual time=5.171..5.173 rows=1 loops=1)
                     Sort Key: gmt_create, pid
                     Sort Method:  top-N heapsort  Memory: 4224kB
                     ->  Bitmap Heap Scan on yida_entity_instance a  (cost=4.51..4.64 rows=1 width=51) (actual time=5.156..5.159 rows=1 loops=1)
                           Recheck Cond: (json_data @> '{"employeeField_kw0b5hyf_code": ["050323"]}'::jsonb)
                           Filter: ((is_deleted = 'n'::bpchar) AND ((app_type)::text = 'APP_GXUUGZJ1ZPPBIJKLE9BH'::text) AND ((model_uuid)::text =
'FORM-EX866CB1E6TV7F6SZME2YQA5IKWO1052BFOWKK'::text))
                           ->  Bitmap Index Scan on idx_json_data_path  (cost=0.00..4.50 rows=1 width=0) (actual time=5.129..5.129 rows=4 loops=1)
                                 Index Cond: (json_data @> '{"employeeField_kw0b5hyf_code": ["050323"]}'::jsonb)
 Planning time: 0.345 ms
   (slice0)    Executor memory: 157K bytes.
   (slice1)    Executor memory: 495K bytes avg x 128 workers, 576K bytes max (seg31).  Work_mem: 33K bytes max.
 Memory used:  2047000kB
 Optimizer: Postgres query optimizer
 Execution time: 30.130 ms
(18 rows)

这里次要调整了查问 SQL
把 a.json_data->’employeeField_kw0b5hyf_code’='[“050323”]’ 换成 json_data @> ‘{“employeeField_kw0b5hyf_code”: [“050323”]}’::jsonb 查问，充分利用 gin jsonb_path_ops 的索引（相比 gin jsob_ops 索引更高效）；
当然下面在数据量略微大一点成果更显著，因为数据量太少会默认走表扫描，不走索引更快；

阐明在 JSONB 上创立 GIN 索引的形式有两种：应用默认的 jsonb_ops 操作符创立和应用 jsonb_path_ops 操作符创立。两者的区别在 jsonb_ops 的 GIN 索引中，JSONB 数据中的每个 key 和 value 都是作为一个独自的索引项的，而 jsonb_path_ops 则只为每个 value 创立一个索引项。

大家还记得最下面案例二吗？下面调整后的 SQL 语句，如果是在案例二场景当中，调整后会更慢，所以须要具体场景具体分析；

针对存储优化器的查问效率状况，自行抉择最佳的扫描打算形式

正如下面案例说的，优化器也不肯定是精确的，所以有些时候须要咱们代码优化器自行抉择最佳的扫描打算形式

比如说：咱们查问场景中会有 limit 1，limit 2，limit 5 等这种查问语句，用 limit 来确保它在找到 n 个满足条件的行时就停下，而不必扫描整个表；
对于优化器来说，针对 limit 很小的数值，认为表扫描可能会更快，咱们有些利用数据量很大，这种场景如果走表扫描，不走索引，效率会更慢（取决于扫描行数来命中）；这种状况咱们查问的时候，会针对性 force index 申请去强制走索引扫描打算，晋升速度；

后续针对每个 SQL 的代价效率统计，也能够主动抉择对应扫描打算，也算是对优化器针对业务场景不同下不同代价执行的一个补充；这个步骤能够了解叫“探查执行”，在宜搭专属大客户场景下，后续会基于这个进行“探查执行”，以达到大数据量下查问效率的最优解；

SQL Parse 业务优化器，前置解决优化 SQL 查问

这里 SQL Parse 业务优化器，次要是针对咱们业务上不合理待优化的 SQL，算是前置拦挡优化，与存储的还是有点区别；目标是优化器的补充，让存储更加专一于基于代价和老本的优化（CBO，cost based optimization）上，让优化器能更多的集中在了解计算进行执行打算优化这件事件上。

宜搭自身有很多业务性能，这些业务性能对接底层元数据引擎，来操作获取数据；业务上具体抉择 AST 数据操作参数来调用元数据引擎对立 API；

元数据底层获取到对应的 AST 参数，解析后组装 SQL，这里针对性生成的不合理 SQL 进行优化

没有利用标识，底层会兜底上下文去取，如果没有的话，会抛出不合理的异样，回绝不合理的 SQL 去查问，缩小查问范畴到具体利用层面；
limit 没传的，组装 SQL 会默认给个值；
针对 select * 查问语法，默认解析成 select 字段
以及一些函数或者表达式的变换，比方日期函数大于等于值，命中不了索引，SQL 优化；
数值介于查问，显著命中不了数据的，直接判断拦挡；
一些不合理多条件查问，合并
…

最近团队有一些 hc，对元数据或者低代码平台感兴趣的来试试；java 研发，数据研发，技术专家等都可，欢送来撩
简历发 edagarli.lz@alibaba-inc.com 或者微信私我 hangzhoushoot

Understanding Postgres GIN Indexes: The Good and the Bad
Postgres Planner not using GIN index Occasionally
Gitlab once faced a GIN related issue
Understanding Postgres query planner behaviour on GIN index
Statistics used by the query planner
When To Avoid JSONB In A PostgreSQL Schema
Using JSONB in PostgreSQL: How to Effectively Store & Index JSON Data in PostgreSQL
https://zhuanlan.zhihu.com/p/523900025
https://www.cnblogs.com/flying-tiger/p/6702796.html
https://cloud.tencent.com/developer/article/1943819

正文完

后端

发表至：后端

2022-12-23

0

关于后端:订单支付超时自动关闭订单实现

关于后端:关系数据库如何工作

关于后端:一文吃透Java并发高频面试题

关于后端:某东Java工程师笔试题解含BFS通用通俗模板

关于程序员:翻译图像到Base64字符串转换

关于后端:索引选择度问题优化整理

案例一

案例二

案例剖析

论断

一些有用的准则

准则 1: 少即是多

准则 2: 统计信息要精确

准则 3: 进步可察看性

一些优化的措施

调整 SQL 语句，使得之前特定组件没走索引，充沛走索引；比方上面 gin 语句的查问

针对存储优化器的查问效率状况，自行抉择最佳的扫描打算形式

SQL Parse 业务优化器，前置解决优化 SQL 查问

参考

站内搜索