本文是从 秦天爽老师 视频中整理的一部分，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]>