TiDB 联合索引是如何工作的

本文是从 秦天爽老师 视频中整理的一部分,TiDB 联合索引是如何工作的

https://university.pingcap.com/views/common/audition.html?courseId=780063&courseWareId=900069&designId=780597

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赞

感谢整理分享:+1:

在学习知识的过程中不忘分享,非常感谢楼主对社区的建设 :+1: