TiDB索引不用于查询

Question

我使用mysql连接器与TiDB连接。我发现在我的查询中没有使用索引。经过我的分析，我发现由于铸造指标没有使用。E.g

CREATE TABLE Employee (
ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
EmpID VARCHAR(200) NOT NULL
);
CREATE INDEX idx_EmpID ON Employee(EmpID);
1.Explain SELECT * FROM Employee WHERE EmpID= 123;
2.Explain SELECT * FROM Employee WHERE EmpID= '123';

在上面的示例中，如果我运行第一个查询并使用explain检查执行计划，那么在第二个查询中不使用索引，因为我正在为varchar数据类型索引提供引号。

但是，该解决方案需要通过.net mysql连接器库进行测试。目前，我正在使用mysql命令参数来执行查询。

1.电流逻辑与AddWithValue函数

                MySqlCommand cmd1 = new MySqlCommand
                {
                    CommandText = "Select * from Employee where EmpID = @EmpID"
                };

                cmd1.Parameters.AddWithValue($"@EmpID", 1234);

2.通过使用Add函数为字段提供数据类型来更新逻辑

                MySqlCommand cmd1 = new MySqlCommand
                {
                    CommandText = "Select * from Employee where EmpID = @EmpID"
                };

                cmd1.Parameters.Add($"@EmpID", MySqlDbType.VarChar).Value = 1234;

在处理请求时，我想知道服务器端的查询表示。更新的逻辑.i.e点2会通过为varchar数据类型提供引号来形成查询吗？

Answer 1

我认为TiDB和MySQL在这里有同样的行为。如果要将VARCHAR列与字符串进行比较，则它将其作为字符串进行比较，并且它将使用索引。当将其与整数进行比较时，它将尝试将每一行的值转换为整数，然后进行比较。那么它就不能使用索引了。请注意，您需要向表中添加几行以获得更真实的解释计划。还请注意，取决于类型，结果可能是不同的。

mysql 8.0.22 > CREATE TABLE Employee (
    -> ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> EmpID VARCHAR(200) NOT NULL
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql 8.0.22 > CREATE INDEX idx_EmpID ON Employee(EmpID);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 8.0.22 > EXPLAIN SELECT * FROM Employee WHERE EmpID= 123;
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | Employee | NULL       | index | idx_EmpID     | idx_EmpID | 802     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 3 warnings (0.00 sec)

mysql 8.0.22 > EXPLAIN SELECT * FROM Employee WHERE EmpID= '123';
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | Employee | NULL       | ref  | idx_EmpID     | idx_EmpID | 802     | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql 8.0.22 > INSERT INTO Employee(EmpID) VALUES ('010'),('10');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 8.0.22 > SELECT * FROM Employee;
+----+-------+
| ID | EmpID |
+----+-------+
|  1 | 010   |
|  2 | 10    |
+----+-------+
2 rows in set (0.00 sec)

mysql 8.0.22 > SELECT * FROM Employee WHERE EmpID='10';
+----+-------+
| ID | EmpID |
+----+-------+
|  2 | 10    |
+----+-------+
1 row in set (0.00 sec)

mysql 8.0.22 > SELECT * FROM Employee WHERE EmpID=10;
+----+-------+
| ID | EmpID |
+----+-------+
|  1 | 010   |
|  2 | 10    |
+----+-------+
2 rows in set (0.00 sec)

tidb 5.7.25-TiDB-v5.0.0 > CREATE TABLE Employee (
    -> ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> EmpID VARCHAR(200) NOT NULL
    -> );
Query OK, 0 rows affected (0.15 sec)

tidb 5.7.25-TiDB-v5.0.0 > CREATE INDEX idx_EmpID ON Employee(EmpID);
Query OK, 0 rows affected (2.87 sec)

tidb 5.7.25-TiDB-v5.0.0 > EXPLAIN SELECT * FROM Employee WHERE EmpID= 123;
+-------------------------+----------+-----------+----------------+------------------------------------+
| id                      | estRows  | task      | access object  | operator info                      |
+-------------------------+----------+-----------+----------------+------------------------------------+
| TableReader_7           | 8000.00  | root      |                | data:Selection_6                   |
| └─Selection_6           | 8000.00  | cop[tikv] |                | eq(cast(test.employee.empid), 123) |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:Employee | keep order:false, stats:pseudo     |
+-------------------------+----------+-----------+----------------+------------------------------------+
3 rows in set (0.01 sec)

tidb 5.7.25-TiDB-v5.0.0 > EXPLAIN SELECT * FROM Employee WHERE EmpID= '123';
+------------------------+---------+-----------+----------------------------------------+-----------------------------------------------------+
| id                     | estRows | task      | access object                          | operator info                                       |
+------------------------+---------+-----------+----------------------------------------+-----------------------------------------------------+
| IndexReader_6          | 10.00   | root      |                                        | index:IndexRangeScan_5                              |
| └─IndexRangeScan_5     | 10.00   | cop[tikv] | table:Employee, index:idx_EmpID(EmpID) | range:["123","123"], keep order:false, stats:pseudo |
+------------------------+---------+-----------+----------------------------------------+-----------------------------------------------------+
2 rows in set (0.00 sec)

tidb 5.7.25-TiDB-v5.0.0 > INSERT INTO Employee(EmpID) VALUES ('010'),('10');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

tidb 5.7.25-TiDB-v5.0.0 > SELECT * FROM Employee;
+----+-------+
| ID | EmpID |
+----+-------+
|  1 | 010   |
|  2 | 10    |
+----+-------+
2 rows in set (0.00 sec)

tidb 5.7.25-TiDB-v5.0.0 > SELECT * FROM Employee WHERE EmpID='10';
+----+-------+
| ID | EmpID |
+----+-------+
|  2 | 10    |
+----+-------+
1 row in set (0.00 sec)

tidb 5.7.25-TiDB-v5.0.0 > SELECT * FROM Employee WHERE EmpID=10;
+----+-------+
| ID | EmpID |
+----+-------+
|  1 | 010   |
|  2 | 10    |
+----+-------+
2 rows in set (0.00 sec)