TiDB 联合索引是如何工作的
作者：TiDB 社区干货传送门
- 2022 年 7 月 11 日
本文字数：7388 字
阅读完需：约 24 分钟
作者： mao_siyu 原文来源：https://tidb.net/blog/57f983bc
本文是从 秦天爽老师 视频中整理的一部分，TiDB 联合索引是如何工作的
首页
PingCAP University 是 PingCAP 官方面向企业和个人，培养 TiDB 领域 DBA、业务开发、社区贡献者的培训和认证机构。课程包括：TiDB、TiSpark、TiFlash、Cloud TiDB 等视频课程、Demo 演示、实操指导等，还包括 TiDB 考试大纲、认证指引。
TiDB 版本 3.0.5
TiDB 联合索引是如何工作的
联合索引可以这样理解，比如
（a,b,c），abc 都是排好序的，在任意一段 a 的下面 b 都是排好序的，任何一段 b 下面 c 都是排好序的；联合索引的生效原则是 从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；
查询的顺序是指 索引中的顺序
index:a, b, c,而不是WHERE条件的顺序TiDB 的联合索引只占用一个名额，例如: table_1 有 唯一索引, 联合索引 计算方式是
30W / (1+1+1) = 10W
创建带有联合索引的表
CREATE TABLE table_1 ( a BIGINT, b VARCHAR ( 255 ), c INT );ALTER TABLE table_1 ADD INDEX m_index ( a, b, c );
复制代码
使用查询计划来查看，联合索引是如何执行的
1 等值查询
1.1 WHERE a = 1 AND b = '2' AND c = 3;
这种三个索引顺序使用中间没有断点，全部发挥作用
MySQL [sbtest]> EXPLAIN ANALYZE SELECT * FROM table_1 WHERE a = 1 AND b = '2' AND c = 3;+-------------------+-------+------+---------------------------------------------------------------------------------------+----------------------------------+-----------+| id | count | task | operator info | execution info | memory |+-------------------+-------+------+---------------------------------------------------------------------------------------+----------------------------------+-----------+| IndexReader_6 | 0.00 | root | index:IndexScan_5 | time:1.774986ms, loops:1, rows:0 | 184 Bytes || └─IndexScan_5 | 0.00 | cop | table:table_1, index:a, b, c, range:[1 "2" 3,1 "2" 3], keep order:false, stats:pseudo | time:0s, loops:1, rows:0 | N/A |+-------------------+-------+------+---------------------------------------------------------------------------------------+----------------------------------+-----------+2 rows in set (0.01 sec)
MySQL [sbtest]>
复制代码
1.2 WHERE a = 1 AND c = 3;
这种情况下 b 就是断点，a 发挥了效果，c 没有效果
MySQL [sbtest]> EXPLAIN ANALYZE SELECT * FROM table_1 WHERE a = 1 AND c = 3;+---------------------+-------+------+---------------------------------------------------------------------------+----------------------------------+-----------+| id | count | task | operator info | execution info | memory |+---------------------+-------+------+---------------------------------------------------------------------------+----------------------------------+-----------+| IndexReader_7 | 0.01 | root | index:Selection_6 | time:1.692165ms, loops:1, rows:0 | 160 Bytes || └─Selection_6 | 0.01 | cop | eq(sbtest.table_1.c, 3) | time:0s, loops:1, rows:0 | N/A || └─IndexScan_5 | 10.00 | cop | table:table_1, index:a, b, c, range:[1,1], keep order:false, stats:pseudo | time:0s, loops:1, rows:0 | N/A |+---------------------+-------+------+---------------------------------------------------------------------------+----------------------------------+-----------+3 rows in set (0.00 sec)
MySQL [sbtest]>
复制代码
1.3 WHERE b = '2' AND c = 3;
这种情况下 a 就是断点，在 a 后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果
MySQL [sbtest]> EXPLAIN ANALYZE SELECT * FROM table_1 WHERE b = '2' AND c = 3;+---------------------+----------+------+---------------------------------------------------------------------------------+----------------------------------+-----------+| id | count | task | operator info | execution info | memory |+---------------------+----------+------+---------------------------------------------------------------------------------+----------------------------------+-----------+| IndexReader_7 | 0.01 | root | index:Selection_6 | time:3.071065ms, loops:1, rows:0 | 142 Bytes || └─Selection_6 | 0.01 | cop | eq(sbtest.table_1.b, "2"), eq(sbtest.table_1.c, 3) | time:0s, loops:1, rows:0 | N/A || └─IndexScan_5 | 10000.00 | cop | table:table_1, index:a, b, c, range:[NULL,+inf], keep order:false, stats:pseudo | time:0s, loops:1, rows:0 | N/A |+---------------------+----------+------+---------------------------------------------------------------------------------+----------------------------------+-----------+3 rows in set (0.01 sec)
MySQL [sbtest]>
复制代码
1.4 WHERE b = '2' AND c = 3 AND a = 1;
这个跟第一个一样，全部发挥作用，abc 只要用上了就行，跟写的顺序无关
MySQL [sbtest]> EXPLAIN ANALYZE SELECT * FROM table_1 WHERE b = '2' AND c = 3 AND a = 1;+-------------------+-------+------+---------------------------------------------------------------------------------------+----------------------------------+-----------+| id | count | task | operator info | execution info | memory |+-------------------+-------+------+---------------------------------------------------------------------------------------+----------------------------------+-----------+| IndexReader_6 | 0.00 | root | index:IndexScan_5 | time:921.345µs, loops:1, rows:0 | 182 Bytes || └─IndexScan_5 | 0.00 | cop | table:table_1, index:a, b, c, range:[1 "2" 3,1 "2" 3], keep order:false, stats:pseudo | time:0s, loops:1, rows:0 | N/A |+-------------------+-------+------+---------------------------------------------------------------------------------------+----------------------------------+-----------+2 rows in set (0.00 sec)
MySQL [sbtest]>
复制代码
1.5 WHERE a = 1 AND b IN ( '2', '4') AND c = 3;
在 TiDB 中 除了 = 等号 和 IN 以外都是范围查询 b 是等值查询
MySQL [sbtest]> EXPLAIN ANALYZE SELECT * FROM table_1 WHERE a = 1 AND b IN ( '2', '4') AND c = 3;+-------------------+-------+------+----------------------------------------------------------------------------------------------------------+----------------------------------+-----------+| id | count | task | operator info | execution info | memory |+-------------------+-------+------+----------------------------------------------------------------------------------------------------------+----------------------------------+-----------+| IndexReader_6 | 0.00 | root | index:IndexScan_5 | time:1.920177ms, loops:1, rows:0 | 186 Bytes || └─IndexScan_5 | 0.00 | cop | table:table_1, index:a, b, c, range:[1 "2" 3,1 "2" 3], [1 "4" 3,1 "4" 3], keep order:false, stats:pseudo | time:0s, loops:1, rows:0 | N/A |+-------------------+-------+------+----------------------------------------------------------------------------------------------------------+----------------------------------+-----------+2 rows in set (0.01 sec)
MySQL [sbtest]>
复制代码
2 范围查询
2.1 WHERE a = 1 AND b > '2' AND c = 3;
b 是范围查询, 区间是左开右闭，导致 c 不起作用
MySQL [sbtest]> EXPLAIN ANALYZE SELECT * FROM table_1 WHERE a = 1 AND b > '2' AND c = 3;+---------------------+-------+------+------------------------------------------------------------------------------------+----------------------------------+-----------+| id | count | task | operator info | execution info | memory |+---------------------+-------+------+------------------------------------------------------------------------------------+----------------------------------+-----------+| IndexReader_7 | 0.03 | root | index:Selection_6 | time:1.376259ms, loops:1, rows:0 | 176 Bytes || └─Selection_6 | 0.03 | cop | eq(sbtest.table_1.c, 3) | time:0s, loops:1, rows:0 | N/A || └─IndexScan_5 | 33.33 | cop | table:table_1, index:a, b, c, range:(1 "2",1 +inf], keep order:false, stats:pseudo | time:0s, loops:1, rows:0 | N/A |+---------------------+-------+------+------------------------------------------------------------------------------------+----------------------------------+-----------+3 rows in set (0.00 sec)
MySQL [sbtest]>
复制代码
2.2 WHERE a = 1 AND b < '2' AND c = 3;
b 是范围查询, 区间是左闭右开，导致 c 不起作用
MySQL [sbtest]> EXPLAIN ANALYZE SELECT * FROM table_1 WHERE a = 1 AND b < '2' AND c = 3;+---------------------+-------+------+------------------------------------------------------------------------------------+----------------------------------+-----------+| id | count | task | operator info | execution info | memory |+---------------------+-------+------+------------------------------------------------------------------------------------+----------------------------------+-----------+| IndexReader_7 | 0.03 | root | index:Selection_6 | time:1.696979ms, loops:1, rows:0 | 158 Bytes || └─Selection_6 | 0.03 | cop | eq(sbtest.table_1.c, 3) | time:0s, loops:1, rows:0 | N/A || └─IndexScan_5 | 33.23 | cop | table:table_1, index:a, b, c, range:[1 -inf,1 "2"), keep order:false, stats:pseudo | time:0s, loops:1, rows:0 | N/A |+---------------------+-------+------+------------------------------------------------------------------------------------+----------------------------------+-----------+3 rows in set (0.01 sec)
MySQL [sbtest]>
复制代码
2.3 WHERE a = 1 AND b like '2%' AND c = 3;
b 是范围查询, like 的区间是左闭右开 使用的是前缀范围查询，导致 c 不起作用
MySQL [sbtest]> EXPLAIN ANALYZE SELECT * FROM table_1 WHERE a = 1 AND b like '2%' AND c = 3;+---------------------+-------+------+-----------------------------------------------------------------------------------+----------------------------------+-----------+| id | count | task | operator info | execution info | memory |+---------------------+-------+------+-----------------------------------------------------------------------------------+----------------------------------+-----------+| IndexReader_7 | 0.00 | root | index:Selection_6 | time:1.800475ms, loops:1, rows:0 | 176 Bytes || └─Selection_6 | 0.00 | cop | eq(sbtest.table_1.c, 3) | time:0s, loops:1, rows:0 | N/A || └─IndexScan_5 | 2.50 | cop | table:table_1, index:a, b, c, range:[1 "2",1 "3"), keep order:false, stats:pseudo | time:0s, loops:1, rows:0 | N/A |+---------------------+-------+------+-----------------------------------------------------------------------------------+----------------------------------+-----------+3 rows in set (0.00 sec)
MySQL [sbtest]>
复制代码
2.4 WHERE a = 1 AND b like '%2' AND c = 3;
b 是范围查询, 使用的是非前缀范围查询，TiDB 目前是不能够使用非前缀范围查询索引的，导致 b,c 都不起作用
MySQL [sbtest]> EXPLAIN ANALYZE SELECT * FROM table_1 WHERE a = 1 AND b like '%2' AND c = 3;+---------------------+-------+------+---------------------------------------------------------------------------+----------------------------------+-----------+| id | count | task | operator info | execution info | memory |+---------------------+-------+------+---------------------------------------------------------------------------+----------------------------------+-----------+| IndexReader_7 | 0.01 | root | index:Selection_6 | time:1.461115ms, loops:1, rows:0 | 160 Bytes || └─Selection_6 | 0.01 | cop | eq(sbtest.table_1.c, 3), like(sbtest.table_1.b, "%2", 92) | time:0s, loops:1, rows:0 | N/A || └─IndexScan_5 | 10.00 | cop | table:table_1, index:a, b, c, range:[1,1], keep order:false, stats:pseudo | time:0s, loops:1, rows:0 | N/A |+---------------------+-------+------+---------------------------------------------------------------------------+----------------------------------+-----------+3 rows in set (0.01 sec)
MySQL [sbtest]>
复制代码
2.5 WHERE a IS NULL AND b = '2' AND c = 3;
在 TiDB 中 除了 = 等号 和 IN 以外都是范围查询
MySQL [sbtest]> EXPLAIN ANALYZE SELECT * FROM table_1 WHERE a IS NULL AND b = '2' AND c = 3;+---------------------+-------+------+---------------------------------------------------------------------------------+----------------------------------+-----------+| id | count | task | operator info | execution info | memory |+---------------------+-------+------+---------------------------------------------------------------------------------+----------------------------------+-----------+| IndexReader_7 | 0.00 | root | index:Selection_6 | time:2.761675ms, loops:1, rows:0 | 144 Bytes || └─Selection_6 | 0.00 | cop | eq(sbtest.table_1.b, "2"), eq(sbtest.table_1.c, 3) | time:0s, loops:1, rows:0 | N/A || └─IndexScan_5 | 10.00 | cop | table:table_1, index:a, b, c, range:[NULL,NULL], keep order:false, stats:pseudo | time:0s, loops:1, rows:0 | N/A |+---------------------+-------+------+---------------------------------------------------------------------------------+----------------------------------+-----------+3 rows in set (0.01 sec)
MySQL [sbtest]>
复制代码
划线
评论
复制
发布于: 刚刚阅读数: 2
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/4f5dab1a19ece422e9c0c72e5】。文章转载请联系作者。
TiDB 社区干货传送门
关注
TiDB 社区官网:https://tidb.net/ 2021.12.15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论