基于tidbV6.0探索索引优化思路-低调大师

基于tidbV6.0探索索引优化思路

2022-05-15 516

作者：边城元元

原文来源：https://tidb.net/blog/f3ae08ad

一、背景

在分布式数据库中数据量级大都在千万以上，用到的最多的sql类似 where ,order by ,limit 的语句。

where，order by的字段能否同时走索引呢？带着这个疑问基于 TiDBV6.0 做一个实验验证。

二、环境准备

2.1 建立TiDB cluster111

拓扑如下（cluster111-full.yaml）

global:
  user: "tidb"
  ssh_port: 22
  deploy_dir: "/tidb-deploy"
  data_dir: "/tidb-data"

# # Monitored variables are applied to all the machines.
monitored:
  node_exporter_port: 9100
  blackbox_exporter_port: 9115

server_configs:
  tidb:
    log.slow-threshold: 300
    binlog.enable: false
    binlog.ignore-error: false
  tikv:
    readpool.storage.use-unified-pool: false
    readpool.coprocessor.use-unified-pool: true
  pd:
    schedule.leader-schedule-limit: 4
    schedule.region-schedule-limit: 2048
    schedule.replica-schedule-limit: 64
    replication.location-labels:
      - host

pd_servers:
  - host: 10.0.2.15
    # ssh_port: 22
    # name: "pd-1"
    client_port: 2379
    # peer_port: 2380


tidb_servers:
  - host: 10.0.2.15


tikv_servers:
  - host: 10.0.2.15
    # ssh_port: 22
    port: 20160
    status_port: 20180
    config:
      server.grpc-concurrency: 4
      #server.labels: {host: "10.0.2.15.20160" }

monitoring_servers:
  - host: 10.0.2.15

grafana_servers:
  - host: 10.0.2.15

alertmanager_servers:
  - host: 10.0.2.15

部署集群

具体的部署可以参考文章 https://tidb.net/blog/af8080f7#TiDB-最小实践Cluster111

# tiup cluster list 
# tiup cluster stop cluster111
# tiup cluster destroy cluster111

# 部署cluster111集群
tiup cluster deploy cluster111 ./cluster111-full.yaml --user root -p

tiup cluster start cluster111

2.2 创建库表

CREATE DATABASE `b_crm` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ ;

drop table if exists `m_test_sort`;
CREATE TABLE `m_test_sort` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orgid` int(11) NOT NULL default 0,
    `labelid` int not null default 0,
    `catid` int not null default 0,
    `productid` int not null default 0,
    `p1` int not null default 0,
    `p2` int not null default 0,
    `p3` int not null default 0,
    `name` varchar(20) not null default '',
    `cust_id` char(30) not null default '', 
  PRIMARY KEY (`id`) ,
    key ix_orgid(`orgid`),
    key ix_labelid_catid(`labelid`,`catid`),
    key ix_p1_p2_p3(`p1`,`p2`,`p3`),
    UNIQUE KEY uix_cust_id(`cust_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

2.3 表数据初始化

-- 插入1条 seed
insert into m_test_sort(orgid,labelid,catid,productid,p1,p2,p3,name,cust_id)values(1,1,1,1,1,1,1,'111','111');

-- 执行多次14次
insert into m_test_sort(orgid,labelid,catid,productid,p1,p2,p3,name,cust_id)
select  FLOOR(RAND() * 100),FLOOR(RAND() * 1000),FLOOR(RAND() * 100),FLOOR(RAND() * 1000),FLOOR(RAND() * 1000),FLOOR(RAND() * 1000),FLOOR(RAND() * 1000),concat(substring('赵钱孙李周吴郑王冯陈诸卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康伍余元卜顾孟平黄和穆萧尹姚邵堪汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董粱杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯咎管卢莫经房裘干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚',floor(1+190*rand()),1),substring('明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',floor(1+400*rand()),1),substring('明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',floor(1+400*rand()),if(rand()>0.6,0,1))),substring(md5(concat(rand(),rand(),rand(),rand())), 1, 30) from m_test_sort;

-- 16384 条记录
select count(*) from m_test_sort;

三、测试

主键忽略

-- 查找 最多的orgid
select orgid ,count(*) as ct from m_test_sort GROUP BY orgid ORDER BY ct desc limit 10;

3.1 二级索引 ix_orgid(`orgid`)

3.1.1 where:orgid，order:id

-- where:orgid索引；order:id索引读
EXPLAIN ANALYZE  select id from m_test_sort where orgid=8 order by id limit 100;

3.1.2 where:orgid，order:cust_id

-- where:orgid索引；order:回表 cust_id虽然是唯一索引但是还是回表
EXPLAIN ANALYZE  select id from m_test_sort where orgid=8 order by cust_id limit 100  ;

3.1.3 where:orgid，order:productid

-- where:orgid索引；order:回表  productid 没有索引
EXPLAIN ANALYZE select id from m_test_sort where orgid=8 order by productid limit 100 ;

3.1.4 where:orgid，order:catid

-- where:orgid索引；order:回表  
EXPLAIN ANALYZE select id from m_test_sort where orgid=8 order by catid limit 100;

3.1.5 where:orgid，order:labelid

-- where:orgid索引；order:回表
EXPLAIN ANALYZE select id from m_test_sort where orgid=85 order by labelid  limit 100;

3.2 复合索引两列 ix_labelid_catid(`labelid`,`catid`)

-- 查询 最多的 labelid
select labelid ,count(*) as ct from m_test_sort GROUP BY labelid ORDER BY ct desc limit 10;

3.2.1 where:labelid，order:id

-- where:labelid索引,order:索引读
EXPLAIN ANALYZE select id from m_test_sort where labelid=962 order by id  limit 100;

3.2.2 where:labelid，order:catid

-- where:labelid索引,order:catid 走了复合索引的第二个字段
EXPLAIN ANALYZE select id from m_test_sort where labelid=962 order by catid  limit 100;

3.2.3 where:labelid，order:labelid

-- where:labelid索引,order:labelid 索引读
EXPLAIN ANALYZE select id from m_test_sort where labelid=962 order by labelid  limit 100;

3.2.4 where:labelid，order:cust_id

-- where:labelid索引,order:回表
EXPLAIN ANALYZE  select id from m_test_sort where labelid=962 order by cust_id limit 100  ;

3.2.5 where:labelid，order:productid

-- where:labelid索引,order:回表
EXPLAIN ANALYZE select id from m_test_sort where labelid=962 order by productid  limit 100;

3.2.6 where:labelid，order:orgid

-- where:labelid索引,order:回表
EXPLAIN ANALYZE select id from m_test_sort where labelid=962 order by orgid limit 100;

3.3复合索引三列 ix_p1_p2_p3(`p1`,`p2`,`p3`)

-- 查找 最多的 labelid
select p1 ,count(*) as ct from m_test_sort GROUP BY p1 ORDER BY ct desc limit 10;

3.3.1 where:p1，order:id

-- where:p1索引,order:索引读
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by id  limit 100;

3.3.2 where:p1，order:p1

-- where:p1索引,order:索引读
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by p1  limit 100;

3.3.3 where:p1，order:p2

-- where:p1索引,order:索引读
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by p2  limit 100;

3.3.4 where:p1，order:p3

-- where:p1索引,order:索引读
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by p3  limit 100;

3.3.5 where:p1,p2，order:p3

-- where:p1,p2索引,order:索引读
EXPLAIN ANALYZE select id from m_test_sort where p1=346 and p2=32 order by p3  limit 100;

3.3.6 where:p1，order:labelid

-- where:p1索引,order:回表
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by labelid  limit 100;

3.3.7 where:p1，order:cust_id

-- where:p1索引,order:回表
EXPLAIN ANALYZE  select id from m_test_sort where p1=346 order by cust_id limit 100  ;

3.3.8 where:p1，order:productid

-- where:p1索引,order:回表
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by productid  limit 100;

3.3.9 where:p1，order:orgid

-- where:p1索引,order:回表
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by orgid limit 100;

3.3.10 where:p1，order:catid

-- where:p1索引,order:回表
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by catid  limit 100;

3.4 关于TopN 与limit

参考：https://docs.pingcap.com/zh/tidb/stable/topn-limit-push-down

Limit 节点等价于一个排序规则为空的 TopN 节点。

SQL 中的 LIMIT 子句在 TiDB 查询计划树中对应 Limit 算子节点，ORDER BY 子句在查询计划树中对应 Sort 算子节点，此外，我们会将相邻的 Limit 和 Sort 算子组合成 TopN 算子节点，表示按某个排序规则提取记录的前 N 项。从另一方面来说，Limit 节点等价于一个排序规则为空的 TopN 节点。

和谓词下推类似，TopN（及 Limit，下同）下推将查询计划树中的 TopN 计算尽可能下推到距离数据源最近的地方，以尽早完成数据的过滤，进而显著地减少数据传输或计算的开销。

四、总结

4.1 执行计划汇总

-- 查找 最多的orgid
select orgid ,count(*) as ct from m_test_sort GROUP BY orgid ORDER BY ct desc limit 10;

-- 二级索引
-- where:orgid索引；order:id索引读
EXPLAIN ANALYZE  select id from m_test_sort where orgid=8 order by id limit 100  ;
-- where:orgid索引；order:回表 cust_id虽然是唯一索引但是还是回表
EXPLAIN ANALYZE  select id from m_test_sort where orgid=8 order by cust_id limit 100  ;
-- where:orgid索引；order:回表  productid 没有索引
EXPLAIN ANALYZE select id from m_test_sort where orgid=8 order by productid limit 100 ;
-- where:orgid索引；order:回表  
EXPLAIN ANALYZE select id from m_test_sort where orgid=8 order by catid limit 100;
-- where:orgid索引；order:回表
EXPLAIN ANALYZE select id from m_test_sort where orgid=85 order by labelid  limit 100;



--------------- 
-- 复合索引 2列复合
-- 查找 最多的 labelid
select labelid ,count(*) as ct from m_test_sort GROUP BY labelid ORDER BY ct desc limit 10;
-- where:labelid索引,order:索引读
EXPLAIN ANALYZE select id from m_test_sort where labelid=962 order by id  limit 100;    
-- where:labelid索引,order:catid 走了复合索引的第二个字段
EXPLAIN ANALYZE select id from m_test_sort where labelid=962 order by catid  limit 100;
-- where:labelid索引,order:labelid 索引读
EXPLAIN ANALYZE select id from m_test_sort where labelid=962 order by labelid  limit 100;
-- where:labelid索引,order:回表
EXPLAIN ANALYZE  select id from m_test_sort where labelid=962 order by cust_id limit 100  ;
-- where:labelid索引,order:回表
EXPLAIN ANALYZE select id from m_test_sort where labelid=962 order by productid  limit 100;
-- where:labelid索引,order:回表
EXPLAIN ANALYZE select id from m_test_sort where labelid=962 order by orgid limit 100;

--------------- 
-- 复合索引 3列复合
-- 查找 最多的 labelid
select p1 ,count(*) as ct from m_test_sort GROUP BY p1 ORDER BY ct desc limit 10;
-- where:p1索引,order:索引读
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by id  limit 100;    
-- where:p1索引,order:索引读
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by p1  limit 100;
-- where:p1索引,order:索引读
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by p2  limit 100;
-- where:p1索引,order:索引读
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by p3  limit 100;
-- where:p1,p2索引,order:索引读
EXPLAIN ANALYZE select id from m_test_sort where p1=346 and p2=32 order by p3  limit 100;
-- where:p1索引,order:回表
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by labelid  limit 100;
-- where:p1索引,order:回表
EXPLAIN ANALYZE  select id from m_test_sort where p1=346 order by cust_id limit 100  ;
-- where:p1索引,order:回表
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by productid  limit 100;
-- where:p1索引,order:回表
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by orgid limit 100;
-- where:p1索引,order:回表
EXPLAIN ANALYZE select id from m_test_sort where p1=346 order by catid  limit 100;

4.2 sql语句建议

where 条件尽量走索引
order by 尽量避免回表

谢谢 TiDB社区，谢谢TiDBer，后续会探索更多的TiDB相关的技术！

微信关注我们

原文链接：https://my.oschina.net/u/5674736/blog/5529265

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

TiDB HTAP特性的应用场景简析

作者：何朝洋原文来源：https://tidb.net/blog/da6fe78a TiDB是一种典型的HTAP数据库，即交易分析混合负载型DB，目前已经成为一种流行的新型数据库代表，具有隔离性、实时性、一致性“三性合一”的优秀特性，同时随着版本的迭代，AP的能力在逐渐增强。HTAP不仅概念很火，并且也在逐渐成为除OLTP、OLAP之外，越来越多数据库用户新的选型规范。然而，现阶段，又存在一些现象：一是一夜之间，所有的数据库都变成了HTAP数据库；二是除了“能同时承载交易与分析SQL”这一极易模糊的理解之外，基本没有清晰明确的界定；自然而然，对HTAP的应用场景，也是八仙过海，各式各样，并不清晰的。这些，都导致HTAP有成为一种噱头的趋势。 1. HTAP的界定这里认为，HTAP既然要成为一种新的标准与规范，就必须有尽量明确界定。而达此目的的最基本原则，就是它在技术上必须有不同于过去经典数据库的能力（不应该只是分布式），并且必须对客户的数字化进程带来创新与升级，这同时包括业务架构、应用架构、数据架构与技术架构等层面。面向这一原则，其定义与界定可以持续探讨，而本文仅提出如下几点以供...

2022-05-16

462

Forest 介绍 Forest 是一个开源的 Java HTTP 客户端框架，它能够将 HTTP 的所有请求信息（包括 URL、Header 以及 Body 等信息）绑定到您自定义的 Interface 方法上，能够通过调用本地接口方法的方式发送 HTTP 请求在 Gitee 上现已超过 2.4k star Forest 如何使用 Forest 不需要您编写具体的 HTTP 调用过程，只需要您定义一个接口，然后通过 Forest 注解将 HTTP 请求的信息添加到接口的方法上即可。请求发送方通过调用您定义的接口便能自动发送请求和接受请求的响应 Forest 的工作原理 Forest 会将您定义好的接口通过动态代理的方式生成一个具体的实现类，然后组织、验证 HTTP 请求信息，绑定动态数据，转换数据形式，SSL 验证签名，调用后端 HTTP API (httpclient 等 API) 执行实际请求，等待响应，失败重试，转换响应数据到 Java 类型等脏活累活都由这动态代理的实现类给包了。请求发送方调用这个接口时，实际上就是在调用这个干脏活累活的实现类重点更新内容此次版本发布主...

2022-05-20

517

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。