【TiDB 4.0 PCTA 学习笔记】-查询优化器&TiDB的索引&TiDB的分表@3班+Datamz

课程名称:课程版本(101/201/301)+ 查询优化器&TiDB的索引&TiDB的分表

学习时长:35min

课程收获:

课程内容:

3.7.3 Optimize query health(查询优化器)

系统表:定位不健康的查询以及分析原因

  • STATEMENTS_SUMMARY:系统内存表——当前tidb server
  • SLOW_QUERY:表的数据是从慢日志文件中分析得到的——当前tidb server
  • cluster_STATEMENTS_SUMMARY:范围是所有的tidb server
  • cluster_SLOW_QUERY:范围是所有的tidb server
  • STATEMENTS_SUMMARY_HISTORY:历史表

判断是否健康主要是通过查询是否延迟来判断,慢sql可能的原因:

  • 统计信息过期
  • 读写冲突

系统表 STATEMENTS_SUMMARY常用查询:

01、查询哪个SQL执行时间最长;

SELECT sum_latency, avg_latency , exec_count, query_sample_text
FROM information_schema.statements_summary
ORDER BY sum_latency DESC
LIMIT 3

02、查询某个具体SQL的执行时间和执行次数;

SELECT avg_latency, exec_count , query_sample_text
FROM information_schema.statements_summary
WHERE digest_text LIKE ' SELECT count(*) FROM employee%;

03、查询计划是否变更;

SELECT digest, COUNT(*) , MIN(QUERY_SAMPLE_TEXT) , MIN (plan) , MAX(plan)
FROM STATEMENTS_SUMMARY
GROUP BY digest
HAVING COUNT(*) > 1;

系统表 SLOW_QUERY 常用查询:

01、Top N 慢查询列表;

SELECT query_time ,query ,user FROM cluster_slow_query
WHERE is internal=false AND user like "test%"
ORDER BY query_time desc
LIMIT 5

02、查询统计信息过期的慢SQL;

SELECT query ,query_time ,stats FROM cluster_slow_query
WHERE is internal = false
AND stats like '%pseudo%';

03、查询执行计划发生变更的慢SQL;

SELECT COUNT(distinct plan_digest) As count ,digest ,MIN(query)
FROM cluster_slow_query
GROUP BY digest
HAVING count >1
LIMIT 3

查询指定时间范围内的慢SQL:

SELECT * FROM
(SELECT count (*),
min(time),
sum(query_time) AS sum_query_time,
sum(Process_time) AS sumprocess_time,
sum(Wait_time) AS sumwait_time,
sum(Commit_time)
min(query),
digest
FROM information_schema.CLUSTER_SLOW_QUERY
WHERE time >= '2020-08-10 13:24:00'
AND time < '2020-08-10 13:27:00'
AND Is internal = false
GROUP BY digest) As t1
WHERE t1.digest NOT IN
SELECT digest
FROM information_schema.CLUSTER_SLOW_QUERY
WHERE time >= '2020-08-10 13:20:00'
AND time<'2020-08-10 13:23:00'
GROUP BY digest)
ORDER BY t1.sum-query time DESC Limit 10\G

3.7.4 TiDB Indexes(TiDB 的索引)

  • 唯一索引:可以多个空值,distinct=100%?
  • 主键索引:相当于唯一索引加上 not null 约束;
  • 多列索引:复合索引
  • 前缀索引
  • 表达式索引

聚簇索引:如果想要主键索引成为聚簇索引,必须满足2个条件

  1. alter-primary-key 设置为 false
  2. 主键列字段类型必须是整形;

Tidb 5.0之后支持char等类型创建为聚簇索引;

索引相关系统表:

information_schema.tidb_indexes
show index from Table_Name;----查询指定表的索引

Hint写法,,语法格式:USE index_name

  • select * from T use index(T1) ;
  • select * from T force index(T1) ;

忽略索引写法:

select * from T ignore index(t1);
select * from T use index();

3.7.5 Partition Table(TiDB 的分表)

分区表,每个分区是一个独立的物理表;

Tidb支持三种类型分区表:

  • 范围分区:使用 ‘less than’,必须是整数;
  • 列范围分区
  • 哈希分区

分区表达式中包含的列,必须包含在唯一索引中;

同学你好,感谢参与 TiDB 4.0 课程的学习!

本篇笔记逻辑清晰、内容丰富,被评选为优质笔记,将额外获得 20 积分,并在 「TiDB 培训」分类下获得“置顶”权益,积分兑换规则将于近期开放,敬请关注!

期待您继续产出优质内容!