School - TiDB 根据 PK 查询不到数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

// 使用 min 函数可以查看到数据
MySQL [order-tidb]> select min(order_id) from t-order;
+----------------------------+
| min(order_id)              |
+----------------------------+
|   MBK0100057406149351733 |
+----------------------------+
1 row in set (0.12 sec)

// 执行 where 条件后返回为空
MySQL [tid b]> select * from t-order where order_id = 'MBK0100057406149351733';
Empty set (0.00 sec)

MySQL [ordertidb]> select max(order_id) from t-order;
+-----------------------------+
| max(order_id)               |
+-----------------------------+
| MBKÁ0060110911529542324199  |
+-----------------------------+
1 row in set (0.09 sec)

MySQL [order-tidb]> select order_id from t-order where order_id = 'MBKÁ0060110911529542324199';
+-----------------------------+
| order_id                    |
+-----------------------------+
| MBKÁ0060110911529542324199  |
+-----------------------------+
1 row in set (0.00 sec)

1
2
3
4
5
6

CREATE TABLE `t-order` (
  `order_id` varchar(35) NOT NULL COMMENT '订单ID',
  …… 省略其他信息
  PRIMARY KEY (`order_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='骑行订单表'
1 row in set (0.00 sec)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

// 查询 PK 与数据 行数是否一致
// 开始操作前调整 GC life 作为数据保底
update mysql.tidb set VARIABLE_VALUE = '1h' where VARIABLE_NAME = 'tikv_gc_life_time';

// 锁定 snapshot
MySQL [order-tidb]> set @@tidb_snapshot=now();
Query OK, 0 rows affected (0.01 sec)

// count 全表数据量查询
MySQL [order-tidb]> select count(*) from t-order;
+------------+
| count(*)   |
+------------+
| 1969978080 |
+------------+
1 row in set (3 min 4.04 sec)

// 使用 PK 查询数据量
MySQL [order-tidb]> select count(*) from t-order use index(`primary`);
+------------+
| count(*)   |
+------------+
| 1969978080 |
+------------+
1 row in set (3 min 56.73 sec)

// 得到结果数据一致

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

// 通过 base64 校验
MySQL [order-tidb]> select to_base64(min(order_id)) from t-order;
+--------------------------------------+
| to_base64(min(order_id))             |
+--------------------------------------+
| AAAfGk1CSzAxMDAwNTc0MDYxNDkzNTE3MzM= |
+--------------------------------------+
1 row in set (0.08 sec)

MySQL [order-tidb]> select to_base64('MBK0100057406149351733');
+-------------------------------------+
| to_base64('MBK0100057406149351733') |
+-------------------------------------+
| TUJLMDEwMDA1NzQwNjE0OTM1MTczMw==    |
+-------------------------------------+
1 row in set (0.00 sec)

// to base64 后得到得结果不一致，数据可能存在 UTF8 不可见字符

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

// PK(varchar) 在 2.0 版本中强制使用索引查询 / PK(varchar) 需要 TiDB 分配 tidb_rowid 作为 handle id 使用
MySQL [order-tidb]> explain select count(*) from trip_order use index(`primary`);
+----------------+--------------+----------------+------+------------------------------------------------------------------------+----------+
| id             | parents      | children       | task | operator info                                                          | count    |
+----------------+--------------+----------------+------+------------------------------------------------------------------------+----------+
| IndexScan_16   | StreamAgg_8  |                | cop  | table:trip_order, index:order_id, range:[<nil>,+inf], keep order:false | 10000.00 |
| StreamAgg_8    |              | IndexScan_16   | cop  | , funcs:count(1)                                                       | 1.00     |
| IndexReader_18 | StreamAgg_17 |                | root | index:StreamAgg_8                                                      | 1.00     |
| StreamAgg_17   |              | IndexReader_18 | root | , funcs:count(col_0)                                                   | 1.00     |
+----------------+--------------+----------------+------+------------------------------------------------------------------------+----------+
4 rows in set (0.01 sec)

// PK(int) 在 2.0 版本中强制使用索引查询 / PK(int) 等于 row handle id
MySQL [order-tidb]> explain select count(*) from trip_order use index();
+----------------+--------------+----------------+------+-------------------------------------------------------+----------+
| id             | parents      | children       | task | operator info                                         | count    |
+----------------+--------------+----------------+------+-------------------------------------------------------+----------+
| TableScan_15   | StreamAgg_8  |                | cop  | table:trip_order, range:[-inf,+inf], keep order:false | 10000.00 |
| StreamAgg_8    |              | TableScan_15   | cop  | , funcs:count(1)                                      | 1.00     |
| TableReader_17 | StreamAgg_16 |                | root | data:StreamAgg_8                                      | 1.00     |
| StreamAgg_16   |              | TableReader_17 | root | , funcs:count(col_0)                                  | 1.00     |
+----------------+--------------+----------------+------+-------------------------------------------------------+----------+
4 rows in set (0.00 sec)

MySQL [order-tidb]> explain select count(*) from trip_order use index( );
+----------------+--------------+----------------+------+-------------------------------------------------------+----------+
| id             | parents      | children       | task | operator info                                         | count    |
+----------------+--------------+----------------+------+-------------------------------------------------------+----------+
| TableScan_15   | StreamAgg_8  |                | cop  | table:trip_order, range:[-inf,+inf], keep order:false | 10000.00 |
| StreamAgg_8    |              | TableScan_15   | cop  | , funcs:count(1)                                      | 1.00     |
| TableReader_17 | StreamAgg_16 |                | root | data:StreamAgg_8                                      | 1.00     |
| StreamAgg_16   |              | TableReader_17 | root | , funcs:count(col_0)                                  | 1.00     |
+----------------+--------------+----------------+------+-------------------------------------------------------+----------+
4 rows in set (0.00 sec)