知识卡片
TABLESAMPLE按数据块抽样,避免了取模抽样导致的全表扫描
从一张大表里随机抽出一小部分数据做统计或测试,直觉上常见的两种做法
都有明显的问题:直接LIMIT一个数量只会拿到物理上排在前面的连续
一批行,不是真正随机、代表性很差;用WHERE id % 1000 = 1这类取模
条件筛选,虽然能得到分布相对均匀的样本,但这个条件本身是对每一行
算出来的表达式,优化器无法用索引加速,必须扫描全表逐行计算取模值,
在大表上代价很高——为了”抽一小部分”付出的却是”读全表”的完整代价,
南辕北辙。PostgreSQL的TABLESAMPLE语法提供了一种从物理存储层面
直接解决这个问题的思路:SELECT * FROM table TABLESAMPLE
SYSTEM(0.1)不是逐行判断”这一行要不要被抽中”,而是先在数据块
(page)这个层面按比例随机选出一部分块,再把选中块里的全部行返回
——本质上是把”随机抽样”这个问题从”行级别的全表遍历+条件判断”下移到
“块级别的随机选择”,只需要访问被选中的那一小部分数据块,不需要碰
其余数据块,天然避免了全表扫描。这个设计提醒了一个更普遍的原则:
“随机抽样”这类需求,如果能让存储引擎在物理布局层面就参与实现(按块
选取而非按行判断),往往比在SQL逻辑层面用条件表达式模拟随机性要
高效得多,因为后者绕不开”必须先看到每一行才能判断它是否入选”这个
遍历成本。
这张卡片看不懂?
这张卡片的反馈已满 10 条,暂时无法继续提交。
已有反馈 · 0 / 10 条
还没有人提交反馈。
原书出处
参考来源
- 位置:《PostgreSQL修炼之道:从小工到专家(第2版)》第12章"数据库
优化"12.7.2节"SQL语句的优化技巧"(源文件:
_epub-src/OEBPS/Text/chapter412.xhtml)
- 结论依据:原文明确"select * from test01 where id%1000 = 1……上面
的SQL语句会走全表扫描,效率不高,其实PostgreSQL提供了数据抽样的
语法,可以直接抽样大表的数据:SELECT * FROM test01 TABLESAMPLE
SYSTEM(0.1)……'TABLESAMPLE SYSTEM(0.1)'的含义是随机找一些数据块
进行抽样,抽样比例是0.1%",直接说明取模抽样导致全表扫描的问题及
TABLESAMPLE按数据块抽样的具体机制。
- 原始内容:select * from test01 where id%1000 = 1……上面的SQL语句
会走全表扫描,效率不高,其实PostgreSQL提供了数据抽样的语法……
"TABLESAMPLE SYSTEM(0.1)"的含义是随机找一些数据块进行抽样。