TiDB学习笔记（六）-数据库查询优化

一、数据库优化器原理

预处理阶段

• 针对点查进行优化（直接下推到SQL执行器。优化原因：优化方式单一，使用频率最高。）
• 构造初始的逻辑执行计划
  • 常量折叠
  • 表达式简化
  • 子查询处理（子查询转换为表连接）

逻辑优化

• 列剪裁
• 分区剪裁
• 聚合消除
• MAX/MIN优化
• 投影消除
• 外连接消除
• 谓词下推
• 连接顺序调整

物理优化

• 扫描方式
  • 全表扫描
  • 索引扫描-索引选择
• 算子物理实现方式
  • 聚合算子
  • 连接算子
  • 排序算子
• 是否将算子下推到存储层
  • 聚合算子
  • limit算子
  • TopN算子

二、理解执行计划

执行计划

explain id由上向下，从右向左

explain analyze会执行SQL语句，可返回执行时间，预估的数据更准确 。比explain多返回actRows、execution info、memory、task

算子

• 汇聚数据类算子
  • Hash Aggregate        /*+HASH_AGG()*/       
    • 阻塞，最后得出结果。
    • 不需要提前排序
    • 支持并行
    • 内存占用较大
  • Stream Aggregate      /*+STREAM_AGG()*/ 
    • 非阻塞，对limit操作友好
    • 内存占用小
    • 单线程执行
• 扫描数据类算子
  • Point_get(需走唯一索引),Batch_point_get
  • IndexReader无需回表，IndexLookUp走索引但需回表
  • TableReader
  • IndexMerge   需设置参数tidb_enable_index_merge=1
• 表连接类算子
  • HashJoin        /*+HASH_JOIN(t1,t2)*/
    • 选择小表在内存中建立hash表
    • 外联，外连接表建立hash表
    • 多线程
  • MergeJoin
    • 排序后进行表连接
    • 分批次放入内存，节省内存
    • 单线程
  • IndexJoin    /*+INL_JOIN(t1,t2)*/   
    • 批量处理
    • 多线程

管理执行计划

hint，多个提示器的话可以逗号分开

绑定执行计划

• create binding for ....(被绑定的SQL) using ....（走期望索引的SQL）

三、统计信息管理

表级别、列级别统计信息

等深直方图

count-min sketch

统计信息收集方法

analyze table 表名 index 索引名

analyze table 表名 partition 分区名 index 索引名

with num buckets/num topn/num cmsketch depth/num cmsketch width/num samples

控制analyze 的并发度

• tidb_build_stats_concurrency 切分成多个小任务
• tidb_distsql_scan_concurrency 分析普通列时，控制读取region的数量
• tidb_index_serial_scan_concurrency 分析索引列时，控制读取region的数量

自动更新统计信息

• tidb_auto_analyze_ratio 自动更新阈值
• tidb_auto_analyze_start_time 能够自动更新的开始时间
• tidb_auto_analyze_end_time 能够自动更新的结束时间

查看收集信息的状态 show analyze status;

查看表的元信息show stats_meta

查看表的健康状态 show stats_healthy

查看列的元信息 show stats_histograms

查看直方图信息 show stats_buckets

导入导出统计信息

四、基于索引的SQL优化

最多只能同时存在一个加索引的DDL

动态调整创建索引的速度

• tidb_ddl_reorg_worker_cnt 默认值4
• tidb_ddl_reorg_batch_size 默认值256  控制每个worker一起回填数据单位
• tidb_ddl_reorg_priority 默认值PRIORITY_LOW
• tidb_ddl_error_count_limit 默认值512

admin show ddl jobs where table_name = 't1';查看DDL执行进度

目标列被频繁更新时：

tidb_ddl_reorg_worker_cnt增大，QPS和TPS均会降低。对业务影响也较大

tidb_ddl_reorg_batch_size增大，对业务影响较小

目标列仅涉及查询时，修改上述参数对业务影响较小

算子：indexReader 不回表，indexLookupReader 回表

索引选择的维度

1. 索引列涵盖了多少访问条件
2. 索引是否需要回表
3. 索引是否满足一定顺序

索引基于代价的选择

1. 索引的每行数据在存储层的平均长度
2. 索引生成的查询范围的行数
3. 索引的回表代价
4. 索引查询时的范围数量

五、优化实战

SQL STATEMENTS

SLOW QUERIES

大量DML操作导致OOM，解决方案

1. 通过hint或者use index强制走索引
2. 拆分分成多个部分进行执行

执行计划不稳定导致查询延迟增加问题

原因：统计信息更新不及时

解决方案：

1. 及时收集统计信息

        analyze table手动收集或者定时任务

        调整自动收集频率tidb_auto_analyze_ratio、tidb_auto_analyze_start_time、tidb_auto_analyze_end_time

     2.更改执行计划

        使用hint或use index