3-阎书利-PostgreSQL索引损坏到数据安全的思考.pdf

1、PostgreSQL索引损坏到数据安全的思考演讲人 阎书利公司&职位 云和恩墨&技术顾问Subject 1 查询执行流程Profile hereSubject 2 索引损坏的一些现象Profile hereSubject 3 索引损坏的案例分析Profile hereSubject 4 数据安全方面的思考Profile hereCONTENTPART 01 查询执行流程Content hereSQL执行流程解析器 解析器从纯文本的 SQL 语句生成解析树。后续子系统可以从纯文本的 SQL 语句中读取该解析树。分析器 解行解析树的语义分析并生成查询树。重写器 如果存在这样的规则，则重写器使用存储

2、在规则系统中的规则来转换查询树。计划器 计划器生成可以从查询树最有效地执行的计划树。执行器 执行器通过按计划树创建的顺序访问表和索引来执行查询。CBO决定最优路径优化器（Optimizer）是数据库里的一个核心子系统，它的目的是按照一定规则来得到它认为的目标SQL在当前情形下的最高效的执行路径,也就是说优化器的目的是为了得到目标SQL的执行计划。即CBO会选择那些消耗系统I/O和CPU资源最少的执行路径作为最优选择索引与表一样，单独存储，同样是被切分成很多页，索引扫描，先访问索引（访问索引所在页，在索引页中继续找，找到匹配的键，获取被查找的表的元组的TID，再根据该TID去访问表的某页，根据页

3、偏移找到指定元组返回）索引元组中是kv的结构，key代表的是查询条件的值，value即TID，TID中记录了两部分信息，block=2代表页面号，数据位于第几个块（页面），offset=2代表第二个元组，这样就通过索引直接定位了某一条记录，而不需要对页面进行扫描。PART 02 索引损坏的一些现象Content here索引损坏的一些现象1.ERROR:could not read block 1233 in file base/16392/2633 2.ERROR:invalid page header in block 118 of relation base/16384/305673.E

4、RROR:index t1_info_idx contains unexpected zero page at block 04.登录数据库失败postgresysla$psqlpsql:error:could not connect to server:FATAL:index pg_class_oid_index contains unexpected zero page at block 0HINT:Please REINDEX it.5.表上的Autovacuum失效，不能自动收缩age在PostgreSQL中，有多种情况可能导致索引或表的损坏多种情况可能导致索引或表的损坏。可能是Pos

5、tgreSQL的BUG。可能是文件系统的问题，也可能是掉电引起的，存储系统不能再写入磁盘上的文件。而索引损坏的表现形式表现形式比较多样，例如损坏当时没有问题，重启库之后，psql登陆不进数据库。或者能登陆到数据库里，在进行一些查询的时候报出损坏问题。对受影响的表执行DDL或DML操作时，数据库中的损坏通常以错误的形式表现出来。这些错误通常显示为由于缺少文件而导致的读取表的错误。然而，有时候损坏的索引并不像损坏的表那样明显。通常情况下，为了发现是否确实存在索引损坏，需要观察查询的执行时间和结果。需要关注：需要关注：1.使用索引的查询执行了更多的顺序扫描。2.pg_stat_user_tables

6、视图，与seq_scan相比，idx_scan列增加的速度要慢得多（或者根本就没有变化）。3.获取到的一些数据并不是预期的数据，也就是索引查询的数据和顺序扫描的数据不一致。cat postgresql_11.log|grep-E 0-9|grep-i-E ERROR|FATAL|PANIC|awk-F print$5,$6,$7,$8,$9,$10,$11,$12|sort|uniq c例如每天有一个固定的查询，根据应用需求查询数据，并根据策略删除数据，但是某一天查询到的结果和之前的结果查了非常多，就可能出现了问题根据日志和命令筛选出报错大致种类，并结合重点报错查看日志详细内容分析日志分析日志