c# - 使用变量时不使用索引

Question

背景:服务器应用程序 (C#) 使用下面的查询(简化)从数据库中获取订单数据。我记录了执行时间的平均时间，现在执行时间突然增加(平均从 5 毫秒增加到 50 毫秒)。所以我去检查这个查询作为开始。

查询是从服务器应用程序执行的，如下所示:

String ordernr = "123456789";
String sql = "SELECT * FROM MYDB.`MYTABLE` WHERE id = @id";
using (MySqlCommand cmd = new MySqlCommand(sql, conn))
{
    cmd.Parameters.Add("@id", MySqlDbType.VarChar, 16).Value = ordernr;
    using (MySqlDataReader reader = cmd.ExecuteReader())
    {
        //reading data
    }
}

当我在 mysql 中检查查询时，得到以下结果:

MariaDB [MYDB]> SET @id  = '123456789';
Query OK, 0 rows affected (0.00 sec)

MariaDB [MYDB]> explain SELECT SQL_NO_CACHE * FROM MYDB.`MYTABLE` WHERE id  = @id ;
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | MYTABLE | ALL  | NULL          | NULL | NULL    | NULL | 1448219 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

MariaDB [MYDB]> explain SELECT SQL_NO_CACHE * FROM MYDB.`MYTABLE` WHERE id  = '123456789';
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table     | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | MYTABLE | const | PRIMARY       | PRIMARY | 18      | const |    1 |       |
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

MariaDB [MYDB]>

现在我的问题是，为什么当我使用变量时主索引会被忽略？

我不知道这是否是性能下降的原因(因为在本例中两个查询都在 0.00 秒内返回)，但这让我皱起眉头为什么会有差异。我也在服务器应用程序中使用带有变量的准备好的查询。所以我想检查一下这是否相关。

谁能解释一下吗？

Answer 1

索引长度表明id实际上是一个char/varchar列(VARCHAR(16)是我的猜测)。

然后，问题是表或列的字符集与其中一个连接不匹配。考虑以下因素:

MariaDB [test]> create table t1 (id varchar(16) primary key, i int) charset latin1;
Query OK, 0 rows affected (0.72 sec)

MariaDB [test]> insert into t1 values ('123456789',1),('987654321',2);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> set names latin1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set @a='123456789';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain select * from t1 where id = @a;
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY | 18      | const |    1 |       |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

MariaDB [test]> set names utf8;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set @a='123456789';
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> explain select * from t1 where id = @a;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

您需要更改表，或者在 session 中设置字符集，或者使用显式转换:

MariaDB [test]> explain select * from t1 where id = @a;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

MariaDB [test]> explain select * from t1 where id = convert(@a using latin1);
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY | 18      | const |    1 |       |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+